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 2008 Forums  Transact-SQL (2008)  Sql Query

Author  Topic  shaloo29 Starting Member 1 Post Posted - 2011-07-26 : 17:00:57 Hi All I need help in writing a sql query for the field Ratio. planid pptcode source tradedate amount ratio 10068 533013938 10 2/8/2002 84.92 84.92/sum(84.92+25.68) 10068 533013938 20 2/8/2002 25.48 25.84/sum(25.48+84.92) 10068 533013938 10 3/7/2002 78.62 (78.62+84.92)/sum(78.62+23.6+25.48+84.92) 10068 533013938 20 3/7/2002 23.6 (23.6+25.48)/sum(23.6+78.62+25.48+84.92) 10068 533013938 10 4/4/2002 86.56 (86.56+78.62+84.92)/sum(86.56+23.6+78.62+25.48+84.92+25.97) 10068 533013938 20 4/4/2002 25.97 (25.97+23.6+25.48)/sum(25.97+86.56+23.6+78.62+25.48+84.92) 10068 533097651 10 1/9/2004 417.17 sum of amounts for all 10s including this record irrespective of pptcode and dividing with the total sum <= to the sum of all the amounts for this planid 10068 533097651 10 3/1/2010 346.43 (sum of amounts for all 10s )/sumof all amounts irrespective of source 10068 533097651 25 3/1/2010 92.38 (92.38)/(sum(92.38 though 84.92)) 10068 533097651 10 3/31/2010 433.63 10068 533097651 20 3/31/2010 91.45 10068 533097651 10 5/3/2010 359.23 10068 533097651 10 6/1/2010 340.62 10068 533097651 10 6/29/2010 353.41 10068 533097651 10 7/30/2010 302.26 10068 533097651 10 9/2/2010 424.32 10068 533097651 10 10/4/2010 338.29 10068 533097651 10 10/27/2010 353.41 I tried doing like this, but not achieving the desired results CREATE TABLE ##TempRatio1 ( ID int ,PlanID int ,PPTCode int ,Source int ,TradeDate Date ,Amount Money ) CREATE TABLE ##TempRatio2 ( ID int ,PlanID int ,PPTCode int ,Source int ,TradeDate Date ,Amount Money ) Insert Into ##TempRatio1 Select TOP 100000 ID, PlanID,PPTCode,Source, convert(varchar(11), tradedate, 101) tradedate, Amount From my401.dbo.PPTArchDefr Where TradeDate IS NOT NULL AND Source <> 90 AND PlanID= 11956 Group By ID,planid, pptcode, source, tradedate,Amount Order By ID,planid, pptcode, tradedate, source,Amount Insert Into ##TempRatio2 Select TOP 100000 ID, PlanID,PPTCode,Source, convert(varchar(11), tradedate, 101) tradedate, Amount From my401.dbo.PPTArchDefr Where TradeDate IS NOT NULL AND Source <> 90 AND PlanID = 11956 Group By ID,planid, pptcode, source, tradedate,Amount Order By ID,planid, pptcode, tradedate, source,Amount Select * From ##TempRatio1 Select * From ##TempRatio2 Select a.planid ,a.pptcode ,a.source ,a.tradedate ,b.amount ,Case When b.tradedate <= a.tradedate and (a.planid = b.planid and a.source = b.source and a.pptcode= b.pptcode ) Then SUM(b.amount) over ( partition by a.planid, a.source, a.tradedate ) End As numerator ,SUM(b.amount) Over ( Partition By a.planid ,a.tradedate ) As denominator --,numerator/denominator as ratio From ##tempratio1 a INNER JOIN ##tempratio2 b On a.id= b.id Group By a.planid, b.PlanID ,a.PPTCode ,b.PPTCode ,a.Source ,b.Source,a.TradeDate ,b.TradeDate, b.amount Order By a.planid ,a.pptcode ,a.tradedate ,a.source =============================================================== Description for Ratio field is given below: Ratio ( Notes) : 1.Group by Trade Date and create a set of records for each Trade Date 2.For each Trade Date, Group by Source and sum all Records with a Trade Date that is less than or equal to the Trade Date the record is being created for. 3.Then Divide the Sum of the Source by the Total for all Sources with a Trade Date Less than or equal to the Trade Date you are creating the record for. Thanks in advance.