asked
selecting the top 10 and bottom 10
I am trying to do some analysis for the max and min 10 limits for each of my departments for each month but the only way I can think of is to use the code below then combining them together but it seems long winded and I dont think it will be very effective is there another way of doing this? many thanks in advance. SELECT top 10 [POLICYNUM] ,[INCPDT] ,SUBSTRING(incpdt,6,2)[MONTH] ,[LIMIT] ,[Narrative] ,[SYSTEM] FROM [LEE].[dbo].[DLMTIVS2] where Narrative = 'Construction' order by narrative, SUBSTRING(incpdt,6,2), LIMIT desc select top 10 [POLICYNUM] ,[INCPDT] ,SUBSTRING(incpdt,6,2)[MONTH] ,[LIMIT] ,[Narrative] ,[SYSTEM] FROM [LEE].[dbo].[DLMTIVS2] where Narrative = 'Construction' order by narrative, SUBSTRING(incpdt,6,2), LIMIT asc
Thanks for the quick response, below is some sample data from the tablewhich has about 1000 rows of data. Sorry if I wasnt completly clear but will this code also work for my other depts as I need to get the max and min for each dept and month. POLICYNUM INCPDT MONTH LIMIT Narrative SYSTEM AFF139127A12 2012-01-25 01 15519482729 Construction DLM AFF140467A12 2012-01-24 01 1400500398.8 Construction DLM AFP141555A12 2012-01-31 01 372948443 Construction DLM AFD117659A12 2012-01-01 01 285012473.82 Construction DLM
@Blackhawk-17 +1. The best help can be given when DDLs, test data and desired output is given? Chance are minimal for syntax and logical errors then.
Sorry please ignore the month column as this is not in the table as I must have copied over the data including the month. The output I want is the top and bottom 10 limits for each month and for each dept.( i have 5 depts i need this for). Thanks again and sorry for not stating this to begin with.
THanks for taking the time to go through this with me. Here is some more sample data from the table I have selected 1 row for each dept. What I want is the 10 max limits and 10 min limits for each dept and for each month I unioned my depts so that all the depts are together. Below is the sample. POLICYNUM INCPDT LIMIT Narrative SYSTEM ACN103390C12 2012-03-01 15190409540 CONSTRUCTION DLM ACN103552C12 2012-03-01 9512556 PROPERTY OTHER DLM ACP094654D12 2012-02-01 3661946438 Property DLM ACR038092K12 2012-05-01 110000000000 ENERGY DLM ACR038092K12 2012-05-01 2480741830 ENERGY OTHER DLM
Have you tried my edited answer? I guess it should give you the desired result.
Don't worry... we'll ask for what we need. It just helps both us and you when as much detail as possible is passed along as early as possible.
Since you need month wise limits, I guess the following could be according to the need ; WITH CTE AS ( SELECT [POLICYNUM] , [INCPDT] , SUBSTRING(incpdt, 6, 2) [MONTH] , [LIMIT] , [Narrative] , [SYSTEM] , ROW_NUMBER() OVER (PARTITION BY SUBSTRING(incpdt, 6, 2) ORDER BY Limit ) LimitAsc , ROW_NUMBER() OVER (PARTITION BY SUBSTRING(incpdt, 6, 2) ORDER BY Limit DESC ) LimitDesc FROM [LEE].[dbo].[DLMTIVS2] WHERE Narrative = 'Construction' ) SELECT * FROM CTE WHERE LimitAsc BETWEEN 1 AND 10 OR LimitDesc BETWEEN 1 AND 10 EDIT: ; WITH CTE AS ( SELECT [POLICYNUM] , [INCPDT] , SUBSTRING(incpdt, 6, 2) [MONTH] , [LIMIT] , [Narrative] , [SYSTEM] , ROW_NUMBER() OVER (PARTITION BY Narrative, SUBSTRING(incpdt, 6, 2) ORDER BY Limit ) LimitAsc , ROW_NUMBER() OVER (PARTITION BY Narrative, SUBSTRING(incpdt, 6, 2) ORDER BY Limit DESC ) LimitDesc FROM [LEE].[dbo].[DLMTIVS2] --WHERE Narrative = 'Construction' NOW NEEDING ALL THE DEPARTMENTS ) SELECT * FROM CTE WHERE LimitAsc BETWEEN 1 AND 10 OR LimitDesc BETWEEN 1 AND 10
@n3w2sql Since you already have the MONTH column, then why are you using SUBSTRING to extract the same? Is it different?
This is a translation to get both results in one run through using a CTE and the windowing function ROW_NUMBER(): ;WITH PreppedData AS (SELECT [POLICYNUM], [INCPDT], SUBSTRING(incpdt, 6, 2) [MONTH], [LIMIT], [Narrative], [SYSTEM], ROW_NUMBER() OVER (ORDER BY narrative, SUBSTRING(incpdt, 6, 2), Limit) MinLimit, ROW_NUMBER() OVER (ORDER BY narrative, SUBSTRING(incpdt, 6, 2), Limit DESC) MaxLimit FROM [LEE].[dbo].[DLMTIVS2] WHERE Narrative = 'Construction') SELECT * FROM PreppedData WHERE Minlimit
Thank you Usman and everyone else for all your time and help now I can understand the logic behind this a lot better and it has fixed what I was trying to do. works perfectly. Thanks again
@n3w2sql Thanks for letting us know. Please mark the helping answer as accepted, which would help the future users as well.
10 People are following this question.
