Adding 2 Pivot Counts in Multiples

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)

Adding 2 Pivot Counts in Multiples

Author

Topic

Riaan
Starting Member

4 Posts

Posted - 2009-07-11 : 08:47:29

Dear Experts,

I'm fairly new to the world of Pivots and Group By clauses and am having a challenging time calculating TEUS of containers in our shipment system.

Basically I have relational tables for Shipments and Containers which have been linked in a many-to-many relationship using an intermediate table as follows:

Shipments
ShipmentID
SH_SOB
*

Containers
ContainerID
CO_Type
*

Relate_Shipments_Containers
RSC_ShipmentLink
RSC_ContainerLink

Next, I have a table called ContainerTypes linked to the Containers table via the ContainerType field in a single-many relationship.

ContainerTypes
ContainerTypeID
CT_Description

The ContainerTypes listed are "20ft" and "40ft"

The SH_SOB field in the Shipments table is a date of the shipment and each shipment may contain any number of linked containers each of a different single ContainerType.

What I have so far achieved is a count(*) of the number of "20ft" containers falling within each month of the year 2009 (Based on the SH_SOB Date) using a Pivot Table.

Here is my T-SQL that does this:


SELECT [1] as Jan, [2] as Feb, [3] as Mar, [4] as Apr, [5] as May, [6] as Jun, [7] as Jul,  as Aug, [9] as Sep, [10] as Oct, [11] as Nov, [12] as [Dec]
	 FROM (
		SELECT     Containers.ContainerID, MONTH(Shipments.SH_SOB) AS SHMonth
		FROM         ContainerTypes INNER JOIN
							  Containers INNER JOIN
							  Shipments INNER JOIN
							  Relate_Shipments_Containers ON Shipments.ShipmentID = Relate_Shipments_Containers.RSC_ShipmentLink ON 
							  Containers.ContainerID = Relate_Shipments_Containers.RSC_ContainerLink ON ContainerTypes.ContainerTypeID = Containers.CO_Type_Link
		WHERE     (YEAR(Shipments.SH_SOB) = '2009' AND ContainerTypes.CT_Description like '%20%')
		GROUP BY Containers.ContainerID, Shipments.SH_SOB) pvt 
PIVOT (Count(ContainerID) FOR SHMonth In ([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12])) as child

Now, the same is logical for 40ft.

However!

To get the TEUS of each container type, I need a multiple of 1 for each 20ft container count (Which is basically the count(*) as above) but for the 40ft, I need a multiple of 2 (I.e. Double the count(*))

E.G. (2 x 20ft = 2*1 = 2 TEUS)+(2 x 40ft = 2*2 = 4 TEUS) = 6 TEUS in Total for that month.

How, on this earth, do I get the 20ft count added to the 40ft double-count within the same format as my original pivot table over months???

E.G.
Type Jan Feb Mar etc...
20ft 2 1 7
40ft 4 6 8 (These are double counts of 2, 3 and 4)
-------------------------
Total 6 7 15 (I just need this row at best!!!)

I hope this makes sense...
All help will be appreciated, code even more...

xR

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-12 : 04:56:49

do you mean this?


SELECT  ContainerTypes.CT_Description,
CASE WHEN ContainerTypes.CT_Description='20ft' THEN 1 ELSE 2 END * SUM(CASE WHEN MONTH(Shipments.SH_SOB)=1 THEN 1 ELSE 0 END) AS Jan,
CASE WHEN ContainerTypes.CT_Description='20ft' THEN 1 ELSE 2 END * SUM(CASE WHEN MONTH(Shipments.SH_SOB)=2 THEN 1 ELSE 0 END) AS Feb,
CASE WHEN ContainerTypes.CT_Description='20ft' THEN 1 ELSE 2 END * SUM(CASE WHEN MONTH(Shipments.SH_SOB)=3 THEN 1 ELSE 0 END) AS Mar,
...
CASE WHEN ContainerTypes.CT_Description='20ft' THEN 1 ELSE 2 END * SUM(CASE WHEN MONTH(Shipments.SH_SOB)=12 THEN 1 ELSE 0 END) AS Dec
		FROM         ContainerTypes INNER JOIN
							  Containers INNER JOIN
							  Shipments INNER JOIN
							  Relate_Shipments_Containers ON Shipments.ShipmentID = Relate_Shipments_Containers.RSC_ShipmentLink ON 
							  Containers.ContainerID = Relate_Shipments_Containers.RSC_ContainerLink ON ContainerTypes.ContainerTypeID = Containers.CO_Type_Link
		WHERE     (YEAR(Shipments.SH_SOB) = '2009')
		GROUP BY ContainerTypes.CT_Description

Riaan
Starting Member

4 Posts

Posted - 2009-07-12 : 06:41:36

Working perfectly. Thanks!!

xR

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-12 : 08:26:00

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources