November 8, 2007 at 8:09 am
Lol.. thank you guys. Thank you for your time as well. Of course i have data for the july - august . Ill put a syntax like jason's and then i'll add in the where statement and month(date_stamp) not in (7,8) lol.
Thank you for your time! 🙂
November 8, 2007 at 8:30 am
😀
Now that's funny, but it just goes to show something that I learned a long time ago and struggle to remember every day and that's "Don't assume anything" .... *grin*
No worries Dfalir, you were just making me think I was going nuts. :w00t:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 8, 2007 at 8:54 am
This code shows how to convert a date to an Academic Year starting on September 1 of each year.
select
a.MyDate,
[Academic Year] = year(dateadd(mm,-8,a.MyDate))
from
MyTable a
where
a.MyDate between '20070831' and '20080902'
Results:
MyDate Academic Year
------------------------------------------------------ -------------
2007-08-31 00:00:00.000 2006
2007-09-01 00:00:00.000 2007
2007-09-02 00:00:00.000 2007
...
...
...
2008-08-30 00:00:00.000 2007
2008-08-31 00:00:00.000 2007
2008-09-01 00:00:00.000 2008
2008-09-02 00:00:00.000 2008
November 8, 2007 at 12:32 pm
Dfalir (11/8/2007) the management tells me the academic year is from 1st of September (2005) till the End of June (2006) and so on for the year
OK, now not only your management, but you and some of us know what's the academic year.
Does you system know about it?
Where this information is stored in your database?
I'm not sure your server can read your mind, so you must pass necessary knowledge to it if you want it to do the job.
And before you go on and hardcode the boundaries of academic year like Michael did think about a case when your management will shorten the next academic year by one week due to some special very important either nation-wide or local event.
_____________
Code for TallyGenerator
December 13, 2007 at 7:47 am
This is great, have learnt loads. Thanks Even though am using MySQL and Oracle still very useful.
Quick one: most academic years are displayed as "06/07" any clue as to how to get your queries above to label these years such?
Jamie
December 13, 2007 at 7:57 am
try something like this:
select right(datepart(yy,startDate),2)+'/'+right(datepart(yy,endDate),2) as YearLabel,
etc....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 13, 2007 at 8:01 am
jamie.denman (12/13/2007)
This is great, have learnt loads. Thanks Even though am using MySQL and Oracle still very useful.Quick one: most academic years are displayed as "06/07" any clue as to how to get your queries above to label these years such?
Jamie
In my humble opinion, that's presentation code and should be kept out of SQL as much as possible. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 13, 2007 at 8:24 am
In my humble opinion, that's presentation code and should be kept out of SQL as much as possible. :D[/quote]
I agree, but am using iReports which I know even less about getting it to show what I want than SQL.
Thanks to you both for getting back so quick. Excellent.
Had to use CONCAT in MySQL as the str + str added the strings so dates looked like "13" for 06/07 !!! At least it's trying to be smart. Oracle will probaly do something else again - gee I love "standard" SQL!
Oh, and need to make GROUP BY exactly the same as the SELECT statements.
Thanks again
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply