Deriving a new DATETIME field column from an existing DayOfMonth column

Question

Deriving a new DATETIME field column from an existing DayOfMonth column

sql1411

Ten Centuries

Points: 1390
More actions
September 6, 2012 at 10:47 am

#261578

I've three columns as FlightNumber(int), ScheduleDepartureDate(datetime), ActualArrivalDay(tinyint),
Here is how the sample data looks:
FlightNumber
123
124
125
ScheduledDeptDate
2012-08-17 00:00:00.000
2012-08-17 00:00:00.000
2012-07-31 00:00:00.000
ActualDeptDay oftheMonth
17
18
1
Now,I've to derrived a new column ActualArrivalDate(datetime) based on the ActualArrivalDay. Here is how this new column should look for the above corresponding records
ActualDeptDate
2012-08-17 00:00:00.000
2012-08-18 00:00:00.000
2012-08-01 00:00:00.000
The catch here is to add a month in this column if the ScheduleDepartureDate is last day of previous month and ArrivalDay is the 1st day of the new month.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

try this: http://www.sqlservercentral.com/articles/Best+Practices/61537/

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

sql1411 Ten Centuries Points: 1390 More actions · Answer 2

Ok so here is the revised posting.

USE [db_ABC]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

---Create a sample table

CREATE TABLE [dbo].[DeptArrival](

[FlightNumber] [int] NOT NULL,

[ScheduleDeptDate] [datetime] NOT NULL,

[ActualArrivalDay] [tinyint] NULL,

[ActualArrivalDate] [datetime] NULL

) ON [PRIMARY]

GO

--Insert some sample data manually into the table.

-- Right now I'm manually populating the data in all the columns. But 'ActualArrivalDate' should be a derrived column.

GO

INSERT INTO dbo.DeptArrival (FlightNumber, ScheduleDeptDate, ActualArrivalDay,ActualArrivalDate)

SELECT 123, '2012-08-17 00:00:00.000',17,'2012-08-17 00:00:00.000'

UNION ALL

SELECT 124, '2012-08-17 00:00:00.000',18,'2012-08-18 00:00:00.000'

UNION ALL

SELECT 123, '2012-07-31 00:00:00.000',1,'2012-08-01 00:00:00.000'

GO

--Select all the data from this table. Now this is how I want to see the data. In my table the fourth column is however empty.

GO

SELECT FlightNumber,ScheduleDeptDate,ActualArrivalDay, ActualArrivalDate

FROM dbo.DeptArrival

ORDER BY ScheduleDeptDate DESC

GO

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

If it takes more than one day to arrive.

SELECTFlightNumber,

ScheduleDeptDate,

ActualArrivalDay,

ActualArrivalDate,

CASE WHEN ActualArrivalDay >= DAY(ScheduleDeptDate)

THEN DATEADD( dd, ActualArrivalDay - 1, DATEADD( mm, DATEDIFF( mm, 0, ScheduleDeptDate), 0))

ELSE DATEADD( dd, ActualArrivalDay - 1, DATEADD( mm, 1+DATEDIFF( mm, 0, ScheduleDeptDate), 0)) END

FROM DeptArrival

ORDER BY ScheduleDeptDate DESC

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sql1411 Ten Centuries Points: 1390 More actions · Answer 4

sql1411

Ten Centuries

Points: 1390

September 6, 2012 at 12:59 pm

#1534716

It seems to be working 🙂

Thanks