daily pastebin goal
48%
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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top