SQL Server Programming - need help with crosstab query

Author

need help with crosstab query

William

2005-11-25, 7:02 pm

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
where (dbo.tblalarm.date between @startdate and @enddate ) and
(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

Hugo Kornelis

2005-11-25, 7:02 pm

On 25 Nov 2005 07:32:01 -0800, William wrote:

>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
>where (dbo.tblalarm.date between @startdate and @enddate ) and
>(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
WHERE [date] BETWEEN @startdate AND @enddate
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

2005-11-28, 7:59 am

> 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
> WHERE [date] BETWEEN @startdate AND @enddate
> 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 Dor

2005-11-28, 7:59 am

Hi,
I've stumbled upon a kick-ass crosstab implementation:
description is on
http://weblogs.sqlteam.com/jeffs/ar...05/02/4842.aspx

updated version is on http://weblogs.sqlteam.com/jeffs/articles/5120.aspx

enjoy

Hugo Kornelis wrote:
> On 25 Nov 2005 07:32:01 -0800, William wrote:
>
>
>
>
>
>
> 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
> WHERE [date] BETWEEN @startdate AND @enddate
> AND type = 'alm'
> GROUP BY statusnumber
>
> (untested - see www.aspfaq.com/5006 if you prefer a tested reply)
>
> Best, Hugo

Hugo Kornelis

2005-11-29, 7:05 pm

On 28 Nov 2005 04:03:30 -0800, William wrote:

(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
(@Startdate datetime,
@Enddate datetime)
AS
SELECT ....
FROM ....
WHERE [date] BETWEEN @Startdate AND @Enddate
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)

Sponsored Links