November 12, 2018 at 9:05 am
There is a problem with the way your script inserts the dates. It just enters them as text so they are not sortable by date, the same applies to the "Ref" column, which is stored as text not an int. But you can use this query get the data you want (provided your dates and 'Ref' sort correctly).;WITH CTE AS (SELECT DISTINCT l.Dealer, l.DescR, l.Attempt FROM #Log l)
SELECT CTE.Dealer, CTE.DescR, CTE.Attempt, l.Ref, l.Salute,l.Updated_Date, l.Cont_date, l.Compcode
FROM CTE
CROSS APPLY(SELECT TOP(2) *
FROM #Log l
WHERE l.Dealer = CTE.Dealer
AND l.DescR = CTE.DescR
AND l.Attempt = CTE.Attempt
ORDER BY Cont_Date) l
ORDER BY l.Ref
November 12, 2018 at 9:12 am
Amazing thank you very much
November 12, 2018 at 9:15 am
An alternative is to use windowed functions:;WITH l AS
(
SELECT l.Dealer, l.DescR, l.Attempt, l.Ref, l.Salute,l.Updated_Date, l.Cont_date, l.Compcode,
ROW_NUMBER() OVER (PARTITION BY l.Dealer, l.DescR, l.Attempt ORDER BY l.Cont_date) RowNum
FROM #Log l
)
SELECT l.Dealer, l.DescR, l.Attempt, l.Ref, l.Salute,l.Updated_Date, l.Cont_date, l.Compcode
FROM l
WHERE l.RowNum <=2
ORDER BY l.Ref
November 14, 2018 at 6:02 am
Thanks so much for this Jon. I've have a few joins in the live data is there a way to incorporate what you did above into the below 'Query 1'. Many thanks
IF OBJECT_ID('tempdb..#Contacts') IS NOT NULL DROP TABLE #Contacts
SELECT * INTO #Contacts FROM (VALUES
( 'BAA', 'SE1', '1', 'Mr Jones 1', CAST('20180626' as datetime), CAST('20181107' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181107' as datetime), '1', '0'),
( 'BAA', 'SE1', '2', 'Mr Jones 2', CAST('20180627' as datetime), CAST('20180606' as datetime), 'CUA', CAST('' as datetime), '0', '1', CAST('20180630' as datetime), '2', '4021'),
( 'BAA', 'SE1', '3', 'Mr Jones 3', CAST('' as datetime), CAST('20181031' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181031' as datetime), '1', '0'),
( 'BAA', 'MO1', '4', 'Mr Jones 4', CAST('' as datetime), CAST('20181031' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181031' as datetime), '1', '0'),
( 'BAA', 'GMO', '5', 'Mr Jones 5', CAST('' as datetime), CAST('20181105' as datetime), '', CAST('' as datetime), '0', 'MOC', CAST('20181105' as datetime), '1', '0'),
( 'BAA', 'SE1', '6', 'Mr Jones 6', CAST('' as datetime), CAST('20181107' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181107' as datetime), '1', '0'),
( 'BAA', 'SE1', '7', 'Mr Jones 7', CAST('20180801' as datetime), CAST('20180801' as datetime), 'LMS', CAST('' as datetime), '0', '1', CAST('20180804' as datetime), '2', '4013'),
( 'BAA', 'SE2', '8', 'Mr Jones 8', CAST('' as datetime), CAST('20181114' as datetime), '', CAST('' as datetime), '0', 'RT2', CAST('20181114' as datetime), '1', '0'),
( 'BAA', 'SE1', '9', 'Mr Jones 9', CAST('' as datetime), CAST('20181107' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181107' as datetime), '1', '0'),
( 'BAA', 'SE1', '10', 'Mr Jones 10', CAST('' as datetime), CAST('20181114' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181114' as datetime), '1', '0'),
( 'BAA', 'PAS', '11', 'Mr Jones 11', CAST('20180918' as datetime), CAST('20180829' as datetime), 'LMS', CAST('' as datetime), '0', '1', CAST('20180921' as datetime), '2', '310'),
( 'BAA', 'SE1', '12', 'Mr Jones 12', CAST('20180509' as datetime), CAST('20180425' as datetime), 'LMS', CAST('' as datetime), '0', '1', CAST('20180512' as datetime), '2', '4013'),
( 'BAA', 'CAM', '13', 'Mr Jones 13', CAST('20181107' as datetime), CAST('20180926' as datetime), 'CUA', CAST('' as datetime), '0', '1', CAST('20181110' as datetime), '2', '310'),
( 'BAA', 'SE1', '14', 'Mr Jones 14', CAST('' as datetime), CAST('20181114' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181114' as datetime), '1', '0'),
( 'BAA', 'SE2', '15', 'Mr Jones 15', CAST('20180822' as datetime), CAST('20180620' as datetime), 'CUA', CAST('' as datetime), '0', '2', CAST('20180825' as datetime), '3', '551'),
( 'BAA', 'SE1', '16', 'Mr Jones 16', CAST('20180905' as datetime), CAST('20180905' as datetime), 'LMS', CAST('' as datetime), '0', '1', CAST('20180908' as datetime), '2', '4013'),
( 'BAA', 'SE1', '17', 'Mr Jones 17', CAST('20181004' as datetime), CAST('20181003' as datetime), 'LMS', CAST('' as datetime), '0', '1', CAST('20181007' as datetime), '2', '4013'),
( 'BAA', 'MO1', '18', 'Mr Jones 18', CAST('' as datetime), CAST('20181107' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181107' as datetime), '1', '0'),
( 'BAA', 'CAM', '19', 'Mr Jones 19', CAST('20180927' as datetime), CAST('20180926' as datetime), 'LMS', CAST('' as datetime), '0', '1', CAST('20180930' as datetime), '2', '310'),
( 'BAA', 'MO1', '20', 'Mr Jones 20', CAST('' as datetime), CAST('20181107' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181107' as datetime), '1', '0'),
( 'BAA', 'PWM', '21', 'Mr Jones 21', CAST('20181031' as datetime), CAST('20181029' as datetime), 'CCB', CAST('' as datetime), '0', 'CCB', CAST('20181114' as datetime), '1', '550'),
( 'BAA', 'PAS', '22', 'Mr Jones 22', CAST('20181108' as datetime), CAST('20181001' as datetime), 'CUA', CAST('' as datetime), '0', '1', CAST('20181111' as datetime), '2', '310'),
( 'BAA', 'MO1', '23', 'Mr Jones 23', CAST('' as datetime), CAST('20181107' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181107' as datetime), '1', '0'),
( 'BAA', 'MO1', '24', 'Mr Jones 24', CAST('20180712' as datetime), CAST('20180704' as datetime), 'CUA', CAST('' as datetime), '0', '1', CAST('20180715' as datetime), '2', '4013'),
( 'BAA', 'MO1', '25', 'Mr Jones 25', CAST('' as datetime), CAST('20181107' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181107' as datetime), '1', '0'),
( 'BAA', 'MO1', '26', 'Mr Jones 26', CAST('' as datetime), CAST('20181031' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181031' as datetime), '1', '0'),
( 'BAA', 'SE2', '27', 'Mr Jones 27', CAST('20180824' as datetime), CAST('20180822' as datetime), 'CUA', CAST('' as datetime), '0', '1', CAST('20180827' as datetime), '2', '4013'),
( 'BAA', 'SE2', '28', 'Mr Jones 28', CAST('20180824' as datetime), CAST('20180822' as datetime), 'LMS', CAST('' as datetime), '0', '1', CAST('20180827' as datetime), '2', '4013'),
( 'BAA', 'MS1', '29', 'Mr Jones 29', CAST('' as datetime), CAST('20181031' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181031' as datetime), '1', '0'),
( 'BAA', 'SE1', '30', 'Mr Jones 30', CAST('20180628' as datetime), CAST('20180620' as datetime), 'CUA', CAST('' as datetime), '0', '1', CAST('20180701' as datetime), '2', '4013'),
( 'BAA', 'MO1', '31', 'Mr Jones 31', CAST('' as datetime), CAST('20181031' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181031' as datetime), '1', '0'),
( 'BAA', 'PWM', '32', 'Mr Jones 32', CAST('' as datetime), CAST('20181113' as datetime), '', CAST('' as datetime), '0', 'RWM', CAST('20181113' as datetime), '1', '0'),
( 'BAA', 'SE2', '33', 'Mr Jones 33', CAST('20181012' as datetime), CAST('20181003' as datetime), 'CUA', CAST('' as datetime), '0', '1', CAST('20181015' as datetime), '2', '4013'),
( 'BAA', 'MS1', '34', 'Mr Jones 34', CAST('20180620' as datetime), CAST('20180523' as datetime), 'LMS', CAST('' as datetime), '0', '1', CAST('20180623' as datetime), '2', '4013'),
( 'BAA', 'SE2', '35', 'Mr Jones 35', CAST('20180713' as datetime), CAST('20180711' as datetime), 'CUA', CAST('' as datetime), '0', '1', CAST('20180716' as datetime), '2', '4021'),
( 'BAA', 'SE1', '36', 'Mr Jones 36', CAST('' as datetime), CAST('20181114' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181114' as datetime), '1', '0'),
( 'BAA', 'SE1', '37', 'Mr Jones 37', CAST('20180425' as datetime), CAST('20180103' as datetime), 'CCB', CAST('' as datetime), '0', 'CCB', CAST('20180509' as datetime), '2', '4021'),
( 'BAA', 'MO1', '38', 'Mr Jones 38', CAST('' as datetime), CAST('20181107' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181107' as datetime), '1', '0'),
( 'BAA', 'MO1', '39', 'Mr Jones 39', CAST('' as datetime), CAST('20181114' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181114' as datetime), '1', '0'),
( 'BAA', 'MO1', '40', 'Mr Jones 40', CAST('20181024' as datetime), CAST('20181017' as datetime), 'CUA', CAST('' as datetime), '0', '1', CAST('20181027' as datetime), '2', '550'),
( 'BAA', 'PWB', '41', 'Mr Jones 41', CAST('20181031' as datetime), CAST('20181029' as datetime), 'CUA', CAST('' as datetime), '0', '1', CAST('20181103' as datetime), '2', '550'),
( 'BAA', 'SCM', '42', 'Mr Jones 42', CAST('' as datetime), CAST('20181022' as datetime), '', CAST('' as datetime), '0', 'R2M', CAST('20181022' as datetime), '1', '0'),
( 'BAA', 'PWB', '43', 'Mr Jones 43', CAST('20181010' as datetime), CAST('20181008' as datetime), 'LMS', CAST('' as datetime), '0', '1', CAST('20181013' as datetime), '2', '4013'),
( 'BAA', 'PAS', '44', 'Mr Jones 44', CAST('20181108' as datetime), CAST('20181015' as datetime), 'CUA', CAST('' as datetime), '0', '1', CAST('20181111' as datetime), '2', '310'),
( 'BAA', 'PAS', '45', 'Mr Jones 45', CAST('20180918' as datetime), CAST('20180829' as datetime), 'CUA', CAST('' as datetime), '0', '1', CAST('20180921' as datetime), '2', '310'),
( 'BAA', 'SE1', '46', 'Mr Jones 46', CAST('' as datetime), CAST('20181107' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181107' as datetime), '1', '0'),
( 'BAA', 'CAM', '47', 'Mr Jones 47', CAST('20181004' as datetime), CAST('20180926' as datetime), 'LMS', CAST('' as datetime), '0', '1', CAST('20181007' as datetime), '2', '310'),
( 'BAA', 'CAM', '48', 'Mr Jones 48', CAST('20181017' as datetime), CAST('20180926' as datetime), 'LMS', CAST('' as datetime), '0', '1', CAST('20181020' as datetime), '2', '490'),
( 'BAA', 'PWS', '49', 'Mr Jones 49', CAST('20181109' as datetime), CAST('20181105' as datetime), 'LMS', CAST('' as datetime), '0', '1', CAST('20181112' as datetime), '2', '310'),
( 'BAA', 'MO1', '50', 'Mr Jones 50', CAST('' as datetime), CAST('20181107' as datetime), '', CAST('' as datetime), '0', 'RT1', CAST('20181107' as datetime), '1', '0')
) d
( Dealer, Contcode, Salute, Regno, Updated, ContDate, Completecode, Complete, InUse, FollCode, FollDate, Grop,OpNum)
-----------
IF OBJECT_ID('tempdb..#Dealers2') IS NOT NULL DROP TABLE #Dealers2
SELECT * INTO #Dealers2 FROM (VALUES
( 'BA', 'Bristol'),
( 'BAA', 'Bath'),
( 'BF', 'Brecon'),
( 'BVW', 'Bath2'),
( 'CA', 'Cardiff'),
( 'CF', 'Cwmbran'),
( 'CHF', 'Chepstow'),
( 'CVW', 'Merthyr'),
( 'MCH', 'Chippenham'),
( 'NB', 'Newport2'),
( 'NF', 'Newport'),
( 'NSK', 'Newport3')
) d
( Dealer, Name )
------
IF OBJECT_ID('tempdb..#ContactParms') IS NOT NULL DROP TABLE #ContactParms
SELECT * INTO #ContactParms FROM (VALUES
( 'BAA', 'SE1', 'SE1','RT1'),
( 'BAA', 'SE1','SE1','RT1'),
( 'BAA', 'SE1','SE1','RT1'),
( 'BAA', 'MO1','MO1','RT1'),
( 'BAA', 'GMO','GMO','MOC'),
( 'BAA', 'SE1','SE1','RT1'),
( 'BAA', 'SE1','SE1','RT1'),
( 'BAA', 'SE2','SE2','RT2'),
( 'BAA', 'SE1','SE1','RT1'),
( 'BAA', 'SE1','SE1','RT1'),
( 'BAA', 'PAS','PAS','RAS'),
( 'BAA', 'SE1','SE1','RT1'),
( 'BAA', 'CAM','CAM','TIM'),
( 'BAA', 'SE1','SE1','RT1'),
( 'BAA', 'SE2','SE2','RT2'),
( 'BAA', 'SE1','SE1','RT1'),
( 'BAA', 'SE1','SE1','RT1'),
( 'BAA', 'MO1','MO1','RT1'),
( 'BAA', 'CAM','CAM','TIM'),
( 'BAA', 'MO1','MO1','RT1'),
( 'BAA', 'PWM','PWM','RWM'),
( 'BAA', 'PAS','PAS','RAS'),
( 'BAA', 'MO1','MO1','RT1'),
( 'BAA', 'MO1','MO1','RT1'),
( 'BAA', 'MO1','MO1','RT1'),
( 'BAA', 'MO1','MO1','RT1'),
( 'BAA', 'SE2','SE2','RT2'),
( 'BAA', 'SE2','SE2','RT2'),
( 'BAA', 'MS1','MS1','RT1'),
( 'BAA', 'SE1','SE1','RT1'),
( 'BAA', 'MO1','MO1','RT1'),
( 'BAA', 'PWM','PWM','RWM'),
( 'BAA', 'SE2','SE2','RT2'),
( 'BAA', 'MS1','MS1','RT1'),
( 'BAA', 'SE2','SE2','RT2'),
( 'BAA', 'SE1','SE1','RT1'),
( 'BAA', 'SE1','SE1','RT1'),
( 'BAA', 'MO1','MO1','RT1'),
( 'BAA', 'MO1','MO1','RT1'),
( 'BAA', 'MO1','MO1','RT1'),
( 'BAA', 'PWB','PWB','RBW'),
( 'BAA', 'SCM','SCM','R2M'),
( 'BAA', 'PWB','PWB','RBW'),
( 'BAA', 'PAS','PAS','RAS'),
( 'BAA', 'PAS','PAS','RAS'),
( 'BAA', 'SE1','SE1','RT1'),
( 'BAA', 'CAM','CAM','TIM'),
( 'BAA', 'CAM','CAM','TIM'),
( 'BAA', 'PWS','PWS','RWS'),
( 'BAA', 'MO1','MO1','RT1')
) d
( Dealer, Contcode, Description, FollCode )
---------------------------------
IF OBJECT_ID('tempdb..#ContactGroups') IS NOT NULL DROP TABLE #ContactGroups
SELECT * INTO #ContactGroups FROM (VALUES
( 'BAA', '1', 'New Calls'),
( 'BAA', '2', '1st Attempt made'),
( 'BAA', '3', '2nd Attempt made'),
( 'BAA', '4', '3rd Attempt made')
) d
( Dealer, Code, Description )
--------------------
--QUERY 1
select * from #Contacts c
JOIN #Dealers2 AS d WITH(NOLOCK) ON d.Dealer = c.Dealer
JOIN #ContactGroups CG WITH(NOLOCK) ON C.Dealer = CG.Dealer AND C.Grop = CG.Code
WHERE
ISNULL(C.Complete, '1900-01-01 00:00:00.000') = '1900-01-01 00:00:00.000'
AND ISNULL(C.InUse, 0) = 0
AND (ISNULL(C.Follcode,'') <> 'CRC' OR C.OpNum = 0)
AND C.FollDate <= getdate()
AND C.dealer in ('BAA')
AND C.contcode not in ('CSI', 'HCR', 'HEA', 'LAP', 'EVC', 'NFL', 'TYI', 'NCP', 'NTA')
ORDER BY C.Regno
November 14, 2018 at 6:11 am
Can you list the columns you want in "QUERY 1"? There is currently more than one "dealer" column output.
November 14, 2018 at 6:22 am
Sorry please see below
SELECT c.Dealer, c.ContCode, cp.description, CG.Description AS [Attempt], c.RegNo, c.Salute, c.Updated, c.ContDate, c.CompleteCode
November 14, 2018 at 6:33 am
craig.jenkins - Wednesday, November 14, 2018 6:22 AMSorry please see belowSELECT c.Dealer, c.ContCode, cp.description, CG.Description AS [Attempt], c.RegNo, c.Salute, c.Updated, c.ContDate, c.CompleteCode
There is no cp.description in QUERY1
November 14, 2018 at 6:43 am
--QUERY 1
;with cte as
(
SELECT c.Dealer, c.ContCode, CG.Description AS [Attempt], c.RegNo, c.Salute, c.Updated, c.ContDate, c.CompleteCode
from #Contacts c
JOIN #Dealers2 AS d WITH(NOLOCK) ON d.Dealer = c.Dealer
JOIN #ContactGroups CG WITH(NOLOCK) ON C.Dealer = CG.Dealer AND C.Grop = CG.Code
WHERE ISNULL(C.Complete, '1900-01-01 00:00:00.000') = '1900-01-01 00:00:00.000'
AND ISNULL(C.InUse, 0) = 0
AND (ISNULL(C.Follcode,'') <> 'CRC' OR C.OpNum = 0)
AND C.FollDate <= getdate()
AND C.dealer in ('BAA')
AND C.contcode not in ('CSI', 'HCR', 'HEA', 'LAP', 'EVC', 'NFL', 'TYI', 'NCP', 'NTA')
),
cte2 as
(
SELECT distinct CTE.Dealer, CTE.Attempt
FROM cte
)
SELECT Y.*
FROM CTE2
CROSS APPLY(SELECT TOP(2) *
FROM cte
WHERE cte.Dealer = CTE2.Dealer
AND cte.Attempt = CTE2.Attempt
--AND cte.DescR = CTE2.DescR
ORDER BY CTE.ContDate) Y
ORDER BY Y.Regno
November 14, 2018 at 6:43 am
apologies don't worry about that one just use:
SELECT c.Dealer, c.ContCode, CG.Description AS [Attempt], c.RegNo, c.Salute, c.Updated, c.ContDate, c.CompleteCode
November 14, 2018 at 7:13 am
craig.jenkins - Wednesday, November 14, 2018 6:43 AMapologies don't worry about that one just use:SELECT c.Dealer, c.ContCode, CG.Description AS [Attempt], c.RegNo, c.Salute, c.Updated, c.ContDate, c.CompleteCode
See code above
November 14, 2018 at 7:24 am
This is amazing. Thank you so much,
November 14, 2018 at 7:31 am
In this instance you might find the following code faster:;WITH CTE AS
(
SELECT c.Dealer, c.ContCode, CG.Description AS [Attempt], c.RegNo, c.Salute, c.Updated, c.ContDate, c.CompleteCode,
ROW_NUMBER() OVER (PARTITION BY c.Dealer, /* c.DescR, */ CG.Description ORDER BY c.ContDate) RowNum
from #Contacts c
JOIN #Dealers2 AS d WITH(NOLOCK) ON d.Dealer = c.Dealer
JOIN #ContactGroups CG WITH(NOLOCK) ON C.Dealer = CG.Dealer AND C.Grop = CG.Code
WHERE ISNULL(C.Complete, '1900-01-01 00:00:00.000') = '1900-01-01 00:00:00.000'
AND ISNULL(C.InUse, 0) = 0
AND (ISNULL(C.Follcode,'') <> 'CRC' OR C.OpNum = 0)
AND C.FollDate <= getdate()
AND C.dealer in ('BAA')
AND C.contcode not in ('CSI', 'HCR', 'HEA', 'LAP', 'EVC', 'NFL', 'TYI', 'NCP', 'NTA')
)
SELECT Dealer, ContCode, Attempt, RegNo, Salute, Updated, ContDate, CompleteCode
FROM CTE
WHERE RowNum <=2
ORDER BY RegNo
Viewing 12 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply