|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Query Helpwhen 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 chad, You are looping through code and running the select statement for each
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... Show quote "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()))) 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 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 Show quote "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()))) 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 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 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 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, (although 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... , because you're incrementing the control variables BEFORE you run the select... Show quote "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 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 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. >> This query returns records [sic] correctly when there is only one 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 Hi Chad,>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) 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)) Best, Hugo(untested) -- (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics