Re: TRICKY SQL

From: MS User (sqlman_at_sql.com)
Date: 01/12/05 

Date: Wed, 12 Jan 2005 17:25:59 -0600

Thanks Hugo and everyone who helped me in solving this issue.

Joe

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:i02bu0p03djcv83pe5prvn6lce56k5iplq@4ax.com...
> 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
    ... Thanks Hugo and everyone who helped me in solving this issue. ... > Hi Joe, ... > If you had this table, solving your query would be simple. ...
    (microsoft.public.sqlserver.server)
  • Re: Query for accounts that will expire
    ... If I may -- go ahead and post one Joe, ... Dean Wells [MVP / Directory Services] ... doing this query in .NET is particularly easy too. ... Please post back if 1) you're interested in the script and 2) you ...
    (microsoft.public.windows.server.active_directory)
  • Re: Nearest Common Ancestor Report (XDb1s $1000 Challenge)
    ... Hugo Kornelis wrote: ... >>And then for a 10 million record database. ... >>this query would run, please. ... I don't have the money to spare on the kind of hardware that would be ...
    (comp.object)
  • Re: Password problem
    ... Log on as joe; ... question - who is (joe is the current owner of all tables, ... Double click the relevant query - what error occurs? ... See if the user from step 3., has full permissions to ...
    (microsoft.public.access.formscoding)
  • RE: Cant get data entry with a form based on a query
    ... If you run query 1, not the form, can you make changes in the fields? ... "Joe" wrote: ... > Then I used the wizard to create forms... ... >> First, if you have only one table, it really isn't a database, it is more ...
    (microsoft.public.access.forms)