Calculating Intervals
-
I would like to check for eligible activities (is to take all activities between the last refuelling activity, and the current refuelling activity, and checking if the activities are eligible or not).
It appears there are occurrence of multiple refuelling activities in an extremely short time frame.
No activities between the transaction on 16:04 and 16:16, as the truck was obviously standing still getting refuelled.
With the current process, the fuel transactions after 16:04, are all marked as ineligible.
I need this into the calculations. All I can think of is to aggregate them into a single transaction, but this might cause issues with reconciling fuel activities with usage logbooks, and we might have an issue tying up the document numbers with the correct transactions, as well as providing evidence for the transactions when required?
Example of percentage use in calculations, needs to be taken into account(see attached).
An example of my code
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vw_Get_LocationVolumeReadingCalcs] AS
SELECT *,ISNULL(
(
SELECT SUM(da.Litres)
FROM datAFSRecord AS da
WHERE da.LocationId = t1.LocationId
AND da.TransactionDateTime >= t1.PreviousReadingDate
AND da.TransactionDateTime < t1.ReadingDateTime
),
0
) DispensedBetweenReadings,
ISNULL(
(
SELECT SUM(dfd.Volume)
FROM datFuelDelivery AS dfd
WHERE dfd.LocationId = t1.LocationId
AND dfd.DeliveryTime >= t1.PreviousReadingDate
AND dfd.DeliveryTime < t1.ReadingDateTime
),
0
)
+
ISNULL(
(
SELECT SUM(da.Litres)
FROM datAFSRecord AS da
JOIN mapEquipmentLocation AS mel
ON da.EquipmentId = mel.EquipmentId
WHERE mel.LocationId = t1.LocationId
AND da.TransactionDateTime >= t1.PreviousReadingDate
AND da.TransactionDateTime < t1.ReadingDateTime
),
0
) TotalReceivedBetweenReadings
FROM [dbo].[vw_LocationVolumeReading] t1
WHERE NOT (t1.PreviousReading = 1000 AND t1.VolumeReading = 1000)
AND NOT (t1.PreviousReading = 0 AND t1.VolumeReading = 1000)
AND NOT (t1.PreviousReading = 1000 AND t1.VolumeReading = 0)
GONB: https://www.sars.gov.za/wp-content/uploads/Ops/Policies/SE-DSL-02-Manage-Diesel-Refund-Calculations-...
- This topic was modified 2 years, 4 months ago by yrstruly.
-
Hard to tell anything. How about some CREATE TABLE statement, some tets data and explanation of the proble. How can we check the view if we do not know tables?
Zidar's Theorem: The best code is no code at all...
-
The only way to do this with 100% accuracy is if there is an odometer reading present.
Is there an odometer reading in the data?
And, yeah... I agree with Zidar... I hate to provide untested solutions but don't have the time to type the data from a graphic to code. It would be helpful (I'm pretty sure you've been told this before) if you could provide the data in a readily consumable form. See the first link in my signature line below for one of a couple of ways of doing that. "Readily Consumable" means we copy and paste some code to create a test table and populate it with data.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
CREATE VIEW [dbo].[vw_UsageReport]
AS
SELECT r.TransactionDateTime,
r.ReceivedFromStorageUnitNumber,
r.QuantityReceived,
r.TypeOfVehicle,
r.OpeningBalanceFuel,
r.RegNumber,
ISNULL(r.OpeningOdo, IIF(r.NonEligible = 0, 'Refer To TripTrace','N/A')) OpeningOdo,
ISNULL(r.ClosingOdo, 'N/A') ClosingOdo,
ISNULL(r.TotalOdo, 'N/A') TotalOdoUsed,
r.TotalFuelUsed,
r.UnusedBalance,
IIF(
r.QuantityReceived - r.NonEligible > 0,
r.SpecificActivityPerformed,
'N/A'
) SpecificActivityPerformed,
r.NonEligible NonEligible,
--r.TotalEligibleMinutes,
r.WhenActivityPerformed,
r.WhereActivityPerformed,
r.QuantityReceived - r.NonEligible EligiblePurchases
FROM (
SELECT d.TransactionDateTime,
d.Pump ReceivedFromStorageUnitNumber,
d.Litres QuantityReceived,
d.Model TypeOfVehicle,
ISNULL(d.OpeningBalanceFuel, 0) OpeningBalanceFuel,
d.RegNumber,
IIF(
d.ConsumptionMeter = 'L/HR',
[dbo].[fn_ConvertFloatToTime](d.OpeningOdo) + ' Hr',
CAST(ROUND(d.OpeningOdo, 3) AS NVARCHAR(20)) + 'Km'
) OpeningOdo,
IIF(
d.ConsumptionMeter = 'L/HR',
[dbo].[fn_ConvertFloatToTime](d.ClosingOdo) + ' Hr',
CAST(ROUND(d.ClosingOdo, 3) AS NVARCHAR(20)) + 'Km'
) ClosingOdo,
IIF(
d.ConsumptionMeter = 'L/HR',
[dbo].[fn_ConvertFloatToTime]((d.ClosingOdo - d.OpeningOdo)) + ' Hr',
CAST(ROUND((d.ClosingOdo - d.OpeningOdo), 3) AS NVARCHAR(20)) + 'Km'
) TotalOdo,
d.Litres TotalFuelUsed,
ISNULL(d.UnusedBalance, 0) UnusedBalance,
COALESCE(
(
SELECT STRING_AGG(t1.EligibleActivityPerformed, '; ')
FROM (
SELECT DISTINCT CASE
WHEN EligibleActivityPerformed LIKE 'Waste%'
OR EligibleActivityPerformed LIKE 'Top Soil%'
THEN
'Removal of waste products and disposal of mining operations ('+ EligibleActivityPerformed + ')'
WHEN EligibleActivityPerformed LIKE 'LGO%'
OR EligibleActivityPerformed LIKE 'VLGO%'
OR EligibleActivityPerformed LIKE 'HGO%'
OR EligibleActivityPerformed LIKE 'MGO%'
OR EligibleActivityPerformed LIKE 'G1%'
OR EligibleActivityPerformed LIKE 'HLG%'
OR EligibleActivityPerformed LIKE 'LLG%'
OR EligibleActivityPerformed LIKE 'OG2%' THEN
'Transport by vehicle, locomotive or other equipment on the mining sites of ore or other substances containing minerals for processing in operation of recovery of minerals ('
+ EligibleActivityPerformed + ')'
END EligibleActivityPerformed
FROM datTripRecord AS tf WITH (NOLOCK, READUNCOMMITTED)
WHERE tf.EquipmentId = d.Id
AND tf.SourceTime BETWEEN d.PreviousTransaction AND d.TransactionDateTime
AND ISNULL(tf.TripDurationInMinutes,0) > 0
) t1
),'Integral Mining Activity - ' + d.IntegralEquipmentDescription,
d.VehicleCategory,'N/A'
) SpecificActivityPerformed,
IIF(
d.OpeningOdo IS NULL AND NOT ISNULL(( SELECT COUNT(1)
FROM datTripRecord AS tf WITH (NOLOCK, READUNCOMMITTED)
WHERE tf.EquipmentId = d.Id
AND tf.SourceTime BETWEEN d.PreviousTransaction AND d.TransactionDateTime
AND ISNULL(tf.TripDurationInMinutes,0) > 0
AND (EligibleActivityPerformed LIKE 'Waste%'
OR EligibleActivityPerformed LIKE 'Top Soil%'
OR EligibleActivityPerformed LIKE 'LGO%'
OR EligibleActivityPerformed LIKE 'VLGO%'
OR EligibleActivityPerformed LIKE 'HGO%'
OR EligibleActivityPerformed LIKE 'MGO%'
OR EligibleActivityPerformed LIKE 'G1%'
OR EligibleActivityPerformed LIKE 'HLG%'
OR EligibleActivityPerformed LIKE 'LLG%'
OR EligibleActivityPerformed LIKE 'OG2%')
) ,0) > 0
AND d.NonEligible = 0,
d.Litres,
d.NonEligible
) NonEligible,
ISNULL(d.WhenActivityPerformed, 'N/A') WhenActivityPerformed,
COALESCE(
(
SELECT STRING_AGG(t1.WhereActivityPerformed, '; ')
FROM (
SELECT DISTINCT tf.WhereActivityPerformed
FROM datTripRecord AS tf WITH (NOLOCK, READUNCOMMITTED)
WHERE tf.EquipmentId = d.Id
AND tf.SourceTime BETWEEN d.PreviousTransaction AND d.TransactionDateTime
) t1
),
' Mine'
) WhereActivityPerformed,
d.Litres EligiblePurchases,
(
SELECT SUM(tf.TripDurationInMinutes)
FROM datTripRecord AS tf WITH (NOLOCK, READUNCOMMITTED)
WHERE tf.EquipmentId = d.Id
AND tf.SourceTime BETWEEN d.PreviousTransaction AND d.TransactionDateTime
) TotalEligibleMinutes,
(d.ClosingOdo - d.OpeningOdo) * 60 TotalMinutes
FROM (
SELECT dod.TransactionDateTime,
dod.Pump,
dod.Litres,
ISNULL(lvt.Name,'Mining') VehicleCategory,
le.TankSize OpeningBalanceFuel,
le.FleetId RegNumber,
dbo.fn_GetCalculatedOdo(
LAG(dod.Id) OVER(
PARTITION BY le.FleetId ORDER BY dod.TransactionDateTime,
dod.Id
)
) OpeningOdo,
dbo.fn_GetCalculatedOdo(dod.Id) ClosingOdo,
le.TankSize UnusedBalance,
IIF(lct.Name != 'L/HR', dod.Litres, 0) NonEligible,
CONVERT(
NVARCHAR(25),
LAG(dod.TransactionDateTime) OVER(
PARTITION BY le.FleetId ORDER BY dod.TransactionDateTime,
dod.Id
),
20
) + ' - ' + CONVERT(NVARCHAR(25), dod.TransactionDateTime, 20) WhenActivityPerformed,
'Mine' WhereActivityPerformed,
IIF(lct.Name = 'L/HR', dod.Litres, 0) TotalEligible,
le.EquipmentDescription,
lmm5.Name Model,
lct.Name ConsumptionMeter,
lie.[Description] IntegralEquipmentDescription,
le.Id,
LAG(dod.TransactionDateTime) OVER(
PARTITION BY le.FleetId ORDER BY dod.TransactionDateTime,
dod.Id
) PreviousTransaction
FROM datAFSRecord AS dod WITH (NOLOCK, READUNCOMMITTED)
JOIN dbo.lstEquipment AS le WITH (NOLOCK, READUNCOMMITTED)
ON le.Id = dod.EquipmentId
JOIN dbo.lstMake AS lmm3 WITH (NOLOCK, READUNCOMMITTED)
ON lmm3.Id = le.MakeId
JOIN dbo.lstModel AS lmm5 WITH (NOLOCK, READUNCOMMITTED)
ON lmm5.Id = le.ModelId
JOIN dbo.lstConsumptionType AS lct WITH (NOLOCK, READUNCOMMITTED)
ON lct.Id = le.ConsumptionTypeId
LEFT JOIN dbo.lstVehicleType AS lvt WITH (NOLOCK, READUNCOMMITTED)
ON lvt.Id = le.VehicleTypeId
LEFT JOIN dbo.lstIntegralEquipment AS lie ON lie.Id = lmm5.IntegralEquipmentId
WHERE 1 = 1 -- AND EOMONTH(dod.TransactionDateTime) = '2020-01-31'
--AND le.EquipmentDescription IS NOT NULL
AND dod.ProductId = (
SELECT Id
FROM lstProduct AS lp WITH (NOLOCK, READUNCOMMITTED)
WHERE lp.Name = 'DIESEL'
)
AND le.Id NOT IN (SELECT EquipmentId FROM mapEquipmentLocation AS mel)
AND le.FleetId != 'MERGED-DELETED'
AND le.FleetId != 'MERGEDDELETED'
) d
)r
GO
---------------------------
CREATE VIEW [dbo].[vw_UsageReport]
AS
SELECT r.TransactionDateTime,
r.ReceivedFromStorageUnitNumber,
r.QuantityReceived,
r.TypeOfVehicle,
r.OpeningBalanceFuel,
r.RegNumber,
ISNULL(r.OpeningOdo, IIF(r.NonEligible = 0, 'Refer To TripTrace','N/A')) OpeningOdo,
ISNULL(r.ClosingOdo, 'N/A') ClosingOdo,
ISNULL(r.TotalOdo, 'N/A') TotalOdoUsed,
r.TotalFuelUsed,
r.UnusedBalance,
IIF(
r.QuantityReceived - r.NonEligible > 0,
r.SpecificActivityPerformed,
'N/A'
) SpecificActivityPerformed,
r.NonEligible NonEligible,
--r.TotalEligibleMinutes,
r.WhenActivityPerformed,
r.WhereActivityPerformed,
r.QuantityReceived - r.NonEligible EligiblePurchases
FROM (
SELECT d.TransactionDateTime,
d.Pump ReceivedFromStorageUnitNumber,
d.Litres QuantityReceived,
d.Model TypeOfVehicle,
ISNULL(d.OpeningBalanceFuel, 0) OpeningBalanceFuel,
d.RegNumber,
IIF(
d.ConsumptionMeter = 'L/HR',
[dbo].[fn_ConvertFloatToTime](d.OpeningOdo) + ' Hr',
CAST(ROUND(d.OpeningOdo, 3) AS NVARCHAR(20)) + 'Km'
) OpeningOdo,
IIF(
d.ConsumptionMeter = 'L/HR',
[dbo].[fn_ConvertFloatToTime](d.ClosingOdo) + ' Hr',
CAST(ROUND(d.ClosingOdo, 3) AS NVARCHAR(20)) + 'Km'
) ClosingOdo,
IIF(
d.ConsumptionMeter = 'L/HR',
[dbo].[fn_ConvertFloatToTime]((d.ClosingOdo - d.OpeningOdo)) + ' Hr',
CAST(ROUND((d.ClosingOdo - d.OpeningOdo), 3) AS NVARCHAR(20)) + 'Km'
) TotalOdo,
d.Litres TotalFuelUsed,
ISNULL(d.UnusedBalance, 0) UnusedBalance,
COALESCE(
(
SELECT STRING_AGG(t1.EligibleActivityPerformed, '; ')
FROM (
SELECT DISTINCT CASE
WHEN EligibleActivityPerformed LIKE 'Waste%'
OR EligibleActivityPerformed LIKE 'Top Soil%'
THEN
'Removal of waste products and disposal of mining operations ('+ EligibleActivityPerformed + ')'
WHEN EligibleActivityPerformed LIKE 'LGO%'
OR EligibleActivityPerformed LIKE 'VLGO%'
OR EligibleActivityPerformed LIKE 'HGO%'
OR EligibleActivityPerformed LIKE 'MGO%'
OR EligibleActivityPerformed LIKE 'G1%'
OR EligibleActivityPerformed LIKE 'HLG%'
OR EligibleActivityPerformed LIKE 'LLG%'
OR EligibleActivityPerformed LIKE 'OG2%' THEN
'Transport by vehicle, locomotive or other equipment on the mining sites of ore or other substances containing minerals for processing in operation of recovery of minerals ('
+ EligibleActivityPerformed + ')'
END EligibleActivityPerformed
FROM datTripRecord AS tf WITH (NOLOCK, READUNCOMMITTED)
WHERE tf.EquipmentId = d.Id
AND tf.SourceTime BETWEEN d.PreviousTransaction AND d.TransactionDateTime
AND ISNULL(tf.TripDurationInMinutes,0) > 0
) t1
),'Integral Mining Activity - ' + d.IntegralEquipmentDescription,
d.VehicleCategory,'N/A'
) SpecificActivityPerformed,
IIF(
d.OpeningOdo IS NULL AND NOT ISNULL(( SELECT COUNT(1)
FROM datTripRecord AS tf WITH (NOLOCK, READUNCOMMITTED)
WHERE tf.EquipmentId = d.Id
AND tf.SourceTime BETWEEN d.PreviousTransaction AND d.TransactionDateTime
AND ISNULL(tf.TripDurationInMinutes,0) > 0
AND (EligibleActivityPerformed LIKE 'Waste%'
OR EligibleActivityPerformed LIKE 'Top Soil%'
OR EligibleActivityPerformed LIKE 'LGO%'
OR EligibleActivityPerformed LIKE 'VLGO%'
OR EligibleActivityPerformed LIKE 'HGO%'
OR EligibleActivityPerformed LIKE 'MGO%'
OR EligibleActivityPerformed LIKE 'G1%'
OR EligibleActivityPerformed LIKE 'HLG%'
OR EligibleActivityPerformed LIKE 'LLG%'
OR EligibleActivityPerformed LIKE 'OG2%')
) ,0) > 0
AND d.NonEligible = 0,
d.Litres,
d.NonEligible
) NonEligible,
ISNULL(d.WhenActivityPerformed, 'N/A') WhenActivityPerformed,
COALESCE(
(
SELECT STRING_AGG(t1.WhereActivityPerformed, '; ')
FROM (
SELECT DISTINCT tf.WhereActivityPerformed
FROM datTripRecord AS tf WITH (NOLOCK, READUNCOMMITTED)
WHERE tf.EquipmentId = d.Id
AND tf.SourceTime BETWEEN d.PreviousTransaction AND d.TransactionDateTime
) t1
),
' Mine'
) WhereActivityPerformed,
d.Litres EligiblePurchases,
(
SELECT SUM(tf.TripDurationInMinutes)
FROM datTripRecord AS tf WITH (NOLOCK, READUNCOMMITTED)
WHERE tf.EquipmentId = d.Id
AND tf.SourceTime BETWEEN d.PreviousTransaction AND d.TransactionDateTime
) TotalEligibleMinutes,
(d.ClosingOdo - d.OpeningOdo) * 60 TotalMinutes
FROM (
SELECT dod.TransactionDateTime,
dod.Pump,
dod.Litres,
ISNULL(lvt.Name,'Mining') VehicleCategory,
le.TankSize OpeningBalanceFuel,
le.FleetId RegNumber,
dbo.fn_GetCalculatedOdo(
LAG(dod.Id) OVER(
PARTITION BY le.FleetId ORDER BY dod.TransactionDateTime,
dod.Id
)
) OpeningOdo,
dbo.fn_GetCalculatedOdo(dod.Id) ClosingOdo,
le.TankSize UnusedBalance,
IIF(lct.Name != 'L/HR', dod.Litres, 0) NonEligible,
CONVERT(
NVARCHAR(25),
LAG(dod.TransactionDateTime) OVER(
PARTITION BY le.FleetId ORDER BY dod.TransactionDateTime,
dod.Id
),
20
) + ' - ' + CONVERT(NVARCHAR(25), dod.TransactionDateTime, 20) WhenActivityPerformed,
'Mine' WhereActivityPerformed,
IIF(lct.Name = 'L/HR', dod.Litres, 0) TotalEligible,
le.EquipmentDescription,
lmm5.Name Model,
lct.Name ConsumptionMeter,
lie.[Description] IntegralEquipmentDescription,
le.Id,
LAG(dod.TransactionDateTime) OVER(
PARTITION BY le.FleetId ORDER BY dod.TransactionDateTime,
dod.Id
) PreviousTransaction
FROM datAFSRecord AS dod WITH (NOLOCK, READUNCOMMITTED)
JOIN dbo.lstEquipment AS le WITH (NOLOCK, READUNCOMMITTED)
ON le.Id = dod.EquipmentId
JOIN dbo.lstMake AS lmm3 WITH (NOLOCK, READUNCOMMITTED)
ON lmm3.Id = le.MakeId
JOIN dbo.lstModel AS lmm5 WITH (NOLOCK, READUNCOMMITTED)
ON lmm5.Id = le.ModelId
JOIN dbo.lstConsumptionType AS lct WITH (NOLOCK, READUNCOMMITTED)
ON lct.Id = le.ConsumptionTypeId
LEFT JOIN dbo.lstVehicleType AS lvt WITH (NOLOCK, READUNCOMMITTED)
ON lvt.Id = le.VehicleTypeId
LEFT JOIN dbo.lstIntegralEquipment AS lie ON lie.Id = lmm5.IntegralEquipmentId
WHERE 1 = 1 -- AND EOMONTH(dod.TransactionDateTime) = '2020-01-31'
--AND le.EquipmentDescription IS NOT NULL
AND dod.ProductId = (
SELECT Id
FROM lstProduct AS lp WITH (NOLOCK, READUNCOMMITTED)
WHERE lp.Name = 'DIESEL'
)
AND le.Id NOT IN (SELECT EquipmentId FROM mapEquipmentLocation AS mel)
AND le.FleetId != 'MERGED-DELETED'
AND le.FleetId != 'MERGEDDELETED'
) d
)r
GO
---------------------------
CREATE VIEW [dbo].[vw_MeterReading] AS
SELECT dmr.ReadingDate, dmr.CounterReading, dmr.ConsumptionTypeId, dmr.EquipmentId, dmr.ReadingTypeId,
LAG(dmr.ReadingDate) OVER(PARTITION BY dmr.EquipmentId ORDER BY dmr.ReadingDate) PreviousReadingDate,
LAG(dmr.CounterReading) OVER(PARTITION BY dmr.EquipmentId ORDER BY dmr.ReadingDate) PreviousReading,
CounterReading - LAG(dmr.CounterReading) OVER(PARTITION BY dmr.EquipmentId ORDER BY dmr.ReadingDate) ReadingDiff
FROM datMeterReading AS dmr WHERE dmr.IsActive = 1
GOThank you, for your assistance.
-
Well, CREATE VIEW is nice and can beusefull, but only after CREATE TABLE datMeterReading, EligibleActivityPerformed, mapEquipmentLocation
Once we wee how tables look like, then we can comment on the views you submitted, or try to offer some other solutions. Mind you, it may happen that the table design is not optimal for this kind of business. Sometimes it helps to slightly change table designs to result in less complicated queries.
Zidar's Theorem: The best code is no code at all...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply