Updating related records

[Tupacmoche]

Hi SQL Masters,

I have the following sample table the real one has about 5,000 rows:

id_numberreceipt_numberreport_namexsequencefyreceipt_dateallocationallocation_nameAssociated10699741001751504Simth, John1120179/2/201611-A3767Faculty and Friends Campaign 2016-2017Primary10768751001751504Simth, John2220179/2/201611-A3766Faculty and Friends Campaign 2015-2016Joint Donor67834441001751504Simth, John3320179/2/201611-A3766Faculty and Friends Campaign 2015-2016Med Faculty Credit



What, I'm trying to accomplish is the following: As you can see the receipt_number is the same which means this set of records belong together. The xsequence = 1 and Associated = Primary have an allocation code = 11-A3767. I need a script that can change the other allocations that are not equal to 11-A3767 where the Associated types are equal to either 'Joint Donor' or 'Med Faculty Credit' How can this be done?:banghead:

 

[CJ_London]

As you can see

Regret can't - suggest edit your post so the data can be interpreted and also provide an example output from that data of the result you expect, which will be much easier for potential responders to understand. If responders can't understand, they won't respond

 

[Tupacmoche]

Here is the code that produces the output:

With my_cte(id_number, receipt_number, xsequence, fy, allocation, Associated)

as
(
Select id_number, g.receipt_number, xsequence, fy, allocation, Associated
from LMC_GIFT g

Join(Select Distinct receipt_number
from LMC_GIFT
where assoc_code = 'f'
) as gf
on gf.receipt_number = g.receipt_number
)
Select *
from my_cte
Where fy = 2017 and receipt_number = '1001751504'
order by receipt_number, xsequence


Here is the output again:


id_number receipt_number xsequence fy allocation Associated
0001074974 1001751504 1 2017 11-A3767 Primary
0001074975 1001751504 2 2017 11-A3766 Joint Donor
0000034440 1001751504 3 2017 11-A3766 Faculty Credit


The output would have another column set to 'C' indicating the Allocation code is different for the 'Joint Donor' and 'Faculty Credit' row which would then allow me to do something like Set Allocation = 11-A3767 Where NewColumn = 'C'


I hope this makes sense:banghead:.

 

[CJ_London]

better, but still don't understand your description of what the output is supposed to look like.

However I'm signing off now, back in 24 hours.

Post the output, someone else will pick it up

 

[Tupacmoche]

To clarify the output there would be another column added at the end of the output, I sent let say called Correction this column would have 'C' in it is the code does not match. Then, I would run the update query that I mentioned to change the allocation code in the allocation column to match the code in the primary row.

 

[CJ_London]

sorry - describing what you want is not the same as showing what you want. Since I do not understand you description, I cannot help so regret I'll have to duck out

 

[Tupacmoche]

I would like to start from scratch. Here is a script that select sample records:

Select id_number, g.receipt_number, xsequence, fy, allocation, Associated

from LMC_GIFT G

Join(SelectDistinct receipt_number
from LMC_GIFT
where assoc_code ='f'
) Gf on GF.receipt_number = G.receipt_number

Where G.fy = 2017 and g.receipt_number ='1001752244'

orderby G.receipt_number, xsequence

Here is the output:

id_number receipt_num xseq fy allocation Associated
0000260807 1001752244 1 2017 11-A3767 Primary
0001197703 1001752244 2 2017 11-A3766 Joint Donor
0000130994 1001752244 3 2017 11-A3766 Faculty Credit
0000454457 1001752244 4 2017 11-A3766 Faculty Credit
0000824649 1001752244 5 2017 11-A3766 Faculty Credit
0000624282 1001752244 6 2017 11-A3766 Faculty Credit
0001561604 1001752244 7 2017 11-A3766 Faculty Credit

I simply want a script that will select all xseq rows where the allocation number is not equal to 11-A3767 excluding xseq equal to 11.

I hope this is easier to follow. The table has 4000 rows.

 

[jdraw]

I don't think that script produced that result.

Show the actual code.

 

[DevTycoon]

[SOLVED] Updating related records

I believe this will solve your problem. Please mark the post solved if you are a happy camper.
Definition of happy camper --> http://www.urbandictionary.com/define.php?term=happy%20camper

First, Will You please clarify that the way I modeled your data is accurate with this script?

FYI the SQL is also provided in the .txt file attachments

CREATE DATABASE [Tupacmoche]
GO

USE [Tupacmoche]
GO

CREATE TABLE [dbo].[LMC_GIFT]
(
	[id_number] [int] NOT NULL PRIMARY KEY,
	[receipt_number] INT NULL,
	[xsequence] INT NULL,
	[fy] CHAR(4) NULL,
	[allocation] VARCHAR(255) NULL,
	[Associated] VARCHAR(255) NULL
) 


INSERT INTO [Tupacmoche].[dbo].[LMC_GIFT] ([id_number],[receipt_number],[xsequence],[fy],[allocation],[Associated])
VALUES (130994,1001752244,3,2017,'11-A3766','Faculty Credit')

INSERT INTO [Tupacmoche].[dbo].[LMC_GIFT] ([id_number],[receipt_number],[xsequence],[fy],[allocation],[Associated])
VALUES (260807,1001752244,1,2017,'11-A3767','Primary')

INSERT INTO [Tupacmoche].[dbo].[LMC_GIFT] ([id_number],[receipt_number],[xsequence],[fy],[allocation],[Associated])
VALUES (454457,1001752244,4,2017,'11-A3766','Faculty Credit')

INSERT INTO [Tupacmoche].[dbo].[LMC_GIFT] ([id_number],[receipt_number],[xsequence],[fy],[allocation],[Associated])
VALUES (624282,1001752244,6,2017,'11-A3766','Faculty Credit')

INSERT INTO [Tupacmoche].[dbo].[LMC_GIFT] ([id_number],[receipt_number],[xsequence],[fy],[allocation],[Associated])
VALUES (824649,1001752244,5,2017,'11-A3766','Faculty Credit')

INSERT INTO [Tupacmoche].[dbo].[LMC_GIFT] ([id_number],[receipt_number],[xsequence],[fy],[allocation],[Associated])
VALUES (1197703,1001752244,2,2017,'11-A3766','Joint Donor')

INSERT INTO [Tupacmoche].[dbo].[LMC_GIFT] ([id_number],[receipt_number],[xsequence],[fy],[allocation],[Associated])
VALUES (1561604,1001752244,7,2017,'11-A3766','Faculty Credit')

If you like that test environment then this script to get your output you need.

;With 

getPrimaryAssociatedID as
(
select id_number from LMC_GIFT where Associated in ('Primary')
)


select 
 g.*
,case 
	when coalesce(p.id_number,-999) = -999 then 'C' 
	else 'P' 
 end as OpCriteria
from LMC_GIFT g
LEFT JOIN getPrimaryAssociatedID p on g.id_number = p.id_number
--where coalesce(p.id_number,-999) = -999 and xsequence != 11

Un comment the where clause if you want your filter applied. I wanted the output to have full disclosure for you.