0

Have an issue that has been driving me nuts for the past week and can't seem to get my head around it.

I have a stored procedure that grabs a bunch of vacation requests from our scheduling system. I need to now get each day that the individual has requested into a single row to pivot and plot in excel at the request of the user.

SourceTable

This is the end result I am looking for in a SQL Table

FinalProd

Any help is appreciated, already have a datedimension table created, tried using CTE and another attempt using outer apply but at this point I am spinning my wheels.

UPDATE: Calendar Table

This is the table (Calendar Table) I am using - Populated with 30 years of dates

CREATE TABLE [dbo].[DateDimension](
    [Date] [date] NOT NULL,
    [Day] [tinyint] NOT NULL,
    [DaySuffix] [char](2) NOT NULL,
    [Weekday] [tinyint] NOT NULL,
    [WeekDayName] [varchar](10) NOT NULL,
    [IsWeekend] [bit] NOT NULL,
    [IsHoliday] [bit] NOT NULL,
    [HolidayText] [varchar](64) SPARSE  NULL,
    [DOWInMonth] [tinyint] NOT NULL,
    [DayOfYear] [smallint] NOT NULL,
    [WeekOfMonth] [tinyint] NOT NULL,
    [WeekOfYear] [tinyint] NOT NULL,
    [ISOWeekOfYear] [tinyint] NOT NULL,
    [Month] [tinyint] NOT NULL,
    [MonthName] [varchar](10) NOT NULL,
    [Quarter] [tinyint] NOT NULL,
    [QuarterName] [varchar](6) NOT NULL,
    [Year] [int] NOT NULL,
    [MMYYYY] [char](6) NOT NULL,
    [MonthYear] [char](7) NOT NULL,
    [FirstDayOfMonth] [date] NOT NULL,
    [LastDayOfMonth] [date] NOT NULL,
    [FirstDayOfQuarter] [date] NOT NULL,
    [LastDayOfQuarter] [date] NOT NULL,
    [FirstDayOfYear] [date] NOT NULL,
    [LastDayOfYear] [date] NOT NULL,
    [FirstDayOfNextMonth] [date] NOT NULL,
    [FirstDayOfNextYear] [date] NOT NULL
) ON [PRIMARY]
GO
2
  • 1
    The best way long term is to use a calendar table. For better service, read this meta post: Help me write this query in SQL. Commented Sep 6, 2019 at 15:57
  • Thanks Erik, I have a calendar table, went down this road a bit too. Struggling with how I get the data into the format I need, joining the calendar table and vacation table together. Knowing that multiple people may be requesting the same day off.
    – CraigK
    Commented Sep 6, 2019 at 16:02

2 Answers 2

1

Although you can get your desired result by dates generation during run-time with CROSS APPLY (as per Tony Hinkle's answer). in performance point of view it's better to use calendar table. Good that you already have it.

This is what expected part of the question (as per Erik Darling comment):

Create table #Vacation 
(Requestor varchar(10),
SchCode varchar(10),
ReqLine int,
FromDate datetime,
ToDate datetime,
Comment varchar (100)  )
go


Insert into #Vacation
values
('Smith', 'V12', 1, '2019-09-01', '2019-09-03', 'Vacation Request')
go


Create table #DateDimension
(
    [Date] [date] NOT NULL,
    [Day] [tinyint] NOT NULL
)
go

Insert into #DateDimension 
values 
('2019-09-01', 1),
('2019-09-02', 2),
('2019-09-03', 3),
('2019-09-04', 4),
('2019-09-05', 5)
go


--Drop Table #Vacation;
--Drop table #DateDimension;

You can get desired result utilizing datedimension table, as follows:

select  d.Date,
        s.Requestor,
        s.SchCode
from #Vacation as s
        left join #DateDimension as d 
            on d.Date >= s.FromDate and d.Date <= s.ToDate
1
  • Thanks! this is all great information, tested this as well and it worked for what I needed.
    – CraigK
    Commented Sep 6, 2019 at 17:07
1

You can do this with a CROSS APPLY that will generate the dates.

SELECT m.requestor, m.schedcode, o.Date FROM mockup m
CROSS APPLY (
    SELECT  TOP (DATEDIFF(DAY, m.fromdate, m.enddate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, m.fromdate)
        FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b
) AS o

Props to GarethD's answer at Get all dates between two dates in SQL Server for the date generating code.

3
  • This is perfect! Thank you, and thanks to the community for a quick answer!
    – CraigK
    Commented Sep 6, 2019 at 16:13
  • Never use TOP without an ORDER BY. Top according to which order?
    – Ilyes
    Commented Sep 7, 2019 at 13:26
  • It doesn't matter in this case. It is just pulling some random rows to be able to use ROW_NUMBER(). Commented Sep 8, 2019 at 11:13

Your Answer

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

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