Solved

Find new actives students by month in my table

Posted on 2013-06-11
14
235 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
  • 6
  • 6
  • 2
14 Comments
 
LVL 24

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 48

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
 
LVL 48

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 48

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 24

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 48

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 48

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 48

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How can I get this "NOT IN" to work? 5 18
SQL 2012 and SQL 2014 in memory database 11 32
Help with SQL Query 23 39
Sql query 34 22
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 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

20 Experts available now in Live!

Get 1:1 Help Now