Joining tables for a crosstab query

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

SQL Server Development (2000)

Joining tables for a crosstab query

Author

Topic

vpoko
Starting Member

22 Posts

Posted - 2006-03-09 : 13:09:50

Hello,

I have financial data in roughly the following format:
[Dept*] [Year*] [Month*] [Category*] [Amount]
The * fields together make the composite primary key. Category are things like Base Salary, OT Salary, etc. Amount is a currency.

I need to get these into a crosstab format that looks like this:


       [BaseSalJan] [BaseSalFeb] [BaseSalMar] ... [OTSal Jan]...
[Dept]

The query would be for a specific year so we don't need to worry about pivoting that.

The code I have is:


	SELECT tpay1.DeptID,
	  SUM(CASE tpay1.[Month] WHEN 1 THEN tpay1.Dollars ELSE 0 END) As BaseSalJan,
	  SUM(CASE tpay1.[Month] WHEN 2 THEN tpay1.Dollars ELSE 0 END) As BaseSalFeb,
 ...
	  SUM(CASE tpay2.[Month] WHEN 1 THEN tpay2.Dollars ELSE 0 END) As OTSalJan,
	  SUM(CASE tpay2.[Month] WHEN 2 THEN tpay2.Dollars ELSE 0 END) As OTSalFeb
...
	FROM tblDataPayroll AS tpay1
	LEFT OUTER JOIN tblDataPayroll as tpay2
	  ON tpay1.deptid = tpay2.deptid
	 AND tpay1.[year] = tpay2.[year]
	 AND tpay1.[month] = tpay2.[month]
	 AND tpay1.scenario = tpay2.scenario
	WHERE (tpay1.DeptID = @DeptID OR @DeptID IS NULL)
	  AND tpay1.[Year] = @Year
	  AND tpay1.[Scenario] = @Scenario
	  AND tpay1.[Measure] = 'BaseSal'
      AND (tpay2.[measure] = 'OT' OR tpay2.measure IS NULL)
	GROUP BY tpay1.DeptID
	ORDER BY tpay1.DeptID

The problem is when there are no entries for Cat 2 (OT), it returns a blank row instead of inserting 0's. If I comment out the last WHERE condition (dealing with tpay2.measure) it returns data, but seems to cross-join some fields. Any ideas?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-09 : 13:51:33

Try this:

SELECT	DeptID,
	SUM(CASE WHEN [Month] = 1 and [Measure] = 'BaseSal' THEN Dollars ELSE 0 END) As BaseSalJan,
	SUM(CASE WHEN [Month] = 2 and [Measure] = 'BaseSal' THEN Dollars ELSE 0 END) As BaseSalFeb,
 	...
	SUM(CASE WHEN [Month] = 1 and [Measure] = 'OT' THEN Dollars ELSE 0 END) As OTSalJan,
	SUM(CASE WHEN [Month] = 2 and [Measure] = 'OT' THEN Dollars ELSE 0 END) As OTSalFeb
 	...
FROM	tblDataPayroll
WHERE	(DeptID = @DeptID OR @DeptID IS NULL)
	AND [Year] = @Year
	AND [Scenario] = @Scenario
GROUP BY DeptID
ORDER BY DeptID

vpoko
Starting Member

22 Posts

Posted - 2006-03-09 : 14:05:03

quote:
Originally posted by blindman

Try this:
...

Thank you so much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources