Case , Partitioning with Null check

Other SQL Server Topics
1

Hello ,

Need help to get the sql query right. I have data as below -

Order Number Order Line Despatch date
456 1 29/03/2023
456 2 NULL
456 3 29/03/2023
456 4 20/03/2023
456 5 29/03/2023

Expected output - If the despatch date has 'Null' even for one line(Orderline) within an order then 'NULL' else give me the maximum of despatch date.

So in this case expected output is

Order Number Despatch date
456 NULL

Order line not required in Output , shown only for the sake of granularity.

I tried - (rough sql - excuse the syntax /name mismatch)
select
case when Despatch_Date is NULL then NULL else max("Despatch_Date") Over(Partition by "Order Number") end as "daternk"
from Table A
group by Order Number , Despatch_date

This sql returns me two records one for NULL and other max despatch date as I have to include despatch date in group by .But I need only one despatch date of NULL(in this scrnario) for this order. It has to be a generic sql as there are other order Numbers which dont have NULLS and hence picks only the max(depsatch date).

Please help.

Thanks

2

hi

hope this helps

create sample data script

drop table if exists #data

create table #data(OrderNumber int , OrderLine int, DispatchDate date)
insert into #data select 456,1,'2023-03-29'
insert into #data select 456,2,NULL
insert into #data select 456,3,'2023-03-29'
insert into #data select 456,4,'2023-03-20'
insert into #data select 456,5,'2023-03-29'

select * from #data

SELECT
   OrderNumber 
   , nullif(max(isnull(DispatchDate,'9999-09-09')),'9999-09-09')
FROM
   #data A
GROUP BY 
    OrderNumber

image

1 Like
3

Thank You so much for the quick response. This works!!