May 24, 2004 at 2:10 pm
I have two tables (below) where I need to derive two queries from these tables. I'm a little unsure about the wording of the questions asked (below also), so I would appreciate any insight on this. Do you agree with my answers and if not, how do you interpret it and what would your queries look like? Much appreciated.
Ledger table Employee
Id (int, pk_identity) EmployeeId (int, pk_identity)
Postingdate smalldatetime Name_First (varchar)
Employeeid (int, fk_identity) Name_Last (varchar)
Ticker (varchar) Start_Date (smalldatetime)
Shares(int)
Market value (money)
Gain/Loss (money)
And I need to figure out the following:
a.)The daily cumulative Gain/Loss, year-to-date, for any employee with an overall loss of more than $10,000 during the same time period. Sort the recordset in descending order by amount of cumulative loss.
My answer:
SELECT employee.name_first+' '+employee.name_last AS Employee, ticker, sum(ledger.gainloss) AS GainLoss
FROM ledger, employee
WHERE Ledger.EmployeeId=Employee.EmployeeId And PostingDate>= '4/1/2003'
GROUP BY ticker, employee.name_first+' '+employee.name_last
HAVING sum(ledger.gainloss)<-10000
ORDER BY sum(ledger.gainloss) DESC , employee.name_first+' '+employee.name_last;
b.)The daily change in share-count and market value for each “winning” stock owned in 2004. Sort the final recordset by Ticker and Posting Date. Note: “Winning” = any stock with a cumulative gain for the period.
My answer:
SELECT Ledger.PostingDate, Employee.name_last, Ledger.ticker, sum(ledger.shares) AS Share_Count, sum(Ledger.MarketValue) AS MarketValue
FROM Ledger, Employee
WHERE Ledger.employeeid=Employee.employeeid And
Ledger.PostingDate>='1/1/2004'
GROUP BY Ledger.PostingDate, Employee.name_last, Ledger.ticker
HAVING sum(Ledger.MarketValue)>0
ORDER BY Ledger.ticker, Ledger.postingdate;
May 25, 2004 at 9:49 am
I would approach this from a slightly different perspective, specifically a data warehousing view. The problem becomes easier to solve if you split the problem space into two components. From a data warehousing perspective, you are really dealing with time-series data that is static after the specific time interval has passed.
You need to know the cumulative gain/loss year to date for any arbitrary date. You also need to know the daily change in share-count and market value for each "winning" stock. The values will not change after the day has passed, so you can easily create either a computed column or a static column for the values. Once the values are stored, you can then write two simple queries to retrieve and sort the desired values.
This approach has the added benefit of supporting different queries when the threshhold changes from 10,000 to 5,000, for example.
I suppose the principal here is to keep things as simple as possible. Rather than trying to write a complex query, leverage the strength of the database and precompute data where possible. The queries then become simpler and presumably faster.
Hope this helps
Wayne
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply