Author |
Topic |
Ganesh Khuspe
Starting Member
8 Posts |
Posted - 2013-02-26 : 00:20:19
|
hi guys,Currently i got stuck in one query..My output is showing me th following result:Name Status DurationGanesh Idle 265456Ganesh OnWork 26547789Ganesh idle 256489ganesh Onwork 154697But I want to show my output as follows,Name Status DurationGanesh Idle 521945Ganesh OnWork 26702486Here Duaration is the total of my Idle for 1 person.Can anybody help me????Thanks in advance... |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-02-26 : 00:47:53
|
[code]select Name, Status, Duration = sum(Duration)from yourtablegroup by Name, Status[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
Ganesh Khuspe
Starting Member
8 Posts |
Posted - 2013-02-26 : 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
52326 Posts |
Posted - 2013-02-26 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
Ganesh Khuspe
Starting Member
8 Posts |
Posted - 2013-02-26 : 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)
17689 Posts |
Posted - 2013-02-26 : 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[spoiler]Time is always against us[/spoiler] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-26 : 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 yourtablegroup by Status ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Ganesh Khuspe
Starting Member
8 Posts |
Posted - 2013-02-26 : 01:26:12
|
I want Result Like followingName Status DurationGanesh Idle 45606654Ganesh Onwork 888654424Ganesh Breaktime 4569874Peter Idle 4569874Peter Onwork 565445656Peter Breaktime 4569874I m having two tables Userstatus and users which gives us the data |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-02-26 : 01:28:13
|
quote: Originally posted by Ganesh Khuspe I want Result Like followingName Status DurationGanesh Idle 45606654Ganesh Onwork 888654424Ganesh Breaktime 4569874Peter Idle 4569874Peter Onwork 565445656Peter 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[spoiler]Time is always against us[/spoiler] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-26 : 01:28:47
|
quote: Originally posted by Ganesh Khuspe I want Result Like followingName Status DurationGanesh Idle 45606654Ganesh Onwork 888654424Ganesh Breaktime 4569874Peter Idle 4569874Peter Onwork 565445656Peter Breaktime 4569874
this you will get from Tan's suggestion itself so far as data is exactly like what you postedIf 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 MVPhttp://visakhm.blogspot.com/ |
|
|
Ganesh Khuspe
Starting Member
8 Posts |
Posted - 2013-02-26 : 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... |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-02-26 : 01:34:56
|
[code]select u.Name, s.Status, sum(datediff(minute, intime, outime)) as durationfrom users u inner join userstatus s on u.userid = s.idgroup by u.Naae, s.Status[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
Ganesh Khuspe
Starting Member
8 Posts |
Posted - 2013-02-26 : 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.statusHere intime and outtimes are my parametersi m getting out put as,Id Name Status Duration1 Ganesh Idle 5654442 peter oncall 4565443 Ganesh Oncall 4566984 Ganesh Break 45615 Ganesh Idle 5654446 peter oncall 4565447 Ganesh Oncall 4566988 Ganesh Break 4561It 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.. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-26 : 01:59:28
|
[code]select id,username,status,SUM(Duration) AS Durationfrom(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 ussinner join users us on uss.usersid=us.idinner join usergroups ug on us.groupid=ug.id inner join campaign camp onus.currentcampaign=camp.idwhere 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 )tgroup by id,username,status[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Ganesh Khuspe
Starting Member
8 Posts |
Posted - 2013-02-26 : 02:07:55
|
Hey Thank you Visakh16...It worked,,,Thanks a lot...M getting the correct output... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-26 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|