Insert data not found in Table A and Table B

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insert data not found in Table A and Table B

Author

Topic

midavis
Starting Member

23 Posts

Posted - 2011-03-16 : 08:38:06

I have three tables, TableA, TableB and TableC. TableA holds the primary Ids. TableB holds a relationship back to A, but also stores an amount and a record type. I need to be able to get a sum of all the amounts from TableB including all RecordTypes from C that have not been found in TableB. See the data below.

DECLARE @tableA Table ( id int )
DECLARE @tableB Table ( id int , id_from_a int , amount decimal(8,2) , RecordType char(1) )
DECLARE @tableC Table ( RecordType char(1) )

INSERT INTO @tableA
Values
( 1 ),
( 2 ),
( 3 )

INSERT INTO @tableB
Values
( 1 , 2 , 12.3, 'G' ),
( 1 , 2 , 10.0, 'G' ),
( 2 , 1 , 53.0 , 'L' )

INSERT INTO @tableC
Values
( 'L' ), ( 'G' )

The result of the joins should be below

id_from_A , id_from_B , amount , RecordType

1 , 2 , 22.30 , G
1 , 2 , 0.00 , L
2 , 1 , 0.00 , G
2 , 1 , 53.00 , L
3 , 0 , 0.00 , G
3 , 0 , 0.00 , L

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-16 : 09:37:03

[code]DECLARE @tableA Table ( id int )
DECLARE @tableB Table ( id int , id_from_a int , amount decimal(8,2) , RecordType char(1) )
DECLARE @tableC Table ( RecordType char(1) )

INSERT INTO @tableA
Values
( 1 ),
( 2 ),
( 3 )

INSERT INTO @tableB
Values
( 1 , 2 , 12.3, 'G' ),
( 1 , 2 , 10.0, 'G' ),
( 2 , 1 , 53.0 , 'L' )

INSERT INTO @tableC
Values
( 'L' ), ( 'G' )

-- Solution here
SELECT a.id AS id_from_A,
COALESCE(MAX(b.id_from_A) OVER (PARTITION BY a.id), 0) AS id_from_B,
COALESCE(b.Amount, 0) AS amount,
c.recordtype
FROM @tableA AS a
CROSS JOIN @tableC AS c
LEFT JOIN (
SELECT id,
id_from_a,
SUM(amount) AS Amount,
RecordType
FROM @tableB
GROUP BY id,
id_from_a,
RecordType
) AS b ON b.id = a.id
AND b.RecordType = c.RecordType
ORDER BY a.id,
c.RecordType[/code]

N 56°04'39.26"
E 12°55'05.63"

midavis
Starting Member

23 Posts

Posted - 2011-03-16 : 10:04:27

Ok, that works, but lets take this a step further please. Lets say that TableD now holds the sum of all values possible from a Left Join between TableA and TableB. I now need to combine TableD and TableC to grab all the data from TableC with their missing values. Below is the data structure again. You can see (from the insert into tableD) that if I have no data at all in TableB I default the grouping of data to a RecordType of 'G'.

Thanks in advance.

DECLARE @tableC Table ( RecordType char(1) )
DECLARE @tableD Table ( id int , id_from_a int , amount decimal(8,2) , RecordType char(1) )

INSERT INTO @tableC
Values
( 'L' ), ( 'G' )

INSERT INTO @tableD
Values
( 1 , 2 , 22.3 , 'G' ),
( 2 , 1 , 53.0 , 'L' ),
( 3 , 0 , 0.0 , 'G' )

I should have the same result

id_from_A , id_from_B , amount , RecordType

1 , 2 , 22.30 , G
1 , 2 , 0.00 , L
2 , 1 , 0.00 , G
2 , 1 , 53.00 , L
3 , 0 , 0.00 , G
3 , 0 , 0.00 , L

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-16 : 15:35:10

Ok, I will hold posting a solution until you reveal ALL possible combinations.

N 56°04'39.26"
E 12°55'05.63"

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-16 : 16:07:05

Just like a hooker

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources