There is no way we can help you with a problem like this without seeing some sample data. Your expected results would also be incredibly helpful too.
Here are some techniques for you to solve your own problem.
1. Simplify your query! A more readable query is a more easily maintained one. For example you have
CASE WHEN Items.StaticDays = 0 THEN 0 ELSE Items.StaticDays END AS StaticDays,
which has exactly the same effect as
Items.StaticDays,
You use this all over the place.
2. Be consistent. In some places you have used the full table schema+name e.g.
MAX(dbo.TrxInvH.TrxDate)
and in others you have not included the schema but included the name in square brackets when there was no need e.g.
[trxType].trxTypeCode
. Consider using shorter Table aliases (or correlation names) e.g. instead of having
dbo.ItemSubGroups.ItemSubGroupAraName,
in your SELECT it could be
ISG.ItemsSubGroupAraName
- much easier to read when you are doing a complex select like this
3. Read up the documentation on the SQL features you use - even if they seem simple. For example at
SUM (Transact-SQL) - SQL Server | Microsoft Docs[
^] you will learn that the
SUM
function ignores NULL values so there is no need to use
ISNULL
as well.
4. Use "white space" in your queries to make them more readable. I rearranged your query to put a linefeed in front of each join type and the first thing I noticed was there are no ON clauses for some of them
LEFT OUTER JOIN dbo.Stores S
INNER JOIN dbo.trxtypeConfig TC
I then noticed a spurious ON clause in the middle of nowhere
ON I.ItemCode = TF.ItemCode
Being neat and tidy is not just about looks! It can help you spot potential problems
5. While you're in the documentation you might find out about things like Common Table Expressions, Table variables and Temporary tables (that's "temporary" not "temporal" - they are different). You can also use these to make queries more readable. For example you are using a lot of
DateDiff(Day, GETDATE(), MAX(dbo.TrxInvH.TrxDate))
- wouldn't it be great if you could just refer to that as
DiffTrxDate
or something similar? Or
CASE WHEN TT.trxTypeCode = 1 THEN Quantity ELSE - Quantity END
could just be referred to as
Qty
. That might be a bridge too far for you just now though.
Now to actually solving your problem …
Having tidied stuff up I notice that the first two fields in your SELECT are actually identical
SELECT SUM(CASE WHEN TT.trxTypeCode = 1 THEN Quantity ELSE - Quantity END) AS Qty,
SUM(CASE WHEN TT.trxTypeCode = 1 THEN Quantity ELSE - Quantity END) AS Value,
Is that what you meant by "damage problem". It's pointless returning the same value twice so perhaps you meant to have different data in each.
Fix the problems I pointed out in point 4 above. I doubt your query even parses successfully.
If you are getting more rows than you need then it's probably data related - more than one row per parent item on one of those tables. Try breaking your query down a bit - do you really need all those joins or can you pull some of the stuff out into CTEs, table variables or temporary tables?
Beyond this, we would need sample data and expected results to have any hope of helping further