Multiple comments for single record

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)

Multiple comments for single record

Author

Topic

Kulstad
Starting Member

4 Posts

Posted - 2009-02-20 : 11:30:56

I am attempting to write a query that will be the basis for a monthly absence report for employees. Here are the components of the report:
- a listing of every day of the month (generated using a self-created calendar table).
- each day will either have no absences to report, or N absences to report
- absences are represented by established absence codes (ie: a code for a doctor's appointment, a code for a family illness, etc)
- it is possible for an employee to have more than one absence code entered on any given day
- for each absence code on a given day, there needs to be a comma-separated string in a COMMENTS field listing the description of the absence being reported.

The image shows my current SELECT statement output. The highlighted row shows multiple absence codes for a single day.

I am currently using 8 CTEs in my query (LEFT OUTER JOINED with my calendar table), as indicated also in the image. My problem derives from each code (ie: EDT, VAC, CIP, etc) having it's own "comment", and needing to be compiled into the REASONS field.

I am open to almost anything to get this resolved (as this query has already taken far too long to construct and far more than my fair share of grey hairs). I thank you all in advance for any and all help you can offer me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 11:35:34

so each of 8 CTE's return one reason each ? then isnt it just a matter of concatenating them in select list?

Kulstad
Starting Member

4 Posts

Posted - 2009-02-20 : 11:50:50

why is the easiest solution always the one that's overlooked?

Thank you, visakh16. That is almost exactly what I need. However, in my final SELECT statement, I have it written like this:

FTT.Comments + ', ' + HTT.Comments + ', ' + CIT.Comments + ', ' + GIT.Comments + ', ' + STT.Comments + ', ' + ATT.Comments + ', ' + AOT.Comments Reasons

which ends up giving me this:

, , Added during data conversion: Dec 9 2008 10:03AM, , , , Added during data conversion: Dec 9 2008 10:03AM

Is there a way to avoid the unnecessary commas in the end result?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 12:01:50

try like this


COALESCE(NULLIF(FTT.Comments,'') + ', ','') + COALESCE(NULLIF(HTT.Comments,'') + ', ','') + COALESCE(NULLIF(CIT.Comments,'') + ', ','') + COALESCE(NULLIF(GIT.Comments,'') + ', ','') + COALESCE(NULLIF(STT.Comments,'') + ', ','') + COALESCE(NULLIF(ATT.Comments,'') + ', ','') + AOT.Comments Reasons

Kulstad
Starting Member

4 Posts

Posted - 2009-02-20 : 12:47:32

that didn't seem to work. Based your on example, I constructed this test:

DECLARE @first varchar(10)
DECLARE @second varchar(10)
DECLARE @third varchar(10)
DECLARE @fourth varchar(10)
DECLARE @fifth varchar(10)

SET @first = NULL
SET @second = 'second'
SET @third = NULL
SET @fourth = 'fourth'
SET @fifth = 'fifth'

SELECT COALESCE(NULLIF(@first,'') + ', ','')
+ COALESCE(NULLIF(@second,'') + ', ','')
+ COALESCE(NULLIF(@third,'') + ', ','')
+ COALESCE(NULLIF(@fourth,'') + ', ','')
+ @fifth

...and it returned the result of:
, second, , fourth, fifth

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 12:51:29

run this before and then try


SET CONCAT_NULL_YIELDS_NULL ON
GO

DECLARE @first varchar(10)
DECLARE @second varchar(10)
DECLARE @third varchar(10)
DECLARE @fourth varchar(10)
DECLARE @fifth varchar(10)

SET @first = NULL
SET @second = 'second'
SET @third = NULL
SET @fourth = 'fourth'
SET @fifth = 'fifth'

SELECT COALESCE(NULLIF(@first,'') + ', ','')
+ COALESCE(NULLIF(@second,'') + ', ','')
+ COALESCE(NULLIF(@third,'') + ', ','')
+ COALESCE(NULLIF(@fourth,'') + ', ','')
+ @fifth

Kulstad
Starting Member

4 Posts

Posted - 2009-02-20 : 13:05:01

That's exactly it. Thank you so very much for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 13:07:05

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources