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?

    Attachments:
    You must be logged in to view attached files.
  • 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?

    Attachments:
    You must be logged in to view attached files.
  • 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:

    report

    Its from the QA and Production db's. I checked to what function/procedure is linked to the code:

    report2

    repor3

    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


    • This reply was modified 3 months, 1 week ago by  yrstruly.
  • 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)
    GO

    I 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
    GO

     

    Differences are :

    dif

     

    Surely this pattern cant be the problem?

    pat

    Eligible Liters/Litres and Valus are missing

    • This reply was modified 3 months, 1 week ago by  yrstruly.
    • This reply was modified 3 months, 1 week ago by  yrstruly.
    • This reply was modified 3 months, 1 week ago by  yrstruly.
  • 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:

    viewcol

    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

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

     

    • This reply was modified 3 months, 1 week ago by  yrstruly.

Viewing 15 posts - 16 through 30 (of 33 total)

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