Dynamic Count ModifiedDate Rows


  
   --*********begin of sample table and data
 create table LZO_a (id int, MODIFIEDAT date)
insert into LZO_a values (1, '2019-03-01'),(2, '2019-03-02'),(3, '2019-03-03'),(4, '2019-03-04'),(5, '2019-03-05')
,(6, '2019-03-02'),(7, '2019-03-03'),(8, '2019-03-04'),(9, '2019-03-05'),(10, '2019-03-12'),(11, '2019-03-13'),(12, '2019-03-14') 

create table LZO_b (id int, MODIFIEDAT date)


insert into LZO_b values (1, '2019-03-01'),(2, '2019-03-01'),(3, '2019-03-03'),(4, '2019-03-04'),(5, '2019-03-05')
,(6, '2019-03-03'),(7, '2019-03-03'),(8, '2019-03-04'),(9, '2019-03-05'),(10, '2019-03-05'),(11, '2019-03-05'),(12, '2019-03-14') 

 --************end of sample table and data

 


DECLARE @RunDate date = '2019-03-02' --change date parameter
 
DECLARE @sql1 nvarchar(4000)=N'' 
DECLARE @ColsPivot as NVarchar(4000)=null




 Select @sql1= 'Select tname,  cname , count(*) RowCnt into tmp_BUILDMODIFIED FROM ('+  Stuff((
SELECT  ' UNION ALL '+ char(13) + char(10)  + 'Select    ''' + quotename(t.[TABLE_SCHEMA]) + '.' + quotename(t.[TABLE_NAME] ) 
+ ''' as tname, '  + quotename(c.COLUMN_NAME) +'  as cname, MODIFIEDAT   FROM   '
+ quotename(t.[TABLE_SCHEMA]) + '.' + quotename(t.[TABLE_NAME]) + ' WHERE 1=1 and MODIFIEDAT >='''  + CONVERT(NVARCHAR(10),@RunDate)+''''
 + char(13) + char(10)  
 FROM [INFORMATION_SCHEMA].[TABLES]  t join [INFORMATION_SCHEMA].[COLUMNS] c 
  on t.[TABLE_SCHEMA] =c.[TABLE_SCHEMA] and t.[TABLE_NAME]=c.[TABLE_NAME]  
  WHERE TABLE_TYPE='BASE TABLE' and [DATA_TYPE] Like '%date%' and t.[TABLE_NAME] like'LZO_%' 
  and   t.[TABLE_NAME] NOT LIKE '%DJW%' AND t.[TABLE_NAME] NOT LIKE 'LZO_TRUST%'
  and c.COLUMN_NAME='MODIFIEDAT'
FOR XML PATH(''), type ).value('.', 'varchar(max)'),1,10,'')
   + ' ) t  group by tname,cname'

--create the temp table
EXEC sp_executesql @sql1 
 
 
Select @ColsPivot =N' Select tname,   '+STUFF( (SELECT distinct ',' + '  sum(CASE WHEN cname=' + quotename(cname,'''') + ' THEN RowCnt else 0 end ) as ' + quotename(cname,'[')  + char(10)+char(13)
                             FROM tmp_BUILDMODIFIED
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
							+ ' from tmp_BUILDMODIFIED group by tname '

--print @ColsPivot
EXEC sp_executesql @ColsPivot 

--clean up
IF OBJECT_ID('dbo.tmp_BUILDMODIFIED','U') IS NOT NULL 
DROP TABLE tmp_BUILDMODIFIED


 --drop sample tables
  drop table LZO_b,LZO_a

 

 
 
 

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/aa0c4f82-cfd6-4922-9f64-4985fedf8a4c/how-do-i-pivot-date-rows-into-columns?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s