Re: TRICKY SQL

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 01/12/05 

Date: Wed, 12 Jan 2005 21:54:49 +0100

On Wed, 12 Jan 2005 10:14:37 -0600, MS User wrote:

>Sorry...
>I will explain with some sample data, before that a little note
(snip)

Hi Joe,

Unless I am (still) misunderstanding your explanations, the tricky part of
your question is not the query, but the table design.

You're missing an important attribute in your model: a number (or other
identifier) to distinguish between the first, second, etc trip of a given
car. In your model, the only way to find if these two rows:
        CarNum MoveType MoveDate
        1 B 20040110
        1 D 20040203
belong to the same trip, is to use subqueries to find if there is also a
row for car 1 and move type A with a date between 20040110 and 20040203.
This is not efficient. A better table structure would be:

CREATE TABLE ImprovedCarMovements
            (CarNum int NOT NULL,
             MoveNo int NOT NULL,
             MoveType char(1) NOT NULL,
             MoveDate smalldatetime,
             PRIMARY KEY (CarNum, MoveNo, MoveType),
             CHECK (MoveType IN ('A','B','C','D','E'))
            )

If you had this table, solving your query would be simple. But since you
don't have it yet, let's first make a view that holds the same
information:

CREATE VIEW BetterCarMovements
AS
SELECT CarNum,
      (SELECT COUNT(*)
       FROM CarMovements AS b
       WHERE b.CarNum = a.CarNum
       AND b.MoveType = 'A'
       AND b.MoveDate <= a.MoveDate) AS MoveNo,
       MoveType,
       MoveDate
FROM CarMovements AS a

(Note: you can also use this view to quickly convert your current table to
a permanent table with the improved structure - of course, you will have
to change your application accordingly).

Now that you have a view that holds the data in a more usable way, it's
easy to write your query:

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '20041001'
SET @EndDate = '20041005'
SELECT DISTINCT a.CarNum
FROM BetterCarMovements AS a
LEFT JOIN BetterCarMovements AS e
     ON e.CarNum = a.CarNum
     AND e.MoveNo = a.MoveNo
     AND e.MoveType = 'E'
WHERE a.MoveType = 'A'
AND a.MoveDate <= @EndDate
AND COALESCE (e.MoveDate, @StartDate) >= @StartDate

Note: Since you didn't provide CREATE TABLE and INSERT statements to
recreate your test data on my end, I didn't test the above query. See
www.aspfaq.com/5006 for future postings!

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: TRICKY SQL
    ... solving your query would be simple. ... CREATE VIEW BetterCarMovements ... DECLARE @EndDate datetime ... recreate your test data on my end, I didn't test the above query. ...
    (microsoft.public.sqlserver.programming)
  • Re: Query against dates
    ... In my query, I want to query for records that are 'valid' per the dates on ... If the StartDate is left empty and the EndDate is present, ... Dim strSQL As String ...
    (microsoft.public.access.queries)
  • Re: Weekly Date Calculation
    ... Well I tried it both ways and it asks for StartDate and EndDate parameters. ... column on the query. ... and time field) and enter the criteria BETWEEN AND ...
    (microsoft.public.access.forms)
  • Re: SQL query question #2
    ... and having test data makes it easier to test. ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis ... E.g. if I query the above data with required values of 'b' and 'd'. ...
    (comp.databases.ms-sqlserver)
  • Re: Report doesnt show all records between dates
    ... This is the query now: ... The only "extra" thing are the records with empty EndDate but ... - your table contains a StartDate and an EndDate, ... which is the total number of students female and ...
    (microsoft.public.access.reports)