March 2, 2010 at 1:10 pm
I'm trying this at the end of an insert. The @CumulativePercentofTotal works when MonthlyPercentOfTotal is commented out. HOwever does not work with it in the code.
This is what I am running:
UPDATE @PercentTotals
SET @MonthlyPercentofTotal = MonthlyPercentofTotal = cast(GrossCollection/Sum(GrossCollection) as decimal(5,3),
@CumulativePercentofTotal = CumulativePercentofTotal = @CumulativePercentofTotal + MonthlyPercentofTotal
FROM @PercentTotals
This is the error:
Msg 157, Level 15, State 1, Line 50
An aggregate may not appear in the set list of an UPDATE statement.
I've also try cast and coalesce but I'm not sure if I'm writing it correctly.
Any help appreciated.
¤ §unshine ¤
March 2, 2010 at 1:30 pm
Are you attempting to use a 3 part update on a table variable that includes a SUM?
You can't do that: as the error message says, you can't use aggregate functions (SUM) in the SET portion of an update statement.
That doesn't mean you can't do what you're trying to do, just that you'll have to change your methodology somewhat.
If you could, provide table structure and sample data along with the results that you're expecting according to the post in my signature and we can probably help you rework this.
March 2, 2010 at 1:50 pm
Thank you Seth...
Here is the result set I am looking for:
GrossCollection should be GrossCollection/sum(gross collection)
Field3Field2GrossCollectionMonthlyTotal CumulativeTotal
20 0 500000 3.37% 3.37%
1 1500000 8.47% 11.84%
2 11500000 64.91% 76.75%
3 500000 2.82% 79.57%
4 823959.62 4.65% 84.22%
TotalSum 14823959.62
This is the code i'm using except I replaced db, table and column names for security reasons.
DECLARE @PercentTotals TABLE (
Field1 varchar (12),
GrossCollection money,
Field2 int ,
Field3 int,
MonthlyPercentofTotal decimal (5,2),
CumulativePercentofTotal decimal (5,2))
DECLARE @MonthlyPercentofTotal decimal (5,2),
@CumulativePercentofTotal decimal (5,2)
SET @CumulativePercentofTotal = 0
SET@MonthlyPercentofTotal=0
INSERT INTO @PercentTotals
SELECT top (10) Field1,
sum(a.GrossCollection),
DimTime.Field2 ,
DimTime.Field3,
0,
0
FROM
Database.dbo.a
INNER JOIN b ON a.Date =b.Date
WHERE
a.Year = (DATEPART(yyyy, getdate())-1)
AND b.Field3 = 20
GROUP BY
a.Field1,
a.PaymentDate,
a.PaymentMonth,
a.PaymentYear,
b.Field2,
b.Field3
ORDER BY Field3
UPDATE @PercentTotals
SET @MonthlyPercentofTotal = cast(GrossCollection/Sum(GrossCollection)as decimal(5,3))
--@CumulativePercentofTotal = CumulativePercentofTotal = @CumulativePercentofTotal + MonthlyPercentofTotal
FROM @PercentTotals
SELECT * FROM @PercentTotals
¤ §unshine ¤
March 2, 2010 at 6:36 pm
This is still very ambiguous. The article in my signature titled 'How to Provide Sample Data' explains how to make this a lot more clear for us, but I'll see if I can point you in the right direction from what you've provided here.
I'm assuming that one of those renamed fields identifies an item that you want to sum. I'll assume for this that it's Field1. The goal of this next part is to get the overall total in this step so that you don't need the SUM() in the running total. I'm assuming you want the total grouped only by Field1. If you wanted the total by Fields 1,2 and 3, add those to the SELECT, the GROUP BY and the ON.
In your initial Table Variable, add this join:
INNER JOIN (SELECT A2.Field1, SUM(A2.GrossCollection) TotalCollections
FROM Database.dbo.a A2
GROUP BY A2.Field1) DT_A ON A.Field1 = DT_A.Field1
Add DT_A.TotalCollections to the SELECT and GROUPBY (You'll need to add it to the table declaration as well)
You can't create clustered indexes on table variables after the fact(And you need one for a 3 part update), so you'll have to do it in the initial creation by adding a line such as PRIMARY KEY CLUSTERED(Field1, Field2, Field3). The important thing is that this clustered index defines the order that the 3 part update will run in.
Your quirky update should look more like this (Although this is off the top of my head and still very likely to be wrong.)
UPDATE @PercentTotals
SET @CumulativePercentofTotal = CumulativePercentofTotal =
CASE WHEN Field1 = @PrevField1 --AND
--Field2 = @PrevField2 AND
--Field3 = @PrevField3
THEN @CumulativePercentofTotal + cast(GrossCollection/TotalCollections as decimal(5,3))
ELSE cast(GrossCollection/TotalCollections as decimal(5,3))
END,
@PrevField1 = Field1,
@PrevField2 = Field2,
@PrevField3 = Field3
FROM @PercentTotals P
OPTION (MAXDOP 1)
I'll throw it out there again that I'm totally guessing here because you haven't provided sample data + results. It would have been quite a bit easier for both of us if you had.
March 2, 2010 at 8:33 pm
I am completely sorry. The link completely went over my head. I will provide you with the specific format. Again, I'm sorry.
¤ §unshine ¤
March 3, 2010 at 7:36 am
No need to apologize, I wasn't trying to give you a hard time, merely noting it.
What you should basically have is something like this:
CREATE TABLE A(
Field1 int,
Field2 datetime,
...
)
CREATE TABLE B(
Field1 int,
Field2 Datetime,
...
)
INSERT INTO A(Field1, Field2...)
SELECT 1, '01/23/2007',... UNION ALL
SELECT 2, '02/26/2007',... UNION ALL
...
INSERT INTO B(Field1, Field2...)
SELECT 1, '01/23/2007',... UNION ALL
SELECT 2, '02/26/2007',... UNION ALL
...
Then your expected results. I believe you have those above and those are fine, so long as you can arrive at them based on the sample data you provide. Basically, you're showing us data where you are at, and data where you'd like to be, and we're helping you get there.
March 3, 2010 at 12:08 pm
No problem at all, here it is and thank you so much for your help.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Field3 int,
Field2 int,
GrossCollection money,
MonthlyTotal decimal (5,2),
CumulativeTotal decimal (5,2)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
--Monthly Total=(GrossCollection/14823959.62) which is sum of GrossCollection for Field 3 (20)
--or
--Monthly Total=(GrossCollection/16872909.28) (21)
INSERT INTO #mytable
(ID, Field3, Field2, GrossCollection ,MonthlyTotal, CumulativeTotal)
SELECT '1',20,0, 500000 ,3.37 ,3.37UNION ALL
SELECT '2',20,1, 1500000 ,10.12,13.49UNION ALL
SELECT '3',20,2, 11500000 ,77.58,91.07UNION ALL
SELECT '4',20,3, 500000 ,2.82,93.89UNION ALL
SELECT '5',21,0,20202,0.10,0.10UNION ALL
SELECT '6',21,1,106888,0.60,0.80UNION ALL
SELECT '7',21,2,178841,1.10,1.80UNION ALL
SELECT '8',21,3,16566976.45,98.2,100.0
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF
select * from #mytable
¤ §unshine ¤
March 3, 2010 at 3:38 pm
I appreciate the effort, but unfortunately that isn't quite what I need. The concept of creating the table and the inserts are perfect, but I need that for starting data (fictional data is fine, so long as it is an accurate representation), not for the results. That said, those results are much better than the ones you posted last time and let me refine my suggestions from the prior post.
The clustered index I mentioned should probably be (Field3, Field2).
The Case statement should compare Field3 = @PrevField3 not Field1.
Give those mods a shot and let us know how that goes.
March 3, 2010 at 9:04 pm
Okie doke... i'll get back to you tomorrow... 🙂
¤ §unshine ¤
March 4, 2010 at 12:19 pm
Hi there,
It seems as though the case statement is not reading the total collections from the join at the top. I'm getting this error.
Msg 207, Level 16, State 1, Line 63
Invalid column name 'TotalCollections'.
Msg 207, Level 16, State 1, Line 64
Invalid column name 'TotalCollections'.
If it is easier, I can work on getting you the original sample data you asked for. Let me know.
I'll just need a little time. This is my first attempt at business logic and calculations. Double whammy for me. I'm starting to get into development from administration. I appreciate you!
¤ §unshine ¤
March 4, 2010 at 2:28 pm
I have attached everything I think you need. Let me know if I am missing anything!
Thanks again!
¤ §unshine ¤
March 4, 2010 at 4:03 pm
Let's back up a bit and take this one step at a time. The 3 part update is a very complicated concept and trying to pull it off without a solid foundation is going to just make this a mess.
First, let's get the table variable step taken care of so that you're happy with the data and I'm sure I know what kind of data you have. Check out the following code:
[Edit] Bad Code, Fixing...[/Edit]
I think your sample data is missing a field for whatever entity all these payments tie to (perhaps removed to protect your data?), but for this to work, *a* field still needs to be there, it can just be filled with bogus info. For example, if your entityID is 'JPMORGANCO', it can become 'AAAAAAA'. You just need to make sure that the data you provide matches the results you provide so that everything makes sense.
March 4, 2010 at 4:26 pm
I went ahead and debugged this, you can run the whole statement. Note a few things.
1. Your sample data doesn't match your results, things like the workingdayofmonth doesn't match the data you supplied.
2. I added a couple columns to #Dmap. You might already have columns that will serve this purpose that were not included here. If you don't, Sequence can be generated with rownumber.
3. New sample data is at the bottom below yours. The only thing missing is the 'Cumulative' portion, or Running total. After you're happy with this portion, we can move on to that.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#DT','U') IS NOT NULL
DROP TABLE #DT
IF OBJECT_ID('TempDB..#DMAP','U') IS NOT NULL
DROP TABLE #DMAP
IF OBJECT_ID('TempDB..#results','U') IS NOT NULL
DROP TABLE #results
IF OBJECT_ID('TempDB..#results','PK') IS NOT NULL
alter table #results drop CONSTRAINT PK_results
--Table 1
CREATE TABLE #DT(
DTKey int IDENTITY(1,1) NOT NULL,
ThisDate datetime NOT NULL,
ThisDay tinyint NOT NULL,
ThisMonth tinyint NOT NULL,
ThisQuarter tinyint NOT NULL,
ThisYear smallint NOT NULL,
IsWeekEnd char(1) NOT NULL CONSTRAINT DF_DT_IsWeekEnd DEFAULT (0),
IsHoliday char(1) NULL,
LastDateOfMonth datetime NOT NULL,
WorkingDaysInMonth tinyint NULL,
PercentMonthWorked decimal(5, 2) NULL,
WorkingDayOfMonth tinyint NULL,
DayOfWeek char(1) NOT NULL,
DayOfYear smallint NOT NULL,
WeekOfMonth char(1) NOT NULL,
WeekOfYear tinyint NOT NULL,
CONSTRAINT PK_DT PRIMARY KEY CLUSTERED
(DTKey ASC) )
---Table 2
CREATE TABLE #DMAP(
FactPaymentKey int NOT NULL,
FactPlacementKey int NOT NULL,
DimLocationKey int NOT NULL,
Directory smallint NOT NULL,
PaymentDate smalldatetime NOT NULL,
PaymentMonth tinyint NOT NULL,
PaymentYear smallint NOT NULL,
PaymentTreatmentChannel varchar(50) NULL,
PaymentPeriodNumber smallint NOT NULL,
PaymentAmount money NOT NULL,
entityid int,
sequence int
)
--Create Table 3
--Expected Results from Query using table variable
CREATE TABLE #results
(Directory varchar(15),
WorkingDaysInMonth int,
WorkingDayOfMonth int,
GrossCollection money,--#DMAP.PaymentAmount
MonthlyPercentofTotal decimal (5,2),--#DMAP.PaymentAmount/sum(#DMAP.PaymentAmount)per grouped by #DT.workingdaysinmonth
CumulativePercentofTotal decimal (5,2),-- MonthlyPercentofTotal + Previous MonthlyPercentofTotal grouped by#DT.workingdaysinmonth
CONSTRAINT PK_results PRIMARY KEY CLUSTERED
(Directory,WorkingDaysInMonth, WorkingDayOfMonth ))
--Insert Table 1
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #DT ON
--===== Insert the test data into the test table
INSERT INTO #DT
(DTKey,ThisDate,ThisDay,ThisMonth,ThisQuarter,ThisYear,IsWeekEnd,
IsHoliday ,LastDateOfMonth ,WorkingDaysInMonth ,PercentMonthWorked ,WorkingDayOfMonth ,
DayOfWeek ,DayOfYear,WeekOfMonth ,WeekOfYear
)
SELECT '10228','Jan 1 2009 12:00AM','1','1','1','2009','0','1','Jan 31 2009 12:00AM','20','0.00','0','4','1','1','1' UNION ALL
SELECT '10229','Jan 2 2009 12:00AM','2','1','1','2009','0','0','Jan 31 2009 12:00AM','20','0.05','1','5','2','1','1' UNION ALL
SELECT '10230','Jan 3 2009 12:00AM','3','1','1','2009','1','0','Jan 31 2009 12:00AM','20','0.05','2','6','3','1','1' UNION ALL
SELECT '10231','Jan 4 2009 12:00AM','4','1','1','2009','1','0','Jan 31 2009 12:00AM','20','0.05','3','7','4','1','1' UNION ALL
SELECT '10232','Jan 5 2009 12:00AM','5','1','1','2009','0','0','Jan 31 2009 12:00AM','20','0.10','4','1','5','1','1' UNION ALL
SELECT '10440','Aug 1 2009 12:00AM','1','8','3','2009','1','0','Aug 31 2009 12:00AM','21','0.00','0','6','213','1','31' UNION ALL
SELECT '10441','Aug 2 2009 12:00AM','2','8','3','2009','1','0','Aug 31 2009 12:00AM','21','0.00','4','7','214','1','31' UNION ALL
SELECT '10442','Aug 3 2009 12:00AM','3','8','3','2009','0','0','Aug 31 2009 12:00AM','21','0.05','1','1','215','1','31' UNION ALL
SELECT '10443','Aug 4 2009 12:00AM','4','8','3','2009','0','0','Aug 31 2009 12:00AM','21','0.10','2','2','216','1','31' UNION ALL
SELECT '10444','Aug 5 2009 12:00AM','5','8','3','2009','0','0','Aug 31 2009 12:00AM','21','0.14','3','3','217','1','31'
--Insert Table 2
INSERT INTO #DMAP
(FactPaymentKey ,FactPlacementKey ,DimLocationKey ,Directory ,PaymentDate ,
PaymentMonth ,PaymentYear ,PaymentTreatmentChannel ,PaymentPeriodNumber ,PaymentAmount, EntityID, Sequence)
SELECT '28424989','38330215','1','1000','Jan 1 2009 12:00AM','1','2009','WHP','11',10.00,1,1 UNION ALL
SELECT '28424989','38330215','1','1000','Jan 1 2009 12:00AM','1','2009','WHP','11',10.00,1,2 UNION ALL
SELECT '28425635','35223938','5','1000','Jan 2 2009 12:00AM','1','2009','WHP','15',100.00,1,3 UNION ALL
SELECT '28425635','35223938','5','1000','Jan 2 2009 12:00AM','1','2009','WHP','15',100.00,1,4 UNION ALL
SELECT '28425635','35223938','5','1000','Jan 2 2009 12:00AM','1','2009','WHP','15',100.00,2,5 UNION ALL
SELECT '29756420','58755000','1','1000','Aug 1 2009 12:00AM','8','2009','House','0',50.00,3,6 UNION ALL
SELECT '29756420','58755000','1','1000','Aug 1 2009 12:00AM','8','2009','House','0',50.00,3,7 UNION ALL
SELECT '29756420','58755000','1','1000','Aug 1 2009 12:00AM','8','2009','House','0',50.00,3,8 UNION ALL
SELECT '29756420','58755000','1','1000','Aug 1 2009 12:00AM','8','2009','House','0',50.00,3,9 UNION ALL
SELECT '29756420','58755000','1','1000','Aug 1 2009 12:00AM','8','2009','House','0',50.00,3,10
-- Insert expected results
INSERT INTO #results
(Directory, WorkingDaysInMonth, WorkingDayofMonth, GrossCollection ,MonthlyPercentofTotal, CumulativePercentofTotal)
Select'Internal', 20,0,10.00,3.1,3.1 UNION ALL
Select'Internal', 20,1,10.00,3.1,6.3 UNION ALL
Select'Internal', 20,2,100.00,31.3,37.5 UNION ALL
Select'Internal', 20,3,100.00,31.3,68.8 UNION ALL
Select'Internal', 20,4,100.00,31.3,100.0 UNION ALL
Select'Internal', 21,0,50.00,20.0,20.0 UNION ALL
Select'Internal', 21,4,50.00,20.0,40.0 UNION ALL
Select'Internal', 21,1,50.00,20.0,60.0 UNION ALL
Select'Internal', 21,2,50.00,20.0,80.0 UNION ALL
Select'Internal', 21,3,50.00,20.0,100.0
--SELECT FROM ALL TABLES
SELECT * FROM #DT
ORDER BY WORKINGDAYSINMONTH
SELECT * FROM #DMAP
SELECT * FROM #results
DECLARE @PercentTotals TABLE (
EntityIDint,-- Added, Not present in sample data
LastDateOfMonthdatetime, -- Added, need this for the next part.
Directoryvarchar (12),
PaymentAmountmoney,
WorkingDaysInMonthint ,
WorkingDayOfMonthint,
MonthlyPercentofTotaldecimal (5,2),
CumulativePercentofTotaldecimal (5,2),
TotalCollectionsdecimal (9,2),
Sequenceint-- This is really any field that uniquely identifies an individual payment.
-- If you don't have one, we can create one
PRIMARY KEY CLUSTERED (EntityID, LastDateOfMonth, WorkingDayOfMonth,Sequence)
)
INSERT INTO @PercentTotals (EntityID, LastDateOfMonth, Directory, PaymentAmount, WorkingDaysInMonth,
WorkingDayOfMonth, MonthlyPercentofTotal, CumulativePercentofTotal, TotalCollections, Sequence)
SELECT DMap.EntityID,
DT.LastDateOfMonth,
DMap.Directory, -- You have a 'Directory' column in your DMap sample data, but it doesn't match your results.
DMap.PaymentAmount,
DT.WorkingDaysInMonth,
DT.WorkingDayOfMonth,
(DMap.PaymentAmount / DMDT2.MonthlyCollections) * 100,
0,
DMDT1.TotalCollections,
DMap.Sequence
FROM #DT DT
INNER JOIN #DMap DMap ON DT.ThisDate = DMap.PaymentDate
-- You're missing an EntityID that identifies a customer/client/entity you want to group by.
-- Your data reflects that you have one but you're not including it.
-- Put whatever the actual field is in place of entityID
INNER JOIN (SELECT DM2.EntityID , SUM(DM2.PaymentAmount) TotalCollections
FROM #DMap DM2
GROUP BY DM2.EntityID) DMDT1 ON DMap.EntityID = DMDT1.EntityID
INNER JOIN (SELECT DM3.EntityID, DT2.LastDateOfMonth, SUM(DM3.PaymentAmount) MonthlyCollections
FROM #DMap DM3
INNER JOIN #DT DT2 ON DT2.ThisDate = DM3.PaymentDate
GROUP BY DM3.EntityID, DT2.LastDateOfMonth) DMDT2 ON DMap.EntityID = DMDT2.EntityID AND DT.LastDateOfMonth = DMDT2.LastDateOfMonth
WHERE DMap.PaymentYear = (DATEPART(yyyy, getdate())-1)
--AND DT.WorkingDaysInMonth = 20
ORDER BY DMap.EntityID, DT.LastDateOfMonth, DT.WorkingDayOfMonth, DMap.Sequence
SELECT * FROM @PercentTotals PT
March 5, 2010 at 11:28 am
You rock Seth! Thank you!
1. I'm sorry. I think I messed that part up too. I need to group the sum by WorkingDayOfMonth as well. I'll send you a new select statement with correct data.
2. I have a field that uniquely identifies payment and have one that identifies account/client/entity, however I would have to bring in a 3rd table and it does not seem as if I should have to since I'm basing this query on the amount of money being brought in, but I'll play around with this to see if i can get it to work.
3. In my query I change the directory number to a literal word with a case statement.
¤ §unshine ¤
March 5, 2010 at 1:56 pm
My turn for an oops. For whatever reason, when I was reading this last night it appeared that the %'s were all off based on the numbers and made it look like there had to be an entity involved. They do match up, I just wasn't reading things properly. Ignore all the stuff about entityID, it can be removed entirely.
The concept here is that I'm creating 'Derived Tables' to get any of the summary information by different things. You can do this for whatever aggregates you want. You can also use CTE's for the same thing. Because I'm still using SQL 2000 in my day to day environment, I tend to use derived tables by default, but either is fine.
So, if you wanted to add an aggregate for workingdayofmonth, you could just add another derived table with:
LEFT JOIN (
Select WorkingDayOfMonth, SUM(...)
FROM ...
GROUP BY WorkingDayOfMonth) X ON DT.WorkingDayOfMonth = X.WorkingDayOfMonth
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply