+ Reply to Thread
Results 1 to 3 of 3

Generating totals based on date field comparission

  1. 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!


  2. 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

  3. 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.

+ Reply to Thread