SQL query Help
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
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