April 17, 2017 at 3:47 pm
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.
April 17, 2017 at 4:11 pm
Without sample data and EXACTLY what is happening we may not be able to help.
What I do in debugging cases like this is scatter PRINT statements of various important things in the code to let me know exactly what is going on in each phase of execution.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 17, 2017 at 5:30 pm
April 17, 2017 at 9:12 pm
The Goal here is After I link two tables I am having a lot of dups. It is One to many relation. What I am looking. If they find a link and it returns more than one Pick the first one and goes to next row and Make sure I will not use the same ID again. If there is any other Option that would be great. Here is sample Live data that I am using for testing....
)
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'
April 17, 2017 at 9:36 pm
Look up examples for CROSS APPLY and TOP... Here's Itzik's example from Itzik Ben-Gan on SQL 2008
SELECT D.orderID, D.productID, D.qty
FROM Sales.Orders AS O
CROSS APPLY
(SELECT TOP (3) OD.OrderID, OD.ProductID, OD.qty
FROM Sales.OrderDetails OD
WHERE OD.orderID = O.orderID
ORDER BY qty DESC, productID) AS D;
April 17, 2017 at 9:54 pm
Thanks for your reply, for some reason CROSS APPLY with TOP not working for me.
April 18, 2017 at 12:02 am
"Not working" is not terribly helpful when diagnosing a query.
post the T-SQL for your query and maybe we can help.
For someone that's been here as long as you have, you should know how to post an answerable question. Please read this article:
April 18, 2017 at 8:14 am
Sorry for not providing an enough info. Here is my code and above is my sample data that I used to test this query. I am getting a lot of DUPS.
select
distinct
d1.id,d.s_id,d1.s_id
from ##Destination d
Cross apply(
SELECT
DISTINCT
s.S_ID,
D.ID
FROM ##Source_Main_Query S
inner join ##Destination d ON
S.isc = D.isc
AND S.cname = D.cname
AND S.sname = D.sname
AND S.stitle = D.stitle
AND S.w_date = D.weekday
AND D.stime BETWEEN S.startairtime AND S.endairtime
)D1
April 18, 2017 at 7:19 pm
I posted a link to Jeff's article so you would read it. One of the keys to getting an answer is asking a good question, and Jeff explains how to do that in his article... okay with that out of the way...
What's the resulting dataset you're looking for?
Shot on the dark:
SELECT S_ID
, ID
, rownum
FROM
(SELECT
s.S_ID,
D.ID,
ROW_NUMBER() OVER (PARTITION BY s.S_ID ORDER BY d.ID) AS rownum
FROM ParentTable S
inner join Destination d ON S.isc = D.isc
AND S.cname = D.cname
AND S.sname = D.sname
AND S.stitle = D.stitle
AND S.w_date = D.[weekday]
WHERE D.stime BETWEEN S.startairtime AND S.endairtime) x
WHERE x.rownum=1;
April 18, 2017 at 10:21 pm
The end result is, I want to update Destination.s_id field from Parenttable.s_id. Because it is one to many relations, so I am getting a
lot of duplicates. I want to Pick the first row and update it and move to next. Updated column should be unique can't use the same ID again. Above I provided a sample/Live data.
I ran above query and it is not running as I want.
Any advice would be great appreciated.
April 19, 2017 at 10:27 pm
Here is the Live/Sample Code. I know it's too lengthy but that's the perfect example. I am/was trying to avoid (While Loop or Cursor to do this accomplished) so come up with below SQL. However, it is not working perfectly. In some scenario, it is working fine and some don't. The goal here is Update "##Destination.S_ID" from "##Source_Main_Query.S_ID" with linking keys below. It is one to many relation so getting a lot of duplicates. So if I used S_ID before I can't use the same ID again. If one Key Link to 10 rows Pick the first one with HIGHER RATE. I totally understand you are trying to helping me without knowing data and structure but I must say THANK YOU for your help in advance. If You know the best and perfect way to accomplish this scenario I would love to learn it from you. Thanks.
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,
rate decimal(10,2)
)
insert into ##Source_Main_Query (isc,S_ID,sname,cname,stitle,w_date,days,startairtime,endairtime,rate)
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'
select * from ##Destination
select * from ##Source_Main_Query
;WITH CTE_FINAL AS
(
SELECT
DISTINCT
LFHD.ID AS source_id
,t.S_ID AS Des_modid
,t.Days
,T.rate
,DENSE_RANK() OVER (PARTITION BY t.isc,t.stitle,t.sname,t.cname ORDER BY t.s_id ) AS SourceOrder
,DENSE_RANK() OVER (PARTITION BY t.isc,t.stitle,t.sname,t.cname ORDER BY lfhd.id) AS DestOrder
FROM ##Source_Main_Query tINNER JOIN ##Destination lfhdON LTRIM(RTRIM(t.isc)) = LTRIM(RTRIM(lfhd.isc))AND t.stitle = lfhd.stitleAND LTRIM(RTRIM(t.sname)) = LTRIM(RTRIM(lfhd.sname))AND LTRIM(RTRIM(t.cname)) = LTRIM(RTRIM(lfhd.cname))AND lfhd.stimeBETWEEN T.startairtime AND t.endairtimeand t.w_date = lfhd.weekday)SELECT * FROM CTE_FINALWHERE CTE_FINAL.DestOrder = CTE_FINAL.SourceOrder
April 20, 2017 at 2:32 am
-- Part 1: Test rig. Stare & compare...
;WITH
DestinationAS (SELECT rn = ROW_NUMBER() OVER (PARTITION BY isc, stitle, sname, cname, [weekday]ORDER BY stime), * FROM #Destination),
Source_Main_QueryAS (SELECT rn = ROW_NUMBER() OVER (PARTITION BY isc, stitle, sname, cname,w_date ORDER BY S_ID), * FROM #Source_Main_Query)
SELECT
d.*,
'#''#', -- this is a visual divider between columns from d and those from x
x.*
FROM Destination d
OUTER APPLY (
SELECT*
FROMSource_Main_Query s
WHEREs.isc = d.isc
ANDs.stitle = d.stitle
ANDs.sname = d.sname
ANDs.cname = d.cname
ANDs.w_date = d.[weekday]
ANDd.stime BETWEEN startairtime AND endairtime
ANDs.rn = d.rn
) x
ORDER BY d.isc, d.stitle, d.sname, d.cname, d.[weekday],stime
-- Part 2: Perform the update
;WITH
DestinationAS (SELECT rn = ROW_NUMBER() OVER (PARTITION BY isc, stitle, sname, cname,[weekday] ORDER BY stime), * FROM #Destination),
Source_Main_QueryAS (SELECT rn = ROW_NUMBER() OVER (PARTITION BY isc, stitle, sname, cname,w_date ORDER BY S_ID), * FROM #Source_Main_Query)
UPDATE d SET S_ID = x.S_ID
FROM Destination d
OUTER APPLY (
SELECTs.S_ID
FROMSource_Main_Query s
WHEREs.isc = d.isc
ANDs.stitle = d.stitle
ANDs.sname = d.sname
ANDs.cname = d.cname
ANDs.w_date = d.[weekday]
ANDd.stime BETWEEN s.startairtime AND s.endairtime
ANDs.rn = d.rn
) x
-- check the results
SELECT * FROM #Destination
[/code]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 20, 2017 at 8:32 am
Chris, I really APPRECIATE your help. Unfortunately, It is updating the same S_ID again and again and once I used that S_ID I don't want to update again.
Any advice would be a great help at this point!
April 20, 2017 at 9:03 am
rocky_498 - Thursday, April 20, 2017 8:32 AMChris, I really APPRECIATE your help. Unfortunately, It is updating the same S_ID again and again and once I used that S_ID I don't want to update again.Any advice would be a great help at this point!
It works perfectly with the sample data. Two possibilities: One, something got lost in translation between the query I posted and the query you are using, or Two, the sample data is not representative of the real data.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 20, 2017 at 2:36 pm
ChrisM@Work - Thursday, April 20, 2017 9:03 AMrocky_498 - Thursday, April 20, 2017 8:32 AMChris, I really APPRECIATE your help. Unfortunately, It is updating the same S_ID again and again and once I used that S_ID I don't want to update again.Any advice would be a great help at this point!
It works perfectly with the sample data. Two possibilities: One, something got lost in translation between the query I posted and the query you are using, or Two, the sample data is not representative of the real data.
>> I totally understand you are trying to helping me without knowing data and structure but I must say THANK YOU for your help in advance. If You know the best and perfect way to accomplish this scenario I would love to learn it from you.<<
Then you know that what you've asked of us is impossible. What little you did post is not even a table but a very bad deck of punch cards written in SQL. There is no such thing as a generic "id" in RDBMS; and identifier has to identify something in particular (ever hear of the law of identity in formal logic?). And of course it can never be a numeric data type; what's a square root of your credit card number? In fact, your column names seem to be horrible generic things that don't conform to ISO 11179 rules. You have prefixes that imply datatypes; a truly horrible error in design. Finally, if committed the design flaw known as "attribute splitting"; this is taking something that should have been one scalar value, like a timestamp, and putting it in two or more tables or two or more columns. In your case you took date and time out of the timestamp and put them in separate columns! There's no need to do this and it's absolutely wrong.
You then mix up a bunch of other terms like "link" (that's going back to the old network databases and assembly language!). SQL has references and joins. Those are different concepts and implementations.
If you can post valid DDL (things with keys, constraints, properly defined columns, etc.), then we can help you
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply