InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 00:47:58 GMT20http://www.sqlservercentral.com/Forums/Topic1424223-1292-1.aspxThank you, Jeff and Dwain, for following up.Wed, 27 Feb 2013 05:41:38 GMTEd Wagnerhttp://www.sqlservercentral.com/Forums/Topic1424223-1292-1.aspx[quote][b]dmarz96 (2/26/2013)[/b]is there a way to get only one record or am i doint this wrong.[/quote]I think this is the statement that caused me to think only one of the rows was required.Wed, 27 Feb 2013 05:39:06 GMTEd Wagnerhttp://www.sqlservercentral.com/Forums/Topic1424223-1292-1.aspx[quote][b]ChrisM@Work (2/27/2013)[/b][hr]Jeff, you and Dwain may have missed [url=http://www.sqlservercentral.com/Forums/FindPost1424153.aspx]the preceeding part of this case[/url].@dmarz96, it's much easier to see what you are trying to do, now that I've seen both threads. Often, breaking a case up into different threads like this causes confusion and fragmented replies as no single thread has all of the information required to put together a solution.[/quote]Indeed I did but it looks like you've got it covered.Wed, 27 Feb 2013 04:50:11 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1424223-1292-1.aspx[code="sql"], x.FAC_ACTIVITY [FAC_ACTIVITY] , x.NUMMAINTEMP [NUMMAINTEMP] , LH.dtLeaseFrom [LHdtLeaseFrom] , LH.dtLeaseTo [LHdtleaseto] , crp.destimated [crpdestimated]--, pps.HValue [ppsNSFFEE] ,pps.NSFFee ,pps.MTMFee ,pps.SecDepIntRate , m.stext [memoStext]FROM vw_DK_RMMISCDATA xLEFT JOIN Property p ON x.RM_ID = p.sCodeOUTER APPLY ( -- flatten relevant rows from PropOptions SELECT HPROP = MAX(po.HPROP), STYPE = MAX(po.STYPE), SVALUE = MAX(po.SVALUE), NSFFee = MAX(CASE WHEN po.STYPE = 'nsffee' THEN po.hvalue END), MTMFee = MAX(CASE WHEN po.STYPE = 'MTMFee' THEN po.hvalue END), SecDepIntRate = MAX(CASE WHEN po.STYPE = 'DDEPOSITINTEREST' THEN po.hvalue END) FROM PROPOPTIONS po WHERE po.HPROP = p.hmy -- outer reference AND po.STYPE IN ('nsffee', 'MTMFee', 'DDEPOSITINTEREST') -- filter) pps [/code]Wed, 27 Feb 2013 01:28:28 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1424223-1292-1.aspx[quote][b]Jeff Moden (2/26/2013)[/b][hr][quote][b]dwain.c (2/26/2013)[/b][hr]I did have to make an assumption (the 0 in the second row of the set up data) as the OP only provided 4 data columns in that row but 5 in the first row.[/quote]Agreed.@dmarz96,Just to give you a leg up for your future posts, take a look at the article at the first link in my signature line below. It'll really help you get a very high quality coded answer in a pretty short time.[/quote]Jeff, you and Dwain may have missed [url=http://www.sqlservercentral.com/Forums/FindPost1424153.aspx]the preceeding part of this case[/url].@dmarz96, it's much easier to see what you are trying to do, now that I've seen both threads. Often, breaking a case up into different threads like this causes confusion and fragmented replies as no single thread has all of the information required to put together a solution.Wed, 27 Feb 2013 01:14:14 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1424223-1292-1.aspx[quote][b]dwain.c (2/26/2013)[/b][hr]I did have to make an assumption (the 0 in the second row of the set up data) as the OP only provided 4 data columns in that row but 5 in the first row.[/quote]Agreed.@dmarz96,Just to give you a leg up for your future posts, take a look at the article at the first link in my signature line below. It'll really help you get a very high quality coded answer in a pretty short time.Tue, 26 Feb 2013 20:44:43 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1424223-1292-1.aspx[quote][b]Jeff Moden (2/26/2013)[/b][hr][quote][b]dwain.c (2/26/2013)[/b][hr]Like this?[/quote]Looks like it. Heh... we make a hell of a tag team. I'll flush out the requiremments, you write the code. :-)[/quote]Yeah, aren't we though?I did have to make an assumption (the 0 in the second row of the set up data) as the OP only provided 4 data columns in that row but 5 in the first row.Tue, 26 Feb 2013 20:36:11 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1424223-1292-1.aspx[quote][b]dwain.c (2/26/2013)[/b][hr]Like this?[/quote]Looks like it. Heh... we make a hell of a tag team. I'll flush out the requiremments, you write the code. :-)Tue, 26 Feb 2013 20:29:12 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1424223-1292-1.aspxLike this?[code="sql"]WITH PriorResults (HPROP, STYPE, SVALUE, NSFFee, SecDepIntRate) AS ( SELECT 240, 'DDEPOSITINTEREST', 0, NULL, 3000.0000 UNION ALL SELECT 240, 'NSFFee', 0, 50.0000, NULL)SELECT HPROP ,NSFFee=SUM(NSFFee) ,SecDepIntRate=SUM(SecDepIntRate)FROM PriorResultsGROUP BY HPROP[/code]Tue, 26 Feb 2013 18:45:46 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1424223-1292-1.aspxThe ladder I want to combine the rows into one record.the codeSELECT p.HPROP, p.STYPE, p.SVALUE, NSFFee = CASE WHEN p.STYPE = 'nsffee' THEN p.hvalue END, SecDepIntRate = CASE WHEN p.STYPE = 'DDEPOSITINTEREST' THEN p.hvalue END FROM PROPOPTIONS PWHERE p.hprop = '240' and p.STYPE IN ('nsffee','DDEPOSITINTEREST')the resultHPROP STYPE SVALUE NSFFee SecDepIntRate240 DDEPOSITINTEREST 0 NULL 3000.0000240 NSFFee 50.0000 NULLwhat I'm looking for is hprop nsffee SecDepIntRate240 50.0000 3000.0000Tue, 26 Feb 2013 13:41:24 GMTdmarz96http://www.sqlservercentral.com/Forums/Topic1424223-1292-1.aspx[quote][b]dmarz96 (2/26/2013)[/b][hr]That is not going to work for me.[/quote]So, what is it that you actually want? which of the two row do you want returned or do you want to somehow combine the rows? Be specific.Tue, 26 Feb 2013 13:35:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1424223-1292-1.aspxThat is not going to work for me.Tue, 26 Feb 2013 13:25:41 GMTdmarz96http://www.sqlservercentral.com/Forums/Topic1424223-1292-1.aspxYou could use SELECT TOP 1, but I don't know if that's what you really want.If you have more than one row meeting your search criteria, you should either want to return both or further refine your WHERE clause to return only the specific one you want. If you do want only one of them (say the last one added) and use TOP, make sure you include an ORDER BY clause to make the results consistent.Tue, 26 Feb 2013 13:11:54 GMTEd Wagnerhttp://www.sqlservercentral.com/Forums/Topic1424223-1292-1.aspxthis is the code I used to retrieve a record set.SELECT p.HPROP, p.STYPE, p.SVALUE, NSFFee = CASE WHEN p.STYPE = 'nsffee' THEN p.hvalue END, SecDepIntRate = CASE WHEN p.STYPE = 'DDEPOSITINTEREST' THEN p.hvalue END FROM PROPOPTIONS PWHERE p.hprop = '240' and p.STYPE IN ('nsffee','DDEPOSITINTEREST')my result is as followedHPROP STYPE SVALUE NSFFee SecDepIntRate240 DDEPOSITINTEREST 0 NULL 3000.0000240 NSFFee 50.0000 NULLis there a way to get only one record or am i doint this wrong.Tue, 26 Feb 2013 12:49:10 GMTdmarz96