AlmondMan AlmondMan - 1 year ago 29
SQL Question

SQL - Joining to Nonexistent Records

After doing a bit of looking, I thought maybe I'd found a solution here: sql join including null and non existing records. Cross Joining my tables seems like a great way to solve my problem, but now I've hit a snag:

Below are the tables I’m using:

CREATE TABLE [dbo].[DCRSales](
[WorkingDate] [smalldatetime] NOT NULL,
[Store] [int] NOT NULL,
[Department] [int] NOT NULL,
[NetSales] [money] NOT NULL,

CREATE TABLE [dbo].[Stores](
[Number] [int] NOT NULL,
[Has_Deli] [bit] NOT NULL,
[Alcohol_Register] [int] NULL,
[Is_Cost_Saver] [bit] NOT NULL,
[Store_Status] [nchar](10) NOT NULL,
[Supervisor_Number] [int] NOT NULL,
[Email_Address] [nchar](20) NOT NULL,
[Sales_Area] [int] NULL,
[PZ_Store_Number] [int] NULL,
[Has_SCO] [bit] NULL,
[SCO_Reg] [nchar](25) NULL,
[Has_Ace] [bit] NULL,
[Ace_Sq_Ft] [int] NULL,
[Open_Date] [datetime] NULL,
[Specialist] [nchar](2) NULL,
[StateID] [int] NOT NULL)

CREATE TABLE [dbo].[DepartmentMap](
[Department_Number] [int] NOT NULL,
[Description] [nvarchar](max) NOT NULL,
[Parent_Department] [int] NOT NULL)

CREATE TABLE [dbo].[ParentDepartments](
[Parent_Department] [int] NOT NULL,
[Description] [varchar](50) NULL

DCRSales is a table holding new and archived data. The archived data is not perfect, meaning that there are of course certain missing date gaps and some stores which currently have a department they didn't have or no longer have a department they used to have. My goal is to join this table to our department list, list the child departments and parent departments and SUM up the netsales for a given date range. In cases where a store does not have a department whatsoever in that date range, I still need to display it as 0.00.

A more robust solution would probably be to just store all departments for each store regardless of whether they have that department or not (with sales set to 0.00 of course). However I imagine doing that and/or solving my problem here would require very similar queries anyway.

The query I have tried is as follows:

SELECT S.Number as Store, DepartmentMap.Department_Number as Department, ParentDepartments.Parent_Department as Parent, ParentDepartments.Description as ParentDescription, DepartmentMap.Description as ChildDescription
FROM Stores as S CROSS JOIN dbo.DepartmentMap INNER JOIN ParentDepartments ON DepartmentMap.Parent_Department = ParentDepartments.Parent_Department
WHERE S.Number IN(<STORES>) AND Department_Number IN(<DEPTS>)
SELECT CTE.Store, CTE.Department, SUM(ISNULL(DCRSales.NetSales, 0.00)) as Sales, CTE.Parent, CTE.ParentDescription, CTE.ChildDescription
FROM CTE LEFT JOIN DCRSales ON DCRSales.Department = CTE.Department AND DCRSales.Store = CTE.Store
GROUP BY CTE.Store, CTE.Department, CTE.Parent, CTE.ParentDescription, CTE.ChildDescription
ORDER BY CTE.Store ASC, CTE.Department ASC

In this query I try to CROSS JOIN each department to a store from the Stores table so that I can get a combination of every store and every department. I also include the Parent Departments of each department along with the child department's description and the parent department's description. I filter this first portion based on store and department, but this does not change the general concept.

With this result set, I then try to join this table to all of the sales in DCRSales that are within a certain date range. I also include the date if it’s null because the results that have a NULL sales also have a NULL WorkingDate.

This query seemed to work, until I noticed that not all departments are being used with all stores. The stores in particular that do not get combined with all departments are the ones that have no data for the given date range (meaning they have been closed). If there is no data for the department, it should still be listed with its department number, parent number, department description and parent description (with Sales as 0.00). Any help is greatly appreciated.

Answer Source

Your WHERE clause is filtering out records that do have sales at some point in time, but not for the desired period of time, those records don't meet either criteria and are therefore excluded.

I might be under-thinking it, but might just need to move:


To your LEFT JOIN criteria and get rid of WHERE clause. If that's not right, you could filter sales by date in a 2nd cte prior to the join.