April 20, 2017 at 3:35 pm
Chris,
Once THANK YOU for your help on this. You are saving my life here 🙁
I tried my best to provide you everything and this is 100% same what I am doing.
FYI I am using two functions as well. Below is the code of those function, basically what I am doing.
SOURCE Table has field "d_rota = MO-FR, the function is checking the which day is "source_date" e.g source_date = 2017-04-10 00:00:00.000, and day is "MO" the function is checking MO is between MO-FR, if it is return 1 then yes if 0 mean no
Note:- In below example, 24 records should Link 2 records shouldn't Link ( #Destination.id in (2093852,2093851) shouldn't link. Once again Chris, to helping me you are saving my life. Thank You.
--DROP TABLE #Source_Main_Query
-- Function Start
CREATE FUNCTION [dbo].[fnIsValidAirDay]
(
@Days VARCHAR(50),
@AirDateTime DATETIME
)
RETURNS INT
AS
BEGIN
--Check if in day rotation
IF (CHARINDEX(UPPER(SUBSTRING(DATENAME(dw,
@AirDateTime),
1, 2)),
dbo.fngetFullDayString(@Days),
1) <= 0)
BEGIN
RETURN 0
END
RETURN 1
END
GO
CREATE FUNCTION [dbo].[fngetFullDayString]
(
@days VARCHAR(50)
)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @fullDayString VARCHAR(50)
SET @fullDayString = 'MO,TU,WE,TH,FR,SA,SU'
DECLARE
@startRangeDay VARCHAR(50),
@endRangeDay VARCHAR(50),
@betweenDayString VARCHAR(50),
@returnDayString VARCHAR(50),
@dashPosition INT,
@startPosition INT, --- starting position to use as replacement from @fullDayString
@endPosition INT --- end position to use as replacement from @fullDayString
--Look at the days string and see if there is a dash in it.
IF CHARINDEX('-',@days) > 0
BEGIN
--If there is a dash in the days, loop through finding each dash
--and replacing it with the comma separated days between
WHILE CHARINDEX('-',@days) > 0
BEGIN
SET @dashPosition = CHARINDEX('-',@days)
SET @startRangeDay = SUBSTRING(@days,@dashPosition-2,2)
SET @endRangeDay = SUBSTRING(@days,@dashPosition+1,2)
SET @startPosition = CHARINDEX(@startRangeDay, @fullDayString)
SET @endPosition = CHARINDEX(@endRangeDay, @fullDayString)+2
SET @betweenDayString = SUBSTRING(@fullDayString, @startPosition, @endPosition - @startPosition)
SET @days = REPLACE(@days, @startRangeDay + '-' + @endRangeDay,@betweenDayString)
END
END
SET @returnDayString = @days
-- Return the result of the function
RETURN @returnDayString
END
GO
-- FUNCTION END
--SELECT * FROM Source_Main_Query
CREATE TABLE #Source_Main_Query
(
isc VARCHAR(30),
s_id INT,
sname VARCHAR(30),
cname VARCHAR(30),
stitile VARCHAR(20),
w_date DATETIME,
d_rota VARCHAR(10),
startairtime datetime,
endairtime DATETIME,
rate DECIMAL(10,2)
)
INSERT INTO #Source_Main_Query
VALUES
( N'0002471703H', 55966811, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0002471703H', 55966812, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0002471703H', 55966815, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0002471703H', 55966823, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0002471703H', 55966821, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0002471703H', 55966826, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0002471703H', 55966813, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0002471703H', 55966830, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0002471703H', 55966820, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0002471703H', 55966828, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0002471703H', 55966825, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0002471703H', 55966816, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0002471703H', 55966824, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0002471703H', 55966829, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0002471703H', 55966819, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0002471703H', 55966818, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0002471703H', 55966822, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0002471703H', 55966817, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0002471703H', 55966827, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0002471703H', 55966814, N'NY1', N'FSIN', 56287, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022471711H', 56105258, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022471711H', 56105285, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022471711H', 56105283, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022471711H', 56105279, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022471711H', 56105261, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022471711H', 56105256, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022471711H', 56105259, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022471711H', 56105276, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022471711H', 56105284, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022471711H', 56105265, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022471711H', 56105257, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022471711H', 56105264, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022471711H', 56105263, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022471711H', 56105277, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022471711H', 56105278, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022471711H', 56105262, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022471711H', 56105260, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022471711H', 56105281, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022471711H', 56105282, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022471711H', 56105280, N'NY1', N'FSIN', 56448, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56050296, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56019516, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56050297, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56019517, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56019518, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56050299, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56019523, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56050298, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56019520, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56050300, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56019525, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56084631, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56019519, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56084634, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56019522, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56084626, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56084632, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56150219, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56019521, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56019524, N'NY1', N'FSIN', 56449, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 55966781, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 55966782, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 55966783, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 55966784, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 55966785, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 55966786, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 55966787, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 55966788, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 55966789, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 55966790, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 56132489, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56132490, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56150224, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56150225, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56150226, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56150227, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56150228, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56150229, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56150230, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56150231, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56150232, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56150233, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56150234, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56150235, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 56150236, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0022472606H', 55966771, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 55966772, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 55966773, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 55966774, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 55966775, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 55966776, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 55966777, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 55966778, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 55966779, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 55966780, N'NY1', N'FSIN', 56449, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56162288, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 56162289, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 56162290, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 56162291, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 56162292, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 56162293, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 56162294, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 56162295, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 56162296, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 56162297, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0022472606H', 56162278, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56162279, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56162280, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56162281, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56162282, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56162283, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56162284, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56162285, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56162286, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0022472606H', 56162287, N'NY1', N'FSIN', 56449, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56019526, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56019527, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56050301, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56050302, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56050304, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56050303, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56019532, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56019528, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56019530, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56069574, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56019534, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56050305, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56069575, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56019531, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56069576, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56019535, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56019533, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56069577, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56069573, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56019529, N'NY1', N'FSIN', 56450, N'2017-04-03 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56105251, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56105274, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56105271, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56105268, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56105247, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56105266, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56105246, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56105267, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56105275, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56105249, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56105252, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56105248, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56105250, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56105273, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56105254, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56105272, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56105269, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 56105255, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56105253, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-TH', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 56105270, N'NY1', N'FSIN', 56450, N'2017-04-10 00:00:00.000', N'MO-SU', N'1900-01-01 00:00:00.000', N'1900-01-01 23:59:00.000', 0.00 ),
( N'0024471709H', 55966741, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0024471709H', 55966742, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0024471709H', 55966743, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0024471709H', 55966744, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0024471709H', 55966745, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0024471709H', 55966746, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0024471709H', 55966747, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0024471709H', 55966748, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0024471709H', 55966749, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0024471709H', 55966750, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 0.00 ),
( N'0024471709H', 55966721, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966722, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966723, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966724, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966725, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966726, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966727, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966728, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966729, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966730, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966731, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966732, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966733, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966734, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966735, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966736, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966737, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966738, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966739, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 ),
( N'0024471709H', 55966740, N'NY1', N'FSIN', 56450, N'2017-04-17 00:00:00.000', N'MO-FR', N'1900-01-01 09:00:00.000', N'1900-01-01 23:00:00.000', 100.00 )
Create table #Destination
(
ID INT,
source_date DATETIME,
isc varchar(25),
cname varchar(25),
stitle int,
sname varchar(20),
weekday datetime,
stime datetime,
S_ID INT
)
INSERT INTO #Destination (id,source_date,isc,cname,stitle,sname,weekday,stime)
VALUES
( 2093827, N'2017-04-10 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 10:39:00.000' ),
( 2093828, N'2017-04-11 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 13:40:00.000' ),
( 2093829, N'2017-04-12 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 18:39:00.000' ),
( 2093830, N'2017-04-13 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 22:26:00.000' ),
( 2093831, N'2017-04-10 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 18:46:00.000' ),
( 2093832, N'2017-04-11 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 22:34:00.000' ),
( 2093833, N'2017-04-12 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 12:28:00.000' ),
( 2093834, N'2017-04-14 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 10:45:00.000' ),
( 2093835, N'2017-04-10 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 19:30:00.000' ),
( 2093836, N'2017-04-11 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 22:46:00.000' ),
( 2093837, N'2017-04-12 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 09:45:00.000' ),
( 2093838, N'2017-04-13 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 12:36:00.000' ),
( 2093839, N'2017-04-14 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 16:44:00.000' ),
( 2093840, N'2017-04-10 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 21:26:00.000' ),
( 2093841, N'2017-04-11 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 11:39:00.000' ),
( 2093842, N'2017-04-12 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 14:28:00.000' ),
( 2093843, N'2017-04-13 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 17:39:00.000' ),
( 2093844, N'2017-04-14 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 20:40:00.000' ),
( 2093845, N'2017-04-16 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 17:57:00.000' ),
( 2093846, N'2017-04-10 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 21:10:00.000' ),
( 2093847, N'2017-04-11 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 11:48:00.000' ),
( 2093848, N'2017-04-12 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 14:45:00.000' ),
( 2093849, N'2017-04-13 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 18:45:00.000' ),
( 2093850, N'2017-04-14 00:00:00.000', N'0022472606H', N'FSIN', 56449, N'NY1', N'2017-04-10 00:00:00.000', N'1900-01-01 20:54:00.000' ),
( 2093851, N'2017-04-15 00:00:00.000', NULL, NULL, NULL, N'MT', N'2017-04-10 00:00:00.000', N'1900-01-01 20:54:00.000' ),
( 2093852, N'2017-04-16 00:00:00.000', NULL, NULL, NULL, N'BRAVO', N'2017-04-10 00:00:00.000', N'1900-01-01 20:54:00.000' )
SELECT * FROM #Destination
WHERE id IN (2093852,2093851)
SELECT TOP 1 * FROM #Source_Main_Query
;WITH
Destination AS (SELECT rn = ROW_NUMBER() OVER (PARTITION BY isc, stitle, sname, cname, [weekday]ORDER BY stime), * FROM #Destination),
Source_Main_Query AS (SELECT rn = ROW_NUMBER() OVER (PARTITION BY isc, stitile, 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*
FROM Source_Main_Query s
WHERE s.isc = d.isc
AND s.stitile = d.stitle
AND s.sname = d.sname
AND s.cname = d.cname
AND s.w_date = d.[weekday]
AND (dbo.[fnIsValidAirDay]( s.d_rota,d.source_date) = 1)
AND d.stime BETWEEN startairtime AND endairtime
AND s.rn = d.rn
) x
ORDER BY d.isc, d.stitle, d.sname, d.cname, d.[weekday],stime
-- Part 2: Perform the update
;WITH
Destination AS (SELECT rn = ROW_NUMBER() OVER (PARTITION BY isc, stitle, sname, cname,[weekday] ORDER BY stime), * FROM #Destination),
Source_Main_Query AS (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
'
April 20, 2017 at 4:30 pm
jcelko212 32090 - Thursday, April 20, 2017 2:36 PMChrisM@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
Be quiet Joe, and go crawl back into your Ivory Tower. We don't need all that crap to help the user, and they don't need it either.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 20, 2017 at 5:45 pm
Joe,
Just for FYI, I already mentioned, I am sending Live/Sample data. If you are working on Healthcare or any other environment, the company policy is to not share 100% structure/data to anyone. That is why I have to tweak it a little bit. I hope this explains a lot.
P.S THANK YOU Kevin AKA "The SQL Guru" for standing up for what's right.
April 21, 2017 at 5:16 am
rocky_498 - Thursday, April 20, 2017 5:45 PMJoe,
Just for FYI, I already mentioned, I am sending Live/Sample data. If you are working on Healthcare or any other environment, the company policy is to not share 100% structure/data to anyone. That is why I have to tweak it a little bit. I hope this explains a lot.BTW thanks for making a comment on the issue....... 🙁P.S THANK YOU Kevin AKA "The SQL Guru" for standing up for what's right.
Here's something which is intended to replace those funky UDF's, and a little test harness to explore how it all works:;WITH SampleData AS (SELECT d_rota = 'MO-TH', source_date = CAST(GETDATE()-n AS DATE)
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) x (n))
SELECT
d_rota, DayNumberStart, DayNumberEnd,
source_date, [SourceDayNumber], [Day Name] = DATENAME(DW,source_date)
FROM SampleData d
CROSS APPLY ( -- This block could replace both UDFs
-- Translate d_rota into a day number range, where monday = 1
-- Calculate day number of source_date, where monday = 1
SELECT
DayNumberStart = (CHARINDEX(LEFT(d_rota,2),'MOTUWETHFRSASU')+1)/2,
DayNumberEnd = (CHARINDEX(RIGHT(d_rota,2),'MOTUWETHFRSASU')+1)/2,
[SourceDayNumber] = (DATEDIFF(DAY,0,source_date)%7)+1
) x1
WHERE x1.[SourceDayNumber] BETWEEN x1.DayNumberStart AND x1.DayNumberEnd
And here's how I'd incorporate it into your code to achieve the same filter as the original:;WITH
Destination AS (
SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY isc, stitle, sname, cname, [weekday] ORDER BY stime),
[SourceDayNumber] = (DATEDIFF(DAY,0,source_date)%7)+1
FROM #Destination),
Source_Main_Query AS (
SELECT rn = ROW_NUMBER() OVER (PARTITION BY isc, stitile, sname, cname, w_date ORDER BY S_ID),
DayNumberStart = (CHARINDEX(LEFT(d_rota,2),'MOTUWETHFRSASU')+1)/2,
DayNumberEnd = (CHARINDEX(RIGHT(d_rota,2),'MOTUWETHFRSASU')+1)/2,
*
FROM #Source_Main_Query)
SELECT
d.*,
'#' '#', -- this is a visual divider between columns from Destination and those from Source_Main_Query
x.*
FROM Destination d
OUTER APPLY (
SELECT *
FROM Source_Main_Query s
WHERE s.isc = d.isc
AND s.stitile = d.stitle
AND s.sname = d.sname
AND s.cname = d.cname
AND s.w_date = d.[weekday]
AND d.stime BETWEEN s.startairtime AND s.endairtime
AND d.[SourceDayNumber] BETWEEN s.DayNumberStart AND s.DayNumberEnd
AND s.rn = d.rn
) x
ORDER BY d.isc, d.stitle, d.sname, d.cname, d.[weekday],stime
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 21, 2017 at 6:48 am
ChrisM@Work - Friday, April 21, 2017 5:16 AMrocky_498 - Thursday, April 20, 2017 5:45 PMJoe,
Just for FYI, I already mentioned, I am sending Live/Sample data. If you are working on Healthcare or any other environment, the company policy is to not share 100% structure/data to anyone. That is why I have to tweak it a little bit. I hope this explains a lot.BTW thanks for making a comment on the issue....... 🙁P.S THANK YOU Kevin AKA "The SQL Guru" for standing up for what's right.
Here's something which is intended to replace those funky UDF's, and a little test harness to explore how it all works:
;WITH SampleData AS (SELECT d_rota = 'MO-TH', source_date = CAST(GETDATE()-n AS DATE)
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) x (n))SELECT
d_rota, DayNumberStart, DayNumberEnd,
source_date, [SourceDayNumber], [Day Name] = DATENAME(DW,source_date)FROM SampleData d
CROSS APPLY ( -- This block could replace both UDFs
-- Translate d_rota into a day number range, where monday = 1
-- Calculate day number of source_date, where monday = 1
SELECT
DayNumberStart = (CHARINDEX(LEFT(d_rota,2),'MOTUWETHFRSASU')+1)/2,
DayNumberEnd = (CHARINDEX(RIGHT(d_rota,2),'MOTUWETHFRSASU')+1)/2,
[SourceDayNumber] = (DATEDIFF(DAY,0,source_date)%7)+1) x1
WHERE x1.[SourceDayNumber] BETWEEN x1.DayNumberStart AND x1.DayNumberEnd
And here's how I'd incorporate it into your code to achieve the same filter as the original:
;WITH
Destination AS (
SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY isc, stitle, sname, cname, [weekday] ORDER BY stime),
[SourceDayNumber] = (DATEDIFF(DAY,0,source_date)%7)+1
FROM #Destination),
Source_Main_Query AS (
SELECT rn = ROW_NUMBER() OVER (PARTITION BY isc, stitile, sname, cname, w_date ORDER BY S_ID),
DayNumberStart = (CHARINDEX(LEFT(d_rota,2),'MOTUWETHFRSASU')+1)/2,
DayNumberEnd = (CHARINDEX(RIGHT(d_rota,2),'MOTUWETHFRSASU')+1)/2,
*
FROM #Source_Main_Query)SELECT
d.*,
'#' '#', -- this is a visual divider between columns from Destination and those from Source_Main_Query
x.*
FROM Destination d
OUTER APPLY (
SELECT *
FROM Source_Main_Query s
WHERE s.isc = d.isc
AND s.stitile = d.stitle
AND s.sname = d.sname
AND s.cname = d.cname
AND s.w_date = d.[weekday]
AND d.stime BETWEEN s.startairtime AND s.endairtime
AND d.[SourceDayNumber] BETWEEN s.DayNumberStart AND s.DayNumberEnd
AND s.rn = d.rn
) x
ORDER BY d.isc, d.stitle, d.sname, d.cname, d.[weekday],stime
I don't have time to test for validation Chris, but that sure looks like a very elegant solution!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 21, 2017 at 7:52 am
Haha thanks Kevin - but I'll wait and see if it works for the OP before pulling a pint 😉
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 21, 2017 at 12:29 pm
Chris,
It's a HUGE effort that you doing. Highly appreciate. However, from the test/live data example I am still missing some records to Link For some reason, I am still missing few. I just want to confirm it is just on my side or ur side as well? mean I am running the same query against test data. How many records are Linked on your end? and some of them are Linking but should link to Higher Rate
For example, if Source_id 123 they have potential Link to destination table
April 21, 2017 at 1:27 pm
rocky_498 - Friday, April 21, 2017 12:29 PMChris,
It's a HUGE effort that you doing. Highly appreciate. However, from the test/live data example I am still missing some records to Link For some reason, I am still missing few. I just want to confirm it is just on my side or ur side as well? mean I am running the same query against test data. How many records are Linked on your end? and some of them are Linking but should link to Higher Rate
For example, if Source_id 123 they have potential Link to destination tables_id, rate456,0.00896,0.00741,100.00544,100.00So in this example, It should Link to s_id 544 becausea) s_id 741 and 544 has 100.00 then always pick lowest s_id 544Sorry for this hassle and crazy logics. But I can we are one step closer.Once again THANK YOU!
This is a new requirement. Why not try it yourself? Change the ORDER BY of the ROW_NUMBER. Whether or not it works, post up your solution (against the sample data). If it doesn't work, explain very carefully why not. Cheers.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 22, 2017 at 2:03 pm
Hi Chris,
Sorry for the late reply. Just want to give you an update. It is working 95% fine but I am still missing 2 records to be linked. I want to take this opportunity and want to say Thank You for your time and help from last few days. If I come up with some kind of solution I will definitely let you know. I was thinking, is it make sense or even give a try If I use While or Cursor to pull that data what I need from two tables? or shouldn't waste my time to even try. Just want to know your experience advice.
April 24, 2017 at 4:51 am
rocky_498 - Saturday, April 22, 2017 2:03 PMHi Chris,
Sorry for the late reply. Just want to give you an update. It is working 95% fine but I am still missing 2 records to be linked. I want to take this opportunity and want to say Thank You for your time and help from last few days. If I come up with some kind of solution I will definitely let you know. I was thinking, is it make sense or even give a try If I use While or Cursor to pull that data what I need from two tables? or shouldn't waste my time to even try. Just want to know your experience advice.Thank You.
While loops/cursors should be your very last resort for this type of problem - I'm absolutely sure that there is a set-based solution for you.
You say that you have two rows which remain unlinked.
1. Have you checked that there are matching rows available in the source table?
2. Assuming that matching rows are available for these two rows in the target table, have you checked to see whether or not they are all used up on previous rows in the target table?
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 24, 2017 at 9:47 pm
Hi Chris,
Here is the answer to your questions.
1) Have you checked that there are matching rows available in the source table? = Yes there is matching rows available
2. Assuming that matching rows are available for these two rows in the target table, have you checked to see whether or not they are all used up on previous rows in the target table? = Matching IDs never used it before.
I used Cursor and it is working absolutely fine. For some reason WHILE Loop is not working for me.
April 25, 2017 at 6:25 am
rocky_498 - Monday, April 24, 2017 9:47 PMHi Chris,
Here is the answer to your questions.1) Have you checked that there are matching rows available in the source table? = Yes there is matching rows available
2. Assuming that matching rows are available for these two rows in the target table, have you checked to see whether or not they are all used up on previous rows in the target table? = Matching IDs never used it before.I used Cursor and it is working absolutely fine. For some reason WHILE Loop is not working for me.
Once again THANK YOU for your help and time. I learned a lot of your codes. Really appreciate all of your help.
Ordering the two sets by the calculated day of the week eliminates the missing rows. Give this a try:;WITH
Destination AS (
SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY isc, stitle, sname, cname, [weekday] ORDER BY [SourceDayNumber] DESC, stime)
FROM (SELECT *, [SourceDayNumber] = (DATEDIFF(DAY,0,source_date)%7)+1 FROM #Destination) d),
Source_Main_Query AS (
SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY isc, stitile, sname, cname, w_date ORDER BY Rate ASC, DayNumberEnd DESC, S_ID)
FROM (SELECT *,
DayNumberStart = (CHARINDEX(LEFT(d_rota,2),'MOTUWETHFRSASU')+1)/2,
DayNumberEnd = (CHARINDEX(RIGHT(d_rota,2),'MOTUWETHFRSASU')+1)/2 FROM #Source_Main_Query) d)
SELECT
d.*,
'#' '#', -- this is a visual divider between columns from Destination and those from Source_Main_Query
x.*
FROM Destination d
OUTER APPLY (
SELECT DayNumberStart, DayNumberEnd, rn, S_ID, d_rota, startairtime, endairtime, rate
FROM Source_Main_Query s
WHERE s.isc = d.isc
AND s.stitile = d.stitle
AND s.sname = d.sname
AND s.cname = d.cname
AND s.w_date = d.[weekday]
AND d.stime BETWEEN startairtime AND endairtime
AND d.[SourceDayNumber] BETWEEN s.DayNumberStart AND s.DayNumberEnd
AND s.rn = d.rn
) x
ORDER BY d.isc, d.rn
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 25, 2017 at 3:20 pm
Chris,
Looks great! Now I am getting 24 records. However, the problem is I am getting all 24 IDs with lower "Rate". I should get all higher rate ids first then the lower rate. If I change the Order By Rate asc to Order By Rate Desc. I am missing a lot of records.
Thank You for the help!
April 25, 2017 at 3:39 pm
Looking through this thread it is hard to determine, but have you ever provided the expected results based on the sample data provided?
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply