After add field to query grouping is damage and show extra lines problem

Question

1.00/5 (1 vote)

See more:

problem

after add field dbo.trxtypeConfig.TrxArbName to query grouping is damage problem .

this query below is grouping meaning it must show one line per item after add dbo.trxtypeConfig.TrxArbName query damage or

show extra data because it represent details so that i need to add TrxArbName but for last trxdate per items

or with another meaning get from every max trxdate per item I need to get TrxArbName .

TrxArbName get from trxtypeconfig based on data exist on trxinvh field trxtype

dbo.trxtypeConfig.TrxArbName is represent transaction name

it is related to trxinvh

every order must have header as one record represented by trxinvh and more than one record on footer by table trxinvf

first before add dbo.trxtypeConfig.TrxArbName

SQL

SELECT        ISNULL(SUM(CASE WHEN [trxType].trxTypeCode = 1 THEN Quantity ELSE - Quantity END), 0) AS Qty, SUM(CASE WHEN [trxType].trxTypeCode = 1 THEN Quantity  ELSE - Quantity 
                            END) AS Value, dbo.Items.ItemAraName, dbo.ItemGroups.ItemGroupAraName, dbo.ItemSubGroups.ItemSubGroupAraName, 
                         dbo.Items.ItemCode, dbo.Units.UnitAraName, MAX(dbo.TrxInvH.TrxDate) AS LastDate, CASE WHEN Items.StaticDays = 0 THEN 0 ELSE Items.StaticDays END AS StaticDays, 
                         CASE WHEN CASE WHEN Items.StaticDays = 0 THEN 0 ELSE Items.StaticDays END >= DateDiff(Day, GETDATE(), MAX(dbo.TrxInvH.TrxDate)) THEN 1 ELSE 0 END AS ActualStatictemp2, 
                         CASE WHEN DateDiff(DAY, MAX(dbo.TrxInvH.TrxDate), GETDATE()) > CASE WHEN Items.StaticDays = 0 THEN 0 ELSE Items.StaticDays END THEN 1 ELSE 0 END AS ActualStatictemp, DATEDIFF(DAY, 
                         MAX(dbo.TrxInvH.TrxDate), GETDATE()) AS ActualStatic, dbo.Items.ItemLatName, dbo.Units.UnitLatName
FROM            dbo.Units INNER JOIN
                         dbo.Items ON dbo.Units.UnitCode = dbo.Items.UnitCode LEFT OUTER JOIN
                         dbo.Stores INNER JOIN
                         dbo.trxtypeConfig INNER JOIN
                         dbo.TrxInvH ON dbo.trxtypeConfig.BranchCode = dbo.TrxInvH.BranchCode AND dbo.trxtypeConfig.trxtypecode = dbo.TrxInvH.Trxtype INNER JOIN
                         dbo.TrxInvF ON dbo.TrxInvH.BranchCode = dbo.TrxInvF.BranchCode AND dbo.TrxInvH.Trxtype = dbo.TrxInvF.Trxtype AND dbo.TrxInvH.TrxYear = dbo.TrxInvF.TrxYear AND 
                         dbo.TrxInvH.TrxSerial = dbo.TrxInvF.TrxSerial INNER JOIN
                         dbo.trxType ON dbo.trxtypeConfig.TrxTypeID = dbo.trxType.trxTypeCode ON dbo.Stores.StoreCode = dbo.TrxInvF.StoreId AND dbo.Stores.BranchCode = dbo.TrxInvF.BranchCode ON 
                         dbo.Items.ItemCode = dbo.TrxInvF.ItemCode LEFT OUTER JOIN
                         dbo.ItemGroups ON dbo.Items.ItemGroupCode = dbo.ItemGroups.ItemGroupCode LEFT OUTER JOIN
                         dbo.ItemSubGroups ON dbo.Items.ItemGroupCode = dbo.ItemSubGroups.ItemGroupCode AND dbo.Items.ItemSubGroupCode = dbo.ItemSubGroups.ItemSubGroupCode
 WHERE 1 = 1    AND (dbo.TrxInvF.BranchCode in( 1))  and trxinvf.ItemCode='         KFLHS225245' GROUP BY dbo.Items.ItemAraName, dbo.ItemGroups.ItemGroupAraName, dbo.ItemSubGroups.ItemSubGroupAraName, dbo.Items.ItemCode, dbo.Units.UnitAraName, dbo.Items.StaticDays, 
                      dbo.Items.ItemLatName, dbo.Units.UnitLatName
                         HAVING      (ISNULL(SUM(CASE WHEN [trxType].trxTypeCode = 1 THEN Quantity ELSE - Quantity END), 0) <> 0) AND 

                                         
                        (CASE WHEN CASE WHEN Items.StaticDays = 0 THEN 0 ELSE Items.StaticDays END >= DateDiff(Day, GETDATE(), MAX(dbo.TrxInvH.TrxDate)) 
                                              THEN 1 ELSE 0 END = 1)

this query must have one record per item but after add column TrxArbName to query it duplicate data

correct result is max date per item

so that how to modify query to show correct result please?

What I have tried:

After add dbo.trxtypeConfig.TrxArbName as following :

SELECT        ISNULL(SUM(CASE WHEN [trxType].trxTypeCode = 1 THEN Quantity ELSE - Quantity END), 0) AS Qty, SUM(CASE WHEN [trxType].trxTypeCode = 1 THEN Quantity  ELSE - Quantity 
                            END) AS Value, dbo.Items.ItemAraName, dbo.ItemGroups.ItemGroupAraName, dbo.ItemSubGroups.ItemSubGroupAraName, 
                         dbo.Items.ItemCode, dbo.Units.UnitAraName, MAX(dbo.TrxInvH.TrxDate) AS LastDate, CASE WHEN Items.StaticDays = 0 THEN 0 ELSE Items.StaticDays END AS StaticDays, 
                         CASE WHEN CASE WHEN Items.StaticDays = 0 THEN 0 ELSE Items.StaticDays END >= DateDiff(Day, GETDATE(), MAX(dbo.TrxInvH.TrxDate)) THEN 1 ELSE 0 END AS ActualStatictemp2, 
                         CASE WHEN DateDiff(DAY, MAX(dbo.TrxInvH.TrxDate), GETDATE()) > CASE WHEN Items.StaticDays = 0 THEN 0 ELSE Items.StaticDays END THEN 1 ELSE 0 END AS ActualStatictemp, DATEDIFF(DAY, 
                         MAX(dbo.TrxInvH.TrxDate), GETDATE()) AS ActualStatic, dbo.Items.ItemLatName, dbo.Units.UnitLatName, dbo.trxtypeConfig.TrxArbName
FROM            dbo.Units INNER JOIN
                         dbo.Items ON dbo.Units.UnitCode = dbo.Items.UnitCode LEFT OUTER JOIN
                         dbo.Stores INNER JOIN
                         dbo.trxtypeConfig INNER JOIN
                         dbo.TrxInvH ON dbo.trxtypeConfig.BranchCode = dbo.TrxInvH.BranchCode AND dbo.trxtypeConfig.trxtypecode = dbo.TrxInvH.Trxtype INNER JOIN
                         dbo.TrxInvF ON dbo.TrxInvH.BranchCode = dbo.TrxInvF.BranchCode AND dbo.TrxInvH.Trxtype = dbo.TrxInvF.Trxtype AND dbo.TrxInvH.TrxYear = dbo.TrxInvF.TrxYear AND 
                         dbo.TrxInvH.TrxSerial = dbo.TrxInvF.TrxSerial INNER JOIN
                         dbo.trxType ON dbo.trxtypeConfig.TrxTypeID = dbo.trxType.trxTypeCode ON dbo.Stores.StoreCode = dbo.TrxInvF.StoreId AND dbo.Stores.BranchCode = dbo.TrxInvF.BranchCode ON 
                         dbo.Items.ItemCode = dbo.TrxInvF.ItemCode LEFT OUTER JOIN
                         dbo.ItemGroups ON dbo.Items.ItemGroupCode = dbo.ItemGroups.ItemGroupCode LEFT OUTER JOIN
                         dbo.ItemSubGroups ON dbo.Items.ItemGroupCode = dbo.ItemSubGroups.ItemGroupCode AND dbo.Items.ItemSubGroupCode = dbo.ItemSubGroups.ItemSubGroupCode
 WHERE 1 = 1    AND (dbo.TrxInvF.BranchCode in( 1))  and trxinvf.ItemCode='         KFLHS225245' GROUP BY dbo.Items.ItemAraName, dbo.ItemGroups.ItemGroupAraName, dbo.ItemSubGroups.ItemSubGroupAraName, dbo.Items.ItemCode, dbo.Units.UnitAraName, dbo.Items.StaticDays, 
                      dbo.Items.ItemLatName, dbo.Units.UnitLatName,dbo.trxtypeConfig.TrxArbName
                         HAVING      (ISNULL(SUM(CASE WHEN [trxType].trxTypeCode = 1 THEN Quantity ELSE - Quantity END), 0) <> 0) AND 

                                         
                        (CASE WHEN CASE WHEN Items.StaticDays = 0 THEN 0 ELSE Items.StaticDays END >= DateDiff(Day, GETDATE(), MAX(dbo.TrxInvH.TrxDate)) 
                                              THEN 1 ELSE 0 END = 1)

Posted 20-Jan-19 16:28pm

ahmed_sa

Updated 21-Jan-19 1:44am

v2

Add a Solution

Comments

Santosh kumar Pithani 21-Jan-19 5:43am

Your question is not clear,improve your question with sample data and expected output.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2019-01-21T01:44:00

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

SQL

CASE WHEN Items.StaticDays = 0 THEN 0 ELSE Items.StaticDays END AS StaticDays,

which has exactly the same effect as

SQL

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

SQL

LEFT OUTER JOIN dbo.Stores S			-- No ON clause, why?
INNER JOIN      dbo.trxtypeConfig TC	-- No ON clause, why?

I then noticed a spurious ON clause in the middle of nowhere

SQL

ON I.ItemCode = TF.ItemCode -- spurious ON clause!

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

SQL

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