May 21, 2010 at 12:59 pm
Table to populate:
Summary Table:
MinAge,MaxAge,CountPersons,AmountToPay
0,10,0,0
11,20,0,0
21,30,0,0
31,40,0,0
Transaction Table:
Age,PersonID,Name,DollarValue
9,1000,'Joe Toe',$50.00
10,1001,'Jane Toe',$55.00
13,1002,'Billy Bob',$90.00
25,1003,'Jake Snake',$45.00
How do I take the transaction table and then inserting the proper values into it's respective age categories into the Summary Table?
So in this example the end result would be:
MinAge,MaxAge,CountPersons,AmountToPay
0,10,2,1,$105.00
11,20,1,$90.00
21,30,1,$45.00
31,40,0,0
May 22, 2010 at 3:55 am
CREATE TABLE dbo.Person
(
person_id INTEGER NOT NULL PRIMARY KEY,
name NVARCHAR(30) NOT NULL,
birth_date DATETIME NOT NULL
CONSTRAINT [CK dbo.Person birth_date 19000101 -> now]
CHECK (birth_date BETWEEN '19000101' AND CURRENT_TIMESTAMP),
age AS
(
CONVERT(INTEGER, CONVERT(CHAR(8), GETDATE(), 112)) -
CONVERT(INTEGER, CONVERT(CHAR(8), birth_date, 112))
) / 10000
);
CREATE TABLE dbo.Transactions
(
transaction_id INTEGER NOT NULL IDENTITY PRIMARY KEY,
person_id INTEGER NOT NULL
REFERENCES dbo.Person,
dollar_value MONEY NOT NULL
);
CREATE TABLE dbo.AgeRange
(
range_id INTEGER NOT NULL IDENTITY PRIMARY KEY,
lower_bound TINYINT NOT NULL,
upper_bound TINYINT NOT NULL,
CONSTRAINT [CK dbo.AgeRange upper >= lower bound]
CHECK (upper_bound >= lower_bound)
);
INSERT dbo.Person (person_id, name, birth_date) VALUES (1000, N'Joe Toe', '20010501');
INSERT dbo.Person (person_id, name, birth_date) VALUES (1001, N'Jane Toe', '20000501');
INSERT dbo.Person (person_id, name, birth_date) VALUES (1002, N'Billy Bob', '19970501');
INSERT dbo.Person (person_id, name, birth_date) VALUES (1003, N'Jake Snake', '19850501');
INSERT dbo.Transactions (person_id, dollar_value) VALUES (1000, $50.00);
INSERT dbo.Transactions (person_id, dollar_value) VALUES (1001, $55.00);
INSERT dbo.Transactions (person_id, dollar_value) VALUES (1002, $90.00);
INSERT dbo.Transactions (person_id, dollar_value) VALUES (1003, $45.00);
INSERT dbo.AgeRange (lower_bound, upper_bound) VALUES (00, 10);
INSERT dbo.AgeRange (lower_bound, upper_bound) VALUES (11, 20);
INSERT dbo.AgeRange (lower_bound, upper_bound) VALUES (21, 30);
INSERT dbo.AgeRange (lower_bound, upper_bound) VALUES (31, 40);
GO
SELECT R.lower_bound,
R.upper_bound,
person_count = COUNT_BIG(P.person_id),
amount_due = ISNULL(SUM(T.dollar_value), $0.00)
FROM dbo.AgeRange R
LEFT
JOIN dbo.Transactions T
JOIN dbo.Person P
ON P.person_id = T.person_id
ON P.age BETWEEN R.lower_bound AND R.upper_bound
GROUP BY
R.lower_bound,
R.upper_bound
ORDER BY
R.lower_bound,
R.upper_bound;
GO
DROP TABLE dbo.AgeRange;
DROP TABLE dbo.Transactions;
DROP TABLE dbo.Person;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply