In SQL how do I select the latest date that does not have a zero value in another column

Question

I am trying to select the max date in a table that has a Booking Date and a Written Premium value for that date. I want the newest date that has Written Premium (not equal to Zero).

In the above table I want, or expect the 4th Row in my query (7/28/2021, 330000), but I get the first row

(8/20/21, 0)

This is the query I run:

SELECT 
    MAX(booking_date) AS [Max Booking Date]
FROM 
    DW.dbo.Table1
GROUP BY 
    booking_year
HAVING 
    SUM(written_premium) <> 0
    AND booking_year = '2021'

I think this is summing all the written premium, not over the rows so I am just getting the max booking date. Maybe I need a PARTITION BY function or something? I am stuck on this and would appreciate any help on how to best do this.

Thanks,

Brian

You are grouping by a column that you havent shown us. That changes things quite a bit. Show us the grouping column and explain what you want returned wrt that column as well. — RBarryYoung, Aug 27 '21 at 14:20

score 1 · Answer 1 · answered Aug 27 '21 at 14:27

1

I think there are multiple options, but one could be:

SELECT TOP 1 booking_date, written_premium 
FROM DW.dbo.Table1
WHERE written_premium <> 0
ORDER BY booking_date DESC

answered Aug 27 '21 at 14:27

Reinis Verbelis

396
3
8

score 0 · Accepted Answer · answered Aug 27 '21 at 14:27

If all you want is the date then there is no need of group by and a HAVING clause.
Set your conditions in the WHERE clause:

SELECT MAX(booking_date) AS [Max Booking Date]
FROM DW.dbo.Table1
WHERE booking_year = '2021' AND written_premium <> 0;

If you want both columns:

SELECT TOP 1 booking_date AS [Max Booking Date], written_premium 
FROM DW.dbo.Table1
WHERE booking_year = '2021' AND written_premium <> 0
ORDER BY booking_date DESC;

In SQL how do I select the latest date that does not have a zero value in another column

2 Answers2