May 6, 2010 at 11:41 am
Hi I have the following data set:
SEQUENCE,PRODUCT,TIME,PRICE
1,Spoon1,2010-05-05 08:30:00.000,83.50
2,Spoon1,2010-05-05 08:31:01.000,80.00
3,Spoon1,2010-05-05 08:32:02.000,81.00
4,Spoon1,2010-05-05 08:33:03.000,82.00
5,Fork1,2010-05-05 08:31:01.00,90.50
6,Fork1,2010-05-05 08:32:02.000,90.00
7,Fork1,2010-05-05 08:33:03.000,93.00
8,Fork1,2010-05-05 08:34:04.000,95.00
I need a query that will produce the following output:
PRODUCT,FIRST,HIGH,LOW,LAST
Spoon1,83.50,83.50,80.00,82.00
Fork1,90.50,95.00,90.00,95.00
FIRST is the first price for the product and LAST is the last.
Are there any experts here that can lend a helping hand. Any help is appreciated.
Thank you
May 6, 2010 at 12:22 pm
Hi there, this will do the trick for you!
First lets set up the environment to work on by providing the sample data and the DDLs
IF OBJECT_ID('TEMPDB..#PRODUCTS') IS NOT NULL
DROP TABLE #PRODUCTS
CREATE TABLE #PRODUCTS
(
ID INT,
PROD_NAME VARCHAR(15),
DATE_ADDED DATETIME,
PRICE DECIMAL(8,2)
)
INSERT INTO #PRODUCTS
SELECT
1,'Spoon1','2010-05-05 08:30:00.000',83.50
UNION ALL SELECT
2,'Spoon1','2010-05-05 08:31:01.000',80.00
UNION ALL SELECT
3,'Spoon1','2010-05-05 08:32:02.000',81.00
UNION ALL SELECT
4,'Spoon1','2010-05-05 08:33:03.000',82.00
UNION ALL SELECT
5,'Fork1','2010-05-05 08:31:01.000',90.50
UNION ALL SELECT
6,'Fork1','2010-05-05 08:32:02.000',90.00
UNION ALL SELECT
7,'Fork1','2010-05-05 08:33:03.000',93.00
UNION ALL SELECT
8,'Fork1','2010-05-05 08:34:04.000',95.00
Now the code that will aggregate the data for you:
;WITH CTE AS
(
SELECT ID, ROW_NUMBER() OVER(PARTITION BY PROD_NAME ORDER BY ID) RN,
PROD_NAME , DATE_ADDED , PRICE FROM #PRODUCTS
),
RAW_FIRST_LAST AS
(
SELECT PROD_NAME , MIN(RN) [FIRST], MAX(RN) [LAST] FROM CTE
GROUP BY PROD_NAME
),
FIRST_LAST AS
(
SELECT C.PROD_NAME,
MAX(CASE WHEN R.[FIRST] = C.RN THEN C.PRICE END ) 'FIRST',
MAX(CASE WHEN R.[LAST] = C.RN THEN C.PRICE END) 'LAST'
FROM CTE C
JOIN RAW_FIRST_LAST R
ON (R.PROD_NAME = C.PROD_NAME AND (R.[FIRST] = C.RN OR R.[LAST] = C.RN ))
GROUP BY C.PROD_NAME
),
MIN_MAX_PRICE AS
(
SELECT PROD_NAME , MIN(PRICE) MINI , MAX(PRICE) MAXI FROM CTE
GROUP BY PROD_NAME
)
SELECT
FL.PROD_NAME , FL.FIRST [FIRST] , MMP.MAXI HIGH, MMP.MINI LOW , FL.LAST [LAST]
FROM
FIRST_LAST FL
INNER JOIN MIN_MAX_PRICE MMP
ON FL.PROD_NAME = MMP.PROD_NAME
And finally, lets DROP the temporary table as we are done for the day!:cool:
IF OBJECT_ID('TEMPDB..#PRODUCTS') IS NOT NULL
DROP TABLE #PRODUCTS
Hope this brings joy to you! Tel us back here itself if the code worked as you expected!
Cheers!!
May 6, 2010 at 1:06 pm
Hi that worked great for the example i provided. If i add another requirement into the mix what would the SQL look like?
SEQUENCE,PRODUCT,TIME,PRICE
1,Spoon1,2010-05-05 08:30:00.000,83.50
2,Spoon1,2010-05-05 08:31:01.000,80.00
3,Spoon1,2010-05-05 08:32:02.000,81.00
4,Spoon1,2010-05-05 08:33:03.000,82.00
5,Fork1,2010-05-05 08:31:01.00,90.50
6,Fork1,2010-05-05 08:32:02.000,90.00
7,Fork1,2010-05-05 08:33:03.000,93.00
8,Fork1,2010-05-05 08:34:04.000,95.00
9,Spoon1,2010-05-06 08:30:00.000,84.50
10,Spoon1,2010-05-06 08:31:01.000,80.00
11,Spoon1,2010-05-06 08:32:02.000,83.00
12,Spoon1,2010-05-06 08:33:03.000,82.00
I need a query that will produce the following output:
PRODUCT,DATE,FIRST,HIGH,LOW,LAST
Spoon1,2010-05-05,83.50,83.50,80.00,82.00
Spoon1,2010-05-06,84.50,84.50,80.00,82.00
Fork1,90.50,95.00,90.00,95.00
The modification is a grouping by day. I"ve dded a date dimension to the results.
Thanks for any help!
May 6, 2010 at 8:24 pm
riyaz.mohammed (5/6/2010)
Hi that worked great for the example i provided. If i add another requirement into the mix what would the SQL look like?SEQUENCE,PRODUCT,TIME,PRICE
1,Spoon1,2010-05-05 08:30:00.000,83.50
2,Spoon1,2010-05-05 08:31:01.000,80.00
3,Spoon1,2010-05-05 08:32:02.000,81.00
4,Spoon1,2010-05-05 08:33:03.000,82.00
5,Fork1,2010-05-05 08:31:01.00,90.50
6,Fork1,2010-05-05 08:32:02.000,90.00
7,Fork1,2010-05-05 08:33:03.000,93.00
8,Fork1,2010-05-05 08:34:04.000,95.00
9,Spoon1,2010-05-06 08:30:00.000,84.50
10,Spoon1,2010-05-06 08:31:01.000,80.00
11,Spoon1,2010-05-06 08:32:02.000,83.00
12,Spoon1,2010-05-06 08:33:03.000,82.00
I need a query that will produce the following output:
PRODUCT,DATE,FIRST,HIGH,LOW,LAST
Spoon1,2010-05-05,83.50,83.50,80.00,82.00
Spoon1,2010-05-06,84.50,84.50,80.00,82.00
Fork1,90.50,95.00,90.00,95.00
The modification is a grouping by day. I"ve dded a date dimension to the results.
Thanks for any help!
Help us help you. Post your data in the form of INSERT statements like ColdCoffee did. Since you're new to this forum, you should probably study the article at the first link in my signature below. It'll help you to get good solid answers quicker.;-)
--Jeff Moden
May 7, 2010 at 12:58 am
Jeff i learned more about CTE from this post as I have started my carrier as SQL Developer, but for 3 to 4 years i am working as DBA, but I am always interested in learning new things in T-SQL, I just tried query to fix it as per second request, I think i am there please check it let me know if i have done any mistakes, if you have some time
Query for creating temp table and fill data
CREATE TABLE #PRODUCTS
(
ID INT,
PROD_NAME VARCHAR(15),
DATE_ADDED DATETIME,
PRICE DECIMAL(8,2)
)
INSERT INTO #PRODUCTS
SELECT
1,'Spoon1','2010-05-05 08:30:00.000',83.50
UNION ALL SELECT
2,'Spoon1','2010-05-05 08:31:01.000',80.00
UNION ALL SELECT
3,'Spoon1','2010-05-05 08:32:02.000',81.00
UNION ALL SELECT
4,'Spoon1','2010-05-05 08:33:03.000',82.00
UNION ALL SELECT
5,'Fork1','2010-05-05 08:31:01.000',90.50
UNION ALL SELECT
6,'Fork1','2010-05-05 08:32:02.000',90.00
UNION ALL SELECT
7,'Fork1','2010-05-05 08:33:03.000',93.00
UNION ALL SELECT
8,'Fork1','2010-05-05 08:34:04.000',95.00
UNION ALL SELECT
9,'Spoon1','2010-05-06 08:30:00.000',84.50
UNION ALL SELECT
10,'Spoon1','2010-05-06 08:31:01.000',80.00
UNION ALL SELECT
11,'Spoon1','2010-05-06 08:32:02.000',83.00
UNION ALL SELECT
12,'Spoon1','2010-05-06 08:33:03.000',82.00
select query
;WITH CTE AS
(
SELECT ID, ROW_NUMBER() OVER(PARTITION BY PROD_NAME ORDER BY ID) RN,
PROD_NAME , CONVERT(SMALLDATETIME, CONVERT(VARCHAR(10) , DATE_ADDED , 101) , 101) DATE_ADDED , PRICE FROM #PRODUCTS
),
RAW_FIRST_LAST AS
(
SELECT PROD_NAME , DATE_ADDED , MIN(RN) [FIRST], MAX(RN) [LAST] FROM CTE
GROUP BY PROD_NAME , DATE_ADDED
),
FIRST_LAST AS
(
SELECT C.PROD_NAME, C.DATE_ADDED ,
MAX(CASE WHEN R.[FIRST] = C.RN THEN C.PRICE END ) 'FIRST',
MAX(CASE WHEN R.[LAST] = C.RN THEN C.PRICE END) 'LAST'
FROM CTE C
JOIN RAW_FIRST_LAST R
ON (R.PROD_NAME = C.PROD_NAME AND (R.[FIRST] = C.RN OR R.[LAST] = C.RN ) AND C.DATE_ADDED = R.DATE_ADDED)
GROUP BY C.PROD_NAME , C.DATE_ADDED
),
MIN_MAX_PRICE AS
(
SELECT PROD_NAME , DATE_ADDED , MIN(PRICE) MINI , MAX(PRICE) MAXI FROM CTE
GROUP BY PROD_NAME, DATE_ADDED
)
SELECT
FL.PROD_NAME ,
FL.DATE_ADDED ,
FL.FIRST [FIRST] ,
MMP.MAXI HIGH, MMP.MINI LOW ,
FL.LAST [LAST]
FROM
FIRST_LAST FL
INNER JOIN MIN_MAX_PRICE MMP
ON FL.PROD_NAME = MMP.PROD_NAME AND FL.DATE_ADDED = MMP.DATE_ADDED
drop temp table
IF OBJECT_ID('TEMPDB..#PRODUCTS') IS NOT NULL
DROP TABLE #PRODUCTS
Thanks,
Nagesh
Nag
------------------------------------------------
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
May 7, 2010 at 6:00 am
riyaz.mohammed (5/6/2010)
Hi that worked great for the example i provided. If i add another requirement into the mix what would the SQL look like?SEQUENCE,PRODUCT,TIME,PRICE
1,Spoon1,2010-05-05 08:30:00.000,83.50
2,Spoon1,2010-05-05 08:31:01.000,80.00
3,Spoon1,2010-05-05 08:32:02.000,81.00
4,Spoon1,2010-05-05 08:33:03.000,82.00
5,Fork1,2010-05-05 08:31:01.00,90.50
6,Fork1,2010-05-05 08:32:02.000,90.00
7,Fork1,2010-05-05 08:33:03.000,93.00
8,Fork1,2010-05-05 08:34:04.000,95.00
9,Spoon1,2010-05-06 08:30:00.000,84.50
10,Spoon1,2010-05-06 08:31:01.000,80.00
11,Spoon1,2010-05-06 08:32:02.000,83.00
12,Spoon1,2010-05-06 08:33:03.000,82.00
I need a query that will produce the following output:
PRODUCT,DATE,FIRST,HIGH,LOW,LAST
Spoon1,2010-05-05,83.50,83.50,80.00,82.00
Spoon1,2010-05-06,84.50,84.50,80.00,82.00
Fork1,90.50,95.00,90.00,95.00
The modification is a grouping by day. I"ve dded a date dimension to the results.
Thanks for any help!
Good that my code worked for you!
But riyaz, did you understand what i had written in the code and what it does to your data?? You added another column in the mix, fair enough, but before me giving a solution to this, have u tried anything from your side sire? :crying: Hmmm.. I am not getting furious/raging out here buddy, but adding another column is not a big deal. You will just have to add the column in the SELECT and GROUP BY clauses. As easy as that! Had you understood the code i had written, you would surely have solved the mix, yourself :w00t:.
Anyways, the code that Nagesh had written will solve your mix!:cool:
And as Jeff said, please go through the article he is pointing to! Nice article on how you can extract the best help from the volunteers here!
Cheers!
May 7, 2010 at 6:08 am
Nagesh S-432384 (5/7/2010)
Jeff i learned more about CTE from this post
Wow, good that you learned more abt CTE from that code. Am very happy! :blush:
I just tried query to fix it as per second request, I think i am there please check it let me know if i have done any mistakes
Yes, your code performs perfectly as per riyaz's second request! No mistakes! Bingo! 🙂
May 7, 2010 at 7:21 am
Hi
I understand what you're saying completely. Did not mean to annoy anyone. I did understand the code, and I did add the date to the grouping clauses in the various places. However, I was missing the convert function in the first CTE. Thanks to Nagesh for pointing out the solution. You all have been very helpful and I appreciate it. 🙂
I will make sure to follow the guidelines for posting such questions in the future as well.
Regards,
Riyaz
May 7, 2010 at 7:23 am
Thanks Nagesh! That worked like a charm 😀
May 7, 2010 at 7:44 am
I am sorry ColdCoffee I pointed my reply to Jeff whereas you are the one who wrote the code... I started participating on this forum frequently which is giving me more knowledge...Thanks for you as well...
Nag
Nag
------------------------------------------------
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
May 7, 2010 at 9:04 am
Nagesh S-432384 (5/7/2010)
I am sorry ColdCoffee I pointed my reply to Jeff whereas you are the one who wrote the code... I started participating on this forum frequently which is giving me more knowledge...Thanks for you as well...Nag
Oh no nagesh, don't be apologetic! NO issues! It is Jeff that i think as my mentor and the person like who i wanna be! NOt to miss the other bigwigs in SSC.com who impressed me to the core, and before i go, i wish i could meet every single person here! It is learning at SSC and i hope you will learn many things and become mentor for others!
Cheers! Happy learning and code!
May 7, 2010 at 10:17 pm
Hi this would also help u
create table #tmp
(
Sequences tinyint,
Product varchar(100),
Timing datetime,
Price float
)
insert into #tmp(SEQUENCEs,PRODUCT,TIMing,PRICE)
select 2,'Spoon1','2010-05-05 08:31:01.000',80.00
union all
select 3,'Spoon1','2010-05-05 08:32:02.000',81.00
union all
select 4,'Spoon1','2010-05-05 08:33:03.000',82.00
union all
select 5,'Fork1','2010-05-05 08:31:01.00',90.50
union all
select 6,'Fork1','2010-05-05 08:32:02.000',90.00
union all
select 7,'Fork1','2010-05-05 08:33:03.000',93.00
union all
select 8,'Fork1','2010-05-05 08:34:04.000',95.00
SELECT T.*,T1.MaxPrice,T1.MinPrice
FROM
(
SELECT Product,[Firstno]=Row_Number() OVER (PARTITION BY Product Order by Sequences),
[LastNo]=Row_Number() OVER (PARTITION BY Product Order BY Sequences DESC)
FROM
#TMP
) T
JOIN
(
SELECT product,[MaxPrice]=max(Price),[MinPrice]=Min(Price)
from
#tmp T1 group by T1.Product
) T1 ON T.Product = T1.Product
WHERE T.Firstno=1 and T.LastNo=1
May 9, 2010 at 5:32 pm
ColdCoffee (5/7/2010)
Nagesh S-432384 (5/7/2010)
I am sorry ColdCoffee I pointed my reply to Jeff whereas you are the one who wrote the code... I started participating on this forum frequently which is giving me more knowledge...Thanks for you as well...Nag
Oh no nagesh, don't be apologetic! NO issues! It is Jeff that i think as my mentor and the person like who i wanna be! NOt to miss the other bigwigs in SSC.com who impressed me to the core, and before i go, i wish i could meet every single person here! It is learning at SSC and i hope you will learn many things and become mentor for others!
Cheers! Happy learning and code!
Heh... nah... you don't wanna be like me... Pizza gives me heartburn, my eye-sight is starting to go, my knees hurt, and it still takes me too long to write good SQL. 😛
--Jeff Moden
May 9, 2010 at 11:16 pm
Jeff Moden (5/9/2010)
it still takes me too long to write good SQL. 😛
Modesty is your virtue!:-)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply