CODEWITHSUNDEEP
c#sqlsql-server
Jonas Johansson
Jonas Johansson

Reputation: 67

SQL instead of multiple loops

Is there a way to write SQL-code instead of looping through the data for

finding the difference between eDate in an Post compared to the following post's sDate. If the hours between them occur during night time (22:00-06:00) the Time that occur during night hours should be printed.

I have done this at the moment by looping the data in the table but with more functionality there are more and more loops in my current code.

ID  UserID      sDate           eDate
1   1       2017-01-01 01:00    2017-01-01 02:00    
2   1       2017-01-01 03:00    2017-01-01 06:00

for exampel:

nightStart = "2016-12-31 22:00";
nightEnd = "2016-01-01 06:00";

ID=1 

eDate = "2017-01-01 02:00";

ID=2

sDate = "2017-01-01 03:00";

Should find the diffrence between eDate and sDate to be:

01:00

UPDATE

Using the SQL-code example provided, then looping the results against the calcNightHoursInRange Method

SQL:

SELECT  
    THIS.RegTimeID,THIS.UserID,peDate=MAX(PREV.eDate),tseDate=THIS.sDate,diff=DATEDIFF(HOUR,MAX(PREV.eDate),THIS.sDate)
FROM
    tblRegTime THIS
    LEFT OUTER JOIN
    (
        SELECT  RegTimeID,UserID,eDate
        FROM
        tblRegTime
    )PREV ON THIS.UserID=PREV.UserID AND THIS.sDate>PREV.eDate
GROUP BY
    THIS.RegTimeID,THIS.UserID,THIS.sDate,THIS.eDate

- Method:

    public TimeSpan calcNightHoursInRange(DateTime sDate, DateTime eDate, DateTime RuleStartTime, DateTime RuleEndTime)
    {
        //Build RuleStartDateTime & RuleEndDateTime
        DateTime baseDate = DateTime.Parse(sDate.ToShortDateString());
        DateTime RuleStartDateTime = new DateTime(baseDate.Year, baseDate.Month, baseDate.Day, RuleStartTime.Hour, RuleStartTime.Minute, 0);
        DateTime RuleEndDateTime = new DateTime(baseDate.Year, baseDate.Month, baseDate.Day, RuleEndTime.Hour, RuleEndTime.Minute, 0);

        if (RuleStartDateTime > RuleEndDateTime)
        {
            RuleEndDateTime = RuleEndDateTime.AddDays(1);
        }

        TimeSpan MatchTime = new TimeSpan();
        if (sDate <= RuleStartDateTime && eDate <= RuleEndDateTime)
        {
            MatchTime = (eDate - RuleStartDateTime);
        }
        if (sDate >= RuleStartDateTime && eDate <= RuleEndDateTime)
        {
            MatchTime = (eDate - sDate);
        }
        if (sDate <= RuleStartDateTime && eDate >= RuleEndDateTime)
        {
            MatchTime = (RuleEndDateTime - RuleStartDateTime);
        }
        if (sDate > RuleStartDateTime && eDate > RuleEndDateTime)
        {
            MatchTime = (RuleEndDateTime - sDate);
        }
        return MatchTime;
    }

Upvotes: 0

Views: 99

Answers (3)

Riv
Riv

Reputation: 1859

SELECT DATEDIFF(HOUR, sDate 
(
    SELECT eDate FROM TABLENAME t2 WHERE t2.ID = (t1.ID + 1))
) DIFFHOURS
FROM TABLENAME t1

Upvotes: 0

Aaron Dietz
Aaron Dietz

Reputation: 10277

I believe you're looking for DATEDIFF():

SELECT DATEDIFF(hh, '2017-01-01 02:00', '2017-01-01 03:00')

This will simply return 1, the integer difference in hours. You will have to format it to 01:00 if that is needed.

You can use LEAD() or LAG() to access the next row or previous row. Something like this should be what you want:

SELECT DATEDIFF(hh, edate, LEAD(sdate) over (ORDER BY ID))
FROM YourTable

Upvotes: 1

Ross Bush
Ross Bush

Reputation: 15155

This will get you the LAG offset without using LAG, however, using LAG makes it easier.

SELECT  
    THIS.ID,THIS.UserID,THIS.sDate,THIS.eDate,diff=DATEDIFF(HOUR,THIS.sDate,MAX(PREV.eDate))
FROM
    YourTable THIS
    LEFT OUTER JOIN
    (
        SELECT  ID,UserID,eDate
        FROM
        YourTable
    )PREV ON THIS.UserID=PREV.UserID AND THIS.sDate>PREV.eDate
GROUP BY
    THIS.ID,THIS.UserID,THIS.sDate,THIS.eDate

Upvotes: 0

Related Questions