Improve company productivity with a Business Account.Sign Up

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

Find Starting Month of Activity

I have an SQL table for student attendance that has the following columns

ID
Total
Month
Name
DataPresent

I basically want to scan the table and find the month where everyone started their activity.  This happens by calculating the first month where Total > 0 or DataPresent = 1 starting from the month of July.

Since our fiscal year starts in July, the year begins from that month and ends in June

I have attached an example illustrating what I am looking for.
example.xlsx
0
al4629740
Asked:
al4629740
  • 2
  • 2
2 Solutions
 
al4629740Author Commented:
I left out a column called DataPresent.  This column should help us to loop through the months and find the first month where activity started.
0
 
BAKADYCommented:
Hi Al,
in SQL Fiddle are the last PortletPaul's and my proposals

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


just include your new fields there...
0
 
PaulCommented:
Name            Month  TotHrs DataPresent
Johnny Smith    JULY      0    0
Johnny Smith    AUGUST    0    0 
Johnny Smith    SEPTEMBER 0    0 
Johnny Smith    OCTOBER   0    0 
Johnny Smith    NOVEMBER  5    1 -- << first month of Johny Smith activity
Johnny Smith    DECEMBER  8    1
Johnny Smith    JANUARY   10   1 
Johnny Smith    FEBRUARY  0    0
Johnny Smith    MARCH     0    0 
Johnny Smith    APRIL     0    0
Johnny Smith    MAY       0    0
Johnny Smith    JUNE      0    0

Open in new window

@al4629740
as depicted above, this additional column [DataPresent] really adds no particular value to logic of the solution presented presented at ID: 39243080

>>I basically want to scan the table and find the month where everyone started their activity.
This requirement is met by use of row_number() which is "partitioned" for each [ClientID] and ordered by fiscal month.

I would actually recommend you do NOT consider this field.

However, if you feel you absolutely do need this, then all that is required is to extend the where clause to accommodate some additional criteria, as you see here with the added lines 29-31 below:
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

                        /* 2 added conditions for Q_28156514 *              
                        where (   [Total] > 0
                               or [DataPresent] = 1
                              )
                        and 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 version is available to run/see at http://www.sqlfiddle.com/#!3/40751/5

It would be nice to hear from you as I believe both recent questions have been answered.
0
 
PaulCommented:
@al4629740
I notice you accepted the answer on adding an extra field [DataPresent]

Are you still uncertain that my proposal satisfies calculation of first month?

You may have noticed I have used sqlfiddle in my comments, this site allows you to actually run the sql against some sample data and inspect the results. You are welcome to use this and it needed you may add further data.
(although sqlfiddle does not permit a great volume of data)

In my testing against data so far provided the proposal immediately above does accurately determine "first month".
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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