May 13, 2012 at 3:52 pm
Hi, I am trying to put together a new query to pull urgent information.
These are my needed fields:
CompanyNumber, CompanyName, CompanyType, count of documents in Table2 where OpenedDate is within past 3 years, count of documents in Table1 where workType='success'
There are 2 tables: Table1 & Table2
These tables can be joined by CompanyNumber
These are my selection conditions:
1. CompanyProfile = 'Low'
OR
2. ComapnyFinStat='Debt'
OR
3. company was penalized for 3 consecutive years ( use the datePenal field in Table1)
How can I get a count of the documents in Table2 based upon OpenedDate ?
select y1.CompanyNumber, y1.CompanyName, y1.CompanyType
from Table1 y1
inner join
Table1 y2 on y1.CompanyNumber = y2.CompanyNumber and year(y2.datePenal) = year(y1.datePenal) + 1
inner join
Table1 y3 on y2.CompanyNumber = y3.CompanyNumber and year(y3.datePenal) = year(y1.datePenal) + 2
inner join
Table2 on y1.CustomerNumber = Table2.CustomerNumber
where
y1.CompanyProfile = 'Low' or
y1.ComapnyFinStat='Debt'
May 13, 2012 at 4:02 pm
To help you, and to assist those who wish to help you with a tested answer, please, please post table definitions, some sample data for each table, and the required results from the sample data.
To do this click on the first link in my signature block, in the article there is the T-SQL code to post the table definitions, sample data,etc.
May 13, 2012 at 4:37 pm
I do wish to post sample data, but, however, my employer has very private info and this would stir trouble. Can we please take this one step at a time?
The 3 conditions are as follows:
step 1 - I can do and is simple
step 2 - same as step 1
step 3 - I believe I have almost. I need assistance in writing code to combine the 3 coditions and the count of docs in Table2.
May 13, 2012 at 5:14 pm
I would probably try something like this:
SELECT CompanyNumber,
CompanyName,
CompanyType,
(
Select Count(*) From Table2 t2
Where t2.CompanyNumber= t1.CompanyNumber
And t2.OpenedDate < DateAdd(yy, -3, getdate())
) As Count3yrDocs,
(
Select Count(*) From Table1 t12
Where t12.CompanyNumber= t1.CompanyNumber
And t12.workType = 'success'
) As CountScsDocs,
FROM Table1 t1
WHERE CompanyProfile = 'Low'
OR ComapnyFinStat = 'Debt'
OR EXISTS
(
Select *
From Table1 t12
Join Table1 t13
ON t12.CompanyNumber = t13.CompanyNumber
Where t12.CompanyNumber = t1.CompanyNumber
And year(t12.datePenal) = year(y1.datePenal) + 1
And year(t13.datePenal) = year(y1.datePenal) + 2
)
Or course, without definitions, I cannot test it, so you definitely should.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 13, 2012 at 10:45 pm
pgmr1998 (5/13/2012)
I do wish to post sample data, but, however, my employer has very private info and this would stir trouble. Can we please take this one step at a time?The 3 conditions are as follows:
step 1 - I can do and is simple
step 2 - same as step 1
step 3 - I believe I have almost. I need assistance in writing code to combine the 3 coditions and the count of docs in Table2.
We know how some clients are about sensitive data. That is why we are asking you to post "sample data" not the "actual data". Just make up some data and post it.
It would help us in providing a good solution to your requirement and in very little time, if we have some sample data to work with.
You do have the logic of the query. Without sample data we also can only provide you with a logic which, in your situation, might or might not work.
But if you give us some sample data then we can check the solutions we might be able to provide and come up with a Working logic for you.
Please help us help you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply