Solved

Find Starting Month of Activity

Posted on 2013-06-13
5
205 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 250 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 4

Expert Comment

by:BAKADY
ID: 39245515
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 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 48

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

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!

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach 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.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now