CODEWITHSUNDEEP
c#sqlsql-server
Nader Hisham
Nader Hisham

Reputation: 5414

SQL Server generate data using some data in table

Suppose that we have a table in SQL Server that stores shifts for doctors, so instead of storing explicit dates we are using weekdays, the table looks like this

ShiftId  Day    DoctorId    FromTime      ToTime
--------------------------------------------------
1        SUN    1           08:00:00      16:00:00
2        MON    1           09:00:00.00   14:00:00
3        TUE    1           09:00:00.00   15:00:00
4        WED    1           10:00:00.00   17:00:00
5        THU    1           13:00:00.00   18:00:00

I want to create a select statement to generate explicit dates by using data stored in this table

Example

suppose I want to generate dates between Sunday 19th February 2017 to Tuesday 28th February 2017, the output should be like this

DoctorId    Date                Day     FromTime    ToTime
------------------------------------------------------------
1           '02-19-2017'        SUN     08:00:00    16:00:00
1           '02-20-2017'        MON     09:00:00    14:00:00
1           '02-21-2017'        TUE     09:00:00    15:00:00
1           '02-22-2017'        WED     10:00:00    17:00:00
1           '02-23-2017'        THU     13:00:00    18:00:00
1           '02-26-2017'        SUN     08:00:00    16:00:00
1           '02-27-2017'        MON     09:00:00    14:00:00
1           '02-28-2017'        TUE     09:00:00    15:00:00

Explanation

Times generated for these dates correspond to days stored in our table for example the time generated for '02-19-2017' in the first row is 08:00:00 16:00:00 because '02-19-2017' is Sunday, and the time generated for '02-20-2017' in the second row is 09:00:00 14:00:00 because '02-20-2017' is Monday and so on.

As you may notice no dates generated for 24th February FRI and 25th February SAT, because we don't store Friday and Saturday in our table

Can we write a query in T-SQL that returns this result?

Upvotes: 3

Views: 94

Answers (5)

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

You can try this

DECLARE @STARTDate date = '02-19-2017', @ToDate date = '02-27-2017'

;WITH myCTE AS
(
    SELECT @STARTDate AS MyDate, 1 AS iID
    UNION ALL 
    SELECT DATEADD(DAY,1,MyDate), iID + 1
              FROM   myCTE 
              WHERE  MyDate < @ToDate
)
SELECT * FROM YourTable
JOIN myCTE on myCTE.iID = YourTable.ID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You should probably have a calendar table in your database. For this purpose, you can use recursive CTEs or a number table to almost the same effect:

with n as (
      select row_number() over (order by (select null)) - 1 as n
      from master.spt_values
     )
select dd.doctorid, d.dte, s.day, s.fromtime, s.totime
from (select dateadd(day, n.n, @startdate) as dte
      from n
      where dateadd(day, n.n, @startdate) <= @enddate
     ) d cross join
     (select distinct doctorid from shifts
     ) dd join
     shifts s
     on upper(left(datename(wd, d.dte), 3)) = s.day and
        ss.doctorid = d.doctorid;

This is a bit tricky. The idea is to generate the dates (which is pretty easy using the number table). Then do a cross join with the doctors, so all doctors have all dates. Finally, join back to the shifts table, to get the matching records (if any) and the appropriate times.

Note that this version will work for multiple doctors.

Upvotes: 0

Lostblue
Lostblue

Reputation: 419

I think that using SQL for this kind of task is a bad idea due to using a lot of logical operations. Using a C# function should be more efficient.

Using SQL or C# would be the same logical solution:

1 - Create a temporary table that will keep your result.

2 - Loop over your date from starting date to end date. For each date get the DAY part. From the DAY part, get your field FromTime and ToTime. Insert the result on your temporary table.

3 - Return the temporary table (Or Select and destroy it).

Upvotes: 0

Dave C
Dave C

Reputation: 7392

I would use your From/To input parameters to generate a calendar on the fly in a recursive CTE, then join that to your shift table based on the weekday value.

DECLARE @Shift TABLE (ShiftID INT, [Day] VARCHAR(3), DoctorId INT, FromTime TIME, ToTime TIME)
INSERT INTO @Shift 
VALUES (1,'SUN',1,'08:00:00','16:00:00'),
       (2,'MON',1,'09:00:00.00','14:00:00'),
       (3,'TUE',1,'09:00:00.00','15:00:00'),
       (4,'WED',1,'10:00:00.00','17:00:00'),
       (5,'THU',1,'13:00:00.00','18:00:00')

DECLARE @D1 DATE, @D2 DATE
SET @D1 = '2017-02-19'
SET @D2 = '2017-02-28'

;WITH Calendar AS
(
    SELECT @D1 AS [DateVal], LEFT(DATENAME(WEEKDAY,@D1),3) AS [DWName]
    UNION ALL
    SELECT DATEADD(DAY,1,DateVal), LEFT(DATENAME(WEEKDAY,DATEADD(DAY,1,DateVal)),3)
    FROM Calendar
    WHERE DateVal<@D2
)


SELECT S.DoctorId, C.DateVal, S.[Day], S.FromTime, S.ToTime
FROM @Shift S
JOIN Calendar C ON S.[Day]=C.DWName

Upvotes: 2

JohnHC
JohnHC

Reputation: 11195

use a calendar CTE, join to it

with DateCTE as
(
select @DateFrom as Date1
union
select dateadd('DD',1,Date1)
from DateCTE
where Date1 < @DateTo
)
select D1.*,
       datename('DW',Date1) as Day
       FromTime,
       ToTime
from DateCTE D1
left join MyTable M2
  on datename('DW',Date1) like M2.Day
where datename('DW',Date1) not like 'Fri%' 
and datename('DW',Date1) not like 'Sat%'

Upvotes: 0

Related Questions