Author |
Topic |
cardullo4321
Starting Member
40 Posts |
Posted - 2013-04-16 : 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 StillOpenA A0001 4/1/2013 YA A0002 4/2/2013 NB A0003 4/3/2013 YB A0004 4/4/2013 YC A0005 4/5/2013 NC A0006 4/6/2013 NA A0007 4/16/2013 NB A0008 4/16/2013 NC A0009 4/16/2013 NThe results will showClient InvoiceNum InvoiceDate StillOpenA A0001 4/1/2013 YB A0003 4/3/2013 YB A0004 4/4/2013 YGregory Cardullo |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-17 : 00:32:50
|
[code]SELECT Client,InvoiceNum,InvoiceDate,StillOpenFROM(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 TodayCntFROM TableWHERE StillOpen='Y'AND InvoiceDate < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))tWHERE TodayCnt >0[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-04-17 : 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 StillOpenA A0001 4/1/2013 YA A0002 4/2/2013 NB A0003 4/3/2013 YB A0004 4/4/2013 YC A0005 4/5/2013 NC A0006 4/6/2013 NA A0007 4/16/2013 NB A0008 4/16/2013 NC A0009 4/16/2013 NThe results will showClient InvoiceNum InvoiceDate StillOpenA A0001 4/1/2013 YB A0003 4/3/2013 YB A0004 4/4/2013 YGregory 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 - 2013-04-17 : 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(); |
|
|
|
|
|