none
I want to retrieve data between recent days RRS feed

  • Question

  • I have requirement like this: I need to get the data between specific date range, like when i enter date range consider i entered 2017-05-01 and 2017-05-31.

    the requirements are:

    1) I need to get the data only for only first recent within that date range.

    Anyone help?


    • Edited by VinaySJ Sunday, December 22, 2019 5:51 PM
    Monday, December 9, 2019 6:21 AM

Answers

  • Assuming that you are passing 1-May and 31-May in input. You need to filter data in between 1-May to 7-May.

    You may use where condition given in below. 

    Also clarify more on 7-13 days requirement. What dates you want to use for filtering in this case?

    DECLARE @StartDate DATE
    DECLARE @EndDate DATE
    
    SET @StartDate = '2019-05-01'
    SET @EndDate = '2019-05-31'
    
    SELECT DATEADD(day, 6, @startDate)
    -- WHERE CAST(d.d_create_date AS DATE) >= @StartDate and CAST(d.d_create_date AS DATE) <= DATEADD(day,6,@startDate)


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    • Marked as answer by VinaySJ Monday, December 9, 2019 11:23 AM
    Monday, December 9, 2019 6:39 AM
  • Hi Vaibhav,

    Thanks for the query. i want one help from you.

    i want to retrieve the date for 28+ days. how can we do that one?

    + 28 days to which date? 

    Do you mean StartDate + 28 days?  

    DECLARE @StartDate DATE
    SET @StartDate = '2019-05-01'
    
    SELECT DATEADD(day, 28, @startDate) --> 2019-05-29

    If you need all these outputs based on ranges in single select, you can union all of them like below:

    DECLARE @StartDate DATE
    DECLARE @EndDate DATE
    
    SET @StartDate = '2019-05-01'
    SET @EndDate = '2019-05-31'
    
    SELECT '01-06 Days' AS DateRange, *
    FROM YourTable
    WHERE CAST(d.d_create_date AS DATE) >= DATEADD(day, 0, @startDate)
    	AND CAST(d.d_create_date AS DATE) <= DATEADD(day, 5, @startDate)
    
    UNION ALL
    
    SELECT '07-13 Days' AS DateRange, *
    FROM YourTable
    WHERE CAST(d.d_create_date AS DATE) >= DATEADD(day, 6, @startDate)
    	AND CAST(d.d_create_date AS DATE) <= DATEADD(day, 12, @startDate)


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav


    • Edited by Vaibhav.Chaudhari Monday, December 9, 2019 7:42 AM
    • Marked as answer by VinaySJ Monday, December 9, 2019 11:24 AM
    Monday, December 9, 2019 7:41 AM

All replies

  • Assuming that you are passing 1-May and 31-May in input. You need to filter data in between 1-May to 7-May.

    You may use where condition given in below. 

    Also clarify more on 7-13 days requirement. What dates you want to use for filtering in this case?

    DECLARE @StartDate DATE
    DECLARE @EndDate DATE
    
    SET @StartDate = '2019-05-01'
    SET @EndDate = '2019-05-31'
    
    SELECT DATEADD(day, 6, @startDate)
    -- WHERE CAST(d.d_create_date AS DATE) >= @StartDate and CAST(d.d_create_date AS DATE) <= DATEADD(day,6,@startDate)


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    • Marked as answer by VinaySJ Monday, December 9, 2019 11:23 AM
    Monday, December 9, 2019 6:39 AM
  • Hi Vaibhav,

    I am getting the correct results for 0-6 days.

    For 7-13 days, i need to display the dates which are only within 7-13 date range within that date period.

    Please give example on what should be the dates in filters in where clause. 

    7-13 date range --> do you mean 7-May to 13-May ? 

    DECLARE @StartDate DATE
    DECLARE @EndDate DATE
    
    SET @StartDate = '2019-05-01'
    SET @EndDate = '2019-05-31'
    
    SELECT DATEADD(day, 6, @startDate)
    SELECT DATEADD(day, 12, @startDate)
    -- WHERE CAST(d.d_create_date AS DATE) >= DATEADD(day,6,@startDate) and CAST(d.d_create_date AS DATE) <= DATEADD(day,12,@startDate)


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Monday, December 9, 2019 7:00 AM
  • Yeah i wanted the dates between 7 and 13 including 7th date also and 13th date also

    Last query that I shared should work. Please test and revert if it doesn't work.

    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Monday, December 9, 2019 7:17 AM
  • Hi Vaibhav,

    Thanks for the query. i want one help from you.

    i want to retrieve the date for 28+ days. how can we do that one?

    + 28 days to which date? 

    Do you mean StartDate + 28 days?  

    DECLARE @StartDate DATE
    SET @StartDate = '2019-05-01'
    
    SELECT DATEADD(day, 28, @startDate) --> 2019-05-29

    If you need all these outputs based on ranges in single select, you can union all of them like below:

    DECLARE @StartDate DATE
    DECLARE @EndDate DATE
    
    SET @StartDate = '2019-05-01'
    SET @EndDate = '2019-05-31'
    
    SELECT '01-06 Days' AS DateRange, *
    FROM YourTable
    WHERE CAST(d.d_create_date AS DATE) >= DATEADD(day, 0, @startDate)
    	AND CAST(d.d_create_date AS DATE) <= DATEADD(day, 5, @startDate)
    
    UNION ALL
    
    SELECT '07-13 Days' AS DateRange, *
    FROM YourTable
    WHERE CAST(d.d_create_date AS DATE) >= DATEADD(day, 6, @startDate)
    	AND CAST(d.d_create_date AS DATE) <= DATEADD(day, 12, @startDate)


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav


    • Edited by Vaibhav.Chaudhari Monday, December 9, 2019 7:42 AM
    • Marked as answer by VinaySJ Monday, December 9, 2019 11:24 AM
    Monday, December 9, 2019 7:41 AM
  • Vaibhav i need one help like i have data like below

    User      Count1      Count2       Total Count

    ------    ----------    -------       --------------

    User1       5                4                   9

    User2       6                7                   13

    User3       7                1                    8

    how to display the total count for each user?

    You mean, you want to add a new column TotalCount? 

    If it's new requirement and specific to SSRS report, please raise a new question

    SELECT [User]
    	,[Count1]
    	,[Count2]
    	,[Count1] + [Count2] AS 'Total Count'
    FROM Table1


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Monday, December 9, 2019 11:46 AM
  • Hi Vaibhav,

    Can you check the above one?

    I would request, please post a new question in Transact-SQL forum and with complete detailed requirement in it. You will surely get a quick response there.

    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Wednesday, December 11, 2019 6:25 AM