SQL Code Assistance

[Aliq2014]

I have the following code in place is basically pulling data from an Oracle database and access information when employees are coming in to a certain building. I’m new to SQL I took this code from a previous report. Is helping me identify the data that I need. However, there is a tweak that I need to make and I’m having trouble. Basically I want to identify people who came in to a certain buildings and assigned then a unique value. For example if “CT014”, “CT015” then assign “CT” if “FL014”, “FL015” then assign “FL”. Is there a way to implement this using sql? Your assistance and help is truly appreciated.

DECLARE @FROMDATE AS VARCHAR(12)
DECLARE @TODATE AS VARCHAR(12)
DECLARE @PERIODDATE AS VARCHAR(12)
SET @FROMDATE = '06/30/2015'
SET @TODATE = '08/05/2015'
SET @PERIODDATE = '07/31/2015'
SELECT cast(floor(cast(dateadd(hour,datediff(hour,getutcdate(),getdate()),dateadd(s,JLJOURN.Local_DT,convert(datetime,'1990-01-01 00:00:00',102))) as float)) as datetime) as DDT
,EE_INFO.FULL_NM
,EE_INFO.WORK_LOC_NM
,DOOR_GRP_LOC.LOC_BLD_CD
,COUNT(*) AS NUMSWIPES
**
If/then logic:
I need to figured out A) if the DOOR_GRP_LOC.LOC_BLD_CD is in “CT014”, “CT015” then assign “CT”
B) if the DOOR_GRP_LOC.LOC_BLD_CD is in “FL014”, “FL015” then assign “FL”
**
From JLJOURN INNER JOIN EE_INFO ON JLJOURN.User_PID = EE_INFO.PERSON_ID
INNER JOIN Group_Member ON JLJOURN.Int_Data1 = Group_Member.Object_ID
INNER JOIN Groups ON Group_Member.Group_ID = Groups.Group_ID
INNER JOIN DOOR_GRP_LOC ON Groups.Group_Name = DOOR_GRP_LOC.MONITORING_GRP_DESC

WHERE (cast(floor(cast(dateadd(hour,datediff(hour,getutcdate(),getdate()),dateadd(s,JLJOURN.Local_DT,convert(datetime,'1990-01-01 00:00:00',102))) as float)) as datetime) > convert(datetime,@FROMDATE,102)
AND cast(floor(cast(dateadd(hour,datediff(hour,getutcdate(),getdate()),dateadd(s,JLJOURN.Local_DT,convert(datetime,'1990-01-01 00:00:00',102))) as float)) as datetime) < convert(datetime,@TODATE,102)
AND EE_INFO.PERIOD = convert(datetime,@PERIODDATE,102))
GROUP BY cast(floor(cast(dateadd(hour,datediff(hour,getutcdate(),getdate()),dateadd(s,JLJOURN.Local_DT,convert(datetime,'1990-01-01 00:00:00',102))) as float)) as datetime)
,EE_INFO.FULL_NM
,EE_INFO.WORK_LOC_NM
,DOOR_GRP_LOC.LOC_BLD_CD

 

[Micron]

I'm assuming you're using Access as a front end to the Oracle tables.
You will first have to create a recordset then loop through it. Within the loop you use an IF block to check the value of either the name of the field you want to check or it's ordinal number (recordset field counts are zero based) and make the assignment accordingly. Within the block, you use the Left function to make your assignment (but to what I have no idea since you didn't elaborate on that). Based on your SQL, I guess your DOOR_GRP_LOC.LOC_BLD_CD values will be in the fourth field (3). Something like:

[I]Code to Dim the required variables, create the recordset first & ensure there are records to work with, 
based on your valid sql statement. 
[/I]
Do Until rs.EOF
   rs.movefirst
   If LEFT(rs.fields(3),2)="CT" then
      make the CT assignment
   End If

   If LEFT(rs.fields(3),2)="FL" Then
      make the FL assignment
    End If
Loop

 

[ranman256]

build your query from the sql attached tables. Access doesnt use DECLAREs.