September 11, 2007 at 7:10 am
I have this code for selecting the min and max values for a 1 minute period, this looks at the last date in table demand spread and looks 1 min back.
So i can pull the min and max from that table on a float field.
What I would like to do is pull through min and max bid price and also a field called ESDSTOTAL and ER2DSTOTAL, working from the same record as ESBIDPRICE AND ER2BIDPRICE.
So I would pull through the min() ER2BIDPRICE and sql will bring the ER2DSTOTAL on the same record with that.
How do I do that
Thanks
rsRecordset7.Open "SELECT MIN(s.ER2BIDPRICE) AS ER2BIDPRICE, MAX(s.ER2BIDPRICE) AS ER2BIDPRICE, MIN(s.ESBIDPRICE) AS ESBIDPRICE, MAX(s.ESBIDPRICE) AS ESBIDPRICE FROM dbo.DemandSpread s INNER JOIN (SELECT MAX(s1.[TimeStamp]) AS MaxTimeStamp FROM dbo.DemandSpread s1) d ON s.[TimeStamp] BETWEEN DATEADD(mi, -1, d.MaxTimeStamp) AND d.MaxTimeStamp", gcnConnect
If rsRecordset7.EOF = False Then
Sheet1.Range("D16") = rsRecordset7.Fields(0).Value
Sheet1.Range("E16") = rsRecordset7.Fields(1).Value
Sheet1.Range("F16") = rsRecordset7.Fields(2).Value
Sheet1.Range("G16") = rsRecordset7.Fields(3).Value
End If
rsRecordset7.Close
September 11, 2007 at 7:35 am
try this.
I may have made some syntax mistakes but I am sure you will get the jist
SELECT
MINER2BIDPRICE
,
MAXER2BIDPRICE
,
MAXESBIDPRICE
,
MINESBIDPRICE
FROM
dbo.DemandSpread s
(SELECT MIN(ER2BIDPRICE) as MINER2BIDPRICE,[TimeStamp] as MINER2BIDPRICE FROM DemandSpread GROUP BY[TimeStamp]) [1]
INNER JOIN [1].[TimeStamp] BETWEEN DATEADD(mi, -1, d.MaxTimeStamp) AND d.MaxTimeStamp
(SELECT MAX(ER2BIDPRICE) as MINER2BIDPRICE,[TimeStamp] as MAXER2BIDPRICE FROM DemandSpread GROUP BY[TimeStamp]) [2]
INNER JOIN [2].[TimeStamp] BETWEEN DATEADD(mi, -1, d.MaxTimeStamp) AND d.MaxTimeStamp
(SELECT MAX(ESBIDPRICE) as MINER2BIDPRICE,[TimeStamp] as MAXESBIDPRICE FROM DemandSpread GROUP BY[TimeStamp]) [3]
INNER JOIN [3].[TimeStamp] BETWEEN DATEADD(mi, -1, d.MaxTimeStamp) AND d.MaxTimeStamp
(SELECT MIN(ESBIDPRICE) as MINER2BIDPRICE,[TimeStamp] as MINESBIDPRICE FROM DemandSpread GROUP BY[TimeStamp]) [4]
INNER JOIN [4].[TimeStamp] BETWEEN DATEADD(mi, -1, d.MaxTimeStamp) AND d.MaxTimeStamp
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 11, 2007 at 7:55 am
yes thats what I have in my code
I was after when sql finds the min or max bidprice to bring through the dstotal on that record
September 11, 2007 at 8:51 am
mmm ok he he he
I normally find this to be messy trying to do it one query.
So what I do is get the values into a Table Variable or temp table and then join back using the dates you have a collected.
Does this make sense?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 11, 2007 at 9:06 am
err no not really very new at sql queries
the time is in seconds
September 12, 2007 at 9:04 am
lost on this one
September 17, 2007 at 8:44 am
lost on this one
September 17, 2007 at 9:36 am
You're going to need to create this as a stored procedure and invoke it - that will change your OpenRecordset syntax somewhat.
--build your scratch table to work in
create
table #mytable(lowER2BIDPRICE money,lower2dstotal float,
HighER2BIDPRICE
money,higher2dstotal float,
lowESBIDPRICE
money,lowesdstotal float,
highESBIDPRICE
money,highesdstotal float, MaxTimeStamp datetime)
--insert max and mins
insert
#mytable (lowER2BIDPRICE,HighER2BIDPRICE, lowESBIDPRICE, highESBIDPRICE, MaxTimeStamp)
SELECT
MIN(s.ER2BIDPRICE) AS lowER2BIDPRICE, --change the labels , since you can't have 2 fields with same name in a select
MAX
(s.ER2BIDPRICE) AS HighER2BIDPRICE, --change the labels , since you can't have 2 fields with same name in a select
MIN
(s.ESBIDPRICE) AS lowESBIDPRICE, --change the labels , since you can't have 2 fields with same name in a select
MAX
(s.ESBIDPRICE) AS highESBIDPRICE, --change the labels , since you can't have 2 fields with same name in a select
MaxTimeStamp
FROM
dbo.DemandSpread s
INNER JOIN
(SELECT MAX(s1.[TimeStamp]) AS MaxTimeStamp FROM dbo.DemandSpread s1) d ON s.[TimeStamp]
BETWEEN DATEADD(mi, -1, d.MaxTimeStamp) AND d.MaxTimeStamp
--go get the related info
update
m
set
m.lower2dstotal=s.er2dstotal
from
#mytable m inner join dbo.DemandSpread s on s.timestamp between s.[TimeStamp]
BETWEEN DATEADD(mi, -1, d.MaxTimeStamp) AND d.MaxTimeStamp and m.lowER2BIDPRICE = er2bidprice
update
m
set
m.higher2dstotal=s.er2dstotal
from
#mytable m inner join dbo.DemandSpread s on s.timestamp between s.[TimeStamp]
BETWEEN DATEADD(mi, -1, d.MaxTimeStamp) AND d.MaxTimeStamp and m.highER2BIDPRICE = er2bidprice
update
m
set
m.lowesdstotal=s.er2dstotal
from
#mytable m inner join dbo.DemandSpread s on s.timestamp between s.[TimeStamp]
BETWEEN DATEADD(mi, -1, d.MaxTimeStamp) AND d.MaxTimeStamp and m.lowESBIDPRICE = er2bidprice
update
m
set
m.highesdstotal=s.er2dstotal
from
#mytable m inner join dbo.DemandSpread s on s.timestamp between s.[TimeStamp]
BETWEEN DATEADD(mi, -1, d.MaxTimeStamp) AND d.MaxTimeStamp and m.highESBIDPRICE = er2bidprice
--return the results
select
lowER2BIDPRICE ,lower2dstotal ,
HighER2BIDPRICE
,higher2dstotal ,
lowESBIDPRICE
,lowesdstotal ,
highESBIDPRICE
,highesdstotal
from
#mytable
--clean up
drop
table #mytable
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 17, 2007 at 10:07 am
You could try something horrible like:
DECLARE @TimeFrom datetime
,@TimeTo datetime;
SELECT @TimeFrom = DATEADD(mi, -1, MAX([TimeStamp]))
,@TimeTo = MAX([TimeStamp])
FROM dbo.DemandSpread;
SELECT
D1.MinER2BidPrice
,D2.MinER2DSTotal
,D1.MaxER2BidPrice
,D3.MaxER2DSTotal
,D1.MinESBidPrice
,D4.MinESDSTotal
,D1.MaxESBidPrice
,D5.MaxESDSTotal
FROM (
SELECT MIN(S1.ER2BidPrice) AS MinER2BidPrice
,MAX(S1.ER2BidPrice) AS MaxER2BidPrice
,MIN(S1.ESBidPrice) AS MinESBidPrice
,MAX(S1.ESBidPrice) AS MaxESBidPrice
FROM dbo.DemandSpread S1
WHERE S1.[TimeStamp] > @TimeFrom
AND S1.[TimeStamp] <= @TimeTo
) D1
JOIN (
SELECT S2.ER2BidPrice
,MAX(S2.ER2DSTotal) AS MinER2DSTotal
FROM dbo.DemandSpread S2
WHERE S2.[TimeStamp] > @TimeFrom
AND S2.[TimeStamp] <= @TimeTo
GROUP BY S2.ER2BidPrice
) D2
ON D1.MinER2BidPrice = D2.ER2BidPrice
JOIN (
SELECT S3.ER2BidPrice
,MAX(S3.ER2DSTotal) AS MaxER2DSTotal
FROM dbo.DemandSpread S3
WHERE S3.[TimeStamp] > @TimeFrom
AND S3.[TimeStamp] <= @TimeTo
GROUP BY S3.ER2BidPrice
) D3
ON D1.MaxER2BidPrice = D3.ER2BidPrice
JOIN (
SELECT S4.ESBidPrice
,MAX(S4.ESDSTotal) AS MinESDSTotal
FROM dbo.DemandSpread S4
WHERE S4.[TimeStamp] > @TimeFrom
AND S4.[TimeStamp] <= @TimeTo
GROUP BY S4.ESBidPrice
) D4
ON D1.MinESBidPrice = D4.ESBidPrice
JOIN (
SELECT S5.ESBidPrice
,MAX(S5.ESDSTotal) AS MaxESDSTotal
FROM dbo.DemandSpread S5
WHERE S5.[TimeStamp] > @TimeFrom
AND S5.[TimeStamp] <= @TimeTo
GROUP BY S5.ESBidPrice
) D5
ON D1.MaxESBidPrice = D5.ESBidPrice
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply