Write the script that will enable you to create the new values for each of the measures listed above. The financial year end is 28 February 2011

• engstevo

  SSC-Addicted

  Points: 488

  More actions

  July 3, 2013 at 3:26 am

  #395079

  5.You are only given the transaction date in your transactions table and your client requires you to be able to show them their data by Financial Year, Quarter, Month, Week and Week Day

  Write the script that will enable you to create the new values for each of the measures listed above. The financial year end is 28 February 2011

  Then confirm the Financial Year, Quarter, Month, Week and Weekday.

  R/

  CREATE PROCEDURE FinYear2012

  @Cost_date date = '2011-02-15 00:00:00.0000000'

  AS

  BEGIN

  DECLARE @Fin_year int

  DECLARE @Fin_quarter int

  DECLARE @fin_Month int

  DECLARE @Fin_Week int

  DECLARE @Weekday Varchar (25)

  SELECT

  Mydate

  , CASE

  WHEN (Mydate) BETWEEN '2010-02-28 ' AND '2010-05-28 ' THEN 'First Quarter'

  WHEN (Mydate) BETWEEN '2010-05-28 ' AND '2010-08-28 ' THEN 'Second Quarter'

  WHEN (Mydate) BETWEEN '2010-08-28 ' AND '2010-12-28 ' THEN 'Third Quarter'

  WHEN (Mydate) BETWEEN '2010-12-28 ' AND '2011-02-28 ' THEN 'Fourth Quarter'

  END AS FiscalYear

  FROM Transactions

  set @Fin_year = DATEDIFF(year, '2010-02-28 23:59:59.9999999'

  , '2011-02-28 00:00:00.0000000');

  set @Fin_quarter = DATEDIFF(quarter, '2010-02-28 23:59:59.9999999'

  , '2011-02-28 00:00:00.0000000');

  set @fin_Month = DATEDIFF(MONTH, '2010-02-28 23:59:59.9999999'

  , '2011-02-28 00:00:00.0000000');

  set @Fin_Week = DATEDIFF(WEEK, '2010-02-28 23:59:59.9999999'

  , '2011-02-28 00:00:00.0000000');

  set @Weekday = DATEDIFF(WEEKDAY, '2010-02-28 23:59:59.9999999'

  , '2011-02-28 00:00:00.0000000');

  SELECT @Fin_year as FinancialYr ,@Fin_quarter as FinancialQtr , @fin_Month as FinancialMnth , @Weekday as Wekkeday

  END

  GO