March 13, 2012 at 9:04 am
Hi all.
I have a table that contains values for time planning. It is self-referencing, so that the pre and post columns reference the first ID column. What I need to achieve is that where the pre column is not null, to check if its value is contained in the id column of another row (to check if both pre and post rows exist and are linked) for the same day.
If so, keep this pre row but discard its post row twin. There is a reciprocal link.
If a pre value doesn't match an id from its post row on the same day, then leave both rows. The link is not complete...
Brains leaking out of ears at the mo, so could do with some guidance please.
Regards, Greg.
PS Some test data with a reciprocal link follows:Pre val 4 on row 3 links to id val 4 on row 4 and post val 3 in row 4 links back to id val 3 on row 3. We want to keep row 3 and discard row 4.
CREATE TABLE [dbo].[swap](
[id] [int] NULL,
[int] NULL,
[post] [int] NULL,
[date] [datetime] NULL,
[staffid] [int] NULL,
[shift] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[swap] ([id],
, [post], [date], [staffid], [shift]) VALUES (1, NULL, NULL, CAST(0x00009F7100000000 AS DateTime), 67, N'morning ')
INSERT [dbo].[swap] ([id],
, [post], [date], [staffid], [shift]) VALUES (2, NULL, NULL, CAST(0x00009F7100000000 AS DateTime), 67, N'morning ')
INSERT [dbo].[swap] ([id],
, [post], [date], [staffid], [shift]) VALUES (3, 4, NULL, CAST(0x00009F7100000000 AS DateTime), 67, N'holiday ')
INSERT [dbo].[swap] ([id],
, [post], [date], [staffid], [shift]) VALUES (4, NULL, 3, CAST(0x00009F7100000000 AS DateTime), 67, N'morning ')
INSERT [dbo].[swap] ([id],
, [post], [date], [staffid], [shift]) VALUES (5, NULL, NULL, CAST(0x00009F7100000000 AS DateTime), 69, N'evening ')
March 13, 2012 at 10:13 am
Well that's 14 views and no responses. I knew this would be tricky...
March 13, 2012 at 10:15 am
Actually I solved this 30 minutes ago, but I was trying to figure out whether there was a better way.
SELECT *
FROM swap AS s
WHERE NOT EXISTS (
SELECT 1
FROM swap AS s_post
WHERE s_post.id = s.post
AND EXISTS (
SELECT 1
FROM swap AS s_pre
WHERE s_pre.id = s_post.pre
AND s_pre.date = s.date
)
);
-- Gianluca Sartori
March 13, 2012 at 10:17 am
greg.bull (3/13/2012)
Well that's 14 views and no responses. I knew this would be tricky...
Also, we are volunteers. We may have read it, just didn't have time to work it.
March 13, 2012 at 10:24 am
OK thanks, let me give that a try. Much appreciated !
Greg
March 13, 2012 at 10:25 am
I'm not sure if I'm correctly understood your reqs...
Still my try:
delete Post
from swap Pre
join swap Post on Post.id = Pre.pre
where exists (select 1 from swap linked
where linked.id = Post.post and linked.date = pre.date)
March 13, 2012 at 10:27 am
Or, even simpler:
delete post
from swap Pre
join swap Post
on Post.id = Pre.pre
and pre.id = Post.post
and pre.date = post.date
March 13, 2012 at 10:37 am
That's another one for me to try - thanks all !
Greg
March 13, 2012 at 12:26 pm
Joe! Back in your cage! Time for your meds!
On-subject:
It looks like a fairly simple self-join will solve the immediate problem with the data. I'm assuming this is some sort of queue data, where each row represents an event of some sort, and the rules on keeping "post" rows are based on follow-up events to prior events defined as "pre" rows. Is that correct?
If so, do you really want to delete the "post" rows, which would eliminate the record of them, or do you want to exclude them from a query at runtime? Keeping them keeps your event queue intact, and allows for audits. Deleting them eliminates the record they ever existed, and thus eliminates the ability to audit data entry. That may not matter (I don't know what kind of events you're recording here, so can't advise on that point), but it's something you should think about in the design of this system.
If you keep those records, you can eliminate them in queries of the data, without actually deleting the rows, pretty easily.
Again, I can't really advise on that point beyond, "make sure what you're trying to do is what you actually want". I've seen plenty of situations where what a business-rule asks for is not actually what the business needs, and this looks like it might be one of them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 13, 2012 at 12:46 pm
I didn't design the table - although I'm sure the developers who did would appreciate those design comments. If they were still alive. Often, we just have to work with the business apps in place. We don't always have the luxury of redesigning with nirvana in mind.
March 13, 2012 at 12:48 pm
Hmm yes, you are right, I really want to swap those rows at query runtime, not delete them. I think I have enough on the self-join angle to sort this out now, so thanks for your help.
March 13, 2012 at 1:00 pm
greg.bull (3/13/2012)
I didn't design the table - although I'm sure the developers who did would appreciate those design comments. If they were still alive. Often, we just have to work with the business apps in place. We don't always have the luxury of redesigning with nirvana in mind.
Note that what Joe said about the table design is mostly junk. There's no reason in real life to compulsively avoid an ID column. Purist academics who don't understand that their precision grossly exceeds their accuracy believe otherwise. Same for avoiding naming a column "date". He's got a lot of academic knowledge about databases, and about ANSI-SQL and ISO standards, and he's pretty much rabid about the subjects online (I'm told that he's different in person), so sometimes his posts are partially useful, but only if you already know the subject well enough to sort the chaff from the grain in them. And if you do, you don't need his post in the first place, so he really should just avoid writing it altogether.
So don't assume that some long-ago dev made a mistake and that you're stuck with it, or need to appologize for it, or defend yourself because of it, just because Joe Celko, AKA Rabid Database Rodent (that's not actually one of his screen names, but it should be), says so.
On the other hand, I've spent many a good minute making fun of his idiocy on this regard, and it does keep me busy when I might otherwise be bored, so I guess I should thank him for that. Kind of like the "broken window" fallacy of economic theory.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 13, 2012 at 1:04 pm
Amusing ! Well thanks anyway for your humane comments (I do feel a bit quilty nonetheless as I'm sure I could have described this problem better). Anyway, I did receive the help I needed so am beetling off to try this out now, quite contented !
Regards, Greg.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply