Help me Regarding This Qurey......

Question

Dear Brother's I have Table in which records are in following manners..

EmployeeID -- StructureID -- StructureAmount -- DeductionID -- DeductionAmount

01 01 13500 01 70.0

01 02 1500 02 0.0

01 03 1230 03 500

02 01 14500 01 0.0

02 02 1400 02 405

02 03 1100 03 0.0

. . . . .

and So on.. Where StructureID 01 = Basic Salary, 02 Medical, 03 Connvence and DeductionID 01 = IncomeTax, 02 = Leave Fine, 03 = Loan,

Now I want to write a stored procedure which show Data like this....

EmployeeID -- Basic Salary -- Medical -- Connvence -- IncomeTax -- LeaveFine -- Loan -- TotalSalary

01 13500 1500 1230 70.0 0.0 500 15660

02 14500 1400 1100 0.0 405 0.0 16595

. . . . . . . .

and so on,

If any Brother have idea how to slove this qurey then please share it.. I need this very much....

waqar laghari · Answer

Mr. Sontish this is my qurey wrritten on anthor form. and this is prefect qurey. but if some one change the structure then it will populate error. This is not a big qurey but this is llogicall qurey. if you want your problems solution then please read cross tab qurey i hop you will find your answer in it.

waqar laghari · Answer

Mr. Sontish this is my qurey wrritten on anthor form. and this is prefect qurey. but if some one change the structure then it will populate error. This is not a big qurey but this is llogicall qurey. if you want your problems solution then please read cross tab qurey i hop you will find your answer in it.

waqar laghari · Answer

Mr. Sontish this is my qurey wrritten on anthor form. and this is prefect qurey. but if some one change the structure then it will populate error. This is not a big qurey but this is llogicall qurey. if you want your problems solution then please read cross tab qurey i hop you will find your answer in it.

utsav rathour · Answer

That was a very long query. Will take a look at it. Thanks

Santhosh Aravind · Answer

SELECT EmployeeID SUM(CASE WHEN StructureID = '01' THEN StructureAmount ELSE 0 END) as 'Basic Salary' SUM(CASE WHEN StructureID = '02' THEN StructureAmount ELSE 0 END) as 'Medical' SUM(CASE WHEN StructureID = '03' THEN StructureAmount ELSE 0 END) as 'Connvence' SUM(CASE WHEN DeductionID = '01' THEN DeductionAmount ELSE 0 END) as 'Income Tax' SUM(CASE WHEN DeductionID = '02' THEN DeductionAmount ELSE 0 END) as 'Leave Fine' SUM(CASE WHEN DeductionID = '03' THEN DeductionAmount ELSE 0 END) as 'Loan ' SUM(StructureAmount + (-1*DeductionAmount) ) as 'Total Salary' FROM employee GROUP BY EmployeeID

utsav rathour · Answer

I am on the waiting list too. I am searchin for similar thing

waqar laghari · Answer

I am still waiting for good response..... Please help me regarding this problem..