January 11, 2015 at 3:02 pm
Good afternoon, struggling to come up with a way to select all the values listed when the carat_id is MAX(carat_id), which is the primary table key.
Tried a few different things w/o success, I should get a unique row per account_id, vs the dups listed in the output, think I need a subquery but have not been able to figure it out.
Any help would be greatly appreciated.
SELECT b.carat_id
,b.account_id AS 'Account ID'
,ISNULL(CONVERT(VARCHAR(10),b.FO_LOC_expiry_date,101),'N/A') AS 'LOC Expiration Date'
,ISNULL(b.work_program,0) AS 'Work Program'
,b.cy_fin_worksheet_id AS 'Current Year Financial Worksheet ID'
,ISNULL(CONVERT(VARCHAR(10),b.cy_statement_date,101),'N/A') AS 'Current Year Statement Date'
,b.cy_working_capital AS 'Current Year Working Capital'
,b.cy_wc_case AS 'Current Year Working Capital Case'
,b.cy_net_worth AS 'Current Year Net Worth'
,b.cy_nw_case AS 'Current Year Net Worth Case'
,b.cy_current_ratio AS 'Current Year Current Ratio'
,b.cy_UBWC AS 'Current Year UBWC'
,b.cy_debt_worth AS 'Current Year Debt Worth'
,b.cy_bank_debt_worth AS 'Current Year Bank Debt Worth'
,b.cy_revenue AS 'Current Year Revenue'
,b.cy_net_income AS 'Current Year Net Income'
,b.cy_np_pct AS 'Current Year Net Profit Percent'
,b.cy_LQWC AS 'Current Year LQWC'
,b.cy_UBNW AS 'Current Year UBNW'
,b.cy_cash_flow_operations AS 'Current Year Cash Flow Operations'
,b.cy_cash_flow_investments AS 'Current Year Cash Flow Investments'
,b.cy_cash_flow_financing AS 'Current Year Cash Flow Financiang'
,b.py_fin_worksheet_id AS 'Prior Year Financial Worksheet ID'
,ISNULL(CONVERT(VARCHAR(10),b.py_statement_date,101),'N/A') AS 'Prior Year Statement Date'
,ISNULL(b.py_working_capital,0) AS 'Prior Year Working Capital'
,ISNULL(b.py_wc_case,0) AS 'Prior Year Working Capital Case'
,ISNULL(b.py_net_worth,0) AS 'Prior Year Net Worth'
,ISNULL(b.py_nw_case,0) AS 'Prior Year Net Worth Case'
,ISNULL(b.py_revenue,0) AS 'Prior Year Revenue'
,b.py_net_income AS 'Prior Year Net Income'
,b.one_yr_EDF AS '1 Year EDF'
,b.five_yr_EDF AS '5 year EDF'
,b.pctile_retained_earnings_to_assets AS 'Reatained Earnings to Assets'
,b.pct_of_bond_value AS 'Percent of Bond Value'
,b.LQWC_score AS 'LQWC Score'
,b.NP_score AS 'NP Score'
,b.UBNW_score AS 'UNBW Score'
,ISNULL(b.CARAT_score,0) AS 'Carat Score'
,b.ERM_max_gross_exposure as 'Max Gross Exposure'
,b.ERM_probable_gross_exposure AS 'Probable Gross Exposure'
,b.max_issued_class_code_id AS 'Max Issued Class Code'
,b.total_bond_premium AS 'Total Bond Premium'
,b.bond_exposure_maxissuedclasscode AS 'Bond Exposure For Max Issued Class Code'
,ISNULL(c.value_desc,'N/A') AS 'Indemnity Type'
FROM CARAT b
INNER JOIN lookup c
ON b.lu_account_indemnity_type = c.column_value and c.column_name = 'lu_account_indemnity_type'
WHERE b.account_id = 464
Partial Output that shows dups
carat_id Account ID LOC Expiration Date
8981 464 12/31/2005
10264 464 12/31/2005
11574 464 12/31/2005
12881 464 12/31/2005
14192 464 12/31/2005
16641 464 02/28/2006
17906 464 02/28/2006
19134 464 12/31/2006
19680 464 12/31/2006
22375 464 12/31/2006
24121 464 12/31/2006
25363 464 12/31/2006
30048 464 12/31/2006
31471 464 12/31/2006
32897 464 12/31/2006
34334 464 12/31/2006
35775 464 12/31/2006
37228 464 N/A
38686 464 N/A
40161 464 N/A
41659 464 12/31/2007
43175 464 12/31/2007
44702 464 12/31/2007
46238 464 12/31/2007
47817 464 12/31/2007
49411 464 12/31/2007
51051 464 12/31/2007
52720 464 12/31/2007
54387 464 02/29/2008
56059 464 02/29/2008
57751 464 02/29/2008
59461 464 03/31/2008
61183 464 12/31/2008
62918 464 12/31/2008
64714 464 12/31/2008
66535 464 12/31/2008
68362 464 12/31/2008
69598 464 12/31/2008
71461 464 12/31/2008
73087 464 12/31/2008
74981 464 12/31/2008
80233 464 02/28/2009
82131 464 03/31/2009
84038 464 04/30/2009
86543 464 04/30/2009
89649 464 05/30/2009
93472 464 N/A
97787 464 02/28/2010
103059 464 02/28/2010
108722 464 02/28/2010
114657 464 02/28/2010
120772 464 02/28/2010
127225 464 02/28/2010
133869 464 02/28/2010
143918 464 02/28/2010
147737 464 N/A
153125 464 N/A
165164 464 N/A
167651 464 N/A
176506 464 03/31/2011
181542 464 03/31/2011
190028 464 03/31/2011
196713 464 03/31/2011
210988 464 03/31/2011
217093 464 03/31/2011
222838 464 03/31/2011
228731 464 03/31/2011
234266 464 03/31/2011
241882 464 04/30/2011
246883 464 N/A
256656 464 03/31/2012
263805 464 03/31/2012
271642 464 03/31/2012
278709 464 03/31/2012
281227 464 03/31/2012
287092 464 03/31/2012
January 11, 2015 at 6:00 pm
Are you saying that you only want the data from the row that has the MAX Carat_ID for account_id = 464?
--Jeff Moden
January 11, 2015 at 6:27 pm
Sorry for the confusion, I added in account_id =464 in the where clause to limit the output since the data set is pretty large, figured if I could solve for the worst offender,....
I spent a good deal of time searching the forum and just found an answer from a prior post, I think here is what I have, omitted GROUP BY AND ORDER BY account_id ASC
SELECT b.carat_id
,b.account_id AS 'Account ID'
,ISNULL(CONVERT(VARCHAR(10),b.FO_LOC_expiry_date,101),'N/A') AS 'LOC Expiration Date'
,ISNULL(b.work_program,0) AS 'Work Program'
,b.cy_fin_worksheet_id AS 'Current Year Financial Worksheet ID'
,ISNULL(CONVERT(VARCHAR(10),b.cy_statement_date,101),'N/A') AS 'Current Year Statement Date'
,b.cy_working_capital AS 'Current Year Working Capital'
,b.cy_wc_case AS 'Current Year Working Capital Case'
,b.cy_net_worth AS 'Current Year Net Worth'
,b.cy_nw_case AS 'Current Year Net Worth Case'
,b.cy_current_ratio AS 'Current Year Current Ratio'
,b.cy_UBWC AS 'Current Year UBWC'
,b.cy_debt_worth AS 'Current Year Debt Worth'
,b.cy_bank_debt_worth AS 'Current Year Bank Debt Worth'
,b.cy_revenue AS 'Current Year Revenue'
,b.cy_net_income AS 'Current Year Net Income'
,b.cy_np_pct AS 'Current Year Net Profit Percent'
,b.cy_LQWC AS 'Current Year LQWC'
,b.cy_UBNW AS 'Current Year UBNW'
,b.cy_cash_flow_operations AS 'Current Year Cash Flow Operations'
,b.cy_cash_flow_investments AS 'Current Year Cash Flow Investments'
,b.cy_cash_flow_financing AS 'Current Year Cash Flow Financiang'
,b.py_fin_worksheet_id AS 'Prior Year Financial Worksheet ID'
,ISNULL(CONVERT(VARCHAR(10),b.py_statement_date,101),'N/A') AS 'Prior Year Statement Date'
,ISNULL(b.py_working_capital,0) AS 'Prior Year Working Capital'
,ISNULL(b.py_wc_case,0) AS 'Prior Year Working Capital Case'
,ISNULL(b.py_net_worth,0) AS 'Prior Year Net Worth'
,ISNULL(b.py_nw_case,0) AS 'Prior Year Net Worth Case'
,ISNULL(b.py_revenue,0) AS 'Prior Year Revenue'
,b.py_net_income AS 'Prior Year Net Income'
,b.one_yr_EDF AS '1 Year EDF'
,b.five_yr_EDF AS '5 year EDF'
,b.pctile_retained_earnings_to_assets AS 'Reatained Earnings to Assets'
,b.pct_of_bond_value AS 'Percent of Bond Value'
,b.LQWC_score AS 'LQWC Score'
,b.NP_score AS 'NP Score'
,b.UBNW_score AS 'UNBW Score'
,ISNULL(b.CARAT_score,0) AS 'Carat Score'
,b.ERM_max_gross_exposure as 'Max Gross Exposure'
,b.ERM_probable_gross_exposure AS 'Probable Gross Exposure'
,b.max_issued_class_code_id AS 'Max Issued Class Code'
,b.total_bond_premium AS 'Total Bond Premium'
,b.bond_exposure_maxissuedclasscode AS 'Bond Exposure For Max Issued Class Code'
,ISNULL(c.value_desc,'N/A') AS 'Indemnity Type'
FROM CARAT b
INNER JOIN (
SELECT account_id, MAX(carat_id) AS 'Current Carat_ID'
FROM Carat
GROUP BY account_id
) b2 ON b.carat_id = b2.carat_id AND b.account_id = b2.account_id
INNER JOIN lookup c
ON b.lu_account_indemnity_type = c.column_value and c.column_name = 'lu_account_indemnity_type'
OUTPUT
carat_id Account ID LOC Expiration Date
89577 224 N/A
539973 234 N/A
143954 399 N/A
539972 400 N/A
539971 405 N/A
60566 407 N/A
120698 412 N/A
539969 413 08/31/2015
539968 415 N/A
539966 416 N/A
539964 457 05/31/2015
224290 458 02/28/2011
31470 459 N/A
539803 462 N/A
539962 463 06/30/2015
287092 464 03/31/2012
539950 470 N/A
538137 471 06/30/2015
188935 472 N/A
539949 474 N/A
539948 475 N/A
539947 476 N/A
539802 477 05/31/2015
539945 478 N/A
539944 479 N/A
539941 480 N/A
539800 481 N/A
539939 482 09/30/2015
539938 483 06/30/2015
539799 485 07/31/2015
539936 486 07/31/2015
539798 487 N/A
539935 488 06/30/2015
January 11, 2015 at 6:48 pm
rickyschroder (1/11/2015)
...I think here is what I have, omitted GROUP BY AND ORDER BY account_id ASC
That's where I was going with this and why I asked about the Account_ID. Glad you sussed it, though. Because you spent so much time researching the problem, it's one of those things that you won't soon forget. 😀
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply