Updating Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Updating Table

Author

Topic

sindhu sarah
Starting Member

34 Posts

Posted - 2011-10-30 : 04:34:23

Hi all

i need a solution for updating the table.There are 2 tables.one tables has date column with id's.
The other table has 2 date column.one is start date and other is end date.The first table date column needs to be saved
with
2 date column.
For exampl Table A has a date column with data's as follows
2011-08-07 00:00:00
2011-08-08 00:00:00
2011-08-09 00:00:00
2011-08-10 00:00:00
2011-08-11 00:00:00
2011-08-15 00:00:00

The above column need to be saved in table B as
start date End date
2011-08-07 00:00:00 2011-08-11 00:00:00
2011-08-15 00:00:00 2011-08-15 00:00:00

It's like column needs to be saved lik a consecutive date's together.This is for Leave application i.e the employee is leave from
7 th to 11 th of august and again he is on leave on 15 th of august.Hope it is clear.Kindly let me know a good solution and thanks in advance for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-30 : 05:52:25

[code]

declare @tbl table
(
dateval datetime
)
insert @tbl
select '07/08/2011 00:00:00' union all
select '08/08/2011 00:00:00' union all
select '09/08/2011 00:00:00' union all
select '10/08/2011 00:00:00' union all
select '11/08/2011 00:00:00' union all
select '15/08/2011 00:00:00' union all
select '18/08/2011 00:00:00' union all
select '19/08/2011 00:00:00' union all
select '23/08/2011 00:00:00' union all
select '25/08/2011 00:00:00' union all
select '26/08/2011 00:00:00' union all
select '27/08/2011 00:00:00' union all
select '30/08/2011 00:00:00'

;With CTE
AS
(SELECT ROW_NUMBER() OVER (ORDER BY dateval ASC) AS Rn,*
FROM @tbl
),

Dates_CTE
AS
(
SELECT TOP 1 Rn,dateval,1 AS Grp
FROM CTE
UNION ALL
SELECT t.Rn,t.dateval,CASE WHEN DATEDIFF(dd,c.dateval,t.dateval)=1 THEN c.Grp ELSE c.Grp + 1 END
FROM Dates_CTE c
INNER JOIN CTE t
ON t.Rn= c.Rn+1
)

SELECT MIN(dateval) AS Start,MAX(dateval) AS [End]
FROM Dates_CTE GROUP BY Grp

output
-------------------------------------------------------
Start End
2011-08-07 00:00:00.000 2011-08-11 00:00:00.000
2011-08-15 00:00:00.000 2011-08-15 00:00:00.000
2011-08-18 00:00:00.000 2011-08-19 00:00:00.000
2011-08-23 00:00:00.000 2011-08-23 00:00:00.000
2011-08-25 00:00:00.000 2011-08-27 00:00:00.000
2011-08-30 00:00:00.000 2011-08-30 00:00:00.000

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sindhu sarah
Starting Member

34 Posts

Posted - 2011-10-30 : 07:51:24

Thanks a lot for the reply.
The O/P seems to look the same what i was looking for.But when am excuting in SQL 2008,it throws an error.

Msg 242, Level 16, State 3, Line 5
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

(0 row(s) affected)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-30 : 10:04:56

quote:
Originally posted by sindhu sarah

Thanks a lot for the reply.
The O/P seems to look the same what i was looking for.But when am excuting in SQL 2008,it throws an error.

Msg 242, Level 16, State 3, Line 5
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

(0 row(s) affected)

pass date values in yyyy-mm-dd format and you wont have this issue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sindhu sarah
Starting Member

34 Posts

Posted - 2011-10-31 : 01:53:00

Thanks for the solution.But still i am facing problem.Because this is for attendance type.For exampl an employee is on leave from 1st to 31st of august.so when i execute the above query it shows
Start End
2011-08-01 00:00:00.000 2011-08-31 00:00:00.000
But the problem is the employees is on annual vacation from 2011-08-01 00:00:00.000 to 2011-08-15 00:00:00.000 and remaining days he is leave without pay.For ex:
Dates attendance
2011-08-01 00:00:00.000 A
2011-08-02 00:00:00.000 A
etc... A
2011-08-15 00:00:00.000 A
2011-08-16 00:00:00.000 LWP
2011-08-17 00:00:00.000 LWP
etc.. LWP
2011-08-31 00:00:00.000 LWP

Now the result should be showm as
Group by dates and attendance as well
-------------------------------------------------------
Start End
2011-08-01 00:00:00.000 2011-08-15 00:00:00.000
2011-08-16 00:00:00.000 2011-08-31 00:00:00.000

Thanks in advance for your kind reply

sindhu sarah
Starting Member

34 Posts

Posted - 2011-10-31 : 03:10:01

Ya i grouped it with attendance as well..now it work's.I will check it with all other employee's and check for the issue.Thanks a lot for the solution visakh.

sindhu sarah
Starting Member

34 Posts

Posted - 2011-10-31 : 03:10:09

Ya i grouped it with attendance as well..now it work's.I will check it with all other employee's and check for the issue.Thanks a lot for the solution visakh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 04:15:24

quote:
Originally posted by sindhu sarah

Ya i grouped it with attendance as well..now it work's.I will check it with all other employee's and check for the issue.Thanks a lot for the solution visakh.

No problem

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources