SQL Sum Iff Query - Need Help Finishing

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
59
I have the following two tables:

Automatics with Dates

RAREARCODERRATCDSizeStartWeekEndWeekStart DateEnd DateStart YearEnd Year
330​
VITA
1​
34​
39​
8/20/2020​
9/30/2020​
2020​
2020​
330​
VITA
1​
50​
50​
12/10/2020​
12/16/2020​
2020​
2020​
330​
VITA
2​
33​
39​
8/13/2020​
9/30/2020​
2020​
2020​
330​
VITA
2​
50​
50​
12/10/2020​
12/16/2020​
2020​
2020​
Inventory Count

RESXEXDTFDTFUCDXCountOfEXCH#
VIT
8/28/2020​
2​
71​
VIT
8/29/2020​
2​
60​
VIT
8/30/2020​
2​
9​
VIT
9/4/2020​
2​
63​
VIT
9/5/2020​
2​
63​
VIT
9/6/2020​
2​
8​
VIT
9/11/2020​
2​
49​
VIT
9/12/2020​
2​
64​
VIT
9/13/2020​
2​
9​
VIT
9/18/2020​
2​
49​
VIT
9/19/2020​
2​
72​
VIT
8/14/2020​
2​
71​
VIT
8/15/2020​
2​
57​
VIT
8/16/2020​
2​
7​
VIT
8/21/2020​
2​
70​
VIT
8/22/2020​
2​
59​
VIT
8/23/2020​
2​
8​
VIT
9/20/2020​
2​
9​
VIT
9/25/2020​
2​
51​
VIT
9/26/2020​
2​
73​
VIT
9/27/2020​
2​
10​
932​
And have written the following query to determine the quantity of units from table 2 which fall within the ranges outlined on table 1:

VBA Code:
SELECT

[Automatics with Dates].RAREA,
[Automatics with Dates].RCODE,
[Automatics with Dates].RRATCD,
[Automatics with Dates].Size,
[Automatics with Dates].[Start Date],
[Automatics with Dates].[End Date],
[Automatics with Dates].StartWeek,
[Automatics with Dates].EndWeek,
Sum([Inventory Count].[CountOfEXCH#]) AS [Units]

FROM [Automatics with Dates] LEFT JOIN [Inventory Count] ON ([Automatics with Dates].RCODE = [Inventory Count].RESX) AND ([Automatics with Dates].Size = [Inventory Count].UCDX)

WHERE ((([Automatics with Dates].RCODE)='VIT')
         AND ((Exists (SELECT *
         FROM [Automatics with Dates] ab
         WHERE ab.RCODE = [Inventory Count].RESX
         AND ab.Size = [Inventory Count].UCDX
         AND [Inventory Count].EXDTFDTF >= ab.[Start Date]
         AND [Inventory Count].EXDTFDTF <= ab.[End Date]
        
))<>False))


GROUP BY [Automatics with Dates].RAREA, [Automatics with Dates].RCODE, [Automatics with Dates].RRATCD, [Automatics with Dates].Size, [Automatics with Dates].[Start Date], [Automatics with Dates].[End Date], [Automatics with Dates].StartWeek, [Automatics with Dates].EndWeek;
However my result is as follows:

The issue is that I'm seeing 932 units for VIT Size 2 between both 8/13/2020 and 9/30/2020 (which is correct) AND between 12/10/2020 and 12/16/2020 (which is not).
Why is my query applying the same total to both ranges of dates?

Query1

RAREARCODERRATCDSizeStart DateEnd DateStartWeekEndWeekUnits
330​
VITA
1​
8/20/2020​
9/30/2020​
34​
39​
11​
330​
VITA
1​
12/10/2020​
12/16/2020​
50​
50​
11​
330​
VITA
2​
8/13/2020​
9/30/2020​
33​
39​
932​
330​
VITA
2​
12/10/2020​
12/16/2020​
50​
50​
932​
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
255
Office Version
365, 2016
Platform
Windows
What happens if you add the dates to your join ON statement?

As is, it'll always match the same records from the Inventory Count table.
 

hernantorres23

Active Member
Joined
Nov 21, 2019
Messages
256
Office Version
365, 2016
Platform
Windows, Web
Hi try this
Code:
SELECT [Automatics with Dates].RAREA, [Automatics with Dates].RCODE, [Automatics with Dates].RRATCD, [Automatics with Dates].Size, [Automatics with Dates].[Start Date], [Automatics with Dates].[End Date], [Automatics with Dates].StartWeek, [Automatics with Dates].EndWeek, Sum([Inventory Count].CountOfEXCH) AS Units
FROM [Inventory Count] INNER JOIN [Automatics with Dates] ON ([Inventory Count].RESX = [Automatics with Dates].RCODE) AND ([Inventory Count].UCDX = [Automatics with Dates].Size)
GROUP BY [Automatics with Dates].RAREA, [Automatics with Dates].RCODE, [Automatics with Dates].RRATCD, [Automatics with Dates].Size, [Automatics with Dates].[Start Date], [Automatics with Dates].[End Date], [Automatics with Dates].StartWeek, [Automatics with Dates].EndWeek
HAVING ((([Automatics with Dates].StartWeek) Between Min(DatePart("ww",[Inventory Count]![EXDTFDTF])) And Max(DatePart("ww",[Inventory Count]![EXDTFDTF]))));
Please don't use spaces or special characters in field names and adapt table names
 

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
59
Hi try this
Code:
SELECT [Automatics with Dates].RAREA, [Automatics with Dates].RCODE, [Automatics with Dates].RRATCD, [Automatics with Dates].Size, [Automatics with Dates].[Start Date], [Automatics with Dates].[End Date], [Automatics with Dates].StartWeek, [Automatics with Dates].EndWeek, Sum([Inventory Count].CountOfEXCH) AS Units
FROM [Inventory Count] INNER JOIN [Automatics with Dates] ON ([Inventory Count].RESX = [Automatics with Dates].RCODE) AND ([Inventory Count].UCDX = [Automatics with Dates].Size)
GROUP BY [Automatics with Dates].RAREA, [Automatics with Dates].RCODE, [Automatics with Dates].RRATCD, [Automatics with Dates].Size, [Automatics with Dates].[Start Date], [Automatics with Dates].[End Date], [Automatics with Dates].StartWeek, [Automatics with Dates].EndWeek
HAVING ((([Automatics with Dates].StartWeek) Between Min(DatePart("ww",[Inventory Count]![EXDTFDTF])) And Max(DatePart("ww",[Inventory Count]![EXDTFDTF]))));
Please don't use spaces or special characters in field names and adapt table names
This gives me a similar issue in that (8/13-9/30 and 12/10-12/16) give me the same values. Additionally, my range for 8/20-9/30 for size 1 is now wrong:

Query1

RAREARCODERRATCDSizeStart DateEnd DateStartWeekEndWeekUnits
330​
VITA
1​
8/20/2020​
9/30/2020​
34​
39​
1151​
330​
VITA
2​
8/13/2020​
9/30/2020​
33​
39​
5170​
330​
VITA
2​
12/10/2020​
12/16/2020​
50​
50​
5170​
 

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
59
What happens if you add the dates to your join ON statement?

As is, it'll always match the same records from the Inventory Count table.
Right but I'm not sure what to join on...meaning I want the formula to evaluate whether or not the EXDTFDTF falls between the Start Date and End Date...so which would I join to which?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
I only get one row when I run the sample query on the sample data (both from Post 1).

Also, It's not necessary to do a LEFT JOIN with an EXISTS in the where clause. Just use an INNER JOIN. That will make the query logic must simpler to understand and by definition inner join will return matching results (that therefore must exist).
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
Nota Bene, with data using dates you should be sure that your data is stored as real dates. Otherwise, your date greater than and less than comparisons could be doing text comparison rather than dateserial (numeric or true date) comparison.
 

hernantorres23

Active Member
Joined
Nov 21, 2019
Messages
256
Office Version
365, 2016
Platform
Windows, Web
I only get one row when I run the sample query on the sample data (both from Post 1).

Also, It's not necessary to do a LEFT JOIN with an EXISTS in the where clause. Just use an INNER JOIN. That will make the query logic must simpler to understand and by definition inner join will return matching results (that therefore must exist).
Same result, in my case, only one row. Using data example from post 1
 

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
59
My apologies, everyone is correct. Using the data above you will get one row, but my data is significantly larger. I only posted a small section of it.
With the larger dataset I am still getting the same issue.

I am more than willing to share my database with anyone who wants to take a look with me.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
I would recommend:
  1. You provide some sample data that does illustrate the problem (doesn't have to be a lot - even just 4 or 5 rows).
  2. You remove the EXISTS and IIF() from the query and rewrite the query in the normal way.
As a rule for resolving errors like this you can work in one of two directions:

  1. Pull all of the records related to the problem group and inspect them in a detail view (are there only 932 and they are really being counted twice, or are there in fact 1864 and they do go into two groups?
  2. Test your query with less fields in the grouping and add fields one by one until the counts go wonky. For instance, start with just size 2 between 12/16 and 12/10.
SQL is a set-oriented language so all you really need to do is gain an understanding of what's in your set.
 

Forum statistics

Threads
1,081,574
Messages
5,359,704
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top