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 |
|
Adam West
Constraint Violating Yak Guru
261 Posts |
 Posted - 2009-11-26 : 16:18:59
|
| 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 DATETIMEASCREATE 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 ENDSELECT 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 ONA.CUSTNAME = B.CUSTNMBR AND A.DOC_MONTH = B.BUDGET_MONTH AND A.DOC_YEAR = B.BUDGET_YEARWHERE A.DOC_YEAR = YEAR(@CURRDATE) AND A.CUSTNAME = @CUSTNMBR ORDER BY BUDGET_MONTHDROP TABLE #TEMP |
|
|
hans.smith
Starting Member
2 Posts |
Posted - 2009-11-26 : 21:23:12
|
| That would be the MODIFY TABLE command.I linked you in the source.Source(s):I am a developer, myself, and:__________ |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Need to modify this SQL