SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query for Crystal Report
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ganesh Khuspe
Starting Member

India
8 Posts

Posted - 02/26/2013 :  00:20:19  Show Profile  Reply with Quote
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
17627 Posts

Posted - 02/26/2013 :  00:47:53  Show Profile  Reply with Quote
select Name, Status, Duration = sum(Duration)
from   yourtable
group by Name, Status



KH
Time is always against us

Go to Top of Page

Ganesh Khuspe
Starting Member

India
8 Posts

Posted - 02/26/2013 :  01:07:59  Show Profile  Reply with Quote
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??
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/26/2013 :  01:14:45  Show Profile  Reply with Quote
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/

Go to Top of Page

Ganesh Khuspe
Starting Member

India
8 Posts

Posted - 02/26/2013 :  01:20:33  Show Profile  Reply with Quote
Can we combine the all idle records in 1 single row and Can we add those records???
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17627 Posts

Posted - 02/26/2013 :  01:21:18  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/26/2013 :  01:24:52  Show Profile  Reply with Quote
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/

Go to Top of Page

Ganesh Khuspe
Starting Member

India
8 Posts

Posted - 02/26/2013 :  01:26:12  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17627 Posts

Posted - 02/26/2013 :  01:28:13  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/26/2013 :  01:28:47  Show Profile  Reply with Quote
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/

Go to Top of Page

Ganesh Khuspe
Starting Member

India
8 Posts

Posted - 02/26/2013 :  01:32:05  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17627 Posts

Posted - 02/26/2013 :  01:34:56  Show Profile  Reply with Quote

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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/26/2013 :  01:35:16  Show Profile  Reply with Quote
how are they linked?

please post information in below format to avoid unnecessary iterations

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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

Go to Top of Page

Ganesh Khuspe
Starting Member

India
8 Posts

Posted - 02/26/2013 :  01:51:18  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/26/2013 :  01:59:28  Show Profile  Reply with Quote

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/

Go to Top of Page

Ganesh Khuspe
Starting Member

India
8 Posts

Posted - 02/26/2013 :  02:07:55  Show Profile  Reply with Quote
Hey Thank you Visakh16...It worked,,,Thanks a lot...M getting the correct output...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/26/2013 :  02:29:05  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000