October 26, 2011 at 6:09 am
Hello!
Please run the queries below to get along.
The users of the tables below want to see accurate summation of amount per everyone who gives or make a donation in their charity organization but the query for this seems to be right but produces wrong answers. Please help to diagnose. Or is anything wrong with the table creation?
IF OBJECT_ID('dbo.Members') IS NOT NULL BEGIN
DROP TABLE dbo.Members;
END;
CREATE TABLE dbo.Members
(
--MemberID INT NOT NULL
MemberIdentityID INT NOT NULL IDENTITY(1,1)
, LastName NVARCHAR(50) NULL
, CONSTRAINT PK_Membes_MemberID_MemberIdentityID PRIMARY KEY (MemberIdentityID)
);
GO
IF OBJECT_ID('dbo.Partnership') IS NOT NULL BEGIN
DROP TABLE dbo.Partnership;
END;
CREATE TABLE dbo.Partnership
(
PartnershipID INT NOT NULL IDENTITY(1,1)
, MemberIdentityID INT NOT NULL
, LTV MONEY NULL DEFAULT '0'
, RoR MONEY NULL DEFAULT '0'
, PartnershipDate DATETIME NOT NULL
, CONSTRAINT PK_PartnershipID PRIMARY KEY (PartnershipID)
, CONSTRAINT fk_Members_Partnership FOREIGN KEY(MemberIdentityID) REFERENCES dbo.Members(MemberIdentityID)
);
GO
CREATE NONCLUSTERED INDEX idx_MemberIdentityID ON dbo.Partnership(MemberIdentityID);
CREATE NONCLUSTERED INDEX idx_op_Date ON dbo.Partnership(PartnershipDate);
IF OBJECT_ID('dbo.TitheAndOffering') IS NOT NULL BEGIN
DROP TABLE Offering.TitheAndOffering;
END;
CREATE TABLE dbo.TitheAndOffering
(
TitheAndOfferingID INT NOT NULL IDENTITY(1,1)
, MemberIdentityID INT NOT NULL
, Offering MONEY NULL DEFAULT '0'
, Tithe MONEY NULL DEFAULT '0'
, SeedOffering MONEY NULL DEFAULT '0'
, OfferingDate DATETIME NOT NULL
, CONSTRAINT PK_TitheAndOffering PRIMARY KEY(TitheAndOfferingID)
, CONSTRAINT fk_TitheAndOffering_Members FOREIGN KEY(MemberIdentityID) REFERENCES dbo.Members(MemberIdentityID)
);
CREATE NONCLUSTERED INDEX idx_OfferingDate ON dbo.TitheAndOffering(OfferingDate);
CREATE NONCLUSTERED INDEX idx_MemberIdentityID ON dbo.TitheAndOffering(MemberIdentityID);
IF OBJECT_ID('dbo.Pledges') IS NOT NULL BEGIN
DROP TABLE dbo.Pledges;
END;
CREATE TABLE dbo.Pledges
(
PledgeID INT NOT NULL IDENTITY
, MemberIdentityID INT NULL
, PledgeType NVARCHAR(70)
, PledgeAmount MONEY NULL DEFAULT (0)
, PledgeDate DATETIME NOT NULL
, CONSTRAINT pk_pledges PRIMARY KEY NONCLUSTERED(PledgeID)
, CONSTRAINT fk_Pledges FOREIGN KEY(MemberIdentityID) REFERENCES dbo.Members(MemberIdentityID)
);
CREATE CLUSTERED INDEX idx_PledgeType ON dbo.Pledges(PledgeType);
CREATE NONCLUSTERED INDEX idx_PledgeDate ON dbo.Pledges(PledgeDate);
CREATE NONCLUSTERED INDEX idx_MemberIdentityID ON dbo.Pledges(MemberIdentityID);
INSERT INTO dbo.Members(LastName)
VALUES('Craig'),('King'),('Paul')
INSERT INTO dbo.Partnership(MemberIdentityID,LTV,RoR,PartnershipDate)
VALUES(1,20,50,'2011/09/10'),(2,'10','30','2011/09/10'),(3,'10','10','2011/09/10'),
(1,'30','0','2011/10/10'),(2,'80','40','2011/10/10'),(4,'50','50','2011/10/10');
INSERT INTO dbo.Pledges(MemberIdentityID,PledgeType,PledgeAmount,PledgeDate)
VALUES(1,'Web','200','2011/08/10'),(2,'Web','300','2011/08/10'),(3,'Web','100','2011/08/10'),
(2,'Games','50','2011/9/20'),(3,'Games','60','2011/9/20');
INSERT INTO TitheAndOffering(MemberIdentityID,Offering,Tithe,SeedOffering,OfferingDate)
VALUES(1,'10','500','1000','2011/9/20'),(3,'400','600','0','2011/9/20');
-----Correct summation
SELECT LastName
, SUM(LTV) AS LTVTotal
, sum(RoR) RoRTotal
FROM dbo.Members AS m INNER JOIN dbo.Partnership AS ps
ON m.MemberIdentityID = ps.MemberIdentityID
GROUP BY LastName
Craig 90.00
70.00
Don 50.00 50.00
King 10.00 10.00
Paul 50.00 50.00
---wrong summation(why?)
SELECT LastName
, SUM(PledgeAmount)PledgeTotalPerIndividual
, sum(LTV)LTVTotalPerIndividual
, sum(RoR)RoRTotalPerIndividual
, sum(Offering)OfferingTotalPerIndividual
, sum(Offering)TitheTotalPerIndividual
, sum(SeedOffering)SeedOfferingPerIndividual
FROM dbo.Members AS m INNER JOIN dbo.Pledges AS p
ON m.MemberIdentityID = p.MemberIdentityID INNER JOIN dbo.Partnership AS ps
ON m.MemberIdentityID = ps.MemberIdentityID INNER JOIN dbo.TitheAndOffering AS t
ON m.MemberIdentityID = t.MemberIdentityID
GROUP BY LastName
Don 400.00 50.00 50.00 20.00 20.00 2000.00
King 160.00
20.00 20.00
800.00 800.00
0.00
What is wrong?
October 26, 2011 at 6:34 am
SaintGr8 (10/26/2011)
Hello!Please run the queries below to get along....
Your script has numerous faults - I'm sure if you fix them and repost, it won't take long to solve this.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 26, 2011 at 6:38 am
ChrisM@Work (10/26/2011)
SaintGr8 (10/26/2011)
Hello!Please run the queries below to get along....
Your script has numerous faults - I'm sure if you fix them and repost, it won't take long to solve this.
I think the only problem is that he posted in a 2005 forum with a 2008 script.
The only problem with the scripts is with the 4 insert statements that don't work on 2K5.
October 26, 2011 at 6:38 am
I'm not an expert but I'd take a look at your joins. You join to Partnership which has multiple rows for ID 1, then join to the other tables which causes duplicates. You need to split the different summations out into their own queries.
October 26, 2011 at 6:42 am
You've got serious "tuple multiplication" inherent in that structure.
Run the final query without the aggregations, you'll see lots of "duplicate" values.
The whole design needs to be rethought. Sorry, that sounds brutal, but I don't know how to put it more delicately.
I'd need to know a lot more about the business rules it's built on to offer any detailed advice. But the main thing should be consolidating ALL of your donations, et al, into one table, with types, instead of multiple tables.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2011 at 6:43 am
Ninja's_RGR'us (10/26/2011)
ChrisM@Work (10/26/2011)
SaintGr8 (10/26/2011)
Hello!Please run the queries below to get along....
Your script has numerous faults - I'm sure if you fix them and repost, it won't take long to solve this.
I think the only problem is that he posted in a 2005 forum with a 2008 script.
The only problem with the scripts is with the 4 insert statements that don't work on 2K5.
I'm using 2k8 Remi. The first run failed for a number of reasons including an incompatible date, so I put set dateformat mdy at the top of the script and ran it again...
Msg 3726, Level 16, State 1, Line 4
Could not drop object 'dbo.Members' because it is referenced by a FOREIGN KEY constraint.
Msg 2714, Level 16, State 6, Line 8
There is already an object named 'Members' in the database.
Msg 3701, Level 11, State 5, Line 12
Cannot drop the table 'Offering.TitheAndOffering', because it does not exist or you do not have permission.
Msg 2714, Level 16, State 6, Line 16
There is already an object named 'TitheAndOffering' in the database.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 26, 2011 at 6:44 am
IF OBJECT_ID('dbo.Members') IS NOT NULL
BEGIN
DROP TABLE dbo.Members;
END;
CREATE TABLE dbo.Members(
--MemberID INT NOT NULL
MemberIdentityID INT NOT NULL IDENTITY(1,1)
, LastName NVARCHAR(50) NULL);
GO
IF OBJECT_ID('dbo.Partnership') IS NOT NULL
BEGIN
DROP TABLE dbo.Partnership;
END;
CREATE TABLE dbo.Partnership (
PartnershipID INT NOT NULL IDENTITY(1,1)
, MemberIdentityID INT NOT NULL
, LTV MONEY NULL DEFAULT '0'
, RoR MONEY NULL DEFAULT '0'
, PartnershipDate DATETIME NOT NULL);
GO
IF OBJECT_ID('dbo.TitheAndOffering') IS NOT NULL
BEGIN
DROP TABLE dbo.TitheAndOffering;
END;
CREATE TABLE dbo.TitheAndOffering (
TitheAndOfferingID INT NOT NULL IDENTITY(1,1)
, MemberIdentityID INT NOT NULL
, Offering MONEY NULL DEFAULT '0'
, Tithe MONEY NULL DEFAULT '0'
, SeedOffering MONEY NULL DEFAULT '0'
, OfferingDate DATETIME NOT NULL);
IF OBJECT_ID('dbo.Pledges') IS NOT NULL
BEGIN
DROP TABLE dbo.Pledges;
END;
CREATE TABLE dbo.Pledges (
PledgeID INT NOT NULL IDENTITY
, MemberIdentityID INT NULL
, PledgeType NVARCHAR(70)
, PledgeAmount MONEY NULL DEFAULT (0)
, PledgeDate DATETIME NOT NULL);
INSERT INTO dbo.Members(LastName)
VALUES('Craig'),('King'),('Paul')
INSERT INTO dbo.Partnership(MemberIdentityID,LTV,RoR,PartnershipDate)
VALUES(1,20,50,'2011/09/10'),(2,'10','30','2011/09/10'),(3,'10','10','2011/09/10'),
(1,'30','0','2011/10/10'),(2,'80','40','2011/10/10'),(4,'50','50','2011/10/10');
INSERT INTO dbo.Pledges(MemberIdentityID,PledgeType,PledgeAmount,PledgeDate)
VALUES(1,'Web','200','2011/08/10'),(2,'Web','300','2011/08/10'),(3,'Web','100','2011/08/10'),
(2,'Games','50','2011/9/20'),(3,'Games','60','2011/9/20');
INSERT INTO TitheAndOffering(MemberIdentityID,Offering,Tithe,SeedOffering,OfferingDate)
VALUES(1,'10','500','1000','2011/9/20'),(3,'400','600','0','2011/9/20');
SELECT LastName , SUM(PledgeAmount)PledgeTotalPerIndividual , sum(LTV)LTVTotalPerIndividual
, sum(RoR)RoRTotalPerIndividual , sum(Offering)OfferingTotalPerIndividual
, sum(Tithe)TitheTotalPerIndividual, sum(SeedOffering)SeedOfferingPerIndividual
FROM dbo.Members AS m
LEFT JOIN dbo.Pledges AS p ON m.MemberIdentityID = p.MemberIdentityID
LEFT JOIN dbo.Partnership AS ps ON m.MemberIdentityID = ps.MemberIdentityID
LEFT JOIN dbo.TitheAndOffering AS t ON m.MemberIdentityID = t.MemberIdentityID
GROUP BY LastName
October 26, 2011 at 6:47 am
The way I do this without preaggregating everything is with an outer apply.
Works reasonably fast here so I didn't bother trying to find faster way to access the data, so maybe there's one.
October 26, 2011 at 6:50 am
Your advice, I strongly believe is the way forward. But the business rules is to avoid NULL as much as possible hence multiple tables. At the same time they want to see the total donation per donor to their charity work. How do we go about this?
Thank for your candid statements
October 26, 2011 at 6:56 am
Ninja's_RGR'us (10/26/2011)
The way I do this without preaggregating everything is with an outer apply.Works reasonably fast here so I didn't bother trying to find faster way to access the data, so maybe there's one.
Could you share how you did pls?
October 26, 2011 at 7:03 am
SaintGr8 (10/26/2011)
Ninja's_RGR'us (10/26/2011)
The way I do this without preaggregating everything is with an outer apply.Works reasonably fast here so I didn't bother trying to find faster way to access the data, so maybe there's one.
Could you share how you did pls?
SELECT
O.name
, oaCols.Sum_MaxLength
FROM
sys.objects O
OUTER APPLY (
SELECT
SUM(max_length) AS Sum_MaxLength
FROM
sys.columns C
WHERE
C.object_id = O.object_id
) oaCols
ORDER BY
O.name
October 26, 2011 at 7:05 am
Simple inline sub-selects are one way to do it.
select
lastname,
(select sum(offerings)
from dbo.TitheAndOffering
where MemberIdentityID = Members.MemberIdentityID) as TotalOfferings
from dbo.Members;
Just add in sub-selects like that to get each value.
Otherwise, as Remi pointed out, Outer Apply is the way to go:
select
lastname,
TotalOfferings,
TotalTithes
from dbo.Members
outer apply
(select sum(offerings) as TotalOfferings, sum(Tithe) as TotalTithes
from dbo.TitheAndOffering
where MemeberIdentityID = Members.MemberIdentityID) as TandO;
Add in the rest of the columns you need, then add each sub-table in it's own Outer Apply sub-query.
Make sense?
It's not a good design, really, but it can be hacked around.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2011 at 7:36 am
Hello!
I've got the answer from another blog. Here it goes!
;WITH CTEPledgeAmount AS(
SELECT LastName , SUM(PledgeAmount)PledgeTotalPerIndividual
FROM dbo.Members AS m
LEFT JOIN dbo.Pledges AS p ON m.MemberIdentityID = p.MemberIdentityID
GROUP BY LastName), CTELTVTotalPerIndividual AS(
SELECT LastName , SUM(LTV)LTVTotalPerIndividual , SUM(RoR)RoRTotalPerIndividual
FROM dbo.Members AS m
LEFT JOIN dbo.Partnership AS ps ON m.MemberIdentityID = ps.MemberIdentityID
GROUP BY LastName), CTEOfferingTotalPerIndividual AS(
SELECT LastName , SUM(Offering)OfferingTotalPerIndividual, SUM(Tithe)TitheTotalPerIndividual, SUM(SeedOffering)SeedOfferingPerIndividual
FROM dbo.Members AS m
LEFT JOIN dbo.TitheAndOffering AS t ON m.MemberIdentityID = t.MemberIdentityID
GROUP BY LastName
)
SELECT *
FROM CTEPledgeAmount CPA
LEFT JOIN CTELTVTotalPerIndividual CLTV ON CLTV.LastName = CPA.LastName
LEFT JOIN CTEOfferingTotalPerIndividual CTOS ON CTOS.LastName = CPA.LastName
October 26, 2011 at 7:39 am
Check the performance of that VS 2 outer applies.
CTE can become costly real fast when not used correctly and I think your setup is not optimal (going to members more than once).
Moreover they're harder to debug than outer applies!
October 26, 2011 at 7:42 am
Here's the OUTER APPLY version Remi's talking about. It's cleaner, easier to read, and likely to be more performant:
SELECT m.LastName,
p.PledgeTotalPerIndividual,
ps.LTVTotalPerIndividual,
ps.RoRTotalPerIndividual,
t.OfferingTotalPerIndividual,
t.TitheTotalPerIndividual,
t.SeedOfferingPerIndividual
FROM dbo.Members AS m
OUTER APPLY (SELECT
PledgeTotalPerIndividual = SUM(PledgeAmount)
FROM dbo.Pledges
WHERE MemberIdentityID = m.MemberIdentityID) p
OUTER APPLY (SELECT
LTVTotalPerIndividual = SUM(LTV),
RoRTotalPerIndividual = SUM(RoR)
FROM dbo.Partnership
WHERE MemberIdentityID = m.MemberIdentityID) ps
OUTER APPLY (SELECT
OfferingTotalPerIndividual = SUM(Offering),
TitheTotalPerIndividual = SUM(Tithe),
SeedOfferingPerIndividual = SUM(SeedOffering)
FROM dbo.TitheAndOffering
WHERE MemberIdentityID = m.MemberIdentityID) t
ORDER BY m.LastName
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply