Use a loop to create a view?

[Phinnegan]

Hi,
I'm trying to create particular view to help me with some reporting. My main table looks like this:

ID, DateUpdated, Status.

Status can have a value of 1, 2, 3 or 4.

I want to create a view that will show me what the state of the database was on each date. It should look like this:

Date, Status1, Status2, Status3, Status4

Where each of the status columns are the count of records with that status, and that have an UpdatedDate less than or equal to the Date field.

The query I have so far looks like this:

declare @update as datetime
declare date_cursor insensitive cursor for
select dbo.vwDates.dates from dbo.vwDates
open date_cursor
fetch next from date_cursor into @update
while (@@fetch_status <> -1)
begin
select count(*) from dbo.tblDts where Status = 1 and UpdateDate < @update
end
close date_cursor
deallocate date_cursor

I'm not sure if using a cursor is the correct thing to do, but it's all I could think of. All my resources get eaten up when I run the query in query analyser, and nothing gets displayed.

Any help on how to do this is much appreciated.

Thank you,
Phin

 

[Phinnegan]

Edited to add a couple of things:
In the SQL statement, I forgot the &quot;fetch next&quot; before the end loop. But either way, I do not seem to be able to create the view using the cursor. Any other ideas??

Thank you very much,
Phin

 

[sunila7]

Hi,

Try this select stmt... Does it help

SELECT convert(char(8),[Date],101) [Date],
Sum((CASE Status WHEN 1 THEN 1 END)) Status1,Sum((CASE Status WHEN 2 THEN 1 END)) Status2,
Sum((CASE Status WHEN 3 THEN 1 END)) Status3,Sum((CASE Status WHEN 4 THEN 1 END)) Status4
From tblDts
Group by convert(char(8),[Date],101)


Sunil

 

[SQLSister]

Don't use a cursor! You don't need to loop, a case statement and the sum function work here.
select DateUpdated,Sum(Case
When Status = 1 then 1
Else 0
End) As 'Status 1',
Sum(Case
When Status = 2 then 1
Else 0
End) As 'Status 2',
Sum(Case
When Status = 3 then 1
Else 0
End) As 'Status 3',
Sum(Case
When Status = 4 then 1
Else 0
End) As 'Status 4'
From Table2
Group By DAteUpdated

 

[Phinnegan]

Thank you to both sunila7 and SQLSister.

Your replies were extremely helpful, you have my appreciation.

Phin