SQLTeam.com | Weblogs | Forums

Urgent Help With "While Loop Syntax"


#1

Hi Guys, I am using below While Loop Syntax but for some reason, I can't update the records or it is not looping through. Could you Please advise what's going on or what I am doing something wrong? I wish I can provide some type of sample data.

Note: - The purpose I am using top 1 because after Linking bunch of tables temp table has duplicate and I want to pick first-row base on Asc Des_modid

DECLARE @SID INT
DECLARE @MID INT
DECLARE @ROWnUM INT
DECLARE @Counter INT

SET @Counter = 1

SELECT @ROWnUM = COUNT(*) FROM #TempMainTable WHERE time1 IS NULL

--PRINT @ROWnUM

WHILE (@Counter <= @ROWnUM)
BEGIN

	SELECT TOP 1 @SID = source_id, @MID = Des_modid
	FROM #OpenLink OWT
	INNER JOIN dbo.log_file_detail LFHD ON OWT.source_id = LFHD.id
	ORDER BY Des_modid

UPDATE dbo.log_file_header_detail
SET modid = @MID
,LinkingType = 'Whil_Open'
FROM dbo.log_file_detail
WHERE ID = @SID
AND batchid =2000080
AND modid IS NULL
AND @MID NOT IN (SELECT modid FROM dbo.log_file_detail WHERE batchid = 2000080)

SET @Counter = @Counter - 1

END

Thanks in Advance.


#2

Unless the @ROWnUM value starts at 0, it looks to me like that WHILE loop will run forever, since you are decrementing @Counter (so it will be 1, then 0, then -1, etc.)


#3

What value are you getting for @ROWnUM? If it's zero, the code will do nothing.


#4

@ROWNum is my total records that I have to Link TO. it is my main temp table to Link To.


#5

Change MINUS to PLUS ?


#6

I did change
SET @Counter = @Counter + 1
and it is not updating anything. Here I have in WHILE (@Counter <= @ROWNum) = WHILE ( 1 <= 78)
Any advise what I am doing wrong.

Thank You.


#7

Put some PRINT statements in the LOOP so you can see what the variables are, and the @@ROWCOUNT after the SELECT and UPDATE statements.


#8

Kristen,

I come up with LIVE/Test Data. Is it OK If I paste it here or IM you? For some reason I am updating only 0 records.

Thank You.


#9

Also, you're doing an INNER JOIN to dbo.log_file_detail, so if for some reason no matching row is found there, the @SID and @MID values could be NULL, which will prevent the UPDATE statement from matching any rows either.


#10

Scott, Good catch. However, It is one to many Link between Log_File_Detail table and #Temp table. It is always be more than one row(s) to update.


#11

You need to put some debugging code in to show what values are being used at various points in the code to figure out why you are getting zero rows. I can't help with that!

SELECT TOP 1 @SID = source_id, @MID = Des_modid
	FROM #OpenLink OWT
	INNER JOIN dbo.log_file_detail LFHD ON OWT.source_id = LFHD.id
	ORDER BY Des_modid

You need to know how many rows this returns it will either be 0 (then #OpenLink is empty) or 1. Display the value in @@ROWCOUNT immediately after this statement

Then you need to what values have been placed in @SID and @MID.

Are they what you expected?

The you need to check how many rows match:

SELECT [Count]=COUNT(*),
    [In test]=CASE WHEN @MID NOT IN (SELECT modid FROM dbo.log_file_detail WHERE batchid = 2000080) THEN 'NOT IN' ELSE 'FOUND IN' END,
    [@SID]=@SID,
    [@MID]=@MID
FROM dbo.log_file_detail
WHERE ID = @SID
AND batchid =2000080
AND modid IS NULL
AND @MID NOT IN (SELECT modid FROM dbo.log_file_detail WHERE batchid = 2000080)

#12

I understand that the expectation is that there is always at least one matching row. But given that you're not getting an UPDATE when you expect one, I'm saying that maybe a row(s) are missing from the table even though it(they) should be there.


#13

Here is LIVE/Test data. I am fixing my code as you described above. I am struggling myself as well. I really really appreciate in advance for your help.

Create table ##Destination
(
ID INT,
isc varchar(25),
cname varchar(25),
stitle int,
sname varchar(20),
weekday datetime,
stime datetime,
S_ID INT

)

INSERT INTO ##Destination ( ID,isc,cname,stitle,sname,weekday,stime)

select 1,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 10:39:00.000'
union
select 2,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 13:40:00.000'
union
select 3,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 18:39:00.000'
union
select 4,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 22:26:00.000'
union
select 5,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 18:46:00.000'
union
select 6,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 22:34:00.000'
union
select 7,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 12:28:00.000'
union
select 8,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 10:45:00.000'
union
select 9,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 19:30:00.000'
union
select 10,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 22:46:00.000'
union
select 11,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 09:45:00.000'
union
select 12,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 12:36:00.000'
union
select 13,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 16:44:00.000'
union
select 14,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 21:26:00.000'
union
select 15,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 11:39:00.000'
union
select 16,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 14:28:00.000'
union
select 17,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 17:39:00.000'
union
select 18,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 20:40:00.000'
union
select 19,'0022472606H','FSIN',56449,'NY1','2017-04-17 00:00:00.000','1900-01-01 17:57:00.000'
union
select 20,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 21:10:00.000'
union
select 21,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 11:48:00.000'
union
select 22,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 14:45:00.000'
union
select 23,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 18:45:00.000'
union
select 24,'0022472606H','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 20:54:00.000'
union
select 25,'NULL','FSIN',56449,'NY1','2017-04-10 00:00:00.000','1900-01-01 20:54:00.000'
union
select 26,'NULL','FSIN',56449,'NY1','2017-04-17 00:00:00.000','1900-01-01 20:54:00.000'

cREATE TABLE ##Source_Main_Query
(
isc VARCHAR(25),
S_ID INT,
sname varchar(25),
cname varchar(25),
stitle int,
w_date datetime,
days varchar(10),
startairtime datetime,
endairtime datetime
)

insert into ##Source_Main_Query (isc,S_ID,sname,cname,stitle,w_date,days,startairtime,endairtime)

select '0002471703H',55966811,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966812,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966813,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966814,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966815,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966816,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966817,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966818,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966819,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966820,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966821,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966822,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966823,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966824,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966825,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966826,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966827,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966828,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966829,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0002471703H',55966830,'NY1','FSIN',56287,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022471711H',56105256,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022471711H',56105257,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022471711H',56105258,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022471711H',56105259,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022471711H',56105260,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022471711H',56105261,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022471711H',56105262,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022471711H',56105263,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022471711H',56105264,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022471711H',56105265,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022471711H',56105276,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022471711H',56105277,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022471711H',56105278,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022471711H',56105279,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022471711H',56105280,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022471711H',56105281,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022471711H',56105282,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022471711H',56105283,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022471711H',56105284,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022471711H',56105285,'NY1','FSIN',56448,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',55966771,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966772,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966773,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966774,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966775,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966776,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966777,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966778,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966779,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966780,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966781,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966782,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966783,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966784,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966785,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966786,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966787,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966788,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966789,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',55966790,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56019516,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56019517,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56019518,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56019519,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56019520,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56019521,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56019522,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56019523,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56019524,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56019525,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56050296,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56050297,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56050298,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56050299,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56050300,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56084626,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56084631,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56084632,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56084634,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56132489,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56132490,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56150219,'NY1','FSIN',56449,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56150224,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56150225,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56150226,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56150227,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56150228,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56150229,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56150230,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56150231,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56150232,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56150233,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56150234,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56150235,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56150236,'NY1','FSIN',56449,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0022472606H',56162278,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162279,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162280,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162281,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162282,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162283,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162284,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162285,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162286,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162287,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162288,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162289,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162290,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162291,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162292,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162293,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162294,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162295,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162296,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0022472606H',56162297,'NY1','FSIN',56449,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966721,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966722,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966723,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966724,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966725,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966726,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966727,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966728,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966729,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966730,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966731,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966732,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966733,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966734,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966735,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966736,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966737,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966738,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966739,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966740,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966741,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966742,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966743,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966744,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966745,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966746,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966747,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966748,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966749,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',55966750,'NY1','FSIN',56450,'2017-04-17 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56019526,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56019527,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56019528,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56019529,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56019530,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56019531,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56019532,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56019533,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56019534,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56019535,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-FR','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56050301,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56050302,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56050303,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56050304,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56050305,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56069573,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56069574,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56069575,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56069576,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56069577,'NY1','FSIN',56450,'2017-04-03 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56105246,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56105247,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56105248,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56105249,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56105250,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56105251,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56105252,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56105253,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56105254,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56105255,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-TH','1900-01-01 09:00:00.000','1900-01-01 23:00:00.000'
union
select '0024471709H',56105266,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56105267,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56105268,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56105269,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56105270,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56105271,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56105272,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56105273,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56105274,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'
union
select '0024471709H',56105275,'NY1','FSIN',56450,'2017-04-10 00:00:00.000','MO-SU','1900-01-01 00:00:00.000','1900-01-01 23:59:00.000'


#14

Any help or advice would be great appreciated.

Thank You.


#15

Your sample data uses table names:
##Destination
##Source_Main_Query

Your original code used table names:
#OpenLink
dbo.log_file_detail
log_file_header_detail

I have no idea what table name is supposed to match with what. You have to remember that we (I) know nothing about your data and that we are volunteering our time to help. I don't mind helping with SQL itself, but frankly I don't have time to spend to try to line up table definitions with different names.


#16

;WITH CTE_FINAL AS
(
SELECT
DISTINCT
LFHD.ID AS source_id
,t.S_ID AS Des_modid
,T.rate
,DENSE_RANK() OVER (PARTITION BY t.isc,t.stitle,t.sname,t.cname ORDER BY T.RATE DESC, T.S_ID ) AS SourceOrder
,DENSE_RANK() OVER (PARTITION BY t.isc,t.stitle,t.sname,t.cname ORDER BY lfhd.STIME) AS DestOrder

	FROM ##Destination lfhd 

LEFT OUTER JOIN ##Source_Main_Query t
ON LTRIM(RTRIM(t.isc)) = LTRIM(RTRIM(lfhd.isc))
AND t.stitle = lfhd.stitle
AND LTRIM(RTRIM(t.sname)) = LTRIM(RTRIM(lfhd.sname))
AND LTRIM(RTRIM(t.cname)) = LTRIM(RTRIM(lfhd.cname))
AND lfhd.stime BETWEEN T.startairtime AND t.endairtime
and t.w_date = lfhd.weekday
AND LFHD.Source_DayNumber BETWEEN T.DayNumberStart AND T.DayNumberEnd
)
SELECT * FROM CTE_FINAL
WHERE CTE_FINAL.DestOrder = CTE_FINAL.SourceOrder

So far I come up with this. Above code is not consistent. I will provide Sample/Live data. I am going to upload into One drive, Here is the LInk SQL Sample Code
It should Link 24 records. I should get 24 unique IDs,s_ids.

Please advise.
Thank You.