1

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

Improve this question
7
  • 1
    First thing to check: your DimDate table. Is there a 2016-10 YR_MONTH?
    – joanolo
    Mar 27, 2017 at 7:33
  • Yes my DimDate table contains the yr_month 2016-10
    – JeffW
    Mar 27, 2017 at 9:40
  • Can you post some sample source data? Definitely some information we're missing in the question.
    – Mark Sinkinson
    Mar 27, 2017 at 10:55
  • 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
    Mar 27, 2017 at 14:42
  • 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.
    – RDFozz
    Mar 27, 2017 at 15:52

1 Answer 1

Reset to default
0

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

Improve this answer
2
  • 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.
    – JeffW
    Mar 28, 2017 at 22:22
  • @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.
    – motosubatsu
    Mar 29, 2017 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.