SQL NEXTED QUERIES

Question

you all are so damn good at this sql stuff, I'd rather go get some margaritas than pull my hair out for the next 2 - 4 hours (more) ... !! Can someone help me to get the following queries strung together on this table..

CREATE TABLE [dbo].[TblNdx](
[ticker] [nvarchar](12) NOT NULL,
[date] [datetime] NULL,
[time] [datetime] NULL,
[open] [float] NULL,
[high] [float] NULL,
[low] [float] NULL,
[close] [float] NULL,
[volume] [float] NULL,
[change] [float] NULL
 ) ON [PRIMARY]

I need a query that gets the last xxx records WHERE ...

SELECT ticker, date, time, [open], high, low, [close], volume, change
FROM   TblNdx 
WHERE  1=1
AND (ticker = '^IXIC') 
AND (date =
        (
            SELECT TOP (1) date as date1
            FROM TblNdx AS TblNdx_1
            WHERE (ticker = '^IXIC')
            ORDER BY date DESC
        )
    )
ORDER BY time DESC

AND Then ADD ANOTHER COLUMN TO COMPUTER LASTCLOSE WHICH IS THE PREVIOUS CLOSE FOR THE PRECEDING DAY... DATEDIFF(Day, date, date1) = 1 AND Time is TOP 1 TIME FROM 2nd PREVIOUS DAY... in other words the closing price from the previous day... the nested SELECT TOP 1 Clauses are killing me... anyone good at this?

I did not mean disrespect. I am a geek that drinks margaritas just like everyone... ! Anyway, I appreciate edits. To get the the previous last close date I am trying to modify.. any help with this code it says 'only one expression can be specified in SELECT list when the subquery is not introduced with EXISTS..

I use Visual Studio to run queries and it indents very strange ways...and I guess I don't really know how to indent properly!!

SELECT ticker
    , date
    , time
    , [open]
    , high
    , low
    , [close]
    , volume
    , change
    , (SELECT TOP (1) time AS time1, [close] AS lastclose
         FROM TblNdx WHERE (DATEDIFF(day, date, date1) = 1) 
         AND (ticker = '^IXIC') ORDER BY time1 DESC) AS Expr1
    FROM TblNdx AS TblNdx_2
    WHERE (1 = 1) AND (ticker = '^IXIC') 
    AND (date = (SELECT TOP (1) date AS date1
                    FROM TblNdx AS TblNdx_1 
                    WHERE (ticker = '^IXIC')
                    ORDER BY date1 DESC))
    ORDER BY time DESC

Answer 1

     SELECT ticker
 , date
 , time
 , [open]
 , high
 , low
 , [close]
 , volume
 , change
 , (SELECT TOP (1) [close] FROM TblNdx AS t2
   WHERE (DATEDIFF(day, date, t1.date) = 2) 
                             AND (ticker = t1.ticker)
   ORDER BY time DESC) AS LastClose
 FROM TblNdx AS t1
 WHERE (ticker = '^IXIC') 
 AND (date = (SELECT TOP (1) date
  FROM TblNdx AS t3
                    WHERE (ticker = '^IXIC')
 ORDER BY time DESC))
 ORDER BY time DESC

I should have been clearer, I needed last closing date all prices (close) most recent top 1 date... and then the last previous dates close prices TOP 1 time only...

CS

Answer 2

How is this?

Note: I did not test, expect typos

;WITH todaydate as
(
  SELECT max(date) as date, ticker
  FROM tblndx
  group by ticker
),  yesterdaydate as
(
  SELECT max(time) as time, ticker
  FROM tblndx
  group by ticker
  having  DATEDIFF(Day, date, getdate()) = 1
)
SELECT ticker
    , date
    , time
    , [open]
    , high
    , low
    , [close]
    , volume
    , change
    , yeserdaydate.time as [yeserday time]
from tblndx
inner join todaydate on tblndx.ticker = todaydate.ticker and tblndx.date = todaydate.date
left join yesterdaydate on tblndx.ticker = yeserdaydate.ticker