Solved

Access SQL Statement to SQL Server

Posted on 2006-07-10
5
253 Views
Last Modified: 2008-03-10
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
0
Comment
Question by:cory_booth
  • 3
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17074130
DATEDIFF(dd, urColumn, GETDATE())
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 17074149
something like this:

select case when [DWELL_DT]=tblACTIVE.BASD And DateDiff(day,tblACTIVE.BASD,Getdate())/30<24 Or [Dwell_DT]=[PEBD] And DateDiff(day,tblACTIVE.PEBD,getdate())/30<24 Or [DWELL_DT]=[BESD] And DateDiff(day,tblACTIVE.BESD,getdate())/30<24 then 'B'[DWELL_DT]=tblACTIVE.BASD And DateDiff("d",tblACTIVE.BASD,Now())/30>24 Or [DWELL_DT]=tblACTIVE.PEBD And DateDiff(day,tblACTIVE.PEBD,getdate())/30>24 Or [DWELL_DT]=tblACTIVE.BESD And DateDiff(day,tblACTIVE.BESD,getdate())/30>24,'N',[DWELL_DT]<>tblACTIVE.BASD And DateDiff(day,[DWELL_DT],getdate())/30>0 And DateDiff(day,[DWELL_DT],getdate())/30<=12 then '1' when [DWELL_DT]<>tblACTIVE.BASD And DateDiff(day,[DWELL_DT],getdate())/30>12 And DateDiff(day,[DWELL_DT],getdate())/30<=24 then  '2' when [DWELL_DT]<>tblACTIVE.BASD And DateDiff(day,[DWELL_DT],getdate())/30>24 then '3' else IsNull([DWELL_DT]),'D') end AS Dwell

however, you will need more () around the and and or clauses otherwise you get wrong results.
0
 

Author Comment

by:cory_booth
ID: 17074236
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
0
 

Author Comment

by:cory_booth
ID: 17074322
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"

0
 

Author Comment

by:cory_booth
ID: 17074592
Looked up the case statement,

Had to throw in the WHENS
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question