Junchen Liu Junchen Liu - 3 days ago 6
SQL Question

SQL Server Find the date in joining order

I am using MS-SQL Server there are two tables

membership

+---+-----------------+---------------------+----------------
| | membershipName | createddate | price |
+---+-----------------+---------------------+----------------
| 1 | Swimming | 2010-01-01 | 30 |
| 2 | Swimming | 2010-05-01 | 32 |
| 3 | Swimming | 2011-01-01 | 35 |
| 4 | Swimming | 2012-01-01 | 40 |
+---+-----------------+---------------------+----------------


member

+---+-----------------+---------------------+-----------------
| | memberName | membership | joiningDate |
+---+-----------------+---------------------+-----------------
| 0 | Andy | Swimming | 2008-02-02 |
| 1 | John | Swimming | 2010-02-02 |
| 2 | Andy | Swimming | 2011-02-02 |
| 3 | Alice | Swimming | 2015-02-02 |
+---+-----------------+---------------------+----------------


I want find the member's membership price for the right period of time

e.g


  • Andy return NULL

  • John return 30

  • Alice return 40



the best logic is to see

if the joiningDate is in between two start date

if yes choose the earlier date


if not

if the joining date is before the earlier date then use the earliest date
if the joining date is after the latest date then use the latest date


I am a Java programmer, do this in sql is quite tricky for me, any hint would be nice!


  • edit 1: sorry I forgot to consider month

  • edit 2: added desirable result


Answer

I hope I got this correctly. You might try it like this:

Declared table variable to mock-up a test scenario:

EDIT no treatment for the last needed... (ISNULL before LEAD)

DECLARE @membership TABLE(id INT, membershipName VARCHAR(100),createddate DATETIME,price DECIMAL(10,4));
INSERT INTO @membership VALUES
 (1,'Swimming',{d'2010-01-01'},30)
,(2,'Swimming',{d'2010-05-01'},32)
,(3,'Swimming',{d'2011-01-01'},35)
,(4,'Swimming',{d'2012-01-01'},40);

DECLARE @member TABLE(id INT,memberName VARCHAR(100),membership VARCHAR(100),joiningDate DATETIME);
INSERT INTO @member VALUES
 (0,'Andy','Swimming',{d'2008-02-02'})   
,(1,'John','Swimming',{d'2010-02-02'})
,(2,'Andy','Swimming',{d'2011-02-02'})
,(3,'Alice','Swimming',{d'2015-02-02'});

As you are on SQL-Server 2012 you are lucky. You can use LEAD:

The CTE "Intervalls" will collect return the membership table as is and it will add one column with one second before the next rows createddate. LEAD helps you to get hands on a value of a later coming row. First I take away one second, then I set a very high date in case of NULL:

WITH Intervalls AS
(
    SELECT *
          ,ISNULL(DATEADD(SECOND ,-1,LEAD(createddate) OVER(ORDER BY createddate)),{d'2100-01-01'}) AS EndOfIntervall
    FROM @membership AS ms
)

--The SELECT reads all members and joins them to the membership where their date is in the range according to "Intervalls". Only the case ealier than the first must be treated specially:

SELECT m.*
      ,ISNULL(i.price, CASE WHEN YEAR(m.joiningDate)<(SELECT MIN(x.createddate) FROM @membership as x) 
                            THEN (SELECT TOP 1 x.price FROM @membership AS x ORDER BY x.createddate ASC) END)
FROM @member AS m
LEFT JOIN Intervalls AS i ON m.joiningDate BETWEEN i.createddate AND i.EndOfIntervall
Comments