Loops and get next event

Question

What is the best way to loop through records.  For instance I am wring 2-3 queries that pull dates.  So when the first query returns a date i want to look in the second query and get the next date ect. then loop back to the first query and keep populating dates until there are no records found.

Answer 1

For looping through rows in SQL Server you need to use a cursor (assuming that no efficient set based approach exists). If you need to use a cursor, do it right way, so read this blog first:

The Truth about Cursors - Part 1

and then read other parts.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

The best is often to write it as a single query, and let the optimizer figure out what join method is the best one.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

Just my 2 cents,

   Naomi is right. The Cursors are the way to go about it. But if I understand your issue right, you might want to try something on these lines. Again, the success of this implementation would depend on what your requirement is. 

Also, remember that this needs some tweaking for you to customize this to your requirement. And this solution will only work upto two levels i.e, when you have one date to be looked up in another table, and based on the result from that look up, back to the first table and populate some data.

Of course, if this works for ya, then you can replace the cross join and the cte with a simple straight inner join of all 3 tables with the appropriate conditions.

;WITH CTE 
AS
(
select DateColumn1FromTable1 AS DateColumn2FromCTE, DateColumn2FromTable2 AS DateColumn2FromCTE
FROM 
<Table1> O
CROSS APPLY 
(SELECT <DateFromTable2>, <OtherColumns>
 FROM <Table2> WHERE O.<DateFromTable1> = <DateFromTable2>
) 
)

   SELECT RelatedColumns FROM <Table1> 
   Inner Join <CTE> C WHERE DateColumn2FromTable1 = DateColumn2FromCTE 

---

Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

Answer 4

Here's a basic example of one way to do it, assuming self joining to the same table:  (you could also incorporate correlated subqueries, CTEs, etc., more than one way to accomplish it).   In a real world situation, you'd use a DATE field (assuming SQL 2008), or you'd fix each date to not include the time, you wouldn't do "Select *", etc., but this is just an example).  And if you needed to limit to just one row, you'd need a derived query or other means to get just one, and enforce the appropriate order... but this is a start).

Declare @DateList Table (DID int identity, ADate datetime)
 Insert @DateList /* Rough/quick example:  a list of dates */
  Select GetDate() UNION ALL
  Select GetDate() + 1 UNION ALL
  Select GetDate() + 2 UNION ALL
  Select GetDate() + 3 UNION ALL
  Select GetDate() + 4 UNION ALL
  Select GetDate() + 5 UNION ALL
  Select GetDate() + 6 UNION ALL
  Select GetDate() + 7 UNION ALL
  Select GetDate() + 8 UNION ALL
  Select GetDate() +9 
 
 Select D1.*, D2.ADate as NextDate from @DateList D1
  Left Join @DateList D2 on D2.Adate = D1.Adate + 1  /* Rough example: Each row includes the next day's date as another column */

Answer 5

I am trying to let all this seep into my brain, for the query below, i want to get all the Serial no.'s and service dates (and i am also trying to write my first cursor)

am i on the right track?

example:

1234 ---- 1-1-2012,   1-5-2012,   1-20-2012

4567 ---- 1-21-2012,   1-30-2012,   2-20-2012

declare @serialNo varchar(20)
declare @serviceDate datetime
declare c cursor static
for select 
a1.[Asset Serial No_], 
 b1.[Service Date]
 from 
[Sales Invoice Line] as a1  join 
[Sales Invoice Header] as b1 on b1.[No_] = a1.[Document No_] 
where a1.[Asset Serial No_]<>''
Order By a1.[Asset Serial No_],[Service Date] asc
open c
while 1=1
begin fetch c into @serialNo,@serviceDate
if @@fetch_status<>0 break

/* What do i need to do to the data here */

end
close c
deallocate c

Answer 6

Do you mean that if you have the following tables:

SERIAL NUMBERS: 
-1234
-4567

and another table, SERVICE DATES
-1234|1-1-2012
-1234|1-5-2012
etc.,

that you want output, one line per SERIAL number, and as many service dates as exist?  (Or a certain number, the three most recent, or something like that?) If that's the case, the answer you're looking for can be done with PIVOT, and wouldn't need a Cursor.  However, there's probably a way to write it with a cursor too, or a set of CASE statements, or a couple other ways too. I'll wait to hear back if my scenario is what you meant.

Answer 7

My goal is to have all the serial numbers and the service dates in sequence on the same row (in one table) .  After i get this complete i will have to loop through the sql for the repairs and populate this data between the service dates.

I am not really sure how to write cursors that was my attempt above i presume i need to (somehow) populate a temp table with the data....

So  my first Loop will populate all service dates.  

Answer 8

Can you post simplified structures of your tables, some input and desired output? Then we will be able to come up with the direct solution.

I have a feeling you simply need to concatenate multiple rows into one, if that's the case, there are well known solutions.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 9

here are the tables....To start my goal is to get all of the first service dates for an asset...  That is the first service date from the sales order for each asset.. 

---Run to start repair date diff-----

Run - Service date

Start repair-Creation date

The job also has a completion date this completion date will be used to look at the 2nd service date reported this will answer the question after the asset was repair completed how long did it take for the asset to be used again (the next service date)

So i was looking for date diff's for      [1stServiceDate to repairStart],  RepairCompletion to 2ndServiceDate, 2ndServiceDate to NextRepair ect

in an ideal word there would be a    run/repair/run/repair/run/repair   the only linkage is if a asset has a service date i need to look at the repair that has a date >= the service date

-----------------Sales Invoice information------------------
-----The sales header has the asset serial number 
CREATE TABLE [dbo].[Sales Invoice Header](
	
	[No_] [varchar](20) NOT NULL,
	[Sell-to Customer No_] [varchar](20) NOT NULL,
	[Bill-to Customer No_] [varchar](20) NOT NULL,
	[Bill-to Name] [varchar](50) NOT NULL,
	[Bill-to Name 2] [varchar](50) NOT NULL,
	[Bill-to Address] [varchar](50) NOT NULL,
	[Bill-to Address 2] [varchar](50) NOT NULL,
	[Posting Date] [datetime] NOT NULL,
	[Shipment Date] [datetime] NOT NULL,
	[Posting Description] [varchar](50) NOT NULL,
	[Payment Terms Code] [varchar](10) NOT NULL,
	[Due Date] [datetime] NOT NULL


----The sales invoice line holds the  service date

CREATE TABLE [dbo].[Sales Invoice Line](
	
	[Document No_] [varchar](20) NOT NULL,
	[Asset Serial No_] [varchar](20) NOT NULL,
	[Service Date] [datetime] NOT NULL
	
	
	--------------------Jobs------------
	

	
	CREATE TABLE [dbo].[Asset](
		
		[Serial No_] [varchar](20) NOT NULL,
		[Item No_] [varchar](20) NOT NULL,
		[Product Group] [varchar](10) NOT NULL,
		[Item Description 2] [varchar](30) NOT NULL,
		[Common Item No_] [varchar](20) NOT NULL,
		[Status Update Date] [datetime] NOT NULL
		


CREATE TABLE [dbo].[Job](

	[No_] [varchar](20) NOT NULL,
	[Creation Date] [datetime] NOT NULL,
	[Ending Date] [datetime] NOT NULL,
	[Status] [int] NOT NULL,
	[Global Dimension 2 Code] [varchar](20) NOT NULL,
	[Item No_] [varchar](20) NOT NULL,
	[Serial No_] [varchar](20) NOT NULL,
	[Job Type 2] [int] NOT NULL,
	[Scrapped] [tinyint] NOT NULL

Answer 10

I am stuck writing the first cursor.  The query below returns results 

1234 1-1-2012

1234 1-2-2012

1235 1-1-2012

1236 1-5-2012

1236 1-6-2012

1236 2-1-2012

-----------------------------

The desired output is 

Row1       Row2          Row3        Row4

1234    1-1-2012    1-2-2012

1235     1-1-2012

1236    1-5-2012    1-6-2012    2-1-2012

select 
a1.[Asset Serial No_], 
 b1.[Service Date]
 from 
[Sales Invoice Line] as a1  join 
[Sales Invoice Header] as b1 on b1.[No_] = a1.[Document No_] 
where a1.[Asset Serial No_]<>''
Order By a1.[Asset Serial No_],[Service Date] asc

Create Table #results(
	ID int identity(1,1),
	Serial varchar(20),
	ServiceDate datetime
)

insert into #results(
Serial,
ServiceDate

)

select 
a1.[Asset Serial No_], 
 b1.[Service Date]
 from 
[Sales Invoice Line] as a1  join 
[Sales Invoice Header] as b1 on b1.[No_] = a1.[Document No_] 
where a1.[Asset Serial No_]<>''
Order By a1.[Asset Serial No_],[Service Date] asc

Declare @Counter INT
set @Counter=1

Answer 11

In your case you don't need a cursor at all, as I said, you just need to concatenate rows:

select 
a1.[Asset Serial No_], 
 
stuff((select  ' ' + convert(varchar(10), b1.[Service Date],110) from
[Sales Invoice Header] as b1 where b1.[No_] = a1.[Document No_] 

order by [Service Date] FOR XML PATH('')),1,1,'') as [Dates]
 from [Sales Invoice Line] as a1
where a1.[Asset Serial No_]<>''
Order By a1.[Asset Serial No_]

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 12

This is how i ended up solving my problem.....

Create Table #results(
	ID int identity(1,1),
	Serial varchar(20),
	ServiceDate datetime,
	CreationDate datetime,
	CompletionDate datetime
)
Declare @ID int
declare @Serial varchar(50)
Declare @ServiceDate datetime
declare @CreationDate datetime
declare	@CompletionDate datetime
Declare @rowID int
insert into #results
select 
a1.[Asset Serial No_], 
b1.[Service Date],
NULL,
NULL
 from 
[Sales Invoice Line] as a1  join 
[Sales Invoice Header] as b1 on b1.[No_] = a1.[Document No_] 
where a1.[Asset Serial No_]<>''



set @rowID = (select MIN(ID) from #results)
while @rowID is not NULL 
Begin
Select Top (1) @Serial=Serial, @ServiceDate=ServiceDate
From #results
where ID=@rowID

 select 
  top (1)
 @CreationDate=[Creation Date], @CompletionDate=[Completion Date]
  from [Asset] a
   join [Job] as b on b.[Serial No_] = a.[Serial No_]
   
		where
		a.[Serial No_]=@Serial
		and b.[Job Type 2] = 2 --2Repair

		and [Creation Date]>=@ServiceDate
		order by [Creation Date]
update #results set CreationDate=@CreationDate, CompletionDate=@CompletionDate
where ID=@rowID

set @rowID=(select MIN(ID) from #results
where ID>@rowID)
end

select * from #results
order by [Serial]
drop table #results

Answer 13

If you needed your results in different columns, then you needed to use PIVOT instead of concatenation. In any case, your solution is weird and inefficient. Can you re-post the desired output - do you always need just 4 columns in the result named Row 1 (Serial #No) Row2 (first date) Row3 (second date) Row 4 (third date)

?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 14

You are totally right the query is massively inefficient, for 5,000 rows it takes about 2 min..... and i am running it for a date range of only a few months.  Currently the top result set is what is returned from my long/bad query......  In a perfect world i would like to see the result set on the bottom.

BUT-  I would be good with the current output format, if i could understand how to properly write the SQL (and not use the current method of while loop)

--Current output
---------------Service----------Creation------Completion
12109	2012-01-30 	2012-02-02 	2012-02-10 
12109	2012-03-13 	2012-03-24 	2012-03-31 
ect...
-----Desired output
            |---DateDiff----|                |-----DateDiff----|  |---DateDiff---|           |-------DateDiff--|
	    Service1     Creation1      Completion1        Service2       Creation2        Completion2        Service3
12109	2012-01-30   2012-02-02     2012-02-10      2012-03-13   2012-03-24 	  2012-03-31 		ect	

Answer 15

If the maximum number of such groups is known, say, 3, then it's simple:

;with cte as (select *, ROW_NUMBER() over (partition by Serial 
order by Service, Creation, Completion) as Rn from (my current query) X)
select Serial, 
max(case when Rn = 1 then Service end) as Service1,
max(case when Rn = 1 then Creation end) as Creation1,
max(case when Rn = 1 then Completion end) as Completion1,
max(case when Rn = 2 then Service end) as Service2,

max(case when Rn = 2 then Creation end) as Creation2,
max(case when Rn = 2 then Completion end) as Completion2,
max(case when Rn = 3 then Service end) as Service3,

max(case when Rn = 3 then Creation end) as Creation3,
max(case when Rn = 3 then Completion end) as Completion3
from cte GROUP BY Serial

The above assumes that the number of total columns is already known and 3 is the maximum number.

If we don't know the maximum, then we're talking about dynamic PIVOT on multiple columns.

I have a blog post explaining how this can be done

Dynamic PIVOT on multiple columns

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 16

Thanks for the info Naomi, I am looking in to it now. I really do not mean to be difficult, just teaching myself SQL one day at a time.  I appreciate all the information this forum gives me !!

Answer 17

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/332fad84-565a-4a3e-8727-7ef4a6104a3a