Calculations in TSQL

  • Please check if my calculation(s) are correct, in accordance to this policy:

    "The general fuel levy increases by 16.0c per litre and the Road Accident Fund (RAF) levy by 9.0c per

    litre respectively, with effect from 01 April 2020, as follows:

    i) General Fuel levy = Increases from 339.0 c/l to 355.0 c/l; and

    ii) RAF levy = Increases from 198.0 c/l to 207.0 c/l.

    b) The diesel refund in respect of on-land primary sector beneficiaries is 40% of the general fuel levy of

    355.0 c/l, which equals 142.0 c/l of the qualifying 80% of diesel consumption.

    c) As from 1 April 2016, the diesel refund levy on the generation of electricity by Eskom’s open cycle gas

    turbines was reduced to 50% of the general fuel levy.

    2.1 Example 1(on land) – tax period 6 monthly

    Return for tax period

    08/2020

    (01 March to 31 August)

    Invoices to 31/03/2020 @ 333.6 c/l Invoices from 01/04/2020 @ 349.0 c/l

    Total Litres 102 000.00 102 000.00

    Non-eligible Litres 2 000.00 2 000.00

    Eligible Litres 100 000.00 100 000.00

    80% of Eligible Litres 80 000.00 80 000.00

    Amount Refundable R 266 880.00 R 279 200.00

    TOTAL REFUNDABLE R 266 880.00 + R 279 200.00 = R 546 080.00 "


    /****** Object: UserDefinedFunction [dbo].[fn_GetSummaryReport] Script Date: 2022/08/27 20:11:21 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO





    CREATE FUNCTION [dbo].[fn_GetSummaryReport]
    (
    @pStartDate DATE,
    @pEndDate DATE
    )
    RETURNS TABLE

    RETURN
    SELECT SUM(vurc.QuantityReceived) TotalLitres,
    SUM(vurc.QuantityReceived * afp.PricePerL) TotalValue,
    SUM(vurc.EligiblePurchases) EligibleLitres,
    SUM(vurc.EligiblePurchases * afp.PricePerL) EligibleValue,
    SUM(vurc.NonEligible) IneligibleLitres,
    SUM(vurc.NonEligible * afp.PricePerL) IneligibleValue,
    COUNT(vurc.QuantityReceived) TotalNumberOfTransactions,
    COUNT(IIF(le.RegNumber = 'RNF',NULL,1)) TotalNumberOfCompleteTransactions,
    COUNT(IIF(le.RegNumber = 'RNF',1,NULL)) TotalNumberOfFaultyTransactions,
    SUM(IIF(le.RegNumber = 'RNF',NULL,vurc.QuantityReceived)) TotalVolumeOfCompleteTransactions,
    SUM(IIF(le.RegNumber = 'RNF',vurc.QuantityReceived,NULL)) TotalVolumeOfFaultyTransactions,
    alb.RAFLevy RAFLevy,
    alb.FuelLevy FuelLevy,
    alb.PercEligible PercEligible,
    (alb.RAFLevy + alb.FuelLevy) / 100 * alb.PercEligible FinalRebatePL,
    (
    SUM(vurc.EligiblePurchases * alb.FuelLevy) + SUM(vurc.EligiblePurchases * alb.RAFLevy)
    ) / 100 * alb.PercEligible TotalClaimable
    FROM vw_UsageReportCached AS vurc
    LEFT JOIN vw_FuelPrice AS afp
    ON vurc.TransactionDateTime BETWEEN afp.PurchaseDate AND ISNULL(afp.NextPurchaseDate, GETDATE())
    LEFT JOIN auditLevyBreakdown AS alb
    ON vurc.TransactionDateTime BETWEEN alb.StartDate AND ISNULL(alb.EndDate, GETDATE())
    AND alb.IsActive = 1
    LEFT JOIN lstEquipment AS le ON le.FleetId = vurc.RegNumber
    WHERE CAST(vurc.TransactionDateTime AS DATE) BETWEEN @pStartDate AND @pEndDate
    GROUP BY
    alb.RAFLevy,
    alb.FuelLevy,
    alb.PercEligible
    GO


    ------------

    /****** Object: View [dbo].[vw_FuelPrice] Script Date: 2022/08/27 20:13:22 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO



    CREATE VIEW [dbo].[vw_FuelPrice] AS

    SELECT afp.PurchaseDate,
    afp.PurchaseVolume,
    afp.PurchasePrice,
    afp.PricePerL,
    ISNULL(LEAD(PurchaseDate) OVER(PARTITION BY 1 ORDER BY PurchaseDate),GETDATE()) NextPurchaseDate
    FROM auditFuelPrice AS afp WHERE afp.IsActive = 1
    GO


    --------------


    /****** Object: Table [dbo].[auditLevyBreakdown] Script Date: 2022/08/27 20:14:18 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[auditLevyBreakdown](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [StartDate] [DATETIME] NOT NULL,
    [EndDate] [DATETIME] NULL,
    [IsActive] [BIT] NOT NULL,
    [CreateDate] [DATETIME] NOT NULL,
    [ModifyDate] [DATETIME] NOT NULL,
    [RAFLevy] [FLOAT] NOT NULL,
    [FuelLevy] [FLOAT] NOT NULL,
    [PercEligible] [FLOAT] NOT NULL,
    CONSTRAINT [PK_auditLevyBreakdown] PRIMARY KEY CLUSTERED
    (
    [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[auditLevyBreakdown] ADD CONSTRAINT [DF_auditLevyBreakdown_IsActive] DEFAULT ((1)) FOR [IsActive]
    GO

    ALTER TABLE [dbo].[auditLevyBreakdown] ADD CONSTRAINT [DF_auditLevyBreakdown_CreateDate] DEFAULT (GETDATE()) FOR [CreateDate]
    GO

    ALTER TABLE [dbo].[auditLevyBreakdown] ADD CONSTRAINT [DF_auditLevyBreakdown_ModifyDate] DEFAULT (GETDATE()) FOR [ModifyDate]
    GO


    ----


    /****** Object: Table [dbo].[auditFuelPrice] Script Date: 2022/08/27 20:10:37 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[auditFuelPrice](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [PurchaseDate] [DATETIME] NOT NULL,
    [PurchaseVolume] [FLOAT] NOT NULL,
    [PurchasePrice] [FLOAT] NOT NULL,
    [PricePerL] [FLOAT] NOT NULL,
    [IsActive] [BIT] NOT NULL,
    [CreateDate] [DATETIME] NOT NULL,
    [ModifyDate] [DATETIME] NOT NULL,
    CONSTRAINT [PK_auditFuelPrice] PRIMARY KEY CLUSTERED
    (
    [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[auditFuelPrice] ADD CONSTRAINT [DF_auditFuelPrice_IsActive] DEFAULT ((1)) FOR [IsActive]
    GO

    ALTER TABLE [dbo].[auditFuelPrice] ADD CONSTRAINT [DF_auditFuelPrice_CreateDate] DEFAULT (GETDATE()) FOR [CreateDate]
    GO

    ALTER TABLE [dbo].[auditFuelPrice] ADD CONSTRAINT [DF_auditFuelPrice_ModifyDate] DEFAULT (GETDATE()) FOR [ModifyDate]
    GO


    ----------

    /****** Object: View [dbo].[vw_Get_LocationVolumeReadingCalcs] Script Date: 2022/08/27 20:08:49 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO



    CREATE 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


    ----------








    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
    ),
    'M 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 IN (
    SELECT Id
    FROM lstProduct AS lp WITH (NOLOCK, READUNCOMMITTED)
    WHERE lp.Name IN ('DIESEL','DSL')
    )
    AND le.Id NOT IN (SELECT EquipmentId FROM mapEquipmentLocation AS mel)
    AND le.FleetId != 'MERGED-DELETED'
    AND le.FleetId != 'MERGEDDELETED'

    ) d
    )r
    GO


    --------


    /****** Object: Table [dbo].[auditLevyBreakdown] Script Date: 2022/08/27 20:24:33 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[auditLevyBreakdown](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [StartDate] [DATETIME] NOT NULL,
    [EndDate] [DATETIME] NULL,
    [IsActive] [BIT] NOT NULL,
    [CreateDate] [DATETIME] NOT NULL,
    [ModifyDate] [DATETIME] NOT NULL,
    [RAFLevy] [FLOAT] NOT NULL,
    [FuelLevy] [FLOAT] NOT NULL,
    [PercEligible] [FLOAT] NOT NULL,
    CONSTRAINT [PK_auditLevyBreakdown] PRIMARY KEY CLUSTERED
    (
    [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[auditLevyBreakdown] ADD CONSTRAINT [DF_auditLevyBreakdown_IsActive] DEFAULT ((1)) FOR [IsActive]
    GO

    ALTER TABLE [dbo].[auditLevyBreakdown] ADD CONSTRAINT [DF_auditLevyBreakdown_CreateDate] DEFAULT (GETDATE()) FOR [CreateDate]
    GO

    ALTER TABLE [dbo].[auditLevyBreakdown] ADD CONSTRAINT [DF_auditLevyBreakdown_ModifyDate] DEFAULT (GETDATE()) FOR [ModifyDate]
    GO


  • What about your QA Department???  They're the ones that should be checking to see if things worked.

    Also, did you check it?  Have you found any problems with it?  Generally, forums are for asking help with "A" problem.  We're not here to do code reviews for you... we're not on the company payroll.

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

  • What sort of feedback are you expecting? If someone here says 'Yep, looks good', does that mean you will push the code to Production?

    And when there's a problem, you'll be able to reassure management by telling them: 'Bob from SQLServerCentral said it looked OK'. You might be asked to tidy your desk if that happens.

    This forum is for specific questions, not consulting assignments.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Quick thought, nicely formatted and presented code.

    😎

    Further on Phil's and Jeff's comments, as much as we would like to answer a specific question, that specific question is missing so could you please elaborate further?

    We used to have a crystal ball but unfortunately, someone nicked it... 😉

     

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

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