SQL Select All Companies that have been created this month with at least one order

Question

I am trying to write a query that would allow me to get a list of companies that were created this month that at least would have one order.

I have two tables dbo.Companies and dbo.Orders They are linked by CompanyID

I have written a partial script but I cannot figure out how to complete it.

SELECT COUNT(*) FROM dbo.Companies WHERE Month(CreatedDateTime) = MONTH(getDate())

That gives me a list of all the companies that have been created this month but I am confused how to get the final part of 'that have at least one order'.

possible duplicate of [How can an SQL query return data from multiple tables](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) — Tab Alleman, Jun 23 '15 at 13:53
The EXISTS gave me an idea and I wrote the query as such: SELECT COUNT(*) FROM dbo.Companies WHERE Month(CreatedDateTime) = MONTH(getDate()) AND CompanyID IN (SELECT CompanyID FROM dbo.Orders). It seems to work but just confirming that this is an ok method to use? — Saj, Jun 23 '15 at 13:56

Brian Driscoll · Accepted Answer · 2015-06-23T14:00:04.430

2

The query is relatively straightforward:

SELECT COUNT(*) 
FROM dbo.Companies 
WHERE Month(CreatedDateTime) = MONTH(getDate())
AND YEAR(CreatedDateTime) = YEAR(getDate()) 
AND EXISTS (SELECT 1 FROM dbo.Orders WHERE Orders.CompanyID = Companies.CompanyID)

You can also use an Inner Join on Companies and Orders.

Additionally, you need to check both month and year for companies created in the current month. If you only check month, then a Company created in the current month of ANY year will match MONTH(getDate())

edited Jun 23 '15 at 14:00

answered Jun 23 '15 at 13:53

Brian Driscoll

19,373
3
46
65

Using year and month functions as search criteria is a bad coding since no indexes can be used – James Z Jun 23 '15 at 14:27
@JamesZ I agree, but giving such advice is somewhat out of scope for this question. That would be better dealt with when OP comes in with another question asking why his/her query is slow. Who knows, there may not even be indexes in the first place. – Brian Driscoll Jun 23 '15 at 14:28

jarlh · Answer 2 · 2015-06-23T13:59:44.127

1

EXISTS solution:

SELECT * FROM dbo.Companies c
where Month(CreatedDateTime) = MONTH(getDate())
  and exists (select 1 from dbo.Orders o
              where o.CompanyID = c.CompanyID)

JOIN version:

select distinct c.*
FROM dbo.Companies c
  JOIN dbo.Orders o ON o.CompanyID = c.CompanyID
WHERE Month(CreatedDateTime) = MONTH(getDate())

edited Jun 23 '15 at 13:59

answered Jun 23 '15 at 13:54

jarlh

42,561
8
45
63

Month(CreatedDateTime) = MONTH(getDate()) should be outside of the EXISTS operation – Brian Driscoll Jun 23 '15 at 13:58
Yes, I thought the column CreatedDateTime belonged to Orders. Will edit! Thanks! – jarlh Jun 23 '15 at 13:59

score 1 · Answer 3 · answered Jun 23 '15 at 13:54

1

One way would be to use the exists operator:

SELECT *
FROM   dbo.Companies c
WHERE  MONTH(CreatedDateTime) = MONTH(getDate()) AND
       EXISTS (SELECT *
               FROM   dbo.Orders o
               WHERE  c.id = o.companyId)

answered Jun 23 '15 at 13:54

Mureinik

297,002
52
306
350

I suppose the CreatedDateTime column belongs to Orders table? – jarlh Jun 23 '15 at 13:56
1

@jarlh Nope, he's looking for Companies that were created in the current month. – Brian Driscoll Jun 23 '15 at 13:57
The CreatedDateTime belongs to dbo.Companies – Saj Jun 23 '15 at 13:58

SQL Select All Companies that have been created this month with at least one order

3 Answers3