SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Duplicate Rows/Return only row highest value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

last
Starting Member

25 Posts

Posted - 11/12/2012 :  02:25:08  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 11/12/2012 :  02:42:56  Show Profile  Reply with Quote


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




(OR)



;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

--
Chandu

Edited by - bandi on 11/12/2012 02:46:55
Go to Top of Page

last
Starting Member

25 Posts

Posted - 11/12/2012 :  03:11:12  Show Profile  Reply with Quote
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

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 11/12/2012 :  04:14:09  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000