Import Query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Import Query

Author

Topic

WillBJepp
Starting Member

5 Posts

Posted - 2013-09-23 : 15:29:48

I am trying to manipulate the data being imported from another data source. See below:

I need to make an IF THEN statement: If KeyDate =< 01/01/2013 THEN STATUS = 'Disposed'

Notice I am adding data in the last column as everything is 'Active'

SELECT dbo.UserConfig.Id,

dbo.UserConfig.ServerConfigId, dbo.UserConfig.DisplayName,

dbo.UserConfig.UserName, dbo.UserConfig.MailboxSMTPAddr,
dbo.UserConfig.OverrideSMTPAddr,

dbo.vHandheldSummaryInfo.DeviceType, dbo.vHandheldSummaryInfo.PIN,
dbo.vHandheldSummaryInfo.HomeNetwork,

RIGHT(dbo.vHandheldSummaryInfo.PhoneNumber,10),

dbo.vHandheldSummaryInfo.ModelName,
dbo.vHandheldSummaryInfo.AppsVer,
-- (IF Keydate =< 1/01/2012
-- THEN STATUS = 'Disposed'),,
REPLACE(dbo.UserConfig.MailboxSMTPAddr,'@CompanyName.com','')

usernameshort,
Left(dbo.UserConfig.MailboxSMTPAddr,(charindex('.',dbo.UserConfig.MailboxSMTPAddr,0)-1)) firstname,
replace(dbo.vHandheldSummaryInfo.IMEI,'.','') as IMEI,
replace(Substring(dbo.UserConfig.MailboxSMTPAddr,(charindex('.',dbo.UserConfig.MailboxSMTPAddr,0)+1),100),'@CompanyName.com','') as lastname,
dbo.vHandheldSummaryInfo.ITPolicyName,'BES Server Name'='BESAdmin50',
REPLACE(dbo.UserConfig.MailboxSMTPAddr,'@CompanyName.com','') as

username2nodomain,'DomainName'='DOMAIN',('DOMAIN\' + (REPLACE

(dbo.UserConfig.MailboxSMTPAddr,'@CompanyName.com',''))),
STATUS = 'Active'
FROM dbo.UserConfig LEFT OUTER JOIN
dbo.vHandheldSummaryInfo ON dbo.UserConfig.Id =

dbo.vHandheldSummaryInfo.UserConfigId

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-23 : 15:41:38

Is KeyDate a column in either table? If it is, then you can use a CASE expression - see in red below

SELECT  dbo.UserConfig.Id ,
        dbo.UserConfig.ServerConfigId ,
        dbo.UserConfig.DisplayName ,
        dbo.UserConfig.UserName ,
        dbo.UserConfig.MailboxSMTPAddr ,
        dbo.UserConfig.OverrideSMTPAddr ,
        dbo.vHandheldSummaryInfo.DeviceType ,
        dbo.vHandheldSummaryInfo.PIN ,
        dbo.vHandheldSummaryInfo.HomeNetwork ,
        RIGHT(dbo.vHandheldSummaryInfo.PhoneNumber, 10) ,
        dbo.vHandheldSummaryInfo.ModelName ,
        dbo.vHandheldSummaryInfo.AppsVer , 
-- (IF Keydate =< 1/01/2012
-- THEN STATUS = 'Disposed'),,
        REPLACE(dbo.UserConfig.MailboxSMTPAddr, '@CompanyName.com', '') usernameshort ,
        LEFT(dbo.UserConfig.MailboxSMTPAddr,
             ( CHARINDEX('.', dbo.UserConfig.MailboxSMTPAddr, 0) - 1 )) firstname ,
        REPLACE(dbo.vHandheldSummaryInfo.IMEI, '.', '') AS IMEI ,
        REPLACE(SUBSTRING(dbo.UserConfig.MailboxSMTPAddr,
                          ( CHARINDEX('.', dbo.UserConfig.MailboxSMTPAddr, 0)
                            + 1 ), 100), '@CompanyName.com', '') AS lastname ,
        dbo.vHandheldSummaryInfo.ITPolicyName ,
        'BES Server Name' = 'BESAdmin50' ,
        REPLACE(dbo.UserConfig.MailboxSMTPAddr, '@CompanyName.com', '') AS username2nodomain ,
        'DomainName' = 'DOMAIN' ,
        ( 'DOMAIN\' + ( REPLACE(dbo.UserConfig.MailboxSMTPAddr,
                                '@CompanyName.com', '') ) ) ,
        CASE WHEN Keydate <= '20130101' THEN 'Disposed'
             ELSE 'Active'
        END AS [Status] 
        --STATUS = 'Active'
FROM    dbo.UserConfig
        LEFT OUTER JOIN dbo.vHandheldSummaryInfo ON dbo.UserConfig.Id = dbo.vHandheldSummaryInfo.UserConfigId

WillBJepp
Starting Member

5 Posts

Posted - 2013-09-23 : 16:13:14

KeyDate is a column in the Database the data is being imported from. I will map KeyData to a different field in the imported to database.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-23 : 16:31:44

Are you saying that KeyDate is a column in the TABLE dbo.UserConfig or in the TABLE dbo.vHandheldSummaryInfo? If that is the case, then what I posted would work (with some caveats if that column is in dbo.vHandheldSummaryInfo table.

If KeyDate is not a column in either of those tables, you need to find a way to associate the table that has the Keydate column with the two tables that you have in your current query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources