September 11, 2019 at 7:15 pm
I've included 2 rows below, but not sure that's helpful.
So maybe an explanation will help more. I'm not sure you will approve of the overall design as a best practice but here it is...
The Railcarcycle table receives inserts from 2 other tables sources i.e RailcarYard and RailcarUPD
The insert coming from RailcarYard creates the first record in the table, RailcarUPD finds the same record and does an update.
Im looking to run an SP a few times a day on the RailcarYard that inserts new records into RailcarCycle accepting duplicate 'RailcarID' except if the same RailcarID has an 'EndDate' is Null or 'StatusID' = 1
hope this helps ?
USE [SSSUTIL]
GO
INSERT INTO [dbo].[RailCarCycleYard]
([RailcarID]
,[LoadDate]
,[StatusID])
VALUES
('TILX333975','2019-08-01',1), ('TILX34475','2019-08-01',1)
GO
Just to add...Ive added 3 new records of which only the last one should insert, because the first 2 don't meet criteria as per above.
USE [SSSUTIL]
GO
INSERT INTO [dbo].[RailCarCycle]
([RailcarID]
,[StartDate]
,[StatusID])
VALUES
('TILX333975','2019-08-01',1), ('TILX34475','2019-08-01',1), ('TILX338499', '2019-08-12',1)
GO
September 11, 2019 at 7:55 pm
It was missing parenthesis on the values...this worked.
USE [SSSUTIL]
GO
INSERT INTO [dbo].[RailCarCycleYard]
([RailcarID]
,[LoadDate]
,[StatusID])
VALUES
('TILX333975'
,'2019-08-01'
,1)
GO
The point is that it should have been tested before being posted. I knew what the problem was, but other people may not have been able to see it quickly.
Drew
PS: ( and ) are parentheses. ' is a (single) quote. It was missing quotes, not parentheses.
PPS: Other dialects use parentheses as a more general term and include []{}<> in the term. I prefer to use the terms (square) brackets for [], braces (or curly brackets) for {} and angle brackets for <>.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 12, 2019 at 4:03 pm
The first and we ought to do is correct your DDL. Identity is a table property and not a column, and by definition can never be a key in a logical model. The rest of your column names are incomplete; the start and end dates of what? The postfixes "_status" and "_id" are what ISO 11179 and the metadata committee call attribute properties. Think of them as adjectives on a noun. So you got a list of adjectives with no noun. A key by definition cannot be null, so in your original schema, there's no way to have a valid relational key. There is no attempt to keep any kind of data integrity in your posting. That's why we have datatypes and check constraints.
Here's a quick correction on what you posted. Remember that 80 to 90% of the work in SQL is done in the DDL.
CREATE TABLE RailCar_Cycles -- plural name to show it is a set
(railcar_id CHAR(30) NOT NULL,
cycle_start_date DATE NOT NULL,
cycle_end_date DATE,
CHECK (cycle_start_date<= cycle_end_date)
foobar_status CHAR(1) NOT NULL
CHECK (foobar_status IN ('1', ???),
PRIMARY KEY (railcar_id, cycle_start_date, cycle_end_date)
);
INSERT INTO RailCar_Cycles
VALUES ('TILX333975', '2019-08-01', '2019-09-08','1');
As a historic note, I see you put a comma in the front of each "card image" and your insertion statements. This is exactly how we formatted our punch card decks back in the 1960s because it made it easy to rearrange the deck and insert a new card. For the last few decades, however, programmers had software that pretty print program text when we push a button. The leading comma is a really bad idea for readability. I see you also forgot to put quote marks around dates and strings. Is there any reason you used in NCHAR for the railcar identifiers? Do you have a lot of Chinese characters in those names? As I remember the ISO standards the industry identifiers for virtually everything consist of a subset of Unicode characters with the Latin alphabet digits and a few punctuation marks. The reason for limiting thousands of industry identifiers to this subset of symbols was so that any alphabet or other writing system covered by Unicode would be able to write them. In particular, units of measure in the metric system were targeted. You also don't like using the; at the end of statements. That's another old-time Sybase convention that is being replaced with components to ANSI/ISO standard SQL requirements by Microsoft.
Going back and forth between "load_date" and the ("start_date", "end_date") interval pairs in your posting is confusing. Can you clarify what you want to be done?
Please post DDL and follow ANSI/ISO standards when asking for help.
September 12, 2019 at 9:32 pm
For real ! I think you guys are taking the Piss out of me.
I appreciate the education and advise on ISO standards and best practises , Heaven knows I need to improve all my basic sql skills, But I simply need help with a script and I've expressed my requirement as best a I humanly can multiple time. (im limited) but still haven't got any help.
I have copied and re-pasted my description below.....
The Railcarcycle table receives inserts from 2 other tables sources i.e RailcarYard and RailcarUPD. The insert coming from RailcarYard creates the first record in the table, RailcarUPD finds the same record and does an update.
Im looking to run an SP a few times a day on the RailcarYard that inserts new records into RailcarCycle accepting duplicate 'RailcarID' except if the same RailcarID has an 'EndDate' is Null or 'StatusID' = 1
Not holding my breath but thank you.
September 12, 2019 at 10:04 pm
Is this what you're trying to do?
INSERT #RailCarCycle
(
RailcarID
,StartDate
,StatusID
)
SELECT rccy.RailcarID
,rccy.LoadDate
,rccy.StatusID
FROM #RailCarCycleYard rccy
WHERE NOT EXISTS
(
SELECT 1
FROM #RailCarCycle rcc
WHERE rcc.RailcarID = rccy.RailcarID
AND
(
rcc.EndDate IS NULL
OR rcc.StatusID = 1
)
);
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 12, 2019 at 10:47 pm
Hallelujah !! Yes, that worked perfectly , thank you Phil.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply