Different Layout - 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)

Different Layout

Author

Topic

FernandoLorival
Starting Member

19 Posts

Posted - 2008-10-06 : 10:57:25

Hi all,

I have a query that is giving me this:

Cell -- Shift -- Duration(min)
Loop1 --- 1 -- 1
Loop1 --- 2 -- 98
Loop1 --- 3 -- 14
Loop2 --- 1 -- 20
Loop2 --- 2 -- 37
Loop2 --- 3 -- 18
Loop3 --- 1 -- 141
Loop3 --- 2 -- 97
Loop3 --- 3 -- 137
Press 1 - 2 -- 3
Press 1 - 3 -- 9
Press 3 - 1 -- 97
Press 3 - 2 -- 100
Press 3 - 3 -- 62

In order to create a graphic I need something like this:

Cell -- Shift1 -- Shift2 --- Shift 3
Loop1 --- 1 --- 98 --- 14
Loop2 --- 20 --- 37 --- 18
etc
etc

Here's my initial query:

SELECT [tbl_Cells].[Cell_Description] as [Cell],
[Shift],
(SUM(Duration)/60) as [Downtime]
FROM [Ontegra-Production].[dbo].[tbl_Machine_Events_History]
JOIN tbl_Events ON [tbl_Machine_Events_History].[Event_Id] = [tbl_Events].[Event_Id]
JOIN tbl_Departments ON [tbl_Machine_Events_History].[Department_Id] = [tbl_Departments].[department_Id]
JOIN tbl_Cells ON [tbl_Machine_Events_History].[Cell_Number] = [tbl_Cells].[Cell_Number]
WHERE ([tbl_Cells].[Department_Id] = [tbl_Departments].[department_Id])
AND [tbl_Departments].[Department_Id] = '2'
AND [tbl_Machine_Events_History].[Event_Id] = '10'
AND (CONVERT(Varchar,[EventStart],101) >= '10/03/2008' AND CONVERT(Varchar,[EventEnd],101) <= '10/03/2008')
GROUP BY [tbl_Cells].[Cell_Description],CONVERT(Varchar,[EventStart],101),[Shift]
ORDER BY CONVERT(Varchar,[EventStart],101)

Thank you all!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 11:00:52

[code]SELECT Cell,
MAX(CASE WHEN Shift=1 THEN Duration ELSE NULL END) AS Shift1,
MAX(CASE WHEN Shift=2 THEN Duration ELSE NULL END) AS Shift2,
MAX(CASE WHEN Shift=3 THEN Duration ELSE NULL END) AS Shift3
FROM YourTable
GROUP BY Cell[/code]

FernandoLorival
Starting Member

19 Posts

Posted - 2008-10-06 : 11:21:24

Thank you visakh16,
I have tried that but I get different values...
Maybe the problem lies with the group by...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 11:27:43

You can't MAX over SUM, two aggregates

SELECT		Cell,
		MAX(CASE WHEN Shift = 1 THEN Downtime ELSE NULL END) AS Shift1,
		MAX(CASE WHEN Shift = 2 THEN Downtime ELSE NULL END) AS Shift2,
		MAX(CASE WHEN Shift = 3 THEN Downtime ELSE NULL END) AS Shift3
FROM		(
			SELECT		c.[Cell_Description] as [Cell],
					{meh | e | d | c}.[Shift],
					SUM({meh | e | d | c}.Duration) / 60.0E0 as [Downtime]
			FROM		[Ontegra-Production].[dbo].[tbl_Machine_Events_History] AS meh
			INNER JOIN	tbl_Events AS e ON e.[Event_Id] = meh.[Event_Id]
			INNER JOIN	tbl_Departments AS d ON d.[department_Id] = meh.[Department_Id] 
						AND d.[Department_Id] = '2'
			INNER JOIN	tbl_Cells AS c ON c.[Cell_Number] = meh.[Cell_Number]
						AND c.[Department_Id] = d.[department_Id]
			WHERE		meh.[Event_Id] = '10' 
					AND {meh | e | d | c}.[EventStart] >= '20081003'
					AND {meh | e | d | c}.[EventEnd] < '20081004'
			GROUP BY	c.[Cell_Description],
					{meh | e | d | c}.[Shift]
		) AS d
GROUP BY	Cell
ORDER BY	Cell

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 11:31:52

Or the SQL Server 2005 PIVOT approach

SELECT		p.Cell,
		p.[1] AS Shift1,
		p.[2] AS Shift2,
		p.[3] AS Shift3
FROM		(
			SELECT		c.[Cell_Description] as [Cell],
					{meh | e | d | c}.[Shift],
					SUM({meh | e | d | c}.Duration) / 60.0E0 as [Downtime]
			FROM		[Ontegra-Production].[dbo].[tbl_Machine_Events_History] AS meh
			INNER JOIN	tbl_Events AS e ON e.[Event_Id] = meh.[Event_Id]
			INNER JOIN	tbl_Departments AS d ON d.[department_Id] = meh.[Department_Id] 
						AND d.[Department_Id] = '2'
			INNER JOIN	tbl_Cells AS c ON c.[Cell_Number] = meh.[Cell_Number]
						AND c.[Department_Id] = d.[department_Id]
			WHERE		meh.[Event_Id] = '10' 
					AND {meh | e | d | c}.[EventStart] >= '20081003'
					AND {meh | e | d | c}.[EventEnd] < '20081004'
			GROUP BY	c.[Cell_Description],
					{meh | e | d | c}.[Shift]
		) AS d
PIVOT		(
			MAX(d.Downtime)
			FOR d.Shift IN ([1], [2], [3])
		) AS p
ORDER BY	p.Cell

E 12°55'05.63"
N 56°04'39.26"

FernandoLorival
Starting Member

19 Posts

Posted - 2008-10-06 : 11:53:23

Hi Guys,
I have tried the PIVOT approach that Peso sugested and it works perfectly.
Thank you all and thank you PESO!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources