Fill in missing Dates for Shift

Question

Post reply

Fill in missing Dates for Shift

SQL_Kills

Hall of Fame

Points: 3154

August 23, 2020 at 3:58 pm

#3782701

Hi,

I have two tables DimCalendar and DimPersonShift, I want a SQL query which will fill in the missing dates that are not in the DimPersonShift table.

SQL Script to Create Tables DimCalendar and DimPersonShift

CREATE TABLE [dbo].[DimCalendar](
[ISOdateKey] [int] NOT NULL,
[DateKey] [smalldatetime] NULL,
[DayNum] [int] NULL,
[WeekDayNum] [int] NULL,
[Day] [varchar](9) NULL,
[MonthNum] [int] NULL,
[Year] [int] NULL,
 CONSTRAINT [PK_dimCalendar] PRIMARY KEY CLUSTERED 
(
[ISOdateKey] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO dbo.DimCalendar VALUES (20200626,'26-Jun-2020',26,5,'Friday',6,2020)
INSERT INTO dbo.DimCalendar VALUES (20200627,'27-Jun-2020',27,6,'Saturday',6,2020)
INSERT INTO dbo.DimCalendar VALUES (20200628,'28-Jun-2020',28,7,'Sunday',6,2020)
INSERT INTO dbo.DimCalendar VALUES (20200629,'29-Jun-2020',29,1,'Monday',6,2020)
INSERT INTO dbo.DimCalendar VALUES (20200630,'30-Jun-2020',30,2,'Tuesday',6,2020)
INSERT INTO dbo.DimCalendar VALUES (20200701,'01-Jul-2020',1,3,'Wednesday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200702,'02-Jul-2020',2,4,'Thursday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200703,'03-Jul-2020',3,5,'Friday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200704,'04-Jul-2020',4,6,'Saturday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200705,'05-Jul-2020',5,7,'Sunday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200706,'06-Jul-2020',6,1,'Monday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200707,'07-Jul-2020',7,2,'Tuesday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200708,'08-Jul-2020',8,3,'Wednesday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200709,'09-Jul-2020',9,4,'Thursday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200710,'10-Jul-2020',10,5,'Friday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200711,'11-Jul-2020',11,6,'Saturday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200712,'12-Jul-2020',12,7,'Sunday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200713,'13-Jul-2020',13,1,'Monday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200714,'14-Jul-2020',14,2,'Tuesday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200715,'15-Jul-2020',15,3,'Wednesday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200716,'16-Jul-2020',16,4,'Thursday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200717,'17-Jul-2020',17,5,'Friday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200718,'18-Jul-2020',18,6,'Saturday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200719,'19-Jul-2020',19,7,'Sunday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200720,'20-Jul-2020',20,1,'Monday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200721,'21-Jul-2020',21,2,'Tuesday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200722,'22-Jul-2020',22,3,'Wednesday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200723,'23-Jul-2020',23,4,'Thursday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200724,'24-Jul-2020',24,5,'Friday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200725,'25-Jul-2020',25,6,'Saturday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200726,'26-Jul-2020',26,7,'Sunday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200727,'27-Jul-2020',27,1,'Monday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200728,'28-Jul-2020',28,2,'Tuesday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200729,'29-Jul-2020',29,3,'Wednesday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200730,'30-Jul-2020',30,4,'Thursday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200731,'31-Jul-2020',31,5,'Friday',7,2020)
INSERT INTO dbo.DimCalendar VALUES (20200801,'01-Aug-2020',1,6,'Saturday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200802,'02-Aug-2020',2,7,'Sunday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200803,'03-Aug-2020',3,1,'Monday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200804,'04-Aug-2020',4,2,'Tuesday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200805,'05-Aug-2020',5,3,'Wednesday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200806,'06-Aug-2020',6,4,'Thursday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200807,'07-Aug-2020',7,5,'Friday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200808,'08-Aug-2020',8,6,'Saturday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200809,'09-Aug-2020',9,7,'Sunday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200810,'10-Aug-2020',10,1,'Monday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200811,'11-Aug-2020',11,2,'Tuesday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200812,'12-Aug-2020',12,3,'Wednesday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200813,'13-Aug-2020',13,4,'Thursday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200814,'14-Aug-2020',14,5,'Friday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200815,'15-Aug-2020',15,6,'Saturday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200816,'16-Aug-2020',16,7,'Sunday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200817,'17-Aug-2020',17,1,'Monday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200818,'18-Aug-2020',18,2,'Tuesday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200819,'19-Aug-2020',19,3,'Wednesday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200820,'20-Aug-2020',20,4,'Thursday',8,2020)
INSERT INTO dbo.DimCalendar VALUES (20200821,'21-Aug-2020',21,5,'Friday',8,2020)

CREATE TABLE [dbo].[DimPersonShift](
[EngineerId] [int] NOT NULL,
[FirstName] [varchar](80) NOT NULL,
[LastName] [varchar](80) NOT NULL,
[Email] [varchar](160) NOT NULL,
[AreaBId] [varchar](15) NOT NULL,
[AreaCId] [varchar](15) NOT NULL,
[AreaDId] [varchar](15) NOT NULL,
[PersonTypeId] [varchar](15) NOT NULL,
[EngineerDate] [datetime] NOT NULL,
[Day] [varchar](9) NOT NULL,
[StartTime] [time] NOT NULL,
[EndTime] [time] NOT NULL,
[LunchMins] [int] NOT NULL,
[AvailDay] [int] NOT NULL,

 CONSTRAINT [PK_DimPersonShift] PRIMARY KEY CLUSTERED 
(
[EngineerId] ASC,
[EngineerDate] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000001','Bill','Gates','Bill.Gates@microsoft.com','LAND','CRANE','LOGIC','Engineer','29-Jul-2020','Wednesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000001','Bill','Gates','Bill.Gates@microsoft.com','LAND','CRANE','LOGIC','Engineer','30-Jul-2020','Thursday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000001','Bill','Gates','Bill.Gates@microsoft.com','LAND','CRANE','LOGIC','Engineer','31-Jul-2020','Friday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000001','Bill','Gates','Bill.Gates@microsoft.com','LAND','CRANE','LOGIC','Engineer','03-Aug-2020','Monday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000001','Bill','Gates','Bill.Gates@microsoft.com','LAND','CRANE','LOGIC','Engineer','04-Aug-2020','Tuesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000001','Bill','Gates','Bill.Gates@microsoft.com','LAND','CRANE','LOGIC','Engineer','05-Aug-2020','Wednesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000001','Bill','Gates','Bill.Gates@microsoft.com','LAND','CRANE','LOGIC','Engineer','06-Aug-2020','Thursday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000001','Bill','Gates','Bill.Gates@microsoft.com','LAND','CRANE','LOGIC','Engineer','07-Aug-2020','Friday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000001','Bill','Gates','Bill.Gates@microsoft.com','LAND','CRANE','LOGIC','Engineer','10-Aug-2020','Monday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000001','Bill','Gates','Bill.Gates@microsoft.com','LAND','CRANE','LOGIC','Engineer','11-Aug-2020','Tuesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000001','Bill','Gates','Bill.Gates@microsoft.com','LAND','CRANE','LOGIC','Engineer','12-Aug-2020','Wednesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000001','Bill','Gates','Bill.Gates@microsoft.com','LAND','CRANE','LOGIC','Engineer','13-Aug-2020','Thursday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000001','Bill','Gates','Bill.Gates@microsoft.com','LAND','CRANE','LOGIC','Engineer','14-Aug-2020','Friday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000001','Bill','Gates','Bill.Gates@microsoft.com','LAND','CRANE','LOGIC','Engineer','17-Aug-2020','Monday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000001','Bill','Gates','Bill.Gates@microsoft.com','LAND','CRANE','LOGIC','Engineer','18-Aug-2020','Tuesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000001','Bill','Gates','Bill.Gates@microsoft.com','LAND','CRANE','LOGIC','Engineer','19-Aug-2020','Wednesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000001','Bill','Gates','Bill.Gates@microsoft.com','LAND','CRANE','LOGIC','Engineer','20-Aug-2020','Thursday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000001','Bill','Gates','Bill.Gates@microsoft.com','LAND','CRANE','LOGIC','Engineer','21-Aug-2020','Friday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','29-Jun-2020','Monday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','30-Jun-2020','Tuesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','01-Jul-2020','Wednesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','02-Jul-2020','Thursday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','03-Jul-2020','Friday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','06-Jul-2020','Monday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','07-Jul-2020','Tuesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','08-Jul-2020','Wednesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','09-Jul-2020','Thursday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','10-Jul-2020','Friday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','13-Jul-2020','Monday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','14-Jul-2020','Tuesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','15-Jul-2020','Wednesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','16-Jul-2020','Thursday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','17-Jul-2020','Friday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','20-Jul-2020','Monday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','21-Jul-2020','Tuesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','22-Jul-2020','Wednesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','23-Jul-2020','Thursday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','24-Jul-2020','Friday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','27-Jul-2020','Monday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','28-Jul-2020','Tuesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','29-Jul-2020','Wednesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','30-Jul-2020','Thursday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','31-Jul-2020','Friday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','03-Aug-2020','Monday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','04-Aug-2020','Tuesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','05-Aug-2020','Wednesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','06-Aug-2020','Thursday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','07-Aug-2020','Friday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','GLOBAL','VILLA','DIVISION','Manager','10-Aug-2020','Monday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','GLOBAL','VILLA','DIVISION','Manager','11-Aug-2020','Tuesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','12-Aug-2020','Wednesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','13-Aug-2020','Thursday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','14-Aug-2020','Friday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','RAND','CRANE','REPORT','Manager','17-Aug-2020','Monday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','SOUTH','SWITCH','SOUTHCOAST','Engineer','18-Aug-2020','Tuesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','SOUTH','SWITCH','SOUTHCOAST','Engineer','19-Aug-2020','Wednesday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','SOUTH','SWITCH','SOUTHCOAST','Engineer','20-Aug-2020','Thursday','09:00:00','17:30:00',60,1)
INSERT INTO [dbo].[DimPersonShift] VALUES  ('1000002','Larry','Ellison','Larry.Ellison@scc.com','SOUTH','SWITCH','SOUTHCOAST','Engineer','21-Aug-2020','Friday','09:00:00','17:30:00',60,1)

So the final result will be like this:

Select *
from [dbo].[DimPersonShift]
UNION
SELECT '1000001' AS EngineerId, 'Bill' AS FirstName, 'Gates' AS LastName, 'Bill.Gates@microsoft.com' AS Email, 'LAND' AS AreaBId, 'CRANE' AS AreaCId, 'LOGIC' AS AreaDId, 'Engineer' AS PersonTypeId, '2020-08-09 00:00:00' AS EngineerDate, 'Sunday' AS Day, '00:00:00' AS StartTime, '00:00:00' AS EndTime, 0 AS LunchMins, 0 AS AvailDay
UNION 
SELECT '1000002' AS EngineerId, 'Larry' AS FirstName, 'Ellison' AS LastName, 'Larry.Ellison@scc.com' AS Email, 'RAND' AS AreaBId, 'CRANE' AS AreaCId, 'REPORT' AS AreaDId, 'Manager' AS PersonTypeId, '2020-07-04 00:00:00' AS EngineerDate, 'Saturday' AS Day, '00:00:00' AS StartTime, '00:00:00' AS EndTime, 0 AS LunchMins, 0 AS AvailDay
UNION 
SELECT '1000002' AS EngineerId, 'Larry' AS FirstName, 'Ellison' AS LastName, 'Larry.Ellison@scc.com' AS Email, 'RAND' AS AreaBId, 'CRANE' AS AreaCId, 'REPORT' AS AreaDId, 'Manager' AS PersonTypeId, '2020-07-05 00:00:00' AS EngineerDate, 'Monday' AS Day, '00:00:00' AS StartTime, '00:00:00' AS EndTime, 0 AS LunchMins, 0 AS AvailDay
UNION 
SELECT '1000002' AS EngineerId, 'Larry' AS FirstName, 'Ellison' AS LastName, 'Larry.Ellison@scc.com' AS Email, 'RAND' AS AreaBId, 'CRANE' AS AreaCId, 'REPORT' AS AreaDId, 'Manager' AS PersonTypeId, '2020-07-11 00:00:00' AS EngineerDate, 'Saturday' AS Day, '00:00:00' AS StartTime, '00:00:00' AS EndTime, 0 AS LunchMins, 0 AS AvailDay
UNION 
SELECT '1000002' AS EngineerId, 'Larry' AS FirstName, 'Ellison' AS LastName, 'Larry.Ellison@scc.com' AS Email, 'RAND' AS AreaBId, 'CRANE' AS AreaCId, 'REPORT' AS AreaDId, 'Manager' AS PersonTypeId, '2020-07-12 00:00:00' AS EngineerDate, 'Monday' AS Day, '00:00:00' AS StartTime, '00:00:00' AS EndTime, 0 AS LunchMins, 0 AS AvailDay
UNION 
SELECT '1000002' AS EngineerId, 'Larry' AS FirstName, 'Ellison' AS LastName, 'Larry.Ellison@scc.com' AS Email, 'RAND' AS AreaBId, 'CRANE' AS AreaCId, 'REPORT' AS AreaDId, 'Manager' AS PersonTypeId, '2020-07-18 00:00:00' AS EngineerDate, 'Saturday' AS Day, '00:00:00' AS StartTime, '00:00:00' AS EndTime, 0 AS LunchMins, 0 AS AvailDay
UNION 
SELECT '1000002' AS EngineerId, 'Larry' AS FirstName, 'Ellison' AS LastName, 'Larry.Ellison@scc.com' AS Email, 'RAND' AS AreaBId, 'CRANE' AS AreaCId, 'REPORT' AS AreaDId, 'Manager' AS PersonTypeId, '2020-07-19 00:00:00' AS EngineerDate, 'Monday' AS Day, '00:00:00' AS StartTime, '00:00:00' AS EndTime, 0 AS LunchMins, 0 AS AvailDay
UNION 
SELECT '1000002' AS EngineerId, 'Larry' AS FirstName, 'Ellison' AS LastName, 'Larry.Ellison@scc.com' AS Email, 'RAND' AS AreaBId, 'CRANE' AS AreaCId, 'REPORT' AS AreaDId, 'Manager' AS PersonTypeId, '2020-07-25 00:00:00' AS EngineerDate, 'Saturday' AS Day, '00:00:00' AS StartTime, '00:00:00' AS EndTime, 0 AS LunchMins, 0 AS AvailDay
UNION 
SELECT '1000002' AS EngineerId, 'Larry' AS FirstName, 'Ellison' AS LastName, 'Larry.Ellison@scc.com' AS Email, 'RAND' AS AreaBId, 'CRANE' AS AreaCId, 'REPORT' AS AreaDId, 'Manager' AS PersonTypeId, '2020-07-26 00:00:00' AS EngineerDate, 'Monday' AS Day, '00:00:00' AS StartTime, '00:00:00' AS EndTime, 0 AS LunchMins, 0 AS AvailDay
UNION 
SELECT '1000002' AS EngineerId, 'Larry' AS FirstName, 'Ellison' AS LastName, 'Larry.Ellison@scc.com' AS Email, 'RAND' AS AreaBId, 'CRANE' AS AreaCId, 'REPORT' AS AreaDId, 'Manager' AS PersonTypeId, '2020-08-01 00:00:00' AS EngineerDate, 'Saturday' AS Day, '00:00:00' AS StartTime, '00:00:00' AS EndTime, 0 AS LunchMins, 0 AS AvailDay
UNION 
SELECT '1000002' AS EngineerId, 'Larry' AS FirstName, 'Ellison' AS LastName, 'Larry.Ellison@scc.com' AS Email, 'RAND' AS AreaBId, 'CRANE' AS AreaCId, 'REPORT' AS AreaDId, 'Manager' AS PersonTypeId, '2020-08-02 00:00:00' AS EngineerDate, 'Monday' AS Day, '00:00:00' AS StartTime, '00:00:00' AS EndTime, 0 AS LunchMins, 0 AS AvailDay
UNION 
SELECT '1000002' AS EngineerId, 'Larry' AS FirstName, 'Ellison' AS LastName, 'Larry.Ellison@scc.com' AS Email, 'RAND' AS AreaBId, 'CRANE' AS AreaCId, 'REPORT' AS AreaDId, 'Manager' AS PersonTypeId, '2020-08-08 00:00:00' AS EngineerDate, 'Saturday' AS Day, '00:00:00' AS StartTime, '00:00:00' AS EndTime, 0 AS LunchMins, 0 AS AvailDay
UNION 
SELECT '1000002' AS EngineerId, 'Larry' AS FirstName, 'Ellison' AS LastName, 'Larry.Ellison@scc.com' AS Email, 'RAND' AS AreaBId, 'CRANE' AS AreaCId, 'REPORT' AS AreaDId, 'Manager' AS PersonTypeId, '2020-08-09 00:00:00' AS EngineerDate, 'Monday' AS Day, '00:00:00' AS StartTime, '00:00:00' AS EndTime, 0 AS LunchMins, 0 AS AvailDay
UNION 
SELECT '1000002' AS EngineerId, 'Larry' AS FirstName, 'Ellison' AS LastName, 'Larry.Ellison@scc.com' AS Email, 'RAND' AS AreaBId, 'CRANE' AS AreaCId, 'REPORT' AS AreaDId, 'Manager' AS PersonTypeId, '2020-08-15 00:00:00' AS EngineerDate, 'Saturday' AS Day, '00:00:00' AS StartTime, '00:00:00' AS EndTime, 0 AS LunchMins, 0 AS AvailDay
UNION 
SELECT '1000002' AS EngineerId, 'Larry' AS FirstName, 'Ellison' AS LastName, 'Larry.Ellison@scc.com' AS Email, 'RAND' AS AreaBId, 'CRANE' AS AreaCId, 'REPORT' AS AreaDId, 'Manager' AS PersonTypeId, '2020-08-16 00:00:00' AS EngineerDate, 'Monday' AS Day, '00:00:00' AS StartTime, '00:00:00' AS EndTime, 0 AS LunchMins, 0 AS AvailDay


Order by 1,9

Thanks

This topic was modified 4 years, 4 months ago by SQL_Kills.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

pietlinden SSC Guru Points: 63506 More actions · Answer 2

Fill in the missing dates in what table? In the shift table?

This should get you started:

SELECT c.DateKey
FROM dimCalendar c
WHERE NOT EXISTS (SELECT 1
                  FROM DimPersonShift ps
                  WHERE ps.EngineerDate = c.DateKey);