Go Premium for a chance to win a PS4. Enter to Win

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

Find new actives students by month in my table

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
al4629740
Asked:
al4629740
  • 6
  • 6
  • 2
3 Solutions
 
chaauCommented:
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
 
PortletPaulCommented:
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
 
BAKADYCommented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
PortletPaulCommented:
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
 
BAKADYCommented:
@PortletPaul: look in the excel file:
Desired Output # of new active greater than 0
0
 
PortletPaulCommented:
@BAKADY
look at the sqlfiddle
0
 
BAKADYCommented:
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
 
chaauCommented:
Like the friendly atmosphere here
0
 
BAKADYCommented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
@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
 
BAKADYCommented:
@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
 
PortletPaulCommented:
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
 
BAKADYCommented:
@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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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