Query first 2 results based on age and description

Question

Query first 2 results based on age and description

craig.jenkins

SSCommitted

Points: 1643
More actions
November 12, 2018 at 8:53 am

#376372

Guys, Is there a way to do the following. Temp data and expected results included:
The oldest 2 results based on 'Attempt' by each 'DescR' based on the oldest Cont_Date. I hope this makes sense.
So out of the temp data the expected results would be as per the expected results below. Which shows the oldest two 'DescR' for each 'Attempt' based on the oldest Cont_Date
TEMP DATA
IF OBJECT_ID('tempdb..#Log') IS NOT NULL DROP TABLE #Log
SELECT * INTO #Log FROM (VALUES
( 'BAA ', 'SE1', 'New Calls', '1', 'Mr Jones 1', '01-01-18', '01-02-18', '' ),
( 'BAA ', 'SE1', 'New Calls', '2', 'Mr Jones 2', '02-01-18', '02-02-18', 'CUA' ),
( 'BAA ', 'SE1', 'New Calls', '3', 'Mr Jones 3', '03-01-18', '03-02-18', '' ),
( 'BAA ', 'SE1', '1st Attempt made', '4', 'Mr Jones 4', '04-01-18', '04-02-18', '' ),
( 'BAA ', 'SE1', '1st Attempt made', '5', 'Mr Jones 5', '05-01-18', '05-02-18', '' ),
( 'BAA ', 'SE1', '1st Attempt made', '6', 'Mr Jones 6', '06-01-18', '06-02-18', '' ),
( 'BAA ', 'SE1', '2nd Attempt made', '7', 'Mr Jones 7', '07-01-18', '07-02-18', 'LMS' ),
( 'BAA ', 'SE1', '2nd Attempt made', '8', 'Mr Jones 8', '08-01-18', '08-02-18', '' ),
( 'BAA ', 'SE1', '2nd Attempt made', '9', 'Mr Jones 9', '09-01-18', '09-02-18', 'LMS' ),
( 'BAA ', 'SE1', '3rd Attempt made', '10', 'Mr Jones 10', '10-01-18', '10-02-18', 'LMS' ),
( 'BAA ', 'SE2', 'New Calls', '11', 'Mr Jones 11', '11-01-18', '11-02-18', 'CUA' ),
( 'BAA ', 'SE2', 'New Calls', '12', 'Mr Jones 12', '12-01-18', '12-02-18', 'LMS' ),
( 'BAA ', 'SE2', 'New Calls', '13', 'Mr Jones 13', '13-01-18', '13-02-18', 'LMS' ),
( 'BAA ', 'SE2', '1st Attempt made', '14', 'Mr Jones 14', '14-01-18', '14-02-18', '' ),
( 'BAA ', 'SE2', '1st Attempt made', '15', 'Mr Jones 15', '15-01-18', '15-02-18', 'LMS' ),
( 'BAA ', 'SE2', '1st Attempt made', '16', 'Mr Jones 16', '16-01-18', '16-02-18', '' ),
( 'BAA ', 'SE2', '2nd Attempt made', '17', 'Mr Jones 17', '17-01-18', '17-02-18', '' ),
( 'BAA ', 'SE2', '2nd Attempt made', '18', 'Mr Jones 18', '18-01-18', '18-02-18', 'CUA' ),
( 'BAA ', 'SE2', '2nd Attempt made', '19', 'Mr Jones 19', '19-01-18', '19-02-18', '' ),
( 'BAA ', 'SE2', '3rd Attempt made', '20', 'Mr Jones 20', '20-01-18', '20-02-18', '' ),
( 'BAA ', 'SE3', 'New Calls', '21', 'Mr Jones 21', '21-01-18', '21-02-18', 'CUA' ),
( 'BAA ', 'SE3', 'New Calls', '22', 'Mr Jones 22', '22-01-18', '22-02-18', 'LMS' ),
( 'BAA ', 'SE3', 'New Calls', '23', 'Mr Jones 23', '23-01-18', '23-02-18', '' ),
( 'BAA ', 'SE3', '1st Attempt made', '24', 'Mr Jones 24', '24-01-18', '24-02-18', 'CUA' ),
( 'BAA ', 'SE3', '1st Attempt made', '25', 'Mr Jones 25', '25-01-18', '25-02-18', '' ),
( 'BAA ', 'SE3', '1st Attempt made', '26', 'Mr Jones 26', '26-01-18', '26-02-18', 'CUA' ),
( 'BAA ', 'SE3', '2nd Attempt made', '27', 'Mr Jones 27', '27-01-18', '27-02-18', 'LMS' ),
( 'BAA ', 'SE3', '2nd Attempt made', '28', 'Mr Jones 28', '28-01-18', '28-02-18', 'CUA' ),
( 'BAA ', 'SE3', '2nd Attempt made', '29', 'Mr Jones 29', '29-01-18', '01-03-18', 'CCB' ),
( 'BAA ', 'SE3', '3rd Attempt made', '30', 'Mr Jones 30', '30-01-18', '02-03-18', '' ),
( 'BAA ', 'SE3', 'New Calls', '31', 'Mr Jones 31', '31-01-18', '03-03-18', 'CUA' ),
( 'BAA ', 'MO', 'New Calls', '32', 'Mr Jones 32', '01-02-18', '04-03-18', 'CUA' ),
( 'BAA ', 'MO', 'New Calls', '33', 'Mr Jones 33', '02-02-18', '05-03-18', '' ),
( 'BAA ', 'MO', '1st Attempt made', '34', 'Mr Jones 34', '03-02-18', '06-03-18', 'LMS' ),
( 'BAA ', 'MO', '1st Attempt made', '35', 'Mr Jones 35', '04-02-18', '07-03-18', 'CUA' ),
( 'BAA ', 'MO', '1st Attempt made', '36', 'Mr Jones 36', '05-02-18', '08-03-18', '' ),
( 'BAA ', 'MO', '2nd Attempt made', '37', 'Mr Jones 37', '06-02-18', '09-03-18', 'LMS' ),
( 'BAA ', 'MO', '2nd Attempt made', '38', 'Mr Jones 38', '07-02-18', '10-03-18', 'LMS' ),
( 'BAA ', 'MO', '2nd Attempt made', '39', 'Mr Jones 39', '08-02-18', '11-03-18', '' ),
( 'BAA ', 'MO', '3rd Attempt made', '40', 'Mr Jones 40', '09-02-18', '12-03-18', '' ),
( 'BAA ', 'CAM', 'New Calls', '41', 'Mr Jones 41', '10-02-18', '13-03-18', 'CUA' ),
( 'BAA ', 'CAM', 'New Calls', '42', 'Mr Jones 42', '11-02-18', '14-03-18', 'LMS' ),
( 'BAA ', 'CAM', 'New Calls', '43', 'Mr Jones 43', '12-02-18', '15-03-18', 'CUA' ),
( 'BAA ', 'CAM', '1st Attempt made', '44', 'Mr Jones 44', '13-02-18', '16-03-18', '' ),
( 'BAA ', 'CAM', '1st Attempt made', '45', 'Mr Jones 45', '14-02-18', '17-03-18', '' ),
( 'BAA ', 'CAM', '1st Attempt made', '46', 'Mr Jones 46', '15-02-18', '18-03-18', '' ),
( 'BAA ', 'CAM', '2nd Attempt made', '47', 'Mr Jones 47', '16-02-18', '19-03-18', 'LMS' ),
( 'BAA ', 'CAM', '2nd Attempt made', '48', 'Mr Jones 48', '17-02-18', '20-03-18', 'LMS' ),
( 'BAA ', 'CAM', '2nd Attempt made', '49', 'Mr Jones 49', '18-02-18', '21-03-18', 'CUA' ),
( 'BAA ', 'CAM', '3rd Attempt made', '50', 'Mr Jones 50', '19-02-18', '22-03-18', 'CUA' )
) d
( Dealer, DescR, Attempt, Ref, Salute, Updated_Date, Cont_date, Compcode )
SELECT * FROM #lOG
WHERE #Log.DescR in ('cam', 'MO', 'SE1', 'SE2', 'SE3')
order by descr, Attempt
EXPECTED RESULTS

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 1

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

craig.jenkins SSCommitted Points: 1643 More actions · Answer 2

craig.jenkins

SSCommitted

Points: 1643

November 12, 2018 at 9:12 am

#2013229

Amazing thank you very much

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 3

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

craig.jenkins SSCommitted Points: 1643 More actions · Answer 4

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

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 5

Can you list the columns you want in "QUERY 1"? There is currently more than one "dealer" column output.

craig.jenkins SSCommitted Points: 1643 More actions · Answer 6

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

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 7

craig.jenkins - Wednesday, November 14, 2018 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

There is no cp.description in QUERY1

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 8

--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

craig.jenkins SSCommitted Points: 1643 More actions · Answer 9

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

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 10

craig.jenkins - Wednesday, November 14, 2018 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

See code above

craig.jenkins SSCommitted Points: 1643 More actions · Answer 11

craig.jenkins

SSCommitted

Points: 1643

November 14, 2018 at 7:24 am

#2013454

This is amazing. Thank you so much,

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 12

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