SQL Sum Iff Query - Need Help Finishing

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
70
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​
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
459
Office Version
  1. 365
  2. 2016
Platform
  1. 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
269
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. 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
70
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
70

ADVERTISEMENT

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
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. 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
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
269
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. 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
70
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
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,142
Messages
5,640,371
Members
417,139
Latest member
bdmprasenjit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top