Inherited Code - Assistance please with Temp table and maximum

[Jcfx]

Hello,

I am new to sql and have inherited a project that has my grey hair turning silver exponentially. You've all been of great assistance before, I hope you will be able to help me now as well.

The following code is part of a stored procedure that is used on a GL account report. For each transaction you can have multiple details <basically comment fields that describe what the purchases were for>

Apparantly to the code author thought this was the most effiecent way to handle this. This code is above another select statement. What is happening is that if both Detail one and detail two are null in the GLcode table, this temp table is being populated with duplicates. Before these fields were not allowed to be null, now they are and I am not sure how to adjust this.

select transaction,max(GLCode.Detail1) Detail1,max(GLcode.detail2) Detail2

      INTO #Temp
      from GLcode
      group by transaction

INSERT INTO #Temp

      select transaction,max(GLCode.Detail1) Detail1,max(GLcode.detail2) Detail2
      from GLcode
      group by transaction

The result that is needed is for each transaction we only have one row.

Transaction Detail1 Detail2
123           oops   broke
345           ding          
678                   dong
912

what we are getting instead is 912 is listed twice.

there is a left outer join to this table that is used to display this data based on the transaction

Thanks for any assistance


Julie
CRXI CE10 / RS2005 Sql DB

 

[gmmastros]

Maybe I'm missing something here, but it appears as though you have a select into #Temp and an insert into #Temp, but with the same data. I'm surprised that every row isn't duplicated. Perhaps it would be better if you show some sample data from the GLCode table (not real data, of course). I suspect there is a better way to achieve the same results.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Jcfx]

Transaction	GLCode	Cost	Detail1	Detail2
123	       5006	1.25	oops	
123	       5005	1.75		broke
345	       5006	0.5	ding	
345	       5005	75.12		
912	       5001	1025		
912	       5002	75		
678	       5006	2.5		dong
678	       5006	1.45		
678	       5005	25.12

And I agree, I am not sure why there is a select and then an insert.





Julie
CRXI CE10 / RS2005 Sql DB

 

[SQLBill]

What datatype is Transaction?

Thought: That code really should be duplicating every transaction as George says. Since it's not, then there has to be something making the two 912 codes unique. If it's a char or varchar column, there could be a space before or after one of the codes.

-SQLBill

Posting advice: FAQ481-4875

 

[gmmastros]

Well. It doesn't make sense to me. Try commenting out the insert into statement and see if that fixes the problem. Unless there is more to it that you are not showing, then all values should be duplicated in the #Temp table. It could be the next statement (with the join) that is removing the other duplicates. Without seeing more of the code, it's impossible to say.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Jcfx]

Commented out the insert and still have duplicates

The detail columns are Nvarchar columns



here is the full code

ALTER PROC [dbo].[Report_Master]
@BeginDate datetime,@EndDate DateTime
AS

select transactionkey,max(GLCode.Detail1) Detail1,max(glcode.detail2) Detail2

      INTO #Temp
      From glcode
      group by transactionkey 

INSERT INTO #Temp

      select transactionkey,max(GLCode.Detail1) Detail1,max(glcode.detail2) Detail2
      from glcode
      group by transactionkey 
SELECT  

      Summary.TransactionID TransactionID
      ,CONVERT(VARCHAR(30), Summary.TransactionDateTime,101) TransactionDate
      , Summary.CustomerAccountCode AccountCode
      ,CONVERT(VARCHAR(30), Summary.PostedDateTime,101) PostedDateTime
      ,Amount=
            CASE Summary.CreditOrDebit
                  WHEN 'C' Then Summary.TotalAmountDue * -1
                  ELSE Summary.TotalAmountDue
            End
      ,'XXXXXX' + Summary.CardNumber2 CardNumber
      , Summary.CustomerID CustomerID
      ,Lookup_MCode_Group.Description Category
      ,CardAndVehicle.CardAssignedLastName + ', ' +     CardAndVehicle.CardAssignedFirstName CardHolder
      , Merchant.[Name] MerchantName
      , Merchant.City + ', ' + Merchant.State MerchantLocation
      , Merchant.MCode MCC
      ,Lookup_MCode_Detail.[Description] Description
      ,#Temp.Detail1 Detail1
      ,#Temp.Detail2 Detail2
      ,CONVERT(Varchar(30),@BeginDate,101) BeginDate
      ,CONVERT(VARCHAR(30),@EndDate,101) EndDate
      ,Lookup_MCode_Detail.MGroupId [MCGroupId]
 
      , CorporateSummary.MerchantTaxId
FROM 
      Summary
      LEFT OUTER JOIN
     Merchant 
      ON
      Summary.TransactionKey = Merchant.TransactionKey
	  JOIN
      CardAndVehicle 
      ON
      Summary.TransactionKey = CardAndVehicle.TransactionKey
      LEFT OUTER JOIN
      CorporateSummary 
      ON
      CorporateSummary.TransactionKey = Summary.TransactionKey
      LEFT OUTER JOIN
      Lookup_MCode_Detail
      ON
     Merchant. CategoryCode = Lookup_MCode_Detail. CategoryCode 
      LEFT OUTER JOIN
      Lookup_MCode_Group
      ON
      Lookup_MCode_Detail.MGroupId = Lookup_MCode_Group.MGroupId
      LEFT OUTER JOIN 
	  #Temp
      ON
      Summary.TransactionKey = #Temp.TransactionKey
     
WHERE 
     Summary.PostedDateTime between @BeginDate and @EndDate
      and Merchant.[Name] <> ''

Julie
CRXI CE10 / RS2005 Sql DB