1:  DECLARE @ParamFromDate datetime
   2:  DECLARE @ParamToDate datetime
   3:  DECLARE @ParamQueues varchar(50)
   4:  DECLARE @CurrentDate DATETIME
   5:  DECLARE @L_Start varchar(50)
   6:  DECLARE @L_End varchar(50)
   7:  DECLARE @LSQL as varchar(1500)
   8:  DECLARE @LCOL as varchar(50)
   9:  DECLARE @TableName1 varchar(50)
  10:  DECLARE @TableName2 varchar(50)
  11:   
  12:  SET @TableName1 = 'dbo.utbl_' + replace(convert(varchar(36),newid()),'-','')
  13:  SET @TableName2 = 'dbo.utbl_' + replace(convert(varchar(36),newid()),'-','')
  14:  SET @CurrentDate = GETDATE()
  15:   
  16:  SET @ParamFromDate = ISNULL(@ParamFromDate, Convert(datetime, Convert(varchar(10), @CurrentDate, 101)))
  17:  SET @ParamToDate = ISNULL(@ParamToDate, Convert(datetime, Convert(varchar(10), @CurrentDate, 101))) + 1
  18:   
  19:  SET @L_Start = Convert(varchar(50), @ParamFromDate)
  20:  SET @L_End = Convert(varchar(50), @ParamToDate)
  21:  SET @LSQL = ''
  22:   
  23:  -- This get the columns for the Pivot
  24:  DECLARE csrStatus CURSOR FOR
  25:  select fs.CodeData
  26:  from ActiveInterface.dbo.vFaxStatus fs
  27:  WHERE CODEDATA not in ('ALLSTATUS', 'SENT', 'FAILED', 'CANCELED', 'MODIFIED')
  28:  ORDER By SortOrder
  29:   
  30:  open csrStatus
  31:   
  32:  FETCH NEXT FROM csrStatus
  33:  INTO @LCOL
  34:   
  35:  WHILE @@FETCH_STATUS = 0
  36:  BEGIN
  37:  SET @LSQL = @LSQL + ', 0 [' + @LCOL + ']'
  38:  FETCH NEXT FROM csrStatus
  39:  INTO @LCOL
  40:  END
  41:  CLOSE csrStatus
  42:  DEALLOCATE csrStatus
  43:   
  44:  -- Now create an dummy table with the list of pivot columns
  45:  SET @LSQL = 'select DISTINCT QueueName, WorkQID, SortOrder' + @LSQL + '
  46:  into ' + @TableName1 + '
  47:  from ActiveInterface.dbo.vFaxWorkQueue wq
  48:  where FacilityCode = ''WIS'''
  49:  if @ParamQueues is not null
  50:  SET @LSQL = @LSQL + ' [WorkQID] in (' + @ParamQueues + ')'
  51:  SET @LSQL = @LSQL + '
  52:  ORDER By SortOrder'
  53:   
  54:  EXEC (@LSQL)
  55:   
  56:  -- Create a summary table of the values
  57:  SET @LSQL = 'select rq.workqid, vfs.codedata, count(*) [Count] 
  58:  into ' + @TableName2 + '
  59:  from ActiveFAX.dbo.faxes f
  60:  inner join ActiveFAX.dbo.reviewqueue rq on rq.faxid = f.faxid
  61:  inner join ActiveFAX.dbo.faxstatus fs on fs.faxstatusid = f.faxstatusid
  62:  inner join ActiveInterface.dbo.vfaxstatus vfs on vfs.codeid = fs.statusid
  63:  where (f.DateCreated between ''' + @L_Start + ''' AND ''' + @L_End + ''')
  64:  group by rq.workqid, vfs.codedata
  65:  order by 1, 3, 2'
  66:   
  67:  print @LSQL
  68:  EXEC (@LSQL)
  69:   
  70:  -- Now do some real magic, create a dynamic SQL that loops through the summary table rows
  71:  -- updating the dummy table with the list of pivot columns
  72:  SET @LSQL = '
  73:  DECLARE @LQID as varchar(50)
  74:  DECLARE @LFS as varchar(50)
  75:  DECLARE @LCNT as varchar(50)
  76:  DECLARE @LSQL2 as varchar(500)
  77:   
  78:  DECLARE csrCounts CURSOR FOR
  79:  select WorkQID, CodeData, Count
  80:  from ' + @TABLENAME2 + '
  81:  ORDER By 1, 2
  82:   
  83:  open csrCounts
  84:   
  85:  FETCH NEXT FROM csrCounts
  86:  INTO @LQID, @LFS, @LCNT
  87:   
  88:  WHILE @@FETCH_STATUS = 0
  89:  BEGIN
  90:  SET @LSQL2 = ''UPDATE ' + @TableName1 
  91:  SET @LSQL = @LSQL + ' SET ['' + @LFS + '']'
  92:  SET @LSQL = @LSQL + ' = '' + @LCNT + '' WHERE WorkQID = '' + @LQID + '''''
  93:  SET @LSQL = @LSQL + '
  94:  --PRINT @LSQL2
  95:  EXEC (@LSQL2)
  96:  FETCH NEXT FROM csrCounts
  97:  INTO @LQID, @LFS, @LCNT
  98:  END
  99:  CLOSE csrCounts
 100:  DEALLOCATE csrCounts'
 101:   
 102:  -- PRINT @LSQL
 103:  EXEC (@LSQL)
 104:  EXEC ('select * from ' + @TableName1)
 105:   
 106:  exec ('drop table ' + @TableName1)
 107:  exec ('drop table ' + @TableName2)