need help with crosstab query  
Author Message
CindyE





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: 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)