Solved

Find Starting Month of Activity

Posted on 2013-06-13
5
222 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 49

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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 this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

617 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