CODEWITHSUNDEEP
sqlsql-server
k80sg
k80sg

Reputation: 2473

SQL Server + returning null data

Hi I have a query like this which returns enrollment data for the past 6 months, however data will only be display should there be at least 1 enrollment, how can display the months which have no enrollment as well? I know I probably have to do something to this line:

LEFT(CONVERT(varchar, issuedate, 112), 6) BETWEEN 
   LEFT(CONVERT(varchar, DATEADD(MM, -6, GETDATE()), 112), 6) AND 
   LEFT(CONVERT(varchar, GETDATE(), 112), 6)

Thanks.

SELECT 
    DateName(month,issuedate) + ' ' + CAST(Year(issuedate) as Char(4)) as IssueDate,
    COUNT(distinct m.ID) AS SignUps
FROM
    Member m 
INNER JOIN 
    Card c ON m.ID = c.MemberID
WHERE 
    m.Deletedby is null 
    AND c.Deletedby is null
    AND LEFT(CONVERT(varchar, issuedate, 112), 6) BETWEEN   
            LEFT(CONVERT(varchar, DATEADD(MM, -6, GETDATE()), 112), 6) 
            AND LEFT(CONVERT(varchar, GETDATE(), 112), 6)
 GROUP BY 
    DateName(month, issuedate) + ' ' + CAST(Year(issuedate) AS  Char(4)), CAST(CAST(YEAR(issuedate) AS VARCHAR) + '/'+ CAST(MONTH(issuedate) AS VARCHAR) +'/1' AS DateTime)

Upvotes: 1

Views: 117

Answers (1)

Nikhil S
Nikhil S

Reputation: 1209

Observations:
1. table names don't imply how they are connected.
2. using functions like you have used will never use right indexes.
3. all column references should be qualified by table aliases, to make the code easy to understand.
4. getdate() is a non-deterministic function and causes bad query plan. Pass date into the sp.

Assuming that a entry in Card table is created when some member enrolls for the card:-

SELECT 
    <month_year_string> as IssueDate,
    COUNT(distinct c.memberid) AS SignUps
FROM
    Member m 
lEFT OUTER JOIN 
    Card c ON m.ID = c.MemberID     AND c.Deletedby is null
WHERE 
    m.Deletedby is null 
    AND c.issuedate BETWEEN  DATEADD(MM, -6, GETDATE()) AND GETDATE()
 GROUP BY <month_year_string>

Upvotes: 2

Related Questions