SQL Server + returning null data

Question

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)

Wouldn't simply removing that condition accomplish what you want? — ToddBFisher
– ToddBFisher, Commented Dec 28, 2011 at 8:08

Nikhil S · Accepted Answer · 2011-12-28 08:45:08Z

2

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>

answered Dec 28, 2011 at 8:45

Nikhil S

1,2091 gold badge10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

k80sg Over a year ago

Thanks for the pointers but it doesn't solve my problem of showing null data.

Collectives™ on Stack Overflow

SQL Server + returning null data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related