I need to create a condition in where
according to boolean
.
If% of_% then I will select lines with final values larger than expected ( boolean = true
, etc) if it is ConfirmedVolume > ExpectedVolume
I select everything.
I took a look at several links but I could not pass the idea to my query, for example:
Code:
SELECT RouteId,
Id,
RouteStatusId,
Address,
Latitude,
Longitude,
ExpectedQty,
ConfirmedQty,
FinalQty,
ExpectedWeight,
ConfirmedWeight,
FinalWeight,
ExpectedVolume,
ConfirmedVolume,
FinalVolume,
Comm,
MAX(EndTime) - MAX(StartTime),
MAX(StartTime),
MAX(EndTime),
WaitTime,
MAX(CAST(EndTime AS DATE)),
UOM,
BPName
FROM
(
SELECT
{RouteStop}.[RouteId] RouteId,
{RouteStop}.[Id] Id,
{RouteStop}.[RouteStatusId] RouteStatusId,
{SiteAddress}.[Address] Address,
{SiteAddress}.[Latitude] Latitude,
{SiteAddress}.[Longitude] Longitude,
SUM({RouteStop_Product}.[ExpectedQty]) ExpectedQty,
SUM({RouteStop_Product}.[ConfirmedQty]) ConfirmedQty,
SUM({RouteStop_Product}.[FinalQty]) FinalQty,
SUM({RouteStop_Product}.[ExpectedWeight]) ExpectedWeight,
SUM({RouteStop_Product}.[ConfirmedWeight]) ConfirmedWeight,
SUM({RouteStop_Product}.[FinalWeight]) FinalWeight,
SUM({RouteStop_Product}.[ExpectedVolume]) ExpectedVolume,
SUM({RouteStop_Product}.[ConfirmedVolume]) ConfirmedVolume,
SUM({RouteStop_Product}.[FinalVolume]) FinalVolume,
{RouteStop}.[Comment] Comm,
CASE WHEN {GPS}.[EventTypeId] = @RouteStopStarted
THEN {GPS}.[DateTime]
ELSE CAST(NULL AS DATE)
END StartTime,
CASE WHEN {GPS}.[EventTypeId] = @RouteStopCompleted
OR {GPS}.[EventTypeId] = @RouteStopFailed
THEN {GPS}.[DateTime]
ELSE CAST(NULL AS DATE)
END EndTime,
{GPS}.[EventTypeId] EventTypeId,
{RouteStop}.[WaitTime] WaitTime,
{Order}.[UOM] UOM,
{BusinessPartner}.[CardName] BPName
FROM {RouteStop}
LEFT JOIN {RouteStop_Product} ON {RouteStop}.[Id] = {RouteStop_Product}.[RouteStopId]
LEFT JOIN {GPS} ON {RouteStop}.[Id] = {GPS}.[RouteStopID]
LEFT JOIN {SiteAddress} ON {RouteStop}.[SiteAddress] = {SiteAddress}.[Id]
LEFT JOIN {BusinessPartner} ON {SiteAddress}.[CardCode] = {BusinessPartner}.[Id]
LEFT JOIN {Order} ON {RouteStop_Product}.[OrderId] = {Order}.[Id]
LEFT JOIN {SitePlanningArea} ON {SiteAddress}.[Id] = {SitePlanningArea}.[SiteAddressId]
WHERE {RouteStop}.[Id] = @RouteStopId
AND ({SitePlanningArea}.[PlanningAreaId] = @PlanningAreaId OR @PlanningAreaId = 0)
GROUP BY {RouteStop}.[RouteId],
{RouteStop}.[Id],
{RouteStop}.[RouteStatusId],
{SiteAddress}.[Address],
{SiteAddress}.[Latitude],
{SiteAddress}.[Longitude],
{RouteStop}.[Comment],
{GPS}.[EventTypeId],
{GPS}.[DateTime],
{RouteStop}.[WaitTime],
{Order}.[UOM],
{BusinessPartner}.[CardName]
)
GROUP BY RouteId,
Id,
RouteStatusId,
Address,
Latitude,
Longitude,
ExpectedQty,
ConfirmedQty,
FinalQty,
ExpectedWeight,
ConfirmedWeight,
FinalWeight,
ExpectedVolume,
ConfirmedVolume,
FinalVolume,
Comm,
WaitTime,
UOM,
BPName
Thank you