EVERY with SQL Server 2005 - 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 2005 Forums

Transact-SQL (2005)

EVERY with SQL Server 2005

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-02-12 : 07:12:51

Hi,

I am working on SQL Server 2005.

I have two tables Training(TrainingID, CertificationName) and TrainingDetail(TrgDetailID ,TrainingID, LKCode, CompletedDt). For one TrainingID, there can be multiple values in TrainingDetail with different LKTrainingClassCode.

TrgDetailID TrainingID LKCode CompletedDt
1 4 XYZ 1/1/2010
3 4 PQR 5/5/2007
5 6 XYZ 3/3/2010
9 6 PQR 1/14/2010

I need to list all the different courses for a training in one row. It requires a pivot table approach.

The following query gives me the result.

SELECT T.TrainingID,
MAX (CASE WHEN TD.LKCode = ' XYZ' THEN CompletedDt ELSE NULL END) AS [XYZCompleted],
MAX (CASE WHEN TD.LKCode = ' PQR' THEN CompletedDt ELSE NULL END) AS [PQRCompleted]
FROM Training T
LEFT OUTER JOIN TrainingDetail TD
ON TD.TrainingID = T.TrainingID
GROUP BY T.TrainingID

TrainingID XYZCompleted PQRCompleted
4 1/1/2010 5/5/2007
6 3/3/2010 1/14/2010

NOW, I need to find only those TrainingIDs for which ALL(EVERY) the CompletedDts are between '1/1/2009' and '1/1/2011'.

WHERE condition cannot be applied since it will lead to wrong result as follows:

TrainingID XYZCompleted PQRCompleted
4 1/1/2010 NULL
6 3/3/2010 1/14/2010

The correct result should display only the row corresponding to TrainingID = 6.

It would have been an easy task if EVERY keyword was present. How can I do it in SQL Server 2005?

Please help.

Thanks
Lijo Cheeran Joseph

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-12 : 07:43:15

Try this

SELECT T.TrainingID,
MAX (CASE WHEN TD.LKCode = ' XYZ' THEN CompletedDt ELSE NULL END) AS [XYZCompleted],
MAX (CASE WHEN TD.LKCode = ' PQR' THEN CompletedDt ELSE NULL END) AS [PQRCompleted]
FROM Training T
LEFT OUTER JOIN TrainingDetail TD
ON TD.TrainingID = T.TrainingID
GROUP BY T.TrainingID
HAVING MIN(CompletedDt)>='2009-01-01' and MAX(CompletedDt)<='2011-01-01'

Madhivanan

Failing to plan is Planning to fail

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-02-12 : 07:57:26

Birlliant !!! It works. Thanks...

It is all about looking at the thing from different perspectives.

If anyone is intereseted about a similer condition - ANY. "If any of the dates is between the stipulated range", use this

HAVING SUM(CASE WHEN CompletedDt BETWEEN '1/1/2009' AND '1/1/2011' THEN 1 ELSE 0 END) > 0

Thanks
Lijo Cheeran Joseph

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-12 : 08:32:46

quote:
Originally posted by Lijo Cheeran Joseph

Birlliant !!! It works. Thanks...

It is all about looking at the thing from different perspectives.

If anyone is intereseted about a similer condition - ANY. "If any of the dates is between the stipulated range", use this

HAVING SUM(CASE WHEN CompletedDt BETWEEN '1/1/2009' AND '1/1/2011' THEN 1 ELSE 0 END) > 0

Thanks
Lijo Cheeran Joseph

You are welcome

Also see if you it works for ANY case

HAVING MIN(CompletedDt)>='2009-01-01' or MAX(CompletedDt)<='2011-01-01'

Madhivanan

Failing to plan is Planning to fail

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-02-12 : 09:34:16

Hi Madhivanan

I don't think, the suggested solution for ANY, will work. I need trainings even with dates (Jan/1/2002, Jan/1/2010, Jan/1/2012). This set should get displayed. Aplying MAX and MIN condition will not work.

Please correct me if I am wrong

Thanks
Lijo Cheeran Joseph

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-12 : 09:57:19

quote:
Originally posted by Lijo Cheeran Joseph

Hi Madhivanan

I don't think, the suggested solution for ANY, will work. I need trainings even with dates (Jan/1/2002, Jan/1/2010, Jan/1/2012). This set should get displayed. Aplying MAX and MIN condition will not work.

Please correct me if I am wrong

Thanks
Lijo Cheeran Joseph

Have you tried it?
How will your method take care of dates 1/1/2002?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources