Select Total Count if different by 5% to yesterday
-
Hi there,
I currently have a query that returns a count for sids in my table that have a null or <0 price on each day. I'm trying to take this query further and just returns those counts for each day where the count is greater than 5% of the previous day and the following day.
My present query is:
Declare @StartDate DateTime
Declare @EndDate DateTime
Declare @CurrentDate DateTime
Declare @WeekDay int
Declare @qdate varchar(20)
Declare @strqry varchar(2000)
Declare @strqry1 varchar(2000)
Select @StartDate = '2005.01.01'
Select @EndDate = '2005.04.15'
Select @CurrentDate = @StartDate
While @EndDate >= @CurrentDate
Begin
Select @WeekDay = DatePart(dw, @currentdate)
IF (@Weekday != 1) and (@weekday != 7)
BEGIN
insert into #datelist values (@currentdate)
end
select @currentdate=dateadd(d, 1, @currentdate)
End
select * from
(select q.qdate as qDate, 'FIXED INCOME' as DataRange, count(q.qsid)as SidCount from qpricetruv2005 q, #datelist d
where d.datecheck = q.qdate and q.qsid > 5000000 and q.qsid <= 890000000 and q.qprice is not null group by q.qdate
UNION ALL
select q.qdate as qDate, 'EQUITY' as DataRange, count(q.qsid) as SidCount from qpricetruv2005 q, #datelist d
where d.datecheck = q.qdate and qsid >= 0 and qsid <= 5000000 and q.qprice is not null group by q.qdate) as overall
order by qdate, datarange
Any ideas how I can factor in the percentage check for the count(qsid) Plus I need to do this check for both 'qsid' ranges in the table Equity and Fixed Income.
Any help would be greatly appreciated!!!
Thanks
S
-
First select each days totals into a temp table
so that you have date and total for that date on each row
Then self join
select a.xxx, etc.
from #t a
JOIN #t b on a.datecol = DATEADD(dd, 1, b.datecol )
WHERE a.datecol = --trim the getdate appropriately to mean "today" if you only want to look at today or leave this out if everyday is compared to prior
AND a.sumcol > b.sumcol * 1.05
Or embed the temp table resolution in the From clause.
do a 3x self join if you want to include the day before the day before...
-
and to add to johns' suggestions try to limit the calculations on the temp table to a date range
hth
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply