Re: Help With Complicated SP (for me any way)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Joe Celko (jcelko212_at_earthlink.net)
Date: 07/08/04 

Date: Thu, 08 Jul 2004 10:05:04 -0700

>> I have a table of resource bookings - with a customer_id, and a
start_date and end_date fields [sic]. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Is this what you meant to say?

CREATE TABLE ResourcesBooked
(customer_id INTEGER NOT NULL,
 start_date DATETIME NOT NULL,
 end_date DATETIME NOT NULL,
 CHECK(start_date, end_date),
 PRIMARY KEY (customer_id, start_date));

Rows are not records; fields are columns; tables are not files. Until
you can think in RDBMS terms, all you will do is write file system code
that loop thru things one at a time in SQL.

>> Given a date range entered by the user, I want to check if the
customer_id has an entry in the tblResourceBooked [sic] table on each of
the dates between the range. <<

Read ISO-11179 so you will stop using those silly "-" prefixes; in a
data model, a data element is named for what it means and NEVER for how
is it physically stored.

>> If not, I would like it to add on for the specific customer_id and
current date being checked from the loop: <<

Loop? SQL is a declarative, non-procedural language. We don't use
loops. The first thing you need is a Calendar table; this is a table
that holds all the temporal data for the enterprise in one place. It is
just about the only way to assure that reports and procedures are in
synch.

CREATE PROCEDURE Bookings
(@my_customer_id INTEGER,
 @my_start_date DATETIME,
 @my_end_date DATETIME)
AS
INSERT INTO ResourceBooked (customer_id, start_date, end_date)
SELECT DISTINCT @my_customer_id, @my_start_date, @my_end_date
  FROM ResourceBooked AS R1
 WHERE NOT EXISTS
       (SELECT *
          FROM Calendar AS C1
         WHERE R1.customer_id = @my_customer_id
           AND C1.cal_date BETWEEN @my_start_date AND @my_end_date
           AND C1.cal_date BETWEEN R1.start_date AND R1.end_date);

This is tricky. I go to the calendar table, look at a date, and ask
does it fall into both the range I passed as a parameter and also into
the range of an existing booking for that customer? That is a "double
booked" situation. In practice, you ought to return a failure code of
some kind so the application can call up the existing bookings. If no
such date exists, then insert the booking.

Use of a SELECT statement this way is due to some problems in T-SQL; if
we had Standard SQL, this could be done in other ways. The SELECT
DISTINCT is to filter out duplicate rows that would be generated by each
day in the parameter range.

Want to try to write a proc to merge two overlapping date ranges next?

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: find sequence of numbers
    ... Please post DDL, so that people do not have to guess what the keys, ... constraints, Declarative Referential Integrity, datatypes, etc. in your ... order duplicate items on different lines and not know it. ...
    (microsoft.public.sqlserver.programming)
  • Re: Variation on prisoners dilemma
    ... jail cells in a jailhouse in some small town in Kansas. ... locked up thieves plead to be released so they to can take shelter. ... suddenly come to learn that the jailer tossed the wrong set of keys to ... the keys to diametrically opposite parts of the loop. ...
    (rec.puzzles)
  • Re: sorting and resorting hash
    ... There's no reason to be adding values to an array if you're only using ... Just build the hash directly. ... of the entire %rms hash each time through the loop. ... Why are you creating a whole new array to just store the keys of the hash ...
    (comp.lang.perl.misc)
  • dictionary count is greater than number of keys?
    ... looping over an array made from its keys later. ... However, if I loop to ... Dim ws As Worksheet ...
    (microsoft.public.excel.programming)
  • Re: How to build a cursor with last years numbers
    ... Please post DDL, so that people do not have to guess what the keys, ... constraints, Declarative Referential Integrity, datatypes, etc. in your ... Sample data is also a good idea, ... FROM Foobar AS F1, Foobar AS F2 ...
    (microsoft.public.sqlserver.programming)