Holidays in SQL Server

Hi!
I have a large table in SQL Server 2000 with a datetime-column 'dt'. I want
to select all rows from that table, excluding days which fall on holidays or
weekends. What is the best way to accomplish this? I considered creating a
new table called "holidays" and then selecting all rows (sort of "where not
in (select * from holidays)") , but I was looking for a better solution
since that implies that I have to populate the "holidays" table.
Suggestions are welcome!
Sincerely,
Nils Magnus EnglundCreating a holidays/calendar table is a good thing. This will surely simplif
y
your search. Remember, your holidays might not be the same as mine so having
the
table will eliminate such.
-oj
http://www.rac4sql.net
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I wan
t
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where no
t
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||I think it is a good idea to have a calendar table.
Roji. P. Thomas
SQL Server Programmer
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||select * from TableName where DATETIME not in (select DATETIME from holidays
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> glsD
:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||Use a calendar table. See the "more advanced example" at
http://www.aspfaq.com/2453
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>

Holidays in SQL Server

Hi!
I have a large table in SQL Server 2000 with a datetime-column 'dt'. I want
to select all rows from that table, excluding days which fall on holidays or
weekends. What is the best way to accomplish this? I considered creating a
new table called "holidays" and then selecting all rows (sort of "where not
in (select * from holidays)") , but I was looking for a better solution
since that implies that I have to populate the "holidays" table.
Suggestions are welcome!
Sincerely,
Nils Magnus EnglundCreating a holidays/calendar table is a good thing. This will surely simplify
your search. Remember, your holidays might not be the same as mine so having the
table will eliminate such.
--
-oj
http://www.rac4sql.net
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I want
> to select all rows from that table, excluding days which fall on holidays or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||I think it is a good idea to have a calendar table.
--
Roji. P. Thomas
SQL Server Programmer
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||select * from TableName where DATETIME not in (select DATETIME from holidays
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> ¼¶¼g©ó¶l¥ó·s»D
:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||Use a calendar table. See the "more advanced example" at
http://www.aspfaq.com/2453
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>

Holidays in SQL Server

Hi!

I have a large table in SQL Server 2000 with a datetime-column 'dt'. I want
to select all rows from that table, excluding days which fall on holidays or
weekends. What is the best way to accomplish this? I considered creating a
new table called "holidays" and then selecting all rows (sort of "where not
in (select * from holidays)") , but I was looking for a better solution
since that implies that I have to populate the "holidays" table.

Suggestions are welcome!

Sincerely,
Nils Magnus Englund"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:2rT%b.103$72.176991232@.news.telia.no...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.

That's probably your best idea.

Your holidays may not be mine.

> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund|||Nils Magnus Englund (nils.magnus.englund@.orkfin.no) writes:
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
> want to select all rows from that table, excluding days which fall on
> holidays or weekends. What is the best way to accomplish this? I
> considered creating a new table called "holidays" and then selecting all
> rows (sort of "where not in (select * from holidays)") , but I was
> looking for a better solution since that implies that I have to populate
> the "holidays" table.

And how would you expect SQL Server to know about syttende maj or when
Midsummer is?

You can of course make the holidays table more or less sophisticated.
You can just put in all Mondays to Fridays that are not dates from now
to 2020 or whatever.

You can also write a stored procedure that fills in the table given the
rules about currently known holidays. You would need to find data on
where Easter falls, to determine days for Easter, Whitsun and Ascenion Day.

Yet an alternative is to put all days in that table, and then a flag
whether the day is a working day or not, no matter whether it's Friday
or Sunday.

And finally, for the SELECT it self I prefer:

SELECT *
FROM tbl t
WHERE NOT EXISTS (SELECT *
FROM holidays h
WHERE t.date = h.date)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Ho to Convert Varchar datatype into datetime

declare @.a varchar(10)
select @.a= shiftstarttime from o_parameter
print @.a

declare @.b varchar(10)
select @.b= shiftendtime from o_parameter
print @.b

declare @.dt varchar(20)
set @.dt=Left(getdate(),12)

print @.dt

declare @.dt1 varchar(20)
set @.dt1=Left(dateadd(dd, 1,getdate()),12)
print @.dt1

declare @.dt3 varchar(20)

set @.dt3= @.dt1 + space(0) + @.a

print @.dt3

declare @.dt4 varchar(20)

set @.dt4= @.dt + space(0) + @.b

print @.dt4

output of above script is as fallow

09.30am
06.30pm
Aug 22 2007
Aug 23 2007
Aug 23 2007 09.30am
Aug 22 2007 06.30pm

now i want to convert @.dt3 and @.dt4 into datetime .

because i wnat to calculate datedifference in hours by using this function

SET @.in_hour=DATEDIFF (HH ,@.D1,@.D2)

where @.D1 and @.D2 are datetime parameters.

but how can i get @.dt3 and @.dt4 into datetime so i can pass it to DATEDIFF() function.

so plz Guide me. or if u know how to write it then plz write here

i already use cast for it but it doesn't work.

so plz reply urgently.

Try this:

Code Snippet

DECLARE @.dt3 varchar(20), @.dt4 varchar(20), @.in_hour int

SET @.dt3 = 'AUG 23 2007 09:30am'

SET @.dt4 = 'AUG 23 2007 06:30am'

SET @.in_hour=DATEDIFF(HH,CAST(@.dt3 as datetime), CAST(@.dt4 as datetime))

SELECT @.in_hour

or

Code Snippet

DECLARE @.dt3 datetime, @.dt4 datetime, @.in_hour int

SET @.dt3 = CAST('AUG 23 2007 09:30am' as datetime)

SET @.dt4 = CAST('AUG 23 2007 06:30am' as datetime)

SET @.in_hour=DATEDIFF(HH,@.dt3,@.dt4)

SELECT @.in_hour

|||

You can use the following query,

Code Snippet

select

Datediff(HH,

cast(Convert(varchar, dateadd(dd, 1,getdate()),101) + ' ' + replace(shiftstarttime,'.',':') as datetime),

cast(Convert(varchar, dateadd(dd, 1,getdate()),101) + ' ' + replace(shiftendtime,'.',':') as datetime))

from

o_parameter

--or

Select datediff(HH,Convert(datetime, replace(shiftstarttime,'.',':')), Convert(datetime, replace(shiftendtime,'.',':')))

from

o_parameter

hierarchical selection within a select statment

CREATE TABLE RS_A(ColA char(1), ColB varchar(10))

INSERT INTO RS_A
VALUES ('S', 'shakespeare')
INSERT INTO RS_A
VALUES ('B', 'shakespeare')
INSERT INTO RS_A
VALUES ('P', 'shakespeare')

INSERT INTO RS_A
VALUES ('S', 'milton')
INSERT INTO RS_A
VALUES ('P', 'milton')
INSERT INTO RS_A
VALUES ('B', 'shelley')

INSERT INTO RS_A
VALUES ('B', 'kafka')
INSERT INTO RS_A
VALUES ('S', 'kafka')

INSERT INTO RS_A
VALUES ('P', 'tennyson')

SELECT * FROM RS_A

Now i need a select which selects based on hierarchy

if ColA = 'S', then select only that row
else if ColA = 'B' then select only that row
else if colA = 'P' then select only that row

So my results should look like
S shakespeare
S milton
B shelley
S kafka
P tennyson

Is there a way to do this within a select statement
I tried using a CASE in WHERE CLAUSE but it put out all rows which
existed/

If any of you can help me with this right away, its is greatly
appreciated
Thanks in advance(rshivaraman@.gmail.com) writes:

Quote:

Originally Posted by

SELECT * FROM RS_A
>
Now i need a select which selects based on hierarchy
>
if ColA = 'S', then select only that row
else if ColA = 'B' then select only that row
else if colA = 'P' then select only that row
>
So my results should look like
S shakespeare
S milton
B shelley
S kafka
P tennyson
>
Is there a way to do this within a select statement
I tried using a CASE in WHERE CLAUSE but it put out all rows which
existed/

First translate the codes to numeric values with CASE, you can take
MIN, and then translate back:

SELECT CASE minval WHEN 1 THEN 'S' WHEN 2 THEN 'B' WHEN 3 THEN 'P' END,
ColB
FROM (SELECT ColB, minval = MIN(CASE ColA
WHEN 'S' THEN 1
WHEN 'B' THEN 2
WHEN 'P' THEN 3
END)
FROM RS_A
GROUP BY ColB) AS x

If there are many possible values for ColA, it would be better to
put the mapping in a table and then join with that table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ingenius :
Thank you for the above and the RETURN was what was missing after
RAISEERROR

-RS

hierarchical select

hello,
i have a hierarchical select
something like this
with temp_containerCollect (id) as (
select c.id
from LOGI_T_CONT4REFCOLLECT c
where c.parent_id is null AND c.isActive like 'Y'
/~filter_refuseCollection: AND c.refusecollection_id =
{filter_refuseCollection} ~/
union all
select st.id from LOGI_T_CONT4REFCOLLECT st
inner join temp_containerCollect tst on st.parent_id = tst.id
where st.isActive like 'Y'
) select * from temp_containerCollect t
sometimes i get error with message The statement terminated. The maximum
recursion 100 has been exhausted before statement completion
i understand the message
i know solution with using hint OPTION (MAXRECURSION 2) but i don't wanna
use it
i'd like to set max recursion option globally for all hierarchical selects
so the question is: how to set max recursion option globally for database
instance?
thanks for all
TV
Hi Tomas
"Tomas Vojtech" wrote:

> hello,
> i have a hierarchical select
> something like this
> with temp_containerCollect (id) as (
> select c.id
> from LOGI_T_CONT4REFCOLLECT c
> where c.parent_id is null AND c.isActive like 'Y'
> /~filter_refuseCollection: AND c.refusecollection_id =
> {filter_refuseCollection} ~/
> union all
> select st.id from LOGI_T_CONT4REFCOLLECT st
> inner join temp_containerCollect tst on st.parent_id = tst.id
> where st.isActive like 'Y'
> ) select * from temp_containerCollect t
> sometimes i get error with message The statement terminated. The maximum
> recursion 100 has been exhausted before statement completion
> i understand the message
> i know solution with using hint OPTION (MAXRECURSION 2) but i don't wanna
> use it
> i'd like to set max recursion option globally for all hierarchical selects
> so the question is: how to set max recursion option globally for database
> instance?
> thanks for all
> TV
>
Although not quite what you asked the following suggestion has been logged at
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124653
where you can vote. If necessary you could add you specific request as well.
John

hierarchical select

hello,
i have a hierarchical select
something like this
with temp_containerCollect (id) as (
select c.id
from LOGI_T_CONT4REFCOLLECT c
where c.parent_id is null AND c.isActive like 'Y'
/~filter_refuseCollection: AND c.refusecollection_id =
{filter_refuseCollection} ~/
union all
select st.id from LOGI_T_CONT4REFCOLLECT st
inner join temp_containerCollect tst on st.parent_id = tst.id
where st.isActive like 'Y'
) select * from temp_containerCollect t
sometimes i get error with message The statement terminated. The maximum
recursion 100 has been exhausted before statement completion
i understand the message
i know solution with using hint OPTION (MAXRECURSION 2) but i don't wanna
use it
i'd like to set max recursion option globally for all hierarchical selects
so the question is: how to set max recursion option globally for database
instance?
thanks for all
TVHi Tomas
"Tomas Vojtech" wrote:

> hello,
> i have a hierarchical select
> something like this
> with temp_containerCollect (id) as (
> select c.id
> from LOGI_T_CONT4REFCOLLECT c
> where c.parent_id is null AND c.isActive like 'Y'
> /~filter_refuseCollection: AND c.refusecollection_id =
> {filter_refuseCollection} ~/
> union all
> select st.id from LOGI_T_CONT4REFCOLLECT st
> inner join temp_containerCollect tst on st.parent_id = tst.id
> where st.isActive like 'Y'
> ) select * from temp_containerCollect t
> sometimes i get error with message The statement terminated. The maximum
> recursion 100 has been exhausted before statement completion
> i understand the message
> i know solution with using hint OPTION (MAXRECURSION 2) but i don't wanna
> use it
> i'd like to set max recursion option globally for all hierarchical selects
> so the question is: how to set max recursion option globally for database
> instance?
> thanks for all
> TV
>
Although not quite what you asked the following suggestion has been logged a
t
https://connect.microsoft.com/SQLSe...=1246
53
where you can vote. If necessary you could add you specific request as well.
John

hierarchical select

hello,
i have a hierarchical select
something like this
with temp_containerCollect (id) as (
select c.id
from LOGI_T_CONT4REFCOLLECT c
where c.parent_id is null AND c.isActive like 'Y'
/~filter_refuseCollection: AND c.refusecollection_id = {filter_refuseCollection} ~/
union all
select st.id from LOGI_T_CONT4REFCOLLECT st
inner join temp_containerCollect tst on st.parent_id = tst.id
where st.isActive like 'Y'
) select * from temp_containerCollect t
sometimes i get error with message The statement terminated. The maximum
recursion 100 has been exhausted before statement completion
i understand the message
i know solution with using hint OPTION (MAXRECURSION 2) but i don't wanna
use it
i'd like to set max recursion option globally for all hierarchical selects
so the question is: how to set max recursion option globally for database
instance?
thanks for all
TVHi Tomas
"Tomas Vojtech" wrote:
> hello,
> i have a hierarchical select
> something like this
> with temp_containerCollect (id) as (
> select c.id
> from LOGI_T_CONT4REFCOLLECT c
> where c.parent_id is null AND c.isActive like 'Y'
> /~filter_refuseCollection: AND c.refusecollection_id => {filter_refuseCollection} ~/
> union all
> select st.id from LOGI_T_CONT4REFCOLLECT st
> inner join temp_containerCollect tst on st.parent_id = tst.id
> where st.isActive like 'Y'
> ) select * from temp_containerCollect t
> sometimes i get error with message The statement terminated. The maximum
> recursion 100 has been exhausted before statement completion
> i understand the message
> i know solution with using hint OPTION (MAXRECURSION 2) but i don't wanna
> use it
> i'd like to set max recursion option globally for all hierarchical selects
> so the question is: how to set max recursion option globally for database
> instance?
> thanks for all
> TV
>
Although not quite what you asked the following suggestion has been logged at
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124653
where you can vote. If necessary you could add you specific request as well.
John

Hierarchical Resultset Sorting

I have a query like this

with TempCTE(id, Name, level, sortcol)
As
(
Select id, Name, 0 as level,
cast(cast( id AS BINARY(4)) as varbinary(100)) sortcol
from Table1
where id = 1

union all

Select id, Name, 0 as level,
cast(sortcol + cast( id AS BINARY(4)) as varbinary(100)) sortcol

from Table1 inner join TempCTE on TempCTE.id = Table1.parentid
)

select * from TempCTE order by sortcol

My problem is I want to sort this hierarchical resultset further on name like

aaa
--aaaa
--bbbb
--cccc
aaaaa
bbbbb
--dddd
aaaaa
bbbbb
bbb
--aaaa
--bbbb

Thanks

You need to basically add the appropriate columns in the ORDER BY clause. Based on your query above, you need to probably do ORDER BY sortcol, name. See below example based on a query using AdventureWorks sample tables:

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT replicate('.', EmployeeLevel* 10) + right(replicate('0', 10) + cast(coalesce(ManagerID, 0) as varchar), 10) as Mgr
FROM DirectReports
ORDER BY EmployeeLevel, ManagerID, EmployeeID;
GO

Hiding series in a chart

Good afternoon
I have a hidiously complicated union select statement that i am feeding in
to a chart.
This creates two series and two data fields, some of these combinations dont
hold usefull data and so i would like to hide them. e.g
Series 1 and data field 1 = good data
Series 1 and data field 2 = crap data
Series 2 and data field 1 = crap data
Series 2 and data field 2 = good data
I cant find a way of hiding the series-datafield combinations that i dont
need.
The crap data that i dont want to show is always 0's but by filtering i
remove a whole data field not just part of it.
Please help
Thanks
Steve Dcan you get needed fields from your existing query?
something like this:
select Series1, Field1 from ( your complicated query ) as
ExistingQuery
where Series1 = 'whatever'
union
select Series2, Field2 as Field1 from ( your complicated query) as
ExistingQuery
where Series2 = 'whatever'
you might add extra integer field to your existing query
when Series 1 then 1
Series 2 = 2
and use it in where statement above
Steve Dearman wrote:
> Good afternoon
> I have a hidiously complicated union select statement that i am feeding in
> to a chart.
> This creates two series and two data fields, some of these combinations dont
> hold usefull data and so i would like to hide them. e.g
> Series 1 and data field 1 = good data
> Series 1 and data field 2 = crap data
> Series 2 and data field 1 = crap data
> Series 2 and data field 2 = good data
> I cant find a way of hiding the series-datafield combinations that i dont
> need.
> The crap data that i dont want to show is always 0's but by filtering i
> remove a whole data field not just part of it.
> Please help
> Thanks
> Steve D

hideous join statement

just came across this JOIN stmt

wtf?!

LEFT OUTER JOIN (SELECT DealID, MAX(PersonIDInitiator) PersonIDInitiator, MAX(PersonIDExecutor) PersonIDExecutor, MAX(PersonIDSalesPerson) PersonIDSalesPerson FROM (SELECT DealID, CASE WHEN RoleID = 1 THEN PersonID END PersonIDInitiator, CASE WHEN RoleID = 2 THEN PersonID END PersonIDExecutor, CASE WHEN RoleID = 3 THEN PersonID END PersonIDSalesPerson FROM dbo.DealRole WHERE RoleID BETWEEN 1 AND 3) dr GROUP BY DealID) Roles ON Roles.DealID = D.DealID

ugh!

can someone translate? why are there selects in the join?

You are dealing with what are called "derived tables". Please take a look at the way I have restructured your query:

LEFT OUTER JOIN
( SELECT DealID,
MAX(PersonIDInitiator) PersonIDInitiator,
MAX(PersonIDExecutor) PersonIDExecutor,
MAX(PersonIDSalesPerson) PersonIDSalesPerson
FROM ( SELECT DealID,
CASE WHEN RoleID = 1 THEN PersonID END PersonIDInitiator,
CASE WHEN RoleID = 2 THEN PersonID END PersonIDExecutor,
CASE WHEN RoleID = 3 THEN PersonID END PersonIDSalesPerson
FROM dbo.DealRole
WHERE RoleID BETWEEN 1 AND 3
) dr
GROUP BY DealID
) Roles
ON Roles.DealID = D.DealID

There are two different derived tables in your join. Derived tables are select expressions that can be substituted for and used for tables similar to the way that views can be substituted for and used in the place of tables. I colored the inner derived table in green. Note the inner derived table has "dr" for an alias that I colored brown.

The outer derived table includes the inner derived table so even though I have colored the inner derived table in green, it is still part of the outer derived table. Notice that the outer derived table has "Roles" for an alias and is colored blue.

I notieced by your "ugh" response to the derived table that you are initial repulsed by the use. This is not uncommon; however, derived tables are an integral part of many advanced queries and I would certainly recommend that you take time to learn the use.

Also, SQL Server 2005 introduced the use of "Common Table Expressions" -- CTEs. I would suggest that you might also want to give the CTE beasties a look

|||ok, so bearing in mind that the [deal role].[deal role id] appears to be a number incremented each time the person id on a deal is changed, then it would appear the code is wrong

i.e. it is returning the highest personid, rather than the person id of the highest role id

so if person A was id = 5 was the original initiator, but then person B with person id of 2 is then the originator, it would wrongly show A instead of B as 'current/latest initiator'|||Yes, it sounds like that logic problem exists. Are you using SQL 2005 or SQL Server 2000?|||sql2k5 sp2|||This looks like a job for the ROW_NUMBER function; please give a look to ROW_NUMBER() and OVER in books online while I cook up an example with what you have given.|||

Adolf:

Here is an example of code using (1) a derived table and (2) the ROW_NUMBER() function with the OVER, PARTITION BY and ORDER BY syntax:

Code Snippet

declare @.dealRole table
( drId integer,
DealID integer,
roleId integer,
PersonId integer,
eventDt datetime
)
insert into @.dealRole
select 1, 11, 1, 51, '3/4/7' union all
select 2, 11, 2, 57, '3/5/7' union all
select 3, 11, 3, 54, '3/8/7' union all
select 4, 11, 1, 47, '4/3/7' union all
select 5, 11, 2, 51, '4/3/7' union all
select 6, 11, 1, 44, '4/5/7' union all
select 7, 12, 1, 52, '4/8/7' union all
select 8, 11, 3, 55, '4/8/7' union all
select 9, 11, 4, 14, '4/15/7'

select dealId,
RoleId,
PersonId,
eventDt
from ( select dealId,
roleId,
row_number() over
( partition by dealId, roleId
order by eventDt desc, drId desc
) as seQ,
PersonId,
eventDt
from @.dealRole
where roleId between 1 and 3
) a
where seq = 1

/*
dealId RoleId PersonId eventDt
-- -- -- --
11 1 44 2007-04-05 00:00:00.000
11 2 51 2007-04-03 00:00:00.000
11 3 55 2007-04-08 00:00:00.000
12 1 52 2007-04-08 00:00:00.000
*/