Here is complete explanation of what I want to do..
Hi I have two tables with values:
CREATE TABLE [Member] (
[memberID] [smallint] IDENTITY (1, 1) NOT NULL ,
[name] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED
(
[memberID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
values in the Member table:
memberID name
-------- ----------
1 Smith
2 John
3 Allen
4 Kate
CREATE TABLE [Contribution] (
[contID] [smallint] IDENTITY (1, 1) NOT NULL ,
[memberID] [smallint] NULL ,
[contribution] [decimal](18, 0) NULL ,
CONSTRAINT [PK_Contribution] PRIMARY KEY CLUSTERED
(
[contID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Contribution_Member] FOREIGN KEY
(
[memberID]
) REFERENCES [Member] (
[memberID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
values in Contribution Table:
contID memberID contribution
------ -------- --------------------
1 1 50
2 1 5
3 2 5
4 2 5
5 3 5
6 3 5
7 3 5
8 4 10
A Query: PayablesQuery
SELECT dbo.Member.name, SUM(dbo.Contribution.contribution) AS
[Total Contribution]
FROM dbo.Contribution INNER JOIN
dbo.Member ON dbo.Contribution.memberID =
dbo.Member.memberID
GROUP BY dbo.Member.name
name Total Contribution
---------- ----------------------------------------
Allen 15
John 10
Kate 10
Smith 55
Now I want to add extra calculated fields to PayablesQuery which are
based on the Total Contribution field.
The fields that I want are Difference, Remainder and Payables that
would have the values.
The values for this field is derived according to following rules
1) Difference: sum(Total Contribtion) - Total Contribution
i.e.
90-15 = 75
90-10 = 80
90-10 = 80
90-55 = 35
2) Remanider: sum(Difference) * 1/(no. of Members, in this case its
4)
3) Payables: Remainder - Difference
So the resultant query would look like this:
name Total Contribution Differnce Remainder
Payables
---------- -------------------- --------------- --------------
-------------------
Allen 15 75 67.5 -7.5
John 10 80 67.5 -12.5
Kate 10 80 67.5 -12.5
Smith 55 35 67.5 32.5
Thanks
I'm not sure which engine, so here's a script that should work in both
Access/Jet (ANSI-92 Query Mode) and SQL Server:
CREATE TABLE Member (
memberID SMALLINT NOT NULL,
name CHAR(10),
CONSTRAINT PK_Member PRIMARY KEY (memberID)
)
;
INSERT INTO Member (memberID, name) VALUES (1, 'Smith')
;
INSERT INTO Member (memberID, name) VALUES (2, 'John')
;
INSERT INTO Member (memberID, name) VALUES (3, 'Allen')
;
INSERT INTO Member (memberID, name) VALUES (4, 'Kate')
;
CREATE TABLE Contribution (
contID SMALLINT NOT NULL,
memberID SMALLINT,
contribution DECIMAL(18, 0),
CONSTRAINT PK_Contribution PRIMARY KEY (contID),
CONSTRAINT FK_Contribution_Member
FOREIGN KEY (memberID)
REFERENCES Member (memberID)
ON DELETE CASCADE
ON UPDATE CASCADE
)
;
INSERT INTO Contribution (contID, memberID, contribution) VALUES (1,
1, 50)
;
INSERT INTO Contribution (contID, memberID, contribution) VALUES (2,
1, 5)
;
INSERT INTO Contribution (contID, memberID, contribution) VALUES (3,
2, 5)
;
INSERT INTO Contribution (contID, memberID, contribution) VALUES (4,
2, 5)
;
INSERT INTO Contribution (contID, memberID, contribution) VALUES (5,
3, 5)
;
INSERT INTO Contribution (contID, memberID, contribution) VALUES (6,
3, 5)
;
INSERT INTO Contribution (contID, memberID, contribution) VALUES (7,
3, 5)
;
INSERT INTO Contribution (contID, memberID, contribution) VALUES (8,
4, 10)
;
CREATE VIEW MemberContributionTotal
AS
SELECT M1.memberID,
SUM(C1.contribution) AS [Total Contribution]
FROM Member AS M1
INNER JOIN Contribution AS C1
ON M1.memberID = C1.memberID
GROUP BY M1.memberID
;
CREATE VIEW MemberContributionDifference
AS
SELECT MCT1.memberID,
SUM(MCT2.[Total Contribution]) - MCT1.[Total Contribution] AS
Difference
FROM MemberContributionTotal AS MCT1,
MemberContributionTotal AS MCT2
GROUP BY MCT1.memberID, MCT1.[Total Contribution]
;
CREATE VIEW MemberPayable
AS
SELECT MCD1.memberID, MCD1.Difference,
SUM(MCD2.Difference) / COUNT(*) AS Remainder,
(SUM(MCD2.Difference) / COUNT(*)) - MCD1.Difference AS Payables
FROM MemberContributionDifference AS MCD1,
MemberContributionDifference AS MCD2
GROUP BY MCD1.memberID, MCD1.Difference
;
There's a problem with implicit casting in the above for SQL Server
e.g. some explicit casting to four decimal places for the final query:
SELECT MCD1.memberID, MCD1.Difference,
CAST(SUM(MCD2.Difference) / COUNT(*) AS DECIMAL(20, 4)) AS Remainder,
CAST(SUM(MCD2.Difference) / COUNT(*) AS DECIMAL(20, 4)) -
CAST(MCD1.Difference AS DECIMAL(20, 4)) AS Payables
FROM MemberContributionDifference AS MCD1,
MemberContributionDifference AS MCD2
GROUP BY MCD1.memberID, MCD1.Difference
;
I've assumed that by "no. of Members" in your Remainder calculation
you mean *contributing* members i.e. join of Member and Contribution,
rather than the cardinality of the Member table
Jamie.
--
