SQL Sum Iff Query - Need Help Finishing

MCTampa

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

JonXL

Active Member
Joined
Feb 5, 2018
Messages
257
Office Version
365, 2016
Platform
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
59
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
256
Office Version
365, 2016
Platform
Windows, 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.
 

Forum statistics

Threads
1,082,130
Messages
5,363,333
Members
400,726
Latest member
Shahzad Taimoor

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top