|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
need help with crosstab queryi 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 On 25 Nov 2005 07:32:01 -0800, William wrote:
Show quote >hey all, Hi William,> >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 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)
Show quote
> Hi William, hey Hugo> > 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) 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 On 28 Nov 2005 04:03:30 -0800, William wrote:
(snip) >is there an other way to change the startdate and enddate. becose now Hi William,>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.. 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) 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 Hugo Kornelis wrote: Show quote > 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  |
|||||||||||||||||||||||
Other interesting topics