Excel / VBA / ADO - SQL查询未返回正确的结果

I am trying to pull Folio IDs that have departure dates of greater than 30, 60, and 90 days (all from the same file). If one of the IDs also has an occurrence with a date that is less than 30 days, I don’t want that ID pulled. I also want the date pulled for Folio IDs with a blank (null?) departure date only if that ID also has a departure date greater than 30, 60, or 90 days.

我试图拉出离开日期超过30天，60天和90天的Folio ID（全部来自同一个文件）。如果其中一个ID的出现日期少于30天，我不希望该ID被拉出。我还希望将Folio ID的日期拉为空白（空？）出发日期，前提是该ID的出发日期大于30,60或90天。

I have tried numerous combinations of left, inner, right, and nested joins (thanks to answers provided to a previous question). I have come close, but for some reason my query is not pulling any of the blank departure dates, even though I know they exist. My query so far is as follows:

我已经尝试了左，内，右和嵌套连接的多种组合（感谢前一个问题提供的答案）。我已经接近了，但由于某种原因，我的查询并没有拉出任何空白的出发日期，即使我知道它们存在。我的查询到目前为止如下：

 sSQL = "SELECT t1.[Folio Type], t1.[Folio ID],t1.[Departure Date],t1.[Folio Total]"
    sSQL = sSQL & " FROM"
    sSQL = sSQL & "  (SELECT [Folio Type], [Folio ID],[Folio Total],[Departure Date]"
    sSQL = sSQL & "   FROM [Individual Folios$B2:O150000] GROUP BY [Folio Type],[Folio ID],[Folio Total],[Departure Date]"
    sSQL = sSQL & " HAVING [Folio Total] <> 0 and [Departure Date] < " & G30E & ") As t1 "
    sSQL = sSQL & " LEFT JOIN"
    sSQL = sSQL & "  (SELECT [Folio Type],[Folio ID],[Departure Date],[Folio Total]"
    sSQL = sSQL & "   FROM [Individual Folios$B2:O150000]"
    sSQL = sSQL & "   group by [Folio Type],[Folio ID],[Departure Date],[Folio Total]"
    sSQL = sSQL & "   HAVING [Folio Total] <> 0 and ([Departure Date] > " & G30E & " or [Departure Date] is null)) as t2"
    sSQL = sSQL & "   on t1.[Folio ID] = t2.[Folio ID]"
    sSQL = sSQL & "   where t2.[Folio ID] is null"

I also have a similar query (that tries to accomplish the same thing as the first query) using nested joins. I thought the two queries should pull the same values, but the totals are different:

我也有一个类似的查询（试图完成与第一个查询相同的事情）使用嵌套连接。我认为这两个查询应该提取相同的值，但总数是不同的：

 sSQL = "SELECT t1.[Folio Type], t1.[Folio ID],t1.[Departure Date],t1.[Folio Total]"
sSQL = sSQL & " FROM"
sSQL = sSQL & "  ((SELECT [Folio Type], [Folio ID],[Folio Total],[Departure Date]"
sSQL = sSQL & "   FROM [Individual Folios$B2:O150000] GROUP BY [Folio Type],[Folio ID],[Folio Total],[Departure Date]"
sSQL = sSQL & " HAVING [Folio Total] <> 0 and [Departure Date] < " & G30E & ") As t1 "
sSQL = sSQL & " LEFT JOIN"
sSQL = sSQL & "  (SELECT [Folio Type],[Folio ID],[Departure Date],[Folio Total]"
sSQL = sSQL & "   FROM [Individual Folios$B2:O150000]"
sSQL = sSQL & "   group by [Folio Type],[Folio ID],[Departure Date],[Folio Total]"
sSQL = sSQL & "   HAVING [Folio Total] <> 0 and [Departure Date] > " & G30E & ") as t2"
sSQL = sSQL & "   on t1.[Folio ID] = t2.[Folio ID])"
sSQL = sSQL & " LEFT JOIN"
sSQL = sSQL & "  (SELECT [Folio Type],[Folio ID],[Departure Date],[Folio Total]"
sSQL = sSQL & "   FROM [Individual Folios$B2:O150000]"
sSQL = sSQL & "   group by [Folio Type],[Folio ID],[Departure Date],[Folio Total]"
sSQL = sSQL & "   HAVING [Folio Total] <> 0 and [Departure Date] is null) as t3"
sSQL = sSQL & "   on t1.[Folio ID] = t3.[Folio ID]"
sSQL = sSQL & "   where t2.[Folio ID] is null or t3.[Folio ID] is null"

Any help is greatly appreciated.

任何帮助是极大的赞赏。

1 个解决方案

#1

Try using a series of UNION queries. Each query has its on WHERE clause to restricte the records returned.

尝试使用一系列UNION查询。每个查询都有其WHERE子句来限制返回的记录。

Since most of the SELECT is the same I'd use a WITH clause:

由于大多数SELECT都是相同的，我使用WITH子句：

WITH A AS(SELECT * FROM [FoliosXXX])

SELECT * FROM A WHERE [Departure DATE] <= 30
UNION
SELECT * FROM A WHERE [Departure DATE] > 30 AND [Departure DATE] <= 60
UNION
SELECT * FROM A WHERE [Departure DATE] >= 60

You can make the SELECT for A to be a complex/joined/etc. query. Not sure about GROUPing need to add that to the later SELECTs.

您可以使SELECT for A成为复杂/加入/等。查询。不确定GROUPing是否需要将其添加到以后的SELECT中。

A variation is to use UNIONs and in the SELECT use a NOT IN clause to exclude records already processed.

一种变体是使用UNION并在SELECT中使用NOT IN子句来排除已经处理的记录。

#1