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)
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
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
Best Answer
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: