Selecting certain data from a table

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting certain data from a table

Author

Topic

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-01-24 : 18:44:37

I have a stored proc that creates a temp table,creates some data and I want to output the results. It works fine. The parameters are the project id, a starting period, and an ending period. The stored proc gathers credit/debit information, and I create a starting balance based on the start period entered. So, the first thing is that I create the table based on the project id. Then, I update the table to create a starting balance. Then this temp table gets selected using the start and end periods. My problem is that, even if there was no activity for the start and end periods entered, I still need to see the start Balance, and some other basic information. The stored proc code is below, so if anyone has any ideas, I would really appreciate it!

CREATE PROCEDURE [dbo].[sp_Balance_Sheet_1400]
@project_ID varchar(6), @startPeriod char(6), @endPeriod char(6)
AS

BEGIN
SET NOCOUNT ON;

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 = @project_ID)

Update t
Set t.beginBal = (Select Sum([dramt]-[cramt])from #tbl_BalanceSheet where projectId=t.projectId AND perpost < @startPeriod),
padmName = (Select formalName from tblAbraData where projectId=t.projectId AND pen = padm),
rpmName = (Select formalName from tblAbraData where projectId=t.projectId AND pen = rpm),
projSite = (select case when count(distinct pe_id02) = 1 then min(pe_id02) else 'Many' end as [projSite]
from PJPent where project = t.projectId)
FROM #tbl_BalanceSheet t

Select * from #tbl_BalanceSheet
where perpost >=@startPeriod and perpost <=@endPeriod
ORDER BY perpost

DavidD
Yak Posting Veteran

73 Posts

Posted - 2008-01-24 : 22:19:44

Not 100 percent sure I understand your problem, but if you want to see data outside of your allocated periods you could chuck in an 'or' statement

ie
Select * from #tbl_BalanceSheet
where (perpost >=@startPeriod and perpost <=@endPeriod)
or startbalance = bla bla
ORDER BY perpost

alternatively you could split your table so that the info you always need to see is in one table and then do a left outer join to the data that you only need to see if it exists.

Something like:

select t1.startbalance, t1.whatever, t2.startdate, t2.enddate
from tblalwaysneeded t1 left outer join
(select * from tblSometimesNeeded where perpost >=@startPeriod and perpost <=@endPeriod) t2
on t1.projID = t2.projID (not sure what your link would be)

Hope this helps
David

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-01-25 : 12:45:36

That does help. I have divided the data into two separate tables, but the join statement is only giving me info from the first table, regardless of whether there is data for the second - is there something else I am missing?

select t1.projectId, t1.project_desc, t1.customer, t1.projSite, t1.status_pa, t1.rpmName,
t1.padmName, t1.begin_date, t1.end_date, t1.beginBal
from #tbl_HeaderInfo t1 left outer join
(select * from #tbl_BalanceSheet where perpost >=@startPeriod and perpost <=@endPeriod) t2
on t1.projectID = t2.projectID

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-01-25 : 12:55:07

I got it! I played around a bit with the syntax, and got it working! Thanks so much for your help :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources