How to make while loop result in one table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to make while loop result in one table?

Author

Topic

sssqlserver
Starting Member

6 Posts

Posted - 2011-10-24 : 18:59:09

Hello, I have a DB stored bug info. I want to run a query to get the how many bugs within a period of time. I wrote this query:

DECLARE @dd datetime, @ddd date
SET @dd = '2010-05-05 00:00:00.000'
SET @ddd = @dd

WHILE @dd <> '2011-10-25 00:00:00.000'
BEGIN
SELECT @ddd AS OnDadte, (SELECT count(*)
FROM <DB TABLE NAME>
WHERE pkey like 'nfs12%' AND created <= @dd) AS TotalBugsOnDate,
(SELECT count(*)
FROM <DB TABLE NAME>
WHERE pkey like 'nfs12%' AND created >= DATEADD(DAY, -1,@dd) AND created <= @dd )
AS BugsOpenedOnDate

SET @dd = DATEADD(DAY,1,@dd)
SET @ddd = @dd
END

I got the result but it looks like:
OnDate TotalBugsOnDate BugsOpenedOnDate
1 2010-05-06 66 23

OnDate TotalBugsOnDate BugsOpenedOnDate
1 2010-05-07 69 3

OnDate TotalBugsOnDate BugsOpenedOnDate
1 2010-05-08 71 2
......

I want it looks like:
OnDate TotalBugsOnDate BugsOpenedOnDate
1 2010-05-06 66 23
2 2010-05-07 69 3
3 2010-05-08 71 2

Anyone can help me? Thanks.

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-10-24 : 19:10:34

DO The following Things:

1.create Temporary table before while loop in which you can store the resukted records

2. Now Inside While loop

Insert into [Temporary table](col1,col2,col2,,,so one)
SELECT @ddd AS OnDadte, (SELECT count(*)
FROM <DB TABLE NAME>
WHERE pkey like 'nfs12%' AND created <= @dd) AS TotalBugsOnDate,
(SELECT count(*)
FROM <DB TABLE NAME>
WHERE pkey like 'nfs12%' AND created >= DATEADD(DAY, -1,@dd) AND created <= @dd )
AS BugsOpenedOnDate

3. Outside of the while loop write

select * from [Temporary table]
drop table [Temporary table]

I have given you idea.Please follow the instructions .

Thanks
VIjay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 00:40:59

why should you have a loop at all? wont below suffice?


DECLARE @startdate datetime, @enddate datetime,
SELECT @startdate = '2010-05-05 00:00:00.000',@enddate = '2011-10-25 00:00:00.000'

;With Date_Table (DateVal)
AS
(
SELECT @startdate 
union all
SELECT DATEADD(dd,1,DateVal) 
FROM Date_Table
WHERE DATEADD(dd,1,DateVal)<=@enddate
)
SELECT dt.DateVal,t.TotalBugsOnDate,t.BugsOpenedOnDate
FROM Date_Table dt
CROSS APPLY(SELECT count(case when created <= dt.DateVal then 1 else null end) AS TotalBugsOnDate,
 count(case when created >= DATEADD(DAY, -1,dt.DateVal) AND created <= dt.DateVal then 1 else null end) AS BugsOpenedOnDate
FROM <DB TABLE NAME>
WHERE pkey like 'nfs12%'
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sssqlserver
Starting Member

6 Posts

Posted - 2011-10-25 : 18:01:37

Hi, visakh16,

Thanks for your suggestions. I tried to run your query in SSMS 2008 and get this message:
Msg 8124, Level 16, State 1, Line 5
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

Can you help?

quote:

Originally posted by visakh16

why should you have a loop at all? wont below suffice?


DECLARE @startdate datetime, @enddate datetime
SET @startdate = '2010-05-05 00:00:00.000'
SET @enddate = '2011-10-25 00:00:00.000'

;With Date_Table (DateVal)
AS
(
SELECT @startdate 
union all
SELECT DATEADD(dd,1,DateVal) 
FROM Date_Table
WHERE DATEADD(dd,1,DateVal)<=@enddate
)
SELECT dt.DateVal,t.TotalBugsOnDate,t.BugsOpenedOnDate
FROM Date_Table dt
CROSS APPLY(SELECT count(case when created <= dt.DateVal then 1 else null end) AS TotalBugsOnDate,
 count(case when created >= DATEADD(DAY, -1,dt.DateVal) AND created <= dt.DateVal then 1 else null end) AS BugsOpenedOnDate
FROM <DB TABLE NAME>
WHERE pkey like 'nfs12%'
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sssqlserver
Starting Member

6 Posts

Posted - 2011-10-25 : 18:02:56

Hello, vijays3,

Thanks for your suggestion.

quote:
Originally posted by vijays3

DO The following Things:

1.create Temporary table before while loop in which you can store the resukted records

2. Now Inside While loop

Insert into [Temporary table](col1,col2,col2,,,so one)
SELECT @ddd AS OnDadte, (SELECT count(*)
FROM <DB TABLE NAME>
WHERE pkey like 'nfs12%' AND created <= @dd) AS TotalBugsOnDate,
(SELECT count(*)
FROM <DB TABLE NAME>
WHERE pkey like 'nfs12%' AND created >= DATEADD(DAY, -1,@dd) AND created <= @dd )
AS BugsOpenedOnDate

3. Outside of the while loop write

select * from [Temporary table]
drop table [Temporary table]

I have given you idea.Please follow the instructions .

Thanks
VIjay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-26 : 00:40:40

[code]
DECLARE @startdate datetime, @enddate datetime
SET @startdate = '2010-05-05 00:00:00.000'
SET @enddate = '2011-10-25 00:00:00.000'

;With Date_Table (DateVal)
AS
(
SELECT @startdate
union all
SELECT DATEADD(dd,1,DateVal)
FROM Date_Table
WHERE DATEADD(dd,1,DateVal)<=@enddate
)
SELECT dt.DateVal,t1.TotalBugsOnDate,t2.BugsOpenedOnDate
FROM Date_Table dt
CROSS APPLY(SELECT count(1) AS TotalBugsOnDate
FROM <DB TABLE NAME>
WHERE pkey like 'nfs12%'
and created <= dt.DateVal
)t1
CROSS APPLY(
SELECT count(1) AS BugsOpenedOnDate
FROM <DB TABLE NAME>
WHERE pkey like 'nfs12%'
and created >= DATEADD(DAY, -1,dt.DateVal) AND created <= dt.DateVal
)t2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sssqlserver
Starting Member

6 Posts

Posted - 2011-10-27 : 17:35:45

Hello, visakh16,

Thank you very much for the updated query. It parse but there is a message:
Msg 530, Level 16, State 1, Line 5
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

I can only get 101 rows in the result.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 05:18:19

quote:
Originally posted by sssqlserver

Hello, visakh16,

Thank you very much for the updated query. It parse but there is a message:
Msg 530, Level 16, State 1, Line 5
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

I can only get 101 rows in the result.


DECLARE @startdate datetime, @enddate datetime
SET @startdate = '2010-05-05 00:00:00.000'
SET @enddate = '2011-10-25 00:00:00.000'

;With Date_Table (DateVal)
AS
(
SELECT @startdate 
union all
SELECT DATEADD(dd,1,DateVal) 
FROM Date_Table
WHERE DATEADD(dd,1,DateVal)<=@enddate
)
SELECT dt.DateVal,t1.TotalBugsOnDate,t2.BugsOpenedOnDate
FROM Date_Table dt
CROSS APPLY(SELECT count(1) AS TotalBugsOnDate
FROM <DB TABLE NAME>
WHERE pkey like 'nfs12%'
and created <= dt.DateVal 
)t1
CROSS APPLY(
SELECT count(1) AS BugsOpenedOnDate
FROM <DB TABLE NAME>
WHERE pkey like 'nfs12%'
and created >= DATEADD(DAY, -1,dt.DateVal) AND created <= dt.DateVal
)t2
 OPTION  (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources