CODEWITHSUNDEEP
sql-server
Jeremy
Jeremy

Reputation:

SQL Server compound query

Hello and thanks for looking.

I wrote a simple application for my softball team and I am having some issues with the query I need to rewrite.

SELECT DISTINCT 
    _StartDate, _RescheduleDate, _GameTime, _FieldNumber, _Notes
FROM
    _Schedule
WHERE
    (_StartDate >= DATEADD(wk, 0, { fn NOW() })) 
    AND (_StartDate < DATEADD(wk, 1, { fn NOW() }))

This query simply gets the upcoming weeks game schedule. What I need to do and I had forgotten, was to check also for _RescheduleDates we currently have 1 or 2 games that are rescheduled. So somehow I need to modify this to check if reschedule date has a valid date and not "TBD" or NULL.

The second problem is, our games are on Friday, on the day of the game, this changes the data on the website to next weeks game and I need it to not change until the day after the game which is Saturdays. I tried adding 1 day

(wk, 1 +1d {fn NOW() }))

But obviously this did not work. So I would surely appreciate some help with that.

Upvotes: 0

Views: 2194

Answers (4)

SQLFox
SQLFox

Reputation: 289

select
    _StartDate, _RescheduleDate, _GameTime, _FieldNumber, _Notes
from (
    select distinct
        _NewDate = coalesce(nullif(_RescheduleDate,'TBD'),_StartDate)
        ,_StartDate
        ,_RescheduleDate
        ,_GameTime
        ,_FieldNumber
        ,_Notes
        from _Schedule
    ) sch
where (_NewDate >= cast(getdate() as date))
    and (_NewDate < dateadd(wk, 1, getdate()))

Your "games fall off a day early" issue is because you are comparing the current time to a date--so the time component is assumed to be 00:00:00.000. This is why you'll see advice against storing the start date and start time in separate columns. Note that cast(getdate() as date) requires SQL 2008 or higher; use cast(floor(cast(getdate() as float)) as datetime) for earlier versions.

http://sqlfiddle.com/#!3/9d171/5/0

Upvotes: 1

Autonomic
Autonomic

Reputation: 170

I kind of wanted it included in the current statement. I know it is possible I just am not sure about how to go about it. The schedule page needs to show all games to be played which includes regular scheduled games and also rescheduled games.

Nathan I think you are onto something.. select results like 'data format' for rescheduled games. Do you know how to create a compound select statement? I tried to find an example online and haven't found one applicable yet.

Upvotes: 0

blitzcrag
blitzcrag

Reputation: 21

What needs to happen when you are "checking" the _RescheduleDate value?

If you want another query for only the rescheduled games so you can have this on a separate page or section of the website, just modify the WHERE clause

SELECT DISTINCT 
  _StartDate, _RescheduleDate, _GameTime, _FieldNumber, _Notes
FROM
  _Schedule
WHERE
  (_StartDate >= DATEADD(wk, 0, { fn NOW() })) 
  AND (_StartDate < DATEADD(wk, 1, { fn NOW() }))
  AND _RescheduleDate IS NOT NULL
  AND _RescheduleDate <> 'TBD'

Otherwise the CASE statement will be able to 'check' for the value upon selecting the values. 

SELECT DISTINCT 
    _StartDate
, CASE 
     WHEN _RescheduleDate IS NULL THEN ''
     WHEN _RescheduleDate = 'TBD' THEN '' 
     ELSE --do something with the valid datetime 
  END AS _RescheduleDate
, _GameTime, _FieldNumber, _Notes

Upvotes: 0

nathan_jr
nathan_jr

Reputation: 9282

Can you please show us the desired result from a setup as such:

declare @_Schedule table (_StartDate datetime, _RescheduleDate datetime, _GameTime datetime, _FieldNumber int, _Notes varchar(10))
insert into @_Schedule
    select '2013-05-31', null,          '10:30:00', 1, '...' union all
    select '2013-06-02', '2013-06-03',  '10:30:00', 1, '...' union all
    select '2013-06-04', null,          '10:30:00', 1, '...' union all
    select '2013-06-05', null,          '10:30:00', 1, '...'


SELECT DISTINCT 
    _StartDate, _RescheduleDate, _GameTime, _FieldNumber, _Notes
FROM
    @_Schedule
WHERE
    (_StartDate >= DATEADD(wk, 0, getdate())) 
    AND (_StartDate < DATEADD(wk, 1, getdate()))

Upvotes: 0

Related Questions