-
May 7th, 2013, 11:34 AM
#1
Thread Starter
PowerPoster
[RESOLVED] sql server aggregate query
Here are some rows from my table. You can see they are all for GroupID 57. I am interested in the StatusID = 5 and 6 records. I have to produce a report saying how many days the item spent in between status 5 and 6. So the first pair of 5/6 records (RowID 122 and 674) don't count because there's another newer pair. I want to build a table that has a row with three columns something like 57 - 11:02:17 - 11:02:59 (I left off the mm/dd/yy for the sake of brevity).
RowId GroupID StatusID CreateDate
120 57 1 2011-09-02 06:29:32.290
121 57 3 2011-09-02 06:36:32.437
122 57 5 2011-09-02 06:38:37.633
674 57 6 2011-09-20 11:02:01.830
675 57 5 2011-09-20 11:02:17.340
676 57 6 2011-09-20 11:02:59.697
However, it's possible there could be another 5 record even newer, such as this:
RowId GroupID StatusID CreateDate
120 57 1 2011-09-02 06:29:32.290
121 57 3 2011-09-02 06:36:32.437
122 57 5 2011-09-02 06:38:37.633
674 57 6 2011-09-20 11:02:01.830
675 57 5 2011-09-20 11:02:17.340
676 57 6 2011-09-20 11:02:59.697
677 57 5 2011-09-20 11:05:36.990
I don't want any row at all in that case, because I can't calculate the time since it's still status 5.
I started to write a query that looks like this, but you can see where I am having a problem:
Code:
select
egs.groupid,
MAX(egs.groupstatusid)
from GroupStatus egs
inner join
(
select
groupid,
MAX(groupstatusid) as x
from GroupStatus egs
where statusid=6
group by Groupid
)EGS1
on egs.GroupID = EGS1.GroupID
where egs.StatusID = 5
-- you need something in here so you get 675 and not 677
group by egs.GroupID
What do I need to do here?
Last edited by MMock; May 7th, 2013 at 11:38 AM.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 7th, 2013, 11:44 AM
#2
Re: sql server aggregate query
Perhaps this?
Code:
AND egs.CreateDate <= EGS1.CreateDate
-
May 7th, 2013, 11:49 AM
#3
Thread Starter
PowerPoster
Re: sql server aggregate query
CreateDate is not a column in the table aliased by EGS1.
Also, I would want to use the row id rather than the date, but if I could just figure out how to do it one way I think I'd know how to do it other ways too.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 7th, 2013, 12:09 PM
#4
Thread Starter
PowerPoster
Re: sql server aggregate query
So it would be this:
Code:
-- This return 57 675
select
egs.enrollgroupid,
MAX(egs.enrollgroupstatusid) EGStatusID
from EnrollGroupStatus egs
inner join
(
-- This returns: 57 676
select
enrollgroupid,
MAX(enrollgroupstatusid) as x
from EnrollGroupStatus egs
where enrollstatusid=6
group by EnrollGroupid
)EGS1
on egs.EnrollGroupID = EGS1.EnrollGroupID
where egs.EnrollStatusID = 5
AND egs.enrollgroupstatusid <= EGS1.x
group by egs.EnrollGroupID
I still have more to figure out but thanks for the help so far.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 7th, 2013, 12:47 PM
#5
Thread Starter
PowerPoster
Re: sql server aggregate query
So if this is my table being built:
Code:
GroupID Status5RowId CreateDateStatus5 Status6RowId CreateDateStatus6
43 95 NULL NULL NULL
57 675 NULL NULL NULL
I next want to update EnrollStatus6RowId with 676, because 676 is my max GroupStatusId of my 6 records. But I get an error that I can't do a MAX in a SET (An aggregate may not appear in the set list of an UPDATE statement):
Code:
update @FinalStatus_5and6_RowID
set Status6RowId = MAX(groupstatusid)
from @FinalStatus_5and6_RowID A
inner join GroupStatus B
on A.GroupID = B.GroupID
and B.StatusID = 6
Last edited by si_the_geek; May 7th, 2013 at 12:58 PM.
Reason: added tags to data
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 7th, 2013, 12:47 PM
#6
Thread Starter
PowerPoster
Re: sql server aggregate query
I apologize for my formatting - I don't know why it looks good when I'm typing it then when I post it, it all scrunches over. I know it's awful to read that way...
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
May 7th, 2013, 01:02 PM
#7
Re: sql server aggregate query
The forum software removes duplicate spaces from text... the best way I know of is to use Code tags, I added them to your post above.
Rather than using a from clause associated with the Update, try using a sub-query:
Code:
update @FinalStatus_5and6_RowID A
set Status6RowId = (SELECT MAX(groupstatusid)
fromGroupStatus B
on A.GroupID = B.GroupID
and B.StatusID = 6)
..this probably wont work as-is, my brain is fried at the moment.
-
May 7th, 2013, 01:12 PM
#8
Re: sql server aggregate query
You could break your data in to temp tables to make it easier to work with
Code:
declare @table table(RowId int primary key, GroupId int, StatusId int, CreateDate datetime)
--create sample data
INSERT INTO @table(RowId, GroupId, StatusId, CreateDate)
Values
(120, 57, 1, '2011-09-02 06:29:32.290'),
(121, 57, 3, '2011-09-02 06:36:32.437'),
(122, 57, 5, '2011-09-02 06:38:37.633'),
(674, 57, 6, '2011-09-20 11:02:01.830'),
(675, 57, 5, '2011-09-20 11:02:17.340'),
(676, 57, 6, '2011-09-20 11:02:59.697'),
(677, 57, 5, '2011-09-20 11:05:36.990')
--get max rowid for status code six
SELECT MAX(rowid) RowId, GroupId, MAX(CreateDate) CreateDate
INTO #six
FROM @table
WHERE StatusId = 6
GROUP by GroupId
--get max rowid for status code five when there is a six record after the five record
SELECT MAX(t.rowid) RowId, t.GroupId, MAX(t.CreateDate) CreateDate
INTO #five
FROM @table t
INNER JOIN #six six on six.GroupId = t.GroupId and t.RowId < six.RowId
WHERE StatusId = 5
GROUP by t.GroupId
--query or update here
SELECT five.GroupId
, five.RowId 'fiverowid'
, six.RowId 'sixrowid'
, five.CreateDate 'fivecreate'
, six.CreateDate 'sixcreate'
, DATEDIFF(SECOND,five.CreateDate,six.CreateDate) as 'Diff'
FROM #five five
INNER JOIN #six six ON six.GroupId = five.GroupId
DROP TABLE #five
DROP TABLE #six
That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma
Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney
-
May 9th, 2013, 11:53 AM
#9
Thread Starter
PowerPoster
Re: sql server aggregate query
Hi wild_bill - I've been having problems posting lately...recently I double-posted the same reply somehow while here I zero-posted though I am sure I replied the other day that temp tables were what I was going to use... Just came in now to mark resolved and saw that no reply had been posted. But yes, I went with your suggestion and that was exactly what I needed to do. Thanks.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|