• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 176
  • Last Modified:

Combining records

I am trying to combine records based on a grouping

Attached is the example result data  and below the current query arriving that that result.

SELECT    g.Date,g.Period,  g.Reference, g.Description, a.AccountNumber,  
                                           CASE WHEN gd.Amount > 0 THEN gd.Amount ELSE NULL END AS Debit
                                           , CASE WHEN gd.Amount < 0 THEN - gd.Amount ELSE NULL END AS Credit, gd.Amount
                     
FROM         dbo.GLJournal AS g INNER JOIN
                      dbo.GLJournalDetails AS gd ON gd.JournalID = g.JournalID INNER JOIN
                      dbo.GLAccounts AS a ON a.AccountID = gd.GLID LEFT OUTER JOIN
                      dbo.GLDepts AS d ON d.DeptID = gd.DeptID LEFT OUTER JOIN
                      dbo.ShTransactionTypes AS t ON t.TransactionTypeID = g.TransactionTypeID
                     
                  where   g.Reference = 'MOAR10700' and (a.AccountNumber like '4%' or a.AccountNumber like '5%')
                 
                  --order by RIGHT(a.AccountNumber , 3)
                  order by g.Reference


What I need is to see the result as seen in the attached based on grouping of the first  5 records.  The records should only combine if they (as in the example) have the same first 5 fields and the 5th field the last 3 digits are the same.  eg  4400 and 5400.

Hope this makes sense

THank you
example.xlsx
0
acardullo
Asked:
acardullo
1 Solution
 
lcohanDatabase AnalystCommented:
You will need to actually GROUP BY those five fields and add the

WHERE RIGHT(fifth_field, 4) IN (4400,5400)

clause as well
0
 
acardulloAuthor Commented:
I have no idea what you just said.
I also need to tell you that there are more than just the 5400 and 4400  combos.  There are 5350,4350  etx combo's as well.
0
 
acardulloAuthor Commented:
HOw do I get the values from the 5400  record in columns behind the 4400 records?
THe first 4 columns are identical  so they start the recod out. THen I need the account number  4400. then the credit debit and amount fields of the 440 record.  THen the column for the 5400 record and the credit debit and amount columns.  Hope that makes sense

so in effect  I am combining the records which have the first 4 fields  identical and also have the last 3 digits  of the account number field identical.
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
David ToddSenior DBACommented:
Hi,

Need sample data to present a more complete solution.

Please post sample data as create table/insert SQL statements, which makes it easier for experts to respond.

Regards
  David
0
 
acardulloAuthor Commented:
Here is some sample data for you guys.
PS When there are  duplicates based on the first 6 columns then those have to be grouped and sum  .

Thanks again for looking into this.

CREATE TABLE [dbo].[GMSAMPLE1](
	[Date] [datetime] NOT NULL,
	[Period] [int] NOT NULL,
	[Reference] [varchar](15) NOT NULL,
	[Description] [varchar](255) NOT NULL,
	[AccountNumber] [varchar](20) NOT NULL,
	[Debit] [money] NULL,
	[Credit] [money] NULL,
	[Amount] [money] NOT NULL
) ON [PRIMARY]




insert into GMSAMPLE1 values 
	('2013-05-05 00:00:00.000',	201304,	'MOAR102',	'FL2321: Brock And Scott Pllc',	4350,	NULL,	300.00,	-300.00),
	('2013-05-05 00:00:00.000',	201304,	'MOAR102',	'FL2321: Brock And Scott Pllc',	5350,	2.10,	NULL,	2.10),
	('2013-05-05 00:00:00.000',	201304,	'MOAR102',	'FL2321: Brock And Scott Pllc',	5350,	NULL,	2.10,	-2.10),
	('2013-05-09 00:00:00.000',	201304,	'MOAR709',	'FL2267: Classic Promotions',	4360,	NULL,	1580.90,	-1580.90),
	('2013-05-09 00:00:00.000',	201304,	'MOAR709',	'FL2267: Classic Promotions',	5350,	1.40,	NULL,	1.40),
	('2013-05-09 00:00:00.000',	201304,	'MOAR709',	'FL2267: Classic Promotions',	5360,	782.56,	NULL,	782.56),
	('2013-05-09 00:00:00.000',	201304,	'MOAR709',	'FL2267: Classic Promotions',	5350,	NULL,	1.40,	-1.40)

Open in new window

0
 
acardulloAuthor Commented:
Correction.  Grouping needs to be based on the first 5 columns.  Not 6.  The 5th columns
 only the last 3 digits of the 4 digit reference code need to be matched. If there is a record that has for instance a reference column 4350  and no other record where the first 5 columns match then that records needs to still be part of the result.
0
 
David ToddSenior DBACommented:
Hi,

use ExpertsExchange
go

if exists(
	select *
	from sys.objects
	where
		object_id = object_id( N'dbo.GMSAMPLE1' )
		and type in( N'U' )
	)
	drop table dbo.GMSAMPLE1
;
go

CREATE TABLE [dbo].[GMSAMPLE1](
	[Date] [datetime] NOT NULL,
	[Period] [int] NOT NULL,
	[Reference] [varchar](15) NOT NULL,
	[Description] [varchar](255) NOT NULL,
	[AccountNumber] [varchar](20) NOT NULL,
	[Debit] [money] NULL,
	[Credit] [money] NULL,
	[Amount] [money] NOT NULL
) ON [PRIMARY]




insert into GMSAMPLE1 values 
	('2013-05-05 00:00:00.000',	201304,	'MOAR102',	'FL2321: Brock And Scott Pllc',	4350,	NULL,	300.00,	-300.00),
	('2013-05-05 00:00:00.000',	201304,	'MOAR102',	'FL2321: Brock And Scott Pllc',	5350,	2.10,	NULL,	2.10),
	('2013-05-05 00:00:00.000',	201304,	'MOAR102',	'FL2321: Brock And Scott Pllc',	5350,	NULL,	2.10,	-2.10),
	('2013-05-09 00:00:00.000',	201304,	'MOAR709',	'FL2267: Classic Promotions',	4360,	NULL,	1580.90,	-1580.90),
	('2013-05-09 00:00:00.000',	201304,	'MOAR709',	'FL2267: Classic Promotions',	5350,	1.40,	NULL,	1.40),
	('2013-05-09 00:00:00.000',	201304,	'MOAR709',	'FL2267: Classic Promotions',	5360,	782.56,	NULL,	782.56),
	('2013-05-09 00:00:00.000',	201304,	'MOAR709',	'FL2267: Classic Promotions',	5350,	NULL,	1.40,	-1.40)


select
	[Date]
	, Period
	, Reference
	, Description
	, right( AccountNumber, 3 ) RightAccountNumber
	, sum( Debit ) SumDebit
	, sum( Credit ) SumCredit
	, sum( Amount ) SumAmount
from dbo.GMSAMPLE1
group by
	[Date]
	, Period
	, Reference
	, Description
	, right( AccountNumber, 3 )
order by
	[Date]
	, Period
	, Reference
	, Description
	, right( AccountNumber, 3 )
;

Open in new window


gives
Date	Period	Reference	Description	RightAccountNumber	SumDebit	SumCredit	SumAmount
2013-05-05 00:00:00.000	201304	MOAR102	FL2321: Brock And Scott Pllc	350	2.10	302.10	-300.00
2013-05-09 00:00:00.000	201304	MOAR709	FL2267: Classic Promotions	350	1.40	1.40	0.00
2013-05-09 00:00:00.000	201304	MOAR709	FL2267: Classic Promotions	360	782.56	1580.90	-798.34

Open in new window


HTH
  David

PS Be careful in summing the amount column. It could be a running balance, and summing it makes little sense.
0
 
acardulloAuthor Commented:
David, thanks for your help. I do however need only the values summed if there is for instance 2 records with the account number 4350.  if there is  record with the a matching 5350  account number then those 3 values need to be added as separate columns  

please see attachment
gm.csv
0
 
David ToddSenior DBACommented:
Hi,

I'm sorry but I can't reconcile your output with the sample data you provided. You have other source columns in the output that are not in the sample data.

Regards
  David
0
 
acardulloAuthor Commented:
David,  you are correct.  I wasn't very clear in my attempt to explain.
IS it clear now what I am attempting to do? Do I need to elaborate.  I am sorry if I caused you unnecessary work.
0
 
David ToddSenior DBACommented:
Hi

Please elaborate - are you trying to add columns across the row or down the column?

Can you add the extra columns into your sample so that it matches the output. I think you need a few more rows there.

Regards
  David
0
 
acardulloAuthor Commented:
what I need is the account number and credit debit amount fields added to the record  with the matching first 5 fields.  make sense?
I don't know how else to explain this.
0
 
acardulloAuthor Commented:
adding columns  to the row.
Combining the two matching records and only taking the account number debit, credit and amount and adding them to the matching record.
0
 
David ToddSenior DBACommented:
Hi

Sorry, doesn't make too much sense.

Can you post an excel spreadsheet that shows a worked example.

Regards
  David
0
 
Scott PletcherSenior DBACommented:
I think the right 3 digits/chars of the AccountNumber have more meaning to you than you're telling us.

Something like below might (should?) be able to work, but I'd need more details to go any further:



SELECT
    [Date], [Period], [Reference], [Description], RIGHT(AccountNumber, 3) AS AccountNumberRight3,
    SUM(CASE WHEN LEFT(AccountNumber, 1) = '5' THEN Amount ELSE 0 END) AS RvDeb,
    SUM(CASE WHEN LEFT(AccountNumber, 1) = '4' THEN Amount ELSE 0 END) AS RvCred,
FROM ...
GROUP BY    
    [Date], [Period], [Reference], [Description], RIGHT(AccountNumber, 3)
0
 
acardulloAuthor Commented:
The 3 digits bare very important.
Lets say there are two records  where the first 4 records identical and the account numbers respectively are 4350  and 5350.
They need to be joined.  I need the field after the account number  be added as columns to the first "4350 record. The names of the columns need to be aliased of course and maybe have a charcter added tot he column name to make it unieque from the other dollar amount columns for the 4350  record..
0
 
acardulloAuthor Commented:
So basically I need the amount debit andcredit from both the 5350 and 4350 records in one records as an

Date   Period   Reference Description account number   Debit   Credit   Amount  Account number2 Debit2 Credit2 Amount2  

Open in new window

0
 
acardulloAuthor Commented:
Anybody have any advice on my problem?

Thank you
0
 
acardulloAuthor Commented:
Does anybody have any more ideas on how to do this?

Any help would be appreciated.

THanks
0
 
acardulloAuthor Commented:
Can any of you experts please help me with this problem?

It would be greatly appreciated.

Thank you,
0
 
acardulloAuthor Commented:
I must've under estimated the degree of difficulty on this one.
0
 
acardulloAuthor Commented:
Was not a solution but a good step in the right direction.  Nobody is trying to help bring this solution to end.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now