SQL Query joining two tables. 1

[nakkii]

I am stumped on trying to write a query that will accomplish my desired output.

What I have is two tables, the first contains an account number (and other specific details), the second contains a record of statements recieved. There should always be one statement recieved each year. I am wanting an output to show me which accounts have NOT recieved a statement and for which year.

Here is data in table 1:

AcctNo Active FirstYr
139 1 2001
214 1 2003
536 1 2004
758 1 2000

Here is data in Table 2:

AcctNo Year RecvDate
214 2003 1/12/2004
214 2004 1/2/2005
536 2004 12/20/2003
758 2004 1/3/2005

Desired Output:

AcctNo Year RecvDate
139 2003 NULL
139 2004 NULL
214 2003 1/12/2004
214 2004 1/2/2005
536 2004 12/20/2003
758 2003 NULL
758 2004 1/3/2004

From that I can then generate my report that show which accounts have not recieved a statement based on plan year.

I am sure I just missing something simple, or trying to do to much with a single query.

Can someone help or at least point me in the right direction.

Thanks.

Persistence....Nothing in the world can take the place of persistence. Talent will not; nothing is more common than unsuccessful men with talent. Genius will not; unrewarded genius is almost a proverb. Education will not; the world is full of educated derelicts. Persistence and determination alone are omnipotent. -Calvin Coolidge

 

[SQLSister]

ID this something that will only run once or will it be periodically run? Do you have other things which might run aganst the individual active years of an account?

Right now the only solution I see is to use a cursor to populate a table called ActiveYears (or #Active Years) to get the actual years each account was active. Then join that table on customerID and Year to table2.

If you need this as a permamanet solution, create a job to run on 1 Jan to populate the active years table for the new year. And then have an insert trigger which populates this table every time you insert a new record. At least that way you will only have to run the cursor once, not every time you need to query the data this way.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[nakkii]

Hmm, I like that. It needs to be a permanent solution able to provide reports in real time, so creating the job to run Jan 1 works. However you confused me on the trigger, what is the trigger for?

I created a table called tblAcctActiveYear and populated like so:

AcctNo ActiveYear
139 2001
139 2002
139 2003
139 2004
214 2003
214 2004
536 2004
758 2000 etc...

Then created a view:

SELECT dbo.Table1.AcctNo, dbo.Table2.RecvDate, dbo.tblAcctActiveYear.ActiveYear
FROM dbo.Table2 RIGHT OUTER JOIN
dbo.tblAcctActiveYear ON dbo.Table2.PlanYear = dbo.tblAcctActiveYear.ActiveYear AND
dbo.Table2.AcctNo = dbo.tblAcctActiveYear.AcctNo RIGHT OUTER JOIN
dbo.Table1 ON dbo.tblAcctActiveYear.AcctNo = dbo.Table1.AcctNo


By doing a paramaterized query against the view I get a list of plans that didn't get a statement.

Thanks Very Much SQLSister, just wish I asked my question sooner.




Persistence....Nothing in the world can take the place of persistence. Talent will not; nothing is more common than unsuccessful men with talent. Genius will not; unrewarded genius is almost a proverb. Education will not; the world is full of educated derelicts. Persistence and determination alone are omnipotent. -Calvin Coolidge

 

[SQLSister]

In the permanent solution you need to make sure it autoamtically maintains itself. I presume that duringthe year, new accounts are opened. When they are, then they need to be added to the Active years table. A trigger will do this every time you insert a record and you won't have to think about it and your data is always up-to-date.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[nakkii]

:~/

Ouch, that threw a big monkey wrench in things... Trigger won't work for our environment (at least not easily) but running the job on the first of the month for the prior month Account Year End should work nicely.

Thanks again.



Persistence....Nothing in the world can take the place of persistence. Talent will not; nothing is more common than unsuccessful men with talent. Genius will not; unrewarded genius is almost a proverb. Education will not; the world is full of educated derelicts. Persistence and determination alone are omnipotent. -Calvin Coolidge

 

[SQLSister]

Or you can change the insert stored procedure if all your inserts are done by sp to always insert into the other table as well. Then you still have real time data.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief