Handling missing dates

Database
Enthusiast

Handling missing dates

Hi,

I am facing some challenges while dealing with my result set which has no records for few dates. I tried making a join with the System calendar table. However it is returning numerous unwanted records which is expected. Is there a way to fix this issue? The current output and the desired output are listed below.

Day of the month -> day from Transaction_Date field
Total Count -> Sum of transactions on each day by each dept
Total Pmt -> Sum of payments made by each dept on each day

Any guidance is much appreciated.

Thank you

Naren

Current Output:

Day of the month Total Count Dept Total Payment
1 25 Dept A $5000
2 30 Dept A $6000
4 15 Dept A $7000
5 65 Dept A $3000
7 50 Dept A $4500

Desired Output:

Day of the month Total Count Division Total Payment
1 25 Dept A $5000
2 30 Dept A $6000
3 0 Dept A $0
4 15 Dept A $7000
5 65 Dept A $3000
6 0 Dept A $0
7 50 Dept A $4500
6 REPLIES
Enthusiast

Re: Handling missing dates

The format of the sample output seems confusing. Hence I am posting again.

Day of the month --------------Total Count------------- Dept --------------Total Payment
-------1-------------------------------- 25 -------------------Dept A ---------------$5000
-------2 --------------------------------30 -------------------Dept A ---------------$6000
-------4-------------------------------- 15 -------------------Dept A ---------------$7000
-------5-------------------------------- 65------------------- Dept A ---------------$3000
-------7-------------------------------- 50 -------------------Dept A ---------------$4500

Desired Output:

Day of the month --------------Total Count------------- Dept --------------Total Payment
-------1-------------------------------- 25 -------------------Dept A ---------------$5000
-------2 --------------------------------30 -------------------Dept A ---------------$6000
-------3 --------------------------------0- -------------------Dept A ---------------$0
-------4-------------------------------- 15 -------------------Dept A ---------------$7000
-------5-------------------------------- 65------------------- Dept A ---------------$3000
-------6 --------------------------------0- -------------------Dept A ---------------$0
-------7-------------------------------- 50 -------------------Dept A ---------------$4500
Enthusiast

Re: Handling missing dates

To use the syscal create a small dataset (derived table) with 31 dates to work with and the left join that to the transaction table and coalesce the measures to 0 which should sum 0 and not NULL for the missing dates.

Try this query and change it accordingly and let me know if it works.

SELECT SYSCAL.DAY , COALESCE(TRAN.DEPT , 'NA') AS DEPT

SUM(COALESCE(CNT,0)) AS TOTAL_CNT,

SUM(COALESCE(PYMNT,0)) AS TOTAL_PYMNT

FROM

(SELECT DAY from SYS_CALENDER

WHERE MONTH = 1

AND YEAR = 2010

) AS SYSCAL

LEFT OUTER JOIN

TRAN_TABLE TRAN

ON SYSCAL.DAY = TRAN.DAY

WHERE TRAN.TRANSACTION_DATE BETWEEN <START_DT> AND <END_DT>

GROUP BY SYSCAL.DAY , TRAN.DEPT

Thanks

Manik

Enthusiast

Re: Handling missing dates

Hi Manik,

Thank you very much. It worked. However there is still one issue. Now the script gives an output as follows:

Day of the month --------------Total Count------------- Dept --------------Total Payment

-------1-------------------------------- 25 -------------------Dept A ---------------$5000

-------1 --------------------------------30 -------------------Dept B ---------------$6000

-------2 --------------------------------100- -----------------Dept A---------------$6500

-------2-------------------------------- 15 -------------------Dept B---------------$7000

-------3-------------------------------- 0-------------------     NA ------------------  $0

-------4 --------------------------------70- -------------------Dept A ---------------$5000

-------4-------------------------------- 50 -------------------Dept B ---------------$4500

However I would like to have separate rows for each dept for every day. For instance, the desired output should look like the one shown below:

Day of the month --------------Total Count------------- Dept --------------Total Payment

-------1-------------------------------- 25 -------------------Dept A ---------------$5000

-------1 --------------------------------30 -------------------Dept B ---------------$6000

-------2 --------------------------------100- -----------------Dept A---------------$6500

-------2-------------------------------- 15 -------------------Dept B---------------$7000

-------3-------------------------------- 0-------------------     Dept A ------------------  $0

-------3-------------------------------- 0-------------------     Dept B ------------------  $0

-------4 --------------------------------70- -------------------Dept A ---------------$5000

-------4-------------------------------- 50 -------------------Dept B ---------------$4500

Could you please advice me on resolving this?

Thank you very much.

Naren

Enthusiast

Re: Handling missing dates

For that, you'll need a list of all of the departments.

Do you have a department table? If so, you can include that in as an outer join as well. It will function similarly to the calendar join described above. If not, it becomes more difficult. You can do things with derived tables and SELECT Dept FROM Table GROUP BY Dept but that may not perform will for large tables.
Enthusiast

Re: Handling missing dates

I agree with the above post !!

Please create a seperate derived table set for DEPARTMENTS and proceed in the same manner.

Thanks

Manik

Enthusiast

Re: Handling missing dates

Hi,

Thanks for the guidance. I created a derived table and now the result is in the desired format. Your hekp is much appreciated.

Sincerely,

Naren