Finding difference in two databases/tables
-
I copied the table and it still does not give me any data back using
SELECT * FROM [dbo].[fn_GetSummaryReport] (
'2022-06-28','2022-07-28')
In Production.
-
I would suggest that you take the function and break it down and run things in a piecemeal fashion and verify where the issue is then.
If you have copied the data from QA to Prod and your still getting no data then it’s not the auditLevyBreakdown table which is causing you the problem.
Otherwise your in the territory of potentially bringing in a consultant to fix this as there have been many threads started for this issue. As we are not first hand on the issue we don’t see the full picture having someone to sit side by side you may help on this.
-
An update ran and it is giving output now that were previously blank, accept for Value. Any idea's?
-
Looks like you need to debug the issue some more.
Take the procedure/function generating this output and debug why the value is not showing.
-
Do i need check System Stored procedures too?
-
No you can ignore the system object as they won’t affect your user defined objects.
-
Do i need to chech any other functions than Table Value and Scalar value functions?
-
You need to take the function / procedure you are running to get the bad data. Script that function and run it in a piecemeal fashion to find where the issue is occurring, then debug that particular piece of code giving you the incorrect results.
Its not going to be all functions procedures views triggers.
You need to only know what your current debugging function is touching.
-
I am using the following code to view if data is showing:
Its from the QA and Production db's. I checked to what function/procedure is linked to the code:
I checked the function sql code in both db's and it is the same, what am i missing?
/****** Object: UserDefinedFunction [dbo].[fn_GetSummaryReport] Script Date: 2022/08/20 12:25:49 ******/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 -
What field populates the missing data?
Then you know where you need to look next.
I’m going on a wild guess here it is this calculation field
SUM(vurc.EligiblePurchases * afp.PricePerL) EligibleValue,
So the issue is here I would say so either vurc data is missing or afp data is missing
More than likely it is the afp data
So you need to look at why your getting no data or wrong data from the view vw_FuelPrice, maybe the join clause is wrong or there is no data for the date range.
-
This is the change i found in two tables. I changed it to QA and still no data:
QA
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[auditLevyBreakdown] (
[Id] [int] 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
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[auditLevyBreakdown] SET (LOCK_ESCALATION = TABLE)
GO
------------------------
Production
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING OFF
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])
ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[auditLevyBreakdown]
ADD
CONSTRAINT [DF_auditLevyBreakdown_CreateDate]
DEFAULT (getdate()) FOR [CreateDate]
GO
ALTER TABLE [dbo].[auditLevyBreakdown]
ADD
CONSTRAINT [DF_auditLevyBreakdown_IsActive]
DEFAULT ((1)) FOR [IsActive]
GO
ALTER TABLE [dbo].[auditLevyBreakdown]
ADD
CONSTRAINT [DF_auditLevyBreakdown_ModifyDate]
DEFAULT (getdate()) FOR [ModifyDate]
GO
ALTER TABLE [dbo].[auditLevyBreakdown] SET (LOCK_ESCALATION = TABLE)
GOI am using APEXSQL Compare. Found these differences and changed them:
QA
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetStorageReportLive]
(
@pLocationId INT
)
RETURNS TABLE
AS
--*/
--DECLARE @pLocationId INT = 6
RETURN
SELECT t2.[Date],
ISNULL(t2.MeterReadingAfterDisposal - t2.LitresReceived + t2.LitresDisposed,0) OpeningBalance,
t2.InvoiceNumber,
t2.PurchaseDate,
IIF(
t2.LitresReceived = 0,
'N/A',
CAST(t2.LitresReceived AS NVARCHAR(25))
) LitresReceived,
IIF( t2.LitresReceived = 0,'N/A', CAST(t2.MeterReadingAfterDisposal AS NVARCHAR(25))) OpeningBalancePlusReceipts,
t2.DisposalDate,
t2.LitresDisposed,
t2.DisposedToVehicle,
t2.RegNumber,
COALESCE(
(
SELECT 'Eligible - ' + STRING_AGG(CAST(t1.EligibleActivityPerformed AS VARCHAR(MAX)), '; ')
FROM (
SELECT DISTINCT CASE
WHEN EligibleActivityPerformed LIKE 'Waste%' THEN
'Removal of waste products and disposal of mining operations'
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 = t2.EquipmentId
AND tf.SourceTime BETWEEN t2.PreviousTransactionDateTime AND t2.TransactionDateTime
) t1
),
IIF(
t2.ConsumptionTypeId = (
SELECT Id
FROM lstConsumptionType AS lct WITH (NOLOCK, READUNCOMMITTED)
WHERE lct.Name = 'L/HR'
),
'Eligible - Mining',
'Ineligible'
)
) PurposeOfDisposal,
t2.MeterReadingAfterDisposal--, t2.PreviousTransactionDateTime
FROM (
SELECT CAST(t1.TransactionDateTime AS DATE) [Date],
t1.TransactionDateTime,
t1.EquipmentId,
IIF(t1.IsDisposal = 0, t1.VoucherNumber, 'N/A') InvoiceNumber,
IIF(
t1.IsDisposal = 0,
CAST(CAST(t1.TransactionDateTime AS DATE) AS NVARCHAR(25)),
'N/A'
) [PurchaseDate],
IIF(t1.IsDisposal = 0, t1.Litres, 0) [LitresReceived],
IIF(
t1.IsDisposal = 1,
CAST(CAST(t1.TransactionDateTime AS DATE) AS NVARCHAR(25)),
'N/A'
) [DisposalDate],
IIF(t1.IsDisposal = 1, t1.Litres, 0) LitresDisposed,
IIF(t1.IsDisposal = 1, t1.Model, 'N/A') DisposedToVehicle,
IIF(t1.IsDisposal = 1, t1.FleetId, 'N/A') RegNumber,
LAG(t1.TransactionDateTime) OVER(
PARTITION BY t1.EquipmentId ORDER BY t1.TransactionDateTime
) PreviousTransactionDateTime,
'' PurposeOfDisposal,
vlvr.PreviousReading + SUM(
IIF(t1.IsDisposal = 0, t1.Litres, 0) - IIF(t1.IsDisposal = 1, t1.Litres, 0)
) OVER(PARTITION BY vlvr.ReadingDateTime ORDER BY t1.TransactionDateTime) MeterReadingAfterDisposal,
-- ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY t1.TransactionDateTime) rn,
t1.ConsumptionTypeId
FROM (
SELECT da.VoucherNumber,
da.TransactionDateTime,
da.Litres,
da.EquipmentId,
le.FleetId,
lm2.Name Model,
1 IsDisposal,
le.ConsumptionTypeId
FROM datAFSRecord AS da WITH (NOLOCK, READUNCOMMITTED)
JOIN dbo.lstEquipment AS le WITH (NOLOCK, READUNCOMMITTED)
ON le.Id = da.EquipmentId
JOIN dbo.lstModel AS lm2 WITH (NOLOCK, READUNCOMMITTED)
ON lm2.Id = le.ModelId
WHERE da.LocationId = @pLocationId
AND da.ProductId IN (
SELECT Id
FROM lstProduct AS lp WITH (NOLOCK, READUNCOMMITTED)
WHERE lp.Name IN ('DIESEL','DSL')
)
UNION ALL
SELECT ISNULL(dfd.DocumentNumber, 'N/A') VoucherNumber,
dfd.DeliveryTime TransactionDateTime,
dfd.Volume Litres,
NULL EquipmentId,
NULL FleetId,
NULL Model,
0 IsDisposal,
NULL ConsumptionTypeId
FROM datFuelDelivery AS dfd WITH (NOLOCK, READUNCOMMITTED)
WHERE dfd.LocationId = @pLocationId
AND dfd.Volume != 0
UNION ALL
SELECT da.VoucherNumber,
da.TransactionDateTime,
da.Litres,
da.EquipmentId,
le.FleetId,
lm2.Name Model,
0 IsDisposal,
le.ConsumptionTypeId
FROM datAFSRecord AS da WITH (NOLOCK, READUNCOMMITTED)
JOIN dbo.lstEquipment AS le WITH (NOLOCK, READUNCOMMITTED)
ON le.Id = da.EquipmentId
JOIN dbo.lstModel AS lm2 WITH (NOLOCK, READUNCOMMITTED)
ON lm2.Id = le.ModelId
WHERE da.EquipmentId IN (SELECT EquipmentId
FROM mapEquipmentLocation AS mel WITH (NOLOCK, READUNCOMMITTED)
WHERE mel.LocationId = @pLocationId
AND mel.IsActive = 1)
AND da.ProductId IN (
SELECT Id
FROM lstProduct AS lp WITH (NOLOCK, READUNCOMMITTED)
WHERE lp.Name IN ('DIESEL','DSL')
)
) t1
LEFT JOIN (SELECT vlvr.VolumeReading, vlvr.PreviousReadingDate, vlvr.ReadingDateTime, PreviousReading
FROM vw_LocationVolumeReading AS vlvr WITH (NOLOCK, READUNCOMMITTED) WHERE vlvr.LocationId = @pLocationId) vlvr ON t1.TransactionDateTime > vlvr.PreviousReadingDate AND t1.TransactionDateTime <= vlvr.ReadingDateTime
) t2
GO
---------------------
Production
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetStorageReportLive]
(
@pLocationId INT
)
RETURNS TABLE
AS
--*/
--DECLARE @pLocationId INT = 6
RETURN
SELECT t2.[Date],
ISNULL(t2.MeterReadingAfterDisposal - t2.LitresReceived + t2.LitresDisposed,0) OpeningBalance,
t2.InvoiceNumber,
t2.PurchaseDate,
IIF(
t2.LitresReceived = 0,
'N/A',
CAST(t2.LitresReceived AS NVARCHAR(25))
) LitresReceived,
IIF( t2.LitresReceived = 0,'N/A', CAST(t2.MeterReadingAfterDisposal AS NVARCHAR(25))) OpeningBalancePlusReceipts,
t2.DisposalDate,
t2.LitresDisposed,
t2.DisposedToVehicle,
t2.RegNumber,
COALESCE(
(
SELECT 'Eligible - ' + STRING_AGG(CAST(t1.EligibleActivityPerformed AS VARCHAR(MAX)), '; ')
FROM (
SELECT DISTINCT CASE
WHEN EligibleActivityPerformed LIKE 'Waste%' THEN
'Removal of waste products and disposal of mining operations'
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 = t2.EquipmentId
AND tf.SourceTime BETWEEN t2.PreviousTransactionDateTime AND t2.TransactionDateTime
) t1
),
IIF(
t2.ConsumptionTypeId = (
SELECT Id
FROM lstConsumptionType AS lct WITH (NOLOCK, READUNCOMMITTED)
WHERE lct.Name = 'L/HR'
),
'Eligible - Mining',
'Ineligible'
)
) PurposeOfDisposal,
t2.MeterReadingAfterDisposal--, t2.PreviousTransactionDateTime
FROM (
SELECT CAST(t1.TransactionDateTime AS DATE) [Date],
t1.TransactionDateTime,
t1.EquipmentId,
IIF(t1.IsDisposal = 0, t1.VoucherNumber, 'N/A') InvoiceNumber,
IIF(
t1.IsDisposal = 0,
CAST(CAST(t1.TransactionDateTime AS DATE) AS NVARCHAR(25)),
'N/A'
) [PurchaseDate],
IIF(t1.IsDisposal = 0, t1.Litres, 0) [LitresReceived],
IIF(
t1.IsDisposal = 1,
CAST(CAST(t1.TransactionDateTime AS DATE) AS NVARCHAR(25)),
'N/A'
) [DisposalDate],
IIF(t1.IsDisposal = 1, t1.Litres, 0) LitresDisposed,
IIF(t1.IsDisposal = 1, t1.Model, 'N/A') DisposedToVehicle,
IIF(t1.IsDisposal = 1, t1.FleetId, 'N/A') RegNumber,
LAG(t1.TransactionDateTime) OVER(
PARTITION BY t1.EquipmentId ORDER BY t1.TransactionDateTime
) PreviousTransactionDateTime,
'' PurposeOfDisposal,
vlvr.PreviousReading + SUM(
IIF(t1.IsDisposal = 0, t1.Litres, 0) - IIF(t1.IsDisposal = 1, t1.Litres, 0)
) OVER(PARTITION BY vlvr.ReadingDateTime ORDER BY t1.TransactionDateTime) MeterReadingAfterDisposal,
-- ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY t1.TransactionDateTime) rn,
t1.ConsumptionTypeId
FROM (
SELECT da.VoucherNumber,
da.TransactionDateTime,
da.Litres,
da.EquipmentId,
le.FleetId,
lm2.Name Model,
1 IsDisposal,
le.ConsumptionTypeId
FROM datAFSRecord AS da WITH (NOLOCK, READUNCOMMITTED)
JOIN dbo.lstEquipment AS le WITH (NOLOCK, READUNCOMMITTED)
ON le.Id = da.EquipmentId
JOIN dbo.lstModel AS lm2 WITH (NOLOCK, READUNCOMMITTED)
ON lm2.Id = le.ModelId
WHERE da.LocationId = @pLocationId
AND da.ProductId = (
SELECT Id
FROM lstProduct AS lp WITH (NOLOCK, READUNCOMMITTED)
WHERE lp.Name = 'DIESEL'
)
UNION ALL
SELECT ISNULL(dfd.DocumentNumber, 'N/A') VoucherNumber,
dfd.DeliveryTime TransactionDateTime,
dfd.Volume Litres,
NULL EquipmentId,
NULL FleetId,
NULL Model,
0 IsDisposal,
NULL ConsumptionTypeId
FROM datFuelDelivery AS dfd WITH (NOLOCK, READUNCOMMITTED)
WHERE dfd.LocationId = @pLocationId
AND dfd.Volume != 0
UNION ALL
SELECT da.VoucherNumber,
da.TransactionDateTime,
da.Litres,
da.EquipmentId,
le.FleetId,
lm2.Name Model,
0 IsDisposal,
le.ConsumptionTypeId
FROM datAFSRecord AS da WITH (NOLOCK, READUNCOMMITTED)
JOIN dbo.lstEquipment AS le WITH (NOLOCK, READUNCOMMITTED)
ON le.Id = da.EquipmentId
JOIN dbo.lstModel AS lm2 WITH (NOLOCK, READUNCOMMITTED)
ON lm2.Id = le.ModelId
WHERE da.EquipmentId IN (SELECT EquipmentId
FROM mapEquipmentLocation AS mel WITH (NOLOCK, READUNCOMMITTED)
WHERE mel.LocationId = @pLocationId
AND mel.IsActive = 1)
AND da.ProductId = (
SELECT Id
FROM lstProduct AS lp WITH (NOLOCK, READUNCOMMITTED)
WHERE lp.Name = 'DIESEL'
)
) t1
LEFT JOIN (SELECT vlvr.VolumeReading, vlvr.PreviousReadingDate, vlvr.ReadingDateTime, PreviousReading
FROM vw_LocationVolumeReading AS vlvr WITH (NOLOCK, READUNCOMMITTED) WHERE vlvr.LocationId = @pLocationId) vlvr ON t1.TransactionDateTime > vlvr.PreviousReadingDate AND t1.TransactionDateTime <= vlvr.ReadingDateTime
) t2
GO
AND
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
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
------------------
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
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 = (
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
GODifferences are :
Surely this pattern cant be the problem?
Eligible Liters/Litres and Valus are missing
-
From here I suggest you go and speak to someone internal to help you debug the issue you have.
You need the domain knowledge of the application here to help you make sense of the issue at hand and what the correct logic should be and what the correct data should be.
There is nothing further we can do here to help you as this is all internal logical issues.
If the logic is different? Why is it different?
if the data is different? Why is it different?
Only your application development team can answer those questions not people on a public forum.
-
Thank you. The culprit is "vw_UsageReportCached" or so it seems. I deleted vw_UsageReportCached from Production and imported the same view into Production from QA and it worked.
Im curious, since the codes are exactly the same, what could it be?
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [dbo].[vw_UsageReportCached]
AS
SELECT *
FROM [dbo].[cacheUsageLogbook]
WHERE
1=1
AND RegNumber NOT IN (SELECT le.RegNumber
FROM lstEquipment AS le WHERE le.Id IN (SELECT EquipmentId FROM mapEquipmentLocation AS mel)
)
GO
-------------------
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [dbo].[vw_UsageReportCached]
AS
SELECT *
FROM [dbo].[cacheUsageLogbook]
WHERE
1=1
AND RegNumber NOT IN (SELECT le.RegNumber
FROM lstEquipment AS le WHERE le.Id IN (SELECT EquipmentId FROM mapEquipmentLocation AS mel)
)
GO -
I am just going to comment on the last post here - not sure what else has been stated. The problem with the view is the fact that you are using SELECT * in the definition.
When a view is created - the * is expanded to the actual columns and metadata based on the table definitions. If the underlying table(s) change - either data types or inserting new columns, or adding/removing columns - basically any changes to the table definition, the view will not be updated with those changes.
This can - and as you have found out - cause issues that are very hard to track down. Dropping and recreating the view updated the view definition and re-aligned the column definitions. That could have also been done using sp_refreshview - but a better solution is to not use * and specify the actual columns.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
When i copied the View from QA. i placed it as n Table in Production. I tried to create the view with the DDL from QA, but it resorts to that same error. I tried to select the different columns as indicated, instead of *, but are getting errors in creating this view in Production using the column names:
See code(s):
/****** Object: View [dbo].[vw_UsageReportCached] Script Date: 2022/08/20 20:03:59 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Script for SelectTopNRows command from SSMS ******/CREATE VIEW [dbo].[vw_UsageReportCached]
AS
SELECT *
FROM [dbo].[cacheUsageLogbook]
WHERE
1=1
AND RegNumber NOT IN (SELECT le.RegNumber
FROM lstEquipment AS le WHERE le.Id IN (SELECT EquipmentId FROM mapEquipmentLocation AS mel)
)
--------------
I used the columns insead, but are getting an error:
CREATE TABLE [dbo].[cacheUsageLogbook](
[TransactionDateTime] [DATETIME] NOT NULL,
[ReceivedFromStorageUnitNumber] [NVARCHAR](50) NULL,
[QuantityReceived] [FLOAT] NOT NULL,
[TypeOfVehicle] [NVARCHAR](50) NOT NULL,
[OpeningBalanceFuel] [FLOAT] NOT NULL,
[RegNumber] [NVARCHAR](25) NULL,
[OpeningOdo] [NVARCHAR](53) NOT NULL,
[ClosingOdo] [NVARCHAR](53) NOT NULL,
[TotalOdoUsed] [NVARCHAR](53) NOT NULL,
[TotalFuelUsed] [FLOAT] NOT NULL,
[UnusedBalance] [FLOAT] NOT NULL,
[SpecificActivityPerformed] [NVARCHAR](4000) NULL,
[NonEligible] [FLOAT] NOT NULL,
[WhenActivityPerformed] [NVARCHAR](53) NOT NULL,
[WhereActivityPerformed] [NVARCHAR](4000) NULL,
[EligiblePurchases] [FLOAT] NOT NULL
) ON [PRIMARY]
GO
--------------------------
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply