• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

update query Sql server 2005

I have the following table named SalesData

Shop  CurrSales  PriorSales    Sales Diff   SalesMiss  SalesNeg
1201   0                20000          -20000                
1202   10000        0                  10000
1203    25000      24000           1000
1204    32000      32200            -200

I am trying to write an update query that for each shop will do the following

      update SalesMiss with a Y if either    CurrSales or PriorSales    = 0.  Otherwise, should be set to  N
     
      update SalesNeg with A Y if  salesdiff is < 0.  Otherwise should be set to N  



Here is the result I am looking for


Shop  CurrSales  PriorSales    Sales Diff   SalesMiss  SalesNeg
1201   0                20000          -20000           Y              Y
1202   10000        0                  10000            Y               N    
1203    25000      24000           1000              N               N
1204    32000      32200            -200              N               Y



0
johnnyg123
Asked:
johnnyg123
  • 2
1 Solution
 
lluddenCommented:
Update SalesData
SET SalesMiss = CASE WHEN CurrSales = 0 OR PriorSales = 0 THEN 'Y' ELSE 'N' END,
SalesNeg = CASE WHEN SalesDiff < 0 THEN 'Y' ELSE 'N' END
0
 
brad2575Commented:
update sales data
set SalesMiss = CASE when currsales = 0 or priorsales = 0 then 'Y' ELSE 'N' END
SET SalesNeg = CASE WHEN salesdiff <0 THEN 'Y' ELSE 'N' END
0
 
brad2575Commented:
lludden beat me by a little but same code :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now