SQL Syntax - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Syntax

Author

Topic

kgbagentsmith
Starting Member

1 Post

Posted - 2007-02-28 : 15:36:54

I need help from a SQL guru. Assuming I have a table with the following rows:

01-Feb-2007, red
01-Feb-2007, blue
02-Feb-2007, red
02-Feb-2007, green
02-Feb-2007, yellow
02-Feb-2007, purple
03-Feb-2007, blue
03-Feb-2007, orange

I need a query that will tell me how many new colors appear on each day. In this case, 2 colors appear on 01-Feb that do not appear prior to 01-Feb (red, blue), 3 colors appear on 02-Feb that did not appear before (green, yellow, purple) and 1 new color (orange) appears on 03-Feb.

Assuming my table has properly formatted date, varchar columns and data, what would the SQL SELECT look like produce the desired result set:

01-Feb-2007, 2
02-Feb-2007, 3
03-Feb-2007, 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 15:53:16

[code]-- prepare sample data
declare @s table (dt datetime, color varchar(20))

insert @s
select '01-Feb-2007', 'red' union all
select '01-Feb-2007', 'blue' union all
select '02-Feb-2007', 'red' union all
select '02-Feb-2007', 'green' union all
select '02-Feb-2007', 'yellow' union all
select '02-Feb-2007', 'purple' union all
select '03-Feb-2007', 'blue' union all
select '03-Feb-2007', 'orange'

-- Show the expected result
SELECT dt AS theDate,
SUM(CASE WHEN PreHit = 0 THEN 1 ELSE 0 END) AS theNew
FROM (
SELECT s.dt,
(SELECT COUNT(*) FROM @s AS t WHERE t.Color = s.Color AND t.dt < s.dt) AS PreHit
FROM @s AS s
) AS x
GROUP BY x.dt
ORDER BY x.dt[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources