Need Help With SQL Server 2008 Join With 3 Tables

Question

I am wondering if someone can solve this for me please.

I am trying to do a join on two tables. One is a date calendar table with dates from the year 2000 until 2050. This table has column named Year_Month which I want to group by. The 2nd table is a transaction table containing dates and other details. What I want is to return all Year_Months with a count of the transaction records and a zero where data does not exist.

My query is as follows

select dd.YR_MONTH, 
 a.dep_code,
 a.div_code,
 d.dep_name, 
 actions_assigned = isnull(count(a.action_id),0) 
from DimDate dd
Left outer join sr_assigned_to a 
on (assign_date =[DATE] or assign_date is null)
Left outer join sr_dep_codes d 
on (d.dep_code = a.dep_code or d.dep_code is null) 
and (d.div_code = a.div_code or d.div_code is null)
where [date] between DATEADD(m,-13,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0)) 
 and DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
group by a.dep_code, 
 a.div_code, 
 d.dep_name, 
 dd.YR_MONTH

The results returned are missing some months (in this example Oct 2016 is missing)

enter image description here

Where am I going wrong?

Table Schemas

CREATE TABLE [dbo].[sr_assigned_to](
 [request_id] [decimal](8, 0) NOT NULL,
 [responsible_code] [varchar](16) NOT NULL,
 [assign_date] [datetime] NOT NULL,
 [reason_assigned] [varchar](10) NULL,
 [outcome_code] [varchar](10) NULL,
 [outcome_date] [datetime] NULL,
 [status_code] [varchar](10) NULL,
 [comments] [varchar](60) NULL,
 [client_notified] [varchar](1) NULL,
 [notified_via] [varchar](10) NULL,
 [date_notified] [datetime] NULL,
 [finalised_ind] [varchar](1) NULL,
 [assign_time] [datetime] NULL,
 [booked_date] [datetime] NULL,
 [booked_time] [datetime] NULL,
 [officer_notified] [varchar](1) NULL,
 [outcome_time] [datetime] NULL,
 [priority_code] [varchar](1) NULL,
 [action_format] [varchar](1) NULL,
 [collect_extras] [decimal](2, 0) NULL,
 [serial_no] [varchar](10) NULL,
 [position_no] [decimal](3, 0) NULL,
 [after_pos_no] [decimal](3, 0) NULL,
 [amount_held] [decimal](10, 2) NULL,
 [in_time_ind] [varchar](1) NULL,
 [escalated_ind] [varchar](1) NULL,
 [length_no] [decimal](6, 2) NULL,
 [width_no] [decimal](6, 2) NULL,
 [action_id] [decimal](8, 0) NOT NULL,
 [div_code] [varchar](10) NULL,
 [dep_code] [varchar](10) NULL,
 [taken_hrs] [decimal](8, 0) NULL,
 [over_hrs] [decimal](8, 0) NULL,
 [under_hrs] [decimal](8, 0) NULL,
 [posted_ind] [varchar](1) NULL,
 [change_booked_date] [varchar](1) NULL,
 [change_action_reqd] [varchar](1) NULL,
 [change_action_officer] [varchar](1) NULL,
 [change_priority] [varchar](1) NULL,
 [skip_time_taken] [varchar](1) NULL,
 [udf_ind] [varchar](1) NULL,
 [gen_wflow_code] [varchar](10) NULL,
 CONSTRAINT [pk_sr_assigned_to] PRIMARY KEY CLUSTERED 
(
 [action_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DimDate](
 [DATE] [datetime] NOT NULL,
 [NEXT_DAY_DATE] [datetime] NOT NULL,
 [YEAR] [smallint] NOT NULL,
 [YEAR_QUARTER] [int] NOT NULL,
 [YEAR_MONTH] [int] NOT NULL,
 [ALT_YR_MONTH] [varchar](7) NOT NULL,
 [YEAR_DAY_OF_YEAR] [int] NOT NULL,
 [QUARTER] [tinyint] NOT NULL,
 [MONTH] [tinyint] NOT NULL,
 [DAY_OF_YEAR] [smallint] NOT NULL,
 [DAY_OF_MONTH] [smallint] NOT NULL,
 [DAY_OF_WEEK] [tinyint] NOT NULL,
 [YEAR_NAME] [varchar](4) NOT NULL,
 [YEAR_QUARTER_NAME] [varchar](7) NOT NULL,
 [YEAR_MONTH_NAME] [varchar](8) NOT NULL,
 [YEAR_MONTH_NAME_LONG] [varchar](14) NOT NULL,
 [QUARTER_NAME] [varchar](2) NOT NULL,
 [MONTH_NAME] [varchar](3) NOT NULL,
 [MONTH_NAME_LONG] [varchar](9) NOT NULL,
 [WEEKDAY_NAME] [varchar](3) NOT NULL,
 [WEEKDAY_NAME_LONG] [varchar](9) NOT NULL,
 [START_OF_YEAR_DATE] [datetime] NOT NULL,
 [END_OF_YEAR_DATE] [datetime] NOT NULL,
 [START_OF_QUARTER_DATE] [datetime] NOT NULL,
 [END_OF_QUARTER_DATE] [datetime] NOT NULL,
 [START_OF_MONTH_DATE] [datetime] NOT NULL,
 [END_OF_MONTH_DATE] [datetime] NOT NULL,
 [START_OF_WEEK_STARTING_SUN_DATE] [datetime] NOT NULL,
 [END_OF_WEEK_STARTING_SUN_DATE] [datetime] NOT NULL,
 [START_OF_WEEK_STARTING_MON_DATE] [datetime] NOT NULL,
 [END_OF_WEEK_STARTING_MON_DATE] [datetime] NOT NULL,
 [START_OF_WEEK_STARTING_TUE_DATE] [datetime] NOT NULL,
 [END_OF_WEEK_STARTING_TUE_DATE] [datetime] NOT NULL,
 [START_OF_WEEK_STARTING_WED_DATE] [datetime] NOT NULL,
 [END_OF_WEEK_STARTING_WED_DATE] [datetime] NOT NULL,
 [START_OF_WEEK_STARTING_THU_DATE] [datetime] NOT NULL,
 [END_OF_WEEK_STARTING_THU_DATE] [datetime] NOT NULL,
 [START_OF_WEEK_STARTING_FRI_DATE] [datetime] NOT NULL,
 [END_OF_WEEK_STARTING_FRI_DATE] [datetime] NOT NULL,
 [START_OF_WEEK_STARTING_SAT_DATE] [datetime] NOT NULL,
 [END_OF_WEEK_STARTING_SAT_DATE] [datetime] NOT NULL,
 [QUARTER_SEQ_NO] [int] NOT NULL,
 [MONTH_SEQ_NO] [int] NOT NULL,
 [WEEK_STARTING_SUN_SEQ_NO] [int] NOT NULL,
 [WEEK_STARTING_MON_SEQ_NO] [int] NOT NULL,
 [WEEK_STARTING_TUE_SEQ_NO] [int] NOT NULL,
 [WEEK_STARTING_WED_SEQ_NO] [int] NOT NULL,
 [WEEK_STARTING_THU_SEQ_NO] [int] NOT NULL,
 [WEEK_STARTING_FRI_SEQ_NO] [int] NOT NULL,
 [WEEK_STARTING_SAT_SEQ_NO] [int] NOT NULL,
 [JULIAN_DATE] [int] NOT NULL,
 [MODIFIED_JULIAN_DATE] [int] NOT NULL,
 [ISO_DATE] [varchar](10) NOT NULL,
 [ISO_YEAR_WEEK_NO] [int] NOT NULL,
 [ISO_WEEK_NO] [smallint] NOT NULL,
 [ISO_DAY_OF_WEEK] [tinyint] NOT NULL,
 [ISO_YEAR_WEEK_NAME] [varchar](8) NOT NULL,
 [ISO_YEAR_WEEK_DAY_OF_WEEK_NAME] [varchar](10) NOT NULL,
 [DATE_FORMAT_YYYY_MM_DD] [varchar](10) NOT NULL,
 [DATE_FORMAT_YYYY_M_D] [varchar](10) NOT NULL,
 [DATE_FORMAT_MM_DD_YYYY] [varchar](10) NOT NULL,
 [DATE_FORMAT_M_D_YYYY] [varchar](10) NOT NULL,
 [DATE_FORMAT_MMM_D_YYYY] [varchar](12) NOT NULL,
 [DATE_FORMAT_MMMMMMMMM_D_YYYY] [varchar](18) NOT NULL,
 [DATE_FORMAT_MM_DD_YY] [varchar](8) NOT NULL,
 [DATE_FORMAT_M_D_YY] [varchar](8) NOT NULL,
PRIMARY KEY CLUSTERED 
(
 [DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO

I ran the following (joins commented out)

select ALT_YR_MONTH as YR_MONTH
--, a.dep_code,a.div_code,d.dep_name,actions_assigned = isnull(count(action_id),0) 
from DimDate 
--Left outer join sr_assigned_to a on (assign_date =[DATE] or assign_date is null)
--Left outer join sr_dep_codes d on (d.dep_code = a.dep_code or d.dep_code is null) and (d.div_code = a.div_code or d.div_code is null)
where [date] between DATEADD(m,-13,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0)) and DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
group by 
--a.dep_code,a.div_code,d.dep_name, 
ALT_YR_MONTH

and it returns 2016-10 as one of the results

enter image description here

But if I run the code uncommented

select ALT_YR_MONTH as YR_MONTH,
 a.dep_code,
 a.div_code,
 d.dep_name,
 actions_assigned = isnull(count(action_id),0) 
from DimDate 
Left outer join sr_assigned_to a 
on (assign_date =[DATE] or assign_date is null)
Left outer join sr_dep_codes d 
on (d.dep_code = a.dep_code or d.dep_code is null) 
and (d.div_code = a.div_code or d.div_code is null)
where [date] between DATEADD(m,-13,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0)) 
 and DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
group by a.dep_code,
 a.div_code,
 d.dep_name, 
 ALT_YR_MONTH

It drops out 2016-10 where there is no data for the year and month in sr_assigned_to

• joanolo: First thing to check: your DimDate table. Is there a 2016-10 YR_MONTH?
• JeffW: Yes my DimDate table contains the yr_month 2016-10
• Mark Sinkinson: Can you post some sample source data? Definitely some information we're missing in the question.
• RDFozz: Just to be sure - your table definition has a YEAR_MONTH and an ALT_YR_MONTH column, but no YR_MONTH column in DimDate. So, that would imply your query isn't exact, or your table definitions aren't. If the query isn't exactly correct, it's much harder to diagnose something that, say, breaks the LEFT JOIN.
• RDFozz: Suggestion: Run the query with both LEFT JOIN and their columns commented out. Confirm you do see 2016-10. If you don't see it when you put the joins and their columns back in, then something in your query may be forcing a LEFT JOIN to act as an INNER JOIN. If you're looking for a column from one of the LEFT JOIN tables to have a value, make sure you allow for that value to be NULL.

Answer 1

I've set up the tables as per your posted definitions (there wasn't one for sr_dep_codes so I created it with just the fields used in the query) and input some basic data into the tables - one DimDate entry that has a matching sr_assigned_to row and one that doesn't and using bottom version of the query (with the correct reference to ALT_YR_MONTH) it behaves as expected:

select ALT_YR_MONTH as YR_MONTH,
 a.dep_code,
 a.div_code,
 d.dep_name,
 actions_assigned = isnull(count(action_id),0) 
from DimDate 
Left outer join sr_assigned_to a 
on (assign_date =[DATE] or assign_date is null)
Left outer join sr_dep_codes d 
on (d.dep_code = a.dep_code or d.dep_code is null) 
and (d.div_code = a.div_code or d.div_code is null)
where [date] between DATEADD(m,-13,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0)) 
 and DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
group by a.dep_code,
 a.div_code,
 d.dep_name, 
 ALT_YR_MONTH

Results

• JeffW: Many thanks for going to the trouble of testing. If I run your code against my data I get a different result. I will have to do some data quality checks. You have confirmed that my logic is correct. Cheers.
• motosubatsu: @JeffW - yeah, sorry it didn't solve it but hopefully it will get you a bit closer. If you want (and are able) to post the structure for the sr_dep_codes table and some data (both that "works" and that the 2016-10 row that doesn't) I can plug them into my environment here and see if I can replicate what you're seeing.