May 31, 2013 at 4:07 am
Hey,
I've used STUFF successfully before but I can't get it quite right this time as I'm using 9 columns.
Take a resultset of;
Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 | Col8 | Col9
ABC | DEF | GHI | JKL | MNO | PQR | STU | VWX | YZ
ABC | DEF | GHI | JKL | 123 | PQR | STU | VWX | YZ
So both rows are the same except Col5. What I want is this;
Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 | Col8 | Col9
ABC | DEF | GHI | JKL | MNO; 123 | PQR | STU | VWX | YZ
Obviously the STUFF needs to be ignored if there aren't multiple OWNERS.
Col5 relates to the OWNER line (N_O columns) here;
SELECTDISTINCT C.IRN, CONVERT(DATE, DD.EVENTDUEDATE),
ISNULL(DD_EC.EVENTDESCRIPTION, DD_EV.EVENTDESCRIPTION) +
CASE WHEN DD.EVENTTEXT IS NOT NULL THEN ' (' + DD.EVENTTEXT + ')' ELSE '' END,
ISNULL(dbo.fn_FormatName(N_I.NAME, N_I.FIRSTNAME, N_I.TITLE, NULL), 'UNKNOWN INSTRUCTOR'),
ISNULL(dbo.fn_FormatName(N_O.NAME, N_O.FIRSTNAME, N_O.TITLE, NULL), 'UNKNOWN OWNER'),
ISNULL(C.CURRENTOFFICIALNO, 'UNKNOWN'), DDI.IMPORTANCEDESC,
ISNULL(N_E.SEARCHKEY2, '0'), ISNULL(N_S.SEARCHKEY2, '0')
FROMCASES C WITH (NOLOCK) JOIN
CASEEVENT DD WITH (NOLOCK) ON (DD.CASEID = C.CASEID) JOIN
OPENACTION DD_OA WITH (NOLOCK) ON (DD_OA.CASEID = C.CASEID) JOIN
EVENTCONTROL DD_EC WITH (NOLOCK) ON (DD_EC.CRITERIANO = DD_OA.CRITERIANO AND
DD_EC.EVENTNO = DD.EVENTNO) JOIN
EVENTS DD_EV WITH (NOLOCK) ON (DD_EV.EVENTNO = DD.EVENTNO) JOIN
CASENAME CN_I WITH (NOLOCK) ON (CN_I.CASEID = C.CASEID AND CN_I.NAMETYPE = 'I') JOIN
NAME N_I WITH (NOLOCK) ON (N_I.NAMENO = CN_I.NAMENO) LEFT JOIN
CASENAME CN_O WITH (NOLOCK) ON (CN_O.CASEID = C.CASEID AND CN_O.NAMETYPE = 'O') LEFT JOIN
NAME N_O WITH (NOLOCK) ON (N_O.NAMENO = CN_O.NAMENO) LEFT JOIN
CASENAME CN_E WITH (NOLOCK) ON (CN_E.CASEID = C.CASEID AND CN_E.NAMETYPE = 'EMP') LEFT JOIN
NAME N_E WITH (NOLOCK) ON (N_E.NAMENO = CN_E.NAMENO) LEFT JOIN
CASENAME CN_S WITH (NOLOCK) ON (CN_S.CASEID = C.CASEID AND CN_S.NAMETYPE = 'SIG') LEFT JOIN
NAME N_S WITH (NOLOCK) ON (N_S.NAMENO = CN_S.NAMENO) JOIN
IMPORTANCE DDI ON DDI.IMPORTANCELEVEL = COALESCE(DD_EC.IMPORTANCELEVEL, DD_EV.IMPORTANCELEVEL, 9)
WHERE(DD_OA.ACTION = DD_EV.CONTROLLINGACTION OR (DD_EV.CONTROLLINGACTION IS NULL AND
DD_EC.CRITERIANO = ISNULL(DD.CREATEDBYCRITERIA, DD_OA.CRITERIANO)) OR DD_OA.CASEID IS NULL) AND
ISNULL(DD.OCCURREDFLAG, 0) = 0 AND DD.EVENTDUEDATE IS NOT NULL AND C.IRN = 'P42951EP' AND EXISTS
(SELECT1
FROMOPENACTION OAX WITH (NOLOCK) JOIN
EVENTCONTROL ECX WITH (NOLOCK) ON (ECX.CRITERIANO = OAX.CRITERIANO AND
ECX.EVENTNO = DD.EVENTNO) JOIN
ACTIONS AX WITH (NOLOCK) ON (AX.ACTION = OAX.ACTION)
WHEREOAX.CASEID = C.CASEID AND OAX.ACTION = CASE WHEN (DD.EVENTNO = -11) THEN 'RN' ELSE
ISNULL(DD_EV.CONTROLLINGACTION, OAX.ACTION) END AND OAX.POLICEEVENTS = 1 AND
OAX.ACTION <> '~2' AND OAX.CYCLE = CASE WHEN (AX.NUMCYCLESALLOWED > 1) THEN DD.CYCLE ELSE 1 END AND
ISNULL(AX.ACTIONTYPEFLAG, 0) <> 1) AND (DD.EVENTDUEDATE < DATEADD(MONTH, 1, GETDATE()));
May 31, 2013 at 7:57 am
It is really hard to help with what you have posted. The code you posted is poorly formatted it looks like a big bowl of spaghetti. I took the liberty of running it through a formatter so it is legible.
SELECT DISTINCT C.IRN
,CONVERT(DATE, DD.EVENTDUEDATE)
,ISNULL(DD_EC.EVENTDESCRIPTION, DD_EV.EVENTDESCRIPTION) + CASE
WHEN DD.EVENTTEXT IS NOT NULL
THEN ' (' + DD.EVENTTEXT + ')'
ELSE ''
END
,ISNULL(dbo.fn_FormatName(N_I.NAME, N_I.FIRSTNAME, N_I.TITLE, NULL), 'UNKNOWN INSTRUCTOR')
,ISNULL(dbo.fn_FormatName(N_O.NAME, N_O.FIRSTNAME, N_O.TITLE, NULL), 'UNKNOWN OWNER')
,ISNULL(C.CURRENTOFFICIALNO, 'UNKNOWN')
,DDI.IMPORTANCEDESC
,ISNULL(N_E.SEARCHKEY2, '0')
,ISNULL(N_S.SEARCHKEY2, '0')
FROM CASES C WITH (NOLOCK)
INNER JOIN CASEEVENT DD WITH (NOLOCK) ON (DD.CASEID = C.CASEID)
INNER JOIN OPENACTION DD_OA WITH (NOLOCK) ON (DD_OA.CASEID = C.CASEID)
INNER JOIN EVENTCONTROL DD_EC WITH (NOLOCK) ON (
DD_EC.CRITERIANO = DD_OA.CRITERIANO
AND DD_EC.EVENTNO = DD.EVENTNO
)
INNER JOIN EVENTS DD_EV WITH (NOLOCK) ON (DD_EV.EVENTNO = DD.EVENTNO)
INNER JOIN CASENAME CN_I WITH (NOLOCK) ON (
CN_I.CASEID = C.CASEID
AND CN_I.NAMETYPE = 'I'
)
INNER JOIN NAME N_I
WITH (NOLOCK) ON (N_I.NAMENO = CN_I.NAMENO)
LEFT JOIN CASENAME CN_O WITH (NOLOCK) ON (
CN_O.CASEID = C.CASEID
AND CN_O.NAMETYPE = 'O'
)
LEFT JOIN NAME N_O
WITH (NOLOCK) ON (N_O.NAMENO = CN_O.NAMENO)
LEFT JOIN CASENAME CN_E WITH (NOLOCK) ON (
CN_E.CASEID = C.CASEID
AND CN_E.NAMETYPE = 'EMP'
)
LEFT JOIN NAME N_E
WITH (NOLOCK) ON (N_E.NAMENO = CN_E.NAMENO)
LEFT JOIN CASENAME CN_S WITH (NOLOCK) ON (
CN_S.CASEID = C.CASEID
AND CN_S.NAMETYPE = 'SIG'
)
LEFT JOIN NAME N_S
WITH (NOLOCK) ON (N_S.NAMENO = CN_S.NAMENO)
INNER JOIN IMPORTANCE DDI ON DDI.IMPORTANCELEVEL = COALESCE(DD_EC.IMPORTANCELEVEL, DD_EV.IMPORTANCELEVEL, 9)
WHERE (
DD_OA.ACTION = DD_EV.CONTROLLINGACTION
OR (
DD_EV.CONTROLLINGACTION IS NULL
AND DD_EC.CRITERIANO = ISNULL(DD.CREATEDBYCRITERIA, DD_OA.CRITERIANO)
)
OR DD_OA.CASEID IS NULL
)
AND ISNULL(DD.OCCURREDFLAG, 0) = 0
AND DD.EVENTDUEDATE IS NOT NULL
AND C.IRN = 'P42951EP'
AND EXISTS (
SELECT 1
FROM OPENACTION OAX WITH (NOLOCK)
INNER JOIN EVENTCONTROL ECX WITH (NOLOCK) ON (
ECX.CRITERIANO = OAX.CRITERIANO
AND ECX.EVENTNO = DD.EVENTNO
)
INNER JOIN ACTIONS AX WITH (NOLOCK) ON (AX.ACTION = OAX.ACTION)
WHERE OAX.CASEID = C.CASEID
AND OAX.ACTION = CASE
WHEN (DD.EVENTNO = - 11)
THEN 'RN'
ELSE ISNULL(DD_EV.CONTROLLINGACTION, OAX.ACTION)
END
AND OAX.POLICEEVENTS = 1
AND OAX.ACTION <> '~2'
AND OAX.CYCLE = CASE
WHEN (AX.NUMCYCLESALLOWED > 1)
THEN DD.CYCLE
ELSE 1
END
AND ISNULL(AX.ACTIONTYPEFLAG, 0) <> 1
)
AND (DD.EVENTDUEDATE < DATEADD(MONTH, 1, GETDATE()));
Unfortunately this doesn't help solve your current issue. In fact I don't see that you have stuff anywhere in here. I can't possibly help you with this query because I don't have any of those tables.
I would however like to make a couple suggestions. The scalar function dbo.fn_FormatName is likely going to cause serious performance issues.
Second is the liberal use of NOLOCK. Are you aware of what the hint does and the issues it brings to the table?
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
If you really want some help with your query you will need to post some ddl and sample data in a consumable format so we can help. Please take a few minutes and read the first article in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply