Calculating Intervals

Question

Calculating Intervals

Hall of Fame

Points: 3475

July 29, 2022 at 6:22 pm

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.

pbi

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)

GO

NB: 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.

Attachments:

You must be logged in to view attached files.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Zidar SSC Enthusiast Points: 126 More actions · Answer 1

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...

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

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)

yrstruly Hall of Fame Points: 3475 More actions · Answer 3





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
GO

Thank you, for your assistance.

This reply was modified 2 years, 4 months ago by yrstruly.

Zidar SSC Enthusiast Points: 126 More actions · Answer 4

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...