Who was here on [date]? - 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 2008 Forums

Transact-SQL (2008)

Who was here on [date]?

Author

Topic

Boxersoft
Starting Member

28 Posts

Posted - 2010-04-16 : 19:44:42

Let's say I have a table of Movements keyed to a table of People. Structure is very simple:

id, person_id, date, type (Arrival or Departure)

I want to be able to select all people who were present (i.e. had arrived but not yet departed) on a given date, and I'm struggling to figure how to do that (bearing in mind that a person can visit more than once).

Suggestions, anyone?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-16 : 21:03:52

will you have 2 consecutive Arrival without Departure ?

It will be easier if you can post some sample data of various scenario and also show what is the expected result

KH
[spoiler]Time is always against us[/spoiler]

Boxersoft
Starting Member

28 Posts

Posted - 2010-04-17 : 02:37:47

No, no-one can Arrive if they're already here. Sample data would be:

01 Jan, Bob, Arrive
01 Jan, Bill, Arrive
04 Jan, Bob, Depart
10 Jan, Jane, Arrive
11 Jan, Bill, Depart
11 Jan, Bob, Arrive
15 Jan, Bob, Depart
17 Jan, Bill, Arrive
18 Jan, Bill, Depart

Expected results:

(1) WhoWasHere(12 Jan): Jane, Bob.
(2) WhoWasHere(11 Jan): Bill, Jane, Bob.

In (2), note that Bill and Bob both get counted even though one is leaving and the other just arriving.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-17 : 03:31:24

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
theDate DATETIME,
thePerson VARCHAR(4),
theType VARCHAR(6)
)

INSERT @Sample
SELECT '01 Jan 2010', 'Bob' , 'Arrive' UNION ALL
SELECT '01 Jan 2010', 'Bill', 'Arrive' UNION ALL
SELECT '04 Jan 2010', 'Bob' , 'Depart' UNION ALL
SELECT '10 Jan 2010', 'Jane', 'Arrive' UNION ALL
SELECT '11 Jan 2010', 'Bill', 'Depart' UNION ALL
SELECT '11 Jan 2010', 'Bob' , 'Arrive' UNION ALL
SELECT '15 Jan 2010', 'Bob' , 'Depart' UNION ALL
SELECT '17 Jan 2010', 'Bill', 'Arrive' UNION ALL
SELECT '18 Jan 2010', 'Bill', 'Depart'

DECLARE @Date DATETIME

-- First case
SET @Date = '12 Jan 2010'

;WITH cteSource(theDate, thePerson, theType, recID)
AS (
SELECT theDate,
thePerson,
theType,
ROW_NUMBER() OVER (PARTITION BY thePerson ORDER BY theDate DESC) AS recID
FROM @Sample
WHERE CASE theType
WHEN 'Arrive' THEN theDate
ELSE DATEADD(DAY, 1, theDate)
END <= @Date
)
SELECT theDate,
thePerson
FROM cteSource
WHERE recID = 1
AND theType = 'Arrive'
ORDER BY theDate,
thePerson

-- Second case
SET @Date = '11 Jan 2010'

;WITH cteSource(theDate, thePerson, theType, recID)
AS (
SELECT theDate,
thePerson,
theType,
ROW_NUMBER() OVER (PARTITION BY thePerson ORDER BY theDate DESC) AS recID
FROM @Sample
WHERE CASE theType
WHEN 'Arrive' THEN theDate
ELSE DATEADD(DAY, 1, theDate)
END <= @Date
)
SELECT theDate,
thePerson
FROM cteSource
WHERE recID = 1
AND theType = 'Arrive'
ORDER BY theDate,
thePerson[/code]

N 56°04'39.26"
E 12°55'05.63"

Boxersoft
Starting Member

28 Posts

Posted - 2010-04-17 : 14:55:00

Wow! It's going to take me some studying to figure out what that's all about and to apply it to my actual tables rather than the simplistic examples I used to demonstrate the problem, but it certainly seems to do the business.

Thanks very much!

Boxersoft
Starting Member

28 Posts

Posted - 2010-04-19 : 04:17:42

OK, I've done some reading on CTEs and ROW_NUMBER()/PARTITION, I get the gist and I've got it working on my data. I've now discovered a further requirement though... :(

In addition to the 'Who was here on [date]', it seems I also need a count of everyone who was here at the end of each date - and that has to be grouped by the status of the people.

I haven't a clue how to do this in SQL, but I'm sure it must be possible. If anyone feels like having a stab at it, I've tried to follow Peso's excellent example with sample tables below. The desired output would be something like:

Date Bronze Silver Gold Platinum
01-Sep-07 5 0 0 0
01-Jan-08 0 5 0 0
03-Feb-08 0 3 0 0
02-Aug-08 4 3 0 0
15-Feb-09 0 7 0 0
18-Mar-09 0 8 0 0
05-Apr-09 0 7 0 0
12-Apr-09 0 5 0 0
12-May-09 0 6 0 0
23-Jul-09 0 5 0 0
21-Aug-09 0 4 0 0
31-Dec-09 1 3 1 0
15-Jan-10 2 2 2 0
16-Jan-10 3 1 3 0
31-Jan-10 4 0 4 0
01-Apr-10 4 0 5 0
07-Apr-10 4 0 4 0

-- Sample data
DECLARE @event TABLE
(
id INT IDENTITY(1,1) NOT NULL,
date DATETIME,
person INT,
status INT,
type INT
)
insert @event
-- Date Psn St Type
SELECT 'Sep 1 2007', 15, 1, 1 UNION ALL
SELECT 'Sep 1 2007', 77, 1, 1 UNION ALL
SELECT 'Sep 1 2007', 78, 1, 1 UNION ALL
SELECT 'Sep 1 2007', 79, 1, 1 UNION ALL
SELECT 'Sep 1 2007', 80, 1, 1 UNION ALL
SELECT 'Jan 1 2008', 15, 2, 2 UNION ALL
SELECT 'Jan 1 2008', 77, 2, 2 UNION ALL
SELECT 'Jan 1 2008', 78, 2, 2 UNION ALL
SELECT 'Jan 1 2008', 79, 2, 2 UNION ALL
SELECT 'Jan 1 2008', 80, 2, 2 UNION ALL
SELECT 'Feb 3 2008', 77, 4, 3 UNION ALL
SELECT 'Feb 3 2008', 78, 4, 3 UNION ALL
SELECT 'Aug 2 2008', 1, 1, 1 UNION ALL
SELECT 'Aug 2 2008', 2, 1, 1 UNION ALL
SELECT 'Aug 2 2008', 3, 1, 1 UNION ALL
SELECT 'Aug 2 2008', 4, 1, 1 UNION ALL
SELECT 'Feb 15 2009', 1, 2, 2 UNION ALL
SELECT 'Feb 15 2009', 2, 2, 2 UNION ALL
SELECT 'Feb 15 2009', 3, 2, 2 UNION ALL
SELECT 'Feb 15 2009', 4, 2, 2 UNION ALL
SELECT 'Mar 18 2009', 5, 2, 1 UNION ALL
SELECT 'Apr 5 2009', 79, 1, 3 UNION ALL
SELECT 'Apr 12 2009', 15, 2, 3 UNION ALL
SELECT 'Apr 12 2009', 80, 2, 3 UNION ALL
SELECT 'May 12 2009', 6, 2, 1 UNION ALL
SELECT 'Jul 23 2009', 5, 2, 3 UNION ALL
SELECT 'Aug 21 2009', 6, 2, 3 UNION ALL
SELECT 'Dec 31 2009', 18, 1, 1 UNION ALL
SELECT 'Dec 31 2009', 1, 3, 2 UNION ALL
SELECT 'Jan 15 2010', 19, 1, 1 UNION ALL
SELECT 'Jan 16 2010', 20, 2, 1 UNION ALL
SELECT 'Jan 31 2010', 31, 1, 1 UNION ALL
SELECT 'Apr 1 2010', 5, 3, 1 UNION ALL
SELECT 'Apr 7 2010', 5, 3, 3

DECLARE @person TABLE
(
id int,
name varchar(10)
)
INSERT @person
SELECT 1, 'Iris' UNION ALL
SELECT 2, 'Kathy' UNION ALL
SELECT 3, 'Sally' UNION ALL
SELECT 4, 'Claire' UNION ALL
SELECT 5, 'Patrick' UNION ALL
SELECT 6, 'Tara' UNION ALL
SELECT 15, 'Betty' UNION ALL
SELECT 18, 'Ailsa' UNION ALL
SELECT 19, 'Annabel' UNION ALL
SELECT 20, 'Adam' UNION ALL
SELECT 31, 'Adair' UNION ALL
SELECT 77, 'Max' UNION ALL
SELECT 78, 'Carrie' UNION ALL
SELECT 79, 'Roger' UNION ALL
SELECT 80, 'Bianca'

DECLARE @eventType TABLE
(
id int,
name varchar(10)
)
INSERT INTO @eventType
SELECT 1, 'Arrival' UNION ALL
SELECT 2, 'Promotion' UNION ALL
SELECT 3, 'Departure' UNION ALL
SELECT 4, 'Demotion'

DECLARE @status TABLE
(
id int,
name varchar(10)
)
INSERT INTO @status
SELECT 1, 'Bronze' UNION ALL
SELECT 2, 'Silver' UNION ALL
SELECT 3, 'Gold' UNION ALL
SELECT 4, 'Platinum'

-- All records showing relationships
select e.id, CONVERT(varchar, e.date, 106) AS EventDate, e.person,
t.name AS Type, p.name, s.name AS Status
FROM @event e
LEFT JOIN @person p ON e.person=p.id
LEFT JOIN @eventType t ON e.type=t.id
LEFT JOIN @status s ON e.status=s.id
ORDER BY e.date

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 05:10:23

Can you explain how you got count as

03-Feb-08 0 3 0 0
for above date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Boxersoft
Starting Member

28 Posts

Posted - 2010-04-19 : 05:41:20

Sure. This is a continuation of the 'movements' issue of the original posting, where we're interested only in individuals that were on the location on a given date. On 03 Feb 08 we had the Departure events of Max and Carrie, reducing the count from 5 to 3.

The original report ('who was here on [date]?') had to include everyone who was here on [date], no matter how fleetingly. This report is effectively a tally of 'at close of business on [date], how many were here?'. I guess that might seem discrepant because the first report will list 5 individual, the second only 3.

Does that makes sense?

Llewellyn
Starting Member

13 Posts

Posted - 2010-04-19 : 06:45:09

select person_ID
FROM (

select A.Person_id,
A.dtDate as DateArrive,
isnull(B.dtDate,GetDate() + 1) as DateDepart
FROM YourTable A LEFT Join YourTable B
ON A.Person_ID = B.Person_id
AND A.Type = 'Arrival'
AND B.type = 'Departed'
WHERE B.id IN (SELECT MIN(id) FROM YourTable C WHERE C.ID > A.ID and A.Person_ID = C.Person_id)
or B.id is null

) z
Where @DateOfInterest >= DateArrive and @DateOfInterest <= DateDepart

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 07:15:18

quote:
Originally posted by Boxersoft

Sure. This is a continuation of the 'movements' issue of the original posting, where we're interested only in individuals that were on the location on a given date. On 03 Feb 08 we had the Departure events of Max and Carrie, reducing the count from 5 to 3.

The original report ('who was here on [date]?') had to include everyone who was here on [date], no matter how fleetingly. This report is effectively a tally of 'at close of business on [date], how many were here?'. I guess that might seem discrepant because the first report will list 5 individual, the second only 3.

Does that makes sense?

may be this


;With CTE
AS
(
select e.id, e.date,--CONVERT(varchar, e.date, 106) AS EventDate, 
e.person,
t.name AS [Type], p.name, s.name AS [Status]
FROM @event e 
LEFT JOIN @person p ON e.person=p.id
LEFT JOIN @eventType t ON e.type=t.id
LEFT JOIN @status s ON e.status=s.id
)


select date,
COUNT(DISTINCT CASE WHEN c2.[Status]='Bronze' THEN name ELSE NULL END) AS Bronze,
COUNT(DISTINCT CASE WHEN c2.[Status]='Silver' THEN name ELSE NULL END)AS Silver,
COUNT(DISTINCT CASE WHEN c2.[Status]='Gold' THEN name ELSE NULL END) AS Gold, 
COUNT(DISTINCT CASE WHEN c2.[Status]='Platinum' THEN name ELSE NULL END) AS Platinum
from (select distinct date from CTE) c
outer apply (select name,SUM(case when Type='Departure' THEN -1 when Type='Arrival' THEN  1 ELSE 0 END) as cnt
             FROM CTE
             WHERE  date<=c.date
             GROUP BY name)c1
outer apply (select TOP 1 [Status]
FROM CTE
WHERE name= c1.name
AND date<=c.date
order by date desc)c2
group by date,cnt
having cnt >0
order by date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-04-20 : 12:21:49

>> Let's say I have a table of Movements keyed to a table of People. Structure is very simple:
id, person_id, date, type (Arrival or Departure) <<

Could you please post real DDL and not narratives on forums? The problem is that you design is wrong; events have durations. You are just mimicking a PHYSICAL sign-in sheet and not doing a proper logical model.

CREATE TABLE Personnel_Movements
(person_id CHAR(9) NOT NULL,
arrival_date DEFAULT CURRENT_DATE DATE NOT NULL,
PRIMARY KEY (person_id, arrival_date),
departure_date DATE,
CONSTRAINT Proper_Duration
CHECK (arrival_date < departure_date));

I am assuming DAY is fine enough granularity; if not use DATETIME. The NULL departure date means he is still here.

Data elements should not have vague names like "id" (please tell me you did not mean to use an IDENTITY!), "date" (of what ONE THING? You overloaded it) and "type" (blood_type? automobile_body_type?). Get a book on basic data modeling or my SQL PROGRAMMING STYLE and read the ISO-11179 rules. Her is your report, without recursion or confusion:

SELECT person_id
FROM Personnel_Movements
WHERE @in_report_date BETWEEN arrival_date
AND COALESCE (departure_date, CURRENT_DATE);

Proper DDL means *orders of magnitude* easier and faster DML.

--CELKO--
Joe Celko, SQL Guru

Boxersoft
Starting Member

28 Posts

Posted - 2010-04-20 : 15:38:28

Llewellyn

Thanks for that, it seems to do the job. I've already implemented Peso's CTE-based approach, but your solution will be useful if I have to move to a DBMS that doesn't support CTEs.

Visakh

Excellent! At first it seemed not quite right, but that turned out to be due to data transcription errors on my part - your code works flawlessly. I don't quite understand all that it's doing, but that's some more reading for me.

Thanks very much for that.

Joe

quote:
please post real DDL and not narratives

Apologies if I've inadvertently broken forum etiquette. I'm not really familiar with DDL (Database Definition Language?) but I'll try to read up on it a bit before posting again. I'm guessing that it's the sort of stuff in Peso's nicely clear response that I tried to follow in my subsequent post.

quote:
The problem is that you design is wrong

I wouldn't be at all surprised. As a matter of fact, I started out with the approach you outline (a 'movement' table with arrival & departure columns), but switched when I discovered that I needed to log 'events' other than arrival & departure. Perhaps I need to treat those events separately from 'movements'; I'll give it some thought.

quote:
events have durations

I'm not convinced that's always true, actually, at least in practical terms. An arrival/departure pair makes a 'visit' event, which does of course have duration, but surely there are many examples that are effectively 'point' events? The 'promotion' event I mentioned would seem to be an example.

quote:
Data elements should not have vague names like "id" [...] and "type" (blood_type? automobile_body_type?)

I thought that when combined with the table would it would be unambiguous and that e.g. event.event_type would be tautologous. If that's the recommended practise, though, I'll happily adopt it.

quote:
please tell me you did not mean to use an IDENTITY!

Ummm... Yes, I was using 'id' as the name of an IDENTITY column serving as the primary key for my tables. That's bad, is it?

Thanks for the input.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 01:45:18

quote:
Originally posted by Boxersoft

Visakh

Excellent! At first it seemed not quite right, but that turned out to be due to data transcription errors on my part - your code works flawlessly. I don't quite understand all that it's doing, but that's some more reading for me.

Thanks very much for that.

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 01:46:12

suggest you to go through below to understand what all u can do with apply

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Boxersoft
Starting Member

28 Posts

Posted - 2010-04-21 : 02:09:02

Thanks Visakh, will do.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 06:20:44

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Boxersoft
Starting Member

28 Posts

Posted - 2010-04-23 : 14:09:27

I've been giving some thought to Joe's suggestion that arrivals and departures should be paired as 'movement' records. As I mentioned, that was my original design before I realised that I also needed to record 'events' other than arrivals and departures, but they are really different things so it probably makes sense to store them separately. I'm not really sure how best to organise them though:

DECLARE @movement TABLE(move_id INT, person_id INT,
arriveDate DATE, departDate DATE)

Storing 'movements' in this way does of course make it trivial to find which individuals were present on a given date. I can then store non-movement 'events' that result in a change of status in a separate table:

DECLARE @event TABLE(event_id INT, event_date DATE,
person_id INT, status_id INT, event_type_id INT)

DECLARE @status TABLE(status_id INT, status_name VARCHAR(10))

The report I have to produce needs to have one row for each Arrival, Departure and Event date. There should be one column for each possible status (rows in the 'status' table) containing the count of animals with that status that were present on the date in question, e.g.:

Date A B C D E F G H I J K L M
01Sep07 0 0 0 0 0 0 0 5 0 0 0 0 0 // 5 Arrivals with status Hotel
27Sep07 0 0 0 0 0 0 0 0 5 0 0 0 0 // 5 Development Events Hotel->India
03Feb08 0 0 0 0 0 0 0 0 3 0 0 0 0 // 2 Departures
02Aug08 4 0 0 0 0 0 0 0 3 0 0 0 0 // 4 Arrivals with status Alpha
10Sep08 0 4 0 0 0 0 0 0 3 0 0 0 0 // 4 Development events Alpha->Beta

... etc.

I suspect that it /should/ be possible to somehow JOIN the 'event' table with the 'movement' table to get the results without recourse to complex SQL, but I haven't been able to figure it out.

More fundamental than the SQL is a question of data structure. Given that I now have arrivals recorded in the 'movements' table rather than the 'events' table, I'm faced with the problem of not having anywhere to store the status of new arrivals. One way around that would be to record an arrival 'event' as well as the movement, but that's something of a duplication and so feels somewhat wrong. Alternatively I suppose I could have a 'status' column in the 'movement' table indicating the status on arrival, but that doesn't seem very elegant either.

I feel that I'm rather going around in circles and bogging myself down with this, so really I'd appreciate some advice. DDL (if I understand the term correctly) follows with structure and sample data, which I hope I've got right.

Thanks in advance for any input.

=================================

-- person
DECLARE @person TABLE(person_id INT, person_name VARCHAR(20))
INSERT @person
SELECT 1, 'Iris' UNION ALL
SELECT 2, 'Kathy' UNION ALL
SELECT 3, 'Saltire' UNION ALL
SELECT 4, 'Claire' UNION ALL
SELECT 5, 'Patrick' UNION ALL
SELECT 6, 'Tara' UNION ALL
SELECT 31, 'Adair' UNION ALL
SELECT 18, 'Ailsa' UNION ALL
SELECT 19, 'Annabel' UNION ALL
SELECT 20, 'Adam' UNION ALL
SELECT 15, 'Betty' UNION ALL
SELECT 77, 'Max' UNION ALL
SELECT 78, 'Carrie' UNION ALL
SELECT 79, 'Roger' UNION ALL
SELECT 80, 'Bianca' ;

-- event_type
DECLARE @event_type TABLE(et_id INT, et_name VARCHAR(20))
INSERT @event_type
SELECT 1, 'Arrival' UNION ALL
SELECT 2, 'Birth' UNION ALL
SELECT 3, 'Promotion' UNION ALL
SELECT 4, 'Other' UNION ALL
SELECT 5, 'Development' ;

-- status
DECLARE @status TABLE(status_id INT, status_name VARCHAR(10))
INSERT @status
SELECT 1, 'Alpha' UNION ALL
SELECT 2, 'Bravo' UNION ALL
SELECT 3, 'Charlie' UNION ALL
SELECT 4, 'Delta' UNION ALL
SELECT 5, 'Echo' UNION ALL
SELECT 6, 'Foxtrot' UNION ALL
SELECT 7, 'Golf' UNION ALL
SELECT 8, 'Hotel' UNION ALL
SELECT 9, 'India' UNION ALL
SELECT 10, 'Juliet' UNION ALL
SELECT 11, 'Kilo' UNION ALL
SELECT 12, 'Lima' UNION ALL
SELECT 13, 'Mike' ;

-- movements
DECLARE @movement TABLE(move_id INT, person_id INT, arriveDate date, departDate date)
INSERT @movement
SELECT 42, 15, '01 Sep 2007', '12 Apr 2009' UNION ALL
SELECT 43, 77, '01 Sep 2007', '03 Feb 2008' UNION ALL
SELECT 44, 78, '01 Sep 2007', '03 Feb 2008' UNION ALL
SELECT 45, 79, '01 Sep 2007', '05 Apr 2009' UNION ALL
SELECT 46, 80, '01 Sep 2007', '12 Apr 2009' UNION ALL
SELECT 1, 1, '02 Aug 2008', NULL UNION ALL
SELECT 2, 2, '02 Aug 2008', NULL UNION ALL
SELECT 3, 3, '02 Aug 2008', NULL UNION ALL
SELECT 4, 4, '02 Aug 2008', NULL UNION ALL
SELECT 5, 5, '18 Mar 2009', '23 Jul 2009' UNION ALL
SELECT 6, 6, '12 May 2009', '21 Aug 2009' UNION ALL
SELECT 9, 18, '31 Dec 2009', NULL UNION ALL
SELECT 30, 19, '15 Jan 2010', NULL UNION ALL
SELECT 32, 20, '16 Jan 2010', NULL UNION ALL
SELECT 8, 31, '31 Jan 2010', NULL

-- events
DECLARE @event TABLE(event_id INT, event_date DATE,
person_id INT, status_id INT, event_type_id INT)
INSERT @event
SELECT 42, '01 Sep 2007', 15, 8, 1 UNION ALL
SELECT 43, '01 Sep 2007', 77, 8, 1 UNION ALL
SELECT 44, '01 Sep 2007', 78, 8, 1 UNION ALL
SELECT 45, '01 Sep 2007', 79, 8, 1 UNION ALL
SELECT 46, '01 Sep 2007', 80, 8, 1 UNION ALL
SELECT 56, '27 Sep 2007', 15, 9, 5 UNION ALL
SELECT 57, '27 Sep 2007', 77, 9, 5 UNION ALL
SELECT 58, '27 Sep 2007', 78, 9, 5 UNION ALL
SELECT 59, '27 Sep 2007', 79, 9, 5 UNION ALL
SELECT 60, '27 Sep 2007', 80, 9, 5 UNION ALL
SELECT 1, '02 Aug 2008', 1, 1, 1 UNION ALL
SELECT 2, '02 Aug 2008', 2, 1, 1 UNION ALL
SELECT 3, '02 Aug 2008', 3, 1, 1 UNION ALL
SELECT 4, '02 Aug 2008', 4, 1, 1 UNION ALL
SELECT 61, '10 Sep 2008', 1, 13, 5 UNION ALL
SELECT 62, '10 Sep 2008', 2, 13, 5 UNION ALL
SELECT 63, '10 Sep 2008', 3, 13, 5 UNION ALL
SELECT 64, '10 Sep 2008', 4, 13, 5 UNION ALL
SELECT 5, '18 Mar 2009', 5, 4, 1 UNION ALL
SELECT 65, '29 Apr 2009', 1, 5, 5 UNION ALL
SELECT 66, '29 Apr 2009', 2, 5, 5 UNION ALL
SELECT 67, '29 Apr 2009', 3, 5, 5 UNION ALL
SELECT 68, '29 Apr 2009', 4, 5, 5 UNION ALL
SELECT 6, '12 May 2009', 6, 13, 1 UNION ALL
SELECT 48, '23 Jun 2009', 6, 5, 5 UNION ALL
SELECT 69, '31 Dec 2009', 1, 6, 3 UNION ALL
SELECT 9, '31 Dec 2009', 18, 1, 2 UNION ALL
SELECT 30, '15 Jan 2010', 19, 1, 2 UNION ALL
SELECT 71, '15 Jan 2010', 3, 6, 3 UNION ALL
SELECT 72, '16 Jan 2010', 4, 6, 3 UNION ALL
SELECT 32, '16 Jan 2010', 20, 2, 2 UNION ALL
SELECT 73, '21 Jan 2010', 20, 3, 4 UNION ALL
SELECT 70, '21 Jan 2010', 2, 6, 3 UNION ALL
SELECT 8, '31 Jan 2010', 31, 2, 1 UNION ALL
SELECT 74, '31 Jan 2010', 31, 3, 4 ;

-- All event records to show relationships
select e.event_id, CONVERT(VARCHAR, e.event_date, 106) AS EventDate, e.person_id,
t.et_name AS Type, p.person_name, s.status_name AS Status
FROM @event e
LEFT JOIN @person p ON e.person_id=p.person_id
LEFT JOIN @event_type t ON e.event_type_id=t.et_id
LEFT JOIN @status s ON e.status_id=s.status_id
ORDER BY e.event_date

Boxersoft
Starting Member

28 Posts

Posted - 2010-04-24 : 14:53:16

Visakh: your solution creates the category columns ('status') using "COUNT(DISTINCT CASE WHEN" clauses. Is it possible to take them from a table instead? My possible Status values (bronze->platinum in my example) are held in such a table, and additional Status values may be added at a later date...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-25 : 02:24:00

yup. thats possible but you need to use dynamic sql in that case

see

http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Boxersoft
Starting Member

28 Posts

Posted - 2010-05-04 : 08:54:47

OK, thanks. I assumed that dynamic SQL would be frowned upon but if it's considered an acceptable approach then I'll stick with that.

Thanks again for all your help, and apologies for the delay in responding.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 12:43:38

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources