export const deliveryByDateRange = `
use AlplaPROD_test1
DECLARE @StartDate DATE = '[startDate]' -- 2025-1-1
DECLARE @EndDate DATE = '[endDate]' -- 2025-1-31
select * from
(select (select wert from dbo.T_SystemParameter where Bezeichnung = 'Werkskuerzel') as Plant,
AuftragsNummer as OrderNumber,
PositionsNummer as CustomerLineNumber,
AbrufNummer as CustomerReleaseNumber,
CONVERT(date, AbrufLiefertermin) as DeliveryDate,
CONVERT(DATE,JournalDatum) Bol_PrintDate,
AbrufMenge AS OrderQuantity,
AbrufMengeVPK as OrderPallets,
GelieferteMenge AS DeliveredQTY,
GelieferteMengeVPK as DeliverdPallets,
JournalNummer as BOLNum,
ProdArtikelBez AS ProductFamily,
dbo.V_LadePlanungenLadeAuftragAbruf.AbrufIdKundenAdresse AS IdCustomer,
dbo.V_LadePlanungenLadeAuftragAbruf.AdressBez AS CustName,
dbo.T_EAIJournal.IdJournalStatus as bolStatus,
V_TrackerAuftragsAbrufe.IdAuftragsAbruf as releaseNum,
V_LadePlanungenLadeAuftragAbruf.IdLadeAuftrag as truckPostion
,'Base Plant' as plantType
from dbo.V_TrackerAuftragsAbrufe (nolock)

left join
dbo.V_LadePlanungenLadeAuftragAbruf on V_TrackerAuftragsAbrufe.IdAuftragsAbruf =
dbo.V_LadePlanungenLadeAuftragAbruf.AbrufIdAuftragsAbruf

left join
dbo.T_EAIJournal on dbo.V_LadePlanungenLadeAuftragAbruf.IdLadeAuftrag =
dbo.T_EAIJournal.IdLadeAuftrag

left join
dbo.V_ArtikelKomplett on V_TrackerAuftragsAbrufe.IdArtikelVarianten =
dbo.V_ArtikelKomplett.IdArtikelvarianten
where GelieferteMengeVPK > 0 AND (
    AbrufLiefertermin IS NULL
    OR CONVERT(date, JournalDatum) BETWEEN @StartDate AND @EndDate
)

/*in house*/
union all

select top (50) (select wert from dbo.T_SystemParameter where Bezeichnung = 'Werkskuerzel') as Plant
,[KundenAuftragsNummer] as OrderNumber
,[KundenPositionsNummer] as CustomerLineNumber
, null as CustomerReleaseNumber
,CONVERT(date, i.Add_Date) as DeliveryDate
,CONVERT(DATE,i.Upd_Date) Bol_PrintDate
,null AS OrderQuantity
,null as OrderPallets
,LieferMengeVereinbart AS DeliveredQTY
,null as DeliverdPallets
,JournalNummer as BOLNum
,null AS ProductFamily
,IdAdresse AS IdCustomer
,null AS CustName
,null as bolStatus
,null as releaseNum
,null as truckPostion
,'In-House' as plantType
--,*

from [dbo].[T_InhouseLieferungen] as i (nolock)

where CONVERT(date, Upd_Date) BETWEEN @StartDate AND @EndDate
) x


order by Bol_PrintDate desc
`;
