need help with crosstab query |
|
Author |
Message |
CindyE
|
Posted: Fri Nov 25 15:46:12 CST 2005 |
Top |
SQL Server Developer >> need help with crosstab query
hey all,
i need some help with a crosstab query. been doing this for 3 days but
no luck
i have a table with the following statusnumber, systemnumber
here is the code i have written
ALTER PROCEDURE dbo.Qrytest4
(@startdate datetime,
@enddate datetime)
AS SELECT Distinct (statusnumber), (CASE ( systemnumber) WHEN
'15401267' THEN COUNT(statusnumber) ELSE 0 END) AS T15401267,
(CASE ( systemnumber) WHEN '15401268' THEN
COUNT(statusnumber) ELSE 0 END) AS T15401268,
(CASE ( systemnumber) WHEN '15401269' THEN
COUNT(statusnumber) ELSE 0 END) AS T15401269,
(CASE ( systemnumber) WHEN '15401270' THEN
COUNT(statusnumber) ELSE 0 END) AS T15401270,
(CASE ( systemnumber) WHEN '15401271' THEN
COUNT(statusnumber) ELSE 0 END) AS T15401271,
(CASE ( systemnumber) WHEN '15401272' THEN
COUNT(statusnumber) ELSE 0 END) AS T15401272,
(CASE ( systemnumber) WHEN '15401273' THEN
COUNT(statusnumber) ELSE 0 END) AS T15401273,
(CASE ( systemnumber) WHEN '15401274' THEN
COUNT(statusnumber) ELSE 0 END) AS T15401274,
(CASE ( systemnumber) WHEN '15401275' THEN
COUNT(statusnumber) ELSE 0 END) AS T15401275
FROM dbo.tblalarm
(dbo.tblalarm.type = 'alm')
GROUP BY statusnumber, systemnumber
the result is like this
statusnumber Txxxxxx Txxxxxxxx Txxxxxxxxx
144 3 0 0
144 0 1 0
144 0 0 1
but what i would like this is
statusnumber Txxxxxx Txxxxxxxx Txxxxxxxxx
144 3 1 1
could someone help me with this. i don't know what to do anymore
thnx
SQL Server306
|
|
|
|
|
Hugo
|
Posted: Fri Nov 25 15:46:12 CST 2005 |
Top |
SQL Server Developer >> need help with crosstab query
>hey all,
>
>i need some help with a crosstab query. been doing this for 3 days but
>no luck
>i have a table with the following statusnumber, systemnumber
>here is the code i have written
>
>ALTER PROCEDURE dbo.Qrytest4
>
>AS SELECT Distinct (statusnumber), (CASE ( systemnumber) WHEN
>'15401267' THEN COUNT(statusnumber) ELSE 0 END) AS T15401267,
> (CASE ( systemnumber) WHEN '15401268' THEN
>COUNT(statusnumber) ELSE 0 END) AS T15401268,
> (CASE ( systemnumber) WHEN '15401269' THEN
>COUNT(statusnumber) ELSE 0 END) AS T15401269,
> (CASE ( systemnumber) WHEN '15401270' THEN
>COUNT(statusnumber) ELSE 0 END) AS T15401270,
> (CASE ( systemnumber) WHEN '15401271' THEN
>COUNT(statusnumber) ELSE 0 END) AS T15401271,
> (CASE ( systemnumber) WHEN '15401272' THEN
>COUNT(statusnumber) ELSE 0 END) AS T15401272,
> (CASE ( systemnumber) WHEN '15401273' THEN
>COUNT(statusnumber) ELSE 0 END) AS T15401273,
> (CASE ( systemnumber) WHEN '15401274' THEN
>COUNT(statusnumber) ELSE 0 END) AS T15401274,
> (CASE ( systemnumber) WHEN '15401275' THEN
>COUNT(statusnumber) ELSE 0 END) AS T15401275
>FROM dbo.tblalarm
>(dbo.tblalarm.type = 'alm')
>GROUP BY statusnumber, systemnumber
>
>the result is like this
>
>statusnumber Txxxxxx Txxxxxxxx Txxxxxxxxx
>144 3 0 0
>144 0 1 0
>144 0 0 1
>
>but what i would like this is
>
>statusnumber Txxxxxx Txxxxxxxx Txxxxxxxxx
>144 3 1 1
>
>could someone help me with this. i don't know what to do anymore
Hi William,
Try if this works:
SELECT statusnumber,
COUNT (CASE WHEN systemnumber = '15401267' THEN 1 END) AS
T15401267,
COUNT (CASE WHEN systemnumber = '15401268' THEN 1 END) AS
T15401268,
....
COUNT (CASE WHEN systemnumber = '15401275' THEN 1 END) AS
T15401275
FROM dbo.tblalarm
AND type = 'alm'
GROUP BY statusnumber
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
|
|
|
|
|
William
|
Posted: Mon Nov 28 06:03:30 CST 2005 |
Top |
SQL Server Developer >> need help with crosstab query
> Hi William,
>
> Try if this works:
>
> SELECT statusnumber,
> COUNT (CASE WHEN systemnumber = '15401267' THEN 1 END) AS
> T15401267,
> COUNT (CASE WHEN systemnumber = '15401268' THEN 1 END) AS
> T15401268,
> ....
> COUNT (CASE WHEN systemnumber = '15401275' THEN 1 END) AS
> T15401275
> FROM dbo.tblalarm
> AND type = 'alm'
> GROUP BY statusnumber
>
> (untested - see www.aspfaq.com/5006 if you prefer a tested reply)
>
> Best, Hugo
> -->
> (Remove _NO_ and _SPAM_ to get my e-mail address)
hey Hugo
thanx for your help it works
is there an other way to change the startdate and enddate. becose now
there will be a pop up screen and i have to fill in the dates. but wat
i want is to get the date from a textbox.. i have tried like this but
get error converting datetime from string..
FROM tblalarm
WHERE ((tblalarm.date between Convert(datetime, '
[forms]![frmmonthreport]![txtstartdate]' , 105) AND Convert(datetime,
' [forms]![frmmonthreport]![txtenddate]', 105)) AND (dbo.tblalarm.type
= 'alm'))
GROUP BY statusnumber
i'm new with sql maybe you can help me out again.
best, William
|
|
|
|
|
Uri
|
Posted: Mon Nov 28 06:18:31 CST 2005 |
Top |
SQL Server Developer >> need help with crosstab query
Hi,
I've stumbled upon a kick-ass crosstab implementation:
description is on
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx
updated version is on http://weblogs.sqlteam.com/jeffs/articles/5120.aspx
enjoy
>
>
>>hey all,
>>
>>i need some help with a crosstab query. been doing this for 3 days but
>>no luck
>>i have a table with the following statusnumber, systemnumber
>>here is the code i have written
>>
>>ALTER PROCEDURE dbo.Qrytest4
>>
>>AS SELECT Distinct (statusnumber), (CASE ( systemnumber) WHEN
>>'15401267' THEN COUNT(statusnumber) ELSE 0 END) AS T15401267,
>> (CASE ( systemnumber) WHEN '15401268' THEN
>>COUNT(statusnumber) ELSE 0 END) AS T15401268,
>> (CASE ( systemnumber) WHEN '15401269' THEN
>>COUNT(statusnumber) ELSE 0 END) AS T15401269,
>> (CASE ( systemnumber) WHEN '15401270' THEN
>>COUNT(statusnumber) ELSE 0 END) AS T15401270,
>> (CASE ( systemnumber) WHEN '15401271' THEN
>>COUNT(statusnumber) ELSE 0 END) AS T15401271,
>> (CASE ( systemnumber) WHEN '15401272' THEN
>>COUNT(statusnumber) ELSE 0 END) AS T15401272,
>> (CASE ( systemnumber) WHEN '15401273' THEN
>>COUNT(statusnumber) ELSE 0 END) AS T15401273,
>> (CASE ( systemnumber) WHEN '15401274' THEN
>>COUNT(statusnumber) ELSE 0 END) AS T15401274,
>> (CASE ( systemnumber) WHEN '15401275' THEN
>>COUNT(statusnumber) ELSE 0 END) AS T15401275
>
>>FROM dbo.tblalarm
>
>>(dbo.tblalarm.type = 'alm')
>>GROUP BY statusnumber, systemnumber
>>
>>the result is like this
>>
>>statusnumber Txxxxxx Txxxxxxxx Txxxxxxxxx
>>144 3 0 0
>>144 0 1 0
>>144 0 0 1
>>
>>but what i would like this is
>>
>>statusnumber Txxxxxx Txxxxxxxx Txxxxxxxxx
>>144 3 1 1
>>
>>could someone help me with this. i don't know what to do anymore
>
>
> Hi William,
>
> Try if this works:
>
> SELECT statusnumber,
> COUNT (CASE WHEN systemnumber = '15401267' THEN 1 END) AS
> T15401267,
> COUNT (CASE WHEN systemnumber = '15401268' THEN 1 END) AS
> T15401268,
> ....
> COUNT (CASE WHEN systemnumber = '15401275' THEN 1 END) AS
> T15401275
> FROM dbo.tblalarm
> AND type = 'alm'
> GROUP BY statusnumber
>
> (untested - see www.aspfaq.com/5006 if you prefer a tested reply)
>
> Best, Hugo
|
|
|
|
|
Hugo
|
Posted: Tue Nov 29 16:50:16 CST 2005 |
Top |
SQL Server Developer >> need help with crosstab query
(snip)
>is there an other way to change the startdate and enddate. becose now
>there will be a pop up screen and i have to fill in the dates. but wat
>i want is to get the date from a textbox.. i have tried like this but
>get error converting datetime from string..
Hi William,
SQL Server is back-end only. You'll have to use whatever you currently
use as your front end to get the data from the text box and pass it to
SQL Server.
In SQL Server, I suggest you create a stored procedure that takes the
start date and end date as parameters:
CREATE PROC MyCrosstab
AS
SELECT ....
FROM ....
AND ....
go
You can then call the stored proc with the parameters you extracted from
the text field. How to do that depends on the front-end you use.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
|
|
|
|
|
|
|