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

Question

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
January 20, 2019 at 8:33 pm

#409447

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
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.UnitLatNameFROM    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)
result of query
QtyValueItemAraNameItemGroupAraNameItemSubGroupAraNameItemCodeUnitAraNameLastDateStaticDaysActualStatictemp2ActualStatictempActualStaticItemLatNameUnitLatName2828كيس لحاف استرايب 225*245اكياس الحف ومخدات وخداديات KFMKكيس مخدة استرايب KFMKS   KFLHS225245عدد2018-04-23 00:00:00.000011273كيس لحاف استرايب 225*245عدد
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.TrxArbNameFROM    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)
result is wrong and more than one record per item
QtyValueItemAraNameItemGroupAraNameItemSubGroupAraNameItemCodeUnitAraNameLastDateStaticDaysActualStatictemp2ActualStatictempActualStaticItemLatNameUnitLatNameTrxArbName11كيس لحاف استرايب 225*245اكياس الحف ومخدات وخداديات KFMKكيس مخدة استرايب KFMKS   KFLHS225245عدد2017-12-31 00:00:00.000011386كيس لحاف استرايب 225*245عددتسوية بالإضافة -1-1كيس لحاف استرايب 225*245اكياس الحف ومخدات وخداديات KFMKكيس مخدة استرايب KFMKS   KFLHS225245عدد2018-04-23 00:00:00.000011273كيس لحاف استرايب 225*245عددتسوية بالخصم2828كيس لحاف استرايب 225*245اكياس الحف ومخدات وخداديات KFMKكيس مخدة استرايب KFMKS   KFLHS225245عدد2017-04-02 00:00:00.000011659كيس لحاف استرايب 225*245عددرصيد افتتاحي
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
QtyValueItemAraNameItemGroupAraNameItemSubGroupAraNameItemCodeUnitAraNameLastDateStaticDaysActualStatictemp2ActualStatictempActualStaticItemLatNameUnitLatNameTrxArbName-1-1كيس لحاف استرايب 225*245اكياس الحف ومخدات وخداديات KFMKكيس مخدة استرايب KFMKS   KFLHS225245عدد2018-04-23 00:00:00.000011273كيس لحاف استرايب 225*245عددتسوية بالخصم
so that how to modify query to show correct result please?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11412 More actions · Answer 1

This is too long and not enough information is provided. Can you please provide DDL and sample data as well as your expected results. The query you have won't work for the rest of us as we don't have your environment

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ninjamy SSC Enthusiast Points: 117 More actions · Answer 2

ahmed_elbarbary.2010 - Sunday, January 20, 2019 8:33 PM
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
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.UnitLatNameFROM    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)
result of query
QtyValueItemAraNameItemGroupAraNameItemSubGroupAraNameItemCodeUnitAraNameLastDateStaticDaysActualStatictemp2ActualStatictempActualStaticItemLatNameUnitLatName2828ÙƒÙŠØ³ Ù„ØØ§Ù Ø§Ø³ØªØ±Ø§ÙŠØ¨ 225*245Ø§ÙƒÙŠØ§Ø³ Ø§Ù„ØÙ ÙˆÙ…Ø®Ø¯Ø§Øª ÙˆØ®Ø¯Ø§Ø¯ÙŠØ§Øª KFMKÙƒÙŠØ³ Ù…Ø®Ø¯Ø© Ø§Ø³ØªØ±Ø§ÙŠØ¨ KFMKS   KFLHS225245Ø¹Ø¯Ø¯2018-04-23 00:00:00.000011273ÙƒÙŠØ³ Ù„ØØ§Ù Ø§Ø³ØªØ±Ø§ÙŠØ¨ 225*245Ø¹Ø¯Ø¯
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.TrxArbNameFROM    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)
result is wrong and more than one record per item
QtyValueItemAraNameItemGroupAraNameItemSubGroupAraNameItemCodeUnitAraNameLastDateStaticDaysActualStatictemp2ActualStatictempActualStaticItemLatNameUnitLatNameTrxArbName11ÙƒÙŠØ³ Ù„ØØ§Ù Ø§Ø³ØªØ±Ø§ÙŠØ¨ 225*245Ø§ÙƒÙŠØ§Ø³ Ø§Ù„ØÙ ÙˆÙ…Ø®Ø¯Ø§Øª ÙˆØ®Ø¯Ø§Ø¯ÙŠØ§Øª KFMKÙƒÙŠØ³ Ù…Ø®Ø¯Ø© Ø§Ø³ØªØ±Ø§ÙŠØ¨ KFMKS   KFLHS225245Ø¹Ø¯Ø¯2017-12-31 00:00:00.000011386ÙƒÙŠØ³ Ù„ØØ§Ù Ø§Ø³ØªØ±Ø§ÙŠØ¨ 225*245Ø¹Ø¯Ø¯ØªØ³ÙˆÙŠØ© Ø¨Ø§Ù„Ø¥Ø¶Ø§ÙØ© -1-1ÙƒÙŠØ³ Ù„ØØ§Ù Ø§Ø³ØªØ±Ø§ÙŠØ¨ 225*245Ø§ÙƒÙŠØ§Ø³ Ø§Ù„ØÙ ÙˆÙ…Ø®Ø¯Ø§Øª ÙˆØ®Ø¯Ø§Ø¯ÙŠØ§Øª KFMKÙƒÙŠØ³ Ù…Ø®Ø¯Ø© Ø§Ø³ØªØ±Ø§ÙŠØ¨ KFMKS   KFLHS225245Ø¹Ø¯Ø¯2018-04-23 00:00:00.000011273ÙƒÙŠØ³ Ù„ØØ§Ù Ø§Ø³ØªØ±Ø§ÙŠØ¨ 225*245Ø¹Ø¯Ø¯ØªØ³ÙˆÙŠØ© Ø¨Ø§Ù„Ø®ØµÙ…2828ÙƒÙŠØ³ Ù„ØØ§Ù Ø§Ø³ØªØ±Ø§ÙŠØ¨ 225*245Ø§ÙƒÙŠØ§Ø³ Ø§Ù„ØÙ ÙˆÙ…Ø®Ø¯Ø§Øª ÙˆØ®Ø¯Ø§Ø¯ÙŠØ§Øª KFMKÙƒÙŠØ³ Ù…Ø®Ø¯Ø© Ø§Ø³ØªØ±Ø§ÙŠØ¨ KFMKS   KFLHS225245Ø¹Ø¯Ø¯2017-04-02 00:00:00.000011659ÙƒÙŠØ³ Ù„ØØ§Ù Ø§Ø³ØªØ±Ø§ÙŠØ¨ 225*245Ø¹Ø¯Ø¯Ø±ØµÙŠØ¯ Ø§ÙØªØªØ§ØÙŠ
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
QtyValueItemAraNameItemGroupAraNameItemSubGroupAraNameItemCodeUnitAraNameLastDateStaticDaysActualStatictemp2ActualStatictempActualStaticItemLatNameUnitLatNameTrxArbName-1-1ÙƒÙŠØ³ Ù„ØØ§Ù Ø§Ø³ØªØ±Ø§ÙŠØ¨ 225*245Ø§ÙƒÙŠØ§Ø³ Ø§Ù„ØÙ ÙˆÙ…Ø®Ø¯Ø§Øª ÙˆØ®Ø¯Ø§Ø¯ÙŠØ§Øª KFMKÙƒÙŠØ³ Ù…Ø®Ø¯Ø© Ø§Ø³ØªØ±Ø§ÙŠØ¨ KFMKS   KFLHS225245Ø¹Ø¯Ø¯2018-04-23 00:00:00.000011273ÙƒÙŠØ³ Ù„ØØ§Ù Ø§Ø³ØªØ±Ø§ÙŠØ¨ 225*245Ø¹Ø¯Ø¯ØªØ³ÙˆÙŠØ© Ø¨Ø§Ù„Ø®ØµÙ…
so that how to modify query to show correct result please?

I would check your trxtypeconfig table and make sure you dont have duplicates, checking the values you are using in your query.. in the joins and groupings.