March 4, 2009 at 11:23 am
Hi.
I'm working on a live database where there has been a design flaw relating to data type.
Thing is, it´s a database on education, with records going back to the 1800s.
Its a datatype varchar and what I need to do is select count number of graduates from the oldest date ie 1850´s and the same for the latest data which would be from 2008 for example.
Ideally I would like to change the datatype to Datetime, but lack the knowledge to do a cast convert and just insert the year yyyy.
Is there anyone here that could point me in the right direction on how to solve the count query?
I cant use where year = because its different for each school so I need to determin the value of the oldest record dynamically.
I hope this makes sense but if not please delete and apologies.
March 4, 2009 at 11:36 am
Can you provide a sample of what the data looks like? I'm not quite visualizing it from your description.
If, for example, you have dates that look like this, "1/2/1800", meaning 2 January 1800, it should be possible to use:
select datepart(year, cast(MyColumn as datetime))
from dbo.MyTable;
Will that do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 4, 2009 at 11:40 am
Yes of course, sorry if I was unclear.
Column name is Year, datatype is varchar and it looks like a 4 digit year ie 1800, but again I must stress it is a varchar and not datetime datatype.
March 4, 2009 at 12:50 pm
It sounds like you might be able to get away with just looking at the Year as an integer using:
CAST([Year] AS int)
You probably want this one though:
CAST([Year] AS datetime)
Example:
If a row has a Year of '2008' this will return '1/1/2008 00:00:000'
This should work for your question though.
Cheers,
Brian
March 4, 2009 at 1:14 pm
You need the number of entries per year? Right?
How about something as simple as:
select Year, count(*) as Qty
from dbo.MyTable
group by Year
order by Year;
Even if it's varchar, that should work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 4, 2009 at 2:08 pm
That worked cheers mate...
one more question, say I wanna take first and last year and skip all the ones in the middle?
March 4, 2009 at 2:25 pm
Assuming you're on SQL 2005 (from the forum you posted in), something like this should work:
create table #T (
ID int identity primary key,
Year char(4));
insert into #T (Year)
select '1800' union all
select '1800' union all
select '1900' union all
select '2000' union all
select '2008';
;with
Years (Yr, Qty) as
(select Year, count(*)
from #T
group by Year),
FirstYear (Yr, Qty) as
(select top 1 Yr, Qty
from Years
order by Yr),
LastYear (Yr, Qty) as
(select top 1 Yr, Qty
from Years
order by Yr desc)
select *
from FirstYear
union
select *
from LastYear;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 4, 2009 at 11:12 pm
Punketal (3/4/2009)
That worked cheers mate...one more question, say I wanna take first and last year and skip all the ones in the middle?
Just curious... why would you need to do that? I mean, what's the business case for that? I like to learn these types of "caveats". Thanks in advance.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2009 at 1:49 am
It was mostly to do with U/I presentational stuff, showing how many had graduated from the school it´s first and last years.
I solved it with some VB programming so it´s ok, but still I´m not really happy with having a varchar datatype when it comes to these dates.
When the guys created it, they simply imported data straight from excel and it´s a bitch to work with.
But alas at least it teaching me SQL better so its not all bad.
Eventually I will have to come crawling here begging for help with converting these to datetime.:P
But thanks so much for all the help guys.
March 5, 2009 at 7:29 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 5, 2009 at 1:38 pm
Punketal (3/5/2009)
It was mostly to do with U/I presentational stuff, showing how many had graduated from the school it´s first and last years.I solved it with some VB programming so it´s ok, but still I´m not really happy with having a varchar datatype when it comes to these dates.
When the guys created it, they simply imported data straight from excel and it´s a bitch to work with.
But alas at least it teaching me SQL better so its not all bad.
Eventually I will have to come crawling here begging for help with converting these to datetime.:P
But thanks so much for all the help guys.
Very cool... thank you for taking the time to explain. We see a lot of requirements that sometimes gets us scratching our head as to wondering why someone would ever want to do something like that... answers like yours help us help others better when it comes to reading the minds of people who don't quite know how to describe what they really want. Thanks again.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2009 at 10:11 am
Hi guys, just a quick update on the Group by order by question.
It´s working like a charm, but now, there is a new requirement.
This is my code : select braut count(*) as Qty from Brautirnemar_V where skID = "&Request("SkID") &" group by braut order by braut
What if I need to add an ID field in the select clause?
I have tried doing that and what happens is I first get an error msg like this :
Column 'Brautirnemar_V.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Anyone have any ideas?
March 12, 2009 at 2:30 pm
select ID,braut count(*) as Qty from Brautirnemar_V where skID = "&Request("SkID") &" group by ID,braut order by braut
As long as you have a field your grabbing that's not summarized, just add it to the group by clause. Good luck!
Cheers,
Brian
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply