Re: Cross Tab Query Help!
- From: Dale Fye <dale.fye@xxxxxxxxxx>
- Date: Wed, 31 Jan 2007 05:06:01 -0800
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" wrote:
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.
On Jan 30, 1:35 pm, Dale Fye <dale....@xxxxxxxxxx> wrote:
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?
--
Email address is not valid.
Please reply to newsgroup only.
"rguarnieri" wrote:
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.- Hide quoted text -- Show quoted text -
- References:
- Cross Tab Query Help!
- From: rguarnieri
- Re: Cross Tab Query Help!
- From: rguarnieri
- Cross Tab Query Help!
- Prev by Date: Re: Edit a Querry
- Next by Date: Re: Creating a zero return
- Previous by thread: Re: Cross Tab Query Help!
- Next by thread: Re: How do I ask the user to enter the day of the week to search for?
- Index(es):
Relevant Pages
|
|
