Looping Query - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Looping Query

Author

Topic

cheilig
Starting Member

3 Posts

Posted - 2005-05-12 : 16:57:38

I have the following query that returns extra header info in the results when there is more than 1 day with no records in the query. I'm trying to figure out how to run this and not get the additional header information.

Here is the query:
SET NOCOUNT ON
DECLARE @datestart int
DECLARE @datestop int
SET @datestart = 1
SET @datestop = 2
WHILE
(SELECT count(1)
FROM [tables]
WHERE (a.Start > DATEADD(d, @datestart, DATEDIFF(d, 0, GETDATE())))
AND (pp.Class = 17) AND (a.Start < DATEADD(d, @datestop, DATEDIFF(d, 0,
GETDATE())))) = 0
BEGIN
SET @datestart = @datestart + 1
SET @datestop = @datestop + 1
SELECT [statement]
FROM [tables]
WHERE (a.Start > DATEADD(d, @datestart, DATEDIFF(d, 0, GETDATE()))) AND
(pp.Class = 17) AND (a.Start < DATEADD(d, @datestop, DATEDIFF(d, 0,
GETDATE())))
ORDER BY pp.Last
END

Here's the output:
Date Name Class
--------------------
Date Name Class
----------- ---------
Date Name Class
----------- -----------------------------------
May 16 2005 David L Afor 17
May 16 2005 Tina M Coll 17
May 16 2005 Dan O Doer 17

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-05-12 : 19:28:40

If you can post table structure and data. I think someone can suggest you better solution that looping.
cheers

mk_garg

cheilig
Starting Member

3 Posts

Posted - 2005-05-13 : 09:54:53

This query returns records correctly when there is only one day of no
records. It is when I hit two days of zero records that I get an additional
header row with no data that messes it up. I set the @datestart and
@datestop at 1 and 2 respectfully to look for tomorrows records, if zero then
increment by 1 and rerun till it finds a count. Once that is satisfied then
it needs to run the second part of the query to retun data.

------------ Here is the entire query with table names changed
-----------------
SET NOCOUNT ON

DECLARE @datestart int
DECLARE @datestop int
SET @datestart = 1
SET @datestop = 2
WHILE
(SELECT count(*)
FROM dbo.TableA a LEFT OUTER JOIN
dbo.TableAC ac ON a.ApptChainId = ac.ApptChainId LEFT
OUTER JOIN
dbo.TableASET aset ON a.ApptSetId = aset.ApptSetId INNER
JOIN
dbo.TableDFF dff ON a.FacilityId = dff.DoctorFacilityId
INNER JOIN
dbo.TablePP pp ON a.OwnerId = pp.PatientProfileId INNER JOIN
dbo.TableDFF dfr ON a.ResourceId = dfr.DoctorFacilityId
LEFT OUTER JOIN
dbo.TableAT at ON a.ApptTypeId = at.ApptTypeId LEFT OUTER JOIN
dbo.TableMLFC mlfc ON pp.FinancialClassMId = mlfc.MedListsId LEFT OUTER
JOIN
dbo.TableC c ON a.CasesId = c.CasesId LEFT OUTER JOIN
dbo.TableMLFCC mlfcc ON c.FinancialClassMId = mlfcc.MedListsId
WHERE (a.ApptStart > DATEADD(d, @datestart, DATEDIFF(d, 0,
GETDATE()))) AND (pp.FinancialClassMId = 1739) AND (a.ApptStart < DATEADD(d,
@datestop, DATEDIFF(d, 0, GETDATE())))) = 0
BEGIN
SET @datestart = @datestart + 1
SET @datestop = @datestop + 1
SELECT CAST(a.ApptStart AS varchar(11)) AS Date, ISNULL(pp.First + ' ',
'') + ISNULL(pp.Middle + ' ', '') + pp.Last AS Name, dfr.ListName AS
ResourceName,
pp.SSN AS SSN, pp.PatientId, ISNULL(at.Name,
'Unknown') AS ApptType, pp.Phone1 AS Phone1, CAST(pp.Birthdate AS
varchar(11)) AS Birthdate,
pp.FinancialClassMId
FROM dbo.TableA a LEFT OUTER JOIN
dbo.TableAC ac ON a.ApptChainId = ac.ApptChainId LEFT
OUTER JOIN
dbo.TableASET aset ON a.ApptSetId = aset.ApptSetId INNER
JOIN
dbo.TableDFF dff ON a.FacilityId = dff.DoctorFacilityId
INNER JOIN
dbo.TablePP pp ON a.OwnerId = pp.PatientProfileId INNER JOIN
dbo.TableDFF dfr ON a.ResourceId = dfr.DoctorFacilityId
LEFT OUTER JOIN
dbo.TableAT at ON a.ApptTypeId = at.ApptTypeId LEFT OUTER JOIN
dbo.TableMLFC mlfc ON pp.FinancialClassMId = mlfc.MedListsId LEFT OUTER
JOIN
dbo.TableC c ON a.CasesId = c.CasesId LEFT OUTER JOIN
dbo.TableMLFCC mlfcc ON c.FinancialClassMId = mlfcc.MedListsId
WHERE (a.ApptStart > DATEADD(d, @datestart, DATEDIFF(d, 0, GETDATE())))
AND (pp.FinancialClassMId = 1739) AND (a.ApptStart < DATEADD(d, @datestop,
DATEDIFF(d, 0,
GETDATE())))
ORDER BY pp.Last
END

mr_mist
Grunnio

1870 Posts

Posted - 2005-05-13 : 10:18:35

You shouldn't need to loop through like that. Join your main query to a sub query that selects counts for ranges of dates, and return all the dates within the lowest range that has a count > 0.

-------
Moo. :)

cheilig
Starting Member

3 Posts

Posted - 2005-05-13 : 10:29:49

quote:
Originally posted by mr_mist

You shouldn't need to loop through like that. Join your main query to a sub query that selects counts for ranges of dates, and return all the dates within the lowest range that has a count > 0.

-------
Moo. :)

I need the query to look at the following day for records that match the query. If there are no records I need it to increment the variables and run the query again. It needs to continue this until it gets a count. At that point then run the query that gives me data for that day. Not visualizing how your answer would work and not use a WHILE statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources