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 belowSELECT 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. |
|
|
|
|
|