|
View previous topic :: View next topic
|
| Author |
Message |
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Hi Team , I am pulling the two different counts from the same table and gets the difference for the same . However the order of rows and the difference is not as expected .
| Code: |
SELECT
distinct date(connected_at) as connected_date,
date(ended_at) as end_date,
CASE
WHEN type = 'A' and Status = 'finished'
THEN COUNT(*) else 0
END as total
,
CASE
WHEN type = 'B' and Status = 'finished'
THEN COUNT(*) else 0
END as total_va
,
COUNT(CASE WHEN type = 'A' and Status = 'finished' then 1 END) -
COUNT(CASE WHEN type = 'B' and Status = 'finished' then 1
END) AS DIFF
FROM TEST_CALL_TABLE
where connected_at is not null
and ended_at is not null
group by 1,2,type,Status
order by 1,2
;
|
Actual result :-
| Code: |
connected_date end_date total total_va DIFF
16-03-2023 16-03-2023 2 0 2
16-03-2023 16-03-2023 0 2 -2
17-03-2023 17-03-2023 24 0 24
17-03-2023 17-03-2023 0 24 -24
20-03-2023 20-03-2023 72 0 72
|
Expected Result :-
| Code: |
connected_date end_date total total_va DIFF
16-03-2023 16-03-2023 2 2 0
17-03-2023 17-03-2023 24 24 0
20-03-2023 20-03-2023 72 72 0
|
Can any one please help me on this .
Thanks
Balaji K |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2286
Location: USA
|
|
|
|
Try this.
I don't have access to DB2 right now, but I hope it should work 
A small trick not to forget: take into account possible NULL counter values for some date ranges...
| Code: |
WITH T(c_date, e_date, type, tot) AS
( SELECT
date(connected_at),
date(ended_at),
type,
count(*)
FROM TEST_CALL_TABLE
WHERE connected_at is not null
and ended_at is not null
and Status = 'finished'
group by 1, 2, 3
)
SELECT
T0.c_date as connected_date,
T0.e_date as end_date,
nvl(TA.tot, 0) as total,
nvl(TB.tot, 0) as total_va,
(nvl(TA.tot, 0) - nvl(TB.tot, 0) ) as diff
FROM (
// get full list of dates for both type 'A', and 'B'
(SELECT DISTINCT c_date, e_date
FROM T WHERE type IN ('A', 'B') ) T0
LEFT JOIN
// join with type 'A' counter values
(SELECT * FROM T WHERE type = 'A' ) TA
ON T0.c_date = TA.c_date AND T0.e_date = TA.e_date
LEFT JOIN
// join with type 'B' counter values
(SELECT * FROM T WHERE type = 'B' ) TB
ON T0.c_date = TB.c_date AND T0.e_date = TB.e_date
)
ORDER BY 1, 2
; |
|
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Expected keyword AS but got "("
"WITH T(c_date, e_date, type, tot) AS" - Please help |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2286
Location: USA
|
|
|
|
| balaji81_k wrote: |
Expected keyword AS but got "("
"WITH T(c_date, e_date, type, tot) AS" - Please help |
I do not have access to DB2 right now.
You MUST try to test it yourself, and FIX syntax errors by yourself.
Try some experiments: which '(' is not accepted?
Maybe, a space is required
| Code: |
| WITH T (c_date, e_date, type, tot) AS |
or whatever else?
Do something, not only copy-and-paste!!! |
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
| hi , i am working on the correcting the few info in the sample provided. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2286
Location: USA
|
|
|
|
Based on your message, your DB2 version does not accept field list in that WITH clause.
Try the same without that list:
| Code: |
WITH T AS
( SELECT
date(connected_at) as c_date,
date(ended_at) as e_date,
type,
count(*) as tot
FROM TEST_CALL_TABLE
WHERE connected_at is not null
and ended_at is not null
and Status = 'finished'
group by 1, 2, 3
)
SELECT
T0.c_date as connected_date,
T0.e_date as end_date,
nvl(TA.tot, 0) as total,
nvl(TB.tot, 0) as total_va,
(nvl(TA.tot, 0) - nvl(TB.tot, 0) ) as diff
FROM (
// get full list of dates for both type 'A', and 'B'
(SELECT DISTINCT c_date, e_date
FROM T WHERE type IN ('A', 'B') ) T0
LEFT JOIN
// join with type 'A' counter values
(SELECT * FROM T WHERE type = 'A' ) TA
ON T0.c_date = TA.c_date AND T0.e_date = TA.e_date
LEFT JOIN
// join with type 'B' counter values
(SELECT * FROM T WHERE type = 'B' ) TB
ON T0.c_date = TB.c_date AND T0.e_date = TB.e_date
)
ORDER BY 1, 2
;
|
|
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
| Thankyou Sir. It is working fine . Yes my DB2 version doesn;t allow the list. It is working as per my expectation. Many thanks ! |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
