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

x
?
Solved

Find Starting Month of Activity

Posted on 2013-06-13
5
Medium Priority
?
236 Views
Last Modified: 2013-06-28
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
Comment
Question by:al4629740
  • 2
  • 2
5 Comments
 

Author Comment

by:al4629740
ID: 39245199
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
 
LVL 4

Accepted Solution

by:
BAKADY earned 750 total points
ID: 39245498
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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 750 total points
ID: 39246351
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39254933
@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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

971 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