Hi,
I have a counter that inserts a time stamped record every time it is launched.
Now I have grouped the results by year(hit_date), week(hit_date) and counted the records.
This alone works fine but now I want to create a dynamic chart that only displays the last X weeks.
I have done this with following query:
Now I recognized that it gives me the top x count(*) and not the global top x. Order by kw doesnt work neither (no leading "0" makes that 1/2002 comes before 10/2002 and this before 46/2001.
My Problem:
How can I order the output by date (1/2002 is bigger than 46/2001) and get only latest x of it?
I'ld appreciate any help,
Cattys
I have a counter that inserts a time stamped record every time it is launched.
Now I have grouped the results by year(hit_date), week(hit_date) and counted the records.
This alone works fine but now I want to create a dynamic chart that only displays the last X weeks.
I have done this with following query:
Code:
SELECT anzahl, CAST(woche AS nvarchar) + '/' + CAST(jahr AS nvarchar) AS Kw
FROM
(SELECT TOP 10 COUNT(*) AS anzahl, YEAR(hit_date) AS jahr, { fn WEEK(hit_date) } AS woche
FROM dbo.wns_counter GROUP BY YEAR(hit_date), { fn WEEK(hit_date) } ORDER BY YEAR(hit_date) DESC, { fn WEEK(hit_date) }) DERIVEDTBL
ORDER BY jahr, woche
Now I recognized that it gives me the top x count(*) and not the global top x. Order by kw doesnt work neither (no leading "0" makes that 1/2002 comes before 10/2002 and this before 46/2001.
My Problem:
How can I order the output by date (1/2002 is bigger than 46/2001) and get only latest x of it?
I'ld appreciate any help,
Cattys