| 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 |
|
|
|
Old Invoices Still Open when New Ones paid