TSQL Sum Rows avec des dates inférieures à la valeur de date pour chaque date distincte

J'ai une table structurée comme ça:

CREATE TABLE [dbo].[vt]( [id] [uniqueidentifier] NOT NULL, [ones] [money] NULL, [twos] [money] NULL, [fives] [money] NULL, [tens] [money] NULL, [twenties] [money] NULL, [fifties] [money] NULL, [hundreds] [money] NULL, [ReportDate] [datetime] NULL)

Comment puis-je écrire une requête pour faire ce que la requête ci-dessous fait pour chaque ReportDate distinct:

 declare @date datetime set @date = '11/3/2014'; SELECT ISNULL(SUM([ones]), 0) AS ones ,ISNULL(SUM([twos]), 0) AS twos ,ISNULL(SUM([fives]), 0) AS fives ,ISNULL(SUM([tens]), 0) AS tens ,ISNULL(SUM([twenties]), 0) AS twenties ,ISNULL(SUM([fifties]), 0) AS fifties ,ISNULL(SUM([hundreds]), 0) AS hundreds ,max(ReportDate) as ReportDate FROM [vt] WHERE ReportDate is not null and reportDate <= (@date)

Vous pouvez utiliser OUTER APPLY :

 ;WITH Dates AS ( SELECT DISTINCT ReportDate FROM [vt] ) SELECT * FROM Dates A OUTER APPLY ( SELECT ISNULL(SUM([ones]), 0) AS ones, ISNULL(SUM([twos]), 0) AS twos, ISNULL(SUM([fives]), 0) AS fives, ISNULL(SUM([tens]), 0) AS tens, ISNULL(SUM([twenties]), 0) AS twenties, ISNULL(SUM([fifties]), 0) AS fifties, ISNULL(SUM([hundreds]), 0) AS hundreds, MAX(ReportDate) as ReportDate FROM [vt] WHERE reportDate <= A.ReportDate) B

Vous pouvez joindre la table à la date du rapport <= date du rapport.

 SELECT ISNULL(SUM(b.[ones]), 0) AS ones , ISNULL(SUM(b.[twos]), 0) AS twos , ISNULL(SUM(b.[fives]), 0) AS fives , ISNULL(SUM(b.[tens]), 0) AS tens , ISNULL(SUM(b.[twenties]), 0) AS twenties , ISNULL(SUM(b.[fifties]), 0) AS fifties , ISNULL(SUM(b.[hundreds]), 0) AS hundreds , a.ReportDate FROM [vt] a INNER JOIN [vt] b ON b.ReportDate <= a.ReportDate WHERE a.ReportDate IS NOT NULL GROUP BY a.ReportDate