-
Generating totals based on date field comparission
Hello,
I am attempting to rewrite an old application that did a lot of math
using date fields. This old application cycled through each record
and added a quantity value to a "running total" based on its date
value. I am attempting to do this more "elegantly" through a stored
proc, and I could use some assistance.
An example of the data would look something like this:
PartNumber,Due Date,Quantity
127013G,10/07/08,36
240066K,09/30/08,156
240066K,10/15/08,312
240094F,10/09/08,120
260020F,09/30/08,440
260020F,10/06/08,440
260020F,12/16/08,220
My first task was to group things by part number, and display the
total number of parts and number of records for each part number. I
did this:
SELECT PartNo, SUM(Qty) AS [Total Qty on Order], COUNT(Qty) AS
[Total Record Count]
FROM dbo.MyView
GROUP BY PartNo
HAVING (PartNo IS NOT NULL)
…which gave me these results:
PartNumber, [Total Qty on Order],[Total Record Count]
127013G,36,1
240066K,468,2
240094F,120,1
260020F,1100,3
This is good, but now I need to add some columns that show quantity
totals based on the week that the "Due Date" is in. For example:
PartNumber, [Total Qty on Order],[Total Record Count],[This Week],
[Next Week],[Week2]
127013G,36,1,0,36,0
240066K,468,2,156,0,312
240094F,120,1,0,120,0
260020F,1100,3,880,0,0
How can I get these results? I know how to filter records by date
range, but I don't know how to create fields that do totaling based on
this.
I would greatly appreciate any suggestions or comments.
Thank you!
-
Re: Generating totals based on date field comparission
You can use CASE to determine the week for each summary column:
SELECT PartNo,
SUM(Qty) As total_qty,
SUM(CASE WHEN DATEPART(wk, CURRENT_TIMESTAMP) =
DATEPART(wk, DueDate)
THEN Qty
ELSE 0
END) AS this_week,
SUM(CASE WHEN DATEPART(wk, CURRENT_TIMESTAMP) + 1 =
DATEPART(wk, DueDate)
THEN Qty
ELSE 0
END) AS next_week,
SUM(CASE WHEN DATEPART(wk, CURRENT_TIMESTAMP) + 2 =
DATEPART(wk, DueDate)
THEN Qty
ELSE 0
END) AS week2
FROM MyView
GROUP BY PartNo;
--
Plamen Ratchev
http://www.SQLStudio.com
-
Re: Generating totals based on date field comparission
On Sep 30, 3:27*pm, Plamen Ratchev wrote:
> You can use CASE to determine the week for each summary column:
>
> SELECT PartNo,
> * * * * SUM(Qty) As total_qty,
> * * * * SUM(CASE WHEN DATEPART(wk, CURRENT_TIMESTAMP) =
> * * * * * * * * * * * DATEPART(wk, DueDate)
> * * * * * * * * *THEN Qty
> * * * * * * * * *ELSE 0
> * * * * * * END) AS this_week,
> * * * * SUM(CASE WHEN DATEPART(wk, CURRENT_TIMESTAMP) + 1 =
> * * * * * * * * * * * DATEPART(wk, DueDate)
> * * * * * * * * *THEN Qty
> * * * * * * * * *ELSE 0
> * * * * * * END) AS next_week,
> * * * * SUM(CASE WHEN DATEPART(wk, CURRENT_TIMESTAMP) + 2 =
> * * * * * * * * * * * DATEPART(wk, DueDate)
> * * * * * * * * *THEN Qty
> * * * * * * * * *ELSE 0
> * * * * * * END) AS week2
> FROM MyView
> GROUP BY PartNo;
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com
Plamen,
That was EXACTLY what I wanted! I thought I tried something like that
before, but I never could get the syntax correct.
Thank you VERY MUCH for you quick reply! You really helped me out of
a jam.
