need to modify this SQL

[Cooperdam]

I have to modify this SQL. I need it to total for an entire year the BudgetAmount. Currently it is only totaling for June. I also need the entire expenses for the year.

@CUSTNMBR VARCHAR(50),
@CURRDATE DATETIME
AS

CREATE TABLE #TEMP (CUSTNMBR VARCHAR(50), BUDGET_MONTH INT, BUDGET_YEAR INT, BUDGET_AMOUNT MONEY)

DECLARE @USCATVAL VARCHAR(50), @I INT, @SQL VARCHAR(100), @Custnmbr1 varchar(50)

select top 1 @Custnmbr1 = Custnmbr from rm00101 where custname = @CUSTNMBR



SET @I = 1
WHILE (@I <= 12)
BEGIN



INSERT INTO #TEMP
SELECT @CUSTNMBR, @I, YEAR(@CURRDATE), Sum(ISNULL(BUDGETAMOUNT,0))
BUDGETAMOUNT FROM CustomerBudget WHERE CUSTNMBR = @Custnmbr1 AND
CONVERT(DATETIME,CONVERT(VARCHAR(2),@I) + '-' + '06-' +
CONVERT(VARCHAR(4),YEAR(@CURRDATE))) BETWEEN BUDGETDATEFROM AND
BUDGETDATETO
Group by CUSTNMBR

SET @I = @I + 1
END


SELECT A.CUSTNAME, left(datename(month,dateadd(month, A.DOC_MONTH - 1, 0)),3) as MonthName, B.BUDGET_AMOUNT, A.EXPENSE
FROM dbo.View_CHART1 A Right Outer JOIN #TEMP B ON
A.CUSTNAME = B.CUSTNMBR AND A.DOC_MONTH = B.BUDGET_MONTH AND A.DOC_YEAR = B.BUDGET_YEAR
WHERE A.DOC_YEAR = YEAR(@CURRDATE) AND A.CUSTNAME = @CUSTNMBR ORDER BY BUDGET_MONTH


DROP TABLE #TEMP

 

[JeffModen]

Isn't that what you told it to do?

CONVERT(DATETIME,CONVERT(VARCHAR(2),@I) + '-' + '06-' +

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[Cooperdam]

yes that part is true.

 

[bborissov]

And if you post some example data and want you want as a result from it, it will be great

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[drlex]

I'm with Jeff; your looping with @I < 12 and the construct "CONVERT(DATETIME,CONVERT(VARCHAR(2),@I) + '-' + '06-' + CONVERT(VARCHAR(4),YEAR(@CURRDATE)))" would pull in data with dates between 1-06-yyyy and 12-06-yyyy, which would be the first 12 days of June on a ddmmyyyy basis.

soi là, soi carré