I have two tables:
我有两张桌子:
Application
applicationid (int)
applicationname (varchar)
isavailable (bit)应用程序applicationid(int)applicationname(varchar)是可用的(位)
and
Holidays
applicationid (int)
holidaydate (datetime)假期applicationid(int)holidaydate(datetime)
I need to get the isavailable flag for any given applicationname but it should only return if the day if is not a holiday. The isavailable flag is independent of holidays - it is only set if there are system-wide problems, not on a set schedule.
我需要获取任何给定应用程序名称的isavailable标志,但它应该只返回如果该日不是假日。 isavailable标志与假期无关 - 仅在存在系统范围问题时才设置,而不是在设定的时间表上设置。
I initially had something like:
我最初有类似的东西:
select top 1 apps.isavailable
from dbo.Applications apps, dbo.Holidays hol
where apps.applicationid = hol.applicationid and
apps.applicationname = @appname and
((datediff(dd,getdate(),hol.holidaydate)) != 0)
but that was returning records even if today was a holiday because the other holiday dates don't equal today.
但即使今天是假期,这也是回归记录,因为今天的其他假日日期并不相同。
I tried
and (CONVERT(VARCHAR,getdate(),101)) not in (CONVERT(VARCHAR,hol.holidaydate,101))
(it is on SQL Server 2005, so there is no Date type so I have to convert it)
(它在SQL Server 2005上,所以没有Date类型所以我必须转换它)
but again, it was returning records even if today was a holiday. How can I structure this query using a "not in" or "except" clause (or something else) to only return a record if today isn't a holiday?
但同样,即使今天是假期,它也会返回记录。如果今天不是假日,我如何使用“not in”或“except”子句(或其他内容)来构造此查询,以仅返回记录?
Update
I don't need a list of all applicationnames that don't have a holiday - I need a record for the specified apps.applicationname. The answers below only return the application names that don't have a holiday on today. The query should return the isavailable flag if it is not a holiday, or else return no records if it is a holiday. I don't care about the other applications.
我不需要没有假期的所有应用程序名称列表 - 我需要指定apps.applicationname的记录。以下答案仅返回今天没有假期的应用程序名称。如果查询不是假日,则查询应返回isavailable标志,否则如果是假日则不返回任何记录。我不关心其他应用程序。
Also, what if I added a table like:
另外,如果我添加一个表格如下:
HoursOfOperations
applicationid (int)
mondayopen (datetime)
mondayclose (datetime)
tuesdayopen (datetime)
tuesdayclose (datetime)
//open and close for all seven days of the weekHoursOfOperations applicationid(int)mondayopen(datetime)mondayclose(datetime)tuesdayopen(datetime)tuesdayclose(datetime)//打开和关闭一周的所有七天
Could I join on all three of these tables to only return a record if it is within the hours for the given day and is not a holiday? Do I have to do this in separate queries?
我是否可以加入所有这三个表格,只有在特定日期的小时内且不是假期时才返回记录?我是否必须在单独的查询中执行此操作?
6 个解决方案
#1
14
THe following query should get you a list of applications that DO NOT have a holiday defined for the CURRENT date.
以下查询应该为您提供一个没有为CURRENT日期定义假期的应用程序列表。
SELECT apps.ApplicationName, apps.isavailable
FROM dbo.Applications apps
WHERE apps.ApplicationName = @AppName
AND NOT EXISTS
( SELECT *
FROM Holidays
WHERE ApplicationId = apps.ApplicationId
AND CONVERT(VARCHAR,getdate(),101) = CONVERT(VARCHAR,holidaydate,101)
)
Basically what we do is select everything where it does not have a match.
基本上我们所做的就是选择没有匹配的所有东西。
#2
5
OK, just to be different, how about something like this:
好吧,只是为了与众不同,这样的事情怎么样:
select apps.isavailable
from dbo.Application apps left outer join dbo.Holidays hol
on apps.applicationid = hol.applicationid
and convert(varchar(10),getdate(),101) = convert(varchar(10),hol.holidaydate,101)
where apps.applicationname = @appname
and hol.applicationid is null
Basically, you're joining the tables based on applicationid and the current date. Since it's a left join, you'll always get all the applications that match @appname, then you just filter out any results that get a match based on the holiday date being the current date. Assuming that applicationname is unique, you'll always get a single row where the right half of the join is null, unless the current date matches a holiday, in which case the query will return no results.
基本上,您将根据applicationid和当前日期加入表。由于它是左连接,因此您将始终获得与@appname匹配的所有应用程序,然后您只需根据假日日期作为当前日期筛选出任何匹配的结果。假设applicationname是唯一的,除非当前日期与假日匹配,否则您将始终获得单行,其中右半部分为空,在这种情况下,查询将不返回任何结果。
I don't know how it stacks up with the other solutions performance-wise; I believe joins are generally supposed to be faster than sub-queries, but that probably depends on a variety of factors, so YMMV.
我不知道它如何与性能方面的其他解决方案叠加;我认为连接通常比子查询更快,但这可能取决于各种因素,所以YMMV。
#3
4
You can use "WHERE NOT EXISTS":
您可以使用“WHERE NOT EXISTS”:
SELECT *
FROM Applications a
WHERE NOT EXISTS (
SELECT *
FROM Holidays h
WHERE h.ApplicationID = a.ApplicationID
AND HolidayDate = cast(cast(getdate() as int) as datetime)
)
I'm doing the cast there to truncate the getdate() call back to just the date. Haven't tested that exact query but I think it'll do the job for you.
我正在那里进行强制转换,将getdate()调用截断为日期。没有测试过那个确切的查询,但我认为它会为你完成这项工作。
#4
2
Do something like this:
做这样的事情:
SELECT (fields) FROM Application
WHERE NOT EXISTS
(SELECT * FROM Holidays
WHERE ApplicationID = Application.ApplicationID
AND DAY(getdate()) = DAY(holidaydate)
AND MONTH(getdate()) = MONTH(holidaydate)
AND YEAR(getdate()) = YEAR(holidaydate)
)
Of course it would be a whole lot easier and faster with SQL Server 2008's "DATE" datatype, or if you could store day, month, year in "Holidays" separately.
当然,使用SQL Server 2008的“DATE”数据类型会更容易,更快,或者如果您可以分别在“假期”中存储日,月,年。
Marc
#5
2
SELECT a.isAvailable
FROM Application a
WHERE NOT EXISTS (
SELECT TOP 1 0
FROM Holidays b
WHERE a.applicationid = b.applicationid
AND holidaydate = $today
)
#6
0
What about this one:
这个如何:
SELECT
Application.isavailable
FROM
Holidays
RIGHT JOIN
Application
ON
Holidays.applicationid = Application.applicationid
WHERE
((Application.applicationname='app1') AND
(((Holidays.holidaydate=CurrentDate()) AND (Holidays.applicationid Is Null)) OR
(holidays.holidaydate Is Null)));
"'app1'" should be replaced by a variable specifier and "CurrentDate()" by the system specific function to return the current date.
“'app1'”应由系统特定函数的变量说明符和“CurrentDate()”替换,以返回当前日期。
Cheers
/a
#1
14
THe following query should get you a list of applications that DO NOT have a holiday defined for the CURRENT date.
以下查询应该为您提供一个没有为CURRENT日期定义假期的应用程序列表。
SELECT apps.ApplicationName, apps.isavailable
FROM dbo.Applications apps
WHERE apps.ApplicationName = @AppName
AND NOT EXISTS
( SELECT *
FROM Holidays
WHERE ApplicationId = apps.ApplicationId
AND CONVERT(VARCHAR,getdate(),101) = CONVERT(VARCHAR,holidaydate,101)
)
Basically what we do is select everything where it does not have a match.
基本上我们所做的就是选择没有匹配的所有东西。
#2
5
OK, just to be different, how about something like this:
好吧,只是为了与众不同,这样的事情怎么样:
select apps.isavailable
from dbo.Application apps left outer join dbo.Holidays hol
on apps.applicationid = hol.applicationid
and convert(varchar(10),getdate(),101) = convert(varchar(10),hol.holidaydate,101)
where apps.applicationname = @appname
and hol.applicationid is null
Basically, you're joining the tables based on applicationid and the current date. Since it's a left join, you'll always get all the applications that match @appname, then you just filter out any results that get a match based on the holiday date being the current date. Assuming that applicationname is unique, you'll always get a single row where the right half of the join is null, unless the current date matches a holiday, in which case the query will return no results.
基本上,您将根据applicationid和当前日期加入表。由于它是左连接,因此您将始终获得与@appname匹配的所有应用程序,然后您只需根据假日日期作为当前日期筛选出任何匹配的结果。假设applicationname是唯一的,除非当前日期与假日匹配,否则您将始终获得单行,其中右半部分为空,在这种情况下,查询将不返回任何结果。
I don't know how it stacks up with the other solutions performance-wise; I believe joins are generally supposed to be faster than sub-queries, but that probably depends on a variety of factors, so YMMV.
我不知道它如何与性能方面的其他解决方案叠加;我认为连接通常比子查询更快,但这可能取决于各种因素,所以YMMV。
#3
4
You can use "WHERE NOT EXISTS":
您可以使用“WHERE NOT EXISTS”:
SELECT *
FROM Applications a
WHERE NOT EXISTS (
SELECT *
FROM Holidays h
WHERE h.ApplicationID = a.ApplicationID
AND HolidayDate = cast(cast(getdate() as int) as datetime)
)
I'm doing the cast there to truncate the getdate() call back to just the date. Haven't tested that exact query but I think it'll do the job for you.
我正在那里进行强制转换,将getdate()调用截断为日期。没有测试过那个确切的查询,但我认为它会为你完成这项工作。
#4
2
Do something like this:
做这样的事情:
SELECT (fields) FROM Application
WHERE NOT EXISTS
(SELECT * FROM Holidays
WHERE ApplicationID = Application.ApplicationID
AND DAY(getdate()) = DAY(holidaydate)
AND MONTH(getdate()) = MONTH(holidaydate)
AND YEAR(getdate()) = YEAR(holidaydate)
)
Of course it would be a whole lot easier and faster with SQL Server 2008's "DATE" datatype, or if you could store day, month, year in "Holidays" separately.
当然,使用SQL Server 2008的“DATE”数据类型会更容易,更快,或者如果您可以分别在“假期”中存储日,月,年。
Marc
#5
2
SELECT a.isAvailable
FROM Application a
WHERE NOT EXISTS (
SELECT TOP 1 0
FROM Holidays b
WHERE a.applicationid = b.applicationid
AND holidaydate = $today
)
#6
0
What about this one:
这个如何:
SELECT
Application.isavailable
FROM
Holidays
RIGHT JOIN
Application
ON
Holidays.applicationid = Application.applicationid
WHERE
((Application.applicationname='app1') AND
(((Holidays.holidaydate=CurrentDate()) AND (Holidays.applicationid Is Null)) OR
(holidays.holidaydate Is Null)));
"'app1'" should be replaced by a variable specifier and "CurrentDate()" by the system specific function to return the current date.
“'app1'”应由系统特定函数的变量说明符和“CurrentDate()”替换,以返回当前日期。
Cheers
/a