R
Rex
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
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