Help wit a complicated query

21 Jul 2006 4:27 PM

BK-Chicago

I have a dataaset like the one below:

Qualifier    Date    Whole/Partial    Partial Count
A    1/12/2000    W    NULL
A    1/3/2001    P    1
A    1/6/2001    P    2
A    1/9/2001    P    3
A    1/12/2001    P    4
B    1/4/2001    W    NULL
B    1/7/2001    P    1
B    1/10/2001    P    2
B    1/1/2002    P    3
B    1/1/2006    P    4
B    1/4/2002    P    4

There are more than a million rows of data in the database

What would be the most efficient SQL server query to extract for each
qualifier the
the latest 4 Partial dates given a reference date (like say getDate() or a
date)

Regards

21 Jul 2006 4:49 PM

Arnie Rowland

By "latest 4 Partial dates', do you mean that you want to retrieve the row
that have dates closest to, but 'before' the 'reference date'? How to handle
rows that have the same dates -for example, in your data below 'A' only has
4 dates, and 'B' only has 4 unique dates.

It would be helpful if you could send the table DDL, sample data in the form
of INSERT statements, and a 'simulated' presentation of your desired
results.

--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"BK-Chicago" <BK-Chic***@discussions.microsoft.com> wrote in message
news:F5768CA5-4D74-4D30-9C2D-8011B4DFDD36@microsoft.com...
>I have a dataaset like the one below:
>
> Qualifier Date Whole/Partial Partial Count
> A 1/12/2000 W NULL
> A 1/3/2001 P 1
> A 1/6/2001 P 2
> A 1/9/2001 P 3
> A 1/12/2001 P 4
> B 1/4/2001 W NULL
> B 1/7/2001 P 1
> B 1/10/2001 P 2
> B 1/1/2002 P 3
> B 1/1/2006 P 4
> B 1/4/2002 P 4
>
>
> There are more than a million rows of data in the database
>
> What would be the most efficient SQL server query to extract for each
> qualifier the
> the latest 4 Partial dates given a reference date (like say getDate() or
> a
> date)
>
> Regards

21 Jul 2006 8:23 PM

BK-Chicago

Thanks for looking into it. Here are more details.

Yes I would like to retrieve the rows that have dates closest to the
reference date. Eg: if the reference date is T0 i would like to get the 4
dates that are immediately before T0 such that T1>T2>T3>T4>T0.

In fact the actual problem is to retrieve the dates for 4 consequetive
quarters(partial = P in this case) preceeding the reference date. So in
addition to identifying the 4 dates above, i require that the date difference
between the min dates and the max dates to be somewhere around 365 days(+/-
45 days)

Any help would be appreciated.

Show quote

"Arnie Rowland" wrote:

> By "latest 4 Partial dates', do you mean that you want to retrieve the row
> that have dates closest to, but 'before' the 'reference date'? How to handle
> rows that have the same dates -for example, in your data below 'A' only has
> 4 dates, and 'B' only has 4 unique dates.
>
> It would be helpful if you could send the table DDL, sample data in the form
> of INSERT statements, and a 'simulated' presentation of your desired
> results.
>
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "BK-Chicago" <BK-Chic***@discussions.microsoft.com> wrote in message
> news:F5768CA5-4D74-4D30-9C2D-8011B4DFDD36@microsoft.com...
> >I have a dataaset like the one below:
> >
> > Qualifier Date Whole/Partial Partial Count
> > A 1/12/2000 W NULL
> > A 1/3/2001 P 1
> > A 1/6/2001 P 2
> > A 1/9/2001 P 3
> > A 1/12/2001 P 4
> > B 1/4/2001 W NULL
> > B 1/7/2001 P 1
> > B 1/10/2001 P 2
> > B 1/1/2002 P 3
> > B 1/1/2006 P 4
> > B 1/4/2002 P 4
> >
> >
> > There are more than a million rows of data in the database
> >
> > What would be the most efficient SQL server query to extract for each
> > qualifier the
> > the latest 4 Partial dates given a reference date (like say getDate() or
> > a
> > date)
> >
> > Regards
>
>
>

22 Jul 2006 3:48 AM

--CELKO--

>> I have a data set [table?] like the one below: <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Why do you have a running count in a table? Also, learn the basics of
the trade like ISO-8601 data formats. Did you mean something more like
this, if you knew DDL?

CREATE TABLE Foobar
(foobar_qualifier CHAR(1) N0T NULL,
posting_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
wp_flag CHAR(1) DEFAULT 'W' NOT NULL
CHECK (foobar_qualifier IN ('W', 'P')),
PRIMARY KEY (foobar_qualifier, posting_date)); -- wild guess?

>> What would be the most efficient SQL server query to extract for each qualifier the the latest 4 Partial dates given a reference date (like say CURRENT_TIMESTAMP or a

date) <<

SELECT foobar_qualifier, posting_date,
ROW_NUMBER() OVER (PARTITION BY foobar_qualifier ORDER BY
posting_date)
AS rn
FROM Foobar
WHERE rn <= 4
AND wp_flag = 'P'
AND posting_date >= @my_date;

Untested, SQL 2005.