March 17, 2010 at 1:22 pm
I am trying to come up with a solution for the following problem:
In a table with 'X' amount of receipt values, remove all rows with the same receipt after the 4th occurence (per user request).
For example:
ID Receipt
1 A
2 A
3 A
1 B
2 B
3 B
..and so on.
I am trying VERY hard to avoid a cursor because they are performance hogs, and can't really come up with a set based solution. My original thought was to just have a @variable to hold the receipt and change it based off the next row but that is wrong. I then thought maybe an identity column based off the receipt but I don't think that is possible either.
If I can get the ID's to be sequential and reset based off of the receipt, The delete part will be easy.
Delete from TABLE where ID > 4
I am using SQL Server 2000.
Suggestions?
Thanks,
-Mike
March 17, 2010 at 2:10 pm
Sounds like a job for ROW_NUMBER() OVER (PARTITION BY), but I don't think that is available in SQL 2000.
March 17, 2010 at 2:39 pm
@steve-2: You're right. Row_number and the like is not an option here. Unfortunately.
@mike-2: If the data you have actually look like the sample you descirbed it's fairly easy (DELETE ID>4).
Therefore, I'm guessing that you don't have the ID column filled yet.
This actually raise the question about how the receipt need to be ordered (what are the criteria to define the 4th?). It looks like there are some addtl. columns missing. Please clarify.
Otherwise, you could get your desired output based on something like the following.
It's using the columns you mentioned and gives the expected output. But I'm pretty sure it's not what you're looking for...
So, please help us help you.
SELECT ROW, col1
FROM
(
SELECT col1
FROM(
SELECT 'a' AS col1 UNION ALL
SELECT 'a' UNION ALL
SELECT 'a' UNION ALL
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'b' UNION ALL
SELECT 'b'
) t1
GROUP BY col1
)t2
CROSS JOIN
(
SELECT 1 AS ROW UNION ALL
SELECT 2 UNION ALL
SELECT 3
)s
March 17, 2010 at 3:24 pm
try this?
CREATE TABLE [dbo].[test](
[id] [int] IDENTITY(1,1) NOT NULL,
[receipt] [char](10) NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[testtemp](
[idmx] [int] NULL,
[receipt] [char](10) NULL
) ON [PRIMARY]
GO
insert into test values('A');
insert into test values('A');
insert into test values('A');
insert into test values('A');
insert into test values('A');
insert into test values('B');
insert into test values('B');
insert into test values('C');
insert into test values('C');
insert into test values('C');
insert into test values('C');
declare @stop int
set @stop = 1;
while @stop > 0
begin
insert into testtemp
select max(id) as idmx,receipt from test where receipt in (select receipt from test group by receipt having count(*) > 3)
group by receipt
delete from test where id in (select idmx from testtemp);
set @stop = @@rowcount;
end
March 17, 2010 at 4:36 pm
lmu92 (3/17/2010)
@Steve: You're right. Row_number and the like is not an option here. Unfortunately.@mike-2: If the data you have actually look like the sample you descirbed it's fairly easy (DELETE ID>4).
Therefore, I'm guessing that you don't have the ID column filled yet.
This actually raise the question about how the receipt need to be ordered (what are the criteria to define the 4th?). It looks like there are some addtl. columns missing. Please clarify.
Otherwise, you could get your desired output based on something like the following.
It's using the columns you mentioned and gives the expected output. But I'm pretty sure it's not what you're looking for...
So, please help us help you.
SELECT ROW, col1FROM
(
SELECT col1
FROM(
SELECT 'a' AS col1 UNION ALL
SELECT 'a' UNION ALL
SELECT 'a' UNION ALL
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'b' UNION ALL
SELECT 'b'
) t1
GROUP BY col1
)t2
CROSS JOIN
(
SELECT 1 AS ROW UNION ALL
SELECT 2 UNION ALL
SELECT 3
)s
@steve-2: Yes I did notice that, but it is only available in 05+. We are upgrading soon so I will probably rewrite it using that when we do the upgrade, but until then, I am stuck.
@LMU: There are other columns, but I just simplified it to get my point across. What the program actually does is combines donor information based on the given gift. So if you, me, and 4 other people gave a gift to the university, there would be a total of 6 gift records saying how much we each gave.
Each of these gifts would have the same receipt because it was given at the same time for the same department. So receipt "A" would have the 6 gift records for each donor.
The users wanted each gift to be on 1 line, with up to a total of 4 of the donors on that exact line. What I did was take the receipt from the gift table, and inserted that into another table with the donor information they wanted. So Table1 has 1 record, Table2 has 6 records. I joined Table2 to Table1 4 times based on the receipt, which allowed me to get the donor information on 1 line.
The final table has the Receipt, DateEntered, Allocation, Donor1_Name, Donor1_ID, Donor2_Name, Donor2_ID, etc. and stops at 4.
The table which holds the donors just needs to be ordered by receipt; that way, like receipts are together. This Table2 still holds all six donors, and I need to get rid of anything above the 4th one.
Hope this helps.
Thanks,
-Mike
March 17, 2010 at 4:56 pm
mth13 (3/17/2010)
...The table which holds the donors just needs to be ordered by receipt; that way, like receipts are together. This Table2 still holds all six donors, and I need to get rid of anything above the 4th one.
...
What would define the order of the 6 donors to be able to eliminate two out of 6 for an IDENTICAL receipt?
What is "the 4th one" based on? Is it DateEntered, Allocation, Donor1_Name, Donor1_ID, Donor2_Name, Donor2_ID, or one of the "etc." columns?
Right now there is no way to tell how the donations need to be sorted. Once you tell us the column you want to use to define the rows that need to be kept, please provide sample data for that column as well. Feel free to modify the sample code I already used to give us something we can simply copy and paste. Use the code tags on the left side of the reply box will help a lot.
March 17, 2010 at 6:33 pm
lmu92 (3/17/2010)
mth13 (3/17/2010)
...The table which holds the donors just needs to be ordered by receipt; that way, like receipts are together. This Table2 still holds all six donors, and I need to get rid of anything above the 4th one.
...
What would define the order of the 6 donors to be able to eliminate two out of 6 for an IDENTICAL receipt?
What is "the 4th one" based on? Is it DateEntered, Allocation, Donor1_Name, Donor1_ID, Donor2_Name, Donor2_ID, or one of the "etc." columns?
Right now there is no way to tell how the donations need to be sorted. Once you tell us the column you want to use to define the rows that need to be kept, please provide sample data for that column as well. Feel free to modify the sample code I already used to give us something we can simply copy and paste. Use the code tags on the left side of the reply box will help a lot.
The order would be based on one of the "etc" columns, which would be the "GiftDonorType" field. Here is the table definition:
CREATE TABLE [dbo].[donorinfo](
[ID] [int] NULL,
[BSRID] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Receipt] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Alloc] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateEntered] [datetime] NULL,
[GiftFlag] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GiftDonorType] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DonorLegalCredit] [numeric](14, 2) NULL,
[DonorAssocCredit] [numeric](14, 2) NULL,
[DonorProspectPrincipal] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DonorProspectGroup] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DonorRecordType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DonorClassYear] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DonorSchool] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DonorSpouseBSRID] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DonorName] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FormalSalutation] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[JointMailName1] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[JointMailName2] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PresSalutation] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PresMailingName] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address1] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address2] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address3] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Country] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Here is a sample of the data:
Receipt DonorID Alloc DateEntered GiftFlag GiftDonorType
0001234560123456781ABCDE01/01/1800GGroup Donor
0001234560123456782ABCDE01/01/1800GGroup Donor
0001234560123456783ABCDE01/01/1800G Group Donor
0001234560123456784ABCDE01/01/1800G Primary Donor
0005678911234512345AAABA01/01/1900GGroup Donor
0005678921234511111AAABA01/01/1900GGroup Donor
0005678931232122313AAABA01/01/1900GPrimary Donor
Here is what I need:
GiftID ReceiptDonorIDAlloc DateEnteredGiftFlagGiftDonorType
1 0001234560123456781ABCDE01/01/1800GGroup Donor
2 0001234560123456782ABCDE01/01/1800GGroup Donor
3 0001234560123456783ABCDE01/01/1800G Group Donor
4 0001234560123456784ABCDE01/01/1800G Primary Donor
1 0005678911234512345AAABA01/01/1900GGroup Donor
2 0005678921234511111AAABA01/01/1900GGroup Donor
3 0005678931232122313AAABA01/01/1900GPrimary Donor
The users don't really care which donors show up, as long as the primary always shows first.
I hope this brings more clarity to my complex situation.
Thanks,
-Mike
March 18, 2010 at 3:06 pm
There is no column [DonorID] in your sample table. Where
City, state and zip as well as some other columns defined as VARCHAR(8000) seems to be "slightly oversized".
Finally: Your sample data and your written description don't match.
1) Why do the rwos with Receipt 000567891,000567892. and 000567893 have sequential GiftID's?
2) Requirement "...as long as the primary always shows first..." cannot be found in the expected output.
3) The number of rows is identical for source and target. It seems like it's not required to think about how to "stop at 4". Therewith, the whole task is in question...
Please help us help you by providing verifyed and meaningful test data, preferred in a ready to use format.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply