?
Solved

name columns base on changing dates

Posted on 2014-10-02
11
Medium Priority
?
61 Views
Last Modified: 2015-06-03
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.
0
Comment
Question by:Butterfly2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
11 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40357008
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
 

Author Comment

by:Butterfly2
ID: 40357166
Paul, are you referring to my link that is actually an embedded attachment? or are u referring to something else?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40357181
It was to my previous link.
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

Author Comment

by:Butterfly2
ID: 40357185
how do you do a dynamic pivot?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40357436
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
 

Author Comment

by:Butterfly2
ID: 40357649
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40357877
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40358524
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
 

Accepted Solution

by:
Butterfly2 earned 0 total points
ID: 40370760
I still cannot get this to work.   I am going to just try another approach.
0
 

Author Closing Comment

by:Butterfly2
ID: 40809830
never resolved, ended using a different approach.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question