weekly report
-
We have the data for llast 3 years, We would like to run a weekly report for all the data of three years.Howz can i do that?
Thanks in advance
-
If the data is in the same table, you could group by the week and year:
SELECT
DatePart(yyyy, DateField) as "Year"
, DatePart(wk, DateField) as "Week"
FROM MyTable
GROUP BY
DatePart(yyyy, DateField)
, DatePart(wk, DateField)
ORDER BY
DatePart(yyyy, DateField)
, DatePart(wk, DateField) -
So you have a column that indicates date, right? Do you need to see only last week's data or all three years data grouped by week?
In the first case, just use the WHERE clause and apply appropriate date functions (see "Date and Time Functions" in BOL).
In the second case, use the GROUP BY clause with appropriate aggregate functions (SUM, AVG, MAX, etc.) and date functions (see BOL).
-
Hopefully though, you're not working on a production system. With all due respect, if you're asking a question as simple as this you shouldn't be playing with business critical SQL data. I'm not trying to belittle you, but SQL is a very poerful tool and a little knowledge can be a very dangerous thing.
-
You just need to use GROUP BY with the appropriate aggregate function(s) in the SELECT statement.
SELECT DatePart(wk, Date_field), DatePart(yyyy, Date_field), put aggregate functions here
FROM table
GROUP BY DatePart(wk, Date_field), DatePart(yyyy, Date_field)
-
come on now tim, be nice - the guy's gotta learn somehow...and querying isn't that dangerous if he's working at 3am, right? 🙂
if you're a newbie to all this, get your dba to push the data into an access table and you can play with it from your desktop...!
-
There is absolutely no shame in asking a valid question. The only poor question is the one not asked. Please keep forum posts respectful of other's abilities and experience levels.
-
quote:
There is absolutely no shame in asking a valid question. The only poor question is the one not asked. Please keep forum posts respectful of other's abilities and experience levels.Agreed with that.
From my point of view, one of the basic intentions of this forum is knowledge transfer. From the ones who know more (in fact, work longer with SQL Server) to the ones who just started or use it not so intensive. So, I think it's not justifiable to say: 'Don't ask such a silly question'.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank and jpipes, my post wasn't at all disrespectful or implying that it was a silly question. I agree that 'The only poor question is the one not asked'. I was simply pointing out the pitfalls one with little knowledge of the bigger picture may fall into. I'm sure bruce_reid (or his employers) would not be terribly impressed if he tried to venture his toes a little further into the Transact waters and screwed a live system up big time.
The best way to learn is to ask dumb questions, but by answering a questioner that is showing they have little knowledge of the big picture without qualifying the implications, is dangerous.
Obviously, the above is all said with the greatest of respect to all posters (should I be found guilty of the heinous crime of 'dissing' my peers again!!)
-
Hi Tim,
never mind! Let's forget it
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
'make love, not war'
'can't we all just get along?'
'peace, my brotha'
or, you could just blow that whole notion off altogether and, instead, use this:
'I know what you're thinking. Did he fire six shots or only five? Well, to tell you the truth, in all this excitement, I've kinda lost track myself. But being as this is a .44 Magnum, the most powerful handgun in the world, and would blow your head clean off, you've got to ask yourself one question: Do I feel lucky? Well, do ya punk?'
-
quote:
'I know what you're thinking. Did he fire six shots or only five? Well, to tell you the truth, in all this excitement, I've kinda lost track myself. But being as this is a .44 Magnum, the most powerful handgun in the world, and would blow your head clean off, you've got to ask yourself one question: Do I feel lucky? Well, do ya punk?'that's from Dirty Harry, isn't it?
Can you elaborate more on this?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
it's clint all right, and it don't need no elaboration...! 🙂
-
quote:
it's clint all right, and it don't need no elaboration...! 🙂come on, bear with a slow german, trying to get behind the intention of your post
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
the intention was humor...it's monday morning, i'm sitting in this hole-of-a-cube at this suck-ass job, and i really need some...if you'd like to have even more fun this morning, go here where i found the quote:
http://www.angelfire.com/oh/quotations/moviequotes.html
enjoy, and have a beautiful day - i'm friggin sure i will...
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply