CODEWITHSUNDEEP
sqlsql-server
Sandeep
Sandeep

Reputation: 31

How to calculate StartDate and EndDate based on ReportingMonth value

I Need to calculate StartDate and EndDate based on ReportingMonth value

sample data:

CREATE TABLE TestDate (
    [ID] [int] NULL,
    [PerID] [int] NULL,
    [CommID] [int] NULL,
    [ReportingMonth] [nvarchar](200) NULL,
    [Number] [nvarchar](200) NULL
)

Insert into TestDate ( [ID],[PerID],[CommID],[ReportingMonth],[Number])
    Values ('3820','12508','7','Jul-16','31');
Insert into TestDate ( [ID],[PerID],[CommID],[ReportingMonth],[Number])
    Values ('10235','12498','58','Aug-16','1');
Insert into TestDate ( [ID],[PerID],[CommID],[ReportingMonth],[Number])
    Values ('10235','12498','2','Jul-16','15');

I need to calculate StartDate and EndDate based on ReportingMonth with the difference of Number column

Expected output for StartDate and EndDate should be like this

ID  PerID   CommID  ReportingMonth  StartDate   Number  EndDate
3820    12508   7   Jul-16          30/06/2016  31      31/07/2016
10235   12498   58  Aug-16          15/07/2016  1       1/08/2016
10235   12498   2   Jul-16          15/07/2016  15      1/08/2016

SQL Query that I used to calculate start date for one month is working but when there are more than one same ID then it is not working 

select [ID]
       ,[PerID]
       ,[CommID]
       ,[ReportingMonth]
       ,[Number]
       ,DATEADD(DAY, -(cast([Number] as int)), eomonth(CAST([Number] as DateTime)))  as StartDate
    from TestDate

How to get StartDate and EndDate base on just having only ReportingMonth field

Thanks, Sandeep

Upvotes: 1

Views: 108

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269713

SQL Server is pretty good about figuring out month conversions. So, this should work:

select cast('01-' + ReportingMonth as date) as month_start,
       eomonth(cast('01-' + ReportingMonth as date)) as month_end

Upvotes: 0

Related Questions