Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Updating First Field Based on the First of Grouped Rows Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, June 13, 2013 12:47 PM
 Valued Member Group: General Forum Members Last Login: 2 days ago @ 6:51 AM Points: 51, Visits: 114
 I have the following table.Month EmpNum Prod intRate YTDRate1 1111 prd1 2 3.1232408142 1111 prd2 2 1.2053884623 1111 prd1 2 0.7891720694 1111 prd1 2 3.5661128845 1111 prd2 2 2.6691550991 2222 prd4 2 0.0543454622 2222 prd1 2 2.3697770673 2222 prd2 1.25 6.3458340074 2222 prd1 2 3.4960475615 2222 prd1 2 4.0542200566 2222 prd2 2 2.7600991612 3333 prd2 1.25 3.9345911853 3333 prd2 2 2.2208531364 3333 prd1 2 1.718195 3333 prd2 1.25 1.0837347476 3333 prd2 1.25 3.84453072I wrote the following query to find the first month in which YTDRate > intRate by Prod and by employee No.` SELECT MIN(t1.Trans_Month) as Trans_Month, t1.EmpNum, t1.Prod, t1.intRate, t1.YTDRate FROM Sales_Data t1 JOIN (SELECT Trans_Month, EmpNum, Prod, intRate, YTDRate FROM Sales_Data t2 where YTDRate > intRate GROUP BY Trans_Month, t2.EmpNum, Prod, intRate, YTDRate) t3 ON t3.EmpNum = t1.EmpNum AND t3.YTDRate = t1.YTDRate AND t3.intRate = t1.intRate AND t3.Prod = t1.Prod AND t3.Trans_Month = t1.Trans_MonthGROUP BY t1.Trans_Month, t1.EmpNum, t1.Prod, t1.YTDRate, t1.intRate`Now I want to update a field Status with text "Exceeds intRate". How do I modify the queyr to do the update?Any help would be greatly appreciated.Thanks
Post #1463261
 Posted Thursday, June 13, 2013 1:15 PM
 SSCommitted Group: General Forum Members Last Login: Yesterday @ 3:46 PM Points: 1,872, Visits: 4,130
 I'm not sure what you're trying to do, I might need some expected results based on the data you provided. To get better help, you should post DDL and consumable sample data. I'm helping you with this, but in order to help you with your problem, you must give more information.`DECLARE @Sales_Data TABLE( Trans_Month int, empnum int, prod char(4), intRate decimal(15,9), YTDRate decimal(15,9))INSERT @Sales_DataVALUES(1, 1111, 'prd1', 2, 3.123240814),(2, 1111, 'prd2', 2, 1.205388462),(3, 1111, 'prd1', 2, 0.789172069),(4, 1111, 'prd1', 2, 3.566112884),(5, 1111, 'prd2', 2, 2.669155099),(1, 2222, 'prd4', 2, 0.054345462),(2, 2222, 'prd1', 2, 2.369777067),(3, 2222, 'prd2', 1.25, 6.345834007),(4, 2222, 'prd1', 2, 3.496047561),(5, 2222, 'prd1', 2, 4.054220056),(6, 2222, 'prd2', 2, 2.760099161),(2, 3333, 'prd2', 1.25, 3.934591185),(3, 3333, 'prd2', 2, 2.220853136),(4, 3333, 'prd1', 2, 1.71819),(5, 3333, 'prd2', 1.25, 1.083734747),(6, 3333, 'prd2', 1.25, 3.84453072)SELECT * FROM @Sales_Data`Notice that the code you posted will give the same results without the aggregate. And basically the same results as your inner query. Luis C.Please don't trust me, test the solutions I give you before using them.Forum Etiquette: How to post data/code on a forum to get the best help
Post #1463275
 Posted Thursday, June 13, 2013 1:49 PM
 Valued Member Group: General Forum Members Last Login: 2 days ago @ 6:51 AM Points: 51, Visits: 114
 Thanks, what I want to do is for each employee and for each product i wan to find the first month where YTDRate > IntRate. If it is, update the status to "exceeds rate".So for empNum = 1111, product = prd1 the first month is 1 whereas for prd2, the first month is 5.For empNum = 2222, product = prd1 it is 2, but for prd2 it is 3and for empNum = 3333 and prd2 the first month is 2.For the above rows, status will be updated to "exceeds rate" but left null for other months even if ytdRate > intRate.
Post #1463291

 Permissions