A bit of an abstract problem

  • 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.

  • 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

  • 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.

  • 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

  • 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

  • That worked cheers mate...

    one more question, say I wanna take first and last year and skip all the ones in the middle?

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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?

  • 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