| Author |
 Topic  |
|
|
pgmr1998
Yak Posting Veteran
USA
66 Posts |
 Posted - 05/13/2012 : 16:28:06
|
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)
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'
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47963 Posts |
Posted - 05/13/2012 : 16:45:47
|
can you post some sample data from tables and then give expected output?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
pgmr1998
Yak Posting Veteran
USA
66 Posts |
Posted - 05/13/2012 : 17:28:17
|
| how could I get a count of documents in Table2 based on OpenedDate within past 3 years? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47963 Posts |
Posted - 05/13/2012 : 19:30:37
|
quote:
Originally posted by pgmr1998
how could I get a count of documents in Table2 based on OpenedDate within past 3 years?
SELECT COUNT(DocumentID) AS Cnt
FROM table
WHERE OpenedDate > = DATEADD(yy,DATEDIFF(yy,0,GETDATE())-3,0)
AND OpenedDate < DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Company Query
