Pfthb

I have a simple query that works well, but I have a need to run it varying the conditional 'where' statement for each of 12 months * several years. I would like to produce 12 columns, each representing a count through that month; it would also be acceptable if this data were to be somehow represented in rows.

Current query:

SELECT 
 field1
 ,field2
 ,field3
 ,count(distinct(table1.field4)) 

 FROM table1 
 inner join table2 
 on table1.field4=table2.field4
 where year_month <=201808

group by 
 field1
 ,field2
 ,field3

order by 
 field1
 ,field2
 ,field3

there you go :

DECLARE @year INT,
 @i INT = 1,
 @date VARCHAR(6),
 @month INT = 1,
 @months INT = 12,
 @sql NVARCHAR(MAX)

DECLARE @years TABLE (
 Id INT IDENTITY,
 myYear INT
)

INSERT INTO @years (myYear)
VALUES (2016),
 (2017),
 (2018)

CREATE TABLE #table (
 field1 [your data type],
 field2 [your data type],
 field3 [your data type],
 myDate VARCHAR(6),
 myCount INT
)

WHILE @i <= (SELECT MAX(Id) FROM @years)
BEGIN
 SELECT @year = myYear
 FROM @years
 WHERE Id = @i

 WHILE @month <= @months
 BEGIN
 IF @month < 10
 SET @date = @year + '0' + CAST(@month as VARCHAR(2))
 ELSE
 SET @date = @year + CAST(@month as VARCHAR(2))

 SET @sql = 'INSERT INTO #table SELECT field1 ,field2 ,field3, year_month as myDate, count(distinct(table1.field4)) as myCount 
 FROM table1 JOIN table2 ON table1.field4 = table2.field4
 WHERE year_month = ''' + CAST(@date AS NVARCHAR(MAX)) + ''' GROUP BY field1, field2, field3, year_month ORDER BY year_month, field1, field2, field3'

 EXECUTE sp_executesql @sql

 SET @month = @month + 1
 END

 SET @month = 1
 SET @i = @i + 1
END

SELECT * FROM #table

DROP TABLE #table