2-part question: Totals and Dates

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

General SQL Server Forums

New to SQL Server Programming

2-part question: Totals and Dates

Author

Topic

Jason2112
Starting Member

17 Posts

Posted - 2009-03-27 : 19:05:20

So I've been thrown under the bus (so to speak) to finish a project that a former consultant started - but I really don't know anything about SQL code and I only have 3 days to finish it

I can work my way through a GUI pretty well, so here's what I've got:

I created a view (derived from 4 tables) that contains SalesRepNo, CustomerNo, PostingDate, ItemNo, DollarsSold, QuanShipped. I need to provide the DollarsSold and QuanShipped amounts for each ItemNo by CustomerNo by SalesRep in the following formats:
- MTD (which is really the entire previous month)
- MTD LY (entire previous month of last year)
- YTD (01/01/09 through last day of previous month)
- YTD LY (01/01/08 through last day of previous month for last year)

So the layout will need to look like this:
SalesRepNo | CustomerNo | ItemNo | MTDSales | MTDLYSales | %Diff | MTDQuan | MTDLYQuan | %Diff

The same format for a 2nd report but for YTD info.

I've spent all day researching into options. Stored Procedures, Case statements, etc. I'm not sure where to start. This is on a SQL 2005 server, the data will end up in an Excel spreadsheet or Crystal Report. Any help is greatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-28 : 03:14:13

for MTD use


SELECT SalesRepNo, CustomerNo, ItemNo,
MTDSales,MTDLYSales,(MTDSales-MTDLYSales)*100.0 /MTDLYSales AS [%DiffSales],
MTDQuan, MTDLYQuan,(MTDQuan-MTDLYQuan)*100.0 /MTDLYQuan AS [%DiffQuan]
FROM
(
SELECT SalesRepNo, CustomerNo, ItemNo,
SUM(CASE WHEN PostingDate>= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)
AND PostingDate< DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) THEN DollarsSold ELSE 0 END) AS MTDSales,
SUM(CASE WHEN PostingDate>= DATEADD(yy,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0))
AND PostingDate<  DATEADD(yy,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) THEN DollarsSold ELSE 0 END) AS MTDLYSales,
SUM(CASE WHEN PostingDate>= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)
AND PostingDate< DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) THEN DollarsSold ELSE 0 END) AS MTDQuan,
SUM(CASE WHEN PostingDate>= DATEADD(yy,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0))
AND PostingDate<  DATEADD(yy,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) THEN DollarsSold ELSE 0 END) AS MTDLYQuan
FROM YourView
GROUP BY SalesRepNo, CustomerNo, ItemNo
)t

and for YTD use


SELECT SalesRepNo, CustomerNo, ItemNo,
YTDSales,YTDLYSales,(YTDSales-YTDLYSales)*100.0 /YTDLYSales AS [%DiffSales],
YTDQuan, YTDLYQuan,(YTDQuan-YTDLYQuan)*100.0 /YTDLYQuan AS [%DiffQuan]
FROM
(
SELECT SalesRepNo, CustomerNo, ItemNo,
SUM(CASE WHEN PostingDate>= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
AND PostingDate< DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) THEN DollarsSold ELSE 0 END) AS YTDSales,
SUM(CASE WHEN PostingDate>=DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)
AND PostingDate<  DATEADD(yy,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) THEN DollarsSold ELSE 0 END) AS YTDLYSales,
SUM(CASE WHEN PostingDate>= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
AND PostingDate< DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) THEN DollarsSold ELSE 0 END) AS YTDQuan,
SUM(CASE WHEN PostingDate>= DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)
AND PostingDate<  DATEADD(yy,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) THEN DollarsSold ELSE 0 END) AS YTDLYQuan
FROM YourView
GROUP BY SalesRepNo, CustomerNo, ItemNo
)t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources