I'm stumped by this SQL problem that I suspect will be easy pickings for someone out there.
I have a table that contains rows representing several daily lists of ranked items. The relevent fields are as follows: ID, ListID, ItemID, ItemName, ItemRank, Date.
I have a query that returns the items that were on a list yesterday but not today (Items Off List) as follows:
Select ItemID, ListID, ItemName, convert(varchar(10),MAX(date),101) as date, COUNT(ItemName) as days_on_list
From Table
Group By ItemID, ListID, ItemName
Having Max(date) = DATEADD("d",-1,convert(varchar(10),getdate(),101)) and ListID = 1
Order By ListID, ItemName, COUNT(ItemName)
Basically I'm looking for records where the max date is yesterday. It works fine and shows the number of days each item was previously on the list (although not necessarily consecutively, but that's fine for now).
The problem is when I try to add ranking to see what yesterday's rank was. I tried the following:
Select ItemID, ListID, ItemName, ranking, convert(varchar(10),MAX(date),101) as date, COUNT(ItemName) as days_on_list
From Table
Group By ItemID, ListID, ItemName, ranking
Having Max(date) = DATEADD("d",-1,convert(varchar(10),getdate(),101)) and ListID = 1
Order By ListID, ItemName, ranking, COUNT(ItemName)
This returns a great deal more records than the previous query so something isn't right with it. I want the same number of records, but with the ranking included. I can get the rank by doing a self-join with a subquery and getting records where the ItemID occurs yesterday but not today - but then I don't know how to get the Count any more.
Appreciation in advance for any help with this.
======== SOLVED ==============
Select ItemID, ListID, ItemName, ranking, convert(varchar(10),MAX(date),101) as date, COUNT(ItemName) as days_on_list
from Table T
Where date = DATEADD("d",-1,convert(varchar(10),getdate(),101)) and ListID = 1 and T.ItemID Not In
(select T.ItemID from Table T
join Table T2 on T.ItemID = T2.ItemID and T.ListID = T2.ListID
where T.date = DATEADD("d",-1,convert(varchar(10),getdate(),101)) and T2.date = convert
(varchar(10),getdate(),101) and T.ListID = 1)
Group by ItemID, ListID, ItemName, ranking
Basically, what I did was create a subquery that finds all items that appear in both days, and finds items that appeared yesterday but are not in the set of items that appeared both days. Then I was able to do the aggregate function and grouping correctly. I would NOT be surprised if this is more convoluted than necessary but I understand it and can modify it as needed and performance doesn't seem to be an issue.
Thanks everyone for the assist.
