locked
Load Only Recent Data Not All Table Data RRS feed

  • Question

  • User2033107836 posted

    Hello

    I have one doubt let us consider i have a data in table from 01-Jan-2017 to 30-Nov-2017

    Mostly user view data in recent data so is that any option where if user run report it should check only first 3 months data it should not check whole table coz if the query check whole table it take long time to load.

    So whats the best options for this scenrio

    Thanking You

    Saturday, December 2, 2017 8:06 AM

Answers

  • User991499041 posted

    Hi Asp.ambur,

    To get data from last 3 months, the script could like this.

    declare @TABLE_NAME table(Date_Column nvarchar(50))
    
    insert into @TABLE_NAME values
    ('01-Jan-2017'),
    ('01-Feb-2017'),
    ('01-Mar-2017'),
    ('01-Apr-2017'),
    ('01-Sep-2017'),
    ('01-Oct-2017'),
    ('30-Nov-2017')
    
    SELECT * 
    FROM @TABLE_NAME
    WHERE Date_Column >= DATEADD(MONTH, -3, GETDATE()) 

    Regards,

    zxj

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 4, 2017 7:18 AM

All replies

  • User-1716253493 posted

    add aditional dateinserted colomn to yourtable, set dateinserted defaultvalu=getdate()

    Then query the data WHERE dateadd(mm,3,dateinserted)>getdate()

    Saturday, December 2, 2017 9:03 AM
  • User347430248 posted

    Hi asp.ambur,

    you can try to use parameterized query as a data source.

    When you define a query for a dataset, you choose the data source type. The data source type specifies the data processing extension or registered .NET Framework data provider used to connect to the data source. The data provider is the software layer that interacts with the data source and determines what support there is for using variables in a query.

    Reference:

    Using Query Parameters with Specific Data Sources (Reporting Services)

    after loading the data in dataset , you can try to use filter if you want to show specific data from that dataset.

    Add a Filter (Report Builder and SSRS)

    Filter, Group, and Sort Data (Report Builder and SSRS)

    Regards

    Deepak

    Monday, December 4, 2017 1:50 AM
  • User991499041 posted

    Hi Asp.ambur,

    To get data from last 3 months, the script could like this.

    declare @TABLE_NAME table(Date_Column nvarchar(50))
    
    insert into @TABLE_NAME values
    ('01-Jan-2017'),
    ('01-Feb-2017'),
    ('01-Mar-2017'),
    ('01-Apr-2017'),
    ('01-Sep-2017'),
    ('01-Oct-2017'),
    ('30-Nov-2017')
    
    SELECT * 
    FROM @TABLE_NAME
    WHERE Date_Column >= DATEADD(MONTH, -3, GETDATE()) 

    Regards,

    zxj

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 4, 2017 7:18 AM