sys.object
- yrstruly wrote:
"find out how the table is populated, fn_GetSummaryReport only reads from the table. If you can't find the proc that populates it then you'll have to compare it to the QA environment, it might be static data"
Have you looked in the QA environment to see if the table exists and contains data? As this was suggestion was provided up front I would make sure to check before spending too much time hunting through code.
At a previous job all production data were migrated from the test environment after approval had been granted by the god of Sarbanes Oxley. The production servers did not contain the code that processed the data; a tool simply moved rows between tables that were defined in tables, so no dependencies existed for the tables being loaded. I have no idea if that's what you're dealing with, but it's possible, so I would establish whether there is data of any quality in QA. I'd also run the dependency code scripts in QA and Dev (if it exists).
-
By QA environment, do you mean my live data?
- yrstruly wrote:
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
@frederico_fonseca @JeffModen This is what i get sraight from the server, see attached.
Can this 'NOT IN' be the problem?
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)
)
GOOk... you open up the function you posted and understand how you're not making friends by posting code like that. 😉 And, yes... NOT IN has the problem where if the "in" list has even one NULL, you'll get nothing back.
--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) -
I queried the QA database and found different results, see attached. I used the Compare tool in SSIS and it sates the schema's are the same. Pls assist?
-
Please check my logig and advise?
-
To be honest, we've been bouncing all over the place and you're posting snippets and a whole bunch of things. The only thing that I remember about of of this is that you had an empty table in prod and no one knows why it's empty in prod and why it's not in QA.
You've been provided with code to hopefully find code that populates the table. Posting functions isn't going to help because functions can't write to even Temp tables.
If the table isn't empty in QA, I'd put a trigger on it to capture (in a table) the ORIGINAL_LOGIN() that's writing to it. If nothing is writing to the table in QA, (and, since the table is empty in Prod, you know nothings writing to that), could it just be that the table was deployed and is simply not being populated by anything at all and that your dealing with a "dead table" and a was of useless code that refers to it?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - yrstruly wrote:
By QA environment, do you mean my live data?
QA stands for Quality Assurance - usually some type of Pre-Production area before pushing code or data to Production.
"Live" usually means production. So "Live" and QA would not (normally) mean the same thing.
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply