April 10, 2014 at 9:21 am
Hi all,
I need to find a (reasonably) simple way to get the lowest value from multiple columns along with several other columns for each row; something like this (virtually; I know it doesn't work like this):
SELECT ProductID,ProductDesc,ProductCost,
MIN(OrderDateTime,InventoryDateTime,SoldDateTime...)
This will be used in a production report that accesses millions of rows, so I'm hoping for a solution that doesn't inherently kill performance.
Thanks,
~ Jeff
April 10, 2014 at 10:06 am
I am not sure if this is the one you are asking for, but from what I understood you might need to unpivot first on those date fields and then write a simple MIN statement using the ID field which will be same for all those. That's the way I am thinking it would be.
To see how to unpivot a column , see this article. http://sqlsaga.com/sql-server/how-to-unpivot-single-column-in-sql-server/[/url]
I wrote some sample code. It's more than what you need. I think you should look only for the first part that is inside the CTE.
;WITH CTE AS
(SELECT ProductKey, MIN([Dates]) AS [Dates]
FROM
(select productkey, OrderDateKey, DueDateKey, ShipDateKey from FactInternetSales) a
UNPIVOT ([Dates] FOR [DateKey] IN ([OrderDateKey], [DueDateKey], [ShipDateKey])) pvt
GROUP BY [ProductKey]
)
SELECT DISTINCT a.ProductKey, a.[Dates], b.DateKey
FROM CTE a
INNER JOIN (SELECT * FROM
(select productkey, OrderDateKey, DueDateKey, ShipDateKey from FactInternetSales) a
UNPIVOT ([Dates] FOR [DateKey] IN ([OrderDateKey], [DueDateKey], [ShipDateKey])) pvt
) b ON a.ProductKey = b.ProductKey AND a.Dates = b.Dates
ORDER BY [ProductKey]
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
April 10, 2014 at 10:16 am
jhager (4/10/2014)
Hi all,I need to find a (reasonably) simple way to get the lowest value from multiple columns along with several other columns for each row; something like this (virtually; I know it doesn't work like this):
SELECT ProductID,ProductDesc,ProductCost,
MIN(OrderDateTime,InventoryDateTime,SoldDateTime...)
This will be used in a production report that accesses millions of rows, so I'm hoping for a solution that doesn't inherently kill performance.
Thanks,
~ Jeff
What would help is you posted the DDL (CREATE TABLE statement) for the table involved, some sample data (in the form of INSERT INTO statements), and the expected results based on the sample data.
For help on this, please read the first article I reference below in my signature block regarding asking for help. It will walk you through what you need to post and how to do it. Do this and you will get better answers to your questions and tested code in return.
Please remember that we are volunteers and we can't see what you see.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply