Sql-server – Multiple Entries Return Lowest Data With Lowest Date

sql serversql-server-2008-r2t-sql

We had a few spreadsheets get imported multiple times into a database so we now have duplicate data (which of course had primary keys etc been assigned this would not have happed…)

However – the issue at hand is I need a way to return the data associated with the smallest (oldest) date for each PN in the database. Below is a sample of the data and the structure. I tried to use Top 1 but I realized there was no rhyme or reason to what SSMS deemed as the 1st entry.

What would a query be for the syntax below to return the oldest date – expected returned results:

Number One 111111 2014-01-01
Number Two 2222 2014-02-22

Sample DDL ->

  Declare @Test Table (pn varchar(200), ECN int, d1 date)
  Insert Into @Test (pn, ECN, d1) Values
  ('Number One', '111111', '2014-01-01')
  ,('Number One', '111111', '2014-01-31')
  ,('Number One', '111111', '2014-02-14')
  ,('Number Two', '2222', '2014-02-22')
  ,('Number Two', '2222', '2014-02-25')

Best Answer

SELECT pn, MIN(d1) AS [oldestDate]
FROM @Test
GROUP BY pn


pn          oldestDate
Number One  2014-01-01
Number Two  2014-02-22

Related Solutions

Sql-server – the best way to get all data for a date range, plus the last event just before the range

I am going to assume that there isn't an index on the date columns, otherwise I think that the query would have been structured differently. If there is, you can probably find a better performing one than this.

The advantage of this query is that it can get all the data in one scan. The disadvantage is that it has to sort the data and join EventEmployee on the entire table. So as always, test with your own situation. This query also assumes that the MAX date is either unique or that equivalent rows would be acceptable.

USE AdventureWorks2012
GO
;
WITH Base AS (
   SELECT 
      TransactionHistory.*
      ,ProductVendor.BusinessEntityID
      ,MAX(CASE WHEN TransactionDate < '2008-08-01' THEN TransactionDate END) 
           OVER (PARTITION BY ProductVendor.BusinessEntityID) AS PreviousVendorTransaction
      ,COUNT(CASE WHEN TransactionDate >= '2008-08-01' THEN 1 END ) 
           OVER (PARTITION BY ProductVendor.BusinessEntityID) AS VendorAfterCutoff
   FROM
      Production.TransactionHistory
      -- Doesn't make the most sense, but I need a repeating relation
      INNER JOIN Purchasing.ProductVendor
         ON TransactionHistory.ProductID = ProductVendor.ProductID
),
Filtered AS (
   SELECT
      *
   FROM
      Base
   WHERE
      Base.TransactionDate >= '2008-08-01'
      OR (TransactionDate = PreviousVendorTransaction AND VendorAfterCutoff > 0)
)
SELECT DISTINCT
   TransactionID
   ,ProductID
   ,ReferenceOrderID
   ,ReferenceOrderLineID
   ,TransactionDate
   ,TransactionType
   ,Quantity
   ,ActualCost
   ,ModifiedDate
FROM
   Filtered

Edit:

Hmm, I think I may have to take back my comment on structuring it differently if there are indexes. The other suggestions that I have are probably fairly minor.

Make sure the query is using the indexes you're expecting it to. Start and End date to build temp table, end date to drive the previous event loop.
If the query to build the temp table is doing a lookup on the clustered index, it may be better to hold off and do that as part of the main query.
Try using a cte instead of a temp table. I think that a cte might be more competitive with the way that the query is structured below.
If you are returning a lot of events, it might be better to pull out the event table lookup to the main query to give the optimizer the option of doing a merge join.
I don't see a way of optimizing the previous event lookup short of an indexed view.

Here's a query that combines a few of those ideas.

SELECT
    e.[EventID]
INTO #EventTemp
FROM
    [Events] AS e
WHERE
    ( e.[EventStart] >= @StartDate AND e.[EventStart] <= @EndDate )
    OR ( e.[EventEnd] >= @StartDate AND e.[EventEnd] <= @EndDate )

;
WITH PrevEvent AS (
    SELECT
        EmpPrevEvent.[EventID]
    FROM
    (
        SELECT DISTINCT
            ee.[EmployeeID]
        FROM
            #EventTemp
            INNER JOIN [EventEmployee] AS ee ON
                #EventTemp.[EventID] = ee.[EventID]
    ) AS Emp
    CROSS APPLY (
        SELECT TOP 1
            e.[EventID]
        FROM
            [Events] AS e
            INNER JOIN [EventEmployee] AS ee ON
                e.[EventID] = ee.[EventID]
        WHERE
            ee.[EmployeeID] = Emp.[EmployeeID]
            AND e.[EventEnd] < @StartDate
        ORDER BY 
            e.[EventEnd] DESC
    ) AS EmpPrevEvent
)
SELECT
    e.[EventID],
    e.[EventStart],
    e.[EventEnd],
    e.[EventTypeID]
FROM
    [Events] AS e
WHERE
    e.EventID IN (
        SELECT EventID
        FROM #EventTemp
        UNION
        SELECT EventID
        FROM PrevEvent
    )

Sql-server – Query All Databases Listed In Table

It is unlikely that the cursor you are using is the cause of the delay in returning results. Most likely, the delay is due to running many select statements over possibly a large amount of data. Having said that, I likely wouldn't use the DECLARE CURSOR cur FOR ... construct to solve this issue.

I generally approach this class of problem by generating a single dynamic SQL statement, then execute that statement using either sp_executesql() or EXEC (), as in:

IF COALESCE(OBJECT_ID('dbo.TableList'), 0) <> 0
DROP TABLE dbo.TableList;

CREATE TABLE dbo.TableList
(
    DatabaseName SYSNAME NOT NULL
    , SchemaName SYSNAME NOT NULL
    , TableName SYSNAME NOT NULL
);

INSERT INTO dbo.TableList(DatabaseName, SchemaName, TableName)
VALUES ('Database1', 'Schema1', 'Table1')
    ,  ('Database2', 'Schema2', 'Table2');

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = '';

SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10) END 
    + 'SELECT ''' + tl.DatabaseName 
        + '.' + tl.SchemaName 
        + '.' + tl.TableName 
        + ''', * '
    + 'FROM ' + QUOTENAME(tl.DatabaseName) 
        + '.' + QUOTENAME(tl.SchemaName) 
        + '.' + QUOTENAME(tl.TableName) + ';'
FROM dbo.TableList tl;

PRINT (@cmd);
EXEC sp_executesql @cmd;

This constructs statements required from the TableList table, appending each statement into a variable. The variable contents are then executed using the sp_executesql system stored procedure. This methodology can be used for a multitude of problems where you need to generate T-SQL statements based on the results of some query.

The output will be a single result set for each row in TableList - you probably don't want to do this for more than about 50 or 100 tables since the output might become unmanageable. If you have a lot of tables, you may want to consider inserting the output into a temporary table, then running a single select statement against that table.

If the structure of the tables listed in the TableName column are identical, you can use a UNION ALL to join the output into a single resultset:

SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10) 
    + 'UNION ALL' + CHAR(13) + CHAR(10) END 
    + 'SELECT ''' + tl.DatabaseName 
        + '.' + tl.SchemaName 
        + '.' + tl.TableName 
        + ''', * '
    + 'FROM ' + QUOTENAME(tl.DatabaseName) 
        + '.' + QUOTENAME(tl.SchemaName) 
        + '.' + QUOTENAME(tl.TableName) + ';'
FROM dbo.TableList tl;

Be aware that UNION ALL is generally much faster than UNION since the ALL option doesn't need to run a DISTINCT operator over the data.

Best Answer

Related Solutions

Sql-server – the best way to get all data for a date range, plus the last event just before the range

Sql-server – Query All Databases Listed In Table

Related Question