Quarterly and Yearly sums (SQL2K)

# Thread: Quarterly and Yearly sums (SQL2K)

1. Member
Join Date
Dec 1969
Posts
70

## Quarterly and Yearly sums (SQL2K)

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Can I say BLECH?

A *much* easier--and I think much faster--way to generate EOM, EOQ, and EOY totals is something like this:<BR><BR>SELECT ..., ...,<BR> Sum( CASE WHEN Month(dates)=Month(getDate()) THEN Pax Else 0 END) AS EOM,<BR> Sum( CASE WHEN Month(dates) BETEEN Month(getDate())-2 AND Month(getDate()) THEN Pax Else 0 END) AS EOQ,<BR> Sum( CASE WHEN Month(dates) BETEEN Month(getDate())-11 AND Month(getDate()) THEN Pax Else 0 END) AS EOY<BR>FROM table<BR>WHERE ...<BR>GROUP BY ...<BR><BR>I believe that will do it all in one operation.<BR><BR>If you make this a SQL stored proc, you&#039;ll get better speed if you pre-build variables to hold the Month(getDate()), et al.<BR><BR>Set @QStart = Month(getDate())-2<BR>Set @YStart = Month(getDate())-11<BR>Set @CurMonth = Month(getDate())<BR><BR>And then<BR><BR>SELECT ..., ...,<BR> Sum( CASE WHEN Month(dates)=@CurMonth THEN Pax Else 0 END) AS EOM,<BR> Sum( CASE WHEN Month(dates) BETEEN @QStart AND @CurMonth THEN Pax Else 0 END) AS EOQ,<BR> Sum( CASE WHEN Month(dates) BETEEN @YStart AND @CurMonth THEN Pax Else 0 END) AS EOY<BR>FROM table<BR>WHERE ...<BR>GROUP BY ...<BR><BR>********<BR><BR>Truth to tell, untested off top of my head. But give it a try.<BR><BR><BR><BR>

3. Member
Join Date
Dec 1969
Posts
70

## RE: Can I say BLECH?

I want running totals. I do not have any input parameters. It is a straight list of running QTR and YR totals specifcally for reporting purposes. The output will be stored in a table specifically used for reporting.<BR><BR>Does that make the situation any different?

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Dunno what you mean...

...by running totals.<BR><BR>And there&#039;s no input parameters in what I showed. It just uses MONTH( GETDATE() ) to get data relative to the current month.<BR><BR>Hmmm...stored in another table. No, I guess not.<BR><BR>If you run this query tomorrow, then even though the month hasn&#039;t changed you&#039;d get all new data if there had been any new records added (either tomorrow&#039;s new data or changes to existing data or historical data newly added). But, then, I don&#039;t see how your query accounts for that kind of change.<BR><BR>If you run this query once a month, though, and get the info for the *PRIOR* month (e.g., just use Month(getDate())-1 as the basis for which month&#039;s data you will get) and add that to a by-the-months table...yeah, it works. So long as nobody goes and changes any existing data.<BR><BR>

5. Member
Join Date
Dec 1969
Posts
70

## RE: Dunno what you mean...

Indeed it is much faster, but I am not getting the right results.<BR><BR>Dates CID RID SID PID CtID MTH QTR YE<BR>2003-02-01 5 8 1 1 1 5 5 5 <BR>2003-01-01 5 8 1 1 1 7 12 12<BR>2003-03-01 5 8 1 1 1 9 21 21<BR>2003-04-01 5 8 1 1 1 3 19 24<BR><BR>So this is the results I would like to get:<BR>MTH is just a straight &#039;get the pax number for that month&#039;<BR>QTR in this case is the sum of the month for a record + previous two months<BR>YE is the sum for a record of that month plus the previous 11 months (can go across years).<BR><BR>So basically i am not interested in the current month i suppose.<BR><BR>To make the comparison I would have to join the table to itself right?<BR><BR>So, I was thinking along the lines of this:<BR><BR>SELECT A.dates, A.categoryid, A.reasonid, A.stateid, A.portid, A.countryid,<BR> min(A.Pax) AS MTH, <BR> Sum( CASE WHEN Month(B.dates) BETWEEN month(A.dates)-2 AND month(A.dates) THEN A.Pax ELSE NULL END) AS QTR, <BR> Sum( CASE WHEN Month(B.dates) BETWEEN month(A.Dates)-11 AND month(A.dates) THEN A.Pax ELSE NULL END) AS YE <BR>FROM tblOADArrival_test A, tblOADArrival_test B<BR>WHERE<BR> A.categoryid= 5 and <BR> A.reasonid = 8 and<BR> A.stateid in (1,2,3,10) and<BR> A.portid in (1,2,3,4,11) and<BR> A.countryid between 1 and 71 <BR>GROUP BY A.dates, A.categoryid, A.reasonid, A.stateid, A.portid, A.countryid<BR>ORDER BY A.dates, A.categoryid, A.reasonid, A.stateid, A.portid, A.countryid<BR><BR>but...........it&#039;s not right......

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## Okay, one more try...

First of all, none of this is going to work!<BR><BR>Because *NO WHERE HERE* do you take into account the *years*!<BR><BR>So, for example, when you get to (say) FEB 2004, Month(FEB 2004) is 2, and Month()-2 is 0, and Month()-11 is -9 !!<BR><BR>Needless to say, there aren&#039;t any months numbered either 0 or -9!<BR><BR>Mea culpa that I didn&#039;t catch that, too!<BR><BR>***********<BR><BR>So you want running totals for *EACH* month???<BR><BR>Then you need to JOIN to a table with nothing *BUT* each month!<BR><BR>You could use a separate table, but you could also simply do:<BR><BR>SELECT DISTINCT Year(Dates) AS Yr, Month(Dates) AS Mn<BR>FROM yourtable<BR><BR>But even better would probably be:<BR><BR>SELECT DISTINCT CONVERT(DateTime, CONVERT(VarChar,Year(Dates)) + &#039;-&#039; + CONVERT(VarChar,Month(Dates)) + &#039;-1&#039;) AS monthStart<BR>FROM yourtable<BR><BR>So that will get you a list of the 1st day of every month that is represented in your table. Convert that into a VIEW, for convenience. Say one called "MonthStarts".<BR><BR>*NOW* you need to JOIN *THAT* with the main table.<BR><BR>But you&#039;ve completely lost me with you <BR> Min(A.Pax) AS MTH <BR><BR>HUH??? That will simply get the smallest value of PAX from the A table--no matter what month it comes from!--and call it MTH.<BR><BR>

#### Posting Permissions

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