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
YEAR_MONTH
and anALT_YR_MONTH
column, but noYR_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 theLEFT JOIN
.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 aLEFT JOIN
to act as anINNER JOIN
. If you're looking for a column from one of theLEFT JOIN
tables to have a value, make sure you allow for that value to be NULL.