Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ats
Starting Member
32 Posts |
 Posted - 2009-06-01 : 10:34:32
|
I don't know how best to title my problem, basically I have a stored procedure which is an else if statement which checks to see if the current year has any data, if not use the previous year etc. Where it falls over if none of the years have any data. In which case I would want it to say = 1 so it has a number in there but I ma not sure how to do it. Am I supposed to use the return value.
ALTER procedure [dbo].[allowance]
(
@companyid int = null
)
as
IF (SELECT SUM(compliance.allowanceDistributed) FROM dbo.compliance WHERE Company_ID = @companyid AND year1 = (SELECT YEAR(GETDATE()))) > 0
Begin
SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM dbo.compliance
WHERE Company_ID = @companyid AND year1 = (SELECT YEAR(GETDATE()))
End
else
IF (SELECT SUM(compliance.allowanceDistributed) FROM dbo.compliance WHERE Company_ID = @companyid AND year1 = (SELECT YEAR(GETDATE()))-1) > 0
Begin
SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM dbo.compliance
WHERE Company_ID = @companyid AND year1 = (SELECT YEAR(GETDATE())-1)
End
IF (SELECT SUM(compliance.allowanceDistributed) FROM dbo.compliance WHERE Company_ID = @companyid AND year1 = (SELECT YEAR(GETDATE())-2)) > 0
Begin
SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM dbo.compliance
WHERE Company_ID = @companyid AND year1 = (SELECT YEAR(GETDATE())-2)
end
IF (SELECT SUM(compliance.allowanceDistributed) FROM dbo.compliance WHERE Company_ID = @companyid AND year1 = (SELECT YEAR(GETDATE())-2)) > 0
Begin
SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM dbo.compliance
WHEn allowancedistrbuted = 0 then = 1
WHERE Company_ID = @companyid AND year1 = (SELECT YEAR(GETDATE())-2) |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-01 : 10:58:59
|
[code]ALTER PROCEDURE dbo.uspAllowance
(
@CompanyID INT = NULL
)
AS
SET NOCOUNT ON
DECLARE @allowanceDistributed INT
SELECT @allowanceDistributed = SUM(allowanceDistributed)
FROM dbo.Compliance
WHERE Company_ID = @CompanyID
AND Year1 >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
AND Year1 < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
IF @allowanceDistributed IS NULL
SELECT @allowanceDistributed = SUM(allowanceDistributed)
FROM dbo.Compliance
WHERE Company_ID = @CompanyID
AND Year1 >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)
AND Year1 < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)
IF @allowanceDistributed IS NULL
SELECT @allowanceDistributed = SUM(allowanceDistributed)
FROM dbo.Compliance
WHERE Company_ID = @CompanyID
AND Year1 >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 2, 0)
AND Year1 < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 2, 0)
SELECT @allowanceDistributed
GO[/code]
E 12°55'05.63"
N 56°04'39.26" |
 |
|
|
yonabout
Posting Yak Master
112 Posts |
Posted - 2009-06-01 : 11:00:45
|
How about sticking an extra else after you've tested all the other years?
so else
begin
select 1 as allowanceDistributed
end
?
Cheers,
Yonabout |
|
|
|
Ats
Starting Member
32 Posts |
Posted - 2009-06-01 : 11:49:39
|
| Thanks guys I took the easy option of adding else select 1 as allowanceDistributed, this worked fine and will do for now. |
|
|
|
|
|
|
|
|
If query empty then = 1