SQL query Help

Job ID: 34850325

Budget: $10 – $30 USD

I can get this to work quite the way I want it to. Please help!

I want to count only results where all releases for an order are true to this statement.
((convert(int, convert(datetime, ShipHead.ShipDate)) - convert(int, convert(datetime, OrderRel.ReqDate)))) <= 0
Most orders have multiple releases.
If any are late releases the whole order does not get counted.
For example in this table
COUNT ( DISTINCT OrderHed.OrderNum ) AS "OTIF"
Should return 2 because release 4 on order 00001 was late.

Here is the Query I have so far.

select
ShipHead.Company as Company,
COUNT ( DISTINCT OrderHed.OrderNum ) AS "OTIF"

from Erp.ShipHead as ShipHead

left outer join Erp.ShipDtl as ShipDtl on
ShipHead.Company = ShipDtl.Company
and ShipHead.PackNum = ShipDtl.PackNum
inner join Erp.OrderRel as OrderRel on
ShipDtl.Company = OrderRel.Company
and ShipDtl.OrderNum = OrderRel.OrderNum
and ShipDtl.OrderLine = OrderRel.OrderLine
and ShipDtl.OrderRelNum = OrderRel.OrderRelNum
inner join Erp.OrderHed as OrderHed on
ShipDtl.Company = OrderHed.Company
and ShipDtl.OrderNum = OrderHed.OrderNum
inner join Erp.Customer as Customer on
ShipHead.Company = Customer.Company
and ShipHead.CustNum = Customer.CustNum

where ShipHead.ReadyToInvoice = 1
and OrderHed.OpenOrder = 0
and (datepart(month, ShipHead.ShipDate))= '09'
and (datepart(year, ShipHead.ShipDate))= '2022'
and [OrderRel].[ReqDate] IS NOT NULL

and OrderHed.OpenOrder = 0
and ((convert(int, convert(datetime, ShipHead.ShipDate)) - convert(int, convert(datetime, OrderRel.ReqDate)))) <= 0

group by ShipHead.Company
Related categories: SQL Database Administration