August 1, 2022 at 3:12 pm
I have this function running(see code). I would like to select data beteen dates e.g 30-06-2022 to 31-07-2022. Specificaly for columns Eligible, Fuel Levy, Claimable and % Eligible of purchase. I am gettig zero for these columns.
Is my code the reason i am getting these errors or my data missing. More of the views/tables ddl:
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
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].[lstEquipment] Script Date: 2022/08/01 17:09:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[lstEquipment](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FleetId] [nvarchar](25) NOT NULL,
[RegNumber] [nvarchar](25) NULL,
[EquipmentDescription] [nvarchar](100) NULL,
[ModelId] [int] NOT NULL,
[MakeId] [int] NOT NULL,
[TankSize] [float] NULL,
[ConsumptionTypeId] [int] NULL,
[VehicleTypeId] [int] NULL,
[DecommissionDate] [datetime] NULL,
[CreateDate] [datetime] NOT NULL,
[ModifyDate] [datetime] NOT NULL,
[IsActive] [bit] NOT NULL,
[AFSEquipmentId] [int] NULL,
CONSTRAINT [PK_lstEquipment] 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].[lstEquipment] ADD CONSTRAINT [DF_lstEquipment_CreateDate] DEFAULT (getdate()) FOR [CreateDate]
GO
ALTER TABLE [dbo].[lstEquipment] ADD CONSTRAINT [DF_lstEquipment_ModifyDate] DEFAULT (getdate()) FOR [ModifyDate]
GO
ALTER TABLE [dbo].[lstEquipment] ADD CONSTRAINT [DF_lstEquipment_IsActive] DEFAULT ((1)) FOR [IsActive]
GO
ALTER TABLE [dbo].[lstEquipment] WITH CHECK ADD CONSTRAINT [FK_lstEquipment_lstConsumptionType] FOREIGN KEY([ConsumptionTypeId])
REFERENCES [dbo].[lstConsumptionType] ([Id])
GO
ALTER TABLE [dbo].[lstEquipment] CHECK CONSTRAINT [FK_lstEquipment_lstConsumptionType]
GO
ALTER TABLE [dbo].[lstEquipment] WITH CHECK ADD CONSTRAINT [FK_lstEquipment_lstMake] FOREIGN KEY([MakeId])
REFERENCES [dbo].[lstMake] ([Id])
GO
ALTER TABLE [dbo].[lstEquipment] CHECK CONSTRAINT [FK_lstEquipment_lstMake]
GO
ALTER TABLE [dbo].[lstEquipment] WITH CHECK ADD CONSTRAINT [FK_lstEquipment_lstModel] FOREIGN KEY([ModelId])
REFERENCES [dbo].[lstModel] ([Id])
GO
ALTER TABLE [dbo].[lstEquipment] CHECK CONSTRAINT [FK_lstEquipment_lstModel]
GO
ALTER TABLE [dbo].[lstEquipment] WITH CHECK ADD CONSTRAINT [FK_lstEquipment_lstVehicleType] FOREIGN KEY([VehicleTypeId])
REFERENCES [dbo].[lstVehicleType] ([Id])
GO
ALTER TABLE [dbo].[lstEquipment] CHECK CONSTRAINT [FK_lstEquipment_lstVehicleType]
GO
------------------
AND
SELECT * FROM [dbo].[fn_GetSummaryReport] (
<@pStartDate, date,>
,<@pEndDate, date,>)
/****** Object: UserDefinedFunction [dbo].[fn_GetSummaryReport] Script Date: 2022/08/01 16:57: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
August 1, 2022 at 6:46 pm
Step 1 would be to query just the view to see if the date range returns anything. Then add a piece of your query for one of the joins and see what happens. Wash, rinse, repeat.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2022 at 2:58 pm
If there's any possibility that the NOT IN column checked can be NULL, then you need to explicitly exclude NULL(s). It doesn't sound like it could be NULL for this table, but it won't hurt to add the condition anyway:
...
WHERE le.Id IN (SELECT EquipmentId FROM mapEquipmentLocation AS mel WHERE EquipmentId IS NOT NULL)
...
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 2, 2022 at 3:20 pm
I tested the view and found that the columns in question does not contain any data, no matter what date range i choose. I also found that table 'auditLevyBreakdown' contains no data.
@JeffModen
If you tested the view correctly (you didn't post the code so we can't actually ascertain that notion as a true statement), I'm thinking that you've just found the root of what you're calling a problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2022 at 3:35 pm
I tried the code you gave:
/****** 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 WHERE EquipmentId IS NOT NULL))
GO
Still got none for the columns in question:
August 2, 2022 at 3:36 pm
@JeffModenI What is the correct way in testing a view? did post the view's code
/****** Object: UserDefinedFunction [dbo].[fn_GetSummaryReport] Script Date: 2022/08/02 17:34:57 ******/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
.
August 2, 2022 at 8:46 pm
@JeffModenI What is the correct way in testing a view? did post the view's code
I wasn't talking about the code for the view. I was talking about the simple code that you tested the view with.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2022 at 9:45 pm
I modified this code:
SELECT * FROM [dbo].[fn_GetSummaryReport] (
<@pStartDate, date,>
,<@pEndDate, date,>)
to
SELECT * FROM [dbo].[fn_GetSummaryReport] (
'2022-0628','2022-07-28') ?
It returned nothing, no matter how far back i changed the date.
August 2, 2022 at 9:58 pm
That's reading from a function, not the view. You don't know if the function is the issue or the view.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2022 at 10:00 pm
The view is returning data when i query it. Please advice, how do i do finer checks?
August 2, 2022 at 10:19 pm
Start tearing apart the query(ies) in the function. You know the data is available. Find out which part of the code in the function is preventing it.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2022 at 10:52 pm
For example... all the other tables are left joined vw_UsageReportCached view. So, remove everything else form the code not having to do with that view and see what you come up with. Like this,
DECLARE
@pStartDate DATE = '2022-06-28'
,@pEndDate DATE = '2022-07-28'
;
SELECT SUM(vurc.QuantityReceived) TotalLitres,
SUM(vurc.QuantityReceived) TotalValue,
SUM(vurc.EligiblePurchases) EligibleLitres,
SUM(vurc.EligiblePurchases) EligibleValue,
SUM(vurc.NonEligible) IneligibleLitres,
SUM(vurc.NonEligible) IneligibleValue,
COUNT(vurc.QuantityReceived) TotalNumberOfTransactions,
FROM vw_UsageReportCached AS vurc
WHERE CAST(vurc.TransactionDateTime AS DATE) BETWEEN @pStartDate AND @pEndDate
;
Actually, if I were doing this and since column names are about the most important thing on Earth, I start working with some vertical alignment techniques and get some religion about using the 2 part naming convention and fixing things like removing functions from column names in WHERE clauses and JOINs. Like this...
DECLARE
@pStartDate DATE = '2022-06-28'
,@pEndDate DATE = '2022-07-28'
;
SELECT TotalLitres = SUM(vurc.QuantityReceived)
,TotalValue = SUM(vurc.QuantityReceived)
,EligibleLitres = SUM(vurc.EligiblePurchases)
,EligibleValue = SUM(vurc.EligiblePurchases)
,IneligibleLitres = SUM(vurc.NonEligible)
,IneligibleValue = SUM(vurc.NonEligible)
,TotalNumberOfTransactions = COUNT(vurc.QuantityReceived)
FROM dbo.vw_UsageReportCached AS vurc
WHERE vurc.TransactionDateTime >= @pStartDate
AND vurc.TransactionDateTime < DATEADD(dd,1,@pEndDate)
;
If that works, then join in the next logical table and start adding pieces of that back in. Of course, TEST as you add stuff back in! 😉 Having column names on the left of the "=" sign means that you'll be able to easily modify or add to the formulas on the right without your alignment and readability starting to suffer.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2022 at 11:19 pm
Also, consider the fact that you're grouping by a table that is the "Right" table of a Left Join. Perhaps it would be better to get the data for the auditLevyBreakdown object assembled first and then use that as a driver for the rest of the works and maybe give up on the LEFT Outer join to the view.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2022 at 8:02 am
I have tested the cod as you adviced. I have not deleted the existing function for this test, is that correct? I am not getting any different results:
Please elaborate on this "Also, consider the fact that you're grouping by a table that is the "Right" table of a Left Join. Perhaps it would be better to get the data for the auditLevyBreakdown object assembled first and then use that as a driver for the rest of the works and maybe give up on the LEFT Outer join to the view." ?
Currently my "auditLevyBreakdown" table contains no data, it is suppose to have data in it.
@JeffModen
GO
/****** Object: Table [dbo].[auditLevyBreakdown] Script Date: 2022/08/03 09:59:13 ******/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
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply