Query required - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query required

Author

Topic

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2007-05-23 : 02:04:43

Below is sample data and i want results like shown.

Sample Data

emp_no|emp_name|emp_status|emp_time|emp_date

0001|John|Present|09:00 AM|Present|09:00 AM|
0002|Smith|Leave| - |Late|11:55 AM|
0003|Clark|Late|11:15 AM|Leave| - |

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-23 : 04:54:36

try this...

declare @t table(emp_no int,emp_name varchar(50),emp_status varchar(20),emp_time varchar(25), emp_date datetime)
insert @t
select 0001,'John','Present','09:00 AM', '05/21/2007' union all
select 0002,'Smith','Leave','-','05/21/2007' union all
select 0003,'Clark','Late','11:15 AM','05/21/2007' union all
select 0001,'John','Present','09:00 AM','05/22/2007' union all
select 0002,'Smith','Late','11:55 AM','05/22/2007' union all
select 0003,'Clark','Leave','-','05/22/2007'

Select a.emp_no,
a.emp_name,
a.emp_status,
a.emp_time,
b.emp_status,
b.emp_time
From @t a join @t b
on a.emp_no = b.emp_no and a.emp_name = b.emp_name and a.emp_date <> b.emp_date
group by a.emp_no,
a.emp_name,
a.emp_status,
a.emp_time,
a.emp_date,
b.emp_status,
b.emp_time
having a.emp_date < max(b.emp_date)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-23 : 06:49:45

[code]
declare @t table(emp_no int,emp_name varchar(10),emp_status varchar(10),emp_time varchar(8), emp_date datetime)
insert @t
select 0001,'John','Present','09:00 AM', '05/21/2007' union all
select 0002,'Smith','Leave','-','05/21/2007' union all
select 0003,'Clark','Late','11:15 AM','05/21/2007' union all
select 0001,'John','Present','09:00 AM','05/22/2007' union all
select 0002,'Smith','Late','11:55 AM','05/22/2007' union all
select 0003,'Clark','Leave','-','05/22/2007'

select t.emp_no, t.emp_name,
emp_status1 = max(case when t.emp_date = m.min_date then t.emp_status end),
emp_time1 = max(case when t.emp_date = m.min_date then t.emp_time end),
emp_status2 = max(case when t.emp_date = m.max_date then t.emp_status end),
emp_time2 = max(case when t.emp_date = m.max_date then t.emp_time end)
from @t t inner join
(
select emp_no,
min_date = min(emp_date),
max_date = max(emp_date)
from @t
group by emp_no
) m
on t.emp_no = m.emp_no
group by t.emp_no, t.emp_name
order by t.emp_no

/*

emp_no emp_name emp_status1 emp_time1 emp_status2 emp_time2
----------- ---------- ----------- --------- ----------- ---------
1 John Present 09:00 AM Present 09:00 AM
2 Smith Leave - Late 11:55 AM
3 Clark Late 11:15 AM Leave -

*/
[/code]

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2007-05-23 : 06:52:16

Thank you so much ... its working ...

One more thing

declare @t table(emp_no int,emp_name varchar(10),emp_status varchar(10),emp_time varchar(8), emp_date datetime)
insert @t
select 0001,'John','Present','09:00 AM', '05/21/2007' union all
select 0002,'Smith','Leave','-','05/21/2007' union all
select 0003,'Clark','Late','11:15 AM','05/21/2007' union all
select 0001,'John','Present','09:00 AM','05/22/2007' union all
select 0002,'Smith','Late','11:55 AM','05/22/2007' union all
select 0003,'Clark','Leave','-','05/22/2007'

If i need to select insert data from a table then how do i write this query.

once again thanks a lot

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-23 : 06:54:09

You don't have to do this. The was for us to test the query. Just use the select query and change the table name to your actual table name

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2007-05-23 : 07:00:31

Now i use select statement with the same query please notify me where i did mistake as there was an error

declare @t Table(emp_no int,emp_name varchar(50),emp_status varchar(20),emp_time varchar(25), emp_date datetime)
insert @t
select emp_no, emp_name, emp_status, emp_time, emp_date from daily_attendance union all

Select a.emp_no,
a.emp_name,
a.emp_status,
a.emp_time,
b.emp_status,
b.emp_time
From @t a join @t b
on a.emp_no = b.emp_no and a.emp_name = b.emp_name and a.emp_date <> b.emp_date
group by a.emp_no,
a.emp_name,
a.emp_status,
a.emp_time,
a.emp_date,
b.emp_status,
b.emp_time
having a.emp_date < max(b.emp_date)

Error msg:
Server: Msg 205, Level 16, State 1, Line 2
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2007-05-23 : 07:20:01

I got it that I dont have to write union all while using select statement.

emp_no emp_name emp_status emp_time emp_date
----------- ---------- ----------- --------- -----------
1 John Present 09:00 AM 21-05-07
2 Smith Leave - 21-05-07
3 Clark Late 11:15 AM 21-05-07

now the problem that I am facing is I got around 40 employees and there are max 31 dates in my table, now how can i manage to get records from the above query?

I need emp_status & emp_time column repeats for each emp_date, is there any loop required to do that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-23 : 08:17:11

quote:
Originally posted by Starlet_GT

I got it that I dont have to write union all while using select statement.

emp_no emp_name emp_status emp_time emp_date
----------- ---------- ----------- --------- -----------
1 John Present 09:00 AM 21-05-07
2 Smith Leave - 21-05-07
3 Clark Late 11:15 AM 21-05-07

now the problem that I am facing is I got around 40 employees and there are max 31 dates in my table, now how can i manage to get records from the above query?

I need emp_status & emp_time column repeats for each emp_date, is there any loop required to do that?

You mean you want to show all 31 diff dates in column ? Are you trying to show a one month calendar of the employee status ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-23 : 08:25:19

Knock, Knock, Madhivanan!
Where are you?? I thought you would have jumped on this thread by now.

Anyway, I take the opportunity to tell you the 100% proven and tested solution for your problem:

DO IT IN THE FRONT-END!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2007-05-23 : 08:31:34

quote:
Originally posted by khtan

quote:
Originally posted by Starlet_GT

I got it that I dont have to write union all while using select statement.

emp_no emp_name emp_status emp_time emp_date
----------- ---------- ----------- --------- -----------
1 John Present 09:00 AM 21-05-07
2 Smith Leave - 21-05-07
3 Clark Late 11:15 AM 21-05-07

now the problem that I am facing is I got around 40 employees and there are max 31 dates in my table, now how can i manage to get records from the above query?

I need emp_status & emp_time column repeats for each emp_date, is there any loop required to do that?

You mean you want to show all 31 diff dates in column ? Are you trying to show a one month calendar of the employee status ?

KH

Yes

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-23 : 08:39:36

can you do this in your front end application or reporting ?

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2007-05-23 : 08:47:27

I had tried it but unable to do it in front end coz there are certain loops which are not allowing me to further insert any loops. If I get this data as described above then it will be very easy.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-23 : 08:47:27

Do it in the front/Report end as others suggested...otherwise take Khtan's query and hardcode the date as below...but if u require for more data...complex

select t.emp_no, t.emp_name,
emp_status1 = max(case when t.emp_date = '05/21/2007' then t.emp_status end),
emp_time1 = max(case when t.emp_date = '05/21/2007' then t.emp_time end),
emp_status2 = max(case when t.emp_date = '05/22/2007' then t.emp_status end),
emp_time2 = max(case when t.emp_date = '05/22/2007' then t.emp_time end),
emp_status3 = max(case when t.emp_date = '05/23/2007' then t.emp_status end),
emp_time3 = max(case when t.emp_date = '05/23/2007' then t.emp_time end),
....,
.....
from @t t
group by t.emp_no, t.emp_name
order by t.emp_no

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-23 : 08:48:50

quote:
Originally posted by Starlet_GT

I had tried it but unable to do it in front end coz there are certain loops which are not allowing me to further insert any loops. If I get this data as described above then it will be very easy.

your 31 days calendar is it always start from 1st of the month ? or it will be rolling window of 31 days

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2007-05-23 : 08:55:48

Yes it always starts from 1st of every month, may be there is any holiday on the first but i have to show that holiday as well in the report.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-23 : 09:04:24

[code]
select t.emp_no, t.emp_name,
emp_status1 = max(case when day(t.emp_date) = 1 then t.emp_status end),
emp_time1 = max(case when day(t.emp_date) = 1 then t.emp_time end),
emp_status2 = max(case when day(t.emp_date) = 2 then t.emp_status end),
emp_time2 = max(case when day(t.emp_date) = 2 then t.emp_time end),
. . .
from @t t
group by t.emp_no, t.emp_name
[/code]

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2007-05-23 : 09:20:15

its mean I have to hardcode it for every day. now I have two questions...
1. can i put month indepandantly so if i give it some month value it can return me the data for desired month?
2. if i want to rename fields then how it will be done? like i need to rename (emp_status1) as Day1?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-23 : 09:26:00

[code]
select t.emp_no, t.emp_name,
~~emp_status1~~ Day1 = max(case when day(t.emp_date) = 1 then t.emp_status end),
emp_time1 = max(case when day(t.emp_date) = 1 then t.emp_time end),
emp_status2 = max(case when day(t.emp_date) = 2 then t.emp_status end),
emp_time2 = max(case when day(t.emp_date) = 2 then t.emp_time end),
. . .
from @t t
where t.emp_date >= @start
and t.emp_date <= @end
group by t.emp_no, t.emp_name
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-23 : 09:31:17

to find the 1st date and last date of the month


declare @date datetime

select	@date = '20070314' -- given a date

select	start_of_mth 	= dateadd(month, datediff(month, 0, @date), 0),
	end_of_mth	= dateadd(month, datediff(month, 0, @date) + 1, -1)

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2007-05-24 : 00:48:18

Thank you guys for the help ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources