How to get 3 months limit of a data in sql server?

Question

Hi Team

I have view and want to get 3 months limit, want to use single query in excel that will populate this so that when i refreshed the spreesheet i get current date of these months. Here is my below, logic.

Create View [dbo].[vw_Adr_Batch_Recipe_Log] AS
SELECT TOP (1000) [idx]
      ,[dt]
      ,[BatchNrA]
      ,[BatchNrB]
      ,[BatchNrC]
      ,[Recipe]
      ,[RecipeCode]
      ,[RecipeDescription]
      ,[InstructionNo]
      ,[Shift]
  FROM [Adroit_Batch_Suite].[dbo].[Adr_Batch_Recipe_Log]
  WHERE [dt] >= DATEADD(Month, -3, GetDate());

SELECT 
    idx,
    dt,
    BatchNrA,
    BatchNrB,
    BatchNrC,
    Recipe,
    RecipeCode,
    RecipeDescription,
    InstructionNo,
    Shift
FROM dbo.vw_Adr_Batch_Recipe_Log
WHERE dt >= DATEADD(month, -3, GETDATE());

Prasad Raveendran · Answer

Your SQL code for creating the view is correct. The view will always return records from the past three months relative to the current date. CREATE VIEW [dbo].[vw_Adr_Batch_Recipe_Log] AS SELECT TOP (1000) [idx] ,[dt] ,[BatchNrA] ,[BatchNrB] ,[BatchNrC] ,[Recipe] ,[RecipeCode] ,[RecipeDescription] ,[InstructionNo] ,[Shift] FROM [Adroit_Batch_Suite].[dbo].[Adr_Batch_Recipe_Log] WHERE [dt] >= DATEADD(Month, -3, GetDate()); You can use Excel's "Get & Transform Data" feature (also known as Power Query) to pull data from SQL Server. Here's how you can set it up: Open Excel and go to the Data tab. Get Data : Select Get Data -> From Database -> From SQL Server Database . Connect to SQL Server : Enter your SQL Server name. Choose the database where your view is stored. Enter your credentials if necessary. Query the View : In the Navigator window, select your database. Find and select the view [dbo].[vw_Adr_Batch_Recipe_Log] . Click Load or Load To to specify how you want to load the data into Excel (as a table, PivotTable, etc.). Refresh Data : Whenever you need to refresh the data, go to the Data tab and click Refresh All or right-click on the table and select Refresh . Here’s a sample SQL query that Excel will use to get the data from the view, which ensures that it always gets the records from the past three months: SELECT idx, dt, BatchNrA, BatchNrB, BatchNrC, Recipe, RecipeCode, RecipeDescription, InstructionNo, Shift FROM dbo.vw_Adr_Batch_Recipe_Log By following these steps, your Excel spreadsheet will automatically fetch the latest data from the past three months each time you refresh it, ensuring that you always have the most up-to-date information.

Guest User · Answer

Hi Team I am now after following both steps mentioned here, this is my current error both sql server and excel sheet when using this query. Msg 208, Level 16, State 1, Line 30 Invalid object name 'dbo.vw_Adr_Batch_Recipe_Log'.

Jignesh Kumar · Answer

Hello, You can create view with additional column RowNum and partiion by date -- Drop the view if it already exists and recreate view again IF OBJECT_ID('[dbo].[vw_Adr_Batch_Recipe_Log]', 'V') IS NOT NULL DROP VIEW [dbo].[vw_Adr_Batch_Recipe_Log]; GO CREATE VIEW [dbo].[vw_Adr_Batch_Recipe_Log] AS SELECT [idx], [dt], [BatchNrA], [BatchNrB], [BatchNrC], [Recipe], [RecipeCode], [RecipeDescription], [InstructionNo], [Shift] FROM ( SELECT [idx], [dt], [BatchNrA], [BatchNrB], [BatchNrC], [Recipe], [RecipeCode], [RecipeDescription], [InstructionNo], [Shift], ROW_NUMBER() OVER (ORDER BY [dt] DESC) AS RowNum FROM [Adroit_Batch_Suite].[dbo].[Adr_Batch_Recipe_Log] ) AS RowOrdered WHERE RowNum = DATEADD(Month, -3, GetDate()); -- create select query from the view to get last three months data SELECT idx, dt, BatchNrA, BatchNrB, BatchNrC, Recipe, RecipeCode, RecipeDescription, InstructionNo, Shift FROM dbo.vw_Adr_Batch_Recipe_Log WHERE dt >= DATEADD(month, -3, GETDATE());

Vishal Joshi · Answer

Hello, Checkout the below SQL script to get the expected result. -- Drop the view if it already exists and recreate view again IF OBJECT_ID('[dbo].[vw_Adr_Batch_Recipe_Log]', 'V') IS NOT NULL DROP VIEW [dbo].[vw_Adr_Batch_Recipe_Log]; GO CREATE VIEW [dbo].[vw_Adr_Batch_Recipe_Log] AS SELECT [idx], [dt], [BatchNrA], [BatchNrB], [BatchNrC], [Recipe], [RecipeCode], [RecipeDescription], [InstructionNo], [Shift] FROM ( SELECT [idx], [dt], [BatchNrA], [BatchNrB], [BatchNrC], [Recipe], [RecipeCode], [RecipeDescription], [InstructionNo], [Shift], ROW_NUMBER() OVER (ORDER BY [dt] DESC) AS RowNum FROM [dbo].[Adr_Batch_Recipe_Log] ) AS RowOrdered WHERE RowNum = DATEADD(Month, -3, GetDate()); GO -- create select query from the view to get last three months data SELECT * FROM [dbo].[vw_Adr_Batch_Recipe_Log] Thanks Vishal Joshi