May 29, 2011 at 10:10 am
I need help for following scenario.
I have a table tb1 with following datetime columns.
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10......so on
A trigger will update the each column with following conditions.
e.g if last column having null/empty date value then it should be updated from second last column value .
for example: if column c10 is empty then c9 value will updated to c10, similarly if col9 doesn't has any value then it should be updated from c8 date value and so on.
Is there any logic to achieve this objective instead of if if if if if if else if else if?
May 29, 2011 at 10:53 am
I'm curious what business case you're trying to implement.
Would you please provide more details? What is the purpose of the trigger?
At a first glance it sounds like a "semi-optimal" table design...
May 29, 2011 at 11:43 am
Dear LutzM
Actually purpose of this trigger is to deal with dates, for more detail; this tables contains different dates such as date_added, date_modified,date_created etc, e.g. date_created is empty and if the date_modified having any date should be updated to date_created, there are many many dates fields those need same treatment, using IF, SWITCH to checking each field is difficult job therefore, I required some alternate method to deal these dates scenario.
Hope You understand my requirement.
May 29, 2011 at 12:07 pm
Why not using a unpivoted versiion of the table and store the type of date in a separate column instead of adding a column per type? I there's a need for an additional date type (e.g. date_approved_by_management) you'd need to change the table def and all possibly existing views. With the unpivoted version it's just another row...
May 29, 2011 at 12:54 pm
flash.rsn (5/29/2011)
I need help for following scenario.I have a table tb1 with following datetime columns.
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10......so on
A trigger will update the each column with following conditions.
e.g if last column having null/empty date value then it should be updated from second last column value .
for example: if column c10 is empty then c9 value will updated to c10, similarly if col9 doesn't has any value then it should be updated from c8 date value and so on.
Is there any logic to achieve this objective instead of if if if if if if else if else if?
As you can imagine, this isn't a trivial task. We need some more information.
First, post the CREATE TABLE statement for th table in question.
Second, is the number of columns going to increase over time?
Third, Lutz is correct... the unpivoted version of this would make life much simpler. Does such a table exist?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 29, 2011 at 1:02 pm
CREATE TABLE tbl
(
APC DATETIME ,
ACS DATETIME ,
AET DATETIME ,
AEC DATETIME ,
AEM DATETIME ,
APR DATETIME ,
AAA DATETIME ,
AAC DATETIME ,
APD DATETIME
)
The above is the example table. Can you please post an example script for more assistance?
Really thanks for help.
May 29, 2011 at 2:28 pm
Please review my above description of scenario again from top, I only put an example of a table not a actual table having PK , constraints etc, i just want to know a logic to solve this issue and other ppls got understanding of problem and give a very good pivot table solution.
Thanks a putting a long and useless reply.
May 29, 2011 at 3:25 pm
Here's a rather meaningless example of an unpivoted table based on your equally vague original table def. As you may notice, there are no rows with NULL values allowed. This table structure should also include foreign key references to a table holding the group_id as well as a separate table for details on Date_Type.
CREATE TABLE tbl
(tbl_id INT IDENTITY(1,1),
group_id INT NOT NULL,
Date_Type CHAR(3) NOT NULL,
Date_Value DATETIME NOT NULL)
INSERT INTO tbl (group_id,Date_Type,Date_Value)
VALUES
(1,'APC',GETDATE()),
(1,'ACS',GETDATE()),
(1,'AET',GETDATE()),
(1,'AEC',GETDATE()),
(1,'AEM',GETDATE()),
(1,'APR',GETDATE()),
(1,'AAA',GETDATE()),
(1,'AAC',GETDATE()),
(1,'APD',GETDATE()),
(2,'APC',GETDATE()),
(2,'ACS',GETDATE()),
(2,'AET',GETDATE()),
(2,'AEC',GETDATE()),
(2,'AEM',GETDATE())
Please re-read your post where you added the table def. What sample script did you expect based on that? It's been said several times so far that your current design needs some "improvement". But based on what we have so far there's very little we can do.
And please understand some of us decide not to answer with just a few lines of code to solve the issue presented if we feel the design itself need to be changed. The wording may differ, but the message is the same...
May 29, 2011 at 3:29 pm
Is this what you mean? Note that it doesn't cascade the values forward.
CREATE TABLE Foobar
( a1 datetime
, a2 datetime
, a3 datetime
, a4 datetime
, a5 datetime
)
GO
INSERT foobar (a1) SELECT '1/1/2011'
INSERT foobar (a1, a2) SELECT '1/1/2011', '1/2/2011'
INSERT foobar (a1, a2, a3) SELECT '1/1/2011', '1/2/2011', '1/3/2011'
INSERT foobar (a1, a2, a3, a4) SELECT '1/1/2011', '1/2/2011', '1/3/2011', '1/4/2011'
UPDATE foobar
SET a5 = CASE WHEN a5 IS NULL THEN a4 ELSE a5 END,
a4 = CASE WHEN a4 IS NULL THEN a3 ELSE a4 END,
a3 = CASE WHEN a3 IS NULL THEN a2 ELSE a3 END,
a2 = CASE WHEN a2 IS NULL THEN a1 ELSE a2 END
SELECT *
FROM foobar
May 29, 2011 at 5:26 pm
flash.rsn (5/29/2011)
Dear LutzMActually purpose of this trigger is to deal with dates, for more detail; this tables contains different dates such as date_added, date_modified,date_created etc, e.g. date_created is empty and if the date_modified having any date should be updated to date_created, there are many many dates fields those need same treatment, using IF, SWITCH to checking each field is difficult job therefore, I required some alternate method to deal these dates scenario.
Hope You understand my requirement.
You mention using SWITCH - what database engine are you using?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 30, 2011 at 11:54 pm
CELKO (5/29/2011)
...we hate all procedural code! I have written five cursors in my career and I know I would not need to use three of the five today.
But you [font="Arial Black"]DO[/font] continue to write RBAR loops to do push stacks to build Nested Sets.;-)
CELKO (5/29/2011)
...Yep, you truly have no idea what RDBMS is. Columns have no ordering; they are accessed by name. Likewise, rows in a table have no ordering because they are elements in a set. If this were a geography forum, you would be asking about a flat earth.
BWAA-HAAA!!!! Listen to the pot calling the kettle black there, Mr. Push Stack. 😀
CELKO (5/29/2011)
Why did you feel you are not expected follow simple Netiquette?
Why, indeed. You should try it yourself sometime. :w00t: Try not being so arrogant as to make your typical ad hominem attacks on someone asking for help. :sick: Who knows? You're actually correct in some areas of your post and if you take the bite out of your posts, people may actually listen instead of saying "Thanks a putting a long and useless reply."
--Jeff Moden
Change is inevitable... Change for the better is not.
May 31, 2011 at 12:00 am
flash.rsn (5/29/2011)
CREATE TABLE tbl(
APC DATETIME ,
ACS DATETIME ,
AET DATETIME ,
AEC DATETIME ,
AEM DATETIME ,
APR DATETIME ,
AAA DATETIME ,
AAC DATETIME ,
APD DATETIME
)
The above is the example table. Can you please post an example script for more assistance?
Really thanks for help.
My apologies. I lost track of this thread. It's 2AM here. I'll try to get to it tonight. Considering your last post on this thread was 2 days ago, do you still need help?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply