How can Column data fetch at a time once according to date

Question

0.00/5 (No votes)

See more:

Table Name:- IndividualSalaryParameters

ISPID SPID EMPNO  AMOUNT 	SUBMISSIONDATE 	MONTHNO TYPE
11		8	1		 	 2000			2013-02-01 00:00:00.000	2/2013		0
12		8	2	         	 300	  		2013-06-01 00:00:00.000	5/2013		0
13		8	1	      		 1500			2013-06-19 00:00:00.000	6/2013		0
14		8	1		 	 2500			2013-05-19 00:00:00.000	6/2013		0

Here amount us dedection amount. Please checkout stored procedure.

I have problem in my query

SQL

if exists(select EmployeeNo from IndividualSalaryParameters where EmployeeNo=@EmpID and SPID=8)
begin
	set @MaxDeductSalary_Date=(select max(SubmissionDate) from IndividualSalaryParameters where SubmissionDate<=@Date and EmployeeNo=@EmpID and SPID=8)
	SELECT @MaxDeductSalary_Date
set @DeductSalary=(select amount from IndividualSalaryParameters where SubmissionDate<=@MaxDeductSalary_Date and EmployeeNo=@EmpID and SPID=8)
print @DeductSalary
end

In my query return More than one row ........
I want to Output data According to SubmissionDate i e -submission date=2013-02-01 then fetch only amount=2000,
submissiondate=2013-06-19 then fetch only amount=1500 and so on means only one record at selected submission date

how can alter in my query Please suggest Sir

Posted 12-Sep-13 21:21pm

Faizymca

Updated 12-Sep-13 21:26pm

Maciej Los

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2013-09-12T21:30:00

Solution 1

It should be something like this:

SQL

SELECT TOP(1) *
FROM IndividualSalaryParameters
WHERE SubmissionDate=@subdate AND Amount=@amount
ORDER BY ISPID DESC

Posted 12-Sep-13 21:30pm

Maciej Los

Comments

Faizymca 13-Sep-13 7:24am

Thanx for reply ..Maciej los sir, actually here when i execute this type table only return the top row data like 2000
i need according to return data condition of SubmissionDate and EmpNo column exact match of related Amount Column...

ArunRajendra · Answer 2 · 2013-09-12T21:32:00

Solution 2

I guess this is the problem.

Change the condition SubmissionDate<=@MaxDeductSalary_Date to SubmissionDate=@MaxDeductSalary_Date

Posted 12-Sep-13 21:32pm

ArunRajendra

Comments

Faizymca 13-Sep-13 7:26am

Thanx for reply ..Maciej los sir, actually here when i execute this type table only return the first row SubmissionDate and return data column every time like 2000
i need according to return data condition of SubmissionDate and EmpNo column exact match of related Amount Column...

ArunRajendra 13-Sep-13 9:01am

You have replied to wrong answer.

Faizymca 14-Sep-13 7:39am

Sorry Arun Sir this answare related to query of Maciej sir....

Faizymca 17-Sep-13 6:19am

set @DeductSalary=(select amount from IndividualSalaryParameters where SubmissionDate<=@MaxDeductSalary_Date and EmployeeNo=@EmpID and SPID=8)

Arun SIr whenever i execute this query if amount 1200 for month of 01-jan-2013 then it's ok if i go for next month like amount 1500 of 01-feb-2013 and then some msg are show "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. 0 0 " pls find where i am wronge ??

ArunRajendra 18-Sep-13 1:26am

post your query and sample data.

Faizymca 18-Sep-13 7:26am

Thanks sir...for reply actually i have solve...this problem .... thanx give me suggesion of ur query based....