SQLTeam.com | Weblogs | Forums

SQL Query Help


#1

Here is my sample data

CREATE TABLE ##Source_table1
(
s_isc VARCHAR(20)
,s_sta VARCHAR(20)
,s_rate DECIMAL
,s_weekday DATE
,s_starttime DATETIME
,s_endtime DATETIME
,s_key int

)

INSERT INTO ##Source_table1
( s_isc ,
s_sta ,
s_rate ,
s_weekday ,
s_starttime ,
s_endtime,
s_key
)

SELECT '000147349','SPR','840.00','2017-04-03 00:00:00.000','1900-01-01 09:00:00.000','1900-01-01 18:00:00.000','123456'
UNION
SELECT '000147349','SPR','840.00','2017-04-03 00:00:00.000','1900-01-01 09:00:00.000','1900-01-01 18:00:00.000','123457'
UNION
SELECT '000147349','SPR','840.00','2017-04-03 00:00:00.000','1900-01-01 09:00:00.000','1900-01-01 18:00:00.000','123458'
UNION
SELECT '000147349','SPR','840.00','2017-04-03 00:00:00.000','1900-01-01 09:00:00.000','1900-01-01 18:00:00.000','123452'

CREATE TABLE ##dest_table2
(
d_isc VARCHAR(20)
,d_sta VARCHAR(20)
,d_rate DECIMAL
,d_date DATE
,d_time DATETIME
,d_s_key INT
,d_id int
)

INSERT INTO ##dest_table2
( d_isc ,
d_sta ,
d_rate ,
d_date ,
d_time,
d_id
)

SELECT '000147349','SPR','840.00','2017-04-03 00:00:00.000','2017-04-03 10:27:40.000','123'
UNION
SELECT '000147349','SPR','840.00','2017-04-03 00:00:00.000','2017-04-03 11:28:25.000','456'
UNION
SELECT '000147349','SPR','840.00','2017-04-03 00:00:00.000','2017-04-04 10:27:55.000','789'

/*
SELECT * FROM Source_table1
SELECT * FROM dest_table2
*/

SELECT
S1.s_key,D2.d_id
FROM ##Source_table1 S1
INNER JOIN ##dest_table2 D2 ON
S1.s_isc = D2.d_isc
AND S1.s_sta = D2.d_sta
AND S1.s_rate = D2.d_rate
AND S1.s_weekday = DATEADD(wk, DATEDIFF(wk,0, D2.d_date ), 0)
AND CONVERT(VARCHAR(5),D2.d_time ,108) BETWEEN CONVERT(VARCHAR(5),S1.s_starttime,108) AND CONVERT(VARCHAR(5),S1.s_endtime ,108)

UPDATE ##dest_table2
SET d_s_key = S1.s_key
FROM ##dest_table2 D2
INNER JOIN ##Source_table1 S1 ON
S1.s_isc = D2.d_isc
AND S1.s_sta = D2.d_sta
AND S1.s_rate = D2.d_rate
AND S1.s_weekday = DATEADD(wk, DATEDIFF(wk,0, D2.d_date ), 0)
AND CONVERT(VARCHAR(5),D2.d_time ,108) BETWEEN CONVERT(VARCHAR(5),S1.s_starttime,108) AND CONVERT(VARCHAR(5),S1.s_endtime ,108)

/*
DROP TABLE ##dest_table2
DROP TABLE ##Source_table1
*/

If you run above SQL it will create a two temp table. What I want I want to Update "dest_table2.d_s_key" from "source_table1" Because of the linking logic I am using
I am getting a duplicate. That's what I am getting after I run update statement

d_isc d_sta d_rate d_date d_time d_s_key d_id
000147349 SPR 840 2017-04-03 2017-04-03 10:27:40.000 123452 123
000147349 SPR 840 2017-04-03 2017-04-03 11:28:25.000 123452 456
000147349 SPR 840 2017-04-03 2017-04-04 10:27:55.000 123452 789

What I want to, if I already use 12352 id I don't want to use again.

Please help me out to fix this issue.


#2

For UPDATE to work reliably there needs to be a one to one match in the JOIN.
From you data, it seems you need the relative order of each match.
Try something like:

WITH Orders
AS
(
SELECT D2.d_id, S1.s_key, D2.d_s_key
	,DENSE_RANK() OVER (PARTITION BY S1.s_isc, S1.s_sta, S1.s_rate, S1.s_weekday ORDER BY S1.s_key) AS SourceOrder
	,DENSE_RANK() OVER (PARTITION BY S1.s_isc, S1.s_sta, S1.s_rate, S1.s_weekday ORDER BY D2.d_time) AS DestOrder
FROM ##Source_table1 S1
	JOIN ##dest_table2 D2
		ON S1.s_isc = D2.d_isc
		AND S1.s_sta = D2.d_sta
		AND S1.s_rate = D2.d_rate
		AND S1.s_weekday = DATEADD(wk, DATEDIFF(wk,0, D2.d_date ), 0)
		AND DATEADD(minute, DATEDIFF(minute, DATEDIFF(day, 0, D2.d_time), D2.d_time), 0)
			BETWEEN S1.s_starttime AND S1.s_endtime
)
--select *
--from ##dest_table2 D
--	join Orders O
--		on D.d_id = O.d_id
--where O.SourceOrder = O.DestOrder;
UPDATE Orders
SET d_s_key = s_key
WHERE SourceOrder = DestOrder;

#3

Hi lfor,

Sorry for late reply. It works. Thank you so much for your help. I will update if I have any questions.

Thank You!


#4

Hi Ilfor,

I used above solution, it works fine but I am missing a lot of records. I will upload some test data for testing. If you can help out would really appreciate.

Thank You


#5

Here is LIVE/Test data. 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'