MS SQL Server
  1. MS SQL Server
  3. Obtenir des plages de dates qui ne se chevauchent pas pour datatables d'historique des prix
Intereting Posts
SQL Server: requête pour get des mises à jour incrémentielles de table Passer la table en paramètre dans le server sql UDF Quel ordre de jointure est plus rapide? Existe-t-il un moyen d'interroger une database de sauvegarde sans la restaurer? meilleur classment par défaut d'une database multilingue Requête simple concernant le service WCF SQL Server 2014 – Plusieurs SUM / COUNT avec sous-requêtes Grouper les tables par schéma dans SSMS SQL Calculer 20 jours ouvrables, supprimer les hols bancaires et faire reference à une autre requête Convertir des lignes en colonnes à l'aide de 'Pivot' dans SQL Server Impossible de se connecter immédiatement à la database SQL Server nouvellement créée GetValue d'une propriété SQL Server 2005 Liens vers l'access Convertir le jour de l'année en datetime dans SQL Server Entity Framework Il existe déjà un DataReader ouvert associé à cette command qui doit être fermé en premier

Obtenir des plages de dates qui ne se chevauchent pas pour datatables d'historique des prix

Supposons que j'ai le tableau suivant: 

CREATE TABLE [dbo].[PricesHist] ( [Product] [varchar](6) NOT NULL, [Price] [float] NOT NULL, [StartDate] [datetime] NOT NULL, [EndDate] [datetime] NOT NULL ) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D2C00000000 AS DateTime), CAST(0x00009D2C00000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D2D00000000 AS DateTime), CAST(0x00009D2D00000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D2E00000000 AS DateTime), CAST(0x00009D2E00000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3000000000 AS DateTime), CAST(0x00009D3000000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3100000000 AS DateTime), CAST(0x00009D3100000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3400000000 AS DateTime), CAST(0x00009D3400000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D3500000000 AS DateTime), CAST(0x00009D3500000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3600000000 AS DateTime), CAST(0x00009D3600000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3700000000 AS DateTime), CAST(0x00009D3700000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3800000000 AS DateTime), CAST(0x00009D3800000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3A00000000 AS DateTime), CAST(0x00009D3A00000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3B00000000 AS DateTime), CAST(0x00009D3B00000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D3C00000000 AS DateTime), CAST(0x00009D3C00000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3D00000000 AS DateTime), CAST(0x00009D3D00000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3E00000000 AS DateTime), CAST(0x00009D3E00000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3F00000000 AS DateTime), CAST(0x00009D3F00000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4100000000 AS DateTime), CAST(0x00009D4100000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4200000000 AS DateTime), CAST(0x00009D4200000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D4300000000 AS DateTime), CAST(0x00009D4300000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4400000000 AS DateTime), CAST(0x00009D4400000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4500000000 AS DateTime), CAST(0x00009D4500000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4600000000 AS DateTime), CAST(0x00009D4600000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4800000000 AS DateTime), CAST(0x00009D4800000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D4A00000000 AS DateTime), CAST(0x00009D4A00000000 AS DateTime))

Comme vous pouvez le voir, il y a deux prix sur ce mois pour Apple. 4,90 et 2,50. Afin de ranger cette table, j'ai besoin d'get cette information comme une plage de dates plutôt qu'une rangée par jour comme c'est actuellement.

Je peux évidemment le faire avec des agrégats Min et Max facilement, mais les plages se chevauchent et d'autres codes d'entreprise attendent des plages non superposées. J'ai aussi essayé d'y parvenir avec self joins et row_number (), mais sans grand succès …

Voici ce que j'essaie de réaliser en sortie: 

 Product | StartDate | EndDate | Price ------------------------------------------- Apples | 01 Mar 2010 | 02 Mar 2010 | 4.90 Apples | 03 Mar 2010 | 03 Mar 2010 | 2.50 Apples | 05 Mar 2010 | 09 Mar 2010 | 4.90 Apples | 10 Mar 2010 | 10 Mar 2010 | 2.50 Apples | 11 Mar 2010 | 16 Mar 2010 | 4.90 Apples | 17 Mar 2010 | 17 Mar 2010 | 2.50 Apples | 18 Mar 2010 | 23 Mar 2010 | 4.90 Apples | 24 Mar 2010 | 24 Mar 2010 | 2.50 Apples | 25 Mar 2010 | 30 Mar 2010 | 4.90 Apples | 31 Mar 2010 | 31 Mar 2010 | 2.50

Quelle serait la meilleure approche pour y arriver?

Merci beaucoup d'avance,

Cela devrait vous rapprocher. Une fois que vous avez déterminé comment gérer les dates manquantes, vous pouvez l'ajuster de manière appropriée: 

 SELECT SD.Product, SD.Price, SD.StartDate, MAX(ED.EndDate) AS EndDate FROM dbo.PricesHist SD LEFT OUTER JOIN dbo.PricesHist ED ON ED.Product = SD.Product AND ED.Price = SD.Price LEFT OUTER JOIN dbo.PricesHist LD ON LD.Product = SD.Product AND LD.Price <> SD.Price AND LD.EndDate < SD.StartDate LEFT OUTER JOIN dbo.PricesHist LMD ON LMD.Product = SD.Product AND LMD.Price = SD.Price AND LMD.StartDate > ISNULL(LD.EndDate, '1900-01-01') AND LMD.StartDate < SD.StartDate WHERE NOT EXISTS (SELECT * FROM dbo.PricesHist MD WHERE MD.Product = SD.Product AND MD.Price <> SD.Price AND MD.StartDate BETWEEN SD.StartDate AND ED.EndDate) AND LMD.Product IS NULL GROUP BY SD.Product, SD.Price, SD.StartDate ORDER BY SD.StartDate