CODEWITHSUNDEEP
sqlsql-serveraggregatewhere-clausesql-max
BeRye
BeRye

Reputation: 39

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

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).

enter image description here

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

Upvotes: 1

Views: 912

Answers (2)

forpas
forpas

Reputation: 164139

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;

Upvotes: 0

user8103263
user8103263

Reputation:

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

Upvotes: 1

Related Questions