CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Vikram Kumar
Vikram Kumar

Reputation: 47

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

Upvotes: 1

Views: 119

Answers (2)

SimarjeetSingh Panghlia
SimarjeetSingh Panghlia

Reputation: 2200

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)

Upvotes: 3

ydoow
ydoow

Reputation: 3006

I'll suggest to create a master table storing all the bill dates you are going to show.

CREATE TABLE MasterDate 
(
    mDate nvarchar(15)
)
GO

Add proper entries, for example

INSERT INTO MasterDate (mDate) VALUES ('06/06/2015');
INSERT INTO MasterDate (mDate) VALUES ('07/06/2015');
INSERT INTO MasterDate (mDate) VALUES ('08/06/2015');
GO

Then join this master table with yours

SELECT
    m.mDate AS billdate,
    isnull(t.Amount, 0)
FROM
    MasterDate m 
LEFT JOIN 
(
    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)
) t ON m.mDate = t.billdate

With this all bill date rows would be shown. If there's no entry in your result, the Amount should be null as a result of LEFT JOIN and later shown as 0 with the ISNULL function. Hope this helps.

Upvotes: 1

Related Questions