Prev: query on max() - Moby
Next: How can I run big SQL files?
From: GTN170777 on 4 Jul 2008 17:10
Hi all,

I'm trying to run a query that produces data from three tables, in essence
these are Location, Category and BannerPeriod, They all have a secondary key
of SiteID and are linked by this, really there are two stages to this query,

Stage 1 produce a complete combination of all categories and locations where
SiteID = 'param' so for instance for a SiteID of 1 there are lets say 3
locations - Hereford, Manchester, Leatherhead and two categories -
Accounting, It, this part of the query would produce six records, Hereford
Accounting, Hereford IT, Manchester Accounting etc.

Stage 2 is where it gets a little complicated, I then have a table called
BannerPeriod, which has the following structure -
BannerID, SiteID, BannerCategory, BannerLocation, BannerCategory,
BannerStartDate, BannerEndDate (There are other columns but these are the
only important ones)

What I'm trying to do, is firstly I pass two parameters to the database,
StartDate and EndDate, based on these parameters the recordset produces a
combined list of all categories and locations for the SiteID, where the
category and location isn't listed in the BannerPeriod table with
BannerStartDate Between Startdate and Enddate of a BannerEndDate between
Startdate and Endate.

I'm really confused about this, so would be really grateful for your input..

thank you

From: Erland Sommarskog on 4 Jul 2008 17:59
GTN170777 (GTN170777(a)discussions.microsoft.com) writes:
> I'm trying to run a query that produces data from three tables, in
> essence these are Location, Category and BannerPeriod, They all have a
> secondary key of SiteID and are linked by this, really there are two
> stages to this query,
>
> Stage 1 produce a complete combination of all categories and locations
> where SiteID = 'param' so for instance for a SiteID of 1 there are lets
> say 3 locations - Hereford, Manchester, Leatherhead and two categories -
> Accounting, It, this part of the query would produce six records,
> Hereford Accounting, Hereford IT, Manchester Accounting etc.
>
> Stage 2 is where it gets a little complicated, I then have a table called
> BannerPeriod, which has the following structure -
> BannerID, SiteID, BannerCategory, BannerLocation, BannerCategory,
> BannerStartDate, BannerEndDate (There are other columns but these are the
> only important ones)
>
> What I'm trying to do, is firstly I pass two parameters to the database,
> StartDate and EndDate, based on these parameters the recordset produces a
> combined list of all categories and locations for the SiteID, where the
> category and location isn't listed in the BannerPeriod table with
> BannerStartDate Between Startdate and Enddate of a BannerEndDate between
> Startdate and Endate.
>
> I'm really confused about this, so would be really grateful for your
> input..

If I understand this right:

WITH combos AS (
SELECT L.Location, C.Category
FROM Locations L
JOIN Categories C ON L.SiteID = C.SiteID
WHERE L.SiteID = @siteid
AND C.SiteID = @siteid
)
SELECT c.Location, c.Category
FROM combos c
WHERE NOT EXISTS (SELECT *
FROM BannerPeriod BP
WHERE BP.BannerLocation = c.Location
AND BP.BannerCategory = c.Category
AND BP.BannerStartDate BETWEEN @start AND @end
AND BP.BannerEndDate BETWEEN @start AND @end)

Chances are slim that this actually gives you the result you actually
want, since it's based on some guessworks, and an assumption of which
version of SQL Server you are using.

If this query does not meet your needs, I suggest that you post:

o CREATE TABLE statements for your tables. Don't forget to include the
key definition.
o INSERT statements with sample data.
o The desired result of the sample.
o Which version of SQL Server you are using.

The condition on BannerStartDate and BannerEndDate is per your description,
but it does not seem right. The dates could com in this order:
BannerStartDate, @start, @end, BannerEndDate. Should really a
location/category combo that was active the entire period be listed?
I think you need

AND BP.BannerStartDate <= @end
AND BP.BannerEndDate => @start


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Hugo Kornelis on 5 Jul 2008 16:35
On Fri, 4 Jul 2008 14:10:01 -0700, GTN170777 wrote:

>Hi all,
(snip)
>thank you

Hi GTN170777,

See my reply in .mseq. And please do not multipost.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
From: GTN170777 on 7 Jul 2008 09:15
Thanks Erland

"Erland Sommarskog" wrote:

> GTN170777 (GTN170777(a)discussions.microsoft.com) writes:
> > I'm trying to run a query that produces data from three tables, in
> > essence these are Location, Category and BannerPeriod, They all have a
> > secondary key of SiteID and are linked by this, really there are two
> > stages to this query,
> >
> > Stage 1 produce a complete combination of all categories and locations
> > where SiteID = 'param' so for instance for a SiteID of 1 there are lets
> > say 3 locations - Hereford, Manchester, Leatherhead and two categories -
> > Accounting, It, this part of the query would produce six records,
> > Hereford Accounting, Hereford IT, Manchester Accounting etc.
> >
> > Stage 2 is where it gets a little complicated, I then have a table called
> > BannerPeriod, which has the following structure -
> > BannerID, SiteID, BannerCategory, BannerLocation, BannerCategory,
> > BannerStartDate, BannerEndDate (There are other columns but these are the
> > only important ones)
> >
> > What I'm trying to do, is firstly I pass two parameters to the database,
> > StartDate and EndDate, based on these parameters the recordset produces a
> > combined list of all categories and locations for the SiteID, where the
> > category and location isn't listed in the BannerPeriod table with
> > BannerStartDate Between Startdate and Enddate of a BannerEndDate between
> > Startdate and Endate.
> >
> > I'm really confused about this, so would be really grateful for your
> > input..
>
> If I understand this right:
>
> WITH combos AS (
> SELECT L.Location, C.Category
> FROM Locations L
> JOIN Categories C ON L.SiteID = C.SiteID
> WHERE L.SiteID = @siteid
> AND C.SiteID = @siteid
> )
> SELECT c.Location, c.Category
> FROM combos c
> WHERE NOT EXISTS (SELECT *
> FROM BannerPeriod BP
> WHERE BP.BannerLocation = c.Location
> AND BP.BannerCategory = c.Category
> AND BP.BannerStartDate BETWEEN @start AND @end
> AND BP.BannerEndDate BETWEEN @start AND @end)
>
> Chances are slim that this actually gives you the result you actually
> want, since it's based on some guessworks, and an assumption of which
> version of SQL Server you are using.
>
> If this query does not meet your needs, I suggest that you post:
>
> o CREATE TABLE statements for your tables. Don't forget to include the
> key definition.
> o INSERT statements with sample data.
> o The desired result of the sample.
> o Which version of SQL Server you are using.
>
> The condition on BannerStartDate and BannerEndDate is per your description,
> but it does not seem right. The dates could com in this order:
> BannerStartDate, @start, @end, BannerEndDate. Should really a
> location/category combo that was active the entire period be listed?
> I think you need
>
> AND BP.BannerStartDate <= @end
> AND BP.BannerEndDate => @start
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
 | 
Pages: 1
Prev: query on max() - Moby
Next: How can I run big SQL files?