Sql-server – Returning a result set with multiple rows based on max date

sql server

I have a child table that is something like this:

[Cust Date Table]

| Customer ID | Some Date  | Balance |
+-------------+------------+---------+
|           1 | 2012-04-30 |   20.00 |
|           1 | 2012-03-31 |   50.00 |
|           2 | 2012-04-30 |    0.00 |
|           2 | 2012-03-31 |   10.00 | 
|           3 | 2012-03-31 |   60.00 |
|           3 | 2012-02-29 |   10.00 |

I would like to be able to get a result set like this – one record for each client with the latest date:

| Customer ID | Some Date  | Balance |
+-------------+------------+---------+
|           1 | 2012-04-30 |   20.00 | 
|           2 | 2012-04-30 |    0.00 |
|           3 | 2012-03-31 |   60.00 |

I know that I can do this for each individual "Customer ID" with the following SQL (SQL Server syntax):

select top 1  [Some Date], [Customer ID], [Balance]
from [Cust Date Table]
where [Customer ID] = 2
order by [Some Date] desc


| Customer ID | Some Date  | Balance |
+-------------+------------+---------+
|           2 | 2012-04-30 |    0.00 |

But I'm not sure how to get all three of the records I want. I'm not sure if this is a situation that calls for a sub-query or something else.

Please note that the max date can be different for any given [Customer ID], (in this example, customer 3's maximum date is 2012-03-31 whereas the other records have a max date of 2012-04-30).
I have tried

select [Customer ID], MAX([Some Date]) AS [Latest Date], Balance 
from [Cust Date Table] 
group by [Customer ID], Balance;

The problem is this doesn't return just the one row for each customer – it returns multiple rows.

Best Answer

You simply want:

SELECT
    [Customer ID],
    MAX([Some Date]) AS[Latest Date]
FROM[Cust Date TABLE]
GROUP BY
    [Customer ID];

Ok - you've revised it. You now want to order the rows and pick the top one:

WITH numbered AS (
    SELECT
        [Customer ID],
        [Some Date],
        [Balance],
        ROW_NUMBER() OVER (
            PARTITION BY
                [Customer ID]
            ORDER BY
                [Some Date] DESC
        ) AS rownum
    FROM[Cust Date TABLE]
)
SELECT
    [Customer ID],
    [Some Date],
    [Balance]
FROM numbered
WHERE
    rownum = 1;

Related Solutions

Sql-server – Replace cursor with set-based approach

I would probably just do this the brute force way, and add indexes to support these joins where they don't exist. Not much gain to treating new customers and old customers any different once you've inserted all the customers that don't already exist:

INSERT dbo.Customer(fname, lname, address, city, state, zip, email)
    SELECT fname, lname, address, city, state, zip, email
     FROM dbo.job AS j
     WHERE job_no = @job_no
     AND NOT EXISTS
     (
        SELECT 1 FROM dbo.Customer
        WHERE fname = j.fname
        AND lname = j.lname
        AND (address = j.address OR email = j.email)
     );

INSERT INTO dbo.personal_code (customer_id, mailing_id, personal_code, email)
SELECT c.customer_id, j.mailing_id, j.personal_code, c.email
  FROM dbo.Customer AS c
  INNER JOIN dbo.job AS j
  ON c.fname = j.fname AND c.lname = j.lname 
  AND (c.address = j.address OR c.email = j.email)
  WHERE j.job_no = @job_no;

INSERT dbo.personal_code_extra(personal_code_id, extra)
SELECT pc.personal_code_id, j.extra
  FROM dbo.personal_code AS pc
  INNER JOIN dbo.Customer AS c
  ON pc.customer_id = c.customer_id
  INNER JOIN dbo.job AS j
  ON c.fname = j.fname AND c.lname = j.lname 
  AND (c.address = j.address OR c.email = j.email)
  WHERE j.job_no = @job_no;

Sql-server – Select multiple rows based on date interval from two fields

Do you have a calendar table ? I am building a kind of calendar table with object cteSeries. Then adjust the Initial_Date and Final_Date to hold BOM (beginning of Month YYYY-MM-01) in cteSample , fields: Initial_Date_M,Final_Date_M

DECLARE @tSample TABLE
(
    Product VARCHAR(10)
    ,Price  DECIMAL(18,2)
    ,Initial_Date DATE
    ,Final_Date DATE)
INSERT INTO  @tSample (Product,price,initial_date,final_date)
VALUES  ('A',20.50,'2014-08-10','2014-10-01')
    ,('B',50,'2015-01-15','2015-02-20');

DECLARE
@dStart AS DATE  ='20140101'
,@dEnd AS DATE ='20160101'

-- building a calendar table  - 
;WITH cteSeries  
AS
(SELECT @dStart AS cteDate
    UNION ALL
 SELECT DATEADD(MONTH,1,cteDate)
 FROM cteSeries
 WHERE
    cteDate < @dEnd
 )


--SELECT    * FROM  cteSeries OPTION (MAXRECURSION 0)

, cteSample
AS
(SELECT Product ,Price ,Initial_Date ,Final_Date 
        , DATEADD(MONTH , DATEDIFF(MONTH , 0,Initial_Date), 0) AS  Initial_Date_M
        , DATEADD(MONTH , DATEDIFF(MONTH , 0,Final_Date), 0) Final_Date_M
    FROM @tSample
)

SELECT
  S.Product
  ,S.Price
  ,cteDate AS Date
  --,*
FROM
   cteSample AS S
   INNER JOIN cteSeries AS C
   ON S.Initial_Date_M<=C.cteDate
   AND S.Final_Date_M >= C.cteDate

and the output :

 Product    Price   Date
 A  20.50   2014-08-01
 A  20.50   2014-09-01
 A  20.50   2014-10-01
 B  50.00   2015-01-01
 B  50.00   2015-02-01

Best Answer

Related Solutions

Sql-server – Replace cursor with set-based approach

Sql-server – Select multiple rows based on date interval from two fields

Related Question