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​
 

MCTampa

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


1:

I want to know how many units fall between the Start Date and End Date at VIT.

Here are my ranges and sizes:

Automatics with Dates

RAREARCODERRATCDSizeStartWeekEndWeekStart DateEnd DateStart YearEnd Year
330​
VITA
0​
16​
23​
4/16/2020​
6/10/2020​
2020​
2020​
330​
VITA
0​
33​
39​
8/13/2020​
9/30/2020​
2020​
2020​
330​
VITA
0​
48​
50​
11/26/2020​
12/16/2020​
2020​
2020​
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​
330​
VITA
3​
33​
39​
8/13/2020​
9/30/2020​
2020​
2020​
Here are my inventory counts:


Inventory Count

RESXEXDTFDTFUCDXCountOfEXCH#
VIT
8/14/2020​
2​
71​
VIT
8/15/2020​
2​
57​
VIT
8/15/2020​
1​
5​
VIT
8/16/2020​
2​
7​
VIT
8/21/2020​
2​
70​
VIT
8/22/2020​
2​
59​
VIT
8/22/2020​
1​
5​
VIT
8/23/2020​
2​
8​
VIT
8/28/2020​
2​
71​
VIT
8/29/2020​
2​
60​
VIT
8/29/2020​
1​
6​
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
9/20/2020​
2​
9​
VIT
9/25/2020​
2​
51​
VIT
9/26/2020​
2​
73​
VIT
9/27/2020​
2​
10​
VIT
10/2/2020​
2​
31​
VIT
10/3/2020​
2​
41​
VIT
10/4/2020​
2​
10​
VIT
10/9/2020​
2​
29​
VIT
10/10/2020​
2​
18​
VIT
10/11/2020​
2​
7​
VIT
10/16/2020​
2​
16​
VIT
10/17/2020​
2​
22​
VIT
10/24/2020​
2​
7​
VIT
10/30/2020​
2​
13​
VIT
10/31/2020​
2​
18​
VIT
11/6/2020​
2​
10​
VIT
11/7/2020​
2​
13​
VIT
11/13/2020​
2​
12​
VIT
11/14/2020​
2​
15​
VIT
11/28/2020​
2​
2​
VIT
12/5/2020​
2​
3​
1215​
My result is telling me that there are 932 Size 2 units between 8/13 and 9/30, which is correct.
Additionally, it's not proposing anything for the 4/16 to 6/20 range, which is also correct.

However my data does not have ANY dates between 12/10 and 12/16. My data does not have any dates past 12/5, so it should be returning 0.

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​
Here are the 932 units reflective of Size 2 from 8/13 to 9/30:


Inventory Count

RESXEXDTFDTFUCDXCountOfEXCH#
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
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
9/20/2020​
2​
9​
VIT
9/25/2020​
2​
51​
VIT
9/26/2020​
2​
73​
VIT
9/27/2020​
2​
10​
932​
2. I'm not good enough at SQL to re-write the query the "normal' way. I got this far cobbling together a structure from a different query.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
455
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Can you test and see if this works?

SQL:
SELECT
    awd.RAREA,
    awd.RCODE,
    awd.RRATCD,
    awd.Size,
    awd.[Start Date],
    awd.[End Date],
    awd.StartWeek,
    awd.EndWeek,
    Sum(ic.[CountOfEXCH#]) AS [Units]
FROM
    [Automatic with Dates] awd
LEFT JOIN
    [Inventory Count] ic
ON
    (awd.RCODE = ic.RESX)
        AND
    (awd.Size = ic.UCDX)
        AND
    (ic.EXDTFDTF >= awd.[Start Date])
        AND
    (ic.EXDTFDTF <= awd.[End Date])
GROUP BY
    awd.RAREA,
    awd.RCODE,
    awd.RRATCD,
    awd.Size,
    awd.[Start Date],
    awd.[End Date],
    awd.StartWeek,
    awd.EndWeek;
 

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
70
Can you test and see if this works?

SQL:
SELECT
    awd.RAREA,
    awd.RCODE,
    awd.RRATCD,
    awd.Size,
    awd.[Start Date],
    awd.[End Date],
    awd.StartWeek,
    awd.EndWeek,
    Sum(ic.[CountOfEXCH#]) AS [Units]
FROM
    [Automatic with Dates] awd
LEFT JOIN
    [Inventory Count] ic
ON
    (awd.RCODE = ic.RESX)
        AND
    (awd.Size = ic.UCDX)
        AND
    (ic.EXDTFDTF >= awd.[Start Date])
        AND
    (ic.EXDTFDTF <= awd.[End Date])
GROUP BY
    awd.RAREA,
    awd.RCODE,
    awd.RRATCD,
    awd.Size,
    awd.[Start Date],
    awd.[End Date],
    awd.StartWeek,
    awd.EndWeek;

I think that works! Verifying a few different scenarios now....
 

hernantorres23

Active Member
Joined
Nov 21, 2019
Messages
269
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Web
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.
In that case you must check your data and your query. I would help, If you want, only share more data.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,258
Messages
5,635,114
Members
416,842
Latest member
Ateen4ever

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