Company Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Company Query

Author

Topic

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2012-05-13 : 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

52326 Posts

Posted - 2012-05-13 : 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

66 Posts

Posted - 2012-05-13 : 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

52326 Posts

Posted - 2012-05-13 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources