Dynamic Columns using Coalesce with PHP & SQL

Question

Latest PHP under WAMPserver

SQL Server 2005

The user passes two variables to my .php report page, $thedate1 and $thedate2 which are a start and end datetime stamp for the values I want to retrieve from the database. I aggregate the data and pivot it on a SUM function to get the report I want, but I don't know how to include all the days inbetween the date range to appear as individual columns in the final report.

So if $thedate1='2012-02-20' and $thedate2='2012-02-28' I want to have a column for each day inbetween (inclusive) to be created dynamically in the final report. Currently I have to add the dates by hand into the query, but I'm hoping there's a way to add them in automatically.

Any ideas?

WITH T 
AS(
    SELECT CorrectionUser, CorrectionsCount, 
    DATEADD(dd, DATEDIFF(d, 0, DateHourCorrected), 0) as [DATE]
    FROM ISIImageCorrections
)
SELECT CorrectionUser AS [Employee], 
       COALESCE([2012-02-20], 0) AS [2012-02-20],
       COALESCE([2012-02-21], 0) AS [2012-02-21],
       COALESCE([2012-02-22], 0) AS [2012-02-22]
FROM T
PIVOT(SUM(CorrectionsCount) FOR [Date] IN([2012-02-20], [2012-02-21], [2012-02-22]))
AS P
ORDER BY CorrectionUser

Refer to these related questions: http://stackoverflow.com/questions/1439403/sql-server-dynamic-pivot-table-sql-injection, http://stackoverflow.com/questions/1827256/dynamic-pivot-in-sql-server-2005, http://stackoverflow.com/questions/6831364/dynamic-pivot-query-without-storing-query-as-string, http://stackoverflow.com/questions/7555968/dynamic-sql-server-2005-pivot — J Cooper, Feb 29 '12 at 14:52

score 0 · Accepted Answer · answered Apr 12 '12 at 10:40

Very easy approach. Hopefully I helped :)

if OBJECT_ID('tempdb..#t1') is not null DROP TABLE #t1;
CREATE TABLE #t1 (d date NOT NULL);

DECLARE @start date;
SET @start = '2012-04-05';
DECLARE @end date;
SET @end = '2012-04-25';
WHILE (@start <= @end)
begin
    INSERT INTO #t1 VALUES(DATEADD(day, 1, @start));
    SET @start = DATEADD(day, 1, @start);
end;

SELECT * FROM #t1;
DROP TABLE #t1

Dynamic Columns using Coalesce with PHP & SQL

1 Answers1