We have 12 types of expenses in our database, some have fairly differing data minus the Amount fields. We have multiple places in the application and reports that require single and multiple expense totals and counts per expense Type and Grand totals. In the end, we want one View for all of these calls but are open to PROCs.
We looked at multiple alternatives for this and found that a CTE allows us to get all the data required without the use of temp tables. Using joins does not work as we saw records being replicated or removed no matter what we tried.
I’ve attached a subset of the expense tables and the query that includes the CTE. Does anybody have a better alternative than this? Something faster? Are we approaching this ‘flattening’ appropriately?
Please note the execution plan is the same for this query whether it is a View or a Proc and the Proc seems to take twice as long to run.
Below is the code
WITH pe AS
(
SELECT
EventRegistrationId
,sum(AmountPaid) as AmountPaidTotal
,sum(CommercialValueAmount) as CommercialValueAmountTotal
,count(1) as ExpenseCount
FROM PettyExpenses
WHERE IsDisputed = 0 AND IsUndisputed = 0
group by EventRegistrationId
),hpe AS
(
SELECT
EventRegistrationId
,sum(AmountPaid) as AmountPaidTotal
,sum(CommercialValueAmount) as CommercialValueAmountTotal
,count(1) as ExpenseCount
FROM HirePremisesExpenses
WHERE IsDisputed = 0 AND IsUndisputed = 0
group by EventRegistrationId
), ae AS
(
SELECT
EventRegistrationId
,sum(AmountPaid) as AmountPaidTotal
,sum(CommercialValueAmount) as CommercialValueAmountTotal
,count(1) as ExpenseCount
FROM AdvertisingExpenses
WHERE IsDisputed = 0 AND IsUndisputed = 0
group by EventRegistrationId
), se AS
(
SELECT
EventRegistrationId
,sum(AmountPaid) as AmountPaidTotal
,sum(CommercialValueAmount) as CommercialValueAmountTotal
,count(1) as ExpenseCount
FROM ServiceExpenses
WHERE IsDisputed = 0 AND IsUndisputed = 0
group by EventRegistrationId
), gse AS
(
SELECT
EventRegistrationId
,sum(AmountPaid) as AmountPaidTotal
,sum(CommercialValueAmount) as CommercialValueAmountTotal
,count(1) as ExpenseCount
FROM GoodsSuppliedExpenses
WHERE IsDisputed = 0 AND IsUndisputed = 0
group by EventRegistrationId
), thve AS
(
SELECT
EventRegistrationId
,sum(AmountPaid) as AmountPaidTotal
,sum(CommercialValueAmount) as CommercialValueAmountTotal
,count(1) as ExpenseCount
FROM TravelHireVehicleExpenses
WHERE IsDisputed = 0 AND
IsUndisputed = 0
group by EventRegistrationId
)
select
distinct eer.EventRegistrationId
--Petty Expense
,ISNULL(pe.AmountPaidTotal,0) as PettyExpenseAmountPaid
,ISNULL(pe.CommercialValueAmountTotal,0) as PettyExpenseCommercial
,ISNULL(pe.ExpenseCount,0) as PettyExpenseCount
--Hire On Premise Expense
,ISNULL(hpe.AmountPaidTotal,0) as HireOnPremisesExpenseAmountPaid
,ISNULL(hpe.CommercialValueAmountTotal,0) as HireOnPremisesExpenseCommercial
,ISNULL(hpe.ExpenseCount,0) as HireOnPremisesExpenseCount
--Advertising Expense
,ISNULL(ae.AmountPaidTotal,0) as AdvertisingExpenseAmountPaid
,ISNULL(ae.CommercialValueAmountTotal,0) as AdvertisingExpenseCommercial
,ISNULL(ae.ExpenseCount,0) as AdvertisingExpenseExpenseCount
--Services Expense
,ISNULL(se.AmountPaidTotal,0) as ServiceExpenseAmountPaid
,ISNULL(se.CommercialValueAmountTotal,0) as ServiceExpenseCommercial
,ISNULL(se.ExpenseCount,0) as ServiceExpenseExpenseCount
--Goods Supplied Expense
,ISNULL(gse.AmountPaidTotal,0) as GoodsSuppliedExpenseAmountPaid
,ISNULL(gse.CommercialValueAmountTotal,0) as GoodsSuppliedExpenseCommercial
,ISNULL(gse.ExpenseCount,0) as GoodsSuppliedExpenseExpenseCount
--Travel and Vehicle Expense
,ISNULL(thve.AmountPaidTotal,0) as TravelVehicleExpenseAmountPaid
,ISNULL(thve.CommercialValueAmountTotal,0) as TravelVehicleExpenseCommercial
,ISNULL(thve.ExpenseCount,0) as TravelVehicleExpenseExpenseCount
--All Expenses
,ISNULL(pe.AmountPaidTotal,0)
+ ISNULL(hpe.AmountPaidTotal,0)
+ ISNULL(ae.AmountPaidTotal,0)
+ ISNULL(se.AmountPaidTotal,0)
+ ISNULL(gse.AmountPaidTotal,0)
+ ISNULL(thve.AmountPaidTotal,0) as AllExpenseAmountPaidTotal
,ISNULL(pe.CommercialValueAmountTotal,0)
+ ISNULL(hpe.CommercialValueAmountTotal,0)
+ ISNULL(ae.CommercialValueAmountTotal,0)
+ ISNULL(se.CommercialValueAmountTotal,0)
+ ISNULL(gse.CommercialValueAmountTotal,0)
+ ISNULL(thve.CommercialValueAmountTotal,0) as AllExpenseCommercialValueTotal
,ISNULL(pe.ExpenseCount,0)
+ ISNULL(hpe.ExpenseCount,0)
+ ISNULL(ae.ExpenseCount,0)
+ ISNULL(se.ExpenseCount,0)
+ ISNULL(gse.ExpenseCount,0)
+ ISNULL(thve.ExpenseCount,0) as AllExpenseCount
from EventRegistrations eer
left join pe on pe.EventRegistrationId = eer.EventRegistrationId
left join hpe on hpe.EventRegistrationId = eer.EventRegistrationId
left join ae on ae.EventRegistrationId = eer.EventRegistrationId
left join se on se.EventRegistrationId = eer.EventRegistrationId
left join gse on gse.EventRegistrationId = eer.EventRegistrationId
left join thve on thve.EventRegistrationId = eer.EventRegistrationId
Here is the db schema with inserts for those that are interested in seeing it live.
CREATE TABLE [AdvertisingExpenses](
[AdvertisingExpenseId] [int] NOT NULL,
[EventRegistrationId] [int] NOT NULL,
[FundingAccountId] [int] NULL,
[ExpenseNumber] [int] NOT NULL,
[ExpenseVendorId] [int] NOT NULL,
[MediaOutletId] [int] NOT NULL,
[PeriodStartDate] [datetime] NOT NULL,
[PeriodEndDate] [datetime] NOT NULL,
[AmountPaid] [decimal](12, 2) NOT NULL,
[CommercialValueAmount] [decimal](12, 2) NOT NULL,
[ExpenseTypeId] [int] NOT NULL,
[IsJointAgreement] [bit] NOT NULL,
[IfPaidDateOfDirection] [datetime] NULL,
[IsDisputed] [bit] NOT NULL,
[IsUndisputed] [bit] NOT NULL,
[AmountClaimed] [decimal](12, 2) NULL,
[NatureOfClaim] [varchar](255) NULL,
[InvoiceReceivedDate] [datetime] NULL,
[IsPaid] [bit] NOT NULL,
[Timestamp] [datetime] NOT NULL);
CREATE TABLE [EventRegistrations](
[EventRegistrationId] [int] NOT NULL,
[UniqueId] [uniqueidentifier] NOT NULL,
[EventLocationId] [int] NOT NULL,
[PartyId] [int] NOT NULL,
[UserId] [nvarchar](128) NOT NULL,
[UserStatusId] [int] NOT NULL,
[WithdrawalDate] [datetime] NULL,
[OverrideSubmissionDate] [datetime] NULL,
[RegistrationStatusId] [int] NULL,
[PersonalExpenseCounter] [int] NULL,
[CompanyExpenseCounter] [int] NULL,
[ManagersUserID] [int] NULL)
CREATE TABLE [GoodsSuppliedExpenses](
[GoodsSuppliedExpenseId] [int] NOT NULL,
[EventRegistrationId] [int] NOT NULL,
[FundingAccountId] [int] NULL,
[ExpenseNumber] [int] NOT NULL,
[ExpenseVendorId] [int] NOT NULL,
[Description] [varchar](max) NOT NULL,
[AmountPaid] [decimal](12, 2) NOT NULL,
[CommercialValueAmount] [decimal](12, 2) NOT NULL,
[PurchaseDate] [datetime] NOT NULL,
[ExpenseTypeId] [int] NOT NULL,
[IsJointAgreement] [bit] NOT NULL,
[IfPaidDateOfDirection] [datetime] NULL,
[IsDisputed] [bit] NOT NULL,
[IsUndisputed] [bit] NOT NULL,
[AmountClaimed] [decimal](12, 2) NULL,
[NatureOfClaim] [varchar](255) NULL,
[InvoiceReceivedDate] [datetime] NULL,
[IsPaid] [bit] NOT NULL,
[Timestamp] [datetime] NOT NULL)
CREATE TABLE [HirePremisesExpenses](
[HirePremisesExpensesId] [int] NOT NULL,
[EventRegistrationId] [int] NOT NULL,
[FundingAccountId] [int] NULL,
[ExpenseNumber] [int] NOT NULL,
[ExpenseVendorId] [int] NOT NULL,
[Description] [varchar](255) NOT NULL,
[PurposeOfUse] [varchar](255) NOT NULL,
[PeriodStartDate] [datetime] NOT NULL,
[PeriodEndDate] [datetime] NOT NULL,
[AmountPaid] [decimal](12, 2) NOT NULL,
[CommercialValueAmount] [decimal](12, 2) NOT NULL,
[ExpenseTypeId] [int] NOT NULL,
[IsJointAgreement] [bit] NOT NULL,
[IfPaidDateOfDirection] [datetime] NULL,
[IsDisputed] [bit] NOT NULL,
[IsUndisputed] [bit] NOT NULL,
[AmountClaimed] [decimal](12, 2) NULL,
[NatureOfClaim] [varchar](255) NULL,
[InvoiceReceivedDate] [datetime] NULL,
[IsPaid] [bit] NOT NULL,
[Timestamp] [datetime] NOT NULL)
CREATE TABLE [PettyExpenses](
[PettyExpenseId] [int] NOT NULL,
[EventRegistrationId] [int] NOT NULL,
[FundingAccountId] [int] NULL,
[ExpenseNumber] [int] NOT NULL,
[AuthorizedPurchaserId] [int] NOT NULL,
[Description] [varchar](max) NOT NULL,
[ExpenseVendorId] [int] NULL,
[AmountPaid] [decimal](12, 2) NOT NULL,
[CommercialValueAmount] [decimal](12, 2) NOT NULL,
[PurchaseDate] [datetime] NOT NULL,
[ExpenseTypeId] [int] NOT NULL,
[IsDisputed] [bit] NOT NULL,
[IsUndisputed] [bit] NOT NULL,
[AmountClaimed] [decimal](12, 2) NULL,
[NatureOfClaim] [varchar](255) NULL,
[InvoiceReceivedDate] [datetime] NULL,
[IsPaid] [bit] NOT NULL,
[IfPaidDateOfDirection] [datetime] NULL,
[Timestamp] [datetime] NOT NULL)
CREATE TABLE [ServiceExpenses](
[ServiceExpenseId] [int] NOT NULL,
[EventRegistrationId] [int] NOT NULL,
[FundingAccountId] [int] NULL,
[ExpenseNumber] [int] NOT NULL,
[ExpenseVendorId] [int] NOT NULL,
[NatureOfWork] [varchar](255) NOT NULL,
[AmountPaid] [decimal](12, 2) NOT NULL,
[CommercialValueAmount] [decimal](12, 2) NOT NULL,
[ExpenseTypeId] [int] NOT NULL,
[IsJointAgreement] [bit] NOT NULL,
[IfPaidDateOfDirection] [datetime] NULL,
[IsDisputed] [bit] NOT NULL,
[IsUndisputed] [bit] NOT NULL,
[AmountClaimed] [decimal](12, 2) NULL,
[NatureOfClaim] [varchar](255) NULL,
[InvoiceReceivedDate] [datetime] NULL,
[IsPaid] [bit] NOT NULL,
[Timestamp] [datetime] NOT NULL)
CREATE TABLE [TravelHireVehicleExpenses](
[TravelHireVehicleExpenseId] [int] NOT NULL,
[EventRegistrationId] [int] NOT NULL,
[FundingAccountId] [int] NULL,
[ExpenseNumber] [int] NOT NULL,
[ExpenseVendorId] [int] NOT NULL,
[NatureOfClaim] [varchar](255) NOT NULL,
[AmountPaid] [decimal](12, 2) NOT NULL,
[CommercialValueAmount] [decimal](12, 2) NOT NULL,
[PurchaseDate] [datetime] NOT NULL,
[ExpenseTypeId] [int] NOT NULL,
[IsJointAgreement] [bit] NOT NULL,
[IfPaidDateOfDirection] [datetime] NULL,
[IsDisputed] [bit] NOT NULL,
[IsUndisputed] [bit] NOT NULL,
[AmountClaimed] [decimal](12, 2) NULL,
[NatureOfClaim1] [varchar](255) NULL,
[InvoiceReceivedDate] [datetime] NULL,
[IsPaid] [bit] NOT NULL,
[Timestamp] [datetime] NOT NULL)
GO
INSERT [EventRegistrations] ([EventRegistrationId], [UniqueId], [EventLocationId], [PartyId], [UserId], [UserStatusId], [WithdrawalDate], [OverrideSubmissionDate], [RegistrationStatusId], [PersonalExpenseCounter], [CompanyExpenseCounter], [ManagersUserID]) VALUES (4915, N'b1fc8d66-5906-4351-8d4e-312dfabd4a3f', 62, 3, N'43AC6D89-C764-7510-8281-AD066DC0C5E3', 1, CAST(N'9999-12-31 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), 1, 0, 0, NULL)
GO
INSERT [EventRegistrations] ([EventRegistrationId], [UniqueId], [EventLocationId], [PartyId], [UserId], [UserStatusId], [WithdrawalDate], [OverrideSubmissionDate], [RegistrationStatusId], [PersonalExpenseCounter], [CompanyExpenseCounter], [ManagersUserID]) VALUES (4916, N'b3d5e9f0-bc50-4b9f-9a73-b0c931c48712', 62, 1, N'21597361-3418-FC34-59EB-E3891178BA83', 1, CAST(N'9999-12-31 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), 1, 0, 0, NULL)
GO
INSERT [EventRegistrations] ([EventRegistrationId], [UniqueId], [EventLocationId], [PartyId], [UserId], [UserStatusId], [WithdrawalDate], [OverrideSubmissionDate], [RegistrationStatusId], [PersonalExpenseCounter], [CompanyExpenseCounter], [ManagersUserID]) VALUES (4917, N'ece57f63-d6e6-47a5-8a1d-22689f61b05c', 62, 5, N'71D0D677-003F-B671-2A6A-E1F18409F9AC', 1, CAST(N'9999-12-31 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), 1, 0, 0, NULL)
GO
INSERT [EventRegistrations] ([EventRegistrationId], [UniqueId], [EventLocationId], [PartyId], [UserId], [UserStatusId], [WithdrawalDate], [OverrideSubmissionDate], [RegistrationStatusId], [PersonalExpenseCounter], [CompanyExpenseCounter], [ManagersUserID]) VALUES (4918, N'7e97a007-449d-48ce-89dc-7febc6dbe506', 63, 3, N'581B38F9-9C3B-68F0-4919-4D404B397B3E', 1, CAST(N'9999-12-31 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), 1, 0, 0, NULL)
GO