HOW to get missing values in query

1

Hi Experts need help,
i am developing report inline query where report is displaying as per month record. i have startdate and enddate i have my base table which has value like ID ,sale and date. my problem is to dispaly data for month where ID and sale is not there like below
ID===Sale_Nov15===ID===Sale_Dec15===ID===Sale_Jan16===ID===Sale_Feb15===SUM
1====50==========1=====40=========1======30=======1======60========180
2====10==========2=====20=========2======0========2======30========60
3====40==========3=====0==========3======0========3======60========100
=====100===============60================30==============150
as you see last column and last row is summary row and column. I am not able to get 0 values of ID 2 for jan16 and 3 for sale_dec15, sale_Jan16 as these are not in DB table

T.I.A

2

You would have to modify your query to return rows for the missing months as well. It is hard to say what you should change to accomplish that without seeing the query. The general pattern is to use a numbers table and left join the table that contains the data to it.

3

ok. Thank you for reply..even though left join the data will not come as that month is not there in table..

4

Try this sample query to get the missing value in query.

SELECT A.ABC_ID, A.VAL WHERE NOT EXISTS 
   (SELECT * FROM B WHERE B.ABC_ID = A.ABC_ID AND B.VAL = A.VAL)

Hoping it will be helpful to you.

5

That is precisely why you need a Numbers table (aka Tally table) and do a LEFT JOIN to it, so you can get contiguous months, even if data for that month is not present in your table.