last X weeks in chart

[cattys]

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:

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

 

[Glitchbane]

I don't recognize the "{ fn WEEK(hit_date) }" part of the code... but I'm mostly 7.0...

In SQL 7, you can get away with it by adding a leading zero for weeks that are 1 character. If fn WEEK is a function, you could try changing that so it adds a leading zero if necessary.

That fixes only half the problem... 01/2002 and 01/2001 would still be next to each other on the list.

I would suggest putting the year first, then two digit week 2001/01 instead of 01/2001. This would keep them in numeric order.

The output I believe you're looking for can be accomplished in SQL 7 with this query (forgive the width of the thing):

SELECT TOP 10 COUNT(*) AS anzahl,
CONVERT(CHAR(4),DATEPART(YEAR,hit_date)) + '/' + REPLICATE('0',2 - LEN(CONVERT(VARCHAR(2),DATEPART(WEEK,hit_date)))) + CONVERT(VARCHAR(2),DATEPART(WEEK,hit_date)) AS Kw
FROM wns_counter (NOLOCK)
GROUP BY CONVERT(CHAR(4),DATEPART(YEAR,hit_date)) + '/' + REPLICATE('0',2 - LEN(CONVERT(VARCHAR(2),DATEPART(WEEK,hit_date)))) + CONVERT(VARCHAR(2),DATEPART(WEEK,hit_date))
ORDER BY Kw DESC

This does not use the fn WEEK, but the DATEPART function instead. It also puts the year in front with a leading zero for the week (if necessary). It also gets rid of the derrived table.

 

[cattys]

Many Thanks!

I dunno what this fn thing means, I just typed week(hit_date) in the Query and it became the fn thing.

Your Query works fine.
I am no SQL expert, therefore I am not able to understand the whole statement but it works, and this is the main thing.

Anyway, don't mind, I will learn this with the time being.

Thanks again.