;WITH CTE
AS
(
SELECT DELIVERYDELAY,DAYS,FREQUENCY,INVENTLOCATIONID,DAYVALUES
FROM SITEDELIVERYSCHEDULE a
UNPIVOT( DAYVALUES FOR DAYS IN (MONDAY,TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY)) p
WHERE p.INVENTLOCATIONID <> ‘’ AND DAYVALUES = 1
GROUP BY INVENTLOCATIONID, DELIVERYDELAY, FREQUENCY, DAYS, DAYVALUES
)
Select rtc.StoreNumber,sum(convert(int, dcmj.cases)) as ‘NumberOfCases’,
CASE
WHEN a.FREQUENCY = 0 THEN ‘Weekly’
WHEN a.FREQUENCY = 1 THEN ‘Bi-Weekly S2’
WHEN a.FREQUENCY = 2 THEN ‘Monthly’
WHEN a.FREQUENCY = 3 THEN ‘Quarterly’
WHEN a.FREQUENCY = 4 THEN ‘Annually’
WHEN a.FREQUENCY = 5 THEN ‘Daily’
ELSE
‘NOT VALID’
END as ‘Schedule Delivery’,
a.DELIVERYDELAY,
a.DAYS,
DAYVALUES,
FROM DISTRIBUTIONCENTERMESSAGEJOUR dcmj
INNER JOIN RETAILCHANNELTABLE rtc ON rtc.STORENUMBER = dcmj.DISTRIBUTIONCENTERTO
INNER JOIN PURCHLINE pl ON dcmj.PURCHLINERECID = pl.RECID
INNER JOIN DISTRIBUTIONCENTERMESSAGEJOURHDR hdr ON hdr.DCMessageJourID = dcmj.DCMessageJourID
INNER JOIN CTE a ON rtc.INVENTLOCATION = a.INVENTLOCATIONID
WHERE convert(date,dcmj.AUTHORIZATIONDATE) = @Datetime
AND pl.Purchstatus = ‘6’
group by rtc.STORENUMBER, a.Frequency, a.Days, a.DAYVALUES, a.DeliveryDelay,dcmj.AUTHORIZATIONDATE
ORDER BY rtc.StoreNumber
X++ (Query Currently I am working on)
while select count(Cases)
from distributionTable
join retailTable
group by distributionTable.DistributionCenterTo
where retailTable.StoreNumber == distributionTable.DistributionCenterTo
&& distributionTable.AuthorizationDate == str2datetime( “2015/05/27 12:00:00” ,321 )
join purchTable
where purchTable.RecId == distributionTable.PurchLineRecId
&& purchTable.PurchStatus == PurchStatus::Sent
join distributionHDRTable
where distributionHDRTable.DCMessageJourID == distributionTable.DCMessageJourID
join siteDeliveryTable
where
siteDeliveryTable.InventLocationId == retailTable.inventLocation
{
if
(siteDeliveryTable.Monday == NoYesBlank::Yes)
{
dayofWeek = ‘Monday’;
}
else if (siteDeliveryTable.Tuesday == NoYesBlank::Yes)
{
dayofWeek = ‘Tuesday’
;
}
else if (siteDeliveryTable.Wednesday == NoYesBlank::Yes)
{
dayofWeek = ‘Wednesday’;
}
else if (siteDeliveryTable.Thursday == NoYesBlank::Yes)
{
dayofWeek = ‘Thursday’;
}
else if (siteDeliveryTable.Friday == NoYesBlank::Yes)
{
dayofWeek = ‘Friday’;
}
else if (siteDeliveryTable.Saturday == NoYesBlank::Yes)
{
dayofWeek = ‘Saturday’
;
}
else if (siteDeliveryTable.Sunday == NoYesBlank::Yes)
{
dayofWeek = ‘Sunday’
;
}
sut4.value(“strNumber”, retailTable.StoreNumber );
sut4.value(“Cases”, distributionTable.Cases );
sut4.value(“ScheduleDelivery”, enum2str
(siteDeliveryTable.Frequency));
sut4.value(“Day”, dayofWeek );
sut4.value(“Delay”, siteDeliveryTable.DeliveryDelay );
info(sut4.toString());