Vikram Kumar Vikram Kumar - 6 months ago 10
SQL Question

How to get not existing records in SQL query

I am getting the following bellow result correct, I need the Record of

07/06/2015 0
in between the bellow figure. I did in the Row data bound and some trials in SQL query also. But no clue.

My Query

SELECT
CONVERT(nvarchar(15), TransDate, 103) billdate,
ISNULL(SUM(CONVERT(int, Amount)), '0') Amount
FROM HMS_DiagnosisTransactions
WHERE
TransDate BETWEEN '2015-06-06 00:00:00.000' AND '2015-06-09 00:00:00.000'
GROUP BY
CONVERT(nvarchar(15), TransDate, 103),
status,
CAST(TransDate AS date)
ORDER BY CAST(TransDate AS date)


Result is

Result

Answer

Try this

;WITH dates AS 
(
    SELECT CONVERT(date,'2015-06-06 00:00:00.000') as Date
    UNION ALL
    SELECT DATEADD(d,1,[Date])
    FROM dates 
    WHERE DATE < '2015-06-09 00:00:00.000'
)
select CONVERT(nvarchar(15), d.date, 103) billdate,
    ISNULL(SUM(CONVERT(int, hd.Amount)), '0') Amount from dates d
left join HMS_DiagnosisTransactions hd on convert(date,d.date) = convert(date,hd.TransDate)         
GROUP BY
    CONVERT(nvarchar(15), d.date, 103),
    hd.status,
    CAST(d.date AS date)
ORDER BY CAST(d.date AS date)
OPTION (MAXRECURSION 100)
Comments