| Author |
 Topic  |
|
|
Ganesh Khuspe
Starting Member
India
8 Posts |
 Posted - 02/26/2013 : 00:20:19
|
hi guys,
Currently i got stuck in one query..My output is showing me th following result:
Name Status Duration
Ganesh Idle 265456
Ganesh OnWork 26547789
Ganesh idle 256489
ganesh Onwork 154697
But I want to show my output as follows,
Name Status Duration
Ganesh Idle 521945
Ganesh OnWork 26702486
Here Duaration is the total of my Idle for 1 person.
Can anybody help me????
Thanks in advance... |
Edited by - Ganesh Khuspe on 02/26/2013 01:21:49
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 02/26/2013 : 00:47:53
|
select Name, Status, Duration = sum(Duration)
from yourtable
group by Name, Status
KH
Time is always against us
|
 |
|
|
Ganesh Khuspe
Starting Member
India
8 Posts |
Posted - 02/26/2013 : 01:07:59
|
| Thanks for reply khtan..but i want distinct status as in the table that i shown above i m having 2 idle and 2 onwork records...now i want only single idle and sing onwork by adding 2 idle and 2 onwork status...what to do?? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/26/2013 : 01:14:45
|
quote:
Originally posted by Ganesh Khuspe
Thanks for reply khtan..but i want distinct status as in the table that i shown above i m having 2 idle and 2 onwork records...now i want only single idle and sing onwork by adding 2 idle and 2 onwork status...what to do??
The query Tan gave will give you only single rows per status value. ie one row for each status for each name so you should get only one row for Idle and Onwork for ganesh
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Ganesh Khuspe
Starting Member
India
8 Posts |
Posted - 02/26/2013 : 01:20:33
|
| Can we combine the all idle records in 1 single row and Can we add those records??? |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 02/26/2013 : 01:21:18
|
quote:
Originally posted by Ganesh Khuspe
Can we combine the all idle records in 1 single row and Can we add those records???
Please do show us the required result
KH
Time is always against us
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/26/2013 : 01:24:52
|
quote:
Originally posted by Ganesh Khuspe
Can we combine the all idle records in 1 single row and Can we add those records???
do you mean this?
select Status, Duration = sum(Duration)
from yourtable
group by Status
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Ganesh Khuspe
Starting Member
India
8 Posts |
Posted - 02/26/2013 : 01:26:12
|
I want Result Like following
Name Status Duration
Ganesh Idle 45606654
Ganesh Onwork 888654424
Ganesh Breaktime 4569874
Peter Idle 4569874
Peter Onwork 565445656
Peter Breaktime 4569874
I m having two tables Userstatus and users which gives us the data
|
Edited by - Ganesh Khuspe on 02/26/2013 01:28:17 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 02/26/2013 : 01:28:13
|
quote:
Originally posted by Ganesh Khuspe
I want Result Like following
Name Status Duration
Ganesh Idle 45606654
Ganesh Onwork 888654424
Ganesh Breaktime 4569874
Peter Idle 4569874
Peter Onwork 565445656
Peter Breaktime 4569874
and how is these data like in the tables ? Please do post the table schema and some sample data from those tables
KH
Time is always against us
|
Edited by - khtan on 02/26/2013 01:28:51 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/26/2013 : 01:28:47
|
quote:
Originally posted by Ganesh Khuspe
I want Result Like following
Name Status Duration
Ganesh Idle 45606654
Ganesh Onwork 888654424
Ganesh Breaktime 4569874
Peter Idle 4569874
Peter Onwork 565445656
Peter Breaktime 4569874
this you will get from Tan's suggestion itself so far as data is exactly like what you posted
If you've any other columns in table which you need to show post us the full details without which we cant guess what exactly you're trying to do!
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Ganesh Khuspe
Starting Member
India
8 Posts |
Posted - 02/26/2013 : 01:32:05
|
My userstatus contain ID,Status,Intime and outime column and users table contain userid,Name,Date of creation,Date of modification ff user.
Status is in Integer formate...data punch into it through service..and i m showing integers as status using case in sql... |
Edited by - Ganesh Khuspe on 02/26/2013 01:34:04 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 02/26/2013 : 01:34:56
|
select u.Name, s.Status, sum(datediff(minute, intime, outime)) as duration
from users u
inner join userstatus s on u.userid = s.id
group by u.Naae, s.Status
KH
Time is always against us
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
|
|
Ganesh Khuspe
Starting Member
India
8 Posts |
Posted - 02/26/2013 : 01:51:18
|
This is my query...
select us.id,us.username,
case uss.status when 1 then 'Idle'
when 3 then 'Idle'
when 9 then 'Idle'
when 2 then 'Break'
when 4 then 'Oncall'
when 5 then 'Oncall'
when 6 then 'Oncall'
when 7 then 'Oncall'
when 8 then 'Oncall'
when 12 then 'Oncall'
when 13 then 'Oncall'
when 14 then 'Other'
when 11 then 'Other'
when 0 then 'Does not logged in yet'
when 10 then 'On Preview Call'
End AS Status,
sum(datediff(ss,uss.intime,uss.outtime))as Duration
from userstatus uss
inner join users us on
uss.usersid=us.id
inner join usergroups ug on
us.groupid=ug.id
inner join campaign camp on
us.currentcampaign=camp.id
where uss.intime between '2012/02/25 00:00:00' and '2013/02/25 23:59:59' and
Uss.outtime between '2012/02/25 00:00:00' and '2013/02/25 23:59:59' and
us.UserName <>'Admin' and Us.Status <> 0 and uss.INTIME IS NOT NULL and
uss.outtime is not null
group by us.id,username,uss.status
Here intime and outtimes are my parameters
i m getting out put as,
Id Name Status Duration
1 Ganesh Idle 565444
2 peter oncall 456544
3 Ganesh Oncall 456698
4 Ganesh Break 4561
5 Ganesh Idle 565444
6 peter oncall 456544
7 Ganesh Oncall 456698
8 Ganesh Break 4561
It is showing the one status more than one time...i want it only one time and duation will show the addition of all the idle status in front of idle,all the oncall infront of oncall for 1 person..
Thank you..
|
Edited by - Ganesh Khuspe on 02/26/2013 02:02:31 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/26/2013 : 01:59:28
|
select id,username,status,
SUM(Duration) AS Duration
from
(
select us.id,us.username,
case uss.status when 1 then 'Idle'
when 3 then 'Idle'
when 9 then 'Idle'
when 2 then 'Break'
when 4 then 'Oncall'
when 5 then 'Oncall'
when 6 then 'Oncall'
when 7 then 'Oncall'
when 8 then 'Oncall'
when 12 then 'Oncall'
when 13 then 'Oncall'
when 14 then 'Other'
when 11 then 'Other'
when 0 then 'Does not logged in yet'
when 10 then 'On Preview Call'
End AS Status,
datediff(ss,uss.intime,uss.outtime) as Duration
from userstatus uss
inner join users us on
uss.usersid=us.id
inner join usergroups ug on
us.groupid=ug.id
inner join campaign camp on
us.currentcampaign=camp.id
where uss.intime between '2012/02/25 00:00:00' and '2013/02/25 23:59:59' and
Uss.outtime between '2012/02/25 00:00:00' and '2013/02/25 23:59:59' and
us.UserName <>'Admin' and Us.Status <> 0 and uss.INTIME IS NOT NULL and
uss.outtime is not null
)t
group by id,username,status
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Ganesh Khuspe
Starting Member
India
8 Posts |
Posted - 02/26/2013 : 02:07:55
|
| Hey Thank you Visakh16...It worked,,,Thanks a lot...M getting the correct output... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/26/2013 : 02:29:05
|
At least in future post the data in required format (see attached link i posted earlier) to avoid these iterations
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Query for Crystal Report
