Show all data only if specific data meets criteria

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

Show all data only if specific data meets criteria

Author

Topic

blund
Starting Member

23 Posts

Posted - 2012-03-12 : 16:45:48

This will probably help in several areas.
For example, show me the documents if and only if the first one is less then 365 days old, or in other words, I would like to see all invoices for a group of customers only if there are no invoices older than 1 year for those customers

Here is the base code, showing the tables and columns I am using to simply get all the data from 2 joined tables.


SELECT  GemSQL.dbo.Invoice_Header.Customer,
        GemSQL.dbo.Invoice_Header.Document_Date,
        GemSQL.dbo.Invoice_Detail.Amount

FROM   GemSQL.dbo.Invoice_Header INNER JOIN
       GemSQL.dbo.Invoice_Detail ON
       GemSQL.dbo.Invoice_Header.[Document] = 
       GemSQL.dbo.Invoice_Detail.[Document]

ORDER BY GemSQL.dbo.Invoice_Header.Customer

I can filter and show the invoices using ">getdate()-365", but I am not sure how to show them only if the very first one is less then 1 year old.

Thank you

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-12 : 18:25:18

[code]SELECT ih.Customer,
ih.Document_Date,
id.Amount
FROM GemSQL.dbo.Invoice_Header ih INNER JOIN GemSQL.dbo.Invoice_Detail id ON ih.[Document] = id.[Document]
INNER JOIN (SELECT Customer, MIN(DocumentDate) as EarliestInvoice FROM GemSQL.dbo.Invoice_Header GROUP BY Customer) AS Earliest on Earliest.Customer = ih.Customer
WHERE Earliest.EarliestInvoice > DATEADD(yy,-1,getdate())
ORDER BY GemSQL.dbo.Invoice_Header.Customer[/code]

--
Gail Shaw
SQL Server MVP

blund
Starting Member

23 Posts

Posted - 2012-03-12 : 18:50:49

Great!
Thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources