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

Guest User

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());

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Prasad Raveendran2y ago
Accepted 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:
1. Open Excel and go to the Data tab.
2. Get Data: Select Get Data -> From Database -> From SQL Server Database.
3. Connect to SQL Server:
  
  Enter your SQL Server name.
  
  Choose the database where your view is stored.
  
  Enter your credentials if necessary.
4. 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.).
5. 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.

Vishal Joshi2y ago

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 <= 1000
    AND [dt] >= 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

Guest User2y ago
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 Kumar2y ago

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 <= 1000
    AND [dt] >= 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());