Sales data in monthly columns - 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 2005 Forums

Transact-SQL (2005)

Sales data in monthly columns

Author

Topic

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-01-22 : 08:55:10

I need a query that gives me sales data by Customer.GroupCode in monthly columns as depicted below:
GrpCd JAN FEB MAR APR MAY JUN JUL etc.
Film 5,000
Aero
Elct 3,000 950
Desg
Edu 150 150

I have two database tables -- InvcHead and Customer with the following fields:
InvcHead:
InvoiceNum InvoiceDate InvoiceAmt CustNum

Customer:
CustNum GroupCode

The query below gets me close but it gives me gives me one row for each customer. So if I have 5 customers with the same group code, I get 5 rows for that group code. I need to modify it or come up with a different approach that gives me only one row for each GroupCode.

SELECT distinct
c.Name,
c.GroupCode,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=1) JAN,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=2) FEB,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=3) MAR,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=4) APR,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=5) MAY,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=6) JUN,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=7) JUL,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=8) AUG,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=9) SEP,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=10) OCT,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=11) NOV,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=12) DEC,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate)) TOT
FROM InvcHead i INNER JOIN Customer c ON (i.custnum=c.custnum) AND (i.company=c.company)
WHERE i.invoicedate>='1-1-2007' AND i.invoicedate<'1-1-2008'

Thanks for the help.
AE

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-22 : 09:04:52

check out PIVOT operator
http://msdn2.microsoft.com/en-us/library/ms177410.aspx

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 09:29:34

Until Compatibility Level is set to 90 or higher, try this

SELECT		c.Name,
		c.GroupCode,
		SUM(CASE WHEN MONTH(i.InvoiceDate) = 1 THEN i.InvoiceAmt ELSE 0 END) AS January,
		SUM(CASE WHEN MONTH(i.InvoiceDate) = 2 THEN i.InvoiceAmt ELSE 0 END) AS February,
		SUM(CASE WHEN MONTH(i.InvoiceDate) = 3 THEN i.InvoiceAmt ELSE 0 END) AS March,
		SUM(CASE WHEN MONTH(i.InvoiceDate) = 4 THEN i.InvoiceAmt ELSE 0 END) AS April,
		SUM(CASE WHEN MONTH(i.InvoiceDate) = 5 THEN i.InvoiceAmt ELSE 0 END) AS May,
		SUM(CASE WHEN MONTH(i.InvoiceDate) = 6 THEN i.InvoiceAmt ELSE 0 END) AS June,
		SUM(CASE WHEN MONTH(i.InvoiceDate) = 7 THEN i.InvoiceAmt ELSE 0 END) AS July,
		SUM(CASE WHEN MONTH(i.InvoiceDate) = 8 THEN i.InvoiceAmt ELSE 0 END) AS August,
		SUM(CASE WHEN MONTH(i.InvoiceDate) = 9 THEN i.InvoiceAmt ELSE 0 END) AS September,
		SUM(CASE WHEN MONTH(i.InvoiceDate) = 10 THEN i.InvoiceAmt ELSE 0 END) AS October,
		SUM(CASE WHEN MONTH(i.InvoiceDate) = 11 THEN i.InvoiceAmt ELSE 0 END) AS November,
		SUM(CASE WHEN MONTH(i.InvoiceDate) = 12 THEN i.InvoiceAmt ELSE 0 END) AS December
FROM		InvcHead AS i
INNER JOIN	Customer AS c ON c.CustNum = i.CustNum
WHERE		i.Company = c.Company
		AND i.InvoiceDate >= '20070101'
		AND i.InvoiceDate < '20080101'
GROUP BY	c.Name,
		c.GroupCode
ORDER BY	c.Name,
		c.GroupCode

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-22 : 11:55:03

using PIVOT:-

SELECT m.GroupCode,p.[January] AS JAN,p.[February] AS FEB,...
FROM
(SELECT c.GroupCode,MONTH(i.invoicedate) AS InvoiceMonth,i.invoiceamt
FROM InvcHead i INNER JOIN Customer c ON (i.custnum=c.custnum) AND (i.company=c.company)
WHERE i.invoicedate>='1-1-2007' AND i.invoicedate<'1-1-2008')m
PIVOT (SUM(invoiceamt) FOR InvoiceMonth IN ([January],[February],[March],...,[December]))p

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-01-22 : 13:48:13

Great.
I think this will get me going.
Thank you all so much for your valuable reponses!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources