Query for Crystal Report - 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

General SQL Server Forums

New to SQL Server Programming

Query for Crystal Report

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 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...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-02-26 : 00:47:53

[code]select Name, Status, Duration = sum(Duration)
from yourtable
group 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 MVP
http://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   yourtable
group by Status

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

Ganesh Khuspe
Starting Member

8 Posts

Posted - 2013-02-26 : 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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-02-26 : 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
[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 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

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 duration
from users u
inner join userstatus s on u.userid = s.id
group by u.Naae, s.Status
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-26 : 01:35:16

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/

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.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..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-26 : 01:59:28

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources