none
Get Last week Last year and this week last year Data in sql

    질문

  • Hello,

    I am having a problem to query Last week Last year and this week last year Data.

    Below query is working but giving only last week last year not this week last year.

    DATEPART(wk, DATE_Column) = DATEPART(wk, GETDATE()) - 1
    AND YEAR(DATE_column ) = YEAR(GETDATE()) - 1

    Below also giving same result

    DECLARE @LastYear DATE = DATEADD(year,-1,GETDATE())

    Select * from DateTable

    where Date_column BETWEEN DATEADD(dd,-6-DATEPART(dw,@LastYear),@LastYear)AND DATEADD(dd,-DATEPART(dw,@LastYear),@LastYear) 

    Please Help.



    • 편집됨 Victory5 2018년 6월 14일 목요일 오후 10:06
    2018년 6월 14일 목요일 오후 9:58

모든 응답

  • Just curious... What do you get with this?

    WHERE Date_Column BETWEEN DATEADD(dd,-6,@LastYear) AND @LastYear

    2018년 6월 14일 목요일 오후 10:16
  • Last Week  03-June-2018 to 08-Jun- 2018

    This Week  10-June-2018 to today

    Last Week Last week  04-Jun-2017 to 10-Jun-2017

    Last Year This week  11-Jun-2017 to 17-Jun- 2107

    Your result is: 2017-06-08 to 2017-06-14 



    • 편집됨 Victory5 2018년 6월 14일 목요일 오후 10:32
    2018년 6월 14일 목요일 오후 10:32
  • last week

    SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7 * 7,-8) AS STartDate,DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,-2) AS ENdDate


    This week

    SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7 * 7,-1) AS StartDate, DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7 * 7,4) AS EndDate


    Last year last week

    SELECT DATEADD(wk,DATEPART(wk,GETDATE()) -2,DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)) AS StartDate,DATEADD(wk,DATEPART(wk,GETDATE()) -2,DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,6)) AS EndDate

    Last year this week

    SELECT DATEADD(wk,DATEPART(wk,GETDATE()) -1,DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)) AS StartDate,DATEADD(wk,DATEPART(wk,GETDATE()) -1,DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,6)) AS EndDate


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 6월 14일 목요일 오후 11:15
  • Last Week  03-June-2018 to 08-Jun- 2018

    This Week  10-June-2018 to today

    Last Week Last week  04-Jun-2017 to 10-Jun-2017

    Last Year This week  11-Jun-2017 to 17-Jun- 2107

    Your result is: 2017-06-08 to 2017-06-14 

    Hi Victory,

    In fact, the date conditions could be written like this.

    CREATE TABLE CALCULATE_DATE
    (
     V_DATE DATE,
     V_WEEK INT
    )
    
    ;WITH CTE AS 
    (
     SELECT CAST('2017-01-01' AS DATE) AS V_DATE,DATEPART(WEEK,CAST('2017-01-01' AS DATE)) AS V_WEEK
     UNION ALL
     SELECT DATEADD(DAY,1,V_DATE),DATEPART(WEEK,DATEADD(DAY,1,V_DATE)) FROM CTE WHERE V_DATE<'2018-12-31'
    )
    INSERT INTO CALCULATE_DATE
    SELECT V_DATE,V_WEEK FROM CTE 
    option (maxrecursion 0)
    
    
    --Last Week  03-June-2018 to 08-Jun- 2018
    SELECT * FROM CALCULATE_DATE WHERE YEAR(V_DATE)=YEAR(GETDATE()) AND DATEPART(WEEK,V_DATE)=DATEPART(WEEK,GETDATE())-1 
    
    --This Week  10-June-2018 to today
    SELECT * FROM CALCULATE_DATE WHERE YEAR(V_DATE)=YEAR(GETDATE()) AND DATEPART(WEEK,V_DATE)=DATEPART(WEEK,GETDATE())
    
    --Last Year Last week  04-Jun-2017 to 10-Jun-2017
    SELECT * FROM CALCULATE_DATE WHERE YEAR(V_DATE)=YEAR(GETDATE())-1 AND DATEPART(WEEK,V_DATE)=DATEPART(WEEK,GETDATE())-1
    
    
    --Last Year This week  11-Jun-2017 to 17-Jun- 2107
    SELECT * FROM CALCULATE_DATE WHERE YEAR(V_DATE)=YEAR(GETDATE())-1 AND DATEPART(WEEK,V_DATE)=DATEPART(WEEK,GETDATE())

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 6월 15일 금요일 오전 3:22
    중재자
  • Your mindset is still stuck in procedural code. But SQL is a table driven language; we'd rather do joins and lookups than computations. I'm going to assume that you know what a calendar table is. You just need to add an extra column to it.

    The ISO 8601 standard includes a display format for dates, based on weeks. It looks like "yyyyW[0-5][0-9]-[1-7] " for the first four digits for the usual common error year, the letter W is a separator, followed by the week within the year number (01 through 52 or 53) , and finally the day of the week, where one is Monday and seven is Sunday.

    Build a calendar lookup table for how many years you need. 1 or 200 years is probably more than enough. You can actually look up calendars in this format on the Internet, cut, paste and edit them into an insert into statement.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    2018년 6월 15일 금요일 오후 8:36
  • Try this..

    create table test(val int, DATE_Column date)
    
    insert into test values(1, '2018-06-16')
    insert into test values(1, '2018-06-08')
    insert into test values(1, '2017-06-16')
    insert into test values(1, '2017-06-15')
    insert into test values(1, '2017-06-09')
    insert into test values(1, '2017-06-08')
    insert into test values(1, '2016-06-08')
    insert into test values(1, '2016-06-08')
    insert into test values(1, '2015-06-08')
    insert into test values(1, '2015-06-08')
    
    select * from test where
    
    (DATE_Column between DATEADD(yy, -1, DATEADD(dd, -(DATEPART(dw, getdate())-1), getdate())) and 
    
    DATEADD(yy, -1, DATEADD(dd, 7-(DATEPART(dw, getdate())), getdate())))
    or
    (DATE_Column between DATEADD(yy, -1, DATEADD(dd, -(DATEPART(dw, getdate())+6), getdate())) and 
    
    DATEADD(yy, -1, DATEADD(dd, -(DATEPART(dw, getdate())), getdate())))
    2018년 6월 16일 토요일 오전 7:10
  • DECLARE @LastYear DATE = DATEADD(YEAR, -1, GETDATE());
    
    SELECT	*
    FROM	dbo.DateTable
    WHERE	Date_column >= @LastYear
    	AND Date_column < DATEADD(DAY, 7, @LastYear);
    


    N 56°04'39.26"
    E 12°55'05.63"

    2018년 6월 16일 토요일 오후 8:40
  • DECLARE	@Today DATE = '20180616'-- GETDATE();
    
    SELECT	DATEADD(DAY, DATEDIFF(DAY, -1, @Today) / 7 * 7, -1), -- Sunday this week this year,
    	DATEADD(DAY, DATEDIFF(DAY, -1, @Today) / 7 * 7,  6)  -- Sunday next week this year;
    
    /* This week this year
    	SELECT	* 
    	FROM	dbo.DateTable 
    	WHERE	Date_Column >= DATEADD(DAY, DATEDIFF(DAY, -1, @Today) / 7 * 7, -1)
    		AND Date_Column >= DATEADD(DAY, DATEDIFF(DAY, -1, @Today) / 7 * 7,  6);
    */
    
    SELECT	DATEADD(DAY, DATEDIFF(DAY, -1, DATEADD(YEAR, -1, @Today)) / 7 * 7, -1), -- Sunday this week previous year,
    	DATEADD(DAY, DATEDIFF(DAY, -1, DATEADD(YEAR, -1, @Today)) / 7 * 7,  6)  -- Sunday next week previous year;
    
    /* This week previous year
    	SELECT	* 
    	FROM	dbo.DateTable 
    	WHERE	Date_Column >= DATEADD(DAY, DATEDIFF(DAY, -1, DATEADD(YEAR, -1, @Today)) / 7 * 7, -1)
    		AND Date_Column >= DATEADD(DAY, DATEDIFF(DAY, -1, DATEADD(YEAR, -1, @Today)) / 7 * 7,  6);
    */
    


    N 56°04'39.26"
    E 12°55'05.63"

    • 답변으로 제안됨 SwePesoMVP 2018년 6월 17일 일요일 오후 5:08
    2018년 6월 16일 토요일 오후 8:47