Cross Tab Query Help!

R

rguarnieri

Hi!, I'm trying to make a cross tab query in sql server, I know how to
do it, but I need to improve the performance of my query

I need to visualize the information of a different way:

My Query:

Date Standard Actual Output
2006/10 5 8 5
2006/11 7 3 7
2006/12 9 2 4

I need to visualize it this way:

2006/10 2006/11 2006/12
Standard 5 7 9
Actual 8 3 2
Output 5 7 4

Thank you so much.
 
D

Dale Fye

It depends on what your raw data looks like. Can you give us an example of
your data or the SQL of the query that generates the first data set you show
below?

Are you doing this in Access against a linked SQL Server table, or are you
creating a pass-thru query to SQL Server?
 
R

rguarnieri

I'm working with vb.net 2005 and sql server 2000

This is my first query:

SELECT CONVERT(varchar, Year(Dte)) + ' M' + RIGHT('0' +
CONVERT(varchar, MONTH(Dte)), 2) As [Date Range],
(Round(((CASE WHEN Sum(TotStdMin) <= 0 THEN 0 ELSE (Sum(GrossQty) /
Sum(TotStdMin)) END) * Sum(TotMin)) , 0) / 1000) as [Output],
((Sum(ProdQty - (CASE WHEN QaRej IS NULL THEN 0 ELSE QaRej END) )) /
1000) as [Actual]
FROM dbo.ViwOEE_Source LEFT OUTER JOIN (SELECT SeqNum as Seq, MachNum
as Mach, SUM(QaRej) AS QaRej
FROM dbo.ViwOEE_Rej3
WHERE (DateCode BETWEEN CAST('20060713' AS smalldatetime)
AND CAST('20061013' AS smalldatetime))
GROUP BY SeqNum, MachNum) Rej3
ON dbo.ViwOEE_Source.SeqNum = Rej3.Seq
AND dbo.ViwOEE_Source.MachNum = Rej3.Mach
WHERE Dte BETWEEN CAST('20060713' AS smalldatetime)
AND CAST('20061013' AS smalldatetime)
AND MachNum in (Select PkSubGrp
From dbo.ViwP_MchGrpDetail
Where((McTypNum = 2)
AND PKMcGrp in (14, 15, 16, 17, 18, 19, 21)))
GROUP BY CONVERT(varchar, Year(Dte)) + ' M' + RIGHT('0' +
CONVERT(varchar, MONTH(Dte)), 2)
ORDER BY [Date Range]

this is the result of my first query:

Date Output Actual
2006 M07 1265.414 955.90999999999997
2006 M08 2512.2759999999998 1878.3620000000001
2006 M09 2413.9409999999998 1837.5820000000001
2006 M10 975.49300000000005 748.17499999999995

the only way that I found is to make one query for each column
(Output, Actual, etc) and make a sum for each date, but I think that
there is another way to do this.

SELECT 'Output At Standard' as 'Values',
Sum(CASE WHEN [Date Range] = '2006 M07' THEN [Output] ELSE NULL END)
as [2006 M07],
Sum(CASE WHEN [Date Range] = '2006 M08' THEN [Output] ELSE NULL END)
as [2006 M08],
Sum(CASE WHEN [Date Range] = '2006 M09' THEN [Output] ELSE NULL END)
as [2006 M09],
Sum(CASE WHEN [Date Range] = '2006 M10' THEN [Output] ELSE NULL END)
as [2006 M10]
FROM (SELECT CONVERT(varchar, Year(Dte)) + ' M' + RIGHT('0' +
CONVERT(varchar, MONTH(Dte)), 2) As [Date Range], (Round(((CASE WHEN
Sum(TotStdMin) <= 0 THEN 0 ELSE (Sum(GrossQty) / Sum(TotStdMin)) END)
* Sum(TotMin)) , 0) / 1000) as [Output]
FROM dbo.ViwOEE_Source LEFT OUTER JOIN (SELECT SeqNum as Seq, MachNum
as Mach, SUM(QaRej) AS QaRej FROM dbo.ViwOEE_Rej3 WHERE (DateCode
BETWEEN CAST('20060713' AS smalldatetime) AND CAST('20061013' AS
smalldatetime)) GROUP BY SeqNum, MachNum) Rej3 ON
dbo.ViwOEE_Source.SeqNum = Rej3.Seq AND dbo.ViwOEE_Source.MachNum =
Rej3.Mach
WHERE Dte BETWEEN CAST('20060713' AS smalldatetime) AND
CAST('20061013' AS smalldatetime) AND MachNum in (Select PkSubGrp From
dbo.ViwP_MchGrpDetail Where((McTypNum = 2) AND PKMcGrp in (14, 15, 16,
17, 18, 19, 21))) GROUP BY CONVERT(varchar, Year(Dte)) + ' M' +
RIGHT('0' + CONVERT(varchar, MONTH(Dte)), 2)) as OEECrossTab
UNION
SELECT 'Output Actual' as 'Values',
Sum(CASE WHEN [Date Range] = '2006 M07' THEN [Actual] ELSE NULL END)
as [2006 M07],
Sum(CASE WHEN [Date Range] = '2006 M08' THEN [Actual] ELSE NULL END)
as [2006 M08],
Sum(CASE WHEN [Date Range] = '2006 M09' THEN [Actual] ELSE NULL END)
as [2006 M09],
Sum(CASE WHEN [Date Range] = '2006 M10' THEN [Actual] ELSE NULL END)
as [2006 M10]
FROM (SELECT CONVERT(varchar, Year(Dte)) + ' M' + RIGHT('0' +
CONVERT(varchar, MONTH(Dte)), 2) As [Date Range], ((Sum(ProdQty -
(CASE WHEN QaRej IS NULL THEN 0 ELSE QaRej END) )) / 1000) as
[Actual]
FROM dbo.ViwOEE_Source LEFT OUTER JOIN (SELECT SeqNum as Seq, MachNum
as Mach, SUM(QaRej) AS QaRej FROM dbo.ViwOEE_Rej3 WHERE (DateCode
BETWEEN CAST('20060713' AS smalldatetime) AND CAST('20061013' AS
smalldatetime)) GROUP BY SeqNum, MachNum) Rej3 ON
dbo.ViwOEE_Source.SeqNum = Rej3.Seq AND dbo.ViwOEE_Source.MachNum =
Rej3.Mach WHERE Dte BETWEEN CAST('20060713' AS smalldatetime) AND
CAST('20061013' AS smalldatetime) AND MachNum in (Select PkSubGrp From
dbo.ViwP_MchGrpDetail Where((McTypNum = 2) AND PKMcGrp in (14, 15, 16,
17, 18, 19, 21))) GROUP BY CONVERT(varchar, Year(Dte)) + ' M' +
RIGHT('0' + CONVERT(varchar, MONTH(Dte)), 2)) as OEECrossTab

Thank you.
 
D

Dale Fye

Have you tried a SQL Server news group?

I know that SS2K doesn't do "Pivot Tables", the way that Access does, and my
SS background is limited. Sorry I couldn't be more help.

Dale
--
Email address is not valid.
Please reply to newsgroup only.


rguarnieri said:
I'm working with vb.net 2005 and sql server 2000

This is my first query:

SELECT CONVERT(varchar, Year(Dte)) + ' M' + RIGHT('0' +
CONVERT(varchar, MONTH(Dte)), 2) As [Date Range],
(Round(((CASE WHEN Sum(TotStdMin) <= 0 THEN 0 ELSE (Sum(GrossQty) /
Sum(TotStdMin)) END) * Sum(TotMin)) , 0) / 1000) as [Output],
((Sum(ProdQty - (CASE WHEN QaRej IS NULL THEN 0 ELSE QaRej END) )) /
1000) as [Actual]
FROM dbo.ViwOEE_Source LEFT OUTER JOIN (SELECT SeqNum as Seq, MachNum
as Mach, SUM(QaRej) AS QaRej
FROM dbo.ViwOEE_Rej3
WHERE (DateCode BETWEEN CAST('20060713' AS smalldatetime)
AND CAST('20061013' AS smalldatetime))
GROUP BY SeqNum, MachNum) Rej3
ON dbo.ViwOEE_Source.SeqNum = Rej3.Seq
AND dbo.ViwOEE_Source.MachNum = Rej3.Mach
WHERE Dte BETWEEN CAST('20060713' AS smalldatetime)
AND CAST('20061013' AS smalldatetime)
AND MachNum in (Select PkSubGrp
From dbo.ViwP_MchGrpDetail
Where((McTypNum = 2)
AND PKMcGrp in (14, 15, 16, 17, 18, 19, 21)))
GROUP BY CONVERT(varchar, Year(Dte)) + ' M' + RIGHT('0' +
CONVERT(varchar, MONTH(Dte)), 2)
ORDER BY [Date Range]

this is the result of my first query:

Date Output Actual
2006 M07 1265.414 955.90999999999997
2006 M08 2512.2759999999998 1878.3620000000001
2006 M09 2413.9409999999998 1837.5820000000001
2006 M10 975.49300000000005 748.17499999999995

the only way that I found is to make one query for each column
(Output, Actual, etc) and make a sum for each date, but I think that
there is another way to do this.

SELECT 'Output At Standard' as 'Values',
Sum(CASE WHEN [Date Range] = '2006 M07' THEN [Output] ELSE NULL END)
as [2006 M07],
Sum(CASE WHEN [Date Range] = '2006 M08' THEN [Output] ELSE NULL END)
as [2006 M08],
Sum(CASE WHEN [Date Range] = '2006 M09' THEN [Output] ELSE NULL END)
as [2006 M09],
Sum(CASE WHEN [Date Range] = '2006 M10' THEN [Output] ELSE NULL END)
as [2006 M10]
FROM (SELECT CONVERT(varchar, Year(Dte)) + ' M' + RIGHT('0' +
CONVERT(varchar, MONTH(Dte)), 2) As [Date Range], (Round(((CASE WHEN
Sum(TotStdMin) <= 0 THEN 0 ELSE (Sum(GrossQty) / Sum(TotStdMin)) END)
* Sum(TotMin)) , 0) / 1000) as [Output]
FROM dbo.ViwOEE_Source LEFT OUTER JOIN (SELECT SeqNum as Seq, MachNum
as Mach, SUM(QaRej) AS QaRej FROM dbo.ViwOEE_Rej3 WHERE (DateCode
BETWEEN CAST('20060713' AS smalldatetime) AND CAST('20061013' AS
smalldatetime)) GROUP BY SeqNum, MachNum) Rej3 ON
dbo.ViwOEE_Source.SeqNum = Rej3.Seq AND dbo.ViwOEE_Source.MachNum =
Rej3.Mach
WHERE Dte BETWEEN CAST('20060713' AS smalldatetime) AND
CAST('20061013' AS smalldatetime) AND MachNum in (Select PkSubGrp From
dbo.ViwP_MchGrpDetail Where((McTypNum = 2) AND PKMcGrp in (14, 15, 16,
17, 18, 19, 21))) GROUP BY CONVERT(varchar, Year(Dte)) + ' M' +
RIGHT('0' + CONVERT(varchar, MONTH(Dte)), 2)) as OEECrossTab
UNION
SELECT 'Output Actual' as 'Values',
Sum(CASE WHEN [Date Range] = '2006 M07' THEN [Actual] ELSE NULL END)
as [2006 M07],
Sum(CASE WHEN [Date Range] = '2006 M08' THEN [Actual] ELSE NULL END)
as [2006 M08],
Sum(CASE WHEN [Date Range] = '2006 M09' THEN [Actual] ELSE NULL END)
as [2006 M09],
Sum(CASE WHEN [Date Range] = '2006 M10' THEN [Actual] ELSE NULL END)
as [2006 M10]
FROM (SELECT CONVERT(varchar, Year(Dte)) + ' M' + RIGHT('0' +
CONVERT(varchar, MONTH(Dte)), 2) As [Date Range], ((Sum(ProdQty -
(CASE WHEN QaRej IS NULL THEN 0 ELSE QaRej END) )) / 1000) as
[Actual]
FROM dbo.ViwOEE_Source LEFT OUTER JOIN (SELECT SeqNum as Seq, MachNum
as Mach, SUM(QaRej) AS QaRej FROM dbo.ViwOEE_Rej3 WHERE (DateCode
BETWEEN CAST('20060713' AS smalldatetime) AND CAST('20061013' AS
smalldatetime)) GROUP BY SeqNum, MachNum) Rej3 ON
dbo.ViwOEE_Source.SeqNum = Rej3.Seq AND dbo.ViwOEE_Source.MachNum =
Rej3.Mach WHERE Dte BETWEEN CAST('20060713' AS smalldatetime) AND
CAST('20061013' AS smalldatetime) AND MachNum in (Select PkSubGrp From
dbo.ViwP_MchGrpDetail Where((McTypNum = 2) AND PKMcGrp in (14, 15, 16,
17, 18, 19, 21))) GROUP BY CONVERT(varchar, Year(Dte)) + ' M' +
RIGHT('0' + CONVERT(varchar, MONTH(Dte)), 2)) as OEECrossTab

Thank you.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding rota 0
Finding the right style field in VBA 0
How to build subquery on aggregate query 0
Arriving at totals 2
Paragraph numbering in Word 1
Subquery not integrating to query to display previous sum of quantities 3
Keep unique values 1
Build Crosstab query from existing query 5

Top