Duplicate Rows/Return only row highest value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Duplicate Rows/Return only row highest value

Author

Topic

last
Starting Member

25 Posts

Posted - 2012-11-12 : 02:25:08

Hi All

I have query here that return the result as in my attached image.

DECLARE @FROM date;
DECLARE @TO date;
DECLARE @ASSIGNMENT varchar(50);
SET @FROM = '2012-10-01';
SET @TO = '2012-10-31';
SET @ASSIGNMENT = 'Cabling';


SELECT    DISTINCT PROBM1.NUMBER, PROBM2.ASSIGNEE_FULL_NAME,PROBM2.SLA_BREACH,MAX(CK1.TOTAL) AS TOTALTIME,
                      CASE WHEN  (DAY(TOTAL)-1=0 AND DATEPART(HOUR,TOTAL)<=4) THEN 1 ELSE 0 END AS UF,
                      CASE WHEN  (DAY(TOTAL)-1=0 AND DATEPART(HOUR,TOTAL)>4 AND DATEPART(HOUR,TOTAL)<=6)  THEN  1 ELSE 0 END AS  US ,
                      CASE WHEN (DAY(TOTAL)-1=0 AND DATEPART(HOUR,TOTAL)>6 AND DATEPART(HOUR,TOTAL)<=8)  THEN  1 ELSE 0 END AS  UE,
                      CASE WHEN DATEPART(HOUR,TOTAL)>8 THEN 1 ELSE 0 END AS OE
FROM         PROBSUMMARYM1 AS PROBM1 INNER JOIN
                      CLOCKSM1 AS CK1 ON PROBM1.NUMBER = CK1.KEY_CHAR INNER JOIN
                      PROBSUMMARYM2 AS PROBM2 ON PROBM1.NUMBER = PROBM2.NUMBER
WHERE     (CK1.SCHEDULE = 'UPworkhours') AND (PROBM1.ASSIGNMENT = @ASSIGNMENT) AND (CONVERT(DATE,PROBM1.OPEN_TIME ,112)>= @FROM AND 
                      CONVERT(DATE,PROBM1.OPEN_TIME,112) <= @TO) AND PROBM1.NUMBER='IM479770'
                      GROUP BY PROBM1.NUMBER,PROBM2.ASSIGNEE_FULL_NAME,PROBM2.SLA_BREACH,CK1.TOTAL

RESULT
Number | Name |TOTALTIME |UF |US| UE |OE
IM479770| Teane Nkane Mr|4000-01-01 00:00:00.000|1 | 0 | 0 |0
IM479770| Teane Nkane Mr|4000-01-01 00:09:00.000|1 | 0 | 0 |0
IM479770| Teane Nkane Mr|4000-01-01 02:52:52.000|1 | 0 | 0 |0
IM479770| Teane Nkane Mr|4000-01-01 02:52:53.000|1 | 0 | 0 |0
IM479770 Teane Nkane Mr|4000-01-01 03:01:52.000|1 | 0 | 0 |0

I would like my result to look like such
Number | Name |TOTALTIME |UF |US| UE |OE
IM479770| Teane Nkane Mr|4000-01-01 03:01:52.000|1 | 0 | 0 |0

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-12 : 02:42:56

[code]

SELECT NUMBER, ASSIGNEE_FULL_NAME, SLA_BREACH, MAX(TOTALTIME) MaxTime, UF, US, UE, OE
FROM (SELECT DISTINCT PROBM1.NUMBER, PROBM2.ASSIGNEE_FULL_NAME, PROBM2.SLA_BREACH,MAX(CK1.TOTAL) AS TOTALTIME,
CASE WHEN (DAY(TOTAL)-1=0 AND DATEPART(HOUR,TOTAL)<=4) THEN 1 ELSE 0 END AS UF,
CASE WHEN (DAY(TOTAL)-1=0 AND DATEPART(HOUR,TOTAL)>4 AND DATEPART(HOUR,TOTAL)<=6) THEN 1 ELSE 0 END AS US ,
CASE WHEN (DAY(TOTAL)-1=0 AND DATEPART(HOUR,TOTAL)>6 AND DATEPART(HOUR,TOTAL)<=8) THEN 1 ELSE 0 END AS UE,
CASE WHEN DATEPART(HOUR,TOTAL)>8 THEN 1 ELSE 0 END AS OE
FROM PROBSUMMARYM1 AS PROBM1 INNER JOIN
CLOCKSM1 AS CK1 ON PROBM1.NUMBER = CK1.KEY_CHAR INNER JOIN
PROBSUMMARYM2 AS PROBM2 ON PROBM1.NUMBER = PROBM2.NUMBER
WHERE (CK1.SCHEDULE = 'UPworkhours') AND (PROBM1.ASSIGNMENT = @ASSIGNMENT) AND (CONVERT(DATE,PROBM1.OPEN_TIME ,112)>= @FROM AND
CONVERT(DATE,PROBM1.OPEN_TIME,112) <= @TO) AND PROBM1.NUMBER='IM479770'
GROUP BY PROBM1.NUMBER,PROBM2.ASSIGNEE_FULL_NAME,PROBM2.SLA_BREACH,CK1.TOTAL
) t
GROUP BY NUMBER, ASSIGNEE_FULL_NAME, SLA_BREACH, UF, US, UE, OE
[/code]

(OR)

[code]

;WITH cte
AS (SELECT PROBM1.NUMBER, PROBM2.ASSIGNEE_FULL_NAME, PROBM2.SLA_BREACH, CK1.TOTAL ,
CASE WHEN (DAY(TOTAL)-1=0 AND DATEPART(HOUR,TOTAL)<=4) THEN 1 ELSE 0 END AS UF,
CASE WHEN (DAY(TOTAL)-1=0 AND DATEPART(HOUR,TOTAL)>4 AND DATEPART(HOUR,TOTAL)<=6) THEN 1 ELSE 0 END AS US ,
CASE WHEN (DAY(TOTAL)-1=0 AND DATEPART(HOUR,TOTAL)>6 AND DATEPART(HOUR,TOTAL)<=8) THEN 1 ELSE 0 END AS UE,
CASE WHEN DATEPART(HOUR,TOTAL)>8 THEN 1 ELSE 0 END AS OE
FROM PROBSUMMARYM1 AS PROBM1 INNER JOIN
CLOCKSM1 AS CK1 ON PROBM1.NUMBER = CK1.KEY_CHAR INNER JOIN
PROBSUMMARYM2 AS PROBM2 ON PROBM1.NUMBER = PROBM2.NUMBER
WHERE (CK1.SCHEDULE = 'UPworkhours') AND (PROBM1.ASSIGNMENT = @ASSIGNMENT) AND (CONVERT(DATE,PROBM1.OPEN_TIME ,112)>= @FROM AND
CONVERT(DATE,PROBM1.OPEN_TIME,112) <= @TO) AND PROBM1.NUMBER='IM479770'
)
SELECT NUMBER, ASSIGNEE_FULL_NAME, SLA_BREACH, MAX(TOTAL) TOTALTime, UF, US, UE, OE
FROM cte
GROUP BY NUMBER, ASSIGNEE_FULL_NAME, SLA_BREACH, UF, US, UE, OE
[/code]
--
Chandu

last
Starting Member

25 Posts

Posted - 2012-11-12 : 03:11:12

Hi Chandu

Thank you for your response. It works well if there is an individual number put in there where clause but if I make it to select all available numbers it will return 2 of each.


DECLARE @FROM date;
DECLARE @TO date;
DECLARE @ASSIGNMENT varchar(50);
SET @FROM = '2012-10-01';
SET @TO = '2012-10-31';
SET @ASSIGNMENT = 'Cabling';

SELECT NUMBER, ASSIGNEE_FULL_NAME, SLA_BREACH, MAX(TOTALTIME) MaxTime, UF, US, UE, OE
FROM (SELECT DISTINCT PROBM1.NUMBER, PROBM2.ASSIGNEE_FULL_NAME, PROBM2.SLA_BREACH,MAX(CK1.TOTAL) AS TOTALTIME,
                      CASE WHEN  (DAY(TOTAL)-1=0 AND DATEPART(HOUR,TOTAL)<=4) THEN 1 ELSE 0 END AS UF,
                      CASE WHEN  (DAY(TOTAL)-1=0 AND DATEPART(HOUR,TOTAL)>4 AND DATEPART(HOUR,TOTAL)<=6)  THEN  1 ELSE 0 END AS  US ,
                      CASE WHEN (DAY(TOTAL)-1=0 AND DATEPART(HOUR,TOTAL)>6 AND DATEPART(HOUR,TOTAL)<=8)  THEN  1 ELSE 0 END AS  UE,
                      CASE WHEN DATEPART(HOUR,TOTAL)>8 THEN 1 ELSE 0 END AS OE
FROM         PROBSUMMARYM1 AS PROBM1 INNER JOIN
                      CLOCKSM1 AS CK1 ON PROBM1.NUMBER = CK1.KEY_CHAR INNER JOIN
                      PROBSUMMARYM2 AS PROBM2 ON PROBM1.NUMBER = PROBM2.NUMBER
WHERE     (CK1.SCHEDULE = 'UPworkhours') AND (PROBM1.ASSIGNMENT = @ASSIGNMENT) AND (CONVERT(DATE,PROBM1.OPEN_TIME ,112)>= @FROM AND 
                      CONVERT(DATE,PROBM1.OPEN_TIME,112) <= @TO) --AND PROBM1.NUMBER='IM479770'
                      GROUP BY PROBM1.NUMBER,PROBM2.ASSIGNEE_FULL_NAME,PROBM2.SLA_BREACH,CK1.TOTAL
) t
GROUP BY NUMBER, ASSIGNEE_FULL_NAME, SLA_BREACH, UF, US, UE, OE

IM479215 didata didata Mr t 4000-01-03 10:50:47.000 0 0 0 1
IM479215 didata didata Mr t 4000-01-01 02:37:01.000 1 0 0 0
IM479390 didata didata Mr t 4000-01-01 03:46:16.000 1 0 0 0
IM479594 Kekana Johny Mr f 4000-01-01 05:15:24.000 0 1 0 0
IM479594 Kekana Johny Mr f 4000-01-01 00:00:00.000 1 0 0 0
IM480269 didata didata Mr t 4000-01-02 00:11:31.000 0 0 0 0
IM481515 didata didata Mr t 4000-01-05 02:09:41.000 0 0 0 0
IM482308 Kekana Johny Mr f 4000-01-01 20:58:29.000 0 0 0 1
IM482308 Kekana Johny Mr f 4000-01-01 00:00:00.000 1 0 0 0

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-12 : 04:14:09

Try this once... here u will get UF, US, UE, and OE values based on maximum TOTAL


SELECT NUMBER, ASSIGNEE_FULL_NAME, SLA_BREACH, TOTALTIME, 
		CASE WHEN  (DAY(TOTALTIME)-1=0 AND DATEPART(HOUR,TOTALTIME)<=4) THEN 1 ELSE 0 END AS UF,
        CASE WHEN  (DAY(TOTALTIME)-1=0 AND DATEPART(HOUR,TOTALTIME)>4 AND DATEPART(HOUR,TOTALTIME)<=6)  THEN  1 ELSE 0 END AS  US ,
        CASE WHEN (DAY(TOTALTIME)-1=0 AND DATEPART(HOUR,TOTALTIME)>6 AND DATEPART(HOUR,TOTALTIME)<=8)  THEN  1 ELSE 0 END AS  UE,
        CASE WHEN DATEPART(HOUR,TOTALTIME)>8 THEN 1 ELSE 0 END AS OE
FROM (SELECT PROBM1.NUMBER, PROBM2.ASSIGNEE_FULL_NAME, PROBM2.SLA_BREACH,MAX(CK1.TOTAL) AS TOTALTIME,
		FROM  PROBSUMMARYM1 AS PROBM1 INNER JOIN
              CLOCKSM1 AS CK1 ON PROBM1.NUMBER = CK1.KEY_CHAR INNER JOIN
              PROBSUMMARYM2 AS PROBM2 ON PROBM1.NUMBER = PROBM2.NUMBER
		WHERE (CK1.SCHEDULE = 'UPworkhours') AND (PROBM1.ASSIGNMENT = @ASSIGNMENT) AND (CONVERT(DATE,PROBM1.OPEN_TIME ,112)>= @FROM AND 
               CONVERT(DATE,PROBM1.OPEN_TIME,112) <= @TO) --AND PROBM1.NUMBER='IM479770'
               GROUP BY PROBM1.NUMBER,PROBM2.ASSIGNEE_FULL_NAME,PROBM2.SLA_BREACH
     ) t

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources