UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Add Iif Then Else Into This Many Joined Query, SQL Server 2008 R2    
 
   
smyeong
post Aug 8 2018, 02:54 AM
Post#1



Posts: 399
Joined: 6-November 02
From: Malaysia


Hi, I wonder if i could able to add IF then else into below SQL ? I need to modify below Alias " OTHR " so that it would show value according to below condition
if departmentID = '15' then it would use DATEDIFF(MI, '17:00:00', TimeOut)
else
DATEDIFF(MI, '17:30:00', TimeOut)
end if





-------------------------------------------------- begin --------------------------------

SELECT dbo.VW_RPT_ATTD_RECORD.TrDate, dbo.VW_RPT_ATTD_RECORD.TrDay, dbo.VW_RPT_ATTD_RECORD.DepartmentDesc, dbo.VW_RPT_ATTD_RECORD.CardNo,
dbo.VW_RPT_ATTD_RECORD.Name, dbo.VW_RPT_ATTD_RECORD.TimeIn, dbo.VW_RPT_ATTD_RECORD.TimeOut, dbo.VW_RPT_ATTD_RECORD.EMPGROUP,



CASE WHEN dbo.VW_RPT_ATTD_RECORD.TrDate IN
(SELECT SDATE
FROM DIM_USAT_VIW
WHERE SGROUP <> EMPGROUP) THEN FLOOR(CAST(DATEDIFF(MI, '13:00:00', TimeOut) / 30 AS decimal(10, 2)))
/ 2 WHEN dbo.VW_RPT_ATTD_RECORD.TrDate IN
(SELECT SDATE
FROM DIM_USAT_VIW
WHERE SGROUP = EMPGROUP) THEN FLOOR(CAST(DATEDIFF(MI, '08:30:00', TimeOut) / 30 AS decimal(10, 2)))
/ 2 WHEN dbo.VW_RPT_ATTD_RECORD.TrDate IN
(SELECT HDDATE
FROM DIM_HDCAL) THEN FLOOR(CAST(DATEDIFF(MI, '08:30:00', TimeOut) / 30 AS decimal(10, 2))) / 2 WHEN (((DATEPART(dw,
dbo.VW_RPT_ATTD_RECORD.TrDate) + @@DATEFIRST) % 7) IN (0) AND EMPGROUP IS NULL) THEN FLOOR(CAST(DATEDIFF(MI, '13:00:00', TimeOut)
/ 30 AS decimal(10, 2))) / 2 WHEN (((DATEPART(dw, dbo.VW_RPT_ATTD_RECORD.TrDate) + @@DATEFIRST) % 7) IN (1)) THEN FLOOR(CAST(DATEDIFF(MI, '08:30:00',
TimeOut) / 30 AS decimal(10, 2))) / 2 ELSE FLOOR(CAST(DATEDIFF(MI, '17:30:00', TimeOut) / 30 AS decimal(10, 2))) / 2 END AS OTHR,





dbo.VW_RPT_ATTD_RECORD.DepartmentCode, CASE WHEN dbo.VW_RPT_ATTD_RECORD.TrDate IN
(SELECT HDDATE
FROM DIM_HDCAL
WHERE HDTYPE = 'PUBLIC') THEN 'PH' WHEN dbo.VW_RPT_ATTD_RECORD.TrDate IN
(SELECT SDATE
FROM DIM_USAT_VIW
WHERE (SGROUP = EMPGROUP)) THEN 'OFFDAY' ELSE '' END AS OFFDAY, dbo.ManualEntrytbl.Remarks, dbo.VW_RPT_ATTD_RECORD.DepartmentId

FROM dbo.VW_RPT_ATTD_RECORD LEFT OUTER JOIN
dbo.ManualEntrytbl ON dbo.VW_RPT_ATTD_RECORD.CardNo = dbo.ManualEntrytbl.CardNo AND YEAR(dbo.VW_RPT_ATTD_RECORD.TrDate)
= YEAR(dbo.ManualEntrytbl.TrDate) AND MONTH(dbo.VW_RPT_ATTD_RECORD.TrDate) = MONTH(dbo.ManualEntrytbl.TrDate) AND
DAY(dbo.VW_RPT_ATTD_RECORD.TrDate) = DAY(dbo.ManualEntrytbl.TrDate)


------------------- end ----------------------

Appreciate any input here..
Thanks a lot,


--------------------
We should see thing in both side. positive and negative
Pls visit http://ipohtech.blogspot.com for tech solutions around the world..
Malaysia.
Go to the top of the page
 
cheekybuddha
post Aug 8 2018, 03:49 AM
Post#2


UtterAccess VIP
Posts: 10,261
Joined: 6-December 03
From: Telegraph Hill


If I understand correctly what you are trying to achieve, you could try:
CODE
SELECT
  v.TrDate,
  v.TrDay,
  v.DepartmentDesc,
  v.CardNo,
  v.Name,
  v.TimeIn,
  v.TimeOut,
  v.EMPGROUP,
  CASE
    WHEN v.TrDate IN (
      SELECT SDATE
      FROM DIM_USAT_VIW
      WHERE SGROUP <> EMPGROUP
    ) THEN FLOOR(CAST(DATEDIFF(MI, '13:00:00', TimeOut) / 30 AS decimal(10, 2))) / 2
    WHEN v.TrDate IN (
      SELECT SDATE
      FROM DIM_USAT_VIW
      WHERE SGROUP = EMPGROUP
    ) THEN FLOOR(CAST(DATEDIFF(MI, '08:30:00', TimeOut) / 30 AS decimal(10, 2))) / 2
    WHEN v.TrDate IN (
      SELECT HDDATE
      FROM DIM_HDCAL
    ) THEN FLOOR(CAST(DATEDIFF(MI, '08:30:00', TimeOut) / 30 AS decimal(10, 2))) / 2
    WHEN (((DATEPART(dw, v.TrDate) + @@DATEFIRST) % 7) IN (0) AND EMPGROUP IS NULL)
      THEN FLOOR(CAST(DATEDIFF(MI, '13:00:00', TimeOut) / 30 AS decimal(10, 2))) / 2
    WHEN (((DATEPART(dw, v.TrDate) + @@DATEFIRST) % 7) IN (1))
      THEN FLOOR(CAST(DATEDIFF(MI, '08:30:00', TimeOut) / 30 AS decimal(10, 2))) / 2
    ELSE FLOOR(CAST(DATEDIFF(MI, CASE WHEN v.DepartmentID = '15' THEN '17:00:00' ELSE '17:30:00' END, TimeOut) / 30 AS decimal(10, 2))) / 2
  END AS OTHR,
  v.DepartmentCode,
  CASE
    WHEN v.TrDate IN (
      SELECT HDDATE
    FROM DIM_HDCAL
    WHERE HDTYPE = 'PUBLIC'
    ) THEN 'PH'
    WHEN v.TrDate IN (
      SELECT SDATE
      FROM DIM_USAT_VIW
      WHERE (SGROUP = EMPGROUP)
    ) THEN 'OFFDAY'
    ELSE ''
  END AS OFFDAY,
  t.Remarks,
  v.DepartmentId
FROM VW_RPT_ATTD_RECORD v
LEFT OUTER JOIN dbo.ManualEntrytbl t
             ON v.CardNo = t.CardNo
            AND YEAR(v.TrDate) = YEAR(t.TrDate)
            AND MONTH(v.TrDate) = MONTH(t.TrDate)
            AND DAY(v.TrDate) = DAY(t.TrDate)
;

--------------------


Regards,

David Marten
Go to the top of the page
 
smyeong
post Aug 8 2018, 07:38 PM
Post#3



Posts: 399
Joined: 6-November 02
From: Malaysia


Thanks CheekyBuddha,

Your good code has resolved my issue and work brilliantly.

CASE WHEN v.DepartmentID = '15' THEN '17:00:00' ELSE '17:30:00' END,

Thanks...



--------------------
We should see thing in both side. positive and negative
Pls visit http://ipohtech.blogspot.com for tech solutions around the world..
Malaysia.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st August 2018 - 08:47 AM