locked
Select next value in condition RRS feed

  • Question

  • Hi there, I have simplified my challenge down to  two tables, Customer and Charge, I can pull off the current charge for the customers however the powers above me want those customers who have 0.00 charge at the moment to pull off the next charge for the Customer.

     

    Any help or direction greatly appreciated.

    SQL Server 2005:

    CREATE TABLE [dbo].[Customer]   
    ([id] [int] IDENTITY(1,1) NOT NULL,  
    [reference] [varchar](12) NOT NULL,  
    [name] [varchar](50) NULL)  
     
     
    CREATE TABLE [dbo].[Charge]  
    ([id] [int] IDENTITY(1,1) NOT NULL,  
    [to_customer] [intNOT NULL,  
    [start_date] [datetime] NOT NULL,  
    [end_date] [datetime] NULL,  
    [charge] [decimal](17, 2) NULL)  
     
     
     
     
    INSERT INTO "Customer" ("reference""name"VALUES ('C001''Sam')  
    INSERT INTO "Customer" ("reference""name"VALUES ('C002''David')  
    INSERT INTO "Customer" ("reference""name"VALUES ('D003''Julie')  
    INSERT INTO "Customer" ("reference""name"VALUES ('D004''Ann')  
     
     
     
     
    INSERT INTO "Charge" ("to_customer""start_date""end_date""charge"VALUES ('1''01/01/2008''01/01/2027''1000.00')  
    INSERT INTO "Charge" ("to_customer""start_date""end_date""charge"VALUES ('2''01/03/2000''30/11/2008''2000.00')  
    INSERT INTO "Charge" ("to_customer""start_date""end_date""charge"VALUES ('3''01/12/2008''31/01/2009''0.00')  
    INSERT INTO "Charge" ("to_customer""start_date""end_date""charge"VALUES ('4''10/12/2008''10/06/2009''0.00')  
    INSERT INTO "Charge" ("to_customer""start_date""end_date""charge"VALUES ('2''01/12/2008''30/11/2009''3000.00')  
    INSERT INTO "Charge" ("to_customer""start_date""end_date""charge"VALUES ('3''01/01/2009''31/12/2020''3000.00')  
    INSERT INTO "Charge" ("to_customer""start_date""end_date""charge"VALUES ('4''11/06/2009''31/12/2021''5000.00')  
    INSERT INTO "Charge" ("to_customer""start_date""end_date""charge"VALUES ('2''01/12/2009''30/11/2010''4000.00')  
     

    using the following select I can pull off the current charges:

    SELECT     C.reference, C.name, Ch.charge   
    FROM         Customer C INNER JOIN 
                          Charge Ch ON C.id = Ch.to_customer  
    WHERE     (Ch.start_date <= GETDATE()) AND (Ch.end_date >= GETDATE()) OR 
                         (Ch.start_date <= GETDATE()) AND (Ch.end_date IS NULL)  
    order by C.reference  
     

    however I am looking to try and get the following results

    Ref            Name            Charge
    C001        Sam                1000.00
    C002        David              3000.00
    D003        Julie                3000.00
    D004        Ann                 5000.00

    many thanks for taking the time to read this - it is my first post and I am learning every day so any comments greatly appreciated.

    regards

    Gregor


    Nice to be important - More important to be nice!
    Friday, December 12, 2008 4:28 PM

Answers

  • Håkan,

    Your suggestion can't be correct, even if it seems to work, because the subquery in your CASE expression specifies SELECT TOP 1 charge with no corresponding ORDER BY clause to define what TOP 1 means. Your query will return "the first non-zero value of [charge] for the customer that has a start_date after the current period" where "first" could mean anything. It doesn't even have to mean the same thing from one execution to the next.

    If there are many future non-zero charges listed for this customer, which one do you want? My guess is you want the first one in chronological order, which if it's unique, you can guarantee getting if you add ORDER BY ch2.start_date to the subquery.

    Steve Kass
    • Marked as answer by GregorRoss Friday, December 19, 2008 10:43 AM
    Monday, December 15, 2008 3:52 AM

All replies

  • Good start, and thank you for providing sample code (that's so rare for a first poster!) :)

    Anyhow, what you're going to want to do is turn your current select statement in to a derived table and then perform a LEFT JOIN back to the original table (self join), the condition of the ON clause being that the current value is zero.

    I'd love to write it for you, but I don't have the neccessaries on this PC to run the statement!
    Anyhow, I hope this gives you a good starting point
    George
    Friday, December 12, 2008 7:05 PM
    Answerer
  • George!  A geek without SQL is like a doctor without....um.....doctor tools.  ;)

    Gregor, you could also use an inline subquery to handle this.  This should do:

    SELECT            C.reference, C.name,   
     
                      CASE 
     
                            WHEN Ch.charge = 0 THEN (SELECT TOP 1 charge  
     
                                                                FROM Charge ch2  
     
                                                                WHERE ch2.end_date < ch.end_date  
     
                                                                AND ch2.charge <> 0)  
     
                            ELSE ch.charge  
     
                      END AS charge  
     
    FROM         Customer C INNER JOIN    
     
                          Charge Ch ON C.id = Ch.to_customer     
     
    WHERE     (Ch.start_date <= GETDATE()) AND (Ch.end_date >= GETDATE()) OR    
     
                         (Ch.start_date <= GETDATE()) AND (Ch.end_date IS NULL)     
     
    order by C.reference     
     

    Sorry for the double-spacing.

    And let me reiterate that your original post was great - sample data, create scripts, a clear problem definition, and "this is what I have so far".  Your question is a pleasure to answer!


    Aaron Alton | thehobt.blogpot.com
    Friday, December 12, 2008 7:50 PM
  • @Aaron: With your query and Gregors sample data I got:

    reference  name   charge
    C001         Sam    1000.00
    C002         David  3000.00
    D003         Julie    2000.00
    D004         Ann     2000.00

    However changing a bit in the subquery like this gives the wanted outcome: 

    1 SELECT C.reference, C.[name],      
    2 CASE WHEN Ch.charge = 0   
    3 THEN (SELECT TOP 1 charge   
    4      FROM Charge ch2   
    5      WHERE (ch2.start_date > ch.start_date)   
    6      AND (ch2.charge <> 0)   
    7      AND (ch2.to_customer = Ch.to_customer))   
    8 ELSE ch.charge END AS charge     
    9 FROM Customer C   
    10 INNER JOIN Charge Ch ON C.id = Ch.to_customer        
    11 WHERE (Ch.start_date <= GETDATE()) AND (Ch.end_date >= GETDATE()) OR (Ch.start_date <= GETDATE()) AND (Ch.end_date IS NULL)        
    12 ORDER BY C.reference; 

     

    • Edited by Håkan B Saturday, December 13, 2008 7:18 PM Edit code (testing the features of the new forum)
    Saturday, December 13, 2008 7:11 PM
  • Håkan,

    Your suggestion can't be correct, even if it seems to work, because the subquery in your CASE expression specifies SELECT TOP 1 charge with no corresponding ORDER BY clause to define what TOP 1 means. Your query will return "the first non-zero value of [charge] for the customer that has a start_date after the current period" where "first" could mean anything. It doesn't even have to mean the same thing from one execution to the next.

    If there are many future non-zero charges listed for this customer, which one do you want? My guess is you want the first one in chronological order, which if it's unique, you can guarantee getting if you add ORDER BY ch2.start_date to the subquery.

    Steve Kass
    • Marked as answer by GregorRoss Friday, December 19, 2008 10:43 AM
    Monday, December 15, 2008 3:52 AM
  • He he busted...
    I just copied Aarons code right out of the box and changed it a bit.
    With this test data of course everything is fine.
    Nice catch.
    Monday, December 15, 2008 9:33 AM
  • Thank you very very much, by reading the comments and code from everyone I have now the information that I was looking for, I am very grateful for this. however which one should I mark as answer? everyone has been very helpful. should I click on the code as the answer or those that pointed out what needed to be changed to capture the correct information?

    I am very grateful for everyones efforts and glad I took the time to create the sample code to show the challenge.
    Nice to be important - More important to be nice!
    Monday, December 15, 2008 12:19 PM
  • Great!  Generally speaking, you should "mark as answer" the post or posts which most closely represent the solution you were looking for.  Any others that were helpful in obtaining the solution, but weren't necessarily the answer to your problem (on their own), you can indicate by using the "post was helpful" button.

    HTH.
    Aaron Alton | thehobt.blogspot.com
    Monday, December 15, 2008 1:34 PM
  • Steve Kass said:

    Håkan,


    Your suggestion can't be correct, even if it seems to work, because the subquery in your CASE expression specifies SELECT TOP 1 charge with no corresponding ORDER BY clause to define what TOP 1 means. Your query will return "the first non-zero value of [charge] for the customer that has a start_date after the current period" where "first" could mean anything. It doesn't even have to mean the same thing from one execution to the next.

    If there are many future non-zero charges listed for this customer, which one do you want? My guess is you want the first one in chronological order, which if it's unique, you can guarantee getting if you add ORDER BY ch2.start_date to the subquery.

    Steve Kass



    I'm so glad someone else caught this! I've been exceedingly suprised at the number of people on these forums that don't appreciate the significance of this!

    +5 points, have a cookie :D
    George
    • Edited by gveeEditor Monday, December 15, 2008 2:21 PM grammar
    Monday, December 15, 2008 1:51 PM
    Answerer
  • Aaron Alton said:

    George!  A geek without SQL is like a doctor without....um.....doctor tools.  ;)


    I've recently moved house and have lost access to a lovely, powerful development machine and am back on my old rig... come January I shall be investing in a shiney new replacement though so you might just have to bare with my vague diagnoses for now ;)
    George
    Monday, December 15, 2008 1:54 PM
    Answerer