Using functions in sql query

Question

I structured my sql table like this:

ItemID   Price   MaxPeople   CalculationUnit
1        10      4           people/item
2        70      2           item
3        30      8           week/item
4        50      2           week

etc.

Now I would like to run a basic stored procedure that looks something like

sp_return_items_total
@Days as int,
@Items as int
AS
select itemid, price, total from table

Total would be a value based on this calculation (number of days * calculation unit * price).

For example for @Days = 5 and '@Items = 2' results would be:

1, 10, 400  (10 * 4 people * 2 items * 5 days)
2, 70, 140 (70 * 1 * 2 items * 1)
3, 30, 42.85 (30 * 1 * 2 items * 5/7 days)
4, 50, 35.71 (50 * 1 * 1 items * 5/7 days)

I am trying to find a solution, how to get the total value based on the sp parameters and calculation unit.

Thanks for participating

One of the challenges to writing a query that produces the desired results is that the first two result rows have three columns wihle the last two result rows have four. Most queries return the same number of columns for each row. — HABO
– HABO, Commented Feb 6, 2012 at 15:56
@user92546 looks like those commas without trailing space are decimal separators (check the calculation)... — Valentino Vranken
– Valentino Vranken, Commented Feb 7, 2012 at 15:51

HABO · Accepted Answer · 2012-02-11 03:15:18Z

EDIT 2/10/12: Revised query to correspond to revised sample output:

-- Set up the test data. 
declare @AmalgamatedStuff as table ( ItemId int, Price int, MaxPeople int, CalculationUnit varchar(16) ) 
insert into @AmalgamatedStuff ( ItemId, Price, MaxPeople, CalculationUnit ) values 
  ( 1, 10, 4, 'people/item' ), 
  ( 2, 70, 2, 'item' ), 
  ( 3, 30, 8, 'week/item' ), 
  ( 4, 50, 2, 'week' ) 

-- Stored procedure parameters. 
declare @Days as int = 5 
declare @Items as int = 2 

-- The query. 
select ItemId, Price, 
  case CalculationUnit 
    when 'item' then Price * @Items
    when 'people/item' then Price * MaxPeople * @Items * @Days 
    when 'week' then round( Price * @Days / 7.0, 2 )
    when 'week/item' then round( Price * @Items * @Days / 7.0, 2 )
    else NULL 
    end as Total 
  from @AmalgamatedStuff

Note that 42.857142 fails to round to 42.85 in the third result row.

EDIT: Bearing in mind that the suggested results with a variable number of columns and unspecified calculations cannot be obtained:

-- Set up the test data.
declare @AmalgamatedStuff as table ( ItemId int, Price int, MaxPeople int, CalculationUnit varchar(16) )
insert into @AmalgamatedStuff ( ItemId, Price, MaxPeople, CalculationUnit ) values
  ( 1, 10, 4, 'people/item' ),
  ( 2, 70, 2, 'item' ),
  ( 3, 30, 8, 'week/item' ),
  ( 4, 50, 2, 'week' )

-- Stored procedure parameters.
declare @Days as int = 5
declare @Items as int = 2

-- The query, give or take the correct calculations.
declare @SpuriousFactorToGetSuggestedResult as int = 2  
select ItemId, Price,
  case CalculationUnit
    when 'item' then Price * @Items
    when 'people/item' then Price * MaxPeople * @Items * @Days
    when 'week' then Price * @Items * @Days / 7
    when 'week/item' then Price * @Items * @Days * @SpuriousFactorToGetSuggestedResult / 7
    else NULL
    end as Total
  from @AmalgamatedStuff

Actually getting the query into a stored procedure is left as an exercise for the OP.

The "design" remains somewhere below rancid, and festering ever faster.

EDIT: Answer to an earlier edit of the "question" remains below:

You can do things using a CASE like:

select ItemId, Price,
  case
    when CalculationUnit = 'day' then @Days * Price
    when CalculationUnit = 'week' then @Days / 7 * Price
    else NULL
    end as 'Total'
  from MyIllConceivedTable

As previously noted, it's a bad design.

In some cases it might make sense to have a lookup table, e.g. something that lets you map various units of measure to some common base. Think weights and their gram equivalents. (Also a handy place for storing the full name "Ounces" and abbreviation "Oz", ... .) Your data table would contain references to the units table.

In some cases it may make sense with units of time. Scheduled events might recur daily, weekly, monthly, quarterly and annually. The lengths of the units are somewhat flexible, and the uses tend to be peculiar. (I have a lunch on the 3rd Wednesday of each month. See you there?)

Regarding performance, calculations that return results aren't bad. You could use a computed column or a view to achieve your (nefarious) end. Performance takes a hit when you have functions called for each row, e.g. a WHERE clause that converts a DATETIME column to a string and uses LIKE to determine if there is an 'R' in the string.

Whatever you choose, please don't use anything as daffy as:

declare @Today as Date
set @Today = SysDateTime()
select @Today,
  DateDiff(day, @Today, DateAdd( "day", 1, @Today ) ) as 'Days in a Day',
  DateDiff(day, @Today, DateAdd( "week", 1, @Today ) ) as 'Days in a Week',
  DateDiff(day, @Today, DateAdd( "month", 1, @Today ) ) as 'Days in a Month' -- Sometimes!

Adam Robinson · Accepted Answer · 2012-01-22 21:32:28Z

4

Yes. The alternative (and good database design) is to store the calculation unit as a coefficient of some unit. In other words, if the options are day and week, then store the number of days (meaning you'd store 1 for any row that currently says day and 7 for any row that currently says week) so that you can use that in your calculation.

answered Jan 22, 2012 at 21:32

Adam Robinson

186k36 gold badges294 silver badges351 bronze badges

Comments

marc_s · Accepted Answer · 2012-01-22 21:33:34Z

1

Using stored functions that have data access "hidden" inside them, yes, that can drastically decrease your performance (mostly due to the fact they might be executed much more often than you'd expect, at first sight).

Functions that mostly do calculations (like it probably would be here) shouldn't be a major problem.

answered Jan 22, 2012 at 21:33

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Collectives™ on Stack Overflow

Using functions in sql query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related