monthly totals horizontal - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

monthly totals horizontal

Author

Topic

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2004-07-23 : 14:55:36

Hey all-
I’ve got some finical data that needs to be summarized in a nice little report. Here’s my problem.

I have a sales table with transactions.
ITEM_NUM
QTY
DOLLAR_TOTAL
SALES_DATE

He would like to see it in a format something like this…

ITEM ONE PREV_MO_TTL CUR_MO_TTL Y2D_TTL
ITEM TWO PREV_MO_TTL CUR_MO_TTL Y2D_TTL
ETC…

How can I do this? I wrote a sp that looks at the transaction table and scans\builds a temp table with a list of items that have been sold. Once I have that list, I start a cursor at the top of the list and compute each total for each item and insert those totals into a temp table.

There has got to be a better way of doing this then using a cursor. Something like this…..
SELECT (SELECT SUM(DOLLAR_TOTAL) FROM TABLE WHERE ITEM_NUM=’ITEM ONE’ AND DATE < THIS MONTH AND DATE > LAST MONTH) AS PREV_MO_TTL???

Any help?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-23 : 15:07:28

No temp tables, no cursors ... PLEASE !!!!


SELECT Item_num,
   SUM(CASE WHEN SalesDate <current month criteria here> THEN Dollar_Total ELSE 0 END) as CurrMonth,
   SUM(CASE WHEN SalesDate <prev month criteria here> THEN Dollar_Total ELSE 0 END) as PrevMonth
FROM
  SalesTransactions
WHERE
  Sales_Date <date range criteria here>
GROUP BY Item_Num

- Jeff