Example create function code / SQL Azure / that uses the IF ELSE statement to calculate dates between three parameters(referenced to fields) to determine an integer difference between dates – in this case whisky in a bond we are calculating number of days and the parameters are – date cask arrived in bond, date cask out of the bond and date cask is disgorged.
In English I am calculating number of days in a bond from the bond in date to todays date if there is no bond out date or disgorge date. If either of those dates exist the number of days in bond is the bond in date to the earlier of either of those dates.
I add in an extra day so that if a cask comes in on Tuesday and goes out the following days the number of days is counted as 2.
Clearly this is a nice structure that can be used in multiple contexts with different factors. I use the most common option in the first switch in the thought that this will speed things up on the server.
CREATE FUNCTION [dbo].[calculatedaysinbondcorrect] ( @datebondin Date, @datebondout Date, @datedisgorge Date ) RETURNS integer as BEGIN DECLARE @date1 DATE, @date2 DATE, @date3 DATE; DECLARE @daysinbond INT; SET @date1 = DATEADD(DAY,-1,@datebondin) SET @date2 = @datedisgorge SET @date3 = @datebondout IF ((@date2 is null) and (@date3 is null)) BEGIN SET @date2 = getdate() END ELSE If ((@date3 is null) and (@date2 is not null)) Begin Set @date2 = @datedisgorge END ELSE IF ((@date3 is not null) and (@date2 is null)) BEGIN SET @date2 = @datebondout END ELSE IF (@date2 > @date3) BEGIN SET @date2 = @datebondout END ELSE IF (@date2 < @date3) BEGIN SET @date2 = @datedisgorge END ELSE IF (@date2 = @date3) BEGIN SET @date2 = @datedisgorge END SELECT @daysinbond = DATEDIFF(dd, @date1, @date2) RETURN @daysinbond; END