Define the Max - SQL Server Forums

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)

Define the Max

Author

Topic

mufasa
Yak Posting Veteran

62 Posts

Posted - 2004-01-22 : 14:51:03

OK here we go
I run a query that returns a value based on a couple of criteria's
I have this running OK, but I want the answer not to exceed a value based on the type of store that it is.

The maximum bonuses are in the dbo.BonusGrid.LYSalesBonus

Example: Bonus for store 001 type A through calculation is 112.25
Bonus for store 002 type A through calculation is 80.55
Bonus for store 003 type B through calculation is 112.25
Bonus for store 004 type B through calculation is 80.55

Store Type A maximum bonus is 100 and Store Type B is 200

The result should be: store 001 Bonus 100.00
Store 002 Bonus 80.55
Store 003 Bonus 112.55
Store 004 Bonus 80.55
Below is my code, I highlighted the part I am having trouble with :

SELECT dbo.[Com-PenStoreHourPayMonth].StoreId, dbo.[Com-PenStoreHourPayMonth].[Year], dbo.[Com-PenStoreHourPayMonth].[Month],
dbo.[Com-PenStoreHourPayMonth].Hours, dbo.[Com-PenStoreHourPayMonth].Payroll, dbo.[Com-Pen Sales].MTYSales,
dbo.[Com-Pen Sales].MTYBudget, dbo.[Com-Pen Sales].MLYSales, dbo.[Com-Pen Sales].MLYBudget,
dbo.[Com-PenStoreHourPayMonth].Payroll / dbo.[Com-Pen Sales].MTYSales AS [W/C],
dbo.[Com-Pen Sales].MTYSales / dbo.[Com-PenStoreHourPayMonth].Hours AS SPH,
(dbo.[Com-Pen Sales].MTYSales / NULLIF (dbo.[Com-Pen Sales].MLYSales, 0) - 1) * 100 AS IncreaseLY,
(dbo.[Com-Pen Sales].MTYSales / dbo.[Com-Pen Sales].MTYBudget - 1) * 100 AS [Budget%], dbo.StoreNew.CompType, dbo.StoreNew.DistrictMgr,
dbo.StoreNew.StoreA, dbo.StoreNew.Category, dbo.StoreNew.[group 3], dbo.StoreNew.Name, dbo.StoreNew.[Open Date],
dbo.[Com-Pen Sales].MTYSPH,
CASE WHEN dbo.[Com-Pen Sales].MTYSales > dbo.[Com-Pen Sales].MTYBudget THEN dbo.BonusGrid.AsstBudgetBonus ELSE 0 END AS AsstBudgetBonus,
CASE WHEN (dbo.[Com-Pen Sales].MTYSales / dbo.[Com-PenStoreHourPayMonth].Hours)
> dbo.[Com-Pen Sales].MTYSPH THEN dbo.BonusGrid.AsstSPHBonus ELSE 0 END AS AsstSPHBonus,

CASE WHEN (dbo.[Com-Pen Sales].MTYSales / NULLIF (dbo.[Com-Pen Sales].MLYSales, 0) - 1) >= .05 AND dbo.StoreNew.CompType = 'Comp' THEN (dbo.[Com-Pen Sales].MTYSales - (dbo.[Com-Pen Sales].MLYSales * 1.05)) * .02 ELSE 0 END AS SalesIncrease
FROM dbo.[Com-PenStoreHourPayMonth] INNER JOIN
dbo.[Com-Pen Sales] ON dbo.[Com-PenStoreHourPayMonth].StoreId = dbo.[Com-Pen Sales].MStore INNER JOIN
dbo.StoreNew ON dbo.[Com-Pen Sales].MStore = dbo.StoreNew.Store INNER JOIN
dbo.BonusGrid ON dbo.StoreNew.Category = dbo.BonusGrid.Category

Thanks
Mufasa

mufasa
Yak Posting Veteran

62 Posts

Posted - 2004-01-22 : 15:23:02

Ok I figured it out, I was hoping for a shorter way, but this is what I did

Case when (CASE WHEN (dbo.[Com-Pen Sales].MTYSales / NULLIF (dbo.[Com-Pen Sales].MLYSales, 0)- 1) >= .05
AND dbo.StoreNew.CompType = 'Comp'
THEN (dbo.[Com-Pen Sales].MTYSales - (dbo.[Com-Pen Sales].MLYSales * 1.05))* .02
ELSE 0 END)> dbo.BonusGrid.LYSalesBonus then dbo.BonusGrid.LYSalesBonus
else (CASE WHEN (dbo.[Com-Pen Sales].MTYSales / NULLIF (dbo.[Com-Pen Sales].MLYSales, 0) - 1) >= .05
AND dbo.StoreNew.CompType = 'Comp'
THEN (dbo.[Com-Pen Sales].MTYSales - (dbo.[Com-Pen Sales].MLYSales * 1.05)) * .02 ELSE 0 END)
end AS SalesIncrease

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources