How to create a specific SQL Server stored procedure? [closed]

by

Yet another option.

Not sure if I agree with the last record of the desired results. I have 2019-01-07 while you have 2019-01-08

Example or dbFiddle

Select A.[Name]
      ,[Begin_Date] = convert(datetime,left(dateadd(DAY,N       ,[Begin_date]),10)+' '+BegTime)
      ,[End_Date]   = convert(datetime,left(dateadd(DAY,N+NxtDay,[Begin_date]),10)+' '+EndTime)
 From  YourTable A
 Cross Apply ( values ( left(replicate(Schedule,10),datediff(DAY,Begin_Date,End_Date)) ) )B(S)
 Cross Apply ( 
               Select N=N-1
                     ,Subs=substring(B.S,N,1)
                 From (  Select Top (len(S)+1) N=Row_Number() Over (Order By (Select Null)) From  master..spt_values n1 ) B1
             ) C
 Join ( values ('m','08:00','20:00',0)
              ,('n','20:00','08:00',1)
              ,('d','08:00','08:00',1)
      ) D(SchdCd,BegTime,EndTime,NxtDay) on Subs=SchdCd
 Order By [Begin_Date]

Results

Name    Begin_Date                  End_Date
John    2019-01-01 20:00:00.000     2019-01-02 08:00:00.000
John    2019-01-03 08:00:00.000     2019-01-03 20:00:00.000
John    2019-01-05 20:00:00.000     2019-01-06 08:00:00.000
John    2019-01-07 08:00:00.000     2019-01-07 20:00:00.000

More Related Contents:

  1. SQL where clause odd behavior [closed]
  2. Select distinct record from table and perform Sum of column (Pallats, Gross) of a duplicate row. And show that duplicate rows once [closed]
  3. How to extrapolate dates in SQL Server to calculate the daily counts?
  4. How do I split a string so I can access item x?
  5. When should I use CROSS APPLY over INNER JOIN?
  6. get a comma delimited string from rows [duplicate]
  7. Find all tables containing column with specified name – MS SQL Server
  8. SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, =
  9. How to split a single column values to multiple column values?
  10. Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
  11. Formatting Numbers by padding with leading zeros in SQL Server
  12. When no ‘Order by’ is specified, what order does a query choose for your record set?
  13. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  14. For Nvarchar(Max) I am only getting 4000 characters in TSQL?
  15. How do I create a foreign key in SQL Server?
  16. How do I group on continuous ranges
  17. join comma delimited data column
  18. Need to list all triggers in SQL Server database with table name and table’s schema
  19. Count based on condition in SQL Server
  20. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  21. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  22. Best equivalent for IsInteger in SQL Server
  23. Store multiple bit values in a single table column
  24. Syntax of for-loop in SQL Server
  25. How do you copy a record in a SQL table but swap out the unique id of the new row?
  26. Using IF ELSE statement based on Count to execute different Insert statements
  27. SQL – Call Stored Procedure for each record
  28. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  29. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  30. Date serial in SQL?

Leave a Comment