How to Insert Based On a Date in Another Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to Insert Based On a Date in Another Table

Author

Topic

Brian C
Starting Member

10 Posts

Posted - 2009-01-26 : 13:54:10

Hi Everyone, I have a logic problem that I hope someone can push me in the right direction. I am performing a select from a table in an operational database and am trying to use a date from that table to select another value (MonthID) from another table in the Aggregate Database. Then use this new selection to insert into a temp table. The temp table is merely an intermediate table where more processing will occur.

I am selecting from a table called Application and am looking at the InsertDateTime field.

In the aggregate database there is a table called Dim_Month. It contains a list of dates from 1800 to present and contains many variations of date. . There is a field called Fulldate that I want to compare against. What I want to return is the MonthID for that date. For example, Dec 2008 could have a MonthID = 1562. So for any record in the Application table that has an InsertDateTime of December 2008 I want to insert into a temp table the MonthID = 1562.

I can do it if I supply 1 date such as @Rundate with the following code:

(select D.Dim_MonthID
from Dim_Date D
where D.FullDate < @RunDate
and D.FullDate >= dateadd(d, -1, @RunDate)
),

But I get an eror when I try to include the Application.InsertDateTime field as it returns more than 1 record.

I thought I might be able to join the original Application table A on an alians table used in the inner join of the select for the MonthID, but I get the same error.

select distinct
TOM.TradeOccupationMasterID,
(select D.Dim_MonthID
from Dim_Date D
inner join [DB_Import].[dbo].Application a1 on a.applicationid = a1.applicationid
where D.FullDate < a.insertdatetime
and a.insertdatetime >= dateadd(d, -1, a.insertdatetime)
and
),

Etc

from [ATOMS_RPT].[dbo].Application A

Etc (There are more inner joins and more selected fields etc.

I hope I explained the above problem sufficently. I’m stumped on this one. Any help would be much appreciated

Thanks

Brian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources