| Author |
 Topic  |
|
|
last
Starting Member
22 Posts |
 Posted - 11/12/2012 : 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
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 11/12/2012 : 02:42:56
|
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 |
 |
|
|
last
Starting Member
22 Posts |
Posted - 11/12/2012 : 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
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 11/12/2012 : 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 |
|
|
| |
Topic |
|
|
|
Duplicate Rows/Return only row highest value
