Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    12

    Unanswered: Extra Row Needed

    Here is the basic sql I am trying to implement:

    select classid, count(*) as [COUNT], dtmready from unit
    where rmpropid = '123' and classid = 'A1'
    group by rmpropid, classid, dtmready
    order by dtmready;

    Here is my result set:

    A1 3 2006-07-01 00:00:00.000
    A1 10 2006-08-15 00:00:00.000
    A1 11 2006-09-15 00:00:00.000
    A1 10 2006-10-15 00:00:00.000
    A1 10 2006-11-01 00:00:00.000
    A1 10 2006-11-30 00:00:00.000

    If you notice, the earliest dtmready is 7/1/2006. What I need is to return an additional row when the earliest dtmready is after today. The desired row would be:

    A1 0 (today's date)

    Background: I am running SQL Server 2000 SP4 and the results of the query are returned to a java program at a level where I do not have the ability to create a new row. So, it would be ideal if I could create the sql that returns a row with a dtmready of today with a count of 0.

  2. #2
    Join Date
    Apr 2006
    Location
    System.Web.UI.WebControls.Home.BedRoom2
    Posts
    42
    Try this:

    select classid, count(*) as [COUNT], dtmready
    into #tempunit
    from unit
    where rmpropid = '123' and classid = 'A1'
    group by rmpropid, classid, dtmready
    order by dtmready;

    if ((select min(dtmready) from #tempunit)>getdate())
    insert into #tempunit values ('a1','0',getdate())

    select * from #tempunit

    This should work?

  3. #3
    Join Date
    Oct 2004
    Posts
    12
    Thanks for the response.

    I don't think I will be allowed to create a temp table (production database bureaucracy etc.) to solve this problem. Do you have any other ideas?

    Thanks, Mike

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by michaelloveusa
    I don't think I will be allowed to create a temp table (production database bureaucracy etc.) to solve this problem.
    No temp tables? That is bogus. I can see them not wanting you to create permanent tables "temporarily", but there should be nothing wrong with creating true "temp" tables.
    Regardless, here is another method:
    Code:
    select	classid,
    	count(*) as [COUNT],
    	dtmready
    from	unit 
    where	rmpropid = '123'
    	and classid = 'A1' 
    group by rmpropid,
    	classid,
    	dtmready 
    UNION
    select	classid,
    	0 as [COUNT],
    	getdate() as dtmready
    from	unit
    group by classid
    having min(dtmready) > getdate()
    order by dtmready;
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Oct 2004
    Posts
    12
    That did it! Thanks so much for your help!

    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •