Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SQL to split row by date (split into multiple rows) Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 11:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 06, 2013 9:53 AM
Points: 9, Visits: 78
Hi Chris,

Thanks for your response. I will test this further.

How would I include time along with a date in the function as I completely forgot to mentioned the StartDate and EndDate are datetime fields?
Post #1464793
Posted Tuesday, June 18, 2013 4:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 06, 2013 9:53 AM
Points: 9, Visits: 78
A slightly related question. If I have the table as per the DDL below, how do I remove rows where the status is 'temp' for overlapping rows by date? In the example below, row with ID 5 will be removed as it overlaps with ID 4 as they are for the same person and job.

if OBJECT_ID('tempdb..#Something') is not null
drop table #Something
GO

set dateformat dmy
Go

create table #Something
(
ID int,
Employee varchar(20),
Job varchar(20),
StartDate datetime,
EndDate datetime,
[Workload] int,
[Status] varchar(30)
)

insert #Something
select *
from (Values
(1, 'John Doe', 'HSBC', '01/01/2013', '31/12/2013', 100, 'temp')
,(2, 'John Doe', 'Vacation', '17/06/2013', '21/06/2013', 100, 'confirmed')
,(3, 'John Doe', 'Vacation', '19/08/2013', '23/08/2013', 100, 'temp')
,(4, 'John Doe', 'Barclays', '01/01/2014', '31/01/2014', 50, 'confirmed')
,(5, 'John Doe', 'Barclays', '06/01/2014', '31/01/2014', 50, 'temp')
,(6, 'John Doe', 'Vacation', '13/01/2014', '25/01/2014', 100, 'confirmed')
) x(a,b,c,d,e,f,g)

select *
from #Something

Post #1464905
Posted Wednesday, July 03, 2013 10:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 06, 2013 9:53 AM
Points: 9, Visits: 78
Just wanted to add that the queries from SSC Veteran and ChrisM@Work are the solutions I went with. Thank you.
Post #1470161
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse