CODEWITHSUNDEEP
c#sqlsql-server
william
william

Reputation: 7664

complicated sql time calculation

I have 2 tables VISIT and CUSTINFO

In VISIT, I have following.

EnterTime, NextTime, CustInfono

In CUSTINFO, I have following.

ApptTime, CustInfono

And the following is my logic in simple if-else statement.

if(visit.custinfono==0)

{ Result=NextTime-EnterTime }

else

{

where visit.custinfono= custinfo.custinfono    

if(EnterTime>ApptTime)

{ Result=NextTime-EnterTime }

else

{ Result=NextTime-ApptTime }

if(Result<0)

{ Result= 0 }

}

How am i supposed to accomplish this in sql statement?

Or do I have to query all necessary fields and do the caluclation in my code?

Upvotes: 0

Views: 194

Answers (3)

Jeremy Shimanek
Jeremy Shimanek

Reputation: 512

Result in one column:

select
    case 
        when visit_custinfono = 0 then nexttime_minus_entertime 
        when visit_custinfono = custinfo_custinfono then
            case 
                when EnterTime > ApptTime then nexttime_minus_entertime_p 
                else nexttime_minus_appttime_p
            end
    end as result
from
(
    select
        visit_custinfono,
        custinfo_custinfono,
        EnterTime,
        ApptTime,
        NextTime - EnterTime as nexttime_minus_entertime,
        NextTime - ApptTime as nexttime_minus_appttime,
        case when nexttime_minus_entertime < 0 then 0 else nexttime_minus_entertime end as nexttime_minus_entertime_p,
        case when nexttime_minus_appttime < 0 then 0 else nexttime_minus_appttime end as nexttime_minus_appttime_p
    from
        (
            select
                visit.custinfono as visit_custinfono,
                custinfo.custinfono as custinfo_custinfono,
                NextTime,
                EnterTime,
                ApptTime,
                NextTime - EnterTime as nexttime_minus_entertime,
                NextTime - ApptTime as nexttime_minus_appttime
            from
                visit 
            left join
                custinfo
            on
                visit.custinfono = custinfo.custinfono
        ) as subquery1
) as subquery2

Upvotes: 1

Brad Christie
Brad Christie

Reputation: 101594

As @MacyAbbey mentioned, you need to use more than just select logic. I haven't tested this, but think it will work.

DELIMITER |

CREATE FUNCTION mydatediff(CustInfono INT, EnterTime TIMESTAMP, NextTime TIMESTAMP, ApptTime TIMESTAMP) RETURNS INT
  BEGIN
    DECLARE result INT DEFAULT 0;

    CASE Custinfono
      WHEN 0 THEN SET result = DATEDIFF(NextTime,EnterTime);
      ELSE
        BEGIN
          IF EnterTime>ApptTime THEN result = DATEDIFF(NextTime,EnterTime);
          ELSE SET result = DATEDIFF(NextTime,ApptTime);
          IF result < 0 THEN SET result = 0;
        END;
    END CASE;

    RETURN result;

  END;
  |

The execute with:

SELECT            mydatediff(VISIT.CustInfono, VISIT.EnterTime,  VISIT.NextTime, CUSTINFO.AppTime)
FROM              VISIT
  LEFT OUTER JOIN CUSTINFO
  ON              CUSTINFO.CustInfono = VISIT.CustInfono

Please excuse if my fatigue inhibits this code ;p

Upvotes: 0

Macy Abbey
Macy Abbey

Reputation: 3887

SELECT
     DATEDIFF(NextTime, EnterTime) as NoCustomerInfoDiffInDays,
     DATEDIFF(NextTime, EnterTime) as EnterAfterApptDiffInDays,
     DATEDIFF(NextTime, ApptTime) as EnterBeforeApptDiffInDays
FROM
     VISIT as v
LEFT OUTER JOIN
     CUSTINFO as ci
ON 
     v.CustInfono = ci.CustInfono

In your C#, decide which of the three columns to use as your result for each of the records based on the conditions you have above.

Upvotes: 0

Related Questions