Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72

    Unanswered: sum up the price and take the date where s=1 (was "Query-Problem")

    Hello,

    I have the follwoing problem:

    I`d like to generate a new table out of the follwing one:

    ID1 PRICE DATE ID2 S
    1C8GYN2M21U130908 10229.00 2005-11-07 00:00:00.000 135816 1
    1FTDX07W0VKB74105 -1500.00 2005-04-15 00:00:00.000 126496 0
    1FTDX07W0VKB74105 14950.00 2005-04-14 00:00:00.000 126496 1
    1G6KY549X1U274486 21301.05 2006-01-17 00:00:00.000 138481 1

    Result:
    ID1 PRICE DATE ID2
    1C8GYN2M21U130908 10229.00 2005-11-07 00:00:00.000 135816
    1FTDX07W0VKB74105 13450.00 2005-04-14 00:00:00.000 126496
    1G6KY549X1U274486 21301.05 2006-01-17 00:00:00.000 138481

    If there is an ID with a S 1 and 0 then sum up the price and take the date where s=1.
    Write the result into another table.

    Can anybody help me ?
    Do I have to implement this by using cursors ?

    Thx.

    Dajm

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by dajm
    Hello,

    I have the follwoing problem:

    I`d like to generate a new table out of the follwing one:

    ID1 PRICE DATE ID2 S
    1C8GYN2M21U130908 10229.00 2005-11-07 00:00:00.000 135816 1
    1FTDX07W0VKB74105 -1500.00 2005-04-15 00:00:00.000 126496 0
    1FTDX07W0VKB74105 14950.00 2005-04-14 00:00:00.000 126496 1
    1G6KY549X1U274486 21301.05 2006-01-17 00:00:00.000 138481 1

    Result:
    ID1 PRICE DATE ID2
    1C8GYN2M21U130908 10229.00 2005-11-07 00:00:00.000 135816
    1FTDX07W0VKB74105 13450.00 2005-04-14 00:00:00.000 126496
    1G6KY549X1U274486 21301.05 2006-01-17 00:00:00.000 138481

    Dajm

    Try this...

    USE pubs
    GO
    CREATE TABLE tt(id1 VARCHAR(10),
    price INT,
    dat DATETIME,
    id2 VARCHAR(10),
    s1 BIT)

    INSERT tt VALUES('e1',1289.43,getdate(),'ee1',1)

    INSERT tt VALUES('e2',333.43,getdate(),'ee2',1)

    INSERT tt VALUES('e1',1200.43,getdate(),'ee1',1)

    INSERT tt VALUES('e1',-444.43,'2006-01-03 14:53:46.817','ee1',0)
    INSERT tt VALUES('e4',44.43,getdate(),'ee4',1)


    SELECT h.id1,sum(h.price)as price,max(h.dat) as date,h.id2 FROM tt h
    GROUP BY h.id1,h.id2

    DROP TABLE tt
    Last edited by rudra; 04-03-06 at 06:39.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudra, your query does not take the date where s=1 as requested
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72

    sum up

    So, R937,

    Do you have another suggestion ?

    Dajm

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what happens when there is only s=0?

    what happens when there is more than one s=1?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72

    sum up the price and take the date where s=1

    Hi r937,

    this is a valid question:

    s=1 will always be there
    s=1 more often than one time does not happen
    s=0 more often than one time can happen and need to be summed up like written before

    Dajm

  7. #7
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by dajm
    Hi r937,

    this is a valid question:

    s=1 will always be there
    s=1 more often than one time does not happen
    s=0 more often than one time can happen and need to be summed up like written before

    Dajm
    so, s1 will be the last date or the first date?? If last then max will do if first then min will do.I would like to hear Rudy's view on it?
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    max and min will be the same
    Code:
    select ID1 
         , sum(PRICE) as PRICE 
         , max(case when S=1 then [DATE] end) as [DATE]
         , ID2 
      from daTable
    group
        by ID1 
         , ID2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by r937
    max(case when S=1 then [DATE] end) as [DATE]
    hmmm....
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  10. #10
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72
    Sry to say,

    but always take the date where s=1, could be the first, could be the last...

    Thx for ur suggestions up to now, but it begins to open my eyes...

    Dajm

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dajm
    Sry to say,
    why are you sry?

    did you try my query?

    by the way, there is no "first" or "last" in a database table

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by dajm
    Sry to say,

    but always take the date where s=1, could be the first, could be the last...

    Thx for ur suggestions up to now, but it begins to open my eyes...

    Dajm
    what sorry? I think Rudy has already given you the solve...
    Last edited by rudra; 04-03-06 at 09:02.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  13. #13
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72
    Aeh, yes, was too late...

    Thx a lot Rudy


Posting Permissions

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