Query for getting value from another record in sam

问题:

I have data imported into a temporary table in MSAccess which looks like this:

to which I have added the "Gap" and "Previous/Current" columns that I need to calculate using an SQL Query. The "Gap Threshold" is User input or PARAMETER supplied to Query and for e.g. is 300. The GlobalID groups ItemID's whereas each ItemID is unique number.

What i want to do is calculate the GAP

(GAP = TEMPORARY_1![VERSION DATE] - TEMPORARY![VERSION DATE])

between ItemID's of similar GlobalID's and identify the items having GAP > GAP THRESHOLD value. Based on this GAP, for each GlobalID-grouped ItemID's, I want to determine which is the "Previous" ItemID and which is the "Current" ItemID.

i.e. determine which is Previous Item and which is Current Item, having a GAP of more than 300 days between them.

Finally, CREATE ANOTHER TABLE that will only import these Current/Previous Pairs for each GlobalID, but display them as one record each like this:

OR Is it a better design to Create 2 separate Tables AFTER CALCULATING GAP > GAP THRESHOLD, called tblPrevious & tblCurrent from the Temporary table like this?:

I need someone to point me in the right direction to have a better normalized design and achieve this using SQL query. Note: all the tables need to be generated dynamically everytime based on new data extract that is imported.

The below query gives error on Gap column and doesn't calculate Previous/Current:

PARAMETERS Threshold Long;
SELECT TEMPORARY.GlobalID, TEMPORARY.ItemID, TEMPORARY.[Version Date], IIf([TEMPORARY]![GlobalID]=
[TEMPORARY_1]![GlobalID],Max([TEMPORARY]![Version Date])-Min([TEMPORARY_1]![Version Date])=0,"Previous") AS Previous, TEMPORARY_1.ItemID, TEMPORARY_1.[Version Date], IIf([TEMPORARY]![GlobalID]=[TEMPORARY_1]![GlobalID],Max([TEMPORARY]![Version Date])-Min([TEMPORARY_1]![Version Date])>[Threshold],"Current") AS [Current], IIf(([TEMPORARY]![Version Date]-[TEMPORARY_1]![Version Date])>[Threshold],[TEMPORARY]![Version Date]-[TEMPORARY_1]![Version Date],"") AS GAP
FROM TEMPORARY, TEMPORARY AS TEMPORARY_1
GROUP BY TEMPORARY.GlobalID, TEMPORARY.ItemID, TEMPORARY.[Version Date], TEMPORARY_1.GlobalID, TEMPORARY_1.ItemID, TEMPORARY_1.[Version Date];

Any help would be most appreciated.

回答1:

Will offer one more contribution - option with VBA code to get the Current/Previous pairs. This does require saving records to table. Tested and runs in a snap.

Sub GetGap()
Dim intTH As Integer, x As Integer, strGID As String
Dim rsT1 As DAO.Recordset, rsT2 As DAO.Recordset
CurrentDb.Execute "DELETE FROM Temp"
Set rsT1 = CurrentDb.OpenRecordset("SELECT * FROM Temporary ORDER BY GlobalID, ItemID DESC;")
Set rsT2 = CurrentDb.OpenRecordset("SELECT * FROM Temp;")
strGID = rsT1!GlobalID
x = 1
While Not rsT1.EOF
    If strGID = rsT1!GlobalID Then
        If x = 1 Then
            rsT2.AddNew
            rsT2!GlobalID = strGID
            rsT2!CurItemID = rsT1!ItemID
            rsT2!CurDate = rsT1![Version Date]
            x = 2
        ElseIf x = 2 Then
            rsT2!GlobalID = strGID
            rsT2!PreItemID = rsT1!ItemID
            rsT2!PreDate = rsT1![Version Date]
            x = 3
        End If
        If Not rsT1.EOF Then rsT1.MoveNext
    Else
        If x = 3 Then rsT2.Update
        strGID = rsT1!GlobalID
        x = 1
    End If
    If rsT1.EOF Then rsT2.Update
Wend
End Sub

Then a query can easily calculate the Gap and filter records.

SELECT Temp.GlobalID, Temp.CurItemID, Temp.CurDate, Temp.PreDate, Temp.PreItemID, [CurDate]-[PreDate] AS Gap FROM Temp WHERE ((([CurDate]-[PreDate])>Int([Enter Threshold])));

Or the code can be expanded to also calc the Gap and save only records that meet the threshold requirement, just a bit more complicated.

回答2:

Review Allen Browne Subquery.

Requirements described in narrative differ from the title. Here are suggestions for both.

Queries pulling Current/Previous pairs.

Query 1:

SELECT [GlobalID], [ItemID] AS CurItemID, [Version Date] AS CurDate, (SELECT TOP 1 [Version Date] FROM Temporary AS Dupe WHERE Dupe.GlobalID=Temporary.GlobalID AND Dupe.ItemID < Temporary.ItemID ORDER BY Dupe.GlobalID, Dupe.ItemID DESC) AS PreDate, (SELECT TOP 1 [ItemID] FROM Temporary AS Dupe WHERE Dupe.GlobalID=Temporary.GlobalID AND Dupe.ItemID < Temporary.ItemID ORDER BY Dupe.GlobalID, Dupe.ItemID DESC) AS PreItemID FROM [Temporary];

Query 2:

SELECT Query1.GlobalID, Query1.CurItemID, Query1.CurDate, Query1.PreDate, Query1.PreItemID, DateDiff("d",[PreDate],[CurDate]) AS Gap FROM Query1 WHERE ((([GlobalID] & [CurItemID]) In (SELECT TOP 1 GlobalID & CurItemID FROM Query1 AS Dupe WHERE Dupe.GlobalID = Query1.GlobalID ORDER BY GlobalID, CurItemID DESC))) AND DateDiff("d",[PreDate],[CurDate]) > Int([Enter Threshold]);

Final output:

GlobalID CurItemID CurDate PreDate PreItemID Gap
00109086 2755630 2/26/2015 3/11/2014 2130881 352
00114899 2785590 3/13/2015 3/25/2014 2093191 353
00154635 2755623 2/26/2015 4/4/2014 2176453 328

Here is query that addresses the requirement for Minimum/Maximum as stated in your title. Not as slow as the Current/Previous queries but if dataset gets significantly larger I expect it will get very slow.

SELECT Maximum.GlobalID, Maximum.ItemID AS MaxItem, Maximum.[Version Date] AS MaxItemDate, Minimum.ItemID AS MinItem, Minimum.[Version Date] AS MinItemDate, Maximum.[Version Date]-Minimum.[Version Date] AS Gap
FROM 
(SELECT T1.GlobalID, T1.ItemID, T1.[Version Date] FROM [Temporary] AS T1 WHERE (((T1.ItemID) In (SELECT Min([ItemID]) AS MinItem FROM Temporary GROUP BY GlobalID)))) AS Minimum 
INNER JOIN 
(SELECT T1.GlobalID, T1.ItemID, T1.[Version Date] FROM [Temporary] AS T1 WHERE (((T1.ItemID) In (SELECT Max([ItemID]) AS MaxItem FROM Temporary GROUP BY GlobalID)))) AS Maximum 
ON Minimum.GlobalID = Maximum.GlobalID
WHERE Maximum.[Version Date]-Minimum.[Version Date]>Int([Enter Threshold]);

Also, your dates are in international format. If you encounter issues with that, review Allen Browne International Dates