Report every week, beginning 1st of month and ending last day of month

Question

Post reply

Report every week, beginning 1st of month and ending last day of month

PJ_SQL

SSCertifiable

Points: 6703
More actions
October 31, 2019 at 1:49 pm

#3693531

Hello!
I need to run weekly report for each month on Friday, beginning the first day of each month, and ending the last day of the month
Example
·         10-1-2019 thru 10-4-2019
·         10-5-2019 thru 10-11-2019
·         10-12-2019 thru 10-18-2019
·         10-19-2019 thru 10-25-2019
·         10-26-2019 thru 10-31-2019
How can this be achieved?

Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

This is a rather nebulous request.

Which part of this would you like help with?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 2

PJ_SQL

SSCertifiable

Points: 6703

October 31, 2019 at 2:28 pm

#3693556

10-1-2019 thru 10-4-2019

Thanks.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

PJ_SQL wrote:

10-1-2019 thru 10-4-2019
Thanks.

That really helps, thanks.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Collins SSCrazy Points: 2157 More actions · Answer 4

To split the partial weeks use GROUP BY month. To get the weeks to align with your preferred days use dateadd to move the window. To generate dates this code (copy, paste, run) uses the dbo.daterange function described here:

https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

declare
  @start_dtdate='2019-10-01',
  @end_dtdate='2019-12-31';

with wk_daterange_cte as(
select 
  datepart(mm, x.[value]) mo,
  datepart(wk, dateadd(day, 1, cast(x.[value] as datetime))) wk, 
  min(x.[value]) wk_start_dt,
  max(x.[value]) wk_end_dt
from
  dbo.daterange(@start_dt, @end_dt, 'dd', 1) x
group by
  datepart(mm, x.[value]),
  datepart(wk, dateadd(day, 1, cast(x.[value] as datetime))))
select
  convert(varchar(10), wk_start_dt, 110) + ' thru ' + convert(varchar(10), wk_end_dt, 110) week_daterange
from
  wk_daterange_cte
order by
  wk_start_dt;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

PJ_SQL wrote:

Hello!
I need to run weekly report for each month on Friday, beginning the first day of each month, and ending the last day of the month
Example
·         10-1-2019 thru 10-4-2019
·         10-5-2019 thru 10-11-2019
·         10-12-2019 thru 10-18-2019
·         10-19-2019 thru 10-25-2019
·         10-26-2019 thru 10-31-2019
How can this be achieved?
Thanks in advance

I think maybe some clarification is needed - are you asking to start at the beginning of the month, find the first Friday and get some data for that period - then move to the next week and get 7 days data, then repeat the process until you get to the end of the month?

MVDBA