添加日期范围到SQL查询

问题描述：

我有以下查询返回的采购订单对供应商特定的公司（我们有多个公司在我们的小组）的总价值。添加日期范围到SQL查询

我想添加一个日期范围使用poheader.orderdate，但因为我没有写这个查询，它比我用的稍微复杂，我不知道在哪里添加此子句。

任何意见将不胜感激。

SELECT vendor.Vendorid AS 'ID', 
     vendor.name AS 'Name', 
     Total 
FROM vendor LEFT JOIN 
     (
      select sum (podetail.orderqty * podetail.unitcost) AS Total, 
        podetail.VendorNum, 
        podetail.Company 
      from podetail 
      where exists (
           select 1 
           from poHeader 
           where poHeader.OrderNum = podetail.OrderNum 
           and  poHeader.ApprovalStatus = 'A' 
          ) 
      and  exists (
           select 1 
           from vendor 
           WHERE vendor.company = 'MyCompany' 
           and  vendor.VendorNum = podetail.VendorNum 
           and  vendor.Company = podetail.Company 
          ) 
      group by podetail.VendorNum, 
         podetail.Company 
     ) OD ON vendor.Vendornum = OD.Vendornum 
       and vendor.Company = OD.Company 
WHERE vendor.company = 'MyCompany' 
ORDER BY vendor.name

答

好吧，一旦它被格式化，它就会看到所有可怕的东西，现在呢？

现在你可以看到where子句的位置了吗？

喜欢的东西

SELECT vendor.Vendorid AS 'ID', 
     vendor.name AS 'Name', 
     Total 
FROM vendor LEFT JOIN 
     (
      select sum (podetail.orderqty * podetail.unitcost) AS Total, 
        podetail.VendorNum, 
        podetail.Company 
      from podetail 
      where exists (
           select 1 
           from poHeader 
           where poHeader.OrderNum = podetail.OrderNum 
           and  poHeader.ApprovalStatus = 'A' 
           /*-------PLACE THE Date Range Check HERE------*/ 
           AND  poheader.orderdate BETWEEN @StartDate AND @EndDate 
          ) 
      and  exists (
           select 1 
           from vendor 
           WHERE vendor.company = 'MyCompany' 
           and  vendor.VendorNum = podetail.VendorNum 
           and  vendor.Company = podetail.Company 
          ) 
      group by podetail.VendorNum, 
         podetail.Company 
     ) OD ON vendor.Vendornum = OD.Vendornum 
       and vendor.Company = OD.Company 
WHERE vendor.company = 'MyCompany' 
ORDER BY vendor.name

这当然可以让我的头更容易，谢谢！ – 2010-11-25 10:22:59

我试着添加'和poheader.orderdate BETWEEN'2010-11-24'和`2010-11-25“，但查询返回的是与添加日期范围之前一样的”total“。任何想法，为什么这可能会发生？ – 2010-11-25 10:24:30

答

我做一个假设，因为你的意思是什么“添加日期范围”。我假设你想筛选出落在特定范围内的结果：

SELECT vendor.Vendorid AS 'ID', vendor.name AS 'Name', Total 
FROM vendor 
LEFT JOIN (select sum (podetail.orderqty * podetail.unitcost) AS Total, podetail.VendorNum, podetail.Company 
    from podetail 
    where exists 
    (select 1 
    from poHeader 
    where poHeader.OrderNum = podetail.OrderNum and poHeader.ApprovalStatus = 'A') 
/*this line*/ and convert(varchar(8),poHeader.orderdate,112) between '20100101' and '201' 
    and exists 
    (select 1 from vendor 
    WHERE vendor.company = 'MyCompany' 
    and vendor.VendorNum = podetail.VendorNum 
    and vendor.Company = podetail.Company) 
    group by podetail.VendorNum, podetail.Company) OD 
    ON vendor.Vendornum = OD.Vendornum 
    and vendor.Company = OD.Company 
WHERE vendor.company = 'MyCompany' 
ORDER BY vendor.name

我已经重新格式化为便于阅读。

我也转换为一个varchar（8），而指定范围的参数可以是一个日期时间：

and poHeader.orderdate between getdate()-10 and getdate()

答

它看起来像第一存在条款应作如下修改：

(select 1 from poHeader where poHeader.OrderNum = podetail.OrderNum and poHeader.ApprovalStatus = 'A' and poheader.orderdate BETWEEN 'start_date' AND 'end_date's)

添加日期范围到SQL查询

相关推荐