Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53

    Unanswered: Problem creating a (simple) date query

    Hi,

    I'm having a problem creating what should be a simple query. We have a table with Pay Periods, and I want to insert a new pay period if the last one is out-of-date. What I've come up with doesn't work - it just keeps selecting a new date no matter what. I could use some help.

    Here's the table:
    Code:
    PayPeriodID	PayPeriodStart		PayPeriodEnd
    -----------------------------------------------------------------
    6		2011-01-17 00:00:00	2011-01-30 00:00:00
    And here's what I've come up with (which doesn't work):
    Code:
    	INSERT INTO lkpPayPeriods (PayPeriodStart, PayPeriodEnd)
    	
    	SELECT TOP 1 DATEADD(D, 1, PayPeriodEnd) AS PayPeriodStart,
    	DATEADD(D, 14, PayPeriodEnd) AS PayPeriodEnd	
    	FROM GeoTime..lkpPayPeriods
    	WHERE PayPeriodEnd >= GETDATE()
    	AND ((SELECT LkP.PayPeriodID FROM GeoTime..lkpPayPeriods LkP WHERE LkP.PayPeriodEnd = DATEADD(D, 14, GeoTime..lkpPayPeriods.PayPeriodEnd)) IS NULL)
    	ORDER BY PayPeriodID DESC
    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Hi,

    could you please elaborate the requirement a bit?

    -Baburaj
    Cheers....

    baburajv

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You could add ((...and PayPeriodEnd >= getdate())) to your subquery WHERE clause.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Also, I think you can make you date creation logic a little less complex (as long as your pay periods are always 14 days, as it appears. Instead of adding 1 day to the End Date to get the new Start date and 14 to the End Date to get the new End Date:
    Code:
            DATEADD(D,  1, PayPeriodEnd) AS PayPeriodStart,
    	DATEADD(D, 14, PayPeriodEnd) AS PayPeriodEnd
    you could just add 14 to both dates (add 14 to Start Date to get new Start Date and 14 to End Date to new End date):
    Code:
            DATEADD(D, 14, PayPeriodStart) AS PayPeriodStart,
    	DATEADD(D, 14, PayPeriodEnd  ) AS PayPeriodEnd
    While I am not sure about the rest of your logic, if PayPeriodID is an Identity (as it appears as it is not part of the Insert), and you want the next PayPeriod, this logic might work and be all you need in your Select Where clasue:
    Code:
    WHERE PayPeriodID = (SELECT MAX(PayPeriodID) FROM table-name)
       AND GETDATE() > PayPeriodEnd

  5. #5
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    Thanks, Stealth - I think that did it!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •