[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?

Perhaps this?

Code:

---

AND egs.CreateDate <= EGS1.CreateDate

---

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.

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.

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

---

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

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.

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

---

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.