Help with Joining Tables to show record for each Period
-
I'm hoping this is the right forum to post this question in.
I'm trying to create a SQL View which list all records in a table based on another joined table. Sounds simple except here is my problem:
I have a table with account transactions based on a Period ID (ie: 1=Jan, 2=feb, etc..)
However not all accounts have a transaction for every period.
I would like to list all transactions from the table and where there is no transactions for the period I would like a record displayed with 0's for the trx amounts for that account.
I've created a Period ID table and now I'm trying to figure out how to link this so that I can achieve my results.
Any help would be greatly appreciated.
Thanks
Cam
-
Can you try ....
select p.perioidID, isnull(amount,0) from
Period p left outer join transactions t on p.periodid=t.periodid
It will be more helpful, if you can provide some sample data.
-
Try:
CREATE VIEW viewPTrans
AS
Select PeriodIDs.PerId,
IsNull(PTransactions.Transamount,0)
FROM PeriodIds
LEFT OUTER JOIN PTransactions ON PeriodIDs.PerID=PTransactions.PerID
Regards
Mads
-
I posted additional information however it doesn't look like it worked so if I duplicate something it is by accident.
I tried using the suggested joins however as soon as I added additional fields the results did not return what I was expecting.
I apologize for the first go round on this question. Below I have tried to clarify my problem a bit more.
I have 3 tables with the following information:
TRX table contains:
CompanyID
AcctID
PeriodID
TRXDate
TrxYear
Reference
SourceDoc
JrnEntry
DebitAmt
CreditAmt
BBFAmt
Accts Table contains:
CompanyID
AcctID
AcctString
PeriodID Table contains:
PeriodID
PeriodDescr
The query results should include the following:
CompanyID
AcctID
AcctString
PeriodID
TRXDate
TrxYear
JrnEntry
Reference
SourceDoc
DebitAmt
CreditAmt
BBFAmt
The results I need are a record for every AcctID in the Accts table for the company for every PeriodID whether there is a record for a particular PeriodID in the TRX table or not.
For Example if Company ONE has records for every PeriodID except for PeriodID 1 in the TRX table, then I need a record to display for PeriodID 1 as well as the records that exist in the TRX table. The PeriodID record would display blanks for the Strings and Zero's for the Numbers.
I hope this helps to clarify my question.
Thanks for all your help, it's greatly appreciated.
Cam
-
Cam,
Consider the following construction : (You may want to copy/paste text between the dashed lines to your SQL Query Analyzer)
Although this probably returns the results you want, it will not fit into a view ! I use this type of contructs as CommandText in the SelectCommand of a DataAdapter.
But I have as strong feeling you need this for reporting .... So, a view would indeed be cleaner !
Forcing it into a view would involve a cross join ("Select * from TRX, Period") to get to the account data. But I am not sure if this is wise : 10,000 transactions within 10 periods leads to 100,000 records that will still need filtering and matching.
Anyway, have a look at it and if you need more info, do not hesitate.
--------------------------------------------
Declare @CompanyId as Integer
Declare @AcctId as Integer
Set @CompanyId = 99
Set @AcctId = 88
Select Accts.CompanyID
,Accts.AcctID
,Accts.AcctString
,Period.PeriodDescr
,TRX.TRXDate
,TRX.TrxYear
,TRX.JrnEntry
,TRX.Reference
,TRX.SourceDoc
,TRX.DebitAmt
,TRX.CreditAmt
,TRX.BBFAmt
From Accts
Join TRX
On TRX.CompanyID = Accts.CompanyID
And TRX.AcctID = Accts.AcctID
Join Period
On Period.PeriodID = TRX.PeriodID
Where (Accts.CompanyID = @CompanyId Or @CompanyID Is Null)
And (Accts.AcctID = @AcctId Or @CompanyID Is Null)
UNION
Select @CompanyID
,@AcctID
,(Select AcctString
From Accts
Where AcctID = @AcctID
And CompanyID = @CompanyID) as AcctString
,Period.PeriodDescr
,Null as TRXDate
,Null as TrxYear
,Null as JrnEntry
,Null as Reference
,Null as SourceDoc
,0 as DebitAmt
,0 as CreditAmt
,0 as BBFAmt
From Period
Where Not Exists
(Select Null
From Accts
Join TRX
On TRX.CompanyID = Accts.CompanyID
And TRX.AcctID = Accts.AcctID
Where TRX.PeriodID = Period.PeriodID
And (Accts.CompanyID = @CompanyId Or @CompanyID Is Null)
And (Accts.AcctID = @AcctId Or @CompanyID Is Null))
-----------------------------------------------------------
Notice that the Key-Issue here is the use of @CompanyID and @AcctID in the
Select statement following UNION. To my opnion this is the only way to ever be able to return an Account Number : for the simple reason that the info DOES NOT EXIST in the database.
If it didn't help, I hope it gave you some idea's ....
Regards, Garry.
-
It think this is your "viewable" solution ... Again, Copy/Paste into SQL Query Analyzer,
test it, wrap it up in a "Create View" statement.
Because of the embedded and expensive cross join, you may want to test it against a LOADED transaction (TRX) table. Looking at the execution plan may help.
I hope my TableNames/ColumnNames are close to what they should be ...
--------------------
Select Accts.CompanyID
,Accts.AcctID
,Accts.AcctString
,Period.PeriodDescr
,TRX.TRXDate
,TRX.TrxYear
,TRX.JrnEntry
,TRX.Reference
,TRX.SourceDoc
,TRX.DebitAmt
,TRX.CreditAmt
,TRX.BBFAmt
From Accts
Join TRX
On TRX.CompanyID = Accts.CompanyID
And TRX.AcctID = Accts.AcctID
Join Period
On Period.PeriodID = TRX.PeriodID
UNION
Select CompanyID = UnExisting_TRX.CompanyID
, AcctID = UnExisting_TRX.AcctID
, AcctString = (Select AcctString
From Accts
Where CompanyID = UnExisting_TRX.CompanyID
And AcctID = UnExisting_TRX.AcctID)
, PeriodDescr = (Select PeriodDescr
From Period
Where PeriodID = UnExisting_Period.PeriodID)
,Null as TRXDate
,Null as TrxYear
,Null as JrnEntry
,Null as Reference
,Null as SourceDoc
,0 as DebitAmt
,0 as CreditAmt
,0 as BBFAmt
From TRX as UnExisting_TRX
,Period As UnExisting_Period
Where Not Exists
(Select *
From Period As Existing_Period
Join TRX as Existing_TRX
On Existing_Period.PeriodID = Existing_TRX.PeriodID
Where Existing_Period.PeriodID = UnExisting_Period.PeriodID
And Existing_TRX.CompanyID = UnExisting_TRX.CompanyID
And Existing_TRX.AcctID = UnExisting_TRX.AcctID
)
Group By UnExisting_TRX.CompanyID, UnExisting_TRX.AcctID, UnExisting_Period.PeriodID
--------------------
-
Thank you very much for the help, unfortunately it looks like it pulls in some null records but not all.
Some accounts pull in only a few of the PeriodID's.
You are correct this is strictly for reporting purposes.
I believe I need to add a group on TRXYEAR since this a key filtering field. I think I forgot to mention that the records are grouped as follows:
CompanyID, TrxYear, AcctID, PeriodID
Therefore I need (if possible) a record for each PeriodID within a year for each AcctID for a specific CompanyID.
Once again thanks for all the help it's greatly appreciated.
Cam
-
I am supprised that the query does not return all of the Null records.
The attached JPEG shows the tables and their sample content I used while testing, together with the returned resultset.
Concerning the grouping by Year : you're free to apply whatever "Where/Order By/Group By" clauses to the view once you get it right.
So, probably the consumer of the view would look somewhat like this :
Select ....
From vwCamsPuzzle
Where CompanyID = 999
Group By TRXYear, AcctID, PeriodID
I hope you can comforably look at the attached image ... If not I can eMail it to you.
Regards and Happy Easter,
Garry
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply