Advertisement

07.01.2008 at 06:47AM PDT, ID: 23530092
[x]
Attachment Details

Query help with Left Join - MS SQL Server 2005

Asked by immixGroup in MS SQL Server, SQL Query Syntax, SQL Server 2005

Tags: Microsoft, SQL SERVER, 2005, query, T-SQL

I'm writing a query in reporting services to provide a count of all activities entered by a particular user on a per month basis. The trick is, the boss wants to see a count of 0 for any user that hasn't entered activities for that period. Unfortunately, my table only contains entries for stuff added, not blank entries for non activities. I figured I could do a select distinct on everyone that had ever entered anything, and then left join my select that returns the count, and show those users that haven't entered anything in the 2nd select. Showing my code may make this easier:

Simple goal: Show the count for all users in 2nd select (works), but also show 0 for all the users in the 1st select who don't exist in the 2nd select.

Keep in mind, I'd like to avoid using temp tables, thats why my 2nd select is structured as a select of a select (reporting services was having trouble when I was using temp tables).

Any ideas?

Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:

1st Select:
select distinct userid from 
dbo.ActivityTrx
where activitydate > '1/1/2007'
 
2nd Select
Select 
 userid , count(activityiD) as 'Total Activities' ,MONTH, year
from (
select  
activityID, userid,
CASE MONTH(activitydate)
WHEN  1 THEN 'JANUARY'
WHEN  2 THEN 'FEBRUARY'
WHEN  3 THEN 'MARCH'
WHEN  4 THEN 'APRIL'
WHEN  5 THEN 'MAY'
WHEN  6 THEN 'JUNE'
WHEN  7 THEN 'JULY'
WHEN  8 THEN 'AUGUST'
WHEN  9 THEN 'SEPTEMBER'
WHEN  10 THEN 'OCTOBER'
WHEN  11 THEN 'NOVEMBER'
WHEN  12 THEN 'DECEMBER' END AS [MONTH], year(activitydate) as year 
from dbo.ActivityTrx) as ttt
where ttt.Month=@varmonth
and ttt.Year=@varyear
group by userid, Month, year
order by userid, year, month
[+][-]07.01.2008 at 06:54AM PDT, ID: 21907801

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: MS SQL Server, SQL Query Syntax, SQL Server 2005
Tags: Microsoft, SQL SERVER, 2005, query, T-SQL
Sign Up Now!
Solution Provided By: angelIII
Participating Experts: 1
Solution Grade: A
 
 
[+][-]07.01.2008 at 07:17AM PDT, ID: 21908021

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.01.2008 at 07:17AM PDT, ID: 21908029

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.01.2008 at 07:19AM PDT, ID: 21908052

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.01.2008 at 07:36AM PDT, ID: 21908250

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.01.2008 at 07:45AM PDT, ID: 21908347

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628