February 22, 2011 at 5:16 pm
Hi,
I have a simple table produced from a query pulling from other tables.
StartSearch = date
ArrivalDate = date
Datediff = float (difference in days between searchdate and arrivaldate)
Basically, with over 5000 records (depending on date range), I want to count how many 1's there are in the datediff column, how many 2's, 3's etc, and then rank them. Ranking isn't my main concern, I can do that easily enough.
I'm more interested in producing a table from that that would show 1 = 248, 2 = 356, 3 = 412 etc..
Any ideas out there?
February 23, 2011 at 3:17 am
you can do it by converting the datediff to varchar and use charindex on that.
example:-
SELECT CHARINDEX('1',DATEDIFF(mm,GETDATE()-30,GETDATE()))
you can manipulate the query as you want.
February 23, 2011 at 9:20 am
Thanks, but I actually managed to do it using the following. It was so easy in the end:
--------------------
select top(@number)
row_number() over (order by count(datedifference) desc)as [Rank],
datedifference,
COUNT(datedifference) as Number
from #search
group by datedifference
order by Number desc
-----------------------
This above works exactly how I want it to, which is awesome. However my next question is.. I'd love to be able to pull the report in 4 different sections of a specified day. Eg. What are the top 10 from 00:00 - 07:00, 7:01-13:00, 13:01 - 17:00....so on.
While the variables in my stored procedure call for start and end date, is there a way I could insert into my temp table using hours 00 to 07 of @report_start?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply