Link to home
Start Free TrialLog in
Avatar of Rich Olu
Rich OluFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL Query

I have a table with the following columns:
Week No, Date, Time,Open,Low,High,Close

For example
Date                Time       Open      Low         High       Close      Week
2014-07-09      22:45      1329.15      1328.65      1329.32      1328.77      28
2014-07-09      22:50      1328.75      1327.60      1328.85      1327.90      28
2014-07-09      22:55      1328.02      1326.92      1328.10      1327.80      28
2014-07-09      23:00      1327.63      1326.87      1327.92      1327.92      28
2014-07-09      23:05      1327.89      1327.71      1328.08      1327.81      28
2014-07-09      23:10      1327.72      1327.71      1328.00      1327.92      28
2014-07-09      23:15      1327.94      1327.63      1328.05      1327.91      28
2014-07-09      23:20      1327.94      1327.71      1327.95      1327.72      28
2014-07-09      23:25      1327.71      1327.64      1328.29      1328.28      28
2014-07-09      23:30      1328.29      1328.26      1328.73      1328.41      28
2014-07-09      23:35      1328.49      1328.47      1328.63      1328.48      28
2014-07-09      23:40      1328.54      1328.43      1328.63      1328.44      28
2014-07-09      23:45      1328.50      1328.13      1328.60      1328.49      28
2014-07-09      23:50      1328.42      1327.77      1328.55      1327.81      28
2014-07-09      23:55      1327.72      1327.44      1327.75      1327.52      28
2014-07-10      01:00      1326.47      1324.25      1327.53      1327.40      28
2014-07-10      01:05      1327.41      1327.06      1327.73      1327.06      28
2014-07-10      01:10      1327.04      1327.02      1327.72      1327.36      28
2014-07-10      01:15      1327.40      1327.40      1327.66      1327.54      28
2014-07-10      01:20      1327.55      1327.07      1327.55      1327.32      28
2014-07-10      01:25      1327.36      1326.86      1327.37      1327.05      28
2014-07-10      01:30      1327.06      1326.92      1327.21      1326.98      28
2014-07-10      01:35      1326.99      1326.96      1327.03      1326.98      28
2014-07-10      01:40      1326.96      1326.95      1327.36      1327.23      28
2014-07-10      01:45      1327.15      1326.97      1327.21      1327.02      28
2014-07-10      01:50      1326.98      1326.83      1327.34      1327.25      28
2014-07-10      01:55      1327.24      1327.06      1327.36      1327.18      28
2014-07-10      02:00      1327.17      1327.03      1327.22      1327.07      28
2014-07-10      02:05      1327.06      1326.95      1327.21      1327.00      28
2014-07-10      02:10      1327.04      1326.89      1327.06      1326.93      28
2014-07-10      02:15      1326.90      1326.85      1326.98      1326.88      28
2014-07-10      02:20      1326.82      1326.31      1326.84      1326.36      28
2014-07-10      02:25      1326.28      1326.22      1326.75      1326.68      28
2014-07-10      02:30      1326.71      1326.69      1327.05      1326.82      28
2014-07-10      02:35      1326.76      1326.76      1327.26      1327.10      28
2014-07-10      02:40      1327.04      1326.52      1327.07      1326.52      28
2014-07-10      02:45      1326.61      1326.43      1326.76      1326.53      28
2014-07-10      02:50      1326.56      1326.44      1326.65      1326.50      28
2014-07-10      02:55      1326.55      1326.55      1326.97      1326.93      28
2014-07-10      03:00      1326.82      1326.82      1327.07      1326.96      28
2014-07-10      03:05      1327.06      1326.90      1327.26      1327.14      28
2014-07-10      03:10      1327.13      1327.06      1327.14      1327.08      28
2014-07-10      03:15      1327.13      1326.97      1327.25      1327.04      28
2014-07-10      03:20      1327.01      1326.99      1327.28      1327.10      28
2014-07-10      03:25      1327.11      1327.09      1328.06      1328.05      28
2014-07-10      03:30      1327.92      1327.87      1328.20      1328.20      28
2014-07-10      03:35      1328.29      1327.91      1328.57      1328.00      28
2014-07-10      03:40      1328.05      1328.00      1328.56      1328.50      28
2014-07-10      03:45      1328.41      1328.11      1328.45      1328.12      28
2014-07-10      03:50      1328.16      1328.11      1328.55      1328.42      28
2014-07-10      03:55      1328.35      1328.33      1328.69      1328.67      28
2014-07-10      04:00      1328.62      1328.30      1329.01      1328.80      28
2014-07-10      04:05      1328.79      1328.78      1329.45      1329.20      28
2014-07-10      04:10      1329.16      1328.99      1329.77      1329.50      28
2014-07-10      04:15      1329.55      1329.29      1330.98      1330.10      28
2014-07-10      04:20      1330.14      1330.14      1330.92      1330.50      28
2014-07-10      04:25      1330.47      1330.28      1331.01      1330.42      28
2014-07-10      04:30      1330.36      1330.25      1330.98      1330.65      28
2014-07-10      04:35      1330.62      1330.38      1330.72      1330.38      28
2014-07-10      04:40      1330.39      1329.71      1330.46      1329.75      28
2014-07-10      04:45      1329.73      1329.37      1330.03      1329.98      28
2014-07-10      04:50      1329.99      1329.89      1330.13      1330.09      28
2014-07-10      04:55      1330.02      1329.89      1330.15      1330.00      28
2014-07-10      05:00      1329.57      1329.33      1329.84      1329.45      28
2014-07-10      05:05      1329.47      1329.20      1329.55      1329.54      28
2014-07-10      05:10      1329.61      1329.54      1329.85      1329.84      28
2014-07-10      05:15      1329.85      1329.68      1329.98      1329.72      28
2014-07-10      05:20      1329.65      1329.22      1329.70      1329.41      28
2014-07-10      05:25      1329.44      1329.41      1329.63      1329.51      28
2014-07-10      05:30      1329.49      1329.19      1329.55      1329.21      28
2014-07-10      05:35      1329.23      1328.63      1329.25      1328.99      28
2014-07-10      05:40      1329.05      1328.89      1329.15      1329.03      28
2014-07-10      05:45      1329.04      1328.97      1329.19      1329.19      28
2014-07-10      05:50      1329.20      1329.20      1329.77      1329.35      28
2014-07-10      05:55      1329.39      1329.35      1329.79      1329.69      28
2014-07-10      06:00      1329.70      1329.31      1329.70      1329.40      28
2014-07-10      06:05      1329.38      1329.28      1329.39      1329.28      28
2014-07-10      06:10      1329.26      1329.17      1329.56      1329.28      28
2014-07-10      06:15      1329.47      1329.27      1329.54      1329.50      28
2014-07-10      06:20      1329.46      1329.46      1329.82      1329.64      28
2014-07-10      06:25      1329.70      1329.48      1329.75      1329.48      28
2014-07-10      06:30      1329.57      1329.27      1329.66      1329.29      28
2014-07-10      06:35      1329.28      1328.99      1329.28      1329.11      28
2014-07-10      06:40      1329.10      1329.09      1329.33      1329.33      28
2014-07-10      06:45      1329.34      1329.28      1329.52      1329.49      28
2014-07-10      06:50      1329.48      1329.42      1329.49      1329.48      28
2014-07-10      06:55      1329.47      1329.28      1329.47      1329.29      28
2014-07-10      07:00      1329.07      1329.00      1329.21      1329.18      28
2014-07-10      07:05      1329.23      1329.10      1329.45      1329.10      28
2014-07-10      07:10      1329.06      1328.90      1329.11      1328.90      28
2014-07-10      07:15      1328.99      1328.99      1329.05      1329.05      28
2014-07-10      07:20      1329.09      1328.89      1329.15      1329.05      28
2014-07-10      07:25      1328.99      1328.79      1329.06      1328.93      28
2014-07-10      07:30      1328.85      1328.39      1328.95      1328.46      28
2014-07-10      07:35      1328.47      1328.29      1328.56      1328.50      28
2014-07-10      07:40      1328.42      1328.10      1328.65      1328.15      28
2014-07-10      07:45      1327.97      1327.96      1328.48      1328.34      28
2014-07-10      07:50      1328.39      1328.22      1328.68      1328.62      28
2014-07-10      07:55      1328.66      1328.51      1328.72      1328.57      28
2014-07-10      08:00      1328.52      1328.47      1328.95      1328.94      28

Each week has days from Sun till Fri. Each day contains data every 5min starting at 1am until say 9pm
Each 5min data contains an open and close, and high and low value.

I would like a query to produce weekly report, showing me per week, the open value (determined by the open value at open time for the week), the lowest value for the week, the highest value for the week.

I can get the highest and lowest for the week by querying for the week number, the highest and lowest grouped by the week. The trouble I'm having is getting the open value at the first(lowest) day and first(open) time for the week.

I trust the genius SQL coders can help with this.

Thanks.
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Which database are you using ?

SQL SERVER / ORACLE ?
Avatar of Rich Olu

ASKER

Apologies for failing to specify this. I trying to do this primarily MS SQL Server 2014 but I would not mind a solution on Oracle as well, or even MySQL.
Thanks.
Avatar of Pavel Celba
As your problem is reduced to Opening value only, here it is:

This should work in SQL Server:
SELECT  Open, week FROM 
   (SELECT Date, Time,Open,Low,High,Close,week, 
           ROW_NUMBER() OVER (PARTITION BY week ORDER BY Date, time) AS rn
      FROM YourTable) innerq
WHERE rn = 1

Open in new window


Another solution expects just one value for each date and time in given week and also supposes the first two columns are of char data type so their concatenation is possible:
SELECT a.Open, a.Week FROM YourTable a
  JOIN (SELECT MIN(Date+Time) DT, Week FROM YourTable GROUP BY Week) x 
    ON x.Week = a.Week AND x.DT = a.Date+a.Time

Open in new window

Can you please provide the final expected output from the sample input you have given?
You haven't listed data types, so this is a guess, but you should be able to modify it.  The assumption here is that the DATE and TIME columns are character types (because Oracle doesn't have 2 different types), the date is in format YYYY-MM-DD and the time is in format HH24:MI.  If those are all true, then the sorting will work.  Note, this does not convert the character fields to date data types because if you had bad data your query would fail.

This query should work in all 3 databases.  Unless the date and time columns are in real date and time data types.  If they are then you need to modify the order by in the ROW_NUMBER function.
SELECT weekno, 
       open, 
       min_open, 
       max_open 
FROM   (SELECT weekno, 
               open, 
               Row_number() 
                 over ( 
                   PARTITION BY weekno 
                   ORDER BY "DATE"|| "TIME") rn, 
               Min(open) 
                 over ( 
                   PARTITION BY weekno)      min_open, 
               Max(open) 
                 over ( 
                   PARTITION BY weekno)      max_open 
        FROM   mytab) myt 
WHERE  rn = 1; 

Open in new window

In the future, when posting sample data, put it in the form of CREATE and INSERT statements.  It saves everyone a lot of time, and will likely get more people to look at your question.
To extend results to your requirement is simple:
SELECT  Week, MIN(CASE WHEN rn = 1 THEN Open ELSE null END) as Open, MIN(Low) as Low, MAX(High) as High 
  FROM 
   (SELECT Date, Time,Open,Low,High,Close,week, 
           ROW_NUMBER() OVER (PARTITION BY week ORDER BY Date, time) AS rn
      FROM YourTable) innerq
GROUP BY Week

Open in new window


To add weekly closing values (which may differ from the next week opening) you would need reverse row numbering which you may implement as additional column in the inner query:
SELECT  Week, MIN(CASE WHEN rn = 1 THEN Open ELSE null END) as Open, MIN(Low) as Low, MAX(High) as High,
      MIN(CASE WHEN rn_rev = 1 THEN Close ELSE null END) as Close
  FROM 
   (SELECT Date, Time,Open,Low,High,Close,week, 
           ROW_NUMBER() OVER (PARTITION BY week ORDER BY Date, time) AS rn,
           ROW_NUMBER() OVER (PARTITION BY week ORDER BY Date DESC, time DESC) AS rn_rev
      FROM YourTable) innerq
GROUP BY Week

Open in new window

I take back the would work in all the database types.  The string concatenation is probably different between them.  So, to convert to different databases, it would need minor modifications, but the overall concept of windowed aggregates to get all 3 values in one pass of the data should work.

At least it works with the sample data you provided.
OK, not sure what I was thinking with string concatenation.  You shouldn't need to do that.  This should be more universal.
SELECT weekno, 
       open, 
       min_open, 
       max_open 
FROM   (SELECT weekno, 
               open, 
               Row_number() 
                 over ( 
                   PARTITION BY weekno 
                   ORDER BY "DATE", "TIME") rn, 
               Min(open) 
                 over ( 
                   PARTITION BY weekno)      min_open, 
               Max(open) 
                 over ( 
                   PARTITION BY weekno)      max_open 
        FROM   mytab) myt 
WHERE  rn = 1;

Open in new window

Thanks for all your responses. I have been out all day shopping for all things reduced!!!

Apologies for not providing data types. Here it is:
(Note, I currently have my data in SQL Server)
Date > date
Time > char
open, low, high, close > decimal
week > smallint

If this would make any difference then please let me know otherwise I will try the suggestions already given and get back to you.
Thanks to all.

R
As long as the time is in a format that when sorted as a string that they appear in the correct order, then they should work.
There is one solution in answer ID: 41937890 which expects the date column to be of the char data type. So if it is of Date data type then you have to convert it appropriately:
SELECT a.Open, a.Week FROM YourTable a
  JOIN (SELECT MIN(CONVERT(char(8), Date, 112)+Time) DT, Week FROM YourTable GROUP BY Week) x 
    ON x.Week = a.Week AND x.DT = CONVERT(char(8), a.Date, 112)+a.Time

Open in new window


The solution from answer ID: 41937907 is data type independent and should work on SQL Server. It should also work on Oracle but I did not test it.
Query from ID: 41937909 definitely work with Oracle.

 Queries in ID: 41937907 run against Oracle and seem to produce correct results.

Again, as you are working with time in a CHAR format, it depends on the data.  If it is in a format that sorts correctly as a string, then all of those will work.
The data sample shows time column in correct format for all above queries.
The sample data does show a correct format, but we cannot be sure that all the data conforms to that format.
In such case we couldn't be sure the week no is correct (several different algorithms exist), some time zones corrections and daylight saving influences could also exist etc. etc. :-)

Of course, quality of the solution depends on quality of the specification.
Thanks guys.
All solutions provided have been tested and seem to work up to a point.
First to clarify some the points raised regarding consistency of data, I can confirm that data quality is pretty consistent.
The data column is of DATE type and the time column is of type CHAR.

The problem I face is that not all the rows appear to be processed. I believe the cause of this is that there are duplicate week numbers across the years. So the existing statements seem to confuse the weeks across the different years.
Somehow, the result needs to be grouped by YEAR and then Weeks.

To further clarify, I want the output to show following:

cYEAR :  cWeek  :  cOpen   :  cHigh  :   cLow  

cYear - This is the year (this can be obtained from YEAR(cDate)
cWeek - This is from the table
cOpen - This is the value from the table at the first/open time for the first day of the week
cHigh - This is the highest value for the week (this is obtained from MAX(cHigh)
cLow - This is the lowest value for the week (obtained from MIN(cLow)

Hope this helps futher.

Thanks.
R.
So if you have data belonging to more years then you have to update the query slightly:
SELECT cYear, cWeek, MIN(CASE WHEN rn = 1 THEN Open ELSE null END) as cOpen, MAX(High) as cHigh, MIN(Low) as cLow 
  FROM 
   (SELECT Date, Time, Open, Low, High, Close, Week AS cWeek, YEAR(Date) AS cYear, 
           ROW_NUMBER() OVER (PARTITION BY week ORDER BY Date, time) AS rn
      FROM YourTable) innerq
GROUP BY cYear, cWeek
ORDER BY cYear, cWeek

Open in new window

SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
pcelba,

It doesn't appear your query works with multi years of data.  I tried it and I don't get the COPEN column populated.  My guess is you have an issue with your partition by clause.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
From your sample data, what would you expect as the first open value and first open date and time?
pseudo code for either sql server or oracle -
select min(low) over (partition by weekno order by weekno) as minlow, max(high) over (partition by weekno order by weekno) as maxhigh,
first_value(open) over (partition by weekno order by date, time) as openval
from yourtable;
Thanks guys. You're awesome.

Johnsone, I had to tweek yours slightly to give me highest and lowest for the week as follows:

               Min(cLow)
                 over (
                   PARTITION BY Year(cDate), cWeek) min_low,
               Max(cHigh)
                 over (
                   PARTITION BY Year(cDate), cWwek) max_high

Many thanks
The queries I posted worked fine with the sample data.
Wow. Even when the query works fine it can produce incorrect results... and to calculate minimum and maximum from opening values cannot provide correct results.
Johnsone, no doubt the query worked fine but it just didn't give ALL the output I wanted. So it was only a minor tweek. That's why I provided my adjustment.
Your query looked for MIN(open) and MAX(open) when what I wanted were MIN(cLow) and MAX(cHigh) for the week.

Thanks.
Out of curiosity, did you ever try the first_value function (would still need to add year(cdate) to the partition by ...)?
awking00,
Sorry I'm not a coder but I have keen interest in coding, especially the logic of it. I am not aware of the first_value function. It has not been used for this problem but I'll investigate. Alternatively, you could help me develop your pseudo code given earlier.

R.
I only have Oracle to work with so a sql server equivalent would require using "+" instead of "||" to concatenate,  year(cdate) instead od the Oracle extract(year) function, and a cast or convert function in place of Oracle's to_date function. At any rate using the following transactions table and small subset of data from your sample:
TRANSDT   TIME        OPEN        LOW       HIGH      CLOSE       WEEK
--------- ----- ---------- ---------- ---------- ---------- ----------
10-JUL-14 01:00    1326.47    1324.25    1327.53     1327.4         28
10-JUL-14 01:05    1327.41    1327.06    1327.73    1327.06         28
10-JUL-14 01:10    1327.04    1327.02    1327.72    1327.36         28
10-JUL-14 01:15     1327.4     1327.4    1327.66    1327.54         28
10-JUL-14 01:20    1327.55    1327.07    1327.55    1327.32         28
10-JUL-14 01:25    1327.36    1326.86    1327.37    1327.05         28
I would expect to see 1326.47 as the open, 1324.25 as the minimum low and 1327.73 as the maximum high which is what the following query produces without any subqueries -
select distinct extract(year from transdt) cyear, week cweek
,first_value(open) over (partition by extract(year from transdt), week order by to_char(transdt,'yyyy-mm-dd ')||time) copen
,min(low) over (partition by extract(year from transdt), week order by low asc) clow
,max(high) over (partition by extract(year from transdt), week order by high desc) chigh
from transactions;
     CYEAR      CWEEK      COPEN       CLOW      CHIGH
---------- ---------- ---------- ---------- ----------
      2014         28    1326.47    1324.25    1327.73
Thanks. I'll give that a try.

R.
Why the string concatenation?  Wouldn't this work:

order by transdt, time

Why do the type conversion when you don't need to?
johnsone, I totally agree with you. I think that I am so used to order by dates that contain time elements, I felt the need to concatenate the character time data to the character form for transdate, then convert the whole thing back to a date now containing time elements, all of which is unnecessary.
select distinct extract(year from transdt) cyear, week cweek
,first_value(open) over (partition by extract(year from transdt), week order by transdt,time) copen
,min(low) over (partition by extract(year from transdt), week order by low asc) clow
,max(high) over (partition by extract(year from transdt), week order by high desc) chigh
from transactions;

I was trying to show how the first_value function works and I probably should have just pointed him to the documentation :-)
For SQL Server
https://msdn.microsoft.com/en-us/library/hh213018.aspx 
For Oracle
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions057.htm
awking00,

I have tested your script (with some mods) and it works like a charm. In fact, I prefer the use of the FIRST_VALUE function over the previous versions. Although it's always good to have more than one method at one's disposal.

Many thanks for your help.

R.
awking00,

One thing I just realised with your script worth noting is that it has a slower performance than say johnstone's. I just thought it's worth pointing that out as something for everyone reading this to consider.

R.