September 2, 2014 at 10:18 am
Okay, what I am trying to do is I am trying to get all the Accounts that are ENROLLED. This is my first actual report outside from school so please bare with me because I am not experienced.
I have 4 tables.
The tAuditTracking has all of the users info as follow:
ID Contract_ID IP Address LoginID Activity Step
1 376877 192.111.4.100 sam@gmail 1 Login
2 376878 192.111.4.100 Bob@gmail 2 Enroll
3 376879 192.111.4.100 Tom@gmail 3 LogOut
ActivityStatus
1
1
2
The Second is tlkCustPortalStatus
StatusID Portal Status
1 Pass
2 Fail
The Third table is tlkCustPortalActivities
ActivityID Portal Activity
1 Login
2 Enroll
3 Logout
The last table is tcontract
Contract_ID Account Number
376877 34104253
376878 35104252
376879 31104257
This is my code. I know its horrible but this is my first report. So please any tips or help would be appreciated
ALTER PROC spCustPortalEnrolledAccounts
(@ENROLLED INT)
As
Begin
SELECT A.ID, A.CONTRACT_ID, 'A.IP ADDRESS',A.DATE,A.LOGINID,A.ACTIVITY,A.STEP,A.ACTIVITYSTS,A.ADDITIONALDATA,B.ENROLL
FROM tAuditTracking A
JOIN tlkCustPortalActivities B ON B.ActivityID = A.Contract_id
WHERE B.ActivityID = 2
END
GO
EXEC spCustPortalEnrolledAccounts'2'
September 2, 2014 at 10:53 am
Think through what columns you want to return and where you're going to start. Consider where the column you need come from and that'll give you what tables you need to join. Then, from the starting table, join in the tables you need based on what columns match in each pair of tables.
I don't know if this is what you're after, but this will bring the audit table id, audit contract id and account number for the audit rows with an activity row that is 2 (Enroll). I don't know if I have the joins correct, as I just used the column names.
select aud.ID, aud.contract_id, cont.account_number
from tAuditTracking aud
inner join tContract cont on aud.contract_id = cont.contract_id
inner join tlkCustPortalActivities act on aud.activity_id = act.activity
where act.activity_id = 2;
HTH
September 2, 2014 at 11:09 am
Same question was asked as SO. http://stackoverflow.com/questions/25628199/getting-the-correct-joins
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply