Solved

# Find new actives students by month in my table

Posted on 2013-06-11
Medium Priority
253 Views
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
Question by:al4629740
• 6
• 6
• 2

LVL 25

Accepted Solution

chaau earned 501 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
``````

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

PortletPaul earned 501 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
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
``````
http://www.sqlfiddle.com/#!3/c66a8/3
0

LVL 4

Assisted Solution

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;
``````
0

LVL 49

Expert Comment

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

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

LVL 49

Expert Comment

ID: 39243106
look at the sqlfiddle
0

LVL 4

Expert Comment

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

ID: 39243143
Like the friendly atmosphere here
0

LVL 4

Expert Comment

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

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

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

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

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

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

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

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
###### Suggested Courses
Course of the Month3 days, 9 hours left to enroll