Sum, max & stuff

mole999 · March 8, 2016, 10:59am

I'm trying to produce the BOLD text from the two display rows, where the first number is identical, everything I have tried so far just doesn't work, and I'm not sure what I am doing wrong, any help would be appreciated

PERSUNQ RELATED_POINTS1 DATE_EXPIRE1 CODE
3408 3 13/01/18 SP50
3408 3 26/11/17 SP30

3408 6 13/01/18 SP30, SP50

from the following code

SELECT DISTINCT
prp.persunq
, DRLCon.related_points AS RELATED_POINTS1
, CONVERT(VARCHAR(10), DRLCon.date_expire, 103) AS DATE_EXPIRE1
, DRCode.code
FROM drvrec_licence_convictions DRLCon
INNER JOIN drvdef_codes AS DRCode
ON DRLCon.drvdef_codes_rid = DRCode.rid
INNER JOIN drvrec_licence AS DL
ON DRLCon.drvrec_licence_rid = DL.rid
INNER JOIN perrec_persons AS PRP
ON PRP.persunq = DL.persunq
WHERE DRLCon.hidden = 'N'
AND PRP.persunq = '3408'
AND DRLCon.date_expire > Getutcdate()
ORDER BY 1

spaghettidba · March 8, 2016, 11:38am

Please post table scripts, sample data and expected output. See http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ for guidance.

madhivanan · March 8, 2016, 11:52am

If you want the row with maximum date (from the example), try

SELECT TOP 1
prp.persunq
, DRLCon.related_points AS RELATED_POINTS1
, CONVERT(VARCHAR(10), DRLCon.date_expire, 103) AS DATE_EXPIRE1
, DRCode.code
FROM drvrec_licence_convictions DRLCon
INNER JOIN drvdef_codes AS DRCode
ON DRLCon.drvdef_codes_rid = DRCode.rid
INNER JOIN drvrec_licence AS DL
ON DRLCon.drvrec_licence_rid = DL.rid
INNER JOIN perrec_persons AS PRP
ON PRP.persunq = DL.persunq
WHERE DRLCon.hidden = 'N'
AND PRP.persunq = '3408'
AND DRLCon.date_expire > Getutcdate()
ORDER BY DATE_EXPIRE1 DESC

mole999 · March 8, 2016, 1:03pm

sorry, that only returns 1 row, which doesn't ADD (6), give the MAX (13/1/2018), or bring the text (SP30,SP50) into the same output

mole999 · March 8, 2016, 1:08pm

Does this work

DROP TABLE mytable;
CREATE TABLE mytable(
PERSUNQ INTEGER NOT NULL PRIMARY KEY
,RELATED_POINTS1 INTEGER NOT NULL
,DATE_EXPIRE1 DATE NOT NULL
,CODE VARCHAR(4) NOT NULL
);
INSERT INTO mytable(PERSUNQ,RELATED_POINTS1,DATE_EXPIRE1,CODE) VALUES (3408,3,'13/01/2018','SP50');
INSERT INTO mytable(PERSUNQ,RELATED_POINTS1,DATE_EXPIRE1,CODE) VALUES (3408,3,'26/11/2017','SP30');

Thanks for the pointer, i'll bookmark it

spaghettidba · March 8, 2016, 2:02pm

This should do:

SET DATEFORMAT DMY;
IF OBJECT_ID('tempdb..#mytable') IS NOT NULL
	DROP TABLE #mytable;

CREATE TABLE #mytable(
PERSUNQ INTEGER NOT NULL  
,RELATED_POINTS1 INTEGER NOT NULL
,DATE_EXPIRE1 DATE NOT NULL
,CODE VARCHAR(4) NOT NULL
);

INSERT INTO #mytable(PERSUNQ,RELATED_POINTS1,DATE_EXPIRE1,CODE) VALUES (3408,3,'13/01/2018','SP50');
INSERT INTO #mytable(PERSUNQ,RELATED_POINTS1,DATE_EXPIRE1,CODE) VALUES (3408,3,'26/11/2017','SP30');


SELECT PERSUNQ, 
	SUM(RELATED_POINTS1) AS RELATED_POINTS1,
	MAX(DATE_EXPIRE1) AS DATE_EXPIRE1,
	CODE = STUFF((
		SELECT CODE
		FROM #mytable
		ORDER BY DATE_EXPIRE1
		FOR XML PATH(''), TYPE
	).value('.','varchar(max)'),1,1,SPACE(0))
FROM #mytable
GROUP BY PERSUNQ;

What you are trying to do with the CODE column is a concatenation aggregate, which is not available in SQL Server, so you will have to build it yourself with that weird STUFF/FOR XML syntax.

mole999 · March 9, 2016, 2:56pm

Thank you very much

mole999 · March 10, 2016, 11:16am

I've now had time to play with this
For some reason the Date returns the Lowest rather than the highest (I can use MIN instead of MAX, but it doesn't feel 100% right and I don't know why its not coming out as intended)

The second issue, only rears its head when I use this for a longer list of more people, and that is CODE goes mad
SP30SP30SP30SP30CU80SP30SP30SP30SP30....

whereas I sort of was hoping for SP30, SP50 for the unique individual tested originally, rather than what appears to be all of the column.

Can the below be refined to address that?

DROP TABLE #PENALTY;
CREATE TABLE #PENALTY(
PERSUNQ INTEGER NOT NULL PRIMARY KEY
,RELATED_POINTS INTEGER NOT NULL
,EXPIRE DATE NOT NULL
,RELATED_POINTS INTEGER NOT NULL
,CODE VARCHAR(4) NOT NULL
);
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (27,3,'15/06/2017',3,'CU30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (54,3,'02/05/2018',3,'SP50');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (244,3,'08/08/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (290,3,'19/11/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (406,3,'14/08/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (456,3,'20/05/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (466,0,'07/07/2018',0,'SP50');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (474,3,'08/02/2019',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (523,3,'27/05/2018',3,'SP50');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (536,3,'06/05/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (571,3,'27/04/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (609,3,'14/02/2019',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (614,3,'28/03/2018',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (618,3,'27/05/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (630,3,'04/03/2018',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (924,3,'04/06/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (958,3,'20/09/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (988,6,'13/12/2017',6,'IN10');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (1004,3,'16/09/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (1027,3,'07/09/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (1041,3,'29/04/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (1206,3,'05/09/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (1290,3,'04/09/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (1367,3,'05/04/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (1536,3,'25/09/2016',3,'TS10');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (1777,4,'09/10/2017',4,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (1973,3,'14/03/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (2131,3,'15/10/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (2336,3,'29/04/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (2437,3,'16/09/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (2479,3,'15/05/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (2744,3,'08/08/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (2860,3,'30/07/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (2906,3,'11/12/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3017,3,'09/08/2016',3,'TS10');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3195,3,'23/11/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3232,3,'14/10/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3248,3,'10/04/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3286,3,'18/09/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3323,3,'10/10/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3408,3,'26/11/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3408,3,'13/01/2018',3,'SP50');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3488,3,'15/02/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3503,3,'23/01/2018',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3509,3,'19/02/2017',3,'CU30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3515,3,'11/02/2018',3,'CU80');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3525,3,'28/08/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3558,3,'05/05/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3591,0,'13/09/2016',0,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3601,0,'09/08/2016',0,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3643,3,'25/05/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3718,3,'05/07/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (3822,3,'01/12/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4026,6,'24/12/2017',6,'MS90');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4109,3,'20/12/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4159,3,'05/12/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4160,3,'26/02/2018',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4218,3,'04/09/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4231,3,'17/06/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4231,3,'04/07/2016',3,'CU50');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4250,3,'08/01/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4325,3,'16/11/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4356,3,'28/04/2016',3,'TS10');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4457,3,'15/11/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4466,3,'07/11/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4481,3,'16/05/2019',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4558,3,'06/08/2018',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4566,3,'11/04/2018',3,'DR10');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4589,3,'18/07/2016',3,'CU80');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4619,3,'05/01/2018',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4620,3,'24/03/2018',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4623,6,'19/07/2016',6,'IN10');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4665,3,'16/01/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4665,3,'02/01/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4705,3,'10/03/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (4741,3,'10/08/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (5048,3,'22/07/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (5185,3,'18/07/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (5269,3,'01/03/2017',3,'CU80');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (5329,3,'28/02/2017',3,'SP50');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (5353,3,'03/04/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (5440,3,'29/07/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (5461,3,'16/10/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (5466,3,'20/12/2041',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (5466,3,'21/07/2023',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (5548,3,'23/06/2017',3,'CD30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (5663,4,'01/01/2017',4,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (5675,5,'28/06/2018',5,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (5679,4,'30/01/2017',4,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6029,3,'15/06/2018',3,'TS10');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6177,3,'11/07/2018',3,'PC30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6205,3,'12/07/2017',3,'CU80');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6229,3,'31/10/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6374,3,'08/11/2018',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6416,3,'31/12/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6427,3,'16/12/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6448,3,'23/09/2018',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6456,3,'21/08/2016',3,'CU80');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6489,3,'02/05/2017',3,'SP50');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6555,3,'08/05/2018',3,'SP10');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6779,3,'04/07/2016',3,'SP50');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6840,3,'14/06/2018',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6856,3,'07/04/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6856,3,'07/04/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (5000035,3,'16/10/2018',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (5000048,3,'01/12/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (5000921,3,'08/04/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (5001279,3,'14/02/2018',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6000319,3,'14/08/2018',3,'CU80');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6000341,3,'23/01/2018',3,'CD10');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6000372,3,'20/04/2018',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6000373,3,'01/08/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6000828,3,'09/01/2017',3,'TS10');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6000871,3,'28/01/2017',3,'SP50');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6001028,3,'12/05/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6001035,6,'09/08/2017',6,'IN10');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6001039,3,'19/09/2016',3,'SP50');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6001114,3,'13/03/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6001128,0,'13/05/2017',0,'SP50');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6001143,3,'25/03/2018',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6001978,3,'04/06/2016',3,'TS10');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6002249,3,'18/02/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6002373,3,'01/04/2016',3,'CU50');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6002381,3,'23/03/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6002526,3,'29/10/2016',3,'CU80');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6002965,3,'09/07/2016',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6002973,3,'17/04/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6003099,3,'19/08/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6003227,3,'10/04/2017',3,'SP30');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6003393,3,'03/01/2017',3,'CU80');
INSERT INTO #PENALTY(PERSUNQ,RELATED_POINTS,EXPIRE,RELATED_POINTS,CODE) VALUES (6003405,3,'05/02/2017',3,'SP30');

mole999 · March 14, 2016, 7:10am

anyone able to help with this conundrum, I know what it is doing, just can't visualise how to control for each unique PERSUNQ

jeffw8713 · March 15, 2016, 8:31pm

On the data - unless you are converting from the date to a string it will get you the max value. For your other issue: