Calulate WeekBegin Date and End Date based on Current Friday Date Dynamically

Question

Post reply

Calulate WeekBegin Date and End Date based on Current Friday Date Dynamically

suresh0534

SSCommitted

Points: 1966
More actions
March 9, 2015 at 10:10 am

#316063

Hi,

How to Calculate week begin date and end date dynamically based on Current Friday Date and needs to pass those Dates to Sql Query.

Below is sample , please look into this and help.

Create table #Register(

Name varchar(50),

c_id int,

created_date datetime)

insert into #Register(Name,c_id,created_date)

select 'Morgan',1,'01/05/2015' union all

select 'Clark',2,'01/05/2015' union all

select 'Clark',3,'01/06/2015' union all

select 'Morgan',4,'01/07/2015' union all

select 'Morgan',5,'01/07/2015' union all

select 'Hales',6,'01/13/2015' union all

select 'Morgan',7,'01/13/2015' union all

select 'Clark',8,'01/14/2015' union all

select 'Clark',9,'01/14/2015' union all

select 'Morgan',10,'01/20/2015' union all

select 'Clark',11,'01/21/2015' union all

select 'Clark',12,'01/21/2015' union all

select 'Morgan',13,'01/21/2015' union all

select 'Morgan',14,'01/22/2015' union all

select 'Hales',15,'01/22/2015' union all

select 'Morgan',16,'01/23/2015' union all

select 'Clark',17,'01/23/2015' union all

select 'Clark',18,'01/23/2015' union all

select 'Morgan',10,'02/02/2015' union all

select 'Clark',11,'02/02/2015' union all

select 'Clark',12,'02/03/2015' union all

select 'Morgan',13,'02/03/2015' union all

select 'Morgan',14,'02/03/2015' union all

select 'Hales',15,'02/04/2015' union all

select 'Morgan',16,'02/04/2015' union all

select 'Clark',17,'02/04/2015' union all

select 'Clark',18,'02/05/2015' union all

select 'Morgan',10,'02/10/2015' union all

select 'Clark',11,'02/10/2015' union all

select 'Clark',12,'02/10/2015' union all

select 'Morgan',13,'02/10/2015' union all

select 'Morgan',14,'02/11/2015' union all

select 'Hales',15,'02/12/2015' union all

select 'Morgan',16,'02/12/2015' union all

select 'Clark',17,'02/12/2015' union all

select 'Clark',18,'02/12/2015'

select * from #Register

Name c_id created_date

Morgan 1 2015-01-05 00:00:00.000

Clark 2 2015-01-05 00:00:00.000

Clark 3 2015-01-06 00:00:00.000

Morgan 4 2015-01-07 00:00:00.000

Morgan 5 2015-01-07 00:00:00.000

Hales 6 2015-01-13 00:00:00.000

Morgan 7 2015-01-13 00:00:00.000

Clark 8 2015-01-14 00:00:00.000

Clark 9 2015-01-14 00:00:00.000

Morgan 10 2015-01-20 00:00:00.000

Clark 11 2015-01-21 00:00:00.000

Clark 12 2015-01-21 00:00:00.000

Morgan 13 2015-01-21 00:00:00.000

Morgan 14 2015-01-22 00:00:00.000

Hales 15 2015-01-22 00:00:00.000

Morgan 16 2015-01-23 00:00:00.000

Clark 17 2015-01-23 00:00:00.000

Clark 18 2015-01-23 00:00:00.000

Morgan 10 2015-02-02 00:00:00.000

Clark 11 2015-02-02 00:00:00.000

Clark 12 2015-02-03 00:00:00.000

Morgan 13 2015-02-03 00:00:00.000

Morgan 14 2015-02-03 00:00:00.000

Hales 15 2015-02-04 00:00:00.000

Morgan 16 2015-02-04 00:00:00.000

Clark 17 2015-02-04 00:00:00.000

Clark 18 2015-02-05 00:00:00.000

Morgan 10 2015-02-10 00:00:00.000

Clark 11 2015-02-10 00:00:00.000

Clark 12 2015-02-10 00:00:00.000

Morgan 13 2015-02-10 00:00:00.000

Morgan 14 2015-02-11 00:00:00.000

Hales 15 2015-02-12 00:00:00.000

Morgan 16 2015-02-12 00:00:00.000

Clark 17 2015-02-12 00:00:00.000

Clark 18 2015-02-12 00:00:00.000

I need to run Job on Every friday.

Based on Current Date on Friday need to Calculate how many c_id's are created on previous Month and current week(begin and enddates) based on dates Created_date.

Ex: Based on Current Friday Date need to calculate Week Begin dates and End Dates.

Here I am running this Sql Job on Feb 13th 2015.

So Automatically I need to Calculate the January-to -Feb up to 13th 2015.

Select Name, COUNT(C_id) from #Register Where Created_date between '01/01/2015' and '01/02/2015' GROUP BY NAME

Select Name, COUNT(C_id) from #Register Where Created_date between '01/03/2015' and '01/09/2015' GROUP BY NAME

Select Name, COUNT(C_ID) from #Register Where Created_date between '01/10/2015' and '01/16/2015' GROUP BY NAME

Select Name, COUNT(C_ID) from #Register Where Created_date between '01/17/2015' and '01/23/2015' GROUP BY NAME

Select Name, COUNT(C_ID) from #Register Where Created_date between '01/24/2015' and '01/30/2015' GROUP BY NAME

Select Name, COUNT(C_ID) from #Register Where Created_date between '01/31/2015' and '02/06/2015' GROUP BY NAME

Select Name, COUNT(C_ID) from #Register Where Created_date between '02/07/2015' and '02/13/2015' GROUP BY NAME

The output should be like if i am running on Feb13th 2015: (It needs to generate automatically)

Name C_id_Jan1stweek C_id_Jan2ndweek C_id_Jan3rdweek C_id_JanthdWeek C_id_jan5thWeek C_id_Feb1stWeek C_id_Feb2ndWeek

Clark 0 2 4 0 4 4 0

Morgan 0 1 4 0 4 4 0

Hales 0 1 1 0 1 1 0

The output should be like if i am running on Jan3oth 2015: (It needs to generate automatically)

Name C_id_Jan1stweek C_id_Jan2ndweek C_id_Jan3rdweek C_id_JanthdWeek C_id_jan5thWeek

Clark 0 2 4 0 4

Morgan 0 1 4 0 4

Hales 0 1 1 0 1

If i am running on August...Then It needs to calculate from Jan-Aug.

Thanks,

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Weeks only start the same time a month starts one out of 7 times over a long period.

With that in mind, the first thing we need to know is what determines which month any given week is in and what is the starting day of the week? Based on what you posted for output requirements, it would appear that your reporting weeks go from Saturday to Friday (which also means that a part of the current Friday will be missing data depending on what time the job runs at).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

suresh0534 SSCommitted Points: 1966 More actions · Answer 2

suresh0534

SSCommitted

Points: 1966

March 9, 2015 at 12:19 pm

#1782669

Hi,

Thanks for your reply.

We need to run Friday at the End of the Day.

Thanks,

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

suresh0534 (3/9/2015)

Hi,

Thanks for your reply.

We need to run Friday at the End of the Day.

Thanks,

That's good. Would you please answer the other questions I had? Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

suresh0534 SSCommitted Points: 1966 More actions · Answer 4

Hi,

Sorry some mistake.

It need to calculate between Week Begin Date and End Date.

If i run the Job on January 9th.

January 1st week we have only 2 Dates Jan 1st and Jan 2nd.

Select Name, COUNT(C_id) from #Register Where Created_date between '01/01/2015' and '01/02/2015' GROUP BY NAME

Select Name, COUNT(C_id) from #Register Where Created_date between '01/03/2015' and '01/09/2015' GROUP BY NAME

If i run the Job on Feb 6th.

Select Name, COUNT(C_id) from #Register Where Created_date between '01/01/2015' and '01/02/2015' GROUP BY NAME

Select Name, COUNT(C_id) from #Register Where Created_date between '01/03/2015' and '01/09/2015' GROUP BY NAME

Select Name, COUNT(C_ID) from #Register Where Created_date between '01/10/2015' and '01/16/2015' GROUP BY NAME

Select Name, COUNT(C_ID) from #Register Where Created_date between '01/17/2015' and '01/23/2015' GROUP BY NAME

Select Name, COUNT(C_ID) from #Register Where Created_date between '01/24/2015' and '01/30/2015' GROUP BY NAME

Select Name, COUNT(C_ID) from #Register Where Created_date between '01/31/2015' and '02/06/2015' GROUP BY NAME

Select Name, COUNT(C_ID) from #Register Where Created_date between '02/07/2015' and '02/13/2015' GROUP BY NAME

Thanks,

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

I got that the first time around. You're not reading my questions that I previously posted. You want the output to identify which week of each month is being presented. WHAT RULE ARE YOU FOLLOWING TO DETERMINE WHAT THE FIRST WEEK OF ANY GIVEN MONTH IS???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

just a thought......have you considered using a calendar table to define your "weeks"?

weeks can be a real PITA....every time I go near them, the powers that be have different ideas on what constitutes a week and when it starts and finishes.......even within the same company ! ...add that into fiscal years / holidays / weekends yadda yadda and it gets funky pretty quickly.....then someone wants a week/same week last year comparison.

as I said just a thought....good luck

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

suresh0534 SSCommitted Points: 1966 More actions · Answer 7

Hi,

I need to run this Job On every week on Friday.

If i run the Job On Middle of Feb on Friday, it needs to calculate From Jan-Feb of Current Friday.

How many Weeks Begin Dates and End Dates in January and up to Current Month of Friday also needs to

calculate the week begin and End Dates.

But i dont know how to calculate dynamically Week Begin and End dates.

Thanks

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

suresh0534 (3/9/2015)

Hi,

I need to run this Job On every week on Friday.

If i run the Job On Middle of Feb on Friday, it needs to calculate From Jan-Feb of Current Friday.

How many Weeks Begin Dates and End Dates in January and up to Current Month of Friday also needs to

calculate the week begin and End Dates.

But i dont know how to calculate dynamically Week Begin and End dates.

Thanks

Do you want the output with the column names you posted or not? As a reminder, here's what you posted in your original post...



Name C_id_Jan[font="Arial Black"]1stweek[/font] C_id_Jan[font="Arial Black"]2ndweek[/font] C_id_Jan[font="Arial Black"]3rdweek[/font] C_id_JanthdWeek C_id_jan[font="Arial Black"]5thWeek[/font] C_id_Feb1stWeek C_id_Feb2ndWeek

Since January 1st occurred on a THURSDAY, do you want that week to be the first week of January or the Last week of December of the previous year. Like I said... WHAT RULE ARE YOU FOLLOWING TO DETERMINE WHAT THE FIRST WEEK OF ANY GIVEN MONTH IS???

If you only want the dates, no problem but that's not what you requested and I'm just trying to figure out what you actually want for the output.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

suresh0534 SSCommitted Points: 1966 More actions · Answer 9

suresh0534

SSCommitted

Points: 1966

March 9, 2015 at 5:03 pm

#1782734

Hi,

Thanks for your reply,

I Need show Column names as End dates with concatenation like below.

January 1st has Occured on Thursday so it should be Last Week of December.

01/09/2015 is First Week End Date.

Examle:

Name Week1 01/09/2015 Week2 01/06/2015 Week3 01/23/2015 Week4 01/30/2015

Thanks,