SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Import Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WillBJepp
Starting Member

USA
5 Posts

Posted - 09/23/2013 :  15:29:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3569 Posts

Posted - 09/23/2013 :  15:41:38  Show Profile  Reply with Quote
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
Go to Top of Page

WillBJepp
Starting Member

USA
5 Posts

Posted - 09/23/2013 :  16:13:14  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3569 Posts

Posted - 09/23/2013 :  16:31:44  Show Profile  Reply with Quote
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.

Edited by - James K on 09/23/2013 16:32:05
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000