Select query output break down by month

What do you mean by an "active month"??  And how do you select a record without a date field from between Effective Date and End Date???  

Please show me the complete record (or at least show me all the key fields) and please tell me what the operational definition of "active month" is, and perhaps I can help.

As i mentioned here.These are the fields i get form the  customer table

cust_id   effective_date    end_date
c1            1/10/2007            5/15/2007

Customer C1 is active from 1/10 to 5/15.So we need to calculate and display how many days is the Customer C1 active in each month starting from Jan to May.
Basically we need to really concentrate on Effcetive month and End month and the months in between will obviously have the total days as active days

Also Active Months are which Customer C1 is active for complete Month(i.e in the above example its Feb,March and April) and i need the Active Days for some other validations.

I believe this is what you are looking for.  If the active range could be more than 1 year, then you will have to change the 366 to some larger number.  The 366 is an arbitrarily large number that has to be as big as the largest date range.

select cust_id, trunc(trunc(a.effective_date) + (b.days - 1), 'MM'), count(1)
from test a, (select level days from dual connect by level <= 366) b
where trunc(a.effective_date) + (b.days - 1) <= a.end_date
group by cust_id, trunc(trunc(a.effective_date) + (b.days - 1), 'MM');

Also, your sample results look incorrect.

In January there should be 22 active days.   You are not counting 1/10 as active.

Same goes for May.

You are correct about days.
i changed Trunc to Truncate and compiled . its giving this error
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'truncate'.
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'by'.

I guess the one you sent is an Oracle one..my database is Sql server 2000

Sorry, I found this posted under the Oracle PL/SQL Zone.

I don't have a SQL Server database here and my experience with it is pretty limited.  I could probably come up with something but without a place to test it, I don't want to guess.

Although the RDBMS is different, the theory should work:

The basic idea is to create a temporary table that has every day in the date range specified by the dates (this is the inline view in the query).
Join to this temporary table and count the number of days, grouping by month.

This is the Zone i selected
Zones:
Databases Miscellaneous, MS SQL Server, PL / SQL
 
I am just wondering why is it posted on Oracle/Pl-sql :-(

Do i need to re-post it to get the answer from a Sql Server Expert..Please suggest

SQL Server does not support "dual connect by level"...Emulating this will take an entire procedure...you're interested?

Yes definitely..I  found this link..I am looking some thing similar to this but its written in Microsoft access

https://www.experts-exchange.com/questions/21812658/Need-ansewr-fast-Get-list-of-months-between-two-dates.html

Thanks!
Sree.

I'm going to have to do this in pieces as I'm working on job-related items.  However, I think you'll find the following user-defined function to be of great assistance.  I've given you BIT functions that test a date for Start of Month or End of Month (returns 1 if true, 0 if false) as well as functions to return the start of this month, end of this month, and start of next month, as well as a function that computes Active Months as you described.  Using these functions, you should be able to get your column values (be sure to prefix them with dbo. when you use them, as all of them are compiled to the dbo user):

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE  FUNCTION dbo.fn_IsEndOfMonth( @testDate DATETIME) RETURNS BIT
AS
BEGIN
      RETURN (CASE WHEN MONTH(@testDate) <> MONTH(DATEADD(dd, 1.0, @testDate)) THEN 1 ELSE 0 END)
END
GO

CREATE  FUNCTION dbo.fn_IsStartOfMonth( @testDate DATETIME) RETURNS BIT
AS
BEGIN
      RETURN (CASE DAY(@testDate) WHEN 1 THEN 1 ELSE 0 END)
END
GO

CREATE  FUNCTION dbo.fn_StartDateOfNextMonth( @testDate DATETIME) RETURNS DATETIME
AS
BEGIN
      DECLARE @d DATETIME
      
      SET @d = DATEADD(m, 1.0, @testDate)
      RETURN CONVERT(DATETIME, CONVERT(VARCHAR(5), MONTH(@d)) + '/' + '01' + '/' + CONVERT(VARCHAR(5), YEAR(@d)))
END
GO

CREATE  FUNCTION dbo.fn_StartDateOfThisMonth( @testDate DATETIME) RETURNS DATETIME
AS
BEGIN
      RETURN CONVERT(DATETIME, '' + CONVERT(VARCHAR(5), MONTH(@testDate)) + '/' + '01' + '/' + CONVERT(VARCHAR(5), YEAR(@testDate)))
END
GO

CREATE  FUNCTION dbo.fn_EndDateOfThisMonth( @testDate DATETIME) RETURNS DATETIME
AS
BEGIN
      RETURN DATEADD(dd, -1.0, dbo.dbo.fn_StartDateOfNextMonth(@testDate))
END
GO

CREATE FUNCTION fn_ActiveMonths( @effectiveDate DATETIME, @endDate DATETIME) RETURNS INT
BEGIN
      DECLARE @rtn INT

      SET @rtn = 0
      SET @rtn = @rtn + (CASE DAY(@effectiveDate) WHEN 1 THEN 1 ELSE 0 END)
      SET @rtn = @rtn + (CASE WHEN MONTH(@endDate) <> MONTH(DATEADD(dd, 1.0, @endDate)) THEN 1 ELSE 0 END)
      SET @rtn = @rtn + DATEDIFF(mm, @effectiveDate, @endDate) - 1
      RETURN @rtn
END

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

I really need to display Active Month Value  as 2007Jan,2007Feb..etc
Any ideas?

Is it possible to convert below acces code to Sql server stored procedure..please getting close to the dead line..i really need this ASAP

create new table call it tblMonth
one field
strDatemonth text (50)
create new form put button on click event enter this code

dim i as integer
dim strmonth as string
dim newdate as date

dim db as DAO.database,rs as Dao.recordset
set db=currentdb
Set rs = db.OpenRecordset("Select * from  tblMonth", dbOpenDynaset)
for i=1 to 12
   newdate = DateAdd("m", i - 1, [startdate])
  rs.addnew
  rs!strDatemonth=month(newdate) & " " & year(newdate)  '<-This add to table
rs.update
next

I am some thing very similar to this.I got this code from the below link

https://www.experts-exchange.com/questions/21812658/Need-ansewr-fast-Get-list-of-months-between-two-dates.html

I am still for a response,Any help is appreciated..

what about using a function:

create function dbo.GetCustomerActiveData (@cust_id varchar(20))
returns @t table ( cust_id varchar(20), year_month varchar(20), percentage varchar(10))
as
begin
  declare @start_date date
  declare @end_date date
  declare @first_of_month date
  declare @first_of_next_month date
  declare @days_this_month

  select @start_date =effective_date , @end_date = end_date from yourtable where cust_id = @cust_id

  while @start_date <= @end_date
  begin
     set @first_of_month = dateadd(day, 1-datepart(day, @start_date), @start_date)
     set @first_of_next_month = dateadd(month, 1, @first_of_month)
 
     set @days_this_month = datepart(day, dateadd(day, -1, @first_of_next_month))

     if @start_date > @first_of_month
        set @days_this_month - datepart(day, @start_date) +1

     if @end_date < @first_of_next_month
        set @days_this_month - datepart(day, @end_date) + datepart(day, dateadd(day, -1, @first_of_next_month))

     insert into @t values
     ( @cust_id
     , datename(month, @start_date) + ' ' + cast(datepart(year, @start_date) as varchar(4))
     , cast( @days_this_month as varchar(10)) + '/' + cast ( datepart(day, dateadd(day, -1, @first_of_next_month)) as varchar(4))
     )

     set @start_date = @first_of_next_month
  end
 
  return
end



select * from dbo.GetCustomerActiveData ('c1' )

Here is the Function i exceuted with some minor changes.When i run the selct statement as mentioned its saying

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any Clue?????????

create function dbo.GetMemberActiveData (@memb_id varchar(20))
returns @t table ( memb_id varchar(20), year_month varchar(20), percentage varchar(10))
as
begin
  declare @start_date as datetime
  declare @term_date as datetime
  declare @first_of_month as datetime
  declare @first_of_next_month as datetime
  declare @days_this_month as int

set @start_date = (Select  eff_date from dbo.ENROLLMENT where  PAT_ID = @memb_id)
set @term_date = (Select  term_date from dbo.ENROLLMENT where  PAT_ID = @memb_id)


  while @start_date <= @term_date
  begin
     set @first_of_month = dateadd(day, 1-datepart(day, @start_date), @start_date)
     set @first_of_next_month = dateadd(month, 1, @first_of_month)
 
     set @days_this_month = datepart(day, dateadd(day, -1, @first_of_next_month))

     if @start_date > @first_of_month
        set @days_this_month = datepart(day, @start_date) + 1

     if @term_date < @first_of_next_month
        set @days_this_month = datepart(day, @term_date ) + datepart(day, dateadd(day, -1, @first_of_next_month))

     insert into @t values
     ( @memb_id
     , datename(month, @start_date) + ' ' + cast(datepart(year, @start_date) as varchar(4))
     , cast( @days_this_month as varchar(10)) + '/' + cast ( datepart(day, dateadd(day, -1, @first_of_next_month)) as varchar(4))
     )

     set @start_date = @first_of_next_month
  end
 
  return
end



select * from dbo.GetMemberActiveData('10018450100')

Never mind..My bad..got it corrected..will keep you posted on the results

I think we  need to change this function little bit as to get the results as below.I mean  it should calculate actual active days when the dates start and end in middle of the month .

cust_id   effective_date    end_date
c1            1/10/2007            5/15/2007

From the above data I need to get the out put as
cust_id   Year_month                              Active Days                                                                         Percentage

c1            Jan                  21(31(Total days in that month)-10(Active days from Effcetive date))         22/31
c1            feb                  28(as end date doesn't fall in this month its active for full month)                28/28
c1            March              31(as end date doesn't fall in this month its active for full month)                 31/31
c1            April                 30(as end date doesn't fall in this month its active for full month)                 30/30
c1           may                  16(31(Total days in that month)-15(Active days from Effcetive date))         17/31

Thanks a buch..It worked for me.