question

sheikhparvaz avatar image
sheikhparvaz asked

HI, I have this procedure but it is not giving correct result. Kindly help

ALTER PROCEDURE [dbo].[abc] ( @dateFrom as datetime= '2014-01-01', @dateTo as datetime= '2014-12-31', @empCode as int = 5569 ) AS BEGIN SET NOCOUNT ON; begin declare @Annual as table (TotalAnnual int,TotalEmergancy int,Totalill int,TotalStudy int) update @Annual set TotalAnnual=( SELECT tblEmployeeVacation.TotalDays FROM tblEmployeeVacation WHERE (empCode = @empCode) AND (vacationRequestStatus = 1) AND (vacationCode = 1) and (tblEmployeeVacation.VacationStartDate between @dateFrom and @dateTo) and (tblEmployeeVacation.VacationEndDate between @dateFrom and @dateTo)) update @Annual set TotalEmergancy=( SELECT tblEmployeeVacation.TotalDays FROM tblEmployeeVacation WHERE (empCode = @empCode) AND (vacationRequestStatus = 1) AND (vacationCode = 2) and (tblEmployeeVacation.VacationStartDate between @dateFrom and @dateTo) and (tblEmployeeVacation.VacationEndDate between @dateFrom and @dateTo)) update @Annual set Totalill=( SELECT tblEmployeeVacation.TotalDays FROM tblEmployeeVacation WHERE (empCode = @empCode) AND (vacationRequestStatus = 1) AND (vacationCode = 3) and (tblEmployeeVacation.VacationStartDate between @dateFrom and @dateTo) and (tblEmployeeVacation.VacationEndDate between @dateFrom and @dateTo)) update @Annual set TotalStudy =( SELECT tblEmployeeVacation.TotalDays FROM tblEmployeeVacation WHERE (empCode = @empCode) AND (vacationRequestStatus = 1) AND (vacationCode = 4) and (tblEmployeeVacation.VacationStartDate between @dateFrom and @dateTo) and (tblEmployeeVacation.VacationEndDate between @dateFrom and @dateTo)) select * from @Annual end End
sql-server-2008sql-server-2008-r2
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Not sure what the correct result should be but it will never have any rows. You declare a table variable and never insert anything into it. The updates you have here are pointless because the table variable has no rows.
0 Likes 0 ·
Tom Staab avatar image
Tom Staab answered
It looks like you want to set 4 columns based on your 4 queries. The only difference in the source queries is the value for vacationCode, so I believe a pivot will work nicely. Try this and let me know how it works for you. ALTER PROCEDURE [dbo].[abc] @dateFrom as datetime = '2014-01-01', @dateTo as datetime = '2014-12-31', @empCode as int = 5569 AS BEGIN; SET NOCOUNT ON; SELECT [Annual], [Emergency], [Ill], [Study] FROM ( SELECT tev.empCode , vacationDesc = CASE tev.vacationCode WHEN 1 THEN 'Annual' WHEN 2 THEN 'Emergency' WHEN 3 THEN 'Ill' WHEN 4 THEN 'Study' ELSE 'Unknown' END , tev.TotalDays FROM tblEmployeeVacation tev WHERE tev.empCode = @empCode AND tev.vacationRequestStatus = 1 AND tev.VacationStartDate BETWEEN @dateFrom AND @dateTo AND tev.VacationEndDate BETWEEN @dateFrom AND @dateTo ) t PIVOT ( SUM(TotalDays) FOR vacationDesc IN ([Annual], [Emergency], [Ill], [Study], [Unknown]) ) p ; END;
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I corrected the code. As Sean said, the only problem was I accidentally included the "tev" table alias in front of the aliased column name. Please try again and either respond with additional questions or mark the answer as correct. Thanks.
0 Likes 0 ·
sheikhparvaz avatar image
sheikhparvaz answered
Thanks for reply, but didnt worked actually. Itz showing error at "=" tev.vacationDesc = CASE tev.vacationCode. Actually my table is like this:![alt text][1] [1]: /storage/temp/2519-table.png

table.png (22.1 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That's nice. It would be a lot more helpful if you stated what the error is and don't make us guess. Looking at the code from Tom Stabb you should remove the tev. from the column. I am not a fan of column aliases at the front end. I prefer them after the column with the keyword AS.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.