maltman maltman - 12 days ago 5
SQL Question

Select counts from same column based on different dates

I have a couple of tables that look like this

Table1
Name varchar
ID int

Table2
ID int
Run_Date datetime


I am trying to select the name in one column, and then a count of the dates into 2 columns. 1 column would be all dates per name from last week and the second column would be all counts per name from this week. The results would look like something like this

So if my tables looked like this

Table1
Name1 1
Name2 2
Name3 3

Table2
1 2016-11-21 8:40:23.570
2 2016-11-21 8:50:23.570
1 2016-11-21 9:40:23.570
1 2016-11-21 10:40:23.570
1 2016-11-16 10:40:23.570
2 2016-11-16 10:40:23.570
3 2016-11-21 8:40:23.570


My results would look something like

Name This_Week Last_week
Name1 3 1
Name2 1 1
Name3 1 0


I can get the values separately by doing

WHERE DATEPART(wk, Run_Date) = DATEPART(wk, GETDATE()) AND DATEPART(yy, Run_Date) = DATEPART(yy, GETDATE())


I just add a -1 after the week to get everything from last week. How can I get these to show up side by side in the same table?

I have tried doing a CASE

COUNT(CASE WHEN DATEPART(wk, Run_DATE) = DATEPART(wk, GETDATE()) AND DATEPART(yy, Run_Date) = DATEPART(yy, GETDATE()) THEN 1 ELSE 0 END) AS This_Week


But it looks like it just counts all dates in database for all Names.

Answer
COUNT(CASE WHEN DATEPART(wk, Run_DATE) = DATEPART(wk, GETDATE()) AND DATEPART(yy, Run_Date) = DATEPART(yy, GETDATE()) THEN 1 END) AS This_Week

Take out the ELSE 0.

COUNT will ignore NULLs but ELSE 0 will assign a value. taking it out altogether will mean it won't count the ELSE side of the statement.

Comments