Query Help

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 17chad, You are looping through code and running the select statement for eac
h
value of the @.dateStart variable, while there are records in the table, but
for each iteration, you check the Count() with one value of @.datestart,
@.datestop, and then you increment the values first, before you run the
select.. . After you change the values, there may not be any records that
match the criteria...
Also, in both your selects you refer to columns with a table prefox 'a', and
another 'pp', but these are not defined anywhere in the query... This does
not look like working code...
"chad" wrote:

> 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()))) AN
D
> (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|||chad,
The way you have it the code will run from DateStart = 1 until it finds
any date with no records, even if subsequent dates have records... Is that
what you want? If not, then I suggest you define the endDate and change loop
as so:
Declare @.DateStart int Set @.DateStart = 1
Declare @.DateStop int Set @.DateStop = 350 -- or whatever
While @.DateStart < @.DateStop
Begin
If Exists (Select * from [Tables]
Where pp.Class = 17
And a.Start > DateAdd(d, @.datestart, DateDiff(d, 0,
getDate()))
And a.Start < DateAdd(d, @.datestart, DateDiff(d, 1,
getDate())))
Select <Stuff> from [Tables]
Where pp.Class = 17
And a.Start > DateAdd(d, @.datestart, DateDiff(d, 0,
getDate()))
And a.Start < DateAdd(d, @.datestart, DateDiff(d, 1,
getDate()))
Set @.DateStart = @.DateStart + 1
End
"chad" wrote:

> 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()))) AN
D
> (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|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Your names in the sample output are a mix of reserved
words and useless violations of basic ISO-11179 conventions. This
totally useless.
The use of a loop tells us that you do not understand a declarative
language. The use of the word "record" tells us do not know SQL, which
is why you are writing loops and other procedural code tha twill runs
ordersof magnitude slower than good code. .
In clear English, so we do not have to guess, what do you want to do.
And post minimal DDL next time. Was CBretana right about about looking
for a run starting at a given date?|||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 the
n
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|||chad,
The way you have your code written, is equivilent to this psueo code
While (There's 1 or more records using DateStart, DateStart +1)
Begin
Increment DateStart
Return Records for NEW CHANGED VALUE of Datestart, datestart +1
End
-- This *sounds* illogical... It means that whenever the code gets to a
place where there ARE records for the current date, (which would have been
returne the last time through the loop), but NONE FOR THE NEXT day, (althoug
h
it doesn't know that yet) it will enter the while loop, return an empty set,
(That's where you're getting your header with no records) and then, when it
retests the while it will stop...
Your boolean test, in the while clause (with the COunt(1) ...) is testing
how many records were selected in the last time through the loop... , becaus
e
you're incrementing the control variables BEFORE you run the select...
"chad" wrote:

> 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 additiona
l
> 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 t
hen
> increment by 1 and rerun till it finds a count. Once that is satisfied the
n
> 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|||CBretana-
I changed the code to resemble your suggestion in the prior post, and it
complete successfully but returns no data.
Declare @.DateStart int
Declare @.DateStop int
Set @.DateStart = 1
Set @.DateStop = 2
WHILE @.DateStart < @.DateStop
BEGIN
IF EXISTS (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.Appointments a LEFT OUTER JOIN
dbo.ApptChain ac ON a.ApptChainId = ac.ApptChainId LEFT OUTER JOIN
dbo.ApptSet aset ON a.ApptSetId = aset.ApptSetId INNER JOIN
dbo.DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId
INNER JOIN
dbo.PatientProfile pp ON a.OwnerId = pp.PatientProfileId INNER JOIN
dbo.DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId
LEFT OUTER JOIN
dbo.ApptType at ON a.ApptTypeId = at.ApptTypeId
WHERE pp.FinancialClassMId = 1739 And a.ApptStart >DATEADD(d,
@.datestart, DATEDIFF(d, 0, GETDATE())) And a.ApptStart < DATEADD(d,
@.datestart, DATEDIFF(d, 1, GETDATE())))
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.Appointments a LEFT OUTER JOIN
dbo.ApptChain ac ON a.ApptChainId = ac.ApptChainId LEFT OUTER JOIN
dbo.ApptSet aset ON a.ApptSetId = aset.ApptSetId INNER JOIN
dbo.DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId INNER
JOIN
dbo.PatientProfile pp ON a.OwnerId = pp.PatientProfileId INNER JOIN
dbo.DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId LEFT
OUTER JOIN
dbo.ApptType at ON a.ApptTypeId = at.ApptTypeId
WHERE pp.FinancialClassMId = 1739 And a.ApptStart > DATEADD(d, @.datestart,
DATEDIFF(d, 0, GETDATE())) And a.ApptStart < DATEADD(d, @.datestart,
DATEDIFF(d, 1, GETDATE()))
ORDER BY pp.Last
SET @.DateStart = @.DateStart + 1
END|||>> I changed the code to resemble your suggestion in the prior post,
and it complete successfully but returns no data. <<
And now, how about some DDL? And try to fix up the obviously absurd
things like "type_id" in the data model.
day of no records [sic]. It is when I hit two days of zero records
[sic] that I get an additional header row with no data that messes it
up. <<
You might also want to learn the differences between records and rows.
If you keep talking in fiel system terms, you will keep producing file
system code, like your cursors.
Sample data and expect results would be nice, too. Are you looking for
runs with gaps of two or more days between them, so a gap of one is
fine?|||On Thu, 12 May 2005 16:10:27 -0700, chad wrote:

>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.
(snip)
Hi Chad,
I'm not sure if you're still reading., since this question is already 5
days old.
Anyway, reading your code I have the feeling that you could do this in
one SELECT statement instead of using a loop:
SELECT statement
FROM tables
WHERE CONVERT(char(10), Start, 114)
= (SELECT CONVERT(char(10), MIN(Start), 114)
FROM tables
WHERE CONVERT(char(10), Start, 114)
> CONVERT(char(10), CURRENT_TIMESTAMP, 114))
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)