View that sums payments by person over a period of years

Question

View that sums payments by person over a period of years

DaveBriCam

SSCarpal Tunnel

Points: 4294
More actions
November 27, 2018 at 1:45 pm

#416518

I am trying to create a view that sums the [amount] by [peopleId] in the table [Payments] but this below is not working:
CREATE VIEW [dbo].[RevenuePerPerson] AS
SELECT sum([amount]) AS 'Revenue' FROM [BCC_DB].[dbo].[Payments] WHERE year([transactionDate]) BETWEEN 2011 and 2018 GROUP BY year([peopleId])
CREATE TABLE [dbo].[Payments]( [transactionId] [int] IDENTITY(1,1) NOT NULL, [peopleId] [int] NULL, [chargeId] [int] NOT NULL, [batchID] [int] NULL, [feeTypeId] [int] NOT NULL, [paymentTypeId] [int] NOT NULL, [amount] [money] NOT NULL CONSTRAINT [DF_Payments_amount] DEFAULT ((0)), [transactionDate] [datetime] NULL, [Notes] [nvarchar](4000) NULL, [refNumber] [nvarchar](50) NULL, [insertUser] [nvarchar](50) NULL, [insertDate] [datetime] NULL, [applied] [bit] NULL CONSTRAINT [DF_Payments_applied] DEFAULT ((0)), [dateApplied] [datetime] NULL, [refunded] [bit] NULL CONSTRAINT [DF_Payments_refunded] DEFAULT ((0)), CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED ( [transactionId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

Viewing 5 posts - 1 through 4 (of 4 total)

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

ryan.mcatee SSC Eights! Points: 860 More actions · Answer 1

?
SELECT peopleId
, year([transactionDate]) AS Year
, SUM(amount) AS Revenue
FROM [BCC_DB].[dbo].[Payments]
WHERE year([transactionDate]) BETWEEN 2011 and 2018
GROUP BY [peopleId]
, year([transactionDate])

Or did you want a total for all of the years, just take year out...
SELECT peopleId , SUM(amount) AS Revenue FROM [BCC_DB].[dbo].[Payments] WHERE year([transactionDate]) BETWEEN 2011 and 2018 GROUP BY [peopleId]

drew.allen SSC Guru Points: 76944 More actions · Answer 2

briancampbellmcad - Tuesday, November 27, 2018 1:45 PM
I am trying to create a view that sums the [amount] by [peopleId] in the table [Payments] but this below is not working:
CREATE VIEW [dbo].[RevenuePerPerson] AS
SELECT sum([amount]) AS 'Revenue' FROM [BCC_DB].[dbo].[Payments] WHERE year([transactionDate]) BETWEEN 2011 and 2018 GROUP BY year([peopleId])
CREATE TABLE [dbo].[Payments]( [transactionId] [int] IDENTITY(1,1) NOT NULL, [peopleId] [int] NULL, [chargeId] [int] NOT NULL, [batchID] [int] NULL, [feeTypeId] [int] NOT NULL, [paymentTypeId] [int] NOT NULL, [amount] [money] NOT NULL CONSTRAINT [DF_Payments_amount] DEFAULT ((0)), [transactionDate] [datetime] NULL, [Notes] [nvarchar](4000) NULL, [refNumber] [nvarchar](50) NULL, [insertUser] [nvarchar](50) NULL, [insertDate] [datetime] NULL, [applied] [bit] NULL CONSTRAINT [DF_Payments_applied] DEFAULT ((0)), [dateApplied] [datetime] NULL, [refunded] [bit] NULL CONSTRAINT [DF_Payments_refunded] DEFAULT ((0)), CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED ( [transactionId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

You've been around long enough to know that we always ask for sample data and expected results.

You've also been around long enough to know that you should not use functions on table fields in WHERE or ON clauses, because it prevents index seeks. Your WHERE clause should be re-written as follows.
WHERE transactionDate >= '2011-01-01' AND transactionDate < '2019-01-01'

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ScottPletcher SSC Guru Points: 100942 More actions · Answer 3

Since you've very likely to (almost) always query by trans date, you should change the clustering on the table to:
( [transactionDate], [transactionId] )

That will help performance for most queries. Maybe not much for this one, unless you have a lot of years before 2011 in the table.

You can leave the trans id as the pk if you like, just make it nonclustered.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

ScottPletcher - Tuesday, November 27, 2018 2:46 PM
Since you've very likely to (almost) always query by trans date, you should change the clustering on the table to:
( [transactionDate], [transactionId] )
That will help performance for most queries. Maybe not much for this one, unless you have a lot of years before 2011 in the table.
You can leave the trans id as the pk if you like, just make it nonclustered.

It depends and, I have to say, with the width of the tables I've been working with, it actually hurts performance of such simple reporting queries a whole lot . If the Leaf Level of the CI is wide byte-wise, then making queries use it can make even the simplest of queries use 2 or more orders of magnitude of memory and cause duration to skyrocket compared to a well designed NCI.

--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)