Obtaining First & Last Value in columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Obtaining First & Last Value in columns

Author

Topic

ddhook
Starting Member

4 Posts

Posted - 2011-03-31 : 08:54:16

I am trying to summarize daily data for a week. I am getting the min and max for a couple of fields but also want to get the first and last values for a couple of columns. I am using a query to group by week and can get the min and max but not sure how to obtain the first and last values. I had tried joining the table on itself, but to no avail.

Below is the query... Any insight is greatly appreciated!!

SELECT MIN(q1.[QUOTE_DATE]) AS STARTDATE,
MAX(q1.[QUOTE_DATE]) AS ENDDATE,
--> FIRST(q2.[OPEN]) AS OPENPRICE,
MAX(q1.[HIGH]) AS HIGH,
MIN(q1.[LOW]) AS LOW,
--> LAST(q1.[CLOSE]) AS CLOSEPRICE
FROM [dbo].[quote] as q1
WHERE q1.[SECURITY_ID] = @securityId
AND q1.[QUOTE_DATE] BETWEEN @fromDate AND @toDate
GROUP BY DATEPART(WEEK,q1.[QUOTE_DATE])
ORDER BY 1 ASC

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-31 : 09:22:29

What determines First and Last? And is this SQL 2005 or later?

Jim

Everyday I learn something that somebody else already knew

ddhook
Starting Member

4 Posts

Posted - 2011-03-31 : 10:10:30

Jim ...

Love your tag line ... I too am always learning.. which is good! :)

Look at the data below:

Date OPEN HIGH LOW CLOSE
3/4/2011 360.07 360.29 357.75 360
3/3/2011 357.19 359.79 355.92 359.56
3/2/2011 349.96 354.35 348.4 352.12
3/1/2011 355.47 355.72 347.68 349.31
2/28/2011 351.24 355.05 351.12 353.21

FIRST would be 351.24 (in OPEN column) and LAST would be 360 (in CLOSE column). Min and Max in each column works fine, but trying to get the FIRST and LAST.

I have SQL 2008.

I wanted to see if I could do this via SQL rather than using a cursor to get initial data then update with obtaining first and last ....

Thanks!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-31 : 11:21:00

Try this maybe?

DECLARE @table Table (QuoteDate datetime,OpenValue numeric(6,2),High numeric(6,2),Low numeric(6,2),CloseValue numeric(6,2))
INSERT INTO @Table
SELECT '3/4/2011', 360.07, 360.29, 357.75, 360 UNION ALL
SELECT '3/3/2011', 357.19 ,359.79, 355.92, 359.56 UNION ALL
SELECT '3/2/2011', 349.96 ,354.35, 348.4, 352.12 UNION ALL
SELECT '3/1/2011', 355.47 ,355.72, 347.68, 349.31 UNION ALL
SELECT '2/28/2011', 351.24, 355.05, 351.12 ,353.21

SELECT DATEPART(week,t1.quotedate)
,MAX(CASE WHEN t1.FirstOpenValue = 1 THEN t1.OpenValue ELSE 0 END)
,MAX(CASE WHEN t1.LastCloseValue = 1 THEN t1.LastCloseValue ELSE 0 END)
,MIN(t2.QuoteDate) as StartDate
,MAX(t2.high) as High
,MIN(t2.low) as Low
FROM
@table t2
INNER JOIN
(
select quotedate
,OpenValue,CloseValue
,ROW_NUMBER() OVER(order by quotedate asc) as FirstOpenValue
,ROW_NUMBER() OVER(order by quotedate desc) as LastCloseValue
from @table
) t1
ON
t2.quotedate = t1.QuoteDate
GROUP BY DATEPART(week,t1.quotedate)

Jim

Everyday I learn something that somebody else already knew

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-03-31 : 11:30:00

CREATE TABLE #tEMPtABLE
(
userDate DateTime,
OpenVal FLOAT,
HighVal FLOAT,
CloseVal FLOAT
)
INSERT INTO #tEMPtABLE
SELECT '3/4/2011',
360.07,
360.29,
360
UNION ALL
SELECT '3/3/2011',
357.19,
359.79,
359.56
UNION ALL
SELECT '3/2/2011',
349.96,
354.35,
352.12
UNION ALL
SELECT '3/1/2011',
355.47,
355.72,
349.31
UNION ALL
SELECT '2/28/2011',
351.24,
355.05,
353.21

SELECT *,
( SELECT TOP ( 1 )
OpenVal
FROM #tEMPtABLE
ORDER BY userDate
) FirstVal,
( SELECT TOP ( 1 )
CloseVal
FROM #tEMPtABLE
ORDER BY userDate DESC
) LastVal
FROM #tEMPtABLE

DROP TABLE #tEMPtABLE

--------------------------
http://connectsql.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-31 : 11:55:16

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

>> I am trying to summarize daily data for a week. I am getting the min and max for a couple of fields [sic: columns are not fields] but also want to get the first [sic: columns are not ordered] and last [sic] values for a couple of columns. I am using a query to group by week and can get the MIN and MAX but not sure how to obtain the first and last values. <<

First, clean up the data element names of what you did post and introduce you to the idea of a report period table. SQL is a database language, so we use data rather doing computational constructions on the fly. The re4port period name would be the ISO week notation – '2011-W01', '2011-W02', etc. Here is my guess at your DDL. Notice the use of constraints to ensure data integrity. I am also assuming that you have filled in every date, no gaps.

CREATE TABLE Quotes
(quote_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL PRIMARY KEY,
open_price DECIMAL (7,2) DEFAULT 0.00 NOT NULL
CHECK (open_price) >= 0.00,
high_price DECIMAL (7,2) DEFAULT 0.00 NOT NULL
CHECK (high_price ) >= 0.00,
low_price DECIMAL (7,2) DEFAULT 0.00 NOT NULL
CHECK (low_price) >= 0.00,
CHECK (high_price >= low_price),
close_price DECIMAL (7,2) DEFAULT 0.00 NOT NULL
CHECK (close_price) >= 0.00);

INSERT INTO Quotes
('2011-03-04', 360.07, 360.29, 357.75, 360.00),
('2011-03-03', 357.19, 359.79, 355.92, 359.56),
('2011-03-02', 349.96, 354.35, 348.40, 352.12),
('2011-03-01', 355.47, 355.72, 347.68, 349.31),
('2011-02-28', 351.24, 355.05, 351.12, 353.21);

>> FIRST would be 351.24 (in OPEN column) and LAST would be 360.00 (in CLOSE column). Min and Max in each column works fine, but trying to get the FIRST and LAST. <<

There are some extensions in Oracle called FIRST()OVER() and LAST()OVER(), but they are not ANSI/ISO Standard or in T-SQL. I think you mean the earliest opening value and the latest closing value in the week.

SELECT R.week_name,
MAX(CASE WHEN Q.quote_date = R.week_start_date
THEN open_price) AS first_open_price,
MAX(CASE WHEN Q.quote_date = R.week_end_date
THEN close_price) AS last_close_price,
MIN(Q.quote_date) AS quote_date_min,
MAX(Q.quote_date) AS quote_date_max,
MAX(Q.high_price) AS high_price_max,
MIN(Q.low_price) AS low_price_min
FROM Quotes AS Q, Report_Weeks AS R
WHERE Q.security_id = @in_security_id
AND R.week_name = @in_week_name
AND Q.quote_date BETWEEN R.week_start_date AND R.week_end_date
GROUP BY R.week_name;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

ddhook
Starting Member

4 Posts

Posted - 2011-03-31 : 13:53:38

All - thanks for your time.

Jim - in what you provided, if you add additional rows ... 5 for 5 days worth of data, your results would yield 2 rows ... with the open or starting value in the first row and a closing value in the second row.... I am looking for the open value for the first quote and the close value for the last day.

So the results look like:
WeekValue StartDate OpeningValue High Low ClosingValue
10 2011-02-28 351.24 360.29 347.68 0
11 2011-03-07 0 361.67 344.90 1

So I am looking on how to get the ClosingValue for each row ...
Above this would be 360 for row 1 and 351.99 for row 2. Also looking for the OpeningValue for row 2 which would be 361.4

Here is Jim's DDL that I added data to ... again ... thanks!!

DECLARE @table Table (QuoteDate datetime,OpenValue numeric(6,2),High numeric(6,2),Low numeric(6,2),CloseValue numeric(6,2))
INSERT INTO @Table

SELECT '3/11/2011', 345.33, 352.32, 345, 351.99 UNION ALL
SELECT '3/10/2011', 349.12, 349.77, 344.9, 346.67 UNION ALL
SELECT '3/9/2011', 354.69, 354.76, 350.6, 352.47 UNION ALL
SELECT '3/8/2011', 354.91, 357.4, 352.25, 355.76 UNION ALL
SELECT '3/7/2011', 361.4, 361.67, 351.31, 355.36 UNION ALL
SELECT '3/4/2011', 360.07, 360.29, 357.75, 360 UNION ALL
SELECT '3/3/2011', 357.19 ,359.79, 355.92, 359.56 UNION ALL
SELECT '3/2/2011', 349.96 ,354.35, 348.4, 352.12 UNION ALL
SELECT '3/1/2011', 355.47 ,355.72, 347.68, 349.31 UNION ALL
SELECT '2/28/2011', 351.24, 355.05, 351.12 ,353.21

SELECT DATEPART(week,t1.quotedate) as WeekValue
,MIN(t2.QuoteDate) as StartDate
,MAX(CASE WHEN t1.FirstOpenValue = 1 THEN t1.OpenValue ELSE 0 END) AS OpeningValue
,MAX(t2.high) as High
,MIN(t2.low) as Low
,MAX(CASE WHEN t1.LastCloseValue = 1 THEN t1.LastCloseValue ELSE 0 END) as ClosingValue

FROM
@table t2
INNER JOIN
(
select quotedate
,OpenValue,CloseValue
,ROW_NUMBER() OVER(order by quotedate asc) as FirstOpenValue
,ROW_NUMBER() OVER(order by quotedate desc) as LastCloseValue
from @table
) t1
ON
t2.quotedate = t1.QuoteDate
GROUP BY DATEPART(week,t1.quotedate)

ddhook
Starting Member

4 Posts

Posted - 2011-04-02 : 10:33:55

All - thanks for your input and steering me in the right direction ... This is what I ended up with

SELECT MIN(q1.[QUOTE_DATE]) AS STARTDATE,
MAX(q1.[QUOTE_DATE]) AS ENDDATE,
MAX(CASE WHEN q1.[QUOTE_DATE] = q2.STARTDATE THEN q1.[OPEN] ELSE 0 END) AS OPENPRICE,
MAX(q1.[HIGH]) AS HIGH,
MIN(q1.[LOW]) AS LOW,
MAX(CASE WHEN q1.[QUOTE_DATE] = q3.ENDDATE THEN q1.[CLOSE] ELSE 0 END) AS CLOSEPRICE
FROM [dbo].[quote] as q1
JOIN
(
SELECT MAX([SECURITY_ID]) as securityID,
MIN([QUOTE_DATE]) AS STARTDATE
FROM [TradingSystem].[dbo].[quote]
WHERE [SECURITY_ID] = @securityId
AND [QUOTE_DATE] BETWEEN @fromDate AND @toDate
GROUP BY DATEPART(WEEK,[QUOTE_DATE])
) as q2
ON q1.[SECURITY_ID] = q2.securityID
JOIN
(
SELECT MAX([SECURITY_ID]) as securityID,
MAX([QUOTE_DATE]) AS ENDDATE
FROM [TradingSystem].[dbo].[quote]
WHERE [SECURITY_ID] = @securityId
AND [QUOTE_DATE] BETWEEN @fromDate AND @toDate
GROUP BY DATEPART(WEEK,[QUOTE_DATE])
) as q3
ON q1.[SECURITY_ID] = q3.securityID
WHERE q1.[SECURITY_ID] = @securityId
AND q1.[QUOTE_DATE] BETWEEN @fromDate AND @toDate
GROUP BY DATEPART(WEEK,q1.[QUOTE_DATE])
ORDER BY 1 ASC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources