Comment needed on Select, Group or rank query

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)

Comment needed on Select, Group or rank query

Author

Topic

clo
Starting Member

10 Posts

Posted - 2010-08-05 : 01:20:34

Hi,

I need hints on how should I query the “table after running query” from the “table before query”?

Here is some query criteria and concept:
1. Same PID may apply to different package at different time
2. Same PID may apply to same package at different time
3. Final result for the “table after running query” is to get max usertime, max kernel time, min privatebyte, min workingset for each group of dtsx with respective pid and datentime from “table before query”.

Greatly appreciate your comments.

Table before query:

datentime pid command_line usertime kerneltime privatebyte workingset
6/27/2010 2:03:00 AM 1111 a.dtsx 1 1 1 1
6/27/2010 2:03:00 AM 1111 a.dtsx 2 2 2 2
6/27/2010 3:03:00 AM 2222 b.dtsx 3 3 3 3
6/27/2010 3:03:00 AM 2222 b.dtsx 4 4 4 4
6/27/2010 3:04:00 AM 2222 b.dtsx 10 10 10 10
6/27/2010 3:04:00 AM 3333 c.dtsx 11 11 11 11
6/27/2010 3:05:00 AM 2222 b.dtsx 12 12 12 12
6/27/2010 4:04:00 AM 2222 c.dtsx 5 5 5 5
6/27/2010 4:04:00 AM 2222 c.dtsx 6 6 6 6
6/28/2010 5:05:00 AM 1111 a.dtsx 7 7 7 7
6/28/2010 5:05:00 AM 1111 a.dtsx 8 8 8 8
6/28/2010 5:06:00 AM 1111 a.dtsx 9 9 9 9

Table after running query:

datentime pid command_line usertime kerneltime privatebyte workingset
6/27/2010 2:03:00 AM 1111 a.dtsx 2 2 1 1
6/27/2010 3:03:00 AM 2222 b.dtsx 12 12 3 3
6/27/2010 3:04:00 AM 3333 c.dtsx 11 11 11 11
6/27/2010 4:04:00 AM 2222 c.dtsx 6 6 5 5
6/28/2010 5:05:00 AM 1111 a.dtsx 9 9 7 7

Sachin.Nand

2937 Posts

Posted - 2010-08-05 : 01:41:57

select max(usertime), max (kernel time), min (privatebyte), min (workingset) from yourtable
group by pid

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

clo
Starting Member

10 Posts

Posted - 2010-08-05 : 02:34:34

Hi Idera,

If we look at the table before query, there are same pid for different dtsx:
6/27/2010 3:05:00 AM 2222 b.dtsx 12 12 12 12
6/27/2010 4:04:00 AM 2222 c.dtsx 5 5 5 5

also different pid for same dtsx.
6/27/2010 3:04:00 AM 3333 c.dtsx 11 11 11 11
6/27/2010 4:04:00 AM 2222 c.dtsx 5 5 5 5

and same pid and same dtsx at different time:
6/27/2010 2:03:00 AM 1111 a.dtsx 1 1 1 1
6/28/2010 5:05:00 AM 1111 a.dtsx 7 7 7 7

Thus, I'm unable to get the "table after running query" with your query as below. Thanks.

select max(usertime), max (kernel time), min (privatebyte), min (workingset) from yourtable
group by pid

Sachin.Nand

2937 Posts

Posted - 2010-08-05 : 03:05:59

[code]
select pid,command_line,max(usertime), max (kerneltime), min (privatebyte), min (workingset) from yourtable group by pid,command_line,convert(varchar(30),datentime,101)
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

clo
Starting Member

10 Posts

Posted - 2010-08-05 : 05:25:46

Hi Idera,

Thanks so much for your reply.

Below is the result after running your suggested query. it is very close to my desire "Table after running query", except for the 3rd row (2222 b.dtsx 4 4 3 3)

pid command_line usertime kerneltime privatebyte workingset
1111 a.dtsx 2 2 1 1
1111 a.dtsx 9 9 7 7
2222 b.dtsx 4 4 3 3
2222 c.dtsx 6 6 5 5
3333 c.dtsx 11 11 11 11

Referring the "table before query", the max(usertime) and max(kernel time) should be "12" instead of "4".
6/27/2010 3:03:00 AM 2222 b.dtsx 3 3 3 3
6/27/2010 3:03:00 AM 2222 b.dtsx 4 4 4 4
6/27/2010 3:04:00 AM 2222 b.dtsx 10 10 10 10
6/27/2010 3:05:00 AM 2222 b.dtsx 12 12 12 12

By the way, I have edit below query to fit the scenario if there is same pid, same package at different time in a day. please comment.

select pid,command_line,max(usertime), max (kerneltime), min (privatebyte), min (workingset) from yourtable group by pid,command_line,substring(convert(varchar(30),datentime, 120), 0, 14)

Sachin.Nand

2937 Posts

Posted - 2010-08-05 : 05:36:27

quote:
Originally posted by clo

Hi Idera,

Thanks so much for your reply.

Below is the result after running your suggested query. it is very close to my desire "Table after running query", except for the 3rd row (2222 b.dtsx 4 4 3 3)

pid command_line usertime kerneltime privatebyte workingset
1111 a.dtsx 2 2 1 1
1111 a.dtsx 9 9 7 7
2222 b.dtsx 4 4 3 3
2222 c.dtsx 6 6 5 5
3333 c.dtsx 11 11 11 11

Referring the "table before query", the max(usertime) and max(kernel time) should be "12" instead of "4".
6/27/2010 3:03:00 AM 2222 b.dtsx 3 3 3 3
6/27/2010 3:03:00 AM 2222 b.dtsx 4 4 4 4
6/27/2010 3:04:00 AM 2222 b.dtsx 10 10 10 10
6/27/2010 3:05:00 AM 2222 b.dtsx 12 12 12 12

By the way, I have edit below query to fit the scenario if there is same pid, same package at different time in a day. please comment.

select pid,command_line,max(usertime), max (kerneltime), min (privatebyte), min (workingset) from yourtable group by pid,command_line,substring(convert(varchar(30),datentime, 120), 0, 14)

I dont think so.Please run the following query


declare @tbl as table(datentime datetime, pid int ,command_line varchar(30), usertime int,
 kerneltime int, privatebyte int, workingset int)
 insert into @tbl
select '6/27/2010 2:03:00 AM', 1111, 'a.dtsx', 1, 1, 1, 1 union all
select '6/27/2010 2:03:00 AM', 1111, 'a.dtsx', 2, 2, 2, 2  union all
select '6/27/2010 3:03:00 AM', 2222, 'b.dtsx', 3, 3, 3, 3  union all
select '6/27/2010 3:03:00 AM', 2222, 'b.dtsx', 4, 4, 4, 4  union all
select '6/27/2010 3:04:00 AM', 2222, 'b.dtsx', 10,10, 10, 10  union all
select '6/27/2010 3:04:00 AM', 3333,'c.dtsx' ,11, 11, 11, 11   union all
select '6/27/2010 3:05:00 AM' ,2222, 'b.dtsx', 12, 12, 12, 12  union all
select '6/27/2010 4:04:00 AM' ,2222, 'c.dtsx', 5 ,5 ,5, 5  union all
select '6/27/2010 4:04:00 AM' ,2222, 'c.dtsx', 6 ,6 ,6, 6  union all
select '6/28/2010 5:05:00 AM' ,1111, 'a.dtsx' ,7 ,7 ,7, 7  union all
select '6/28/2010 5:05:00 AM' ,1111, 'a.dtsx' ,8 ,8 ,8, 8  union all
select '6/28/2010 5:06:00 AM' ,1111, 'a.dtsx' ,9 ,9 ,9, 9

select * from @tbl order by command_line

select pid,
command_line,
max(usertime)usertime,
max (kerneltime)kerneltime,
min (privatebyte)privatebyte, 
min (workingset)workingset
from @tbl
group by pid,command_line,convert(varchar(30),datentime,101)

And the resultset is follows.



pid         command_line                   usertime    kerneltime  privatebyte workingset
----------- ------------------------------ ----------- ----------- ----------- -----------
1111        a.dtsx                         2           2           1           1
1111        a.dtsx                         9           9           7           7
2222        b.dtsx                         12          12          3           3
2222        c.dtsx                         6           6           5           5
3333        c.dtsx                         11          11          11          11

See the above marked red part in the above resultset.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

clo
Starting Member

10 Posts

Posted - 2010-08-05 : 05:53:49

Hi Idera,

Greatly appreciate your prompt reply.

I run your query, I get same result as stated by you.

However, i get the wrong result when i run it on my table.

I have performed a test, if I deleted the row where usertime ='3' and '4', then I can see the max(usertime) and max(kerneltime) is '12'.

Please advice.

Sachin.Nand

2937 Posts

Posted - 2010-08-05 : 06:01:35

Without seeing all the data in your table it really would be difficult for me to help you in anyway.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

clo
Starting Member

10 Posts

Posted - 2010-08-06 : 02:09:57

Hi Idera,

I found my mistake, it is due to the data type assigned wrongly to the usertime and kerneltime.

Thanks and greatly appreciate your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources