Not working Query

mail@pastecode.io avatar
unknown
mysql
2 years ago
1.1 kB
1
Indexable
Never
Select
sl.CompanyID
,sl.DocumentNo
,sl.[LineNo]
,sl.Startdate
,sl.Enddate
From DWH_Sales.sales.Salesline sl

Where 1=1
and sl.DocumentType in (1)
and sl.SalesLineNo in ('Z900.03625.ML','Z900.07978.ML')

Union All

Select 
sla.CompanyID
,sla.DocumentNo
,sla.[LineNo]
,sla.[startdate] = First_Value(sha.SelltoContactNo) Over(Partition by sha.CompanyID, sha.DocumentType, sha.SalesHeaderArchiveNo Order by sla.DocNoOccurrence desc, sla.VersionNo desc)
,sla.Enddate =First_Value(sha.SelltoContactNo) Over(Partition by sha.CompanyID, sha.DocumentType, sha.SalesHeaderArchiveNo Order by sla.DocNoOccurrence desc, sla.VersionNo desc)



From   DWH_Sales.sales.SalesLineArchive sla
inner join [DWH_Dimensions].[dim].customer c on sla.companyID = c.companyID and sla.selltocustomerno = c.customerno

Where 1=1
and sla.DocumentType in (1)


And NOT EXISTS (Select * From DWH_Sales.sales.Salesline
Where DocumentType in (1) and CompanyID = sla.CompanyID and DocumentType = sla.DocumentType and SalesHeaderNo = sla.SalesHeaderArchiveNo  LineNo)