none
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

    Monday, January 21, 2013 10:06 AM

Answers

  • 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

    Wednesday, January 23, 2013 9:31 AM

All replies

  • Hi Naresh,

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

    Monday, January 21, 2013 10:23 AM
  • 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

    Monday, January 21, 2013 10:59 AM
  • 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


    Monday, January 21, 2013 11:38 AM
  • 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

    Monday, January 21, 2013 12:17 PM
  • >>>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

    Monday, January 21, 2013 1:57 PM
  • 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

    Wednesday, January 23, 2013 3:41 AM
  • 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


    Wednesday, January 23, 2013 5:57 AM
  • 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

    Wednesday, January 23, 2013 6:14 AM
  • 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

    Wednesday, January 23, 2013 6:25 AM
  • 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

    Wednesday, January 23, 2013 6:31 AM
  • 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

    Wednesday, January 23, 2013 8:29 AM
  • 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
    Wednesday, January 23, 2013 8:50 AM
  • 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


    Wednesday, January 23, 2013 9:10 AM
  • 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

    Wednesday, January 23, 2013 9:16 AM
  • 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

    Wednesday, January 23, 2013 9:31 AM