August 27, 2022 at 6:27 pm
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
August 28, 2022 at 4:51 am
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
Change is inevitable... Change for the better is not.
August 28, 2022 at 7:01 am
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
August 28, 2022 at 3:53 pm
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