# Calulate WeekBegin Date and End Date based on Current Friday Date Dynamically. [Answered]RSS

## 2 replies

Last post Mar 10, 2015 11:12 AM by sureshtalla

Member

65 Points

326 Posts

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

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.

```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 dynamically.

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.

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
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,```

skg

Star

8536 Points

1376 Posts

Microsoft

### Re: Calulate WeekBegin Date and End Date based on Current Friday Date Dynamically.

Mar 10, 2015 03:03 AM|Edwin Guru Singh|LINK

Hi Sureshtalla,
As per your case , I have been shared some ideas with the help of my workout like below :

1. First of all, I created user-defined function for split the week range (Saturday to Friday) from the given date as per your expectation .
For that you have to pass Start_date & End_Date

```Create FUNCTION [dbo].[UDF_DateRange]
(
@StartDate  DATETIME,
@EndDate    DATETIME
)
RETURNS
@SelectedRange    TABLE
(  FromDate DATETIME,
ToDate DATETIME)
AS
BEGIN
DECLARE @startDate1 AS datetime
-- Get the nearest Friday date from given date  SET @startDate1 = CASE
WHEN DATEPART(dw, @StartDate) <3
THEN DATEADD(d, -1*(DATEPART(dw, @StartDate) + 1)+7, @StartDate)
ELSE DATEADD(d, DATEPART(dw, @StartDate)*-1 + 6, @StartDate)
END;

;WITH cteRange (StartRange,EndRange) AS (
SELECT @startDate,@startDate1
UNION ALL
FROM cteRange
WHERE EndRange <= DATEADD(dd, -7, @EndDate)                  )

--Insert the daterange values into table variable      INSERT INTO @SelectedRange (FromDate,ToDate)
SELECT StartRange,EndRange
FROM cteRange
OPTION (MAXRECURSION 3660);
RETURN
END
```

Check the dateRange as you expected by using this User-defined function :

`SELECT FromDate,ToDate FROM [dbo].[UDF_DateRange]( '04/01/2015', '04/29/2015')`

Then apply this user defined function in your query :

```select A.name,a.c_id,Convert(varchar,A.FromDate,111)+' - '+Convert(varchar,A.ToDate,111)[Week],
CASE WHEN A.created_date between A.FromDate and A.ToDate THEN 1
ELSE 0
END[count]
from
(select R.*,B.* from #Register[R]
Left outer join
(Select FromDate,ToDate FROM [dbo].[DateRange]( '01/01/2015', '02/13/2015')
)[B] on 1=1
)[A]```

I have been workout until this level.hereafter you can convert the rows(Week_column) into dynamic column  by using PIVOT Concept.
Note : SUM(COUNT column) while doing PIVOT

Hopefully this will be helpful for you.
--
with regards,
Edwin

with regards,
Edwin

Member

65 Points

326 Posts