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
;
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
;
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
;
