name columns base on changing dates

Good Morning Experts,

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 empno

Open in new window


here 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         Student

Open in new window


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.
Butterfly2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You would have to use a Dynamic Pivot, because the names of your columns will keep changing.

If you just had Week1, Week2 etc. in columns, that would be easy, but having changing column names makes it much more difficult.
0
Butterfly2Author Commented:
Paul, are you referring to my link that is actually an embedded attachment? or are u referring to something else?
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It was to my previous link.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Butterfly2Author Commented:
how do you do a dynamic pivot?
0
Scott PletcherSenior DBACommented:
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.

;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

Open in new window

0
Butterfly2Author Commented:
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.

 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 @DynamicPivotQuery

Open in new window

 
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(NumOfWeeklyLogins)
           for seesionweek in ([2014-09-20],[2014-09-06])) as Pt' is not a valid identifier.



I have no idea how to troubleshoot this.
0
Scott PletcherSenior DBACommented:
Maybe something like this:

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)

Open in new window


--The underlying SQL doesn't have to fully dynamic, just the part that adjusts (or assigns) the column names.
0
PortletPaulfreelancerCommented:
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:
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)
            )

Open in new window

The dynamic SQL piece which follows is:
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)
;

Open in new window

That dynamic query looks like this (when formatted)
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;

Open in new window


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 |

Open in new window


The dynamic portion may be see working here: http://sqlfiddle.com/#!3/4dd55/1
0
Butterfly2Author Commented:
I still cannot get this to work.   I am going to just try another approach.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Butterfly2Author Commented:
never resolved, ended using a different approach.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.