sql query problem please help

Question

5.00/5 (1 vote)

See more:

hi all,

SQL

create table #leave 
( 
        LeaveId INT IDENTITY(1,1) 
        ,EmployeeId INT 
        ,FromDate Datetime 
        ,ToDate Datetime 
        ,[Status] VARCHAR(50) 
) 
 
insert into #leave values(1,'2011-10-25','2011-10-27','Approved') 
insert into #leave values(2,'2011-10-26','2011-10-27','Approved') 
insert into #leave values(3,'2011-10-27','2011-10-27','Rejected') 
insert into #leave values(1,'2011-11-01','2011-10-01','Approved') 
 
 
select * from #leave

can anyone suggest me the query to return table in this way

EmployeeId       Date                        Status
-----------     -----------------------      --------------
1                 2011-10-25 00:00:00.000  Approved
1                 2011-10-26 00:00:00.000  Approved
1                 2011-10-27 00:00:00.000  Approved
2                 2011-10-26 00:00:00.000  Approved
2                 2011-10-27 00:00:00.000  Approved
3                 2011-10-27 00:00:00.000  Rejected
1                 2011-11-01 00:00:00.000  Approved

the date between FromDate and ToDate are also diplayed with EmployeeId and Status
thanks in advance

Posted 29-Nov-11 21:32pm

mandarapu

Updated 29-Nov-11 21:36pm

Mehdi Gholam

v2

Add a Solution

4 solutions

Solution 3

Here it is :

SQL

with a(i, j, k, l , m)
as
(
  select * from #leave
), b(i, j, k, l , m)
as
(
select a.i, a.j, a.k , a.l , m  from a
union all
select b.i, b.j, DATEADD(d, 1, b.k) , cast(b.l as datetime), b.m  from b
where b.k<b.l
)
select j EmployeeId, k Date, m Status from b
order by i, k

Thanks to the magic of CTEs ;)

Also excuse me for the inconvenient naming style.

Good Luck.

Posted 29-Nov-11 22:22pm

Amir Mahfoozi

Updated 29-Nov-11 23:50pm

v2

Solution 2

select EmployeeId,DateDiff(d,FromDate,ToDate) as [Difference in Day],[Status] from #leave

This will shows the result as your need.

Posted 29-Nov-11 22:13pm

Selva K

Updated 29-Nov-11 22:14pm

v2

Solution 4

hi

VB

select
FromDate,
ToDate,

DATEDIFF(dd,FromDate,ToDate) as DateDifferent

 from #leave

if this helps...do not hesitate to mark it..

Posted 29-Nov-11 22:29pm

dinidusoft123

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

*Karthik Harve* · Accepted Answer · 2011-11-29T22:01:00

TRY WITH THESE QUERIES..

SQL

create table #leave
(
        LeaveId INT IDENTITY(1,1)
        ,EmployeeId INT
        ,FromDate Datetime
        ,ToDate Datetime
        ,[Status] VARCHAR(50)
)

insert into #leave values(1,'2011-10-25','2011-10-27','Approved')
insert into #leave values(2,'2011-10-26','2011-10-27','Approved')
insert into #leave values(3,'2011-10-27','2011-10-27','Rejected')
insert into #leave values(1,'2011-11-01','2011-10-01','Approved')


select * from #leave

IF OBJECT_ID('#sample') IS NOT NULL
    DROP TABLE #sample 
 
SELECT * INTO #sample FROM #leave WHERE 1=2

DECLARE @LeaveID int, @EmpID int
DECLARE @StDate DATETIME, @EndDate DATETIME
DECLARE @status varchar(50)
DECLARE cur CURSOR FOR SELECT * FROM #leave
OPEN cur
FETCH FROM cur INTO @LeaveID,@EmpID,@StDate,@EndDate,@status
PRINT @EndDate
WHILE(@@FETCH_STATUS = 0)
BEGIN
    WHILE(@StDate <= @EndDate)
    BEGIN
        INSERT INTO #sample(EmployeeId,FromDate,[Status]) VALUES(@EmpID,@StDate,@status)
        SET @StDate = DATEADD(DD,1,@StDate)
    END
    FETCH FROM cur INTO @LeaveID,@EmpID,@StDate,@EndDate,@status
END
CLOSE cur

SELECT EmployeeId,FromDate as 'Date',[Status] FROM #sample

HOPE IT WORKS....