Solved

Find Starting Month of Activity

Posted on 2013-06-13
5
209 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help  needed 3 23
SQL 2008 R2 syntax 11 29
SQL server 2008 and after encryption method 32 46
ms sql + get number in list out of total 7 29
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

770 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