Get answers from your peers along with millions of IT pros who visit Spiceworks.
Join Now

Hello friends,

I have one question. It may be a little bit hard to explain but I will try my best. Let me know if my explanation is confusing.

Right now I have a query that has a chain name and lots of repetitions of the same information. I am 99,9% sure that the reason for this is because I am taking Inventory On Hand and On order information from a different table that is not based on a chain level but based on a store level.


After doing some research, I figured out that I just need to sum two columns: sum(Inventory On Hand) and sum(On Order) and it should sum the result based on Chain level and not based on store level.

Here is my code

DECLARE @CurrentDateTime DATETIME = GETDATE();
DECLARE @CurrentDate DATE = @CurrentDateTime;

DECLARE @LastWeekDt DATE = DATEADD(WW, -1, DATEADD(DD, 7 - DATEPART(dw, @CurrentDate), @CurrentDate));
DECLARE @LastWeekDATE VARCHAR(8) = convert(varchar(8),cast (@LASTWEEKDT as date),112);

DECLARE @LastYearLWDt DATE = DATEADD(WW, -52, DATEADD(DD, 7 - DATEPART(dw, @LastWeekDt), @LastWeekDt));
DECLARE @LastYearLWDATE VARCHAR(8) = convert(varchar(8),cast (@LastYearLWDt as date),112);




SET ARITHABORT OFF
SET ANSI_WARNINGS OFF



SELECT F.LOC AS 'Chain', F.DMDUNIT AS 'Item', D.DESCR AS 'Item Attributes. Description', D.U_PRODUCT_CATEGORY AS 'Item Attributes. Product category',
D.U_MSRP AS 'Item Attributes.MSRP', D.U_FORMAT AS 'Item Attributes. Format', D.U_ONSALE_DATE AS 'Item Attributes. On sale date',

s.OH AS 'Inventory On Hand', R.QTY AS 'On Order', F.TOTFCST AS 'Forecast Demand (POS)', F.TOTHIST AS 'Last Week Actual',

FORMAT(f.TOTHIST / (f.TOTFCST - f.TOTHIST) - 1, 'P0') AS 'Forecast Accuracy',
CAST(f.TOTFCST * 100.00 / s.OH AS decimal(5, 2)) AS 'Instock %', s.OH + R.QTY AS 'Projected Available Inventory'




FROM SCPOMGR.FCSTPERFSTATIC AS F
JOIN SCPOMGR.DMDUNIT D
ON F.DMDUNIT=D.DMDUNIT
JOIN SCPOMGR.LOC L
ON F.LOC=L.U_CHAINNAME
JOIN SCPOMGR.SKU S
ON S.LOC=L.LOC
JOIN SCPOMGR.RECSHIP R
ON R.DEST=L.LOC
WHERE STARTDATE BETWEEN @LastYearLWDATE and @LastWeekDATE
AND S.LOC LIKE 'ST%' AND l.LOC LIKE 'ST%' AND R.DEST LIKE 'ST%';

When I try to sum and type it with a sum operator I get this issue


Does someone know how can I sum those two columns and not get any errors so that Inventory on hand and On Order is not based on store level but based on chain level.

Please let me know if you know. Thank you!!



16 Replies

· · ·
AdmiralKirk
Tabasco
OP
AdmiralKirk This person is a Verified Professional
This person is a verified professional.
Verify your account to enable IT peers to see that you are a professional.

What you're seeing is normal for when you're doing aggregate functions (sum, average, max, min, etc.).  The tricky part of aggregates is that *each and every * field you call *must* be aggregated in some way, but you are calling fields that are not included in the aggregation. 

Lots of big words.  In simple terms, if you want to see the total number of OnHand and the total number of OnOrder, you can only include those two fields in your query:

SQL
SELECT 
F.DMDUNIT AS 'Item', 
Sum(s.OH) AS 'Inventory On Hand', 
Sum(R.QTY) AS 'On Order'

FROM SCPOMGR.FCSTPERFSTATIC AS F
JOIN SCPOMGR.LOC L
	ON F.LOC=L.U_CHAINNAME
JOIN SCPOMGR.SKU S
	ON S.LOC=L.LOC
JOIN SCPOMGR.RECSHIP R
	ON R.DEST=L.LOC
WHERE STARTDATE BETWEEN @LastYearLWDATE and @LastWeekDATE
	AND S.LOC LIKE 'ST%' AND l.LOC LIKE 'ST%' AND R.DEST LIKE 'ST%';Group By Item

This will get you the Sum() of each of the OnHand and OnOrder.  "But what about the Item value, it's not in an aggregation like the other two values?" you might ask.  Well, the GroupBy at the end is another aggregate, making it show in your results.

"How does this help me get the final answer I need?" you ask.  This is where your SQL skills take a leap forward.  You turn the above Select into its own table, a temporary table that exists only inside this execution:

SQL
(
SELECT 
F.DMDUNIT AS 'Item', 
Sum(s.OH) AS 'InventoryOnHand', 
Sum(R.QTY) AS 'OnOrder'

FROM SCPOMGR.FCSTPERFSTATIC AS F
JOIN SCPOMGR.LOC L
	ON F.LOC=L.U_CHAINNAME
JOIN SCPOMGR.SKU S
	ON S.LOC=L.LOC
JOIN SCPOMGR.RECSHIP R
	ON R.DEST=L.LOC
WHERE STARTDATE BETWEEN @LastYearLWDATE and @LastWeekDATE
	AND S.LOC LIKE 'ST%' AND l.LOC LIKE 'ST%' AND R.DEST LIKE 'ST%'
Group by Item) as ItemSums ;

Now you have another table, just like your 'real' database tables, and it contains those items with their sums.

SQL
SELECT F.LOC AS 'Chain', 
F.DMDUNIT AS 'Item', 
D.DESCR AS 'Item Attributes. Description', 
D.U_PRODUCT_CATEGORY AS 'Item Attributes. Product category',
D.U_MSRP AS 'Item Attributes.MSRP', 
D.U_FORMAT AS 'Item Attributes. Format', 
D.U_ONSALE_DATE AS 'Item Attributes. On sale date',
ItemSums.InventoryOnHand as 'Inventory On Hand',
ItemSums.OnOrder as 'On Order',
F.TOTFCST AS 'Forecast Demand (POS)', 
F.TOTHIST AS 'Last Week Actual',
FORMAT(f.TOTHIST / (f.TOTFCST - f.TOTHIST) - 1, 'P0') AS 'Forecast Accuracy',
CAST(f.TOTFCST * 100.00 / s.OH AS decimal(5, 2)) AS 'Instock %', s.OH + R.QTY AS 'Projected Available Inventory'

FROM SCPOMGR.FCSTPERFSTATIC AS F
	JOIN SCPOMGR.DMDUNIT D
	ON F.DMDUNIT=D.DMDUNIT
JOIN SCPOMGR.LOC L
	ON F.LOC=L.U_CHAINNAME
JOIN SCPOMGR.SKU S
	ON S.LOC=L.LOC
JOIN SCPOMGR.RECSHIP R
	ON R.DEST=L.LOC
join ItemSums
	on F.DMDUNIT = ItemSums.Item
WHERE STARTDATE BETWEEN @LastYearLWDATE and @LastWeekDATE
	AND S.LOC LIKE 'ST%' AND l.LOC LIKE 'ST%' AND R.DEST LIKE 'ST%';

You can do this two ways:

1) Have the Sum select be a separate query that runs ahead of your main select

SQL
(
select .... sum()....
) as ItemSums;

SELECT F.LOC AS 'Chain', ...

2) Put the Sum select down in your Joins

SQL
JOIN SCPOMGR.DMDUNIT D
	ON F.DMDUNIT=D.DMDUNIT
JOIN SCPOMGR.LOC L
	ON F.LOC=L.U_CHAINNAME
JOIN SCPOMGR.SKU S
	ON S.LOC=L.LOC
JOIN SCPOMGR.RECSHIP R
	ON R.DEST=L.LOC
join (
SELECT 
F.DMDUNIT AS 'Item', 
Sum(s.OH) AS 'Inventory On Hand', 
Sum(R.QTY) AS 'On Order'
....
WHERE STARTDATE BETWEEN @LastYearLWDATE and @LastWeekDATE
	AND S.LOC LIKE 'ST%' AND l.LOC LIKE 'ST%' AND R.DEST LIKE 'ST%'
Group by Item) as ItemSums
	on F.DMDUNIT = ItemSums.Item
WHERE STARTDATE BETWEEN @LastYearLWDATE and @LastWeekDATE

I hope that makes sense.  There may be a tidbit I missed, perhaps one of the bigwig SQL Spiceheads will chime in and point it out. 

1
· · ·
spicehead-endil
Anaheim
OP
spicehead-endil

Thank you for a reply. I am not sure why but when I try to create a temporary table, I get an error. Do you know what is wrong?


0
· · ·
AdmiralKirk
Tabasco
OP
AdmiralKirk This person is a Verified Professional
This person is a verified professional.
Verify your account to enable IT peers to see that you are a professional.

Try taking the “as” out, just have the table name after the closing parentheses.
Most of my sql is AS/400 these days so the nuances of MS syntax always trips me up.

0
· · ·
spicehead-endil
Anaheim
OP
spicehead-endil


Still get the same problem. I use SQL Server Management Studio.

0
· · ·
AdmiralKirk
Tabasco
OP
AdmiralKirk This person is a Verified Professional
This person is a verified professional.
Verify your account to enable IT peers to see that you are a professional.

Hmmm….
Try running the sum-based query first, just standalone. Does it net the sum results you need?
If so, try my second suggestion to put this as a subquery directly inside your other query’s Join section. At that point you should be able to have the ‘as’ syntax.

0
· · ·
spicehead-endil
Anaheim
OP
spicehead-endil

This is where I am right now

DECLARE @CurrentDateTime DATETIME = GETDATE();
DECLARE @CurrentDate DATE = @CurrentDateTime;

DECLARE @LastWeekDt DATE = DATEADD(WW, -1, DATEADD(DD, 7 - DATEPART(dw, @CurrentDate), @CurrentDate));
DECLARE @LastWeekDATE VARCHAR(8) = convert(varchar(8),cast (@LASTWEEKDT as date),112);

DECLARE @LastYearLWDt DATE = DATEADD(WW, -52, DATEADD(DD, 7 - DATEPART(dw, @LastWeekDt), @LastWeekDt));
DECLARE @LastYearLWDATE VARCHAR(8) = convert(varchar(8),cast (@LastYearLWDt as date),112);




SET ARITHABORT OFF
SET ANSI_WARNINGS OFF


SELECT
F.DMDUNIT AS 'Item',
Sum(s.OH) AS 'InventoryOnHand',
Sum(R.QTY) AS 'OnOrder'

FROM SCPOMGR.FCSTPERFSTATIC AS F
JOIN SCPOMGR.LOC L
  ON F.LOC=L.U_CHAINNAME
JOIN SCPOMGR.SKU S
  ON S.LOC=L.LOC
JOIN SCPOMGR.RECSHIP R
  ON R.DEST=L.LOC
WHERE STARTDATE BETWEEN @LastYearLWDATE and @LastWeekDATE
  AND S.LOC LIKE 'ST%' AND l.LOC LIKE 'ST%' AND R.DEST LIKE 'ST%'
Group by F.DMDUNIT

This is the result that I have right now. I am not sure what do you mean by saying 'putting subquery directly inside other query's join. 



0
· · ·
spicehead-endil
Anaheim
OP
spicehead-endil

I am not an expert, and trying to understand if I am summing correctly. Do you think it is correct?

Text
DECLARE @CurrentDateTime DATETIME = GETDATE();
DECLARE @CurrentDate DATE = @CurrentDateTime;

DECLARE @LastWeekDt DATE = DATEADD(WW, -1, DATEADD(DD, 7 - DATEPART(dw, @CurrentDate), @CurrentDate));
DECLARE @LastWeekDATE VARCHAR(8) = convert(varchar(8),cast (@LASTWEEKDT as date),112);

DECLARE @LastYearLWDt DATE = DATEADD(WW, -52, DATEADD(DD, 7 - DATEPART(dw, @LastWeekDt), @LastWeekDt));
DECLARE @LastYearLWDATE VARCHAR(8) = convert(varchar(8),cast (@LastYearLWDt as date),112);




SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF

SELECT F.LOC AS 'Chain', F.DMDUNIT AS 'Item', D.DESCR AS 'Item Attributes. Description', D.U_PRODUCT_CATEGORY AS 'Item Attributes. Product category', 
  D.U_MSRP AS 'Item Attributes.MSRP', D.U_FORMAT AS 'Item Attributes. Format', D.U_ONSALE_DATE AS 'Item Attributes. On sale date',
  
  s.[Inventory On Hand], R.[On Order], F.TOTFCST AS 'Forecast Demand (POS)', F.TOTHIST AS 'Last Week Actual', 
  
  FORMAT(f.TOTHIST / (f.TOTFCST - f.TOTHIST) - 1, 'P0') AS 'Forecast Accuracy', 
  CAST(f.TOTFCST * 100.00 / s.[Inventory On Hand] AS decimal(5, 2)) AS 'Instock %', 
  s.[Inventory On Hand] + R.[On Order] AS 'Projected Available Inventory'


  FROM SCPOMGR.FCSTPERFSTATIC AS F
  JOIN SCPOMGR.DMDUNIT D
  ON F.DMDUNIT=D.DMDUNIT
  JOIN SCPOMGR.LOC L
  ON F.LOC=L.U_CHAINNAME
  JOIN ( --<<--
      SELECT S.LOC, SUM(S.OH) AS 'Inventory On Hand'
      FROM SCPOMGR.SKU S
      WHERE S.LOC LIKE 'ST%'
      GROUP BY S.LOC ) AS S
  ON S.LOC=L.LOC
  JOIN ( --<<--
      SELECT R.DEST, SUM(R.QTY) AS 'On Order'
      FROM SCPOMGR.RECSHIP R
      WHERE R.DEST LIKE 'ST%'
      GROUP BY R.DEST ) AS R 
  ON R.DEST=L.LOC
  WHERE STARTDATE BETWEEN @LastYearLWDATE and @LastWeekDATE 
  AND l.LOC LIKE 'ST%'
0
· · ·
spicehead-endil
Anaheim
OP
spicehead-endil

After I did this code, it seems smth is still wrong. I get the same repetitions as before


0
· · ·
AdmiralKirk
Tabasco
OP
AdmiralKirk This person is a Verified Professional
This person is a verified professional.
Verify your account to enable IT peers to see that you are a professional.

spicehead-endil wrote:

I am not an expert, and trying to understand if I am summing correctly. Do you think it is correct?

Text
DECLARE @CurrentDateTime DATETIME = GETDATE();
DECLARE @CurrentDate DATE = @CurrentDateTime;

DECLARE @LastWeekDt DATE = DATEADD(WW, -1, DATEADD(DD, 7 - DATEPART(dw, @CurrentDate), @CurrentDate));
DECLARE @LastWeekDATE VARCHAR(8) = convert(varchar(8),cast (@LASTWEEKDT as date),112);

DECLARE @LastYearLWDt DATE = DATEADD(WW, -52, DATEADD(DD, 7 - DATEPART(dw, @LastWeekDt), @LastWeekDt));
DECLARE @LastYearLWDATE VARCHAR(8) = convert(varchar(8),cast (@LastYearLWDt as date),112);




SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF

SELECT F.LOC AS 'Chain', F.DMDUNIT AS 'Item', D.DESCR AS 'Item Attributes. Description', D.U_PRODUCT_CATEGORY AS 'Item Attributes. Product category', 
  D.U_MSRP AS 'Item Attributes.MSRP', D.U_FORMAT AS 'Item Attributes. Format', D.U_ONSALE_DATE AS 'Item Attributes. On sale date',
  
  s.[Inventory On Hand], R.[On Order], F.TOTFCST AS 'Forecast Demand (POS)', F.TOTHIST AS 'Last Week Actual', 
  
  FORMAT(f.TOTHIST / (f.TOTFCST - f.TOTHIST) - 1, 'P0') AS 'Forecast Accuracy', 
  CAST(f.TOTFCST * 100.00 / s.[Inventory On Hand] AS decimal(5, 2)) AS 'Instock %', 
  s.[Inventory On Hand] + R.[On Order] AS 'Projected Available Inventory'


  FROM SCPOMGR.FCSTPERFSTATIC AS F
  JOIN SCPOMGR.DMDUNIT D
  ON F.DMDUNIT=D.DMDUNIT
  JOIN SCPOMGR.LOC L
  ON F.LOC=L.U_CHAINNAME
  JOIN ( --<<--
      SELECT S.LOC, SUM(S.OH) AS 'Inventory On Hand'
      FROM SCPOMGR.SKU S
      WHERE S.LOC LIKE 'ST%'
      GROUP BY S.LOC ) AS S
  ON S.LOC=L.LOC
  JOIN ( --<<--
      SELECT R.DEST, SUM(R.QTY) AS 'On Order'
      FROM SCPOMGR.RECSHIP R
      WHERE R.DEST LIKE 'ST%'
      GROUP BY R.DEST ) AS R 
  ON R.DEST=L.LOC
  WHERE STARTDATE BETWEEN @LastYearLWDATE and @LastWeekDATE 
  AND l.LOC LIKE 'ST%'

If that last picture looks like the correct data, just duplicated, then on your outer Select add the “distinct “ tag
Select Distinct F.Loc as “Chain”…

This will eliminate all the duplicates.

0
· · ·
spicehead-endil
Anaheim
OP
spicehead-endil

Something is wrong. When I just added DISTINCT to Chain, my code keeps spinning for over 11 minutes and no result...

0
· · ·
AdmiralKirk
Tabasco
OP
AdmiralKirk This person is a Verified Professional
This person is a verified professional.
Verify your account to enable IT peers to see that you are a professional.
SQL
DECLARE @CurrentDateTime DATETIME = GETDATE();
DECLARE @CurrentDate DATE = @CurrentDateTime;

DECLARE @LastWeekDt DATE = DATEADD(WW, -1, DATEADD(DD, 7 - DATEPART(dw, @CurrentDate), @CurrentDate));
DECLARE @LastWeekDATE VARCHAR(8) = convert(varchar(8),cast (@LASTWEEKDT as date),112);

DECLARE @LastYearLWDt DATE = DATEADD(WW, -52, DATEADD(DD, 7 - DATEPART(dw, @LastWeekDt), @LastWeekDt));
DECLARE @LastYearLWDATE VARCHAR(8) = convert(varchar(8),cast (@LastYearLWDt as date),112);

SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF

SELECT Distinct
F.LOC AS 'Chain', 
F.DMDUNIT AS 'Item', 
D.DESCR AS 'Item Attributes. Description', D.U_PRODUCT_CATEGORY AS 'Item Attributes. Product category', 
D.U_MSRP AS 'Item Attributes.MSRP', 
D.U_FORMAT AS 'Item Attributes. Format', 
D.U_ONSALE_DATE AS 'Item Attributes. On sale date',
s.[Inventory On Hand], 
R.[On Order], 
F.TOTFCST AS 'Forecast Demand (POS)', 
F.TOTHIST AS 'Last Week Actual', 
FORMAT(f.TOTHIST / (f.TOTFCST - f.TOTHIST) - 1, 'P0') AS 'Forecast Accuracy', 
CAST(f.TOTFCST * 100.00 / s.[Inventory On Hand] AS decimal(5, 2)) AS 'Instock %', 
s.[Inventory On Hand] + R.[On Order] AS 'Projected Available Inventory'

FROM SCPOMGR.FCSTPERFSTATIC AS F
  JOIN SCPOMGR.DMDUNIT D
  ON F.DMDUNIT=D.DMDUNIT
JOIN SCPOMGR.LOC L
  ON F.LOC=L.U_CHAINNAME
JOIN (
      SELECT S.LOC, SUM(S.OH) AS 'Inventory On Hand'
      FROM SCPOMGR.SKU S
      WHERE S.LOC LIKE 'ST%'
      GROUP BY S.LOC ) AS S
  ON S.LOC=L.LOC
JOIN ( 
      SELECT R.DEST, SUM(R.QTY) AS 'On Order'
      FROM SCPOMGR.RECSHIP R
      WHERE R.DEST LIKE 'ST%'
      GROUP BY R.DEST ) AS R 
  ON R.DEST=L.LOC
WHERE STARTDATE BETWEEN @LastYearLWDATE and @LastWeekDATE 
  AND l.LOC LIKE 'ST%'

It's always a little awkward not being able to run the query myself, so I apologize for the extra iterations.

I've pasted your code and added the Distinct entry in bold so you can verify you've put it in the right spot.

At this point I am done for the night, and I will be offline until Monday.  There are several top notch SQL Spiceheads here and if this tweak does not do the trick hopefully one of them will pick this up tomorrow.  I'm subscribed so I'll be up on things come Monday.

1
· · ·
Alec6638
Cayenne
OP
Alec6638 This person is a Verified Professional
This person is a verified professional.
Verify your account to enable IT peers to see that you are a professional.
Microsoft SQL Server expert
118 Best Answers
274 Helpful Votes

spicehead-endil wrote:

Something is wrong. When I just added DISTINCT to Chain, my code keeps spinning for over 11 minutes and no result...

How long does each sub-query take?  How many rows in the data?

Doing JOINS on sub-queries can sometimes take awhile depending on the indexes present, and adding DISTINCT will certainly add time to the overall query.

I have sometimes improved query times by putting each sub-query into a #temptable and then adding indexes to the temp tables, and using those in the final query.

I would try letting the query run for a while to see if it completes and gives you the data you want, and then proceed from there.

0
· · ·
spicehead-endil
Anaheim
OP
spicehead-endil

Alec6638​ and AdmiralKirk thank you very much for your help. Unfortunately, I ran DISTINCT query for 1 hour 38 minutes and needed it to stop. It is not doing what I need.​

I think the only way how I can try to group everything is if I sum all those columns

4) Inventory On Hand 5) On Order 6)Forecast Demand (POS) 7) Last Week Actual 8) Forecast Accuracy 9) Instock % 10) Projected Available Inventory (in store)

This is how I have it right now.

I am trying to understand how I can add all those sums to a derived table, as of right now I have only two sums for Inventory On Hand and On Order. I need to sum the rest. If you know how I can insert it to a derived table, please let me know. I greatly appreciate your help. 


The code

Text
DECLARE @CurrentDateTime DATETIME = GETDATE();
DECLARE @CurrentDate DATE = @CurrentDateTime;

DECLARE @LastWeekDt DATE = DATEADD(WW, -1, DATEADD(DD, 7 - DATEPART(dw, @CurrentDate), @CurrentDate));
DECLARE @LastWeekDATE VARCHAR(8) = convert(varchar(8),cast (@LASTWEEKDT as date),112);

DECLARE @LastYearLWDt DATE = DATEADD(WW, -52, DATEADD(DD, 7 - DATEPART(dw, @LastWeekDt), @LastWeekDt));
DECLARE @LastYearLWDATE VARCHAR(8) = convert(varchar(8),cast (@LastYearLWDt as date),112);

SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF

SELECT 
F.LOC AS 'Chain', 
F.DMDUNIT AS 'Item', 
D.DESCR AS 'Item Attributes. Description', D.U_PRODUCT_CATEGORY AS 'Item Attributes. Product category', 
D.U_MSRP AS 'Item Attributes.MSRP', 
D.U_FORMAT AS 'Item Attributes. Format', 
D.U_ONSALE_DATE AS 'Item Attributes. On sale date',
s.[Inventory On Hand], 
R.[On Order], 
F.TOTFCST AS 'Forecast Demand (POS)', 
F.TOTHIST AS 'Last Week Actual', 
FORMAT(f.TOTHIST / (f.TOTFCST - f.TOTHIST) - 1, 'P0') AS 'Forecast Accuracy', 
CAST(f.TOTFCST * 100.00 / s.[Inventory On Hand] AS decimal(5, 2)) AS 'Instock %', 
s.[Inventory On Hand] + R.[On Order] AS 'Projected Available Inventory'

FROM SCPOMGR.FCSTPERFSTATIC AS F
  JOIN SCPOMGR.DMDUNIT D
  ON F.DMDUNIT=D.DMDUNIT
JOIN SCPOMGR.LOC L
  ON F.LOC=L.U_CHAINNAME
JOIN (
      SELECT S.LOC, SUM(S.OH) AS 'Inventory On Hand'
      FROM SCPOMGR.SKU S
      WHERE S.LOC LIKE 'ST%'
      GROUP BY S.LOC ) AS S
  ON S.LOC=L.LOC
JOIN ( 
      SELECT R.DEST, SUM(R.QTY) AS 'On Order'
      FROM SCPOMGR.RECSHIP R
      WHERE R.DEST LIKE 'ST%'
      GROUP BY R.DEST ) AS R 
  ON R.DEST=L.LOC
WHERE STARTDATE BETWEEN @LastYearLWDATE and @LastWeekDATE 
  AND l.LOC LIKE 'ST%'
0
· · ·
AdmiralKirk
Tabasco
OP
AdmiralKirk This person is a Verified Professional
This person is a verified professional.
Verify your account to enable IT peers to see that you are a professional.

I've done the 'aggregate every field whether it needs it or not' approach in the past, it works for one-time ad-hoc stuff, but it is not a good approach for a long term solution.

Does your sum subquery return duplicates, or just one for each item?  If it returns duplicates, add the Distinct to it and see if that changes the runtime of just the subquery.
There are a dozen ways to approach most anything when writing SQL queries, and some work better than others for a given scenario. 
Question for Alec6638:  what if we change the join to a right join, or inner join?  Would that reduce the number of duplicated rows returned?
0
· · ·
Alec6638
Cayenne
OP
Alec6638 This person is a Verified Professional
This person is a verified professional.
Verify your account to enable IT peers to see that you are a professional.
Microsoft SQL Server expert
118 Best Answers
274 Helpful Votes

AdmiralKirk wrote:

I've done the 'aggregate every field whether it needs it or not' approach in the past, it works for one-time ad-hoc stuff, but it is not a good approach for a long term solution.

Does your sum subquery return duplicates, or just one for each item?  If it returns duplicates, add the Distinct to it and see if that changes the runtime of just the subquery.
There are a dozen ways to approach most anything when writing SQL queries, and some work better than others for a given scenario. 
Question for Alec6638:  what if we change the join to a right join, or inner join?  Would that reduce the number of duplicated rows returned?

The DISTINCT query approach is sort of considered to be the 'lazy man's solution' to a query that has duplicate rows returned.  If queries are done properly (if possible with the data provided), then the use of DISTINCT can generally be avoided.

As to whether a right join or inner join will remove duplicates, that depends on the data.

For us to work on this problem in greater detail, we would need access to the table data itself (or portions thereof) so that we can see where the duplicates are coming from.

1
· · ·
spicehead-endil
Anaheim
OP
spicehead-endil

AdmiralKirk​ and Alec6638

I really appreciate your help. I am checking right now my sources, I have suspected that one of the data sources may be wrong. I am not sure at the very moment therefore trying to figure it out. I will return to this issue on Monday. I will keep you informed about the progress on it. 

Thank you very much for your willingness to help!!

0
Oops, something's wrong below.