quote:

---

Originally posted by bmahony993

I have a stored procedure, and the user enters a projectId, start date and end date. The sp does some calculations and also works out a beginning balance. However, if the user wants to use a wildcard for the projectId, I don't know how to make the "beginBal" calculation for each project. Below is part of the code I have. Any help would be greatly appreciated :)

Create Table #tbl_BalanceSheet (projectId char(6),
project_desc char(100),
customer char(10),
projSite char(10),
status_pa char(10),
rpm char(5),
rpmName char(50),
padm char(5),
padmName char(50),
begin_date char(12),
end_date char(12),
JrnlType char(4),
TranType char(3),
TranDate char(12),
PerEnt char(10),
RefNbr char(10),
batNbr char(10),
perpost char(6),
acct char(4),
trandesc char(100),
beginBal money,
cramt money,
dramt money
)


INSERT INTO #tbl_BalanceSheet(projectId, project_desc, customer,projSite, status_pa, rpm, rpmName, padm, padmName, begin_date, end_date, JrnlType, TranType,
TranDate, PerEnt, RefNbr, batNbr, perpost, acct, trandesc, beginBal,cramt, dramt)
SELECT g.projectId, p.project_desc, p.customer, NULL, p.status_pa, p.manager1, NULL, p.manager2, NULL, p.start_date, p.end_date, g.JrnlType, g.TranType, g.TranDate, g.PerEnt,
g.RefNbr, g.batNbr, g.perpost, g.acct, g.trandesc,0, g.cramt, g.dramt
FROM GLTran g INNER JOIN
PJPROJ p ON
g.projectid = p.project
WHERE g.acct = 1400 and g.posted = 'p'
AND (g.projectId LIKE @project_ID + '%')

Update #tbl_BalanceSheet
Set beginBal = (Select Sum([dramt]-[cramt])from #tbl_BalanceSheet where perpost < @startPeriod),
padmName = (Select formalName from tblAbraData where pen = padm),
rpmName = (Select formalName from tblAbraData where pen = rpm),
projSite = (Select distinct pe_id02 from PJPENT where project LIKE @project_ID)

---

quote:

---

Originally posted by bmahony993

That almost works, but seems to only return the beginBal for the first projectId - any others in the dataset are getting a null value?

---

Create Table #tbl_BalanceSheet (projectId char(6),
project_desc char(100),
customer char(10),
projSite char(10),
status_pa char(10),
rpm char(5),
rpmName char(50),
padm char(5),
padmName char(50),
begin_date char(12),
end_date char(12),
JrnlType char(4),
TranType char(3),
TranDate char(12),
PerEnt char(10),
RefNbr char(10),
batNbr char(10),
perpost char(6), 
acct char(4),
trandesc char(100),
beginBal money,
cramt money,
dramt money
)


INSERT INTO #tbl_BalanceSheet(projectId, project_desc, customer,projSite, status_pa, rpm, rpmName, padm, padmName, begin_date, end_date, JrnlType, TranType,
TranDate, PerEnt, RefNbr, batNbr, perpost, acct, trandesc, beginBal,cramt, dramt)
SELECT g.projectId, p.project_desc, p.customer, NULL, p.status_pa, p.manager1, NULL, p.manager2, NULL, p.start_date, p.end_date, g.JrnlType, g.TranType, g.TranDate, g.PerEnt,
g.RefNbr, g.batNbr, g.perpost, g.acct, g.trandesc,0, g.cramt, g.dramt
FROM GLTran g INNER JOIN
PJPROJ p ON 
g.projectid = p.project 
WHERE g.acct = 1400 and g.posted = 'p'
AND (g.projectId LIKE @project_ID + '%') 

Update tbs
Set tbs.beginBal =  Sum(CASE WHEN perpost < @startPeriod THEN tbs.[dramt]-tbs.[cramt] ELSE 0 END),
tbs.padmName = tad1.formalName ,
tbs.rpmName = tad2.formalName ,
tbs.projSite = p.pe_id02
FROM #tbl_BalanceSheet tbs
INNER JOIN tblAbraData tad1
ON tad1.pen =tbs.padm
INNER JOIN tblAbraData tad2
ON tad2.pen =tbs.rpm
INNER JOIN PJPENT p
ON p.project=tbs.projectid

quote:

---

Originally posted by bmahony993

That doesn't work either - "an aggregate may not appear in the set list of an update statement". I tried moving the SUM into the CASE statement but still got the same error :(

---

quote:

---

Originally posted by bmahony993

That still only gives me a beginning balance for the first project, with NULL for the others that follow. I wonder if it just can't be done this way at all...Thanks for all your suggestions though :)

---

quote:

---

Originally posted by bmahony993

There is definitely a value in cramt and dramt - even if the value is 0. I will have to check the data some more - there must be something I am not seeing that is causing the NULL for some values. Again, thanks so much for your help!

---