Select next value in condition

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] [int] NOT 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!

Answer 1

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

Answer 2

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

Answer 3

@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;

 

Answer 4

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

Answer 5

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.

Answer 6

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!

Answer 7

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

Answer 8

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

Answer 9

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