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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

Starting Member

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.vHandheldSummaryInfo.DeviceType, dbo.vHandheldSummaryInfo.PIN,


-- (IF Keydate =< 1/01/2012
-- THEN STATUS = 'Disposed'),,

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),'','') as lastname,
dbo.vHandheldSummaryInfo.ITPolicyName,'BES Server Name'='BESAdmin50',
REPLACE(dbo.UserConfig.MailboxSMTPAddr,'','') as

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

STATUS = 'Active'
dbo.vHandheldSummaryInfo ON dbo.UserConfig.Id =


James K
Flowing Fount of Yak Knowledge

3873 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, '', '') usernameshort ,
             ( CHARINDEX('.', dbo.UserConfig.MailboxSMTPAddr, 0) - 1 )) firstname ,
        REPLACE(dbo.vHandheldSummaryInfo.IMEI, '.', '') AS IMEI ,
                          ( CHARINDEX('.', dbo.UserConfig.MailboxSMTPAddr, 0)
                            + 1 ), 100), '', '') AS lastname ,
        dbo.vHandheldSummaryInfo.ITPolicyName ,
        'BES Server Name' = 'BESAdmin50' ,
        REPLACE(dbo.UserConfig.MailboxSMTPAddr, '', '') AS username2nodomain ,
        'DomainName' = 'DOMAIN' ,
        ( 'DOMAIN\' + ( REPLACE(dbo.UserConfig.MailboxSMTPAddr,
                                '', '') ) ) ,
        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

Starting Member

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

3873 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  
 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.03 seconds. Powered By: Snitz Forums 2000