Author |
Topic  |
|
cardullo4321
Starting Member
USA
40 Posts |
Posted - 04/16/2013 : 22:32:29
|
I am looking to see if any clients paid a newer invoice when the client still has older invoices still open and 4/16/2013 is today.
Client InvoiceNum InvoiceDate StillOpen A A0001 4/1/2013 Y A A0002 4/2/2013 N B A0003 4/3/2013 Y B A0004 4/4/2013 Y C A0005 4/5/2013 N C A0006 4/6/2013 N A A0007 4/16/2013 N B A0008 4/16/2013 N C A0009 4/16/2013 N
The results will show
Client InvoiceNum InvoiceDate StillOpen A A0001 4/1/2013 Y B A0003 4/3/2013 Y B A0004 4/4/2013 Y
Gregory Cardullo |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 04/17/2013 : 00:32:50
|
SELECT Client,InvoiceNum,InvoiceDate,StillOpen
FROM
(
SELECT Client,InvoiceNum,InvoiceDate,StillOpen,
SUM(CASE WHEN InvoiceDate >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND InvoiceDate < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1) AND StillOpen='N' THEN 1 ELSE 0 END) OVER (PARTITION BY Client) AS TodayCnt
FROM Table
WHERE StillOpen='Y'
AND InvoiceDate < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
)t
WHERE TodayCnt >0
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs
|
 |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 04/17/2013 : 11:56:51
|
quote: Originally posted by cardullo4321
I am looking to see if any clients paid a newer invoice when the client still has older invoices still open and 4/16/2013 is today.
Client InvoiceNum InvoiceDate StillOpen A A0001 4/1/2013 Y A A0002 4/2/2013 N B A0003 4/3/2013 Y B A0004 4/4/2013 Y C A0005 4/5/2013 N C A0006 4/6/2013 N A A0007 4/16/2013 N B A0008 4/16/2013 N C A0009 4/16/2013 N
The results will show
Client InvoiceNum InvoiceDate StillOpen A A0001 4/1/2013 Y B A0003 4/3/2013 Y B A0004 4/4/2013 Y
Gregory Cardullo
Hello Gregory, Is this what you are looking for:
CREATE TABLE dbo.tempdatecheck ( Client NVARCHAR(2), InvoiceNum NVARCHAR(8), InvoiceDate DATE, StillOpen NVARCHAR(2) )
INSERT INTO dbo.tempdatecheck(Client, InvoiceNum, InvoiceDate, StillOpen) Values ('A', 'A0001', '4/1/2013', 'Y'), ('A', 'A0002', '4/2/2013', 'N'), ('B', 'A0003', '4/3/2013', 'N'), ('B', 'A0004', '4/4/2013', 'Y'), ('C', 'A0005', '4/5/2013', 'N'), ('C', 'A0006', '4/6/2013', 'N'), ('A', 'A0007', '4/16/2013', 'N'), ('B', 'A0008', '4/16/2013', 'N'), ('C', 'A0009', '4/16/2013', 'Y'), ('A', 'A00010', '4/16/2013', 'N'), ('B', 'A00011', '4/16/2013', 'Y'), ('C', 'A00012', '4/16/2013', 'Y'), ('D', 'A00013', '4/1/2013', 'Y'), ('D', 'A00014', '4/2/2013', 'N'), ('D', 'A00015', '4/5/2013', 'Y'), ('D', 'A00016', '4/20/2013', 'Y');
SELECT D1.InvoiceNum, D1.Client, D1.InvoiceDate, D1.StillOpen from dbo.tempdatecheck as D1 JOIN (SELECT DISTINCT D.InvoiceNum, D.Client, D.InvoiceDate, D.StillOpen, D2.MaxNoDueDate from dbo.tempdatecheck as D JOIN (SELECT Client,InvoiceNum,InvoiceDate,StillOpen, Max(InvoiceDate) over (partition by client order by invoicedate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as MaxNoDueDate FROM dbo.tempdatecheck where StillOpen='N') AS D2 on D.client = D2.Client and D.Invoicedate < D2.InvoiceDate WHERE D.StillOpen='Y') as DD on D1.Client = DD.Client and D1.StillOpen='Y' and D1.InvoiceDate < GetDate(); |
 |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 04/17/2013 : 13:59:18
|
BTW If you don't have SQL 2012, the following query should work:
SELECT D1.InvoiceNum, D1.Client, D1.InvoiceDate, D1.StillOpen from dbo.tempdatecheck as D1 JOIN (SELECT DISTINCT D.InvoiceNum, D.Client, D.InvoiceDate, D.StillOpen, D2.MaxNoDueDate from dbo.tempdatecheck as D JOIN (SELECT Client, Max(InvoiceDate) AS MaxNoDueDate FROM dbo.tempdatecheck where StillOpen='N' group by client) AS D2 on D.client = D2.Client and D.Invoicedate < D2.MaxNoDueDate WHERE D.StillOpen='Y') as DD on D1.Client = DD.Client and D1.StillOpen='Y' and D1.InvoiceDate < GetDate();
|
 |
|
|
Topic  |
|
|
|