Let's say there is a table called Fashion that has the following
fields:
fashion_id    int,
name          varchar, 
wear_date     date, 
shirt         varchar,
tie           varchar,
fashion_score int 

the table has the following data:

fashion_id  name   wear_date   shirt  tie    fashion_score 
----------- ------ ----------- ------ ------ ------------- 
1           Bill   2002-01-01  blue   red    3
2           Bob    2002-01-04  blue   white  5
3           Bill   2002-01-05  green  red    4
4           Bill   2002-01-07  blue   red    7
5           Bob    2002-01-10  blue   red    2
6           Bill   2002-01-10  green  red    2
7           Bill   2002-01-11  green  red    9
					
I need a definition for a view called v_Fashion_Starters that in a
single query would return records that match the following criteria:
- for a given wear_date interval all distinct name/shirt/tie
combinations are returned along with fashion_id and fashion_score for
the earliest occurrence of that combination within the time period.

Example 1:

  SELECT *
  FROM v_Fashion_Starters
  WHERE wear_date >= '2002-01-04' 
        AND wear_date <= '2002-01-11'

would return

  fashion_id  name   wear_date   shirt  tie    fashion_score 
  ----------- ------ ----------- ------ ------ ------------- 
  2           Bob    2002-01-04  blue   white  5
  3           Bill   2002-01-05  green  red    4
  4           Bill   2002-01-07  blue   red    7
  5           Bob    2002-01-10  blue   red    2


Example 2:

  SELECT *
  FROM v_Fashion_Starters
  WHERE wear_date >= '2002-01-01' 
        AND wear_date <= '2002-01-11' 
        AND name = 'Bill'

would return

  fashion_id  name   wear_date   shirt  tie    fashion_score 
  ----------- ------ ----------- ------ ------ ------------- 
  1           Bill   2002-01-01  blue   red    3
  3           Bill   2002-01-05  green  red    4
  
  
You can use subqueries or any number of intermediate views as
necessary, but no stored procedures.  Queries like in the examples
above must be possible.  I believe this is a pretty generic SQL
question, however, the answer would have to be possible to implement
in SQL SERVER 2000.  Let me know if you want me to clarify anything.

---

Clarification of Question by alekb-ga on 28 Oct 2002 06:26 PST

mathtalk, thanks for your suggestion.  However, for my intents this
approach would not be possible.   The idea is to use Business Objects,
a reporting tool, that does not have such flexibility for an
unsophisticated (GUI only) user.

---

Clarification of Question by alekb-ga on 28 Oct 2002 06:29 PST

cleith thank you.  please see clarification to mathtalk-ga.  However
your approach would be cumbersome in a multi-user system, where
several users try to query with different time periods.

---

Clarification of Question by alekb-ga on 31 Oct 2002 06:59 PST

davedave, looks like you got it!!!

a little modification (below) to the vDates view probably makes it a
little cleaner.  I think you need to post an 'Answer' in order to get
paid and rated.  You'll get 5 stars from me.

drop view vDates
go

create view vDates as 
select wear_date as date from Fashion
go

I think that it is not possible under SQL in any easy way, but
somethink like this should work (not tested - I have the syntax often
wrong without checking it at a server...; and it DEFINITELLY will not
be fast)

create view a select * from sourcetbl;   (naming mess...)

create view v_Fashion_Starters select (select fashion_id from a where
a.name=sourcetbl.name, a.wear_date=sourcetbl.wear_date,
a.shirt=sourcetbl.shirt, a.tie=sourcetbl.tie TOP 1) as fashion_id,
name, wear_date, shirt, tie, (select fashion_score from a where
a.name=sourcetbl.name, a.wear_date=sourcetbl.wear_date,
a.shirt=sourcetbl.shirt, a.tie=sourcetbl.tie TOP 1) as fashion_score
from sourcetbl group by name, wear_date, shirt, tie;

Anyway - do NOT do this. If at all possible, use some averaging
functions like
create view aaaa select min(fashion_id) as fashion_id, name,
wear_date, shirt, tie, min(fashion_score) as fashion_score from
sourcetbl group by name, wear_date, shirt, tie;

Try this:

CREATE VIEW v_Fashion_Starters AS
SELECT fashion_id, name, wear_date, shirt, tie, fashion_score
FROM fashion_score fs
WHERE NOT EXISTS ( select wear_date, shirt, tie
                      from fashion_score fs2
                     where fs2.shirt = fs.shirt
                     and fs2.tie = fs.tie
                     and fs2.wear_date < fs.wear_date )
                       
What this query says is: "Give me all the rows in this table that, for
a given combination of shirt and tie, are the first row in this table
(by wear_date) to contain that combination."   More correctly, it says
"Give me all the rows in this table that, for a given combination of
shirt and tie, there are no other rows that have a wear_date earlier
than this one."

Let me know if this works for you.

This method is fine, except if two people wore the same thing on the
same first day, it will give you both of them.

If you want to make sure there is just one "fashion-starter" per
fashion, you can try some string tricks. I don't know if SQL Server
2000 has a tokenizer string function. If it does, you can do

CREATE VIEW v_Fashion_Starters as
SELECT
shirt, 
tie, 
token(date_name_id_score, '~', 1) as date,
token(date_name_id_score, '~', 2) as name,
token(date_name_id_score, '~', 1) as fashion_id,
token(date_name_id_score, '~', 1) as fashion_score
FROM
(SELECT 
 shirt,
 tie, 
 min(date||'~'||name||'~'||fashion_id||'~'||fashion_score)
   as date_name_id_score
 FROM Fashions
 GROUP BY shirt, tie)

tds, thanks for your comment.  However, I don't think your solution is
completely correct.  According to the view you suggest in Example 1
(original question above) the following row would not be returned:
4           Bill   2002-01-07  blue   red    7 

The problem is not being able to specify the wear_date conditions in
the subquery.

It is not possible to do this with a View.  A View is in principle
just like a table; it consists (virtually) of a definite set of rows,
out of which the SQL SELECT statement can filter records, etc.

You ask that WHERE clause conditions on the View be recognized as
parameters to the underlying CREATE VIEW statement, e.g. that weaker
restrictions on wear_date might exclude a row (because of the
existence of an even earlier record satisfying the weakened
restrictions).

If we ignore the CREATE VIEW part of your problem, then you are left
with the need to produce a parameterized SQL query.  It is possible to
do what you want with a SQL query, rather than with a stored
procedure, but the wear_date_begin and wear_date_end parameters will
need to appear in the constructed query more than once.  I'm not sure
of your intended application, but it is common to construct query
strings for transmittal to SQL Server 2000 "on the fly" using either
ODBC or ADO/OLE DB connections.  [This was for a long time critical
with Oracle connections using ADO, because of a bug/limitation in
returning record sets from stored procedures ("packages") in Oracle.]

Let me know if further details of this would be the basis of a
satisfactory answer for you.

regards, mathtalk-ga

Create View 1 using only the Name, Date, Shirt, and Tie filtered by
the date range parameter. View 1 should be an agregation query with
the Minimum parameter on the Date. Then create a second View 2 using
View 1 joined against the original Table 1 using the Name and Date as
the relationship and set as an equal join. Only show those records
were the values exist on both sides of the join. View 2 should include
the T1.Fashion_id, V1.Name, V1.Data, V1.Shirt, V1.Tie, and
T1.Fashion_Score

This should work.

Thanks,
cleith

-- It's very ugly, but try cutting/pasting this into SQL Query
Analyzer
-- The trick is to get a list of the original data combined with all
possible search criteria.

create table Fashion (
	fashion_id    int, 
	name          varchar(20),  
	wear_date     datetime,  
	shirt         varchar(20), 
	tie           varchar(20), 
	fashion_score int
)
go

insert into Fashion select 1, 'Bill',   '2002-01-01',  'blue',  
'red',    3
insert into Fashion select 2, 'Bob',    '2002-01-04',  'blue',  
'white',  5
insert into Fashion select 3, 'Bill',   '2002-01-05',  'green', 
'red',    4
insert into Fashion select 4, 'Bill',   '2002-01-07',  'blue',  
'red',    7
insert into Fashion select 5, 'Bob',    '2002-01-10',  'blue',  
'red',    2
insert into Fashion select 6, 'Bill',   '2002-01-10',  'green', 
'red',    2
insert into Fashion select 7, 'Bill',   '2002-01-11',  'green', 
'red',    9
go

create view vDates as
select date = convert(datetime, '2002-01-01')
union select convert(datetime, '2002-01-02')
union select convert(datetime, '2002-01-03')
union select convert(datetime, '2002-01-04')
union select convert(datetime, '2002-01-05')
union select convert(datetime, '2002-01-06')
union select convert(datetime, '2002-01-07')
union select convert(datetime, '2002-01-08')
union select convert(datetime, '2002-01-09')
union select convert(datetime, '2002-01-10')
union select convert(datetime, '2002-01-11')
go

create view v_Fashion_Starters as
select f.*, fw.FilterDateFrom, fw.FilterDateTo
from (
	select FirstWearDate = min(f.wear_date), FilterDateFrom = df.Date,
FilterDateTo = dt.Date, f.Name, f.Shirt, f.Tie
		from Fashion f
			inner join vDates df on f.wear_date >= df.date
			inner join vDates dt on f.wear_date <= dt.date
		group by df.Date, dt.Date, f.Name, f.Shirt, f.Tie
		) as fw
	inner join Fashion f on fw.FirstWearDate = f.Wear_Date
		and fw.Name = f.Name
		and fw.Shirt = f.Shirt
		and fw.Tie = f.Tie
go

SELECT * 
  FROM v_Fashion_Starters 
  WHERE FilterDateFrom = '2002-01-04'  
        AND FilterDateTo = '2002-01-11'  

SELECT * 
  FROM v_Fashion_Starters 
  WHERE FilterDateFrom = '2002-01-01'  
        AND FilterDateTo = '2002-01-11'  
        AND Name = 'Bill' 
go

drop table Fashion
drop view vDates
drop view v_Fashion_Starters
go

Your version of the vDates definition restricts you to searching only
on dates that happen to appear in the Fashion table.  My vDates view
allows you to search on any date range that appears in the view.

Note: rather than a view with a bunch of silly "union selects", you
should probably make a Dates table.

Note 2: you can't pay me -- I was just looking at google answers for
fun and saw a challenge.

Nice to see ISO 8601 style dates (2002-10-31) in use in a Real World Application.

There is now an entire Google Directory Category devoted to the topic.