daily pastebin goal
14%
SHARE
TWEET

Untitled

a guest Nov 18th, 2017 54 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Declare
  2.     @ReportDate as DATE
  3.     ,@month as DATE
  4.     ,@dayCounter as INT
  5.     ,@monthCounter as INT
  6.     ,@reportDateCurrentMonth as DATE
  7.  
  8.  
  9. SET @ReportDate = sysdatetime()
  10. SET @month = @ReportDate
  11. SET @dayCounter = DATEPART(DAY,@ReportDate)
  12. SET @monthCounter = '0'
  13. SET @reportDateCurrentMonth = DATEADD(month,@monthCounter,@ReportDate)
  14.  
  15. CREATE TABLE #DailyVolumes
  16.     (
  17.        contract_name varchar(50)
  18.        ,Volume INT
  19.        ,date_registered DATETIME
  20.     )
  21.  
  22. WHILE @month > DATEADD(month,-15,@ReportDate)
  23.     BEGIN
  24.  
  25.           WHILE @dayCounter > '0'
  26.  
  27.           BEGIN
  28.  
  29.                 INSERT INTO #DailyVolumes
  30.                 SELECT
  31.                 CONCAT('PREFIX-',DWRccn.contract_name) AS contract_name
  32.                     ,count(distinct(CONCAT(rtrim(p.xxxx_id), '-', rtrim(r.lis_req_id)))) AS Volume
  33.                     ,a.date_registered
  34.                 FROM accession a
  35.                     Left  join value1 P on a.value1_id = p.value1_id
  36.                     left  join [DataWarehouseReporting].[dbo].[DIM_contract_code_name] DWRccn on a.contract_code = DWRccn.contract_code
  37.                 where
  38.                     [date_registered] = @reportDateCurrentMonth
  39.                     AND a.lis_code = 'S'
  40.                     AND visit_type IN ('I', 'E')
  41.                 GROUP BY
  42.                     CONCAT('PREFIX-',DWRccn.contract_name)
  43.                     ,a.date_registered
  44.  
  45.  
  46.                 -- Set new values on Daily counter & Date to grab
  47.                 SET @dayCounter = @dayCounter - '1'
  48.                 SET @reportDateCurrentMonth = DATEADD(day,-1,@reportDateCurrentMonth)
  49.           END;
  50.  
  51.        SET @monthCounter = @monthCounter - '1'
  52.        SET @month = DATEADD(month,-1,@month)
  53.        SET @reportDateCurrentMonth = DATEADD(month,@monthCounter,@ReportDate)
  54.        SET @dayCounter = DATEPART(DAY,@ReportDate)
  55.  
  56.  
  57.  
  58.  
  59.     END;
  60.  
  61.  
  62.  
  63. -- SUM Daily Data into Monthly Slices
  64. Select
  65.     sum(Volume) AS Volume
  66.     ,contract_name
  67.     ,DATEADD(MONTH, DATEDIFF(MONTH, 0, date_registered), 0) AS MonthRegistered
  68. FROM #DailyVolumes
  69. group by
  70.     contract_name
  71.     ,DATEADD(MONTH, DATEDIFF(MONTH, 0, date_registered), 0)
  72. ORDER BY
  73.     MonthRegistered DESC
  74.     ,contract_name
  75. -- Clear Temp Table
  76. DROP TABLE #DailyVolumes
RAW Paste Data
Top