Results 1 to 9 of 9

Thread: [RESOLVED] sql server aggregate query

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

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

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: sql server aggregate query

    Perhaps this?
    Code:
    AND egs.CreateDate <= EGS1.CreateDate

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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.

  4. #4

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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.

  5. #5

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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.

  6. #6

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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.

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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.

  8. #8
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    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

  9. #9

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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
  •  



Click Here to Expand Forum to Full Width