Link to home
Start Free TrialLog in
Avatar of cory_booth
cory_booth

asked on

Access SQL Statement to SQL Server

Is there a way to recreate this Access SQL Command in SQL Server?

Switch([DWELL_DT]=tblACTIVE.BASD And DateDiff("d",tblACTIVE.BASD,Now())/30<24 Or [Dwell_DT]=[PEBD] And DateDiff("d",tblACTIVE.PEBD,Now())/30<24 Or [DWELL_DT]=[BESD] And DateDiff("d",tblACTIVE.BESD,Now())/30<24,'B',[DWELL_DT]=tblACTIVE.BASD And DateDiff("d",tblACTIVE.BASD,Now())/30>24 Or [DWELL_DT]=tblACTIVE.PEBD And DateDiff("d",tblACTIVE.PEBD,Now())/30>24 Or [DWELL_DT]=tblACTIVE.BESD And DateDiff("d",tblACTIVE.BESD,Now())/30>24,'N',[DWELL_DT]<>tblACTIVE.BASD And DateDiff("d",[DWELL_DT],Now())/30>0 And DateDiff("d",[DWELL_DT],Now())/30<=12,'1',[DWELL_DT]<>tblACTIVE.BASD And DateDiff("d",[DWELL_DT],Now())/30>12 And DateDiff("d",[DWELL_DT],Now())/30<=24,'2',[DWELL_DT]<>tblACTIVE.BASD And DateDiff("d",[DWELL_DT],Now())/30>24,'3',IsNull([DWELL_DT]),'D') AS Dwell
Avatar of Aneesh
Aneesh
Flag of Canada image
DATEDIFF(dd, urColumn, GETDATE())
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cory_booth
cory_booth

ASKER

Switch(
                      [dbo.EMILPO_SOLD_T.DWELL_ST_DT] = dbo.EMILPO_SOLD_T.BASD AND DateDiff(dd, dbo.EMILPO_SOLD_T.BASD, GETDATE()) / 30 < 24 OR
                      [dbo.EMILPO_SOLD_T.DWELL_ST_DT] = [dbo.EMILPO_SOLD_T.PEBD] AND DateDiff(dd, dbo.EMILPO_SOLDER_T.PEBD, GETDATE()) / 30 < 24 OR
                      [dbo.EMILPO_SOLD_T.DWELL_ST_DT] = [dbo.EMILPO_SOLD_T.BESD] AND DateDiff(dd, dbo.EMILPO_SOLDER_T.BESD, GETDATE()) / 30 < 24, 'B',
                      [dbo.EMILPO_SOLD_T.DWELL_ST_DT] = dbo.EMILPO_SOLD_T.BASD AND DateDiff(dd, dbo.EMILPO_SOLD_T.BASD, GETDATE()) / 30 > 24 OR
                      [dbo.EMILPO_SOLD_T.DWELL_ST_DT] = dbo.EMILPO_SOLDER_T.PEBD AND DateDiff(dd, dbo.EMILPO_SOLDER_T.PEBD, GETDATE()) / 30 > 24 OR
                      [dbo.EMILPO_SOLD_T.DWELL_ST_DT] = dbo.EMILPO_SOLDER_T.BESD AND DateDiff(dd, dbo.EMILPO_SOLDER_T.BESD, GETDATE()) / 30 > 24, 'N',
                      [dbo.EMILPO_SOLD_T.DWELL_ST_DT] <> dbo.EMILPO_SOLD_T.BASD AND DateDiff(dd, [dbo.EMILPO_SOLD_T.DWELL_ST_DT], GETDATE())
                      / 30 > 0 AND DateDiff(dd, [dbo.EMILPO_SOLD_T.DWELL_ST_DT], GETDATE()) / 30 <= 12, '1',
                      [dbo.EMILPO_SOLD_T.DWELL_ST_DT] <> dbo.EMILPO_SOLD_T.BASD AND DateDiff(dd, [dbo.EMILPO_SOLD_T.DWELL_ST_DT], GETDATE())
                      / 30 > 12 AND DateDiff(dd, [dbo.EMILPO_SOLD_T.DWELL_ST_DT], GETDATE()) / 30 <= 24, '2',
                      [dbo.EMILPO_SOLD_T.DWELL_ST_DT] <> dbo.EMILPO_SOLD_T.BASD AND DateDiff(dd, [dbo.EMILPO_SOLD_T.DWELL_ST_DT], GETDATE()) / 30 > 24,
                      '3', IsNull([dbo.EMILPO_SOLD_T.DWELL_ST_DT]), 'D') AS Dwell

I receive errors on the Parse statement
Avatar of cory_booth
cory_booth

ASKER

CASE WHEN (([dbo.EMILPO_SOLD_T.DWELL_ST_DT] = dbo.EMILPO_SOLD_T.BASD AND DateDiff(dd, dbo.EMILPO_SOLD_T.BASD, GETDATE())
                      / 30 < 24 OR
                      [dbo.EMILPO_SOLD_T.DWELL_ST_DT] = [dbo.EMILPO_SOLD_T.PEBD] AND DateDiff(dd, dbo.EMILPO_SOLD_T.PEBD, GETDATE()) / 30 < 24 OR
                      [dbo.EMILPO_SOLD_T.DWELL_ST_DT] = [dbo.EMILPO_SOLD_T.BESD] AND DateDiff(dd, dbo.EMILPO_SOLD_T.BESD, GETDATE()) / 30 < 24) THEN 'B',
                      ([dbo.EMILPO_SOLD_T.DWELL_ST_DT] = dbo.EMILPO_SOLD_T.BASD AND DateDiff(dd, dbo.EMILPO_SOLD_T.BASD, GETDATE()) / 30 > 24 OR
                      [dbo.EMILPO_SOLD_T.DWELL_ST_DT] = dbo.EMILPO_SOLD_T.PEBD AND DateDiff(dd, dbo.EMILPO_SOLD_T.PEBD, GETDATE()) / 30 > 24 OR
                      [dbo.EMILPO_SOLD_T.DWELL_ST_DT] = dbo.EMILPO_SOLD_T.BESD AND DateDiff(dd, dbo.EMILPO_SOLD_T.BESD, GETDATE()) / 30 > 24) THEN 'N',
                      ([dbo.EMILPO_SOLD_T.DWELL_ST_DT] <> dbo.EMILPO_SOLD_T.BASD AND DateDiff(dd, [dbo.EMILPO_SOLD_T.DWELL_ST_DT], GETDATE())
                      / 30 > 0 AND DateDiff(dd, [dbo.EMILPO_SOLD_T.DWELL_ST_DT], GETDATE()) / 30 <= 12) THEN '1',
                      ([dbo.EMILPO_SOLD_T.DWELL_ST_DT] <> dbo.EMILPO_SOLD_T.BASD AND DateDiff(dd, [dbo.EMILPO_SOLD_T.DWELL_ST_DT], GETDATE())
                      / 30 > 12 AND DateDiff(dd, [dbo.EMILPO_SOLD_T.DWELL_ST_DT], GETDATE()) / 30 <= 24) THEN '2',
                      ([dbo.EMILPO_SOLD_T.DWELL_ST_DT] <> dbo.EMILPO_SOLD_T.BASD AND DateDiff(dd, [dbo.EMILPO_SOLD_T.DWELL_ST_DT], GETDATE())
                      / 30 > 24) THEN '3', (IsNull([dbo.EMILPO_SOLD_T.DWELL_ST_DT]) THEN 'D')) END AS Dwell


Incorrect keyword near "THEN"

Avatar of cory_booth
cory_booth

ASKER

Looked up the case statement,

Had to throw in the WHENS