SQL Sum Iff Query - Need Help Finishing

MCTampa · Dec 10, 2019

I have the following two tables:

Automatics with Dates

RAREA	RCODE	RRATCD	Size	StartWeek	EndWeek	Start Date	End Date	Start Year	End Year
330	VIT	A	1	34	39	8/20/2020	9/30/2020	2020	2020
330	VIT	A	1	50	50	12/10/2020	12/16/2020	2020	2020
330	VIT	A	2	33	39	8/13/2020	9/30/2020	2020	2020
330	VIT	A	2	50	50	12/10/2020	12/16/2020	2020	2020

Inventory Count

RESX	EXDTFDTF	UCDX	CountOfEXCH#
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

RAREA	RCODE	RRATCD	Size	Start Date	End Date	StartWeek	EndWeek	Units
330	VIT	A	1	8/20/2020	9/30/2020	34	39	11
330	VIT	A	1	12/10/2020	12/16/2020	50	50	11
330	VIT	A	2	8/13/2020	9/30/2020	33	39	932
330	VIT	A	2	12/10/2020	12/16/2020	50	50	932

MCTampa · Dec 11, 2019

xenou said:
I would recommend:

You provide some sample data that does illustrate the problem (doesn't have to be a lot - even just 4 or 5 rows).

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:

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?

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

RAREA	RCODE	RRATCD	Size	StartWeek	EndWeek	Start Date	End Date	Start Year	End Year
330	VIT	A	0	16	23	4/16/2020	6/10/2020	2020	2020
330	VIT	A	0	33	39	8/13/2020	9/30/2020	2020	2020
330	VIT	A	0	48	50	11/26/2020	12/16/2020	2020	2020
330	VIT	A	1	34	39	8/20/2020	9/30/2020	2020	2020
330	VIT	A	1	50	50	12/10/2020	12/16/2020	2020	2020
330	VIT	A	2	33	39	8/13/2020	9/30/2020	2020	2020
330	VIT	A	2	50	50	12/10/2020	12/16/2020	2020	2020
330	VIT	A	3	33	39	8/13/2020	9/30/2020	2020	2020

Here are my inventory counts:

Inventory Count

RESX	EXDTFDTF	UCDX	CountOfEXCH#
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

RAREA	RCODE	RRATCD	Size	Start Date	End Date	StartWeek	EndWeek	Units
330	VIT	A	1	8/20/2020	9/30/2020	34	39	11
330	VIT	A	1	12/10/2020	12/16/2020	50	50	11
330	VIT	A	2	8/13/2020	9/30/2020	33	39	932
330	VIT	A	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

RESX	EXDTFDTF	UCDX	CountOfEXCH#
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 · Dec 11, 2019

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 · Dec 11, 2019

JonXL said:

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 · Dec 11, 2019

MCTampa said:
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.

MCTampa · Dec 11, 2019

VERIFIED!

hernantorres23 · Dec 11, 2019

MCTampa said:
VERIFIED!

I'm sorry

, but I'm in Venezuela and internet was down, then the post was waiting internet connection

SQL Sum Iff Query - Need Help Finishing

MCTampa

Board Regular

MCTampa

Board Regular

Excel Facts

JonXL

Well-known Member

MCTampa

Board Regular

hernantorres23

Active Member

MCTampa

Board Regular

hernantorres23

Active Member

Similar threads

Forum statistics

Share this page

SQL Sum Iff Query - Need Help Finishing

MCTampa

Board Regular

MCTampa

Board Regular

Excel Facts

JonXL

Well-known Member

MCTampa

Board Regular

hernantorres23

Active Member

MCTampa

Board Regular

hernantorres23

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock