Solved

name columns base on changing dates

Posted on 2014-10-02
11
36 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
  • 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
 

Author Comment

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

Expert Comment

by:ScottPletcher
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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:ScottPletcher
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 48

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now