时间戳

首页 > 解决方案 > 用于跳过周末的条件 SQL 日期参数

sql-server - 用于跳过周末的条件 SQL 日期参数

问题描述

SQL 新手，对此我完全迷失了。尝试编写查询以提取前一天开设的新帐户，这在周二至周五可以轻松完成。但是在星期一它是星期天，我尝试的一切都出错了。

这是我最接近的，但在最后一行仍然是错误的。我想我正在尝试应用 excel vba 类型逻辑，它只是不喜欢我。有人可以看看吗？

谢谢！

SELECT
    PortfolioCode as "Account", 
    f.reportheading1 as "Name", 
    d.portfoliostatus as "Status", 
    e.investmentstrategy as "Strategy",
    e.brokersource as "Broker", 
    e.referringfirm as "RefBy", 
    e.custodian as "Bank",
    e.manager as "Manager", 
    e.administrator as "APM", 
    d.initialvalue as "Open Value", 
    b.StartDate as "Start Date", 
    e.percentcash as "Cash%", 
    e.targetmvequity as "Eqt Trgt", 
    e.targetmvfixed as "FI Trgt", 
    e.munistrategy as "Muni Strat", 
    e.nationalmuni as "Nat. Muni", 
    e.statemunistrategy as "State Strat.", 
    e.residency as "Residency"
FROM
    AdvPortfolioBaseExt as A
INNER JOIN
    AdvPortfoliobase AS B ON A.PortfolioBaseID = b.PortfolioBaseID
INNER JOIN
    advportfolio AS C ON A.PortfolioBaseID = c.PortfolioID
INNER JOIN
    advapp.vportfolio AS D ON A.PortfolioBaseID = D.PortfolioID
INNER JOIN
    advportfoliobaseext AS E ON A.portfoliobaseid = e.portfoliobaseID
INNER JOIN
    advapp.vportfoliobase AS F ON a.PortfolioBaseID = f.PortfolioBaseID
WHERE 
    c.portfoliostatus <> 'Closed'
    AND IIF(DATEPART(dw, GETDATE()) = 2, b.startdate > GETDATE() - 3, b.startdate > GETDATE()-1)

标签： sql-servertsqldateconditional

解决方案

这可能是矫枉过正，但您可能希望参数化查询然后传递日期值。否则我只会做一个简单的 CTE 并设置一个日期时间变量，该变量将用于 where 条件。下面的 SQL 是针对 2005 的（较新的版本有不同的功能）。我不确定您使用的是哪个版本，所以我选择了最低公分母。无论如何，这应该可以工作，如果有索引字段，它们不会受到您正在尝试的 WHERE 子句中的计算条件的影响。

DECLARE @Date DATETIME;

;WITH WeekDayOffset AS (
    SELECT 1 as WkDay, -2 as OffSet 
    UNION ALL 
    SELECT 2 as WkDay, 0 as OffSet 
    UNION ALL 
    SELECT 3 as WkDay, 0 as OffSet 
    UNION ALL 
    SELECT 4 as WkDay, 0 as OffSet 
    UNION ALL 
    SELECT 5 as WkDay, 0 as OffSet 
    UNION ALL 
    SELECT 6 as WkDay, 0 as OffSet 
    UNION ALL 
    SELECT 7 as WkDay, -1 as Offset
)

SELECT  @Date = CAST(CONVERT(VARCHAR(10), DATEADD(DAY, Offset, GETDATE()),101) AS datetime)
FROM WeekDayOffset
WHERE WkDay = DATEPART(dw, GETDATE())


SELECT
    PortfolioCode as "Account", 
    f.reportheading1 as "Name", 
    d.portfoliostatus as "Status", 
    e.investmentstrategy as "Strategy",
    e.brokersource as "Broker", 
    e.referringfirm as "RefBy", 
    e.custodian as "Bank",
    e.manager as "Manager", 
    e.administrator as "APM", 
    d.initialvalue as "Open Value", 
    b.StartDate as "Start Date", 
    e.percentcash as "Cash%", 
    e.targetmvequity as "Eqt Trgt", 
    e.targetmvfixed as "FI Trgt", 
    e.munistrategy as "Muni Strat", 
    e.nationalmuni as "Nat. Muni", 
    e.statemunistrategy as "State Strat.", 
    e.residency as "Residency"
FROM
    AdvPortfolioBaseExt as A
INNER JOIN
    AdvPortfoliobase AS B ON A.PortfolioBaseID = b.PortfolioBaseID
INNER JOIN
    advportfolio AS C ON A.PortfolioBaseID = c.PortfolioID
INNER JOIN
    advapp.vportfolio AS D ON A.PortfolioBaseID = D.PortfolioID
INNER JOIN
    advportfoliobaseext AS E ON A.portfoliobaseid = e.portfoliobaseID
INNER JOIN
    advapp.vportfoliobase AS F ON a.PortfolioBaseID = f.PortfolioBaseID
WHERE 
    c.portfoliostatus <> 'Closed'
    AND b.startdate > @Date

推荐阅读