Solved

Combining records

Posted on 2014-01-30
22
155 Views
Last Modified: 2014-02-20
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
Comment
Question by:acardullo
22 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 39822138
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
 

Author Comment

by:acardullo
ID: 39822403
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
 

Author Comment

by:acardullo
ID: 39822411
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39822686
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
 

Author Comment

by:acardullo
ID: 39823794
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
 

Author Comment

by:acardullo
ID: 39823807
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39825161
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
 

Author Comment

by:acardullo
ID: 39825256
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39825282
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
 

Author Comment

by:acardullo
ID: 39825291
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39825302
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:acardullo
ID: 39825312
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
 

Author Comment

by:acardullo
ID: 39825316
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39825586
Hi

Sorry, doesn't make too much sense.

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

Regards
  David
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39825650
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
 

Author Comment

by:acardullo
ID: 39829306
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
 

Author Comment

by:acardullo
ID: 39829312
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
 

Author Comment

by:acardullo
ID: 39832316
Anybody have any advice on my problem?

Thank you
0
 

Author Comment

by:acardullo
ID: 39850644
Does anybody have any more ideas on how to do this?

Any help would be appreciated.

THanks
0
 

Author Comment

by:acardullo
ID: 39867574
Can any of you experts please help me with this problem?

It would be greatly appreciated.

Thank you,
0
 

Author Comment

by:acardullo
ID: 39874923
I must've under estimated the degree of difficulty on this one.
0
 

Author Closing Comment

by:acardullo
ID: 39874927
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now