CODEWITHSUNDEEP
sqlt-sqllinq-to-sqlstored-procedures
alexm
alexm

Reputation: 530

T-SQL Custom Sort/OrderBy on a Calculated Value as a Stored Procedure or Table Function

I have the following data structure.

Tables:

CalendarEvents

CalendarEventRepetition

All datetimeoffset values are UTC values in relation to DateTime.MIN and are used to calculate time values only.

I need to be able to return upcoming CalendarEvents sorted by their start time from TODAY. This requires a calculation of ORDER BY parameter. I've never done custom t-sql sorting any inputs would be greatly appreciated.

In the domain logic I was doing the following to calculate the SoonestOccurrence for weekly events:

DateTime startTime = System.TimeZoneInfo.ConvertTimeBySystemTimeZoneId(e.FromDate.LocalDateTime, TimeZone);

foreach(CalendarEvent e in events)
{
            DateTime endTime = System.TimeZoneInfo.ConvertTimeBySystemTimeZoneId(e.ToDate.LocalDateTime, TimeZone);

            DateTime now = System.TimeZoneInfo.ConvertTimeBySystemTimeZoneId(DateTime.Now, TimeZone);

            //clear time component 
            now = new DateTime(now.Year, now.Month, now.Day);

            DayOfWeek day = (DayOfWeek)Enum.Parse(typeof(DayOfWeek), b.Day.SelectedValue);

            int nextWeekDaysAdjustment = (int) day - (int)now.DayOfWeek;

            if (nextWeekDaysAdjustment < 0)
            {
                nextWeekDaysAdjustment+=7;
            }

            DateTime adjustedStartTime = startTime.AddDays(nextWeekDaysAdjustment);
            adjustedStartTime = now.AddTicks(adjustedStartTime.Ticks);

            //SoonestOccurrence is a field added in a partical class for CalendarEvent entity
            e.SoonestOccurrence = adjustedStartTime;

}

The problem is that I can't do this sorting in memory because I need to be able to page this query and page it joined by another data set returned by NearestAccounts() function so I'm stuck.

Thank you for your help in advance.

Update: I'll need to pass in Timezone ID or TimeZone Offset to adjust GetDate() for calculcations as well as all starttimes are stored in UTC.

Upvotes: 2

Views: 706

Answers (3)

alexm
alexm

Reputation: 530

It was hard to write in the comment box, so here it goes in an answer box:

Essentially I had to create two scalar functions: NextCalendarEventOccurenceDate(@EventID) and a scalar function AddDateTimeOffsetMinutes(@ToDate,@MinutesDate)

and use them in combination to figure out the nearest start date of the event.

Then I was able to select eventid with their corresponding nearest start time and sort it by nearest start time.

DECLARE @LocationEvents TABLE (accountid INT, eventid INT,starttime DATETIMEOFFSET)
INSERT INTO @LocationEvents
SELECT ce.AccountID, ce.CalendarEventID, dbo.NextCalendarEventOccurenceDate(ce.CalendarEventID)
FROM CalendarEvents ce
INNER JOIN dbo.NearestAccounts(@lat,@long,@miles) la
ON ce.AccountID = la.accountid

then I just return my @LocationEvents ORDER'ed BY startdate

SELECT accountid, eventid, starttime FROM @LocationEvents ORDER BY starttime

Thank you for your help.

Thank you, @Rob and @Thx for pointing me in the right direction.

Upvotes: 0

rob
rob

Reputation: 210

I'd suggest you start by creating a scalar user defined function that takes your from/to offsets and repetition flags and returns the date of the next repetition.

Once you can calculate that value, the order by will be a lot simpler:

SELECT
  CalendarEventID,
  dbo.GetLatestDate(
    FromDate, ToDate, Monday, Tuesday,
    Wednesday, Thursday, Friday, Saturday, Sunday, 
    EveryNWeeks, EndAfterNOccurences) AS LatestDate
FROM CalendarEvents
ORDER BY 2

If you're using SQL 2005 or later and you're more comfortable in c#, then you could even go for a CLR scalar function rather than TSQL.

Upvotes: 1

TrevDev
TrevDev

Reputation: 1106

Not really clear on what you are looking for.

Here is a quick snippet for paging upcoming calendar events and you can join your nearest accounts table.

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 1;
SET @PageSize = 2;

DECLARE @CalendarEvent TABLE ( calendarid INT, starttime DATETIME)

INSERT INTO @CalendarEvent
SELECT 1, Getdate()+1
UNION ALL
SELECT 2, Getdate()+2
UNION ALL
SELECT 3, Getdate()+3
UNION ALL
SELECT 4, Getdate()+4
UNION ALL
SELECT 5, Getdate()+5
UNION ALL
SELECT 6, Getdate()+6

;WITH orderedevents AS
(
    SELECT Row_number() OVER(ORDER BY starttime) AS rownum,
          calendarid
      FROM @CalendarEvent
      WHERE starttime > Getdate()
)

SELECT ce.calendarid, starttime
FROM orderedevents oe
INNER JOIN @CalendarEvent ce
ON oe.calendarid = ce.calendarid
WHERE rownum BETWEEN (@PageNum - 1) * @PageSize + 1
             AND @PageNum * @PageSize
             ORDER BY rownum

Upvotes: 0

Related Questions