Solved

Find new actives students by month in my table

Posted on 2013-06-11
14
243 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 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 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql Server group by 10 48
Better way to make a query with date filter. 5 42
PolyServe for SQL server 13 41
tempdb log keep growing 7 43
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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

685 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