Click here to Skip to main content
15,064,140 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Dear Friends,

I am doing an application like naukri or Shine or TimesJobs.
In my application I have 3 tables like Fresher, Working Professional and Student.
Each and every table having column like Technology, Location and Experience. I want to sort out the people using these 3 fields a per my requirement.

Here my question is how can I sort people?
1. Using stored Procedure and how?
2. Using code and how?
THANK YOU Friends,

Regards,
Ram
Posted
Comments
   
Give your these 3 table structure
ramuAlla 19-Jul-12 3:37am
   
Sorry Kaushik ..its confidential and thanks for reply
Sandeep Mewara 19-Jul-12 3:30am
   
And would you like to try anything by yourself too?

In your stored procedure you sort them using ORDER BY or GROUP BY statements. In code is not ideal, you should let the DB do it, but using LINQ you can write code that sorts things very intuitively.
   
Comments
ramuAlla 19-Jul-12 3:24am
   
Thanks For Reply Christian,
Can you please send me sample code in LINQ or Send me sample SP for using Orderby and groupby..
Christian Graus 19-Jul-12 3:27am
   
No. If you think I can based on what you've said ( which does not include your DB schema ), then you don't understand your own question. Also, this is the most basic SQL imaginable, you should not work in a database driven app if you cannot write it. If you read up on how to do it, and get stuck, by all means ask a specific question and I'll gladly help. It's physically impossible to create a copy and paste solution based on what you've said, and I prefer not to do that, because I prefer to help real programmers who, when told what to do, can do some basic research and try for themselves.
Hi.....

Please see the below sample procedure.

SQL
create  procedure [dbo].[PanIndia_Settled_PJ]
(
@Zone varchar(max),
@Branchcode varchar(max),

@LastDate DateTime 
)
AS 
BEGIN

if OBJECT_ID('tempdb..#Detail_PJ') is not null 
drop table #Detail_PJ
if OBJECT_ID('tempdb..#Summary_PJ') is not null 
drop table #Summary_PJ
IF OBJECT_ID('tempdb..#StateList') IS NOT NULL 
DROP TABLE #StateList
IF OBJECT_ID('tempdb..#BranchList') IS NOT NULL 
DROP TABLE #BranchList

Create table #StateList 
		(
			stateName VARCHAR(100) NOT NULL
		)
		INSERT INTO #StateList SELECT IntegerfromList as stateName  from udf_GetListAsTable (@Zone,',')

Create table #BranchList 
		(
			Branchcode VARCHAR(100) NOT NULL
		)
		INSERT INTO #BranchList SELECT IntegerfromList as Branchcode  from udf_GetListAsTable (@Branchcode,',')



select STATENAME ,[BRANCH CODE] ,PRODUCT ,[LOAN APPROVAL DATE] as Apprvddt,[SETTLED DATE] as SettleDate,LOAN_STATUS,
	[UNO LOAN NUMBER],[ADVANCE AMOUNT],NETLNAMT,CONT_IRR,TENOR,convert(Numeric(18,2),round(([ADVANCE AMOUNT] *CONT_IRR * TENOR),0),0) AS WIRR1,
	convert(Numeric(18,2),Round([ADVANCE AMOUNT]*TENOR,0),2)  AS WIRR2,
	round(CONVERT (decimal(12,2), case when datediff(dd,[LOAN APPROVAL DATE] ,[SETTLED DATE]) = 0 then 1 
	else datediff(dd,[LOAN APPROVAL DATE] ,[SETTLED DATE]) end /30.00,2),2) as LnTenor,
	round(CONVERT (decimal(12,2), case when datediff(dd,[LOAN APPROVAL DATE] ,[SETTLED DATE]) = 0 then 1 
	else datediff(dd,[LOAN APPROVAL DATE] ,[SETTLED DATE]) end /30.00,2),2)  as netTenor
 into #Detail_PJ
from CUTOFFMONTHLCC ..CutOffLCC_PJ where Loan_Status ='settled'
and STATENAME in (select stateName from #StateList) and [BRANCH CODE]in (select Branchcode from #BranchList)
 and [SETTLED DATE] <= @LastDate 

Alter table #Detail_PJ add  CustHldyAdvInstlmnt smallint

update #Detail_PJ set CustHldyAdvInstlmnt=b.CustHldyAdvInstlmnt from #Detail_PJ a
join LOln_LnDetails_h b  on a.[UNO LOAN NUMBER]=b.Lnno

-	  
	
select StateName,[Branch Code],Product,CONVERT(VARCHAR(6),Apprvddt,112 )as [Approved Month],Loan_Status,
	count([UNO LOAN NUMBER])As Nos,SUM([ADVANCE AMOUNT])As [ADVANCE AMOUNT],SUM(NETLNAMT) AS [NET LOAN AMOUNT],
	SUM(WIRR1) AS WIRR1,SUM(WIRR2) AS WIRR2,sum(convert(Numeric(18,2),round(Cont_Irr * [ADVANCE AMOUNT]* LnTenor,0),0)) as WGRTENOR1,sum(convert(Numeric(18,2),Cont_Irr*[ADVANCE AMOUNT],0)) as WGRTENOR2, 
  sum(convert(Numeric(18,2),round(Cont_Irr*NETLNAMT*netTenor-CustHldyAdvInstlmnt,0),0)) as WNETTENOR1,sum(convert(Numeric(18,2),Cont_Irr* NETLNAMT,0)) as WNETTENOR2 
	
    into #Summary_PJ
	from #Detail_PJ 
	GROUP BY StateName,[Branch Code],Product,CONVERT(VARCHAR(6),Apprvddt,112 ),Loan_Status
	Order BY StateName,[Branch Code],Product,CONVERT(VARCHAR(6),Apprvddt,112 ),Loan_Status

	select * from #Summary_PJ
End
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900