Managed to get it to work, below is the answer. Thanks to everyone that replied.
;with cte as(
select [CAB Part No.]
,[RFT/Scrap/Rework]
,[Process No.]
,[Process]
,[Date & Time]
,[Quantity]
,cast([Date & Time] as date) UtcDay
,DATEPART(hour, [Date & Time]) UtcHour
from [SFDLOG]
where [Date & Time] between '2015-02-1' and '2015-02-2'
)
select [CAB Part No.]
,[RFT/Scrap/Rework]
,[Process No.]
,[Process]
,[Quantity]
,dateadd(hour, utchour, cast(utcday as datetime)) as [SFD Hour]
into #TempDates
from cte
select *
from
(
select [Quantity]
,[SFD Hour]
,[CAB Part No.]
,[Process No.]
,[Process]
,[RFT/Scrap/Rework]
from #tempDates
) x
pivot
(
count([Quantity])
for [SFD Hour]
In ([2015-02-01 00:00:00:000],[2015-02-01 01:00:00:000],[2015-02-01 02:00:00:000],[2015-02-01 03:00:00:000],[2015-02-01 04:00:00:000],[2015-02-01 05:00:00:000],[2015-02-01 06:00:00:000]
,[2015-02-01 07:00:00:000],[2015-02-01 08:00:00:000],[2015-02-01 09:00:00:000],[2015-02-01 10:00:00:000],[2015-02-01 11:00:00:000],[2015-02-01 12:00:00:000],[2015-02-01 13:00:00:000]
,[2015-02-01 14:00:00:000],[2015-02-01 15:00:00:000],[2015-02-01 16:00:00:000],[2015-02-01 17:00:00:000],[2015-02-01 18:00:00:000],[2015-02-01 19:00:00:000],[2015-02-01 20:00:00:000]
,[2015-02-01 21:00:00:000],[2015-02-01 22:00:00:000],[2015-02-01 23:00:00:000])
)p
Order By [CAB Part No.],[Process No.]
drop table #tempdates
This is a bit messy to get the final result because you have multiple SCHEMA_VER for each date. Before I demonstrate how to do this with dynamic SQL, I'll first show how to do it with static code to get the logic correct. In order to get the final result you can utilize both pivot and unpivot.
But first, I'd change your original query to use the following:
select
row_number() over (
partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_MAJOR, SCHEMA_VER_MINOR, SCHEMA_VER_SUB
) as minrownum
, row_number() over (
partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_MAJOR desc, SCHEMA_VER_MINOR desc, SCHEMA_VER_SUB desc
) as maxrownum
,CONVERT(varchar(10), SCHEMA_VER_DATE, 110) as UPG_DATE
,CONVERT(varchar(1), SCHEMA_VER_MAJOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_MINOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_SUB) as SCHEMA_VER
from audit_schema_version
where SCHEMA_VER_REMARK like 'Stored procedure build';
See SQL Fiddle with Demo. I used row_number() to get the first and the last SCHEMA_VER for each date. This is needed so you can concatenate only those values together for the comment.
Then I would use a temp table to store the rows that have a minrownum and maxrownum of 1. The temp table would contain the upg_date and the comment. This comment column contains a concatenated string of the pair of the SCHEMA_VER for each date.
create table #srcData
(
upg_date varchar(10),
comment varchar(500)
);
The code to populate the temp table would then be:
;with cte as
(
select
row_number() over (
partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_MAJOR, SCHEMA_VER_MINOR, SCHEMA_VER_SUB
) as minrownum
, row_number() over (
partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_MAJOR desc, SCHEMA_VER_MINOR desc, SCHEMA_VER_SUB desc
) as maxrownum
,CONVERT(varchar(10), SCHEMA_VER_DATE, 110) as UPG_DATE
,CONVERT(varchar(1), SCHEMA_VER_MAJOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_MINOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_SUB) as SCHEMA_VER
from audit_schema_version
where SCHEMA_VER_REMARK like 'Stored procedure build'
)
insert into #srcData
select distinct
c1.UPG_DATE,
comment
= STUFF((
SELECT ' - ' + c2.SCHEMA_VER
FROM cte c2
WHERE (c2.minrownum = 1 or c2.maxrownum = 1)
and c1.upg_date = c2.upg_date
order by c2.minrownum
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
from cte c1
where c1.minrownum = 1 or c1.maxrownum = 1;
This first pass through your data gets you:
| upg_date | comment |
|------------|-------------------|
| 03-23-2015 | 2.13.0 |
| 04-05-2013 | 1.6.13 - 1.16.13 |
| 04-17-2014 | 1.16.13 - 2.7.0 |
| 12-09-2014 | 2.8.0 - 2.12.0 |
Now you still need to get a count of each date for the year and the full concatenated comment. This would be where unpivot comes into play. You could use the following code to create the full comment for each year and to get the count.
select distinct
Yr = right(s1.upg_date, 4),
cnt = count(*) over(partition by right(s1.upg_date, 4)),
fullcomment
= STUFF((
SELECT '; ' + s2.comment
FROM #srcData s2
WHERE right(s1.upg_date, 4) = right(s2.upg_date, 4)
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
from #srcData s1;
See SQL Fiddle with Demo. The data now looks like:
| Yr | cnt | fullcomment |
|------|-----|-----------------------------------|
| 2013 | 1 | 1.6.13 - 1.16.13 |
| 2014 | 2 | 1.16.13 - 2.7.0; 2.8.0 - 2.12.0 |
| 2015 | 1 | 2.13.0 |
As you can see you've got multiple columns that need to be pivoted, so you can unpivot both the fullcomment and cnt column into multiple rows. This can be done using the UNPIVOT function or CROSS APPLY. I'd prefer cross apply here because you'll want to concatenate values together to create the new column names:
;with cte as
(
select distinct
Yr = right(s1.upg_date, 4),
cnt = count(*) over(partition by right(s1.upg_date, 4)),
fullcomment
= STUFF((
SELECT '; ' + s2.comment
FROM #srcData s2
WHERE right(s1.upg_date, 4) = right(s2.upg_date, 4)
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
from #srcData s1
)
select [2015], [2015_comment], [2014], [2014_comment], [2013], [2013_comment]
from
(
select c.col, val
from cte d
cross apply
(
values
(Yr, cast(cnt as nvarchar(50))),
(Yr+'_comment', fullcomment)
) c (col, val)
) d
pivot
(
max(val)
for col in ([2015], [2015_comment], [2014], [2014_comment], [2013], [2013_comment])
) piv;
See SQL Fiddle with Demo.
Once you've got the logic, you can easily convert this to dynamic SQL.
-- get list of the columns
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(col)
from #srcData
cross apply
(
select right(upg_date, 4), right(upg_date, 4), 2 union all
select right(upg_date, 4), right(upg_date, 4)+'_comment', 1
) c (yr, col, so)
group by yr, col, so
order by yr desc, so desc
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'SELECT ' + @cols + '
from
(
select c.col, val
from
(
select distinct
Yr = right(s1.upg_date, 4),
cnt = count(*) over(partition by right(s1.upg_date, 4)),
fullcomment
= STUFF((
SELECT ''; '' + s2.comment
FROM #srcData s2
WHERE right(s1.upg_date, 4) = right(s2.upg_date, 4)
FOR XML PATH(''''), TYPE).value(''.[1]'', ''nvarchar(max)''), 1, 2, '''')
from #srcData s1
) d
cross apply
(
values
(Yr, cast(cnt as nvarchar(50))),
(Yr+''_comment'', fullcomment)
) c (col, val)
) x
pivot
(
max(val)
for col in (' + @cols + ')
) p '
exec sp_executesql @query;
See SQL Fiddle with Demo. Both versions will give you the result:
| 2015 | 2015_comment | 2014 | 2014_comment | 2013 | 2013_comment |
|------|--------------|------|-----------------------------------|------|-------------------|
| 1 | 2.13.0 | 2 | 1.16.13 - 2.7.0; 2.8.0 - 2.12.0 | 1 | 1.6.13 - 1.16.13 |
Best Answer
You can group the data using a trick using row number - row number partitioned by status. That will create the same number for rows with the same status for a range of dates. This just takes the rows ordered by entry_date and status, but you might want to do something better for the entries on the same day:
Example in SQL Fiddle
Update: Fixed group by id, added handling for null / max exit_date for cases when the newest status has more than one row.