Solved

Find new actives students by month in my table

Posted on 2013-06-11
14
245 Views
Last Modified: 2013-06-28
I have a table named tblAttendance in SQL 2008 that has a listing of all students and their hours.  I would like to find all new records that have started to become active.  This is figured out by finding the first month a person has a total > 0

Using the following fiscal month pattern order starting with June, I would like to find records where the first month the total > o.  


JULY
AUGUST
SEPTEMBER
OCTOBER
NOVEMEBER
DECEMBER
JANUARY
FEBRUARY
MARCH
APRIL
MAY
JUNE

Please see my attachment and notice that Tajour Brown is in 3 months, but he is only counted for the first time in September.  According to the list above, since September is the first month he actually had hours, and that will be the only month we need to count him.  At the end of the year the total should equal the total number of distinct individuals who actually had hours

Let me know if I have to clarify and thank you for your help.  It is greatly appreciated.
desired-output.xlsx
0
Comment
Question by:al4629740
[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
  • 6
  • 6
  • 2
14 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 167 total points
ID: 39240121
This "simple" query will do this in one go:

select Month,
FiscalMonthNo,
count([Client ID])
from
(
  select 
  [Client ID],
  Month,
  datepart(mm,convert(date, '1-'+Month+'-2013')) as monthNo,
  Case when datepart(mm,convert(date, '1-'+Month+'-2013')) > 6 then datepart(mm,convert(date, '1-'+Month+'-2013')) - 6 else datepart(mm,convert(date, '1-'+Month+'-2013')) + 6 end AS FiscalMonthNo,
  dense_rank() OVER (PARTITION BY [Client ID] ORDER BY Case when datepart(mm,convert(date, '1-'+Month+'-2013')) > 6 then datepart(mm,convert(date, '1-'+Month+'-2013')) - 6 else datepart(mm,convert(date, '1-'+Month+'-2013')) + 6 end) AS RankByFiskalMonth 
  from tblAttendance
)a
WHERE RankByFiskalMonth = 1
GROUP BY Month, FiscalMonthNo
UNION ALL
select 'YTD',
13,
count(distinct [Client ID])
from
(
  select 
  [Client ID],
  Month,
  datepart(mm,convert(date, '1-'+Month+'-2013')) as monthNo,
  Case when datepart(mm,convert(date, '1-'+Month+'-2013')) > 6 then datepart(mm,convert(date, '1-'+Month+'-2013')) - 6 else datepart(mm,convert(date, '1-'+Month+'-2013')) + 6 end AS FiscalMonthNo,
  dense_rank() OVER (PARTITION BY [Client ID] ORDER BY Case when datepart(mm,convert(date, '1-'+Month+'-2013')) > 6 then datepart(mm,convert(date, '1-'+Month+'-2013')) - 6 else datepart(mm,convert(date, '1-'+Month+'-2013')) + 6 end) AS RankByFiskalMonth 
  from tblAttendance
)a
WHERE RankByFiskalMonth = 1
ORDER BY 2

Open in new window


SQL Fiddle

Note: please ignore the second column in your report. It is required for sorting only. It is the month Number in the fiscal year (July=1, August=2....  June = 12)

BTW, your YTD value in the excel file is wrong. It should be 3: (distinct clients 312, 419, 425)
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 167 total points
ID: 39240192
This is an alternative approach that uses a small CTE to arrive at the fiscal months which helps with ordering, plus it ensures all months are displayed even if no count is against it. Another CTE is used to prepare the counts of students and then that is leveraged to simplify production of the grand total. (i.e. if grand total isn't required overall it can be simplified further).
declare @BeginsAt as datetime = '2012-07-01'
declare @numMonths as int = 12

set @BeginsAt  = dateadd(month,datediff(month,0,@BeginsAt),0) -- force to 1st of Month

;with MonthRange (id, StartAt, StopAt, Month)
as (
    select 1 as id, @BeginsAt, dateadd(month,1,@BeginsAt), datename(month,@BeginsAt)
    union all
    select (id + 1) , dateadd(month,1,StartAt) , dateadd(month,1,StopAt), datename(month,dateadd(month,1,StartAt))
    from MonthRange
    where (id + 1) <= @numMonths
    ),
Derived as (
            select
              m.id as FiscalMonthNo
            , m.month
            , count(distinct A.[Client ID]) NumNewActive
            from MonthRange as M
            left join (
                        select
                          [Client ID]
                        , A1.[Month]
                        , row_number() over (partition by [Client ID] order by M1.ID) as row_ref
                        from tblAttendance as A1
                        inner join MonthRange as M1 on A1.month = M1.month
                       ) as A ON M.month = A.month
                             AND A.row_ref = 1
            group by
              m.id
            , m.month
            )
select
  FiscalMonthNo
, month
, NumNewActive
from derived
union all
select
  12
, 'YTD'
, sum(NumNewActive)
from derived
order by 1,2

Open in new window

http://www.sqlfiddle.com/#!3/c66a8/3
0
 
LVL 4

Assisted Solution

by:BAKADY
BAKADY earned 166 total points
ID: 39243004
hi again,
i have compare your file desired-output from yesterday and this one,...
i think you are forgetting here your field "Fiscal" (year), if not remove it from the code
anyway...
here is the code...

With FiscalYear (MonthID, Month) As (
  select 1, 'JULY'
  union all
  select 2, 'AUGUST'
  union all
  select 3, 'SEPTEMBER'
  union all
  select 4, 'OCTOBER'
  union all
  select 5, 'NOVEMBER'
  union all
  select 6, 'DECEMBER'
  union all
  select 7, 'JANUARY'
  union all
  select 8, 'FEBRUARY'
  union all
  select 9, 'MARCH'
  union all
  select 10, 'APRIL'
  union all
  select 11, 'MAY'
  union all
  select 12, 'JUNE'
), tblAttendanceIDs (ID, Fiscal, Month, ClientID, RecNo) As (
  select ID, Fiscal, Month, [Client ID], row_number() over (partition by Fiscal, [Client ID] order by ID)
  from tblAttendance
  where Total > 0
)
Select tblAttendanceIDs.Fiscal, FiscalYear.MonthID, FiscalYear.Month, Count(tblAttendanceIDs.ClientID) As Stundents
From FiscalYear, tblAttendanceIDs
Where UPPER(tblAttendanceIDs.Month) = UPPER(FiscalYear.Month)
  And tblAttendanceIDs.RecNo = 1
  -- And tblAttendanceIDs.Fiscal = 2013 -- uncomment this to filter the Fiscal year 
Group By tblAttendanceIDs.Fiscal, FiscalYear.MonthID, FiscalYear.Month
Order By tblAttendanceIDs.Fiscal, FiscalYear.MonthID;

Open in new window

0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 49

Expert Comment

by:PortletPaul
ID: 39243080
if needing a filter on a column called 'Fiscal', see line 28:
declare @BeginsAt as datetime = '2012-07-01'
declare @numMonths as int = 12

set @BeginsAt  = dateadd(month,datediff(month,0,@BeginsAt),0) -- force to 1st of Month

;with MonthRange (id, StartAt, StopAt, Month)
as (
    select 1 as id, @BeginsAt, dateadd(month,1,@BeginsAt), datename(month,@BeginsAt)
    union all
    select (id + 1) , dateadd(month,1,StartAt) , dateadd(month,1,StopAt), datename(month,dateadd(month,1,StartAt))
    from MonthRange
    where (id + 1) <= @numMonths
    ),
Derived as (
            select
              m.id as FiscalMonthNo
            , m.month
            , count(distinct A.[Client ID]) NumNewActive
            from MonthRange as M
            left join (
                        select
                          [Client ID]
                        , A1.[Month]
                        , row_number() over (partition by [Client ID] order by M1.ID) as row_ref
                        from tblAttendance as A1
                        inner join MonthRange as M1 on A1.month = M1.month
              
                        where A1.fiscal = 2012 -- if needed a fiscal year filter
              
                       ) as A ON M.month = A.month
                             AND A.row_ref = 1
            group by
              m.id
            , m.month
            )
select
  FiscalMonthNo
, month
, NumNewActive
from derived
union all
select
  12
, 'YTD'
, sum(NumNewActive)
from derived
order by 1,2
;

Open in new window

This lists all months (as per the expected output) and the YTD total

http://www.sqlfiddle.com/#!3/2ea46/3
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39243091
@PortletPaul: look in the excel file:
Desired Output # of new active greater than 0
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39243106
@BAKADY
look at the sqlfiddle
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39243111
Dude.... again...??? i had look at sqlfiddle... it looks nice, great work...!!! but i read between lines...
Let the Author select which is the right answer...
0
 
LVL 25

Expert Comment

by:chaau
ID: 39243143
Like the friendly atmosphere here
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39243164
do you remember that????
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_28151322.html#a39234277

just kidding.... i glad to find someone with the same motivation like me...

good work PortletPaul
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39243177
which lines need to be read between? the expected results (all months + YTD) are generated  (by working from the fiscal year left joined to the attendance data - that part is essential) and are accurate.

regarding the "new active" that part is already there (but was missing from your proposal yesterday) and regarding "greater than 0" how will "where Total > 0" help at all?
 (it doesn't as the count will always be 0 or positive and it suppresses wanted rows)

you have in the past pointed to an error I made in reading a question - (& that's a good thing to do) - but I didn't then go on to suggest your solution had deficiencies "between the lines". :(

plus, you really should be using ansi joins (always) - part of the responsibility here is to promote best practice. You write good code! but it would be even better with ansi joins.

note there is one thing in your proposal which, arguably, mine might do:
UPPER(tblAttendanceIDs.Month) = UPPER(FiscalYear.Month)

My approach does assume the [Month] column is 'accurate' - but that's exactly how the data was presented in the spreadsheet. The counter argument is that by applying functions to data can remove the ability for use of indexes and of course adds more processing to the query.

as a final note, use of CTEs for generating fiscal periods makes the solution quite flexible, whilst a hardcoded union is less so.

at this point I see no reason to place doubt in the mind of the asker, so, could you clarify what "between lines" need to be addressed?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39243182
@BAKADY, yes I do - and you were 100% right to point it out - I capitulated (without rancor)

my notes above by the way were being written before I saw your comment above.
no hard feelings, at all, from me.
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39243214
@PortletPaul: i never mind your code is wrong, it is better as mine, i write again good work,...
i just said "Desired Output # of new active greater than 0"..., if you apply this to your code, you need to change a couple of lines and al (the author) has more options...

i will be apply your advice about using ansi joins...

just a question... is your keyboard burning????  

kidding again...   ;)

if you have time, take a look here???

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28155538.html
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39243328
ah, this...
>>I would like to find records where the first month the total > o.  
I do not believe this means suppress rows where the total is zero, to me the key phrase in that sentence is "first month" and indeed this the core of the question - to only count individuals when they first appear in the data. So, I don't believe there is a need to amend my proposed solution for the ">0" because it is coupled to "first month".

@al4629740
forgive the banter between us - it may be confusing - sorry about that.
the benefit is that the "peer review" produces a quality outcome - so its not all bad :)

I suggest ID: 39243080 does meet your requirements and you may use the sqlfiddle http://www.sqlfiddle.com/#!3/2ea46/3 to help confirm it (as well as testing on site of course). Note that the filter for field [Fiscal] is optional - i.e. I don't really know if you need it or not.

Cheers, Paul
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39245488
@PortletPaul: this look to me like duplicate questions, just take a look... this is from same author.
"Find Starting Month of Activity"

Regards
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

615 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