CODEWITHSUNDEEP
sql-servert-sql
Beans
Beans

Reputation: 329

TSQL Sum Rows with dates less than date value for each distinct date

I have a table structured like so:

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)

How can I write a query to do what the query below does for each distinct ReportDate:

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)

Upvotes: 1

Views: 2163

Answers (2)

Dave.Gugg
Dave.Gugg

Reputation: 6781

You can inner join the table to itself on the report date <= report date.

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

Upvotes: 3

Lamak
Lamak

Reputation: 70678

You can use 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

Upvotes: 2

Related Questions