Post reply

Using a query to return the next Sunday

  • October 26, 2010 at 10:02 am

    #230942

    I have the following query:

    select

    a.MyDate,

    Sunday = dateadd(dd,((datediff(dd,'17530107',a.MyDate)/7)*7)+7,'17530107')

    from

    ( -- Test Data

    select MyDate = convert(datetime,'20100930')

    ) a

    and what I'd like to do is instead of offering a datetime, I have a SQL field that I need to pull from with the date. Can anyone offer me a suggestion on how to do that?

    What I see changing is the

    select MyDate = [exceptions].exceptiondate.

    I tried that though and I get an error:

    The column prefix 'exceptions' does not match with a table name or alias name used in the query.

    I've also tried this query:

    select

    a.MyDate,

    Sunday = dateadd(dd,((datediff(dd,'17530107',a.MyDate)/7)*7)+7,'17530107')

    from

    ( -- Test Data

    select MyDate payrolldate from payroll

    ) a

    and got this error:

    Server: Msg 207, Level 16, State 3, Line 1

    Invalid column name 'MyDate'.

    Server: Msg 207, Level 16, State 1, Line 1

    Invalid column name 'MyDate'.

    Server: Msg 207, Level 16, State 1, Line 1

    Invalid column name 'MyDate'.

    can anyone offer assistance on this?

    Thank you,

    Doug

  • October 26, 2010 at 10:12 am

    #1242402

    you are referencing objects that don't exist.

    What does this return?

    select MyDate payrolldate from payroll

    If it's the same error, then MyDate is not a column in this table. Pick a valid column.

    select column_name from information_schema.tables where table_name = 'Payroll'

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • October 26, 2010 at 10:16 am

    #1242409

    Does this return anything on your database?

    select payrolldate,

    Sunday = dateadd(dd, ((datediff(dd, '17530107', payrolldate)/7)*7)+7, '17530107')

    from payroll

  • October 26, 2010 at 10:46 am

    #1242438

    andrew,

    yes that does. How would I go about making sure it only returns the last entry though? In other words, I have 3 records in my table, all 3 with different dates, but I only want the most current date to be returned back to me.

    Thank you

    Doug

  • October 26, 2010 at 12:17 pm

    #1242493

    Andrew,

    I forgot, I have another field that has a yes or no value that I can use as a "qualifier" for the payroll date.

    so I have this:

    select payrolldate,

    Sunday = dateadd(dd, ((datediff(dd, '17530107', payrolldate)/7)*7)+7, '17530107')

    from payroll

    where payrollran = 'no'

  • October 26, 2010 at 12:19 pm

    #1242495

    Wouldn't the Max() operator get you 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

  • October 26, 2010 at 1:02 pm

    #1242509

    G,

    It might but since I already have this in place and I'm going to be using the variable anyway, why not use it for this as well.

    Doug

  • October 27, 2010 at 6:34 am

    #1242820

    Because you can't guarantee which row will be assigned to a variable if your assignment can return multiple rows, and you need the last one per your posted requirements. Or am I misunderstanding something in your requirements?

    - 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

  • October 27, 2010 at 6:42 am

    #1242830

    doug 40899 (10/26/2010)

    G,

    It might but since I already have this in place and I'm going to be using the variable anyway, why not use it for this as well.

    Doug

    Notwithstanding what has already been stated, an alternative

    select top 1 payrolldate,

    Sunday = dateadd(dd, ((datediff(dd, '17530107', payrolldate)/7)*7)+7, '17530107')

    from payroll

    where payrollran = 'no'

    order by payrolldate desc

    but this would not be as efficient as using MAX

    Far away is close at hand in the images of elsewhere.
    Anon.

  • October 27, 2010 at 8:42 am

    #1242940

    David,

    Then can you give me an example of max being used? This query is being included in an application that's being built and if I can improve it by writing better code, I'm all for it.

    Thanks

    Doug

  • October 27, 2010 at 8:58 am

    #1242956

    Doug

    Please supply table DDL and sample data, and then we will be able to provide a tested solution.

    Thanks

    John

  • October 27, 2010 at 9:32 am

    #1242984

    doug 40899 (10/27/2010)

    David,

    Then can you give me an example of max being used? This query is being included in an application that's being built and if I can improve it by writing better code, I'm all for it.

    Thanks

    Doug

    As John pointed out without DDL and sample data I'd guess at

    select MAX(payrolldate) AS [payrolldate],

    dateadd(dd, ((datediff(dd, '17530107', MAX(payrolldate))/7)*7)+7, '17530107') AS [Sunday]

    from payroll

    where payrollran = 'no'

    Far away is close at hand in the images of elsewhere.
    Anon.

  • October 27, 2010 at 9:42 am

    #1242993

    David,

    Thanks. I'll use that one to be more precise.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply