SQL SERVER Dates

Question

SET

DATEFIRST 7;

DECLARE

@yourDate DATEtime

declare

@tempDate datetime

declare

@Result table(startweek_date datetime,endweek_date datetime,actual_date datetime,weeknum int)

declare

@num int

set

@num = 12

SET

@yourDate = (select max(datedraw) from rslts where idpatient=1118202 and idtestnumber=2244)

while

@num > 0

begin

insert

into @Result values (DATEADD(dd, -(DATEPART(dw, @yourDate)-1), @yourDate),

(

DATEADD(dd, (7-1)-(DATEPART(dw, @yourDate)-1), @yourDate)),......................., DATEPART(wk,@yourDate))

set

@num = @num -1

set

@yourDate = DATEADD(day,-7,@yourDate)

end

select

* from @Result                          what should i write in that '...........' to get the dates in between startdateweek and enddateweek. I had a dates columns and should include the dates that comes between startdateweek and enddateweek. If there is no date in between a particular weeskstartdate and weekenddate then it should have NULL value. Note: Only recent 12 dates sholud come

---

naresh arjala

Answer 1

Hi Naresh,

Instead of Providing Code,can you please provide some sample data and expecting result from it.

Answer 2

Hi Ramesh,

I need the columns in this manner

startweek_date                              date_in_the_col between startweek and end week date)                             endweek_date

2012-01-08 00:00:00.000                             2012-01-10 00:00:00.000                                                          2012-01-14 00:00:00.000

2012-01-01 00:00:00.000                            2012-01-06 00:00:00.000                                                             2012-01-07 00:00:00.000

Like this I need 12 weeks dates comparing with the max date in dates column bcoz I need results for latest 12 weeks.

if there are no dates (2nd column) between startweek_date and endweek_date then it should show NULL there.

Plsss do reply!!!

---

naresh arjala

Answer 3

You can try this

SET DATEFIRST 7;
DECLARE @yourDate DATEtime;
declare @tempDate datetime;
declare @Result table(startweek_date datetime,endweek_date datetime,weeknum int)

declare @num int;

set @num = 12

SET @yourDate = GETDATE()

;with cte as (
select 1 num , @yourDate Dt
union all 
select num+1 , dateadd(d,num*-7,@yourDate) from cte where num<@num)

select  num, (DATEADD(dd, -(DATEPART(dw, Dt-1)), dt)), dateadd(dd,6,DATEADD(dd, -(DATEPART(dw, dt)-1), dt)),datepart( WK,(DATEADD(dd, -(DATEPART(dw, dt)-1), dt))) 
FROM cte 
Order by num 

Have a compare to actual date column and place null if it doesnt fall between the week start and weekend

Regards
Satheesh

Answer 4

I got what you are intending to explain but i want an additional column that contains recent 12 dates from a date column

between the startweekdate and dweekdate. If there are no dates in that range then it should show NULL value

startweek_date                              date_in_the_col between startweek and end week date)                             endweek_date

2012-01-08 00:00:00.000                             2012-01-10 00:00:00.000                                                          2012-01-14 00:00:00.000

2012-01-01 00:00:00.000                            2012-01-06 00:00:00.000                                                             2012-01-07 00:00:00.000

Here I am comparing start date with maximum date in the dates column... Plss explain how to insert one more column to get the dates in between

startweekdate and endweekdate.

---

naresh arjala

Answer 5

>>>I got what you are intending to explain but i want an additional column that contains recent 12 dates from a date column between the startweekdate and dweekdate. If there are no dates in that range then it should show NULL value

I am not understanding what you exactly need

startweek_date         date_in_the_col between startweek and end week date)   endweek_date

2012-01-08 00:00:00.000                     2012-01-10 00:00:00.000                   2012-01-14 00:00:00.000

2012-01-01 00:00:00.000                       2012-01-06 00:00:00.000                   2012-01-07 00:00:00.000

From  where the date_in_col comes?

Here I am comparing start date with maximum date in the dates column... Plss explain how to insert one more column to get the dates in between startweekdate and endweekdate

do you want to list down all the dates between startweekdate and endweekdate? and how you are going to accomodate all in one single column?

Regards
Satheesh

Answer 6

Hi Satheesh,

I have a separate table called rslts which contains a column called 'datedraw'

I want to get last recent 12 weeks dates from this table and I need to insert these date values into the temp table

'@Resullt_table' along with weekstartdate, weekenddate and weeknumber.

Plsss help me!!!

---

---

naresh arjala

Answer 7

I am not sure if i understand your requirement clearly, here is my guess

declare @myanothertable table(Id int identity(1,1),datedraw datetime)
declare @Resullt_table table(startweek_date datetime,        date_in_the_col datetime,   endweek_date datetime)
insert into @myanothertable values
(dateadd(year,-1,GETDATE())),
(dateadd(month,-12,GETDATE())),
(dateadd(month,-11,GETDATE())),
(dateadd(month,-10,GETDATE())),
(dateadd(month,-9,GETDATE())),
(dateadd(month,-8,GETDATE())),
(dateadd(month,-7,GETDATE())),
(dateadd(month,-6,GETDATE())),
(dateadd(month,-5,GETDATE())),
(dateadd(month,-4,GETDATE())),
(dateadd(month,-3,GETDATE())),
(dateadd(month,-2,GETDATE())),
(dateadd(month,-1,GETDATE())),
(dateadd(day,-1,GETDATE())),
(dateadd(day,-2,GETDATE())),
(dateadd(day,-3,GETDATE())),
(dateadd(day,-4,GETDATE())),
(dateadd(day,-5,GETDATE())),
(dateadd(day,-6,GETDATE()))


declare @num int=12

declare @yourDate datetime= (select max(datedraw) from @myanothertable where id=11);

with cte as (
select 1 num , @yourDate Dt
union all 
select num+1 , dateadd(d,num*-7,@yourDate) from cte where num<@num),
CTE_Weeks
as(select  num, (DATEADD(dd, -(DATEPART(dw, Dt-1)), dt)) WeekStart, 
dateadd(dd,6,DATEADD(dd, -(DATEPART(dw, dt)-1), dt))Weekend,
datepart( WK,(DATEADD(dd, -(DATEPART(dw, dt)-1), dt)))  WeekNum
FROM cte )
select WeekStart,Weekend,case when @yourDate between weekstart and Weekend then @yourDate else NULL end from CTE_Weeks

Please post DDL(Table structures), sample data and expected results rather which would set the expectations much clear. 

Regards
Satheesh

Answer 8

these are my codes and results

this is the result i need

but the problem is if there are no values with in a particular week range then it should show null value

Actually i need 12 weeks startweek dates endweek dates(by comparing max of datedraw)and i need to insert 12 datedraw values from rslts table that fits with in this week ranges.

If there is no value in a particular week range then it should show a null value(That is what i am not able to do)

Need your help Satheesh!!!

---

naresh arjala

Answer 9

You can use something like this

case when datedraw between (DATEADD(dd, -(DATEPART(dw, Dt-1)), dt))  and dateadd(dd,6,DATEADD(dd, -(DATEPART(dw, dt)-1), dt)) then DateDraw else NULL end 

Regards
Satheesh

Answer 10

insert into @Result  
select DATEADD(dd, -(DATEPART(dw, @yourDate)-1), @yourDate),
Dateadd(dd,(7-1)-(datepart(dw,@yourdate)-1,@yourdate),
 case when 
      datedraw between  DATEADD(dd, -(DATEPART(dw, @yourDate)-1), @yourDate)  
        and Dateadd(dd,(7-1)-(datepart(dw,@yourdate)-1,@yourdate)) then 
                 DateDraw else NULL end,
datepart(wk,datedraw) 
from rslts

Regards
Satheesh

Answer 11

It almost worked for me Satheesh.

Thanks a lot.

But the problem here is I need to insert null values for actualdate and results when actualdate doesnot lie between

week range but I am getting 348 rows instead of 12 rows.

---

naresh arjala

Answer 12

Can you please post some data and expected result?  

I think you can have a TOP 1 statement. I believe that should solve your problem, btw if you use a  CTE as in my previous post, you can get rid of the while loop

insert into @Result  
select TOP 1 DATEADD(dd, -(DATEPART(dw, @yourDate)-1), @yourDate),
Dateadd(dd,(7-1)-(datepart(dw,@yourdate)-1,@yourdate),
 case when 
      datedraw between  DATEADD(dd, -(DATEPART(dw, @yourDate)-1), @yourDate)  
        and Dateadd(dd,(7-1)-(datepart(dw,@yourdate)-1,@yourdate)) then 
                 DateDraw else NULL end,
datepart(wk,datedraw) 
from rslts

Regards
satheesh

Answer 13

Consider implementing it with Dan Guzman's calendar table:

http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx

The trouble with implementing it with complex query: nobody can decypher it the day after tomorrow.

---

Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 14

First of all Thanks for your patience Satheesh. I am a fresher so finding it a bit difficult.

In the above fig you can see instead of 12 weeks I am able to get only 7 weeks bcoz actual date values are not there

in those five week ranges so now the problem is I would like to see the remaining 5 weeks data also

for which the actual date and results columns should be null.

Finally I need 12 weeks results!!!

---

naresh arjala

Answer 15

Try this,

SET DATEFIRST 7;

declare @tempDate datetime;
declare @Result table(
				startweek_date datetime,
				endweek_date datetime,
				actual_date datetime,
				weeknum int);

declare @num int;
set @num = 12;
DECLARE @yourDate datetime= (select max(datedraw) from rslts where idpatient=1118202 and idtestnumber=2244);

begin	
	
	with cte as (
		select 1 num , @yourDate Dt
			union all 
			select num+1 , dateadd(d,num*-7,@yourDate) from cte where num<@num),
		CTE_Weeks
		as(select  num, (DATEADD(dd, -(DATEPART(dw, Dt-1)), dt)) WeekStart, 
		dateadd(dd,6,DATEADD(dd, -(DATEPART(dw, dt)-1), dt))Weekend,
		datepart( WK,(DATEADD(dd, -(DATEPART(dw, dt)-1), dt)))  WeekNum
	FROM cte),
	Cte_rslts as (select top 12 *,ROW_NUMBER()Over(order by datedraw desc) num from rslts where {put all the conidtions here}	)
	select weekstart, weekend, weeknum , case when datedraw between weekstart and weekend then datedraw else null end actualdate from cte left join Cte_rslts on cte.num=Cte_rslts.num
	
end

select * from @Result        ;

Regards
Satheesh