[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

capturing totals by weeks, using a date for week start

Hi Experts,
I have a query that captures a count on a particular day at a location,  I need to put the report in a format by week(Monday thru Sunday).  

Here is my query
SELECT CAST(sessionstart as date) as Date,
       count(UserName) as count,
       e.SchCode,
       LOCNAME,
       D2lRole
FROM [GSDR].[d2l].[D2L_Reporting_D2LSessionExport] as d
      cross join (SELECT DATEDIFF(week, 0, getdate())*7 lastmonday) a
      inner join [ProdDatamart].gsdr.d2l.GCPS_OUX_Enrollments_School_tmp as e 
       on d.UserName=e.empno
      inner join [ProdDatamart].gsdr.gems.location l 
       on e.schCode=l.loc
where SessionStart >= a.lastmonday - 7
       and SessionStart < a.lastmonday 
       and D2LRole = 'Student'
       and activesessionduration>0
       and schCode in ('700', '725','855')
group by D2LRole,
             CAST(sessionstart as date) ,
             schCode,
             LOCNAME

order by  
         LOCNAME,
         Date,
         D2LRole

Open in new window


see below for results(I am only including 1 school)
Date          count  SchCode      LOCNAME           D2lRole
8/4/2014       27       700       NORCROSS HIGH      Student
8/5/2014       61       700       NORCROSS HIGH      Student
8/6/2014       258      700       NORCROSS HIGH      Student
8/7/2014       71       700       NORCROSS HIGH      Student
8/8/2014       45       700       NORCROSS HIGH      Student
8/9/2014       32       700       NORCROSS HIGH      Student
8/10/2014      74       700       NORCROSS HIGH      Student
8/11/2014      122      700       NORCROSS HIGH      Student
8/12/2014      92       700       NORCROSS HIGH      Student
8/13/2014      331      700       NORCROSS HIGH      Student
8/14/2014      164      700       NORCROSS HIGH      Student
8/15/2014      357      700       NORCROSS HIGH      Student
8/16/2014      41       700       NORCROSS HIGH      Student
8/17/2014      94       700       NORCROSS HIGH      Student

Open in new window

for the results above I have 14 records but I only what 2 records  

This is what  want

Date                count      SchCode          LOCNAME               D2lRole
8/4/2014        568             700               NORCROSS HIGH       Student
8/11/2014      1201           700               NORCROSS HIGH       Student
0
Butterfly2
Asked:
Butterfly2
  • 2
2 Solutions
 
Shaun KlineLead Software EngineerCommented:
Try changing this:
CAST(sessionstart as date)

To:
CAST(sessionstart as date) - DATEPART(WEEKDAY, sessionstart) + 2

in both the SELECT and GROUP BY clauses.
0
 
Butterfly2Author Commented:
I get this error:

Operand type clash: date is incompatible with int
0
 
Shaun KlineLead Software EngineerCommented:
Must be because of the casting to Date. I used a datetime when I tested.
Use the DATEADD function:

DATEADD(DAY, - DATEPART(WEEKDAY, CAST(sessionstart as date)) + 2, CAST(sessionstart as date))
0
 
Scott PletcherSenior DBACommented:
You just needt to group on the week rather than day:

SELECT  dateadd(week, datediff(week, 0, sessionstart), 0) as Date,
        count(UserName) as count,
        e.SchCode,
        LOCNAME,
        D2lRole
 FROM [GSDR].[d2l].[D2L_Reporting_D2LSessionExport] as d
       cross join (SELECT DATEDIFF(week, 0, getdate())*7 lastmonday) a
       inner join [ProdDatamart].gsdr.d2l.GCPS_OUX_Enrollments_School_tmp as e 
        on d.UserName=e.empno
       inner join [ProdDatamart].gsdr.gems.location l 
        on e.schCode=l.loc
 where SessionStart >= a.lastmonday - 7
        and SessionStart < a.lastmonday 
        and D2LRole = 'Student'
        and activesessionduration > 0
        and schCode in ('700', '725','855')
 group by D2LRole,
              dateadd(week, datediff(week, 0, sessionstart), 0),
              schCode,
              LOCNAME

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now