Professional Web Applications Themes

create YTD and YEAR totals from months - Microsoft SQL / MS SQL Server

Hi, I've a table with products and months like: PRODUCT (k), MONTH(k), QTY, QTY_YTD, QTY_YEAR 100 022001 10 0 0 100 042001 15 0 0 100 022002 20 0 0 200 012000 25 0 0 - Is it possible to update QTY_YTD (year-to-date) and QTY_YEAR without an cursor, ie in an sql statement ? PRODUCT (k), MONTH(k), QTY, QTY_YTD, QTY_YEAR 100 012001 0 0 45 100 022001 10 10 45 100 032001 0 10 45 100 042001 15 25 45 .... /jerome...

  1. #1

    Default create YTD and YEAR totals from months

    Hi,

    I've a table with products and months like:
    PRODUCT (k), MONTH(k), QTY, QTY_YTD, QTY_YEAR
    100 022001 10 0 0
    100 042001 15 0 0
    100 022002 20 0 0
    200 012000 25 0 0

    - Is it possible to update QTY_YTD (year-to-date) and
    QTY_YEAR without an cursor, ie in an sql statement ?

    PRODUCT (k), MONTH(k), QTY, QTY_YTD, QTY_YEAR
    100 012001 0 0 45
    100 022001 10 10 45
    100 032001 0 10 45
    100 042001 15 25 45
    ....

    /jerome


    jerome Guest

  2. #2

    Default Re: create YTD and YEAR totals from months


    --this shows you the sum....
    SELECT SUM(QTY) FROM YourTable GROUP BY [PRODUCT (k)]

    Now all you have to do is update the table...something like this should work:
    UPDATE YourTable SET QTY_YTD = B.SumQty
    FROM YourTable A
    JOIN (SELECT SUM(QTY) AS SumQty, MIN([PRODUCT (k)]) AS Prod
    FROM YourTable GROUP BY [PRODUCT (k)]) B ON A.[PRODUCT (k)] = B.Prod


    --
    Keith

    "jerome" <com> wrote in message news:04a801c36338$2462d0c0$gbl... 
    Keith Guest

  3. #3

    Default Re: create YTD and YEAR totals from months

    Is the month a datetime column or is it char? Also, Do you have the
    products in a separate table or is it always, products per year? Here is one
    option with a view; you can also use a Number table/calendar table to
    achieve similar solution. The following assumes the month is a char column,
    uses a derived table with a bunch of nested queries which can be avoided if
    you can encapsulate them in a view.

    SELECT *
    FROM ( SELECT COALESCE(tbl.Product, (
    SELECT TOP 1 t3.Product
    FROM tbl t3
    WHERE RIGHT(t3.mnth, 4) = RIGHT(D.mnth, 4)
    AND LEFT(t3.mnth, 2) < LEFT(D.mnth, 2)
    ORDER BY LEFT(t3.mnth, 2)) ),
    D.mnth, COALESCE(qty, 0),
    ( SELECT SUM(t1.qty)
    FROM tbl t1
    WHERE t1.Product = COALESCE(tbl.Product,
    ( SELECT TOP 1 t3.Product
    FROM tbl t3
    WHERE RIGHT(t3.mnth, 4) = RIGHT(D.mnth, 4)
    AND LEFT(t3.mnth, 2) < LEFT(D.mnth, 2)
    ORDER BY LEFT(t3.mnth, 2)) )
    AND LEFT(t1.mnth, 2) <= LEFT(D.mnth, 2)
    AND RIGHT(t1.Mnth, 4) = RIGHT(D.mnth, 4)),
    ( SELECT SUM(t2.qty)
    FROM tbl t2
    WHERE t2.Product = COALESCE(tbl.Product,
    ( SELECT TOP 1 t3.Product
    FROM tbl t3
    WHERE RIGHT(t3.mnth, 4) = RIGHT(D.mnth, 4)
    ORDER BY LEFT(t3.mnth, 2)) )
    AND RIGHT(t2.Mnth, 4) = RIGHT(D.mnth, 4))
    FROM tbl
    RIGHT OUTER JOIN (
    SELECT Months.mnth + Years.yr
    FROM ( SELECT '01' UNION SELECT '02' ...'12'
    ) Months (mnth)
    CROSS JOIN ( SELECT '1999' UNION SELECT '2000' ...'2002'
    ) Years(yr)
    ) D (mnth)
    ON LEFT(tbl.mnth, 2) = LEFT(D.mnth, 2)
    AND RIGHT(tbl.Mnth, 4) = RIGHT(D.mnth, 4)
    ) D2 ( Product, mnth, qty, ytd_qty, qty_yr )
    WHERE Product IS NOT NULL ;

    The Product IS NULL in the WHERE clause is a workaround to avoid the rows
    with products with no qty in the first month of an year. If this is not
    desirable, you can use COALESCE(v1.Product, 0) in your query.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  4. #4

    Default Re: create YTD and YEAR totals from months

    months is a datetime column. I have an month/year table
    YEARMONTH if it make it easier:

    Month Month_end Year Year_end
    01-01-2001 31-01-2001 01-01-2001 31-12-2001
    ....

    The products are in a separate table, PRODUCT,

    Product ProductName
    100 Myproduct
    ....

    but only those products/periods with a year values are
    interesting.

    Will this make the sql code more easy ? My sql skills are
    not very big...

    /J
     
    you have the 
    per year? Here is one 
    table/calendar table to 
    is a char column, 
    can be avoided if 
    4) 
    (tbl.Product, 
    RIGHT(D.mnth, 4) 
    LEFT(D.mnth, 2) 
    2)) ) 
    2) 
    (D.mnth, 4)), 
    (tbl.Product, 
    = RIGHT(D.mnth, 4) 
    2)) ) 
    (D.mnth, 4)) 
    SELECT '02' ...'12' 
    SELECT '2000' ...'2002' 
    to avoid the rows 
    If this is not 
    query. 
    jerome Guest

Similar Threads

  1. 5803 will run one year or 6 months ?
    By S B JAZBEC in forum Windows Vista
    Replies: 24
    Last Post: March 12th, 05:40 PM
  2. ASP totals and sub-totals from an Access or SQL DB
    By Laphan in forum ASP Database
    Replies: 3
    Last Post: April 2nd, 08:15 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139