July 5, 2015 at 3:17 am
I have the below table :
IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/2015NULL 23:00.0 NULL
10xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0
11xxxxx@gmail.com 15/06/2015NULL 00:00.0 NULL
12xxxxx@gmail.com NULL 15/06/2015 NULL 00:00.0
13xxxxx@gmail.com 14/06/2015NULL 00:00.0 NULL
14xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0
Then i need to replace second row with value with first row with null and so on :
IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/201514/06/2015 23:00.0 00:00.0
10xxxxx@gmail.com NULL NULL NULL NULL
11xxxxx@gmail.com 15/06/201515/06/2015 00:00.0 00:00.0
12xxxxx@gmail.com NULL NULL NULL NULL
13xxxxx@gmail.com 14/06/201514/06/2015 00:00.0 00:00.0
14xxxxx@gmail.com NULL NULL NULL NULL
July 5, 2015 at 3:59 am
quick idea........self join the table eg A.ID+1 = B.ID
but the question is why alter the exg data to NULL
....what is the benefit / business reason.....why cant a simple view as above provide the necessary result?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 5, 2015 at 4:03 am
Actually i have google calendar file ics, when import it , it comes in one column everything so the whole script i did to have the previouse table is the below, also the first row and second must be in same line and third, fourth must be in same line and so on :
Delete From User1
create table #Temp (Calendar_Data nvarchar(1000))
BULK INSERT #Temp FROM 'D:\basic.ics'
ALTER Table #Temp
ADD name nvarchar(500),
Date_Start date,
Date_End date,
Time_Start varchar(20),
Time_End varchar(20)
Update #Temp
Set name = (Select SUBSTRING(a.Calendar_Data, CHARINDEX(':', a.Calendar_Data) + 1, LEN(a.Calendar_Data)) As 'Name' from #Temp a where a.Calendar_Data like 'X-WR-CALNAME%')
,Date_Start = (Select SUBSTRING(b.Calendar_Data, CHARINDEX(':', b.Calendar_Data) + 1, 8) from #Temp b where b.Calendar_Data like 'DTSTART%' AND #Temp.Calendar_Data = b.Calendar_Data )
, Date_End = (Select SUBSTRING(c.Calendar_Data, CHARINDEX(':', c.Calendar_Data) + 1, 8) from #Temp C where c.Calendar_Data like 'DTEND%' AND #Temp.Calendar_Data = C.Calendar_Data )
, Time_Start = (Select SUBSTRING(d.Calendar_Data, CHARINDEX(':', d.Calendar_Data) + 10, 6) from #Temp d where d.Calendar_Data like 'DTSTART%' AND #Temp.Calendar_Data = d.Calendar_Data )
, Time_End = (Select SUBSTRING(e.Calendar_Data, CHARINDEX(':', e.Calendar_Data) + 10, 6) from #Temp e where e.Calendar_Data like 'DTEND%' AND #Temp.Calendar_Data = e.Calendar_Data )
Delete from #Temp where Date_Start is null and Date_End is null and Time_Start is null and Time_End is null
-- Insert into User table
Insert into User1 (Name,Date_Start,Date_End,Time_Start,Time_End)
Select name,Date_Start,Date_End,
(select cast(msdb.dbo.agent_datetime('19000101', Time_Start) as time(0)))
,(select cast(msdb.dbo.agent_datetime('19000101', Time_End) as time(0)))
from #Temp
Drop table #Temp
July 5, 2015 at 4:15 pm
Thank you , Working
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply