Daily / MTD / YTD Reporting

One way (I am sure there are more efficient ways) off the top of my head:


DECLARE @today SMALLDATETIME,
@fdm SMALLDATETIME,
@fdy SMALLDATETIME;

SELECT @today = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP),
@fdm = @today - DAY(@today) + 1,
@fdy = RTRIM(YEAR(@today)) + '0101';

SELECT [Status],
[Today] = SUM(CASE WHEN [Date] >= @today THEN Price ELSE 0 END),
[MTD] = SUM(CASE WHEN [Date] >= @fdm THEN Price ELSE 0 END),
[YTD] = SUM(CASE WHEN [Date] >= @fdy THEN Price ELSE 0 END)
FROM
[table]
GROUP BY [Status];







On 8/26/08 8:40 PM, in article
ae5eff7e-2c82-47df-a7d5-1c1c78330c92...oglegroups.com,
"Neufusion" wrote:

> I have data in this format:
> OrderID, Price, Date, Status
> 1, 2.00, 2008-08-23, CREATED
> ..
>
> 25, 3.00, 2008-08-03, SUBMITTED
> ...
>
> 343, 4.00, 2008-05-21, CANCELLED
>
> I want to display in this format:
> Status, Total Price Today (SUM), Qty Today (Count), Total Price MTD
> (SUM), Total Price MTD
> CREATED, etc, etc
> SUBMITTED, etc, etc
> CANCELLED, etc, etc
>
> What is the best way to write this query?
>
> Can it be done without temp tables? I started to go down that path,
> but thought there had to be a better way to do it.
>
> On a side note, I antcipate them asking for QTD and/or YTD soon so
> something easily extendible into other periods would be great.

SUM(CASE...) can allow you to total on multiple date ranges in one
pass.

SELECT Status,
SUM(CASE WHEN Date >= @D1Start
AND Date < @D1End
THEN Price
ELSE 0
END) as SumD1,
SUM(CASE WHEN Date >= @D2Start
AND Date < @D2End
THEN Price
ELSE 0
END) as SumD2
FROM Whatever
WHERE (Date >= @D1Start
AND Date < @D1End)
OR (Date >= @D2Start
AND Date < @D2End)
GROUP BY Status

Roy Harvey
Beacon Falls, CT

On Tue, 26 Aug 2008 17:40:22 -0700 (PDT), Neufusion
wrote:

>I have data in this format:
>OrderID, Price, Date, Status
>1, 2.00, 2008-08-23, CREATED
>..
>25, 3.00, 2008-08-03, SUBMITTED
>...
>343, 4.00, 2008-05-21, CANCELLED
>
>I want to display in this format:
>Status, Total Price Today (SUM), Qty Today (Count), Total Price MTD
>(SUM), Total Price MTD
>CREATED, etc, etc
>SUBMITTED, etc, etc
>CANCELLED, etc, etc
>
>What is the best way to write this query?
>
>Can it be done without temp tables? I started to go down that path,
>but thought there had to be a better way to do it.
>
>On a side note, I antcipate them asking for QTD and/or YTD soon so
>something easily extendible into other periods would be great.

Just FYI, but this is the type of analysis that SSAS excels at, and it might
be worth investigating/playing around with if you find yourself doing these
types of calculations often or for many different data items.

--

========
Michael Coles
"Pro T-SQL 2008 Programmer's Guide"
http://www.amazon.com/T-SQL-2008-Pro.../dp/143021001X


"Neufusion" wrote in message
news:ae5eff7e-2c82-47df-a7d5-1c1c78330c92@v16g2000prc.googlegroups.com...
>I have data in this format:
> OrderID, Price, Date, Status
> 1, 2.00, 2008-08-23, CREATED
> ..
> 25, 3.00, 2008-08-03, SUBMITTED
> ...
> 343, 4.00, 2008-05-21, CANCELLED
>
> I want to display in this format:
> Status, Total Price Today (SUM), Qty Today (Count), Total Price MTD
> (SUM), Total Price MTD
> CREATED, etc, etc
> SUBMITTED, etc, etc
> CANCELLED, etc, etc
>
> What is the best way to write this query?
>
> Can it be done without temp tables? I started to go down that path,
> but thought there had to be a better way to do it.
>
> On a side note, I antcipate them asking for QTD and/or YTD soon so
> something easily extendible into other periods would be great.