Hi,

I have a problem here, please help me I am sure it can be done but just
can't figure out how.
The table structure is something like this :

ID           Store_id      Year        Month        Sales
1642         2                 2004         1             1000
1792         2                 2004         2             1100
1978         2                 2004         3             1000
2291         2                 2004         4             1050
2594         2                 2004         5             1030
642          2                  2003         1             1000
792          2                  2003         2             1100
978          2                  2003         3             1000
291          2                  2003         4             1050
594          2                  2003         5             1030
1492         15              2004         1             5400
1692         15              2004         2             2700
1887         15              2004         3             15100
2144         15              2004         4             7300
2521         15              2004         5             3200
492         15               2003         1             5400
692         15               2003         2             2700
887         15               2003         3             15100
144         15               2003         4             7300
521         15               2003         5             3200

The Store table has the store open date, for store 2 open date is 02 jun,
2002 & 15 is 05 jun,2002

the user enters the parameters for Month & Year which are of type INT.
I need to calculate if the following
YTD Prior Year totals - backwards from the month & year entered.eg. If the
user enters Month = 4 & Year = 2004 then I need to check if the sales exist
for 04/2003 then calculate year to date based on complete months i.e. store
2 opened on 02 jun 2002 so I need to caclulate from the next month - July
2002 to April 2003.
YTD Current Year totals - from Jul 2003 to Apr 2004.

Please help, I am not a seasoned sql person.

Thanks

Please post table structures & expected results, so that others can repro
your problem. For details, refer to : www.aspfaq.com/5006

Hi,

Sales Table structure is :
Id    INT
StoreID    INT
Month    INT
Year        INT
Sales        MONEY


Store Table :
StoreID        INT
OpenDate    DATETIME

Thanks


Show quoteHide quote

Vishal (vrajpu***@hotmail.com) writes:
Show quoteHide quoteSELECT S.StoreID, SUM(S.Sales)
FROM   Sales S
JOIN   Stores T ON S.StoreID = T.StoreID
WHERE  (S.Year = @Year AND S.Month <= @Month OR
        S.Year = @Year - 1 AND S.Month > @Month)
   AND (S.Year > year(T.OpenDate) OR
        S.Year = year(T.OpenDate) AND S.Month > month(T.Month)

If this does not meet you request, please post the following:

o  CREATE TABLE statements for your tables.
o  INSERT statements with sample data.
o  The desired result given the sample.

That makes it possible to post a tested solution.

Generally, keep in mind, the less effort you put in to help us to help
you, the less we will be able to help you.

Record locking within a stored procedure
compare '002' and '00²'
Evaluation of conditions
complex and large triggers
Table design question on LARGE table