hi there, here is one piece that might interest you! This is **NOT** the optimal solution for the problem, but for now, you can have this. The desired output can easily be acheived by ROW_NUMBER functions, but as i have some time constraint, i cant sit with that logic.
First lets set-up the dev environment: (Please read thro the article Bhuvnesh has referred and in future post the sample data as i have posted here)
SET DATEFORMAT DMY
IF OBJECT_ID('TEMPDB..#Temp') IS NOT NULL
DROP TABLE #Temp
CREATE TABLE #Temp
(
Name VARCHAR(15),
Price NUMERIC(5,2),
Date DATETIME
)
INSERT INTO #Temp (Name ,Price ,Date)
SELECT 'ProductA', 10.45, '1/June/2010 01:00'
UNION ALL SELECT 'ProductB', 10.90, '1/June/2010 01:00'
UNION ALL SELECT 'ProductA', 10.45, '1/June/2010 02:00'
UNION ALL SELECT 'ProductB', 10.90, '1/June/2010 02:00'
UNION ALL SELECT 'ProductA', 10.45, '1/June/2010 03:00'
UNION ALL SELECT 'ProductB', 10.90, '1/June/2010 03:00'
UNION ALL SELECT 'ProductA', 10.65, '1/June/2010 04:00'
UNION ALL SELECT 'ProductB', 10.90, '1/June/2010 04:00'
Now for the code (cumbersome code to be honest :pinch:) that will produce teh desired result:
;WITH GROUP_DATA AS
(
SELECT NAME,
MIN(price) min_price ,
MAX(price) max_price
FROM #Temp
GROUP BY
NAME
),
SINGLETON AS
(
SELECT
GD.NAME , GD.min_price , MIN(T.DATE) MIN_DATE, NULL max_price , NULL max_date
FROM
GROUP_DATA GD
INNER JOIN
#Temp T ON T.NAME = GD.NAME AND T.price = GD.max_price
WHERE
GD.min_price = GD.max_price
GROUP BY
GD.NAME , GD.min_price
),
DOUBLET AS
(
SELECT GD.NAME , GD.min_price ,
MIN (CASE WHEN T.PRICE = GD.MIN_PRICE THEN T.DATE END ) MINI,
GD.max_price,
MAX (CASE WHEN T.PRICE = GD.MAX_PRICE THEN T.DATE END ) MAXI
FROM
GROUP_DATA GD
INNER JOIN
#Temp T ON T.NAME = GD.NAME
WHERE
GD.min_price <> GD.max_price
GROUP BY
GD.NAME , GD.min_price , GD.max_price
),
MAXI_CAB AS
(
SELECT
GD.NAME , GD.max_price , MAX(T.DATE) max_date, NULL min_price , NULL MIN_DATE
FROM
GROUP_DATA GD
INNER JOIN
#Temp T ON T.NAME = GD.NAME AND T.price = GD.max_price
WHERE
GD.min_price <> GD.max_price
GROUP BY
GD.NAME , GD.max_price
),
UNION_ALL AS
(
SELECT NAME , min_price , MIN_DATE, max_price , max_date FROM SINGLETON
UNION ALL
SELECT NAME , min_price , MINI, max_price,MAXIFROM DOUBLET
UNION ALL
SELECT NAME , max_price , max_date , min_price , MIN_DATE FROM MAXI_CAB
)
SELECT NAME , min_price , MIN_DATE, max_price , max_date FROM UNION_ALL
ORDER BY NAME
Hope this gets you started, at the least! I will come up with an optimum, should time permit me to relax!