Getting dates between specific dates comma-separated

Question

I have the following table:

CREATE TABLE dbo.Test
(
    Name        NVARCHAR(50)
    ,StartDate  DATE
    ,EndDate    DATE
)

INSERT INTO dbo.Test VALUES('ABC','28-Feb-14','03-Mar-14')
INSERT INTO dbo.Test VALUES('DEF','04-Mar-14','04-Mar-14')

Basically this contain start and end date of leave for a given user. I am expecting an output as shown below.

Expected output:

Name | WorkHour| LeaveHour | Remarks
    -------------------------------------
ABC  |   27    |     18    | 28-Feb, 03-Mar
DEF  |   36    |      9    | 04-Mar

1 day of leave corresponds to 9 hours and a work week refers to Friday to the next Thursday. In this case that would be from 28-Feb to 06-Mar.

WorkHour refers to number of hours user has worked barring the leaves and not including the weekends.

LeaveHour refers to the number of hours user is on leave.

'Remarks' refers to distinct leaves for the user between StartDate and EndDate values that should appear as comma separated.

I am able to get the work hour (including weekends which is not desired), leave hour values but finding it difficult to have remarks and value excluding weekends

SELECT      
   RN.Name 
   ,ISNULL(45 - ((DATEDIFF(DD, VT.StartDate, VT.EndDate) + 1) * 9 ), 0) AS 'WorkHours'
   ,ISNULL(((DATEDIFF(DD, VT.StartDate, VT.EndDate) + 1) * 9 ), 0) AS 'LeaveHours'
   --distinct leave dates seperated by comma should be displayed as remarks 
FROM        
   Test VT 
LEFT JOIN   
   ResourceNames RN ON VT.UserId = RN.UserId

Can anyone help?

maybe this will help you: http://stackoverflow.com/questions/252519/count-work-days-between-two-dates — Boklucius, Mar 04 '14 at 12:48
works perfect. How about the Remarks column? Any hints on obtaining it? — VKarthik, Mar 04 '14 at 12:59
Yes this is not a column in the table rather that needs to be calculated i.e. should list all the leave dates in comma separated manner. — VKarthik, Mar 04 '14 at 14:39
I edited the code now. Sorry for the confusion. Basically leave dates should against the user should appear comma separated in dd-mmm format. — VKarthik, Mar 04 '14 at 16:28
What's the purpose of doing this in SQL? You're giving yourself a complex task that it's not suited for (databases should be used for storing data, not presentation), and would be far better (and much more simply) handled by whatever software you're outputting the data to. — Kai, Mar 05 '14 at 09:07
Thanks Kai for the concern. Firstly my presentation in this case is to be displayed in report created from SQL Server Reporting Services. Secondly I am not storing this data rather only getting it for presentation. I am only looking for a leaves taken during a week's time frame. — VKarthik, Mar 05 '14 at 12:46

score 0 · Accepted Answer · answered Mar 05 '14 at 08:53

This should not be done in SQL, but here is a function that will do what you want:

create function CSVDates (@startDate datetime, @endDate datetime)
returns nvarchar(4000)
as 
begin
    declare @csv nvarchar(4000) = ''
    declare @maxDays int = DATEDIFF(DD, @startDate, @endDate)
    declare @count int = 0
    declare @date datetime
    while(@count <= @maxDays )
    begin


        if (DATENAME(dw, @date) = 'Saturday' OR DATENAME(dw, @date) = 'Sunday')
        BEGIN
            set @count = @count + 1
            CONTINUE
        END 

        set @date = DATEADD(d,@count, @startDate)
        if (len(@csv) > 0) set @csv = @csv + ','
        set @csv = @csv + DATENAME(day,@date) + '-' + DATENAME(month,@date)

        set @count = @count + 1

    end
    return @csv 
end

plug it into your select as CSVDates(vt.StartDate, vt.EndDate) if you have a lot of dates in between, nvarchar(4000) may not be enough...

Thank you. Straightforward and elegant. Not sure how I missed that. — VKarthik, Mar 05 '14 at 17:52
this is not working for startdatetime and enddatetime given as 2014-04-11 and 2014-04-15. Any idea where I am going wrong? — VKarthik, Apr 17 '14 at 08:47

score 0 · Answer 2 · answered Jul 11 '14 at 04:05

I figured out what was going wrong as finally I had time to work on this. Basically the block under weekend was not allowing date to get incremented which was resulting in the data not appearing.

Here is the working code for someone looking for similar ask

create function CSVDates (@startDate datetime, @endDate datetime)
returns nvarchar(4000)
as 
begin
    declare @csv nvarchar(4000) = ''
    declare @maxDays int = DATEDIFF(DD, @startDate, @endDate)
    declare @count int = 0
    --assign start date
    declare @date datetime = @startDate
    while(@count <= @maxDays )
    begin

     if (DATENAME(dw, @date) = 'Saturday' OR DATENAME(dw, @date) = 'Sunday')
     begin 
            --do nothing
            set @count =@count 
     end    
     else  
     begin
        if (len(@csv) > 0) 
            set @csv = @csv + ','

        set @csv = @csv + DATENAME(day,@date) + '-' + SUBSTRING(DATENAME(month,@date),1,3)
     end

        set @count = @count + 1     
        set @date = DATEADD(d,@count, @startDate)

    end
    return @csv 
end

Getting dates between specific dates comma-separated

2 Answers2