SQL select where there is only one date

Question

Currently this query:

SELECT     InvoiceNumber, CustomerCode, Amount, InvoiceDate
FROM         Invoice

Returns:

aaa, 111, 5, 07/12/2009
bbb, 111, 5, 07/11/2009
ccc, 222, 5, 07/12/2009

However, I want to write: Where date = '07/12/2009', but I only want to return the results where there the customer has only one invoice...

So Customercode 222 would be returned, but 111 wont, as 111 has invoices from previous months....

Hope you can help! :D

Answer 1

Try this bud

SELECT     InvoiceNumber, CustomerCode, Amount, InvoiceDate, (SELECT count(*) FROM Invoice b WHERE a.CustomerCode=b.CustomerCode AND InvoiceDate = '07/12/2009') as count
FROM         Invoice a
Where InvoiceDate = '07/12/2009'
HAVING count = 1

Answer 2

This should do:

SELECT
    InvoiceNumber, CustomerCode, Amount, InvoiceDate
FROM
    Invoice
WHERE
    COUNT(id) = 1 AND InvoiceDate = '07/12/2009'
GROUP BY
    InvoiceDate

Answer 3

Try this:

SELECT      InvoiceNumber, CustomerCode, Amount, InvoiceDate
FROM        Invoice AS Inv
INNER JOIN  
(
    SELECT CustomerCode
    FROM   Invoice
    GROUP BY CustomerCode
    HAVING COUNT(*) = 1
) AS base
ON        Inv.CustomerCode = base.CustomerCode
WHERE  InvoiceDate = '07/12/2009'

Answer 4

DECLARE @DateParam datetime
SELECT @DateParam = Convert(datetime, '07/12/2009', 103)

SELECT InvoiceNumber, CustomerCode, Amount, InvoiceDate
FROM Invoice
INNER JOIN (SELECT CustomerCode
    FROM Invoice
    GROUP BY CustomerCode
    HAVING Count(*) = 1) AS InvoiceCount ON Invoice.CustomerCode = InvoiceCount.CustomerCode
WHERE InvoiceDate = @DateParam

This will return any where the InvoiceDate is equal to @DateParam, and where there is only one invoice for the customer in the entire Invoice table.

Answer 5

SELECT    i.InvoiceNumber, i.CustomerCode, i.Amount, i.InvoiceDate
FROM      Invoice i
WHERE  i.InvoiceDate = '7/12/2009'
AND    i.InvoiceNumber IN  (
   SELECT MAX(InvoiceNumber)
   FROM   Invoice      
   GROUP BY CustomerCode
   HAVING COUNT(*) = 1
)

Answer 6

You can try something like this

DECLARE @Table TABLE(
     InvoiceNumber VARCHAR(10), 
     CustomerCode VARCHAR(10), 
     Amount FLOAT, 
     InvoiceDate DATETIME
)

INSERT INTO @Table (InvoiceNumber,CustomerCode,Amount,InvoiceDate) SELECT 'aaa', '111', 5, '07/12/2009'
INSERT INTO @Table (InvoiceNumber,CustomerCode,Amount,InvoiceDate) SELECT 'bbb', '111', 5, '07/11/2009'
INSERT INTO @Table (InvoiceNumber,CustomerCode,Amount,InvoiceDate) SELECT 'ccc', '222', 5, '07/12/2009'

SELECT  t.*
FROM    @Table t INNER JOIN
     (
      SELECT CustomerCode
      FROM @Table
      GROUP BY CustomerCode
      HAVING COUNT(CustomerCode) = 1
     ) s ON t.CustomerCode = s.CustomerCode
WHERE   t.InvoiceDate = '07/12/2009'

Answer 7

As suggested, this one seems to not be accepted under SQLSERVER

SELECT * 
FROM Invoice i 
INNER JOIN (
  SELECT CustomerCode, InvoiceDate 
  FROM Invoice 
  WHERE InvoiceDate='07/12/2009' 
  GROUP BY CustomerCode 
  HAVING COUNT(CustomerCode)=1
 ) grp ON (i.CustomerCode=grp.CustomerCode AND i.InvoiceDate=grp.InvoiceDate)

or if the invoice number is unique

SELECT * 
FROM Invoice i 
INNER JOIN (
  SELECT InvoiceNumber 
  FROM Invoice 
  WHERE InvoiceDate='07/12/2009' 
  GROUP BY CustomerCode 
  HAVING COUNT(CustomerCode)=1
 ) grp ON (i.InvoiceNumber =grp.InvoiceNumber)

Answer 8

SELECT InvoiceNumber, CustomerCode, Amount, InvoiceDate 
FROM Invoice
WHERE CustomerCode 
IN (SELECT CustomerCode FROM Invoice WHERE date = '07/12/2009' GROUP BY CustomerCode Having Count(*)=1)

Answer 9

SELECT
  MAX(InvoiceNumber) AS InvoiceNumber
 ,CustomerCode
 ,MAX(Amount) AS Amount
 ,MAX(InvoiceDate) AS InvoiceDate
FROM Invoice
GROUP BY CustomerCode
HAVING COUNT(*) = 1
  AND MAX(InvoiceDate) = '07/12/2009'