Butterfly2
asked on
name columns base on changing dates
Good Morning Experts,
I am in need some assistance:
I have the following query:
here is a sample set of the data :
In my code, the session week goes back 4 weeks based on todays date. I want these weeks to be columns. see attachment on how I want the table to look. data2.xlsx
so basically I need assistance in writing a query that will produce the results that look like the attachment.
I am in need some assistance:
I have the following query:
with a(SchCode,
empno,
orgrole
)
as
(
(SELECT distinct SchCode,
e.empno,
orgrole
FROM Proddatamart.[GSDR].[d2l].[GCPS_OUX_Enrollments_School_tmp] as e
where orgrole in ('Student')
)
union all
(SELECT distinct SchCode,
e.empno,
orgrole
FROM Proddatamart.[GSDR].[d2l].[GCPS_OUX_Enrollments_School_tmp] as e
inner join [ProdDatamart].gsdr.gems.teacher_schedule as ts
on e.empno = ts.EMPNO
where orgrole in ('SchoolAdminCascade', 'Teacher')
)
),
b(SessionStart,
UserName,
ActiveSessionDuration,
D2LRole)
as
(
select SessionStart,
UserName,
ActiveSessionDuration,
D2LRole
from d2l.D2L_Reporting_D2LSessionExport
where ActiveSessionDuration <> 0
and (SessionStart >= dateadd(day,-21,dateadd(day,-7,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)))
and SessionStart <=dateadd(day,-7,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)))
)
select a.empno,
a.SchCode,
CONVERT(varchar(10),(DATEADD(dd, @@DATEFIRST - DATEPART(dw, sessionstart), sessionstart)), 126) as sessionweek,
sum(cast(b.ActiveSessionDuration as Integer)) WeeklyMinutes,
count(b.UserName) as NumOfWeeklyLogins,
a.orgrole
from a
left join b
on a.empno = b.UserName
group by empno,
SchCode,
orgrole,
CONVERT(varchar(10),(DATEADD(dd, @@DATEFIRST - DATEPART(dw, sessionstart), sessionstart)), 126)
order by empnohere is a sample set of the data :
empno SchCode sessionweek WeeklyMinutes NumOfWeeklyLogins orgrole
200100009 643 NULL NULL 0 Student
200100306 196 NULL NULL 0 Student
200100354 750 2014-09-20 24 2 Student
200100354 225 2014-09-20 24 2 Student
200100359 750 NULL NULL 0 Student
200100362 439 NULL NULL 0 Student
200100605 439 NULL NULL 0 Student
200100683 643 2014-09-13 48 2 Student
200100683 643 2014-09-06 73 3 Student
200100683 643 2014-09-20 124 3 StudentIn my code, the session week goes back 4 weeks based on todays date. I want these weeks to be columns. see attachment on how I want the table to look. data2.xlsx
so basically I need assistance in writing a query that will produce the results that look like the attachment.
ASKER
Paul, are you referring to my link that is actually an embedded attachment? or are u referring to something else?
It was to my previous link.
ASKER
how do you do a dynamic pivot?
I think the query below will put out the results you need, except for the column names issue. Naturally the query may need tweaking, since I don't have any way to test it.
Then, as to the final column names, if you're willing to use a temp/intermediate table, you could also write the output below to that table, then either:
1) use dynamic SQL to rename the columns in the table to what you need them to be; then use standard SQL to report from the table -- I would use this option
2) use dynamic SQL to select from that table and rename the columns as part of that select.
Then, as to the final column names, if you're willing to use a temp/intermediate table, you could also write the output below to that table, then either:
1) use dynamic SQL to rename the columns in the table to what you need them to be; then use standard SQL to report from the table -- I would use this option
2) use dynamic SQL to select from that table and rename the columns as part of that select.
;with control_dates(starting_date
)
as
(
--find the 4th Saturday before today
select DATEADD(DAY, DATEDIFF(DAY, 5 /*Saturday*/, GETDATE()) - 21, 0) AS starting_date
),
a(SchCode,
empno,
orgrole
)
as
(
(SELECT distinct SchCode,
e.empno,
orgrole
FROM Proddatamart.[GSDR].[d2l].[GCPS_OUX_Enrollments_School_tmp] as e
where orgrole in ('Student')
)
union all
(SELECT distinct SchCode,
e.empno,
orgrole
FROM Proddatamart.[GSDR].[d2l].[GCPS_OUX_Enrollments_School_tmp] as e
inner join [ProdDatamart].gsdr.gems.teacher_schedule as ts
on e.empno = ts.EMPNO
where orgrole in ('SchoolAdminCascade', 'Teacher')
)
),
b(SessionStart,
UserName,
ActiveSessionDuration,
D2LRole)
as
(
select SessionStart,
UserName,
ActiveSessionDuration,
D2LRole
from d2l.D2L_Reporting_D2LSessionExport
where ActiveSessionDuration <> 0
and (SessionStart >= control_dates.starting_date
and SessionStart < control_dates.starting_date + 28)
)
select a.empno,
a.SchCode,
sum(case when b.sessionstart >= control_dates.starting_date AND b.sessionstart < control_dates.starting_date + 7
then cast(b.ActiveSessionDuration as Integer) else 0 end) as WeeklyMinutes_Week1,
count(case when b.sessionstart >= control_dates.starting_date AND b.sessionstart < control_dates.starting_date + 7
then b.UserName end) as WeeklyLogins_Week1,
sum(case when b.sessionstart >= control_dates.starting_date + 7 AND b.sessionstart < control_dates.starting_date + 14
then cast(b.ActiveSessionDuration as Integer) else 0 end) as WeeklyMinutes_Week2,
count(case when b.sessionstart >= control_dates.starting_date + 7 AND b.sessionstart < control_dates.starting_date + 14
then b.UserName end) as WeeklyLogins_Week2,
sum(case when b.sessionstart >= control_dates.starting_date + 14 AND b.sessionstart < control_dates.starting_date + 21
then cast(b.ActiveSessionDuration as Integer) else 0 end) as WeeklyMinutes_Week3,
count(case when b.sessionstart >= control_dates.starting_date + 14 AND b.sessionstart < control_dates.starting_date + 21
then b.UserName end) as WeeklyLogins_Week3,
sum(case when b.sessionstart >= control_dates.starting_date + 21 AND b.sessionstart < control_dates.starting_date + 28
then cast(b.ActiveSessionDuration as Integer) else 0 end) as WeeklyMinutes_Week4,
count(case when b.sessionstart >= control_dates.starting_date + 21 AND b.sessionstart < control_dates.starting_date + 28
then b.UserName end) as WeeklyLogins_Week4,
a.orgrole
from a
left join b
on a.empno = b.UserName
group by a.empno,
a.SchCode,
a.orgrole
order by empnoASKER
Hi Scott,
thanks for the post it would work for me but the column names have to say week ending 9/27, week ending 9/20 etc. They want me to specify the week ending date. I tried doing a dynamic pivot but it didnt work. Here is what I did.
but i got this error:
Msg 203, Level 16, State 2, Line 14
The name 'select SchCode,
empno,
[2014-09-20],[2014-09-06]
from #UsageDetail
Pivot(sum(NumOfWeeklyLogin
for seesionweek in ([2014-09-20],[2014-09-06]
I have no idea how to troubleshoot this.
thanks for the post it would work for me but the column names have to say week ending 9/27, week ending 9/20 etc. They want me to specify the week ending date. I tried doing a dynamic pivot but it didnt work. Here is what I did.
Declare @DynamicPivotQuery as nvarchar(Max)
Declare @ColumnName as Nvarchar(max)
--Get the dates for my pivot column
Select @ColumnName = ISNULL(@ColumnName + ',','') + QUOTENAME(sessionweek)
from (select distinct sessionweek from #UsageDetail) as u
set @DynamicPivotQuery =
N'select SchCode,
empno,
' + @ColumnName + '
from #UsageDetail
Pivot(NumOfWeeklyLogins
for seesionweek in (' + @ColumnName + ')) as Pt'
exec @DynamicPivotQuerybut i got this error:
Msg 203, Level 16, State 2, Line 14
The name 'select SchCode,
empno,
[2014-09-20],[2014-09-06]
from #UsageDetail
Pivot(sum(NumOfWeeklyLogin
for seesionweek in ([2014-09-20],[2014-09-06]
I have no idea how to troubleshoot this.
Maybe something like this:
--The underlying SQL doesn't have to fully dynamic, just the part that adjusts (or assigns) the column names.
declare @sql varchar(max)
select @sql = replace(replace(replace(replace('
;with control_dates(starting_date
)
as
(
--find the 4th Saturday before today
select DATEADD(DAY, DATEDIFF(DAY, 5 /*Saturday*/, GETDATE()) - 21, 0) AS starting_date
),
a(SchCode,
empno,
orgrole
)
as
(
(SELECT distinct SchCode,
e.empno,
orgrole
FROM Proddatamart.[GSDR].[d2l].[GCPS_OUX_Enrollments_School_tmp] as e
where orgrole in (''Student'')
)
union all
(SELECT distinct SchCode,
e.empno,
orgrole
FROM Proddatamart.[GSDR].[d2l].[GCPS_OUX_Enrollments_School_tmp] as e
inner join [ProdDatamart].gsdr.gems.teacher_schedule as ts
on e.empno = ts.EMPNO
where orgrole in (''SchoolAdminCascade'', ''Teacher'')
)
),
b(SessionStart,
UserName,
ActiveSessionDuration,
D2LRole)
as
(
select SessionStart,
UserName,
ActiveSessionDuration,
D2LRole
from d2l.D2L_Reporting_D2LSessionExport
where ActiveSessionDuration <> 0
and (SessionStart >= control_dates.starting_date
and SessionStart < control_dates.starting_date + 28)
)
select a.empno,
a.SchCode,
sum(case when b.sessionstart >= control_dates.starting_date AND b.sessionstart < control_dates.starting_date + 7
then cast(b.ActiveSessionDuration as Integer) else 0 end) as [WeeklyMinutes_Week1],
count(case when b.sessionstart >= control_dates.starting_date AND b.sessionstart < control_dates.starting_date + 7
then b.UserName end) as [WeeklyLogins_Week1],
sum(case when b.sessionstart >= control_dates.starting_date + 7 AND b.sessionstart < control_dates.starting_date + 14
then cast(b.ActiveSessionDuration as Integer) else 0 end) as [WeeklyMinutes_Week2],
count(case when b.sessionstart >= control_dates.starting_date + 7 AND b.sessionstart < control_dates.starting_date + 14
then b.UserName end) as [WeeklyLogins_Week2],
sum(case when b.sessionstart >= control_dates.starting_date + 14 AND b.sessionstart < control_dates.starting_date + 21
then cast(b.ActiveSessionDuration as Integer) else 0 end) as [WeeklyMinutes_Week3],
count(case when b.sessionstart >= control_dates.starting_date + 14 AND b.sessionstart < control_dates.starting_date + 21
then b.UserName end) as [WeeklyLogins_Week3],
sum(case when b.sessionstart >= control_dates.starting_date + 21 AND b.sessionstart < control_dates.starting_date + 28
then cast(b.ActiveSessionDuration as Integer) else 0 end) as [WeeklyMinutes_Week4],
count(case when b.sessionstart >= control_dates.starting_date + 21 AND b.sessionstart < control_dates.starting_date + 28
then b.UserName end) as [WeeklyLogins_Week4],
a.orgrole
from a
left join b
on a.empno = b.UserName
group by a.empno,
a.SchCode,
a.orgrole
order by empno
'
, '_Week1', ' ' + CAST(MONTH(starting_date) AS varchar(2)) + '/' + CAST(DAY(starting_date) AS varchar(2)))
, '_Week2', ' ' + CAST(MONTH(starting_date + 7) AS varchar(2)) + '/' + CAST(DAY(starting_date + 7) AS varchar(2)))
, '_Week3', ' ' + CAST(MONTH(starting_date + 14) AS varchar(2)) + '/' + CAST(DAY(starting_date + 14) AS varchar(2)))
, '_Week4', ' ' + CAST(MONTH(starting_date + 21) AS varchar(2)) + '/' + CAST(DAY(starting_date + 21) AS varchar(2)))
FROM (
SELECT DATEADD(DAY, DATEDIFF(DAY, 5 /*Saturday*/, GETDATE()) - 21, 0) AS starting_date
) AS week1_date
print @sql
--exec(@sql)--The underlying SQL doesn't have to fully dynamic, just the part that adjusts (or assigns) the column names.
If you truly need variable column headers, then some dynamic SQL is required.
I have assumed you could continue the use of CTEs with a third called "c". This is really just for convenience because I could only really start with your sample data, so my "beginning" is your code that ends in a third CTE:
From the sample data this produced the following result:
The dynamic portion may be see working here: http://sqlfiddle.com/#!3/4dd55/1
I have assumed you could continue the use of CTEs with a third called "c". This is really just for convenience because I could only really start with your sample data, so my "beginning" is your code that ends in a third CTE:
WITH
a (SchCode, empno, orgrole)
AS (
SELECT DISTINCT
SchCode
, e.empno
, orgrole
FROM Proddatamart.[GSDR].[d2l].[GCPS_OUX_Enrollments_School_tmp] AS e
WHERE orgrole IN ('Student')
UNION ALL
SELECT DISTINCT
SchCode
, e.empno
, orgrole
FROM Proddatamart.[GSDR].[d2l].[GCPS_OUX_Enrollments_School_tmp] AS e
INNER JOIN [ProdDatamart].gsdr.gems.teacher_schedule AS ts
ON e.empno = ts.EMPNO
WHERE orgrole IN ('SchoolAdminCascade', 'Teacher')
),
b (SessionStart, UserName, ActiveSessionDuration, D2LRole)
AS (
SELECT
SessionStart
, UserName
, ActiveSessionDuration
, D2LRole
FROM d2l.D2L_Reporting_D2LSessionExport
WHERE ActiveSessionDuration <> 0
AND (SessionStart >= DATEADD(DAY, -21, DATEADD(DAY, -7, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)))
AND SessionStart <= DATEADD(DAY, -7, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)))
),
c
AS (
SELECT
a.empno
, a.SchCode
, CONVERT(varchar(10), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, sessionstart), sessionstart)), 126) AS sessionweek
, SUM(CAST(b.ActiveSessionDuration AS integer)) WeeklyMinutes
, COUNT(b.UserName) AS NumOfWeeklyLogins
, a.orgrole
FROM a
LEFT JOIN b
ON a.empno = b.UserName
GROUP BY
empno
, SchCode
, orgrole
, CONVERT(varchar(10), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, sessionstart), sessionstart)), 126)
)DECLARE @select VARCHAR(max)
DECLARE @cols VARCHAR(max)
DECLARE @query VARCHAR(max)
SELECT @cols = STUFF(
(
SELECT DISTINCT ',' + quotename('Total Logins Week Ending ' + convert(varchar(5),sessionweek,1))
FROM c
FOR XML PATH('')
)
, 1, 1, '')
SELECT @select = STUFF(
(
SELECT DISTINCT ', ISNULL(' + quotename('Total Logins Week Ending ' + convert(varchar(5),sessionweek,1)) + ',0) as ' + quotename('Total Logins Week Ending ' + convert(varchar(5),sessionweek,1))
FROM c
FOR XML PATH('')
)
, 1, 1, '')
--SELECT @cols as x
--union all
--SELECT @select
SET @query = 'SELECT empno, SchCode, '
+ @select
+ ' FROM (SELECT *, ''Total Logins Week Ending '' + convert(varchar(5),sessionweek,1) as x'
+ ' FROM C) AS p PIVOT ( SUM([NumOfWeeklyLogins]) FOR x IN ( '
+ @cols
+ ' )) AS pvt ORDER BY empno, SchCode;'
SELECT @query
--EXECUTE(@query)
;SELECT
empno
, SchCode
, ISNULL([Total Logins Week Ending 09/06], 0) AS [Total Logins Week Ending 09/06]
, ISNULL([Total Logins Week Ending 09/13], 0) AS [Total Logins Week Ending 09/13]
, ISNULL([Total Logins Week Ending 09/20], 0) AS [Total Logins Week Ending 09/20]
FROM (
SELECT
*
, 'Total Logins Week Ending ' + CONVERT(varchar(5), sessionweek, 1) AS x
FROM C
) AS p PIVOT (SUM([NumOfWeeklyLogins]) FOR x IN ([Total Logins Week Ending 09/06], [Total Logins Week Ending 09/13], [Total Logins Week Ending 09/20])) AS pvt
ORDER BY
empno, SchCode;From the sample data this produced the following result:
| EMPNO | SCHCODE | TOTAL LOGINS WEEK ENDING 09/06 | TOTAL LOGINS WEEK ENDING 09/13 | TOTAL LOGINS WEEK ENDING 09/20 |
|-----------|---------|--------------------------------|--------------------------------|--------------------------------|
| 200100009 | 643 | 0 | 0 | 0 |
| 200100306 | 196 | 0 | 0 | 0 |
| 200100354 | 225 | 0 | 0 | 2 |
| 200100354 | 750 | 0 | 0 | 2 |
| 200100359 | 750 | 0 | 0 | 0 |
| 200100362 | 439 | 0 | 0 | 0 |
| 200100605 | 439 | 0 | 0 | 0 |
| 200100683 | 643 | 3 | 0 | 0 |
| 200100683 | 643 | 0 | 2 | 0 |
| 200100683 | 643 | 0 | 0 | 3 |The dynamic portion may be see working here: http://sqlfiddle.com/#!3/4dd55/1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
never resolved, ended using a different approach.
If you just had Week1, Week2 etc. in columns, that would be easy, but having changing column names makes it much more difficult.