December 16, 2008 at 4:05 am
As you can see, it is very easy to change the @SlotSize value to either "dive into" or "pull out of" the calendar.
If you want 15 minute slots, just set @SlotSize to 15.
If you want 30 minute slots, just set @SlotSize to 30. And so on...
The code is not very inventive. This is part of a solution I wrote back in 2000/2001 for an insurance broker company in Sweden. Just had to find the code...
N 56°04'39.16"
E 12°55'05.25"
December 16, 2008 at 5:59 am
Hi,
I do encounter 3 drawbacks:
1) What if an appointment lasts more than one day? Of course "appointment" could be anything such as a two-week holiday.
2) Salesmen also have what I call a working-window. For example: only on weekdays between 8am and 5pm. These are usually stored as blocked times on a day-of-week, and not stored as an actual date.
Of course the above could be solved by inserting the data into the calendar table on a per-day basis, but that doesn't feel right.
3) The code counts the number of apponitments on a time block. This is good, because you know when there are 3 appointments, and you have 3 salesmen, that no-one is available. However, if for some reason one salesman has two appointments on one time block (because of some error) it also counts for 2. So, possibly the code would say a time block is unavailable, while actually one salesman is available.
Still investigating what is the best way to go around it....
Thanks,
Ray
December 16, 2008 at 6:09 am
3) Change
SUM(CASE WHEN DATEPART(DAY, s.FromTime) = 17 THEN 1 ELSE 0 END) AS [17],
to
COUNT(DISTINCT CASE WHEN DATEPART(DAY, s.FromTime) = 17 THEN s.empID ELSE NULL END) AS [17],
N 56°04'39.16"
E 12°55'05.25"
December 16, 2008 at 6:56 am
Jeez, you're a real wizard! I'm learning a lot from you.... THANKS!
No 3) was the showstopper, I think to tackle 1) and 2) I must add an agenda table on a per-day basis. This may be the best solution also for other reasons:
In the root agenda table, all appointments, holidays, etc. are stored, and kept so you can look back.
In the agenda-per-day, only relevant data (like time-from and time-to, and unlike the caption 'holiday to France') of "two months starting from today" is stored, thus keeping the table as small as possible.
Thanks again,
Ray
December 16, 2008 at 7:11 am
Try this 🙂
EDIT: Damn editor to remove code parts.... :angry:
See attached file for sample data and working code.
CalendarFromTimeCalendarToTime010203040506
00:00:0001:00:00000001
01:00:0002:00:00000001
02:00:0003:00:00000001
03:00:0004:00:00000001
04:00:0005:00:00000001
05:00:0006:00:00000001
06:00:0007:00:00000001
07:00:0008:00:00000001
08:00:0009:00:00101011
09:00:0010:00:00100011
10:00:0011:00:00200011
11:00:0012:00:00110011
12:00:0013:00:00200011
13:00:0014:00:00200011
14:00:0015:00:00100011
15:00:0016:00:00100011
16:00:0017:00:00110011
17:00:0018:00:00010011
18:00:0019:00:00000011
19:00:0020:00:00000011
20:00:0021:00:00000011
21:00:0022:00:00000011
22:00:0023:00:00000011
23:00:0024:00:00000011
N 56°04'39.16"
E 12°55'05.25"
December 16, 2008 at 7:36 am
R. van Laake (12/16/2008)
I agree that data access is most important... you mention IO's, I am not familiar how to tweak that... do you maybe have a link that discusses this in depth?
That's a really big subject and not something that can normally be "tweaked" per se (with the possible exception of query hints, which should be a last resort). On this site alone there are dozens maybe even a hundred articles on various aspects of IO performance.
If you want something in-depth, then I would suggest Kalen Delaneys book on the subject: http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=sr_1_2?ie=UTF8&s=books&qid=1229437502&sr=1-2.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 16, 2008 at 8:42 am
Hi Barry,
I will look into your link asap.... sounds like something I really need to know more about.
Hi Peso,
I bow to thee...... I just came out of a meeting and have to run to fetch the kids, but I did run your piece of code and it works! Employee 5's appointment is written over the three days.
I already "translated" your previous code to my situation, if not tonight I will start tomorrow on this new piece of geniality!
Another 10^6 thanks,
Raymond
December 16, 2008 at 10:54 am
would like to comment on one reply:
A bigint gives you 16 hours a day to schedule appointments for your salesmen. If that is not enough time, then your company has a serious problem and it's not related to IT.
Quite often I read remarks/opinions like this on forums. I fail to see the usefulness. I always avoid explaining the (complicated) real world issue and simplify the situation drastically, in order to keep the IT question as simple as possible
I have seen cases where the person asking for help about something jumped into a "solution" which was not warranted at all because something fundamental had escaped the person's attention and given more information on the issue at hand, a superior solution was offered.
There have been cases where "opinions like this on the forum" were entirely legitimate and was an honest effort to understand the issue before jumping to solutions.
If you ask for help, keeping "the IT question to a minimum" might result in someone asking WHY. I see nothing wrong with this.
I am not going to comment on whether or not you are right. I was occasionally shown I was wrong and took it in stride, building up the experience. Other times I was right and just moved on.
December 18, 2008 at 8:51 am
Hi Peso,
Well finally it's done! I can't thank you enough, not just because you helped me finish something
I needed, but also because it gave me insight to a new way of programming.
I pasted the code below in case you like to see it.
Some remarks:
1)
I added the "blocked" times (like: never an appointment on Mondays from 00:00 to 07:00)
as appointments, using a routine I made, based on your code.
2)
I changed the line to:
CASE WHEN @codelogic_agenda_daysahead>=01 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,01,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [01],
Obviously any agenda will have more than 1 day ahead to look at.
The system maximum is set to 62, so when you only need a three week agenda, 40 'counts' are skipped.
Again, thank you very much.
Raymond
[font="Courier New"]
SET DATEFORMAT dmy
--maximum number of days ahead
--if you change this value be sure it matches the COUNT(DISTINCT CASE WHEN DATEADD... lines (below)
DECLARE @codelogic_agenda_systemdaysaheadTINYINT
SET @codelogic_agenda_systemdaysahead = 62
--fetch data about campagne
DECLARE @codelogic_agenda_resolutionINT
DECLARE @codelogic_agenda_daysaheadTINYINT
SELECT@codelogic_agenda_resolution= ca.codelogic_agenda_resolution,
@codelogic_agenda_daysahead= ca.codelogic_agenda_daysahead
FROM campagnes_projecten cp WITH (NOLOCK)
INNER JOIN campagnes ca WITH (NOLOCK) ON ca.pid = cp.campagnepid
WHERE cp.pid=@campagneprojectpid
--print @codelogic_agenda_resolution print @codelogic_agenda_daysahead return
--make sure things don't go crazy: check @codelogic_agenda_resolution, if necessary set to 15 minutes
IF @codelogic_agenda_resolution NOT IN (5,15,30,60,90,120,150,180,210,240,270,300,330,360,390,420,450,480)
SET @codelogic_agenda_resolution = 15
--maximize @codelogic_agenda_daysahead just in case
IF @codelogic_agenda_daysahead > @codelogic_agenda_systemdaysahead
SET @codelogic_agenda_daysahead = @codelogic_agenda_systemdaysahead
--set date range (just in case in the furure these should become parameters)
DECLARE @agendaVan DATETIME
DECLARE @agendaTot DATETIME
SET @agendaVan = DATEADD(d,DATEDIFF(d,0,GETDATE()),0)
SET @agendaTot = DATEADD(d,@codelogic_agenda_systemdaysahead,@agendaVan)
--create table agenda
DECLARE@agenda TABLE
(
relatie_agendapersoonpidINT,
dtvanDATETIME,
dttotDATETIME
)
--add appointments to table agenda
INSERT @agenda
(relatie_agendapersoonpid,dtvan,dttot)
SELECT ag.relatie_agendapersoonpid,ag.dtvan,ag.dttot
FROM agenda ag WITH (NOLOCK)
WHEREDATEDIFF(d,@agendaVan,ag.dttot) >=0
ANDDATEDIFF(d,@agendaTot,ag.dtvan) <=0
AND ag.relatie_agendapersoonpid IN
(
SELECT cpa.relatie_agendapersoonpid
FROM campagnes_projecten_agendapersonen cpa WITH (NOLOCK)
INNER JOIN relaties_agendapersonen ra ON ra.pid=cpa.relatie_agendapersoonpid
WHEREcpa.campagneprojectpid=@campagneprojectpid
ANDra.aktief=1
)
--add blocks to table agenda, as if they are appointments
INSERT @agenda
(relatie_agendapersoonpid,dtvan,dttot)
SELECT DISTINCT
avb.relatie_agendapersoonpid,
CONVERT(DATETIME,CONVERT(CHAR,DATEADD(d,DATEDIFF(d,0,DATEADD(d,v.number,GETDATE())),0),105)+' '+avb.tvan),
CONVERT(DATETIME,CONVERT(CHAR,DATEADD(d,DATEDIFF(d,0,DATEADD(d,v.number,GETDATE())),0),105)+' '+avb.ttot)
FROM master..spt_values v
LEFT OUTER JOIN agenda_vasteblokkades avb ON avb.dow = DATEPART(dw,DATEADD(d,v.number,GETDATE()))
WHEREv.type='P'
AND v.number>=0 AND v.number<=62
AND avb.relatie_agendapersoonpid IS NOT NULL
AND avb.relatie_agendapersoonpid IN
(
SELECT cpa.relatie_agendapersoonpid
FROM campagnes_projecten_agendapersonen cpa WITH (NOLOCK)
INNER JOIN relaties_agendapersonen ra ON ra.pid=cpa.relatie_agendapersoonpid
WHEREcpa.campagneprojectpid=@campagneprojectpid
ANDra.aktief=1
)
SELECTLEFT(t.CalendarFromTime,5) AS blokVan,
LEFT(t.CalendarToTime,5) AS blokTot,
COUNT(DISTINCT CASE WHEN DATEADD(d,00,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) AS [00],
CASE WHEN @codelogic_agenda_daysahead>=01 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,01,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [01],
CASE WHEN @codelogic_agenda_daysahead>=02 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,02,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [02],
CASE WHEN @codelogic_agenda_daysahead>=03 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,03,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [03],
CASE WHEN @codelogic_agenda_daysahead>=04 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,04,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [04],
CASE WHEN @codelogic_agenda_daysahead>=05 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,05,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [05],
CASE WHEN @codelogic_agenda_daysahead>=06 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,06,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [06],
CASE WHEN @codelogic_agenda_daysahead>=07 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,07,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [07],
CASE WHEN @codelogic_agenda_daysahead>=08 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,08,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [08],
CASE WHEN @codelogic_agenda_daysahead>=09 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,09,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [09],
CASE WHEN @codelogic_agenda_daysahead>=10 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,10,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [10],
CASE WHEN @codelogic_agenda_daysahead>=11 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,11,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [11],
CASE WHEN @codelogic_agenda_daysahead>=12 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,12,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [12],
CASE WHEN @codelogic_agenda_daysahead>=13 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,13,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [13],
CASE WHEN @codelogic_agenda_daysahead>=14 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,14,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [14],
CASE WHEN @codelogic_agenda_daysahead>=15 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,15,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [15],
CASE WHEN @codelogic_agenda_daysahead>=16 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,16,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [16],
CASE WHEN @codelogic_agenda_daysahead>=17 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,17,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [17],
CASE WHEN @codelogic_agenda_daysahead>=18 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,18,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [18],
CASE WHEN @codelogic_agenda_daysahead>=19 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,19,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [19],
CASE WHEN @codelogic_agenda_daysahead>=20 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,20,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [20],
CASE WHEN @codelogic_agenda_daysahead>=21 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,21,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [21],
CASE WHEN @codelogic_agenda_daysahead>=22 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,22,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [22],
CASE WHEN @codelogic_agenda_daysahead>=23 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,23,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [23],
CASE WHEN @codelogic_agenda_daysahead>=24 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,24,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [24],
CASE WHEN @codelogic_agenda_daysahead>=25 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,25,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [25],
CASE WHEN @codelogic_agenda_daysahead>=26 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,26,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [26],
CASE WHEN @codelogic_agenda_daysahead>=27 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,27,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [27],
CASE WHEN @codelogic_agenda_daysahead>=28 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,28,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [28],
CASE WHEN @codelogic_agenda_daysahead>=29 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,29,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [29],
CASE WHEN @codelogic_agenda_daysahead>=30 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,30,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [30],
CASE WHEN @codelogic_agenda_daysahead>=31 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,31,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [31],
CASE WHEN @codelogic_agenda_daysahead>=32 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,32,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [32],
CASE WHEN @codelogic_agenda_daysahead>=33 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,33,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [33],
CASE WHEN @codelogic_agenda_daysahead>=34 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,34,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [34],
CASE WHEN @codelogic_agenda_daysahead>=35 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,35,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [35],
CASE WHEN @codelogic_agenda_daysahead>=36 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,36,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [36],
CASE WHEN @codelogic_agenda_daysahead>=37 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,37,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [37],
CASE WHEN @codelogic_agenda_daysahead>=38 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,38,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [38],
CASE WHEN @codelogic_agenda_daysahead>=39 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,39,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [39],
CASE WHEN @codelogic_agenda_daysahead>=40 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,40,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [40],
CASE WHEN @codelogic_agenda_daysahead>=41 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,41,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [41],
CASE WHEN @codelogic_agenda_daysahead>=42 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,42,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [42],
CASE WHEN @codelogic_agenda_daysahead>=43 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,43,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [43],
CASE WHEN @codelogic_agenda_daysahead>=44 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,44,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [44],
CASE WHEN @codelogic_agenda_daysahead>=45 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,45,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [45],
CASE WHEN @codelogic_agenda_daysahead>=46 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,46,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [46],
CASE WHEN @codelogic_agenda_daysahead>=47 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,47,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [47],
CASE WHEN @codelogic_agenda_daysahead>=48 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,48,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [48],
CASE WHEN @codelogic_agenda_daysahead>=49 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,49,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [49],
CASE WHEN @codelogic_agenda_daysahead>=50 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,50,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [50],
CASE WHEN @codelogic_agenda_daysahead>=51 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,51,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [51],
CASE WHEN @codelogic_agenda_daysahead>=52 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,52,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [52],
CASE WHEN @codelogic_agenda_daysahead>=53 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,53,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [53],
CASE WHEN @codelogic_agenda_daysahead>=54 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,54,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [54],
CASE WHEN @codelogic_agenda_daysahead>=55 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,55,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [55],
CASE WHEN @codelogic_agenda_daysahead>=56 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,56,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [56],
CASE WHEN @codelogic_agenda_daysahead>=57 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,57,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [57],
CASE WHEN @codelogic_agenda_daysahead>=58 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,58,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [58],
CASE WHEN @codelogic_agenda_daysahead>=59 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,59,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [59],
CASE WHEN @codelogic_agenda_daysahead>=60 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,60,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [60],
CASE WHEN @codelogic_agenda_daysahead>=61 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,61,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [61],
CASE WHEN @codelogic_agenda_daysahead>=62 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,62,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [62]
FROM(
SELECTCONVERT(CHAR(8), DATEADD(MINUTE, @codelogic_agenda_resolution * Number, 0), 8) AS CalendarFromTime,
DATEADD(MINUTE, @codelogic_agenda_resolution * Number, 0) AS CalendarStartTime,
CASE
WHEN @codelogic_agenda_resolution * Number + @codelogic_agenda_resolution >= 1440 THEN '24:00:00'
ELSE CONVERT(CHAR(8), DATEADD(MINUTE, @codelogic_agenda_resolution * Number + @codelogic_agenda_resolution, 0), 8)
END AS CalendarToTime,
DATEADD(MINUTE, @codelogic_agenda_resolution * Number + @codelogic_agenda_resolution, 0) AS CalendarEndTime
FROM master..spt_values
WHEREtype = 'P'
AND Number * @codelogic_agenda_resolution < 1440
) AS t
LEFT JOIN@agenda ag ON ag.dtvan >= @agendaVan
AND ag.dtvan < @agendaTot
GROUP BYt.CalendarFromTime,
t.CalendarToTime
ORDER BYt.CalendarFromTime[/font]
December 18, 2008 at 10:53 am
If you are going to have a maximum of 62 blocks, you now can switch back to your bit-hack 😀
N 56°04'39.16"
E 12°55'05.25"
December 18, 2008 at 11:01 am
R. van Laake (12/18/2008)
2)I changed the line to:
CASE WHEN @codelogic_agenda_daysahead>=01 THEN COUNT(DISTINCT CASE WHEN DATEADD(d,01,@agendaVan) + t.CalendarStartTime ag.dtvan THEN ag.relatie_agendapersoonpid END) END AS [01],
Oh, I love to see code names mixed up in languages 😉 And also nice to read something in dutch all of a sudden. This topic was great though, some of those WOW experiences how things are done in SQL Server. 🙂
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
December 18, 2008 at 1:29 pm
Could you please help me understand the query you have written, I am tring to work on 'finding the next available time slot' in a scheduling application and can use your expertise?
December 18, 2008 at 1:55 pm
ak (12/18/2008)
Could you please help me understand the query you have written, I am tring to work on 'finding the next available time slot' in a scheduling application and can use your expertise?
That is even simpler!
Post a new topic and ask away.
Don't be afraid to also post some sample data and table DDL.
If you are really adventurous, you can also post some expected output based on the provided sample data.
The basic idea is to map future caclulated timeslots against an appointment table and get first (oldest) timeslot that doesn't have a related appointment.
N 56°04'39.16"
E 12°55'05.25"
December 18, 2008 at 2:02 pm
December 19, 2008 at 1:58 am
Oh, I love to see code names mixed up in languages And also nice to read something in dutch all of a sudden. This topic was great though, some of those WOW experiences how things are done in SQL Server.
Hehehe, it's something we in The Netherlands call cross-learning. Like schools that use English as the spoken language throughout the day, including classes.
Unfortunately some of tables' fieldnames are in Dutch, so I had to use them. Next time I'll do better, promise.
The code I presented was by the way nothing new, but based on the example Peso gave. I had a true WOW feeling....
in the past quite often I ran into similar problems and I solved it either with cursors, or in the ASP page, simply because I couldn't find another way.
If you are going to have a maximum of 62 blocks, you now can switch back to your bit-hack
:w00t: I believe there's no other way to solve the problem (setting a maximum), other than building the query dynamically using a string, and then executing it.
But isn't it true that this would be less efficient?
Anyway, in the real world my customers will not want to plan appointments furhter away than two months (until now the max is 1 month).
And if one customer would - well, then I can simply set the maximum to for example 93.
Thanks,
Ray
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply