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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
2242 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
2242 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  
 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.08 seconds. Powered By: Snitz Forums 2000