Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  help with query

Author  Topic  zuhx Starting Member 1 Post Posted - 2004-05-25 : 10:43:13 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;