October 6, 2011 at 11:03 am
Could somebody(s) please help with this query? I've been asked to do the following:
Create a chart of invoices that are more than 45 days old and unpaid, by month for 12 months, including MTD for the current month. For the current month, I want invoices that are older than 45 days (and unpaid) from GETDATE(). For previous months, I want invoices that are older than 45 days (and unpaid) at the last day of each month (it might have been paid in a future month).
So, assuming today is Oct 6, I need unpaid invoices that were created before Aug 22 for this month, invoices that were created before Aug 16 and are unpaid as of the end of Sept for Sept, invoices that were created before July 17 and are unpaid as of the end of August for August, etc.
My initial thought is that I can do the current month (somehow), and union that with the historical months (somehow). But that's where I get stuck.
Hopefully this can be done in AW? I don't have access to that database at this company. If not, let me know and I'll create some sample data.
Cheers,
Tom
October 6, 2011 at 11:15 am
It would help if you could post the DDL for the table(s), sample data for the table(s), expected results based on the sample data, and what you have done so far to solve your problem.
Please read the first article I reference below in my signature block regarding asking for help. It will show you how you should post the requested information. Doing this will get you the best help possible, plus you will get tested code in return.
October 6, 2011 at 11:15 am
Tom would some4thign like this bee what you are looking for?
select
InvoiceNumber,
InvoiceDate,
Month(InvoiceDate) AS Mn
from sometable
where InvoiceDate < dateadd(dd,-45,getdate)
Group By
InvoiceNumber,
InvoiceDate
Lowell
October 6, 2011 at 12:30 pm
Lynn,
That's why I was hoping this might be doable in the AdventureWorks database, but here's something:
declare @Invoices table (
InvoiceNumber int IDENTITY(1,1) NOT NULL,
InvoiceDate date NOT NULL,
PaidDate date NULL,
IsPaid bit NOT NULL
)
INSERT @Invoices (InvoiceDate, PaidDate, IsPaid)
VALUES ('2011-06-14', '2011-07-20', 1), -- < 45 days
('2011-06-17', '2011-08-02', 1), -- > 45 days in July
('2011-06-18', '2011-09-01', 1), -- > 45 days in July, Aug
('2011-07-10', '2011-09-01', 1), -- > 45 days in Aug
('2011-07-30', NULL, 0), -- > 45 days in Sept and Oct MTD
('2011-07-30', '2011-08-15', 1), -- < 45 days
('2011-08-01', NULL, 0), -- > 45 days in Sept and Oct MTD
('2011-08-21',NULL, 0), -- > 45 days in Oct MTD
('2011-08-30', NULL, 0) -- < 45 days
Lowell (10/6/2011)
Tom would some4thign like this bee what you are looking for?
select
InvoiceNumber,
InvoiceDate,
Month(InvoiceDate) AS Mn
from sometable
where InvoiceDate < dateadd(dd,-45,getdate)
Group By
InvoiceNumber,
InvoiceDate
Lowell, that doesn't take into account invoices that are old for more than one month.
This company is a startup in stealth mode, and the actual query has nothing to do with invoicing. But this is the idea behind what I need to do. If you need to add anything to the table to make this more efficient/easier, feel free. I might have to push back though. The database and a whole lot of .Net code already exists, so I'm not sure how much I can change in that regard.
October 6, 2011 at 1:16 pm
I created invoices for my last company where we were a net 30 and some people had paid a %age POS, some had balances at 30, 60, 90, and 120+ days... Gimme a couple of minutes and I'll have something based on the given sample data.
Thanks,
Jared
Jared
CE - Microsoft
October 6, 2011 at 1:28 pm
Can you please give me a sample of what you want your aging report to look like? In terms of relevant data points for your purpose.
EDIT: Also, are you just wanting a count or is there supposed to be $ amounts?
Thanks,
Jared
Jared
CE - Microsoft
October 6, 2011 at 1:34 pm
jared-709193 (10/6/2011)
Can you please give me a sample of what you want your aging report to look like? In terms of relevant data points for your purpose.Thanks,
Jared
I'm guessing I need Month (Year and Month so it will be in correct order when we cross over a year boundary?) and number of invoices > 45 days old, for current month and past 11 months. Just count of old items. So it should be:
July 2
Aug 2
Sep 2
Oct 3
It's going to go into a line graph.
October 6, 2011 at 1:40 pm
Here's where I am at... I will keep going as I love this kind of stuff! 🙂
SELECT InvoiceNumber, YEAR(InvoiceDate) AS InvoiceYear, MONTH(InvoiceDate) AS InvoiceMonth, InvoiceDate, PaidDate,
DATEADD(D,45, InvoiceDate) Date45Days, IsPaid,
CASE IsPaid WHEN 1 THEN 0 ELSE DATEDIFF(D,InvoiceDate, GETDATE()) END AS TotalDaysLate
FROM #invoices
WHERE PaidDate IS NULL OR PaidDate > DATEADD(D,46, InvoiceDate)
I add lots of extra columns at first for ease of debugging and to see my calculations straight out.
Thanks,
Jared
Jared
CE - Microsoft
October 6, 2011 at 2:13 pm
Try this for now. I would leave the data in a raw form, and then group on things in the report itself if you are making a SSRS report. Otherwise, I think this will help you to start grouping and counting. You may need a temp table to store the total for the current month and then union that 1 row with the rest.
SELECT InvoiceNumber,
YEAR(InvoiceDate) AS InvoiceYear,
MONTH(InvoiceDate) AS InvoiceMonth,
YEAR(PaidDate) AS PaidYear,
MONTH(PaidDate) AS PaidMonth,
YEAR(DATEADD(D,45, InvoiceDate)) AS Year45,
MONTH(DATEADD(D,45, InvoiceDate)) AS Month45,
DATEADD(D,45, InvoiceDate) Date45Days,
CASE
WHEN PaidDate IS NULL THEN DATEDIFF(D,InvoiceDate, GETDATE())
WHEN PaidDate > InvoiceDate THEN DATEDIFF(D,InvoiceDate, PaidDate)
WHEN PaidDate < InvoiceDate THEN 0
ELSE -1
END AS TotalDaysLate,
CASE IsPaid
WHEN 1 THEN 0
ELSE DATEDIFF(D,InvoiceDate, GETDATE())
END AS TotalDaysLateToday
FROM #invoices
WHERE PaidDate IS NULL
OR (PaidDate > DATEADD(D,45, InvoiceDate))
Thanks,
Jared
Jared
CE - Microsoft
October 7, 2011 at 6:38 am
Thanks Jared. Looks promising. But:
Is the only way to get what I want to do something like:
SELECT Count(*), Year, Month
from ...
group by year, month
where year and month = current year and month
UNION
SELECT Count(*), Year, Month
from ...
group by year, month
where year and month = previous year and month
UNION
SELECT Count(*), Year, Month
from ...
group by year, month
where year and month = year and month - 2
UNION
...
for the 6 or 12 months that I want? Is there a better way?
October 7, 2011 at 6:54 am
Well... I think the best way may be to create a table based function that looks at the raw data and use CROSS APPLY to bring your counts.
Thanks,
Jared
Jared
CE - Microsoft
October 7, 2011 at 7:08 am
jared-709193 (10/7/2011)
Well... I think the best way may be to create a table based function that looks at the raw data and use CROSS APPLY to bring your counts.Thanks,
Jared
I'm dense this morning. CROSS APPLY the raw data with what?
October 7, 2011 at 7:46 am
Look at this article http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005
Let me know if this helps 🙂
Thanks,
Jared
Jared
CE - Microsoft
October 9, 2011 at 1:45 pm
different approach....may be give you some ideas
pls note ...date range vailidity needs scrutiny
comments in code
USE [tempdb] --- a safe place
GO
--== condtionally drop table for retesting in SSMS
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_invoices]') AND type in (N'U'))
DROP TABLE [dbo].[tbl_invoices]
GO
CREATE TABLE [dbo].[tbl_invoices](
[InvoiceNumber] [int] IDENTITY(1,1) NOT NULL,
[InvoiceDate] [date] NOT NULL,
[PaidDate] [date] NULL,
[IsPaid] [bit] NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tbl_invoices] ON;
INSERT INTO [dbo].[tbl_invoices]([InvoiceNumber], [InvoiceDate], [PaidDate], [IsPaid])
SELECT 1, '20110614 00:00:00.000', '20110720 00:00:00.000', 1 UNION ALL
SELECT 2, '20110617 00:00:00.000', '20110802 00:00:00.000', 1 UNION ALL
SELECT 3, '20110618 00:00:00.000', '20110901 00:00:00.000', 1 UNION ALL
SELECT 4, '20110710 00:00:00.000', '20110901 00:00:00.000', 1 UNION ALL
SELECT 5, '20110730 00:00:00.000', NULL, 0 UNION ALL
SELECT 6, '20110730 00:00:00.000', '20110815 00:00:00.000', 1 UNION ALL
SELECT 7, '20110801 00:00:00.000', NULL, 0 UNION ALL
SELECT 8, '20110821 00:00:00.000', NULL, 0 UNION ALL
SELECT 9, '20110830 00:00:00.000', NULL, 0
SET IDENTITY_INSERT [dbo].[tbl_invoices] OFF;
--- the following add cols and update could be added into the results select statement
--- but for clarity of thought I have added them as a separate process
--== add a couple of new columns
ALTER TABLE dbo.tbl_invoices ADD
PayDueDate datetime NULL,
PayInTerms varchar (1) NULL
GO
--== update columns
UPDATE tbl_invoices
SET PayDueDate = dateadd(d,45,InvoiceDate),
PayInTerms = CASE when PaidDate < dateadd(d,45,InvoiceDate) then 'Y' ELSE 'N' END
---results
DECLARE @TD DATETIME; --- not necessary, used for testing, replace with GETDATE() in code below
SET @TD = Getdate(); --- see above
--- I have not had time to check the validity of the dates...but hopefully you will get the gist of what this is about.
--- ND...dependent on your data volume , pls check execution plan and create required indexes.
SELECT SUM(CASE
WHEN PayDueDate > ( Dateadd(MM, Datediff(MM, 0, @TD), 0) - 1 )
AND PayDueDate < ( Getdate() )
THEN 1
ELSE 0
END) AS CURMTH,
SUM(CASE
WHEN PayDueDate > ( Dateadd(MM, Datediff(MM, 0, @TD) - 1, 0) - 1 )
AND PayDueDate < ( Dateadd(MM, Datediff(MM, 0, @TD), 0) )
THEN 1
ELSE 0
END) AS MTHMINUS1,
SUM(CASE
WHEN PayDueDate > ( Dateadd(MM, Datediff(MM, 0, @TD) - 2, 0) - 1 )
AND PayDueDate < ( Dateadd(MM, Datediff(MM, 0, @TD) - 1, 0) )
THEN 1
ELSE 0
END) AS MTHMINUS2,
SUM(CASE
WHEN PayDueDate > ( Dateadd(MM, Datediff(MM, 0, @TD) - 3, 0) - 1 )
AND PayDueDate < ( Dateadd(MM, Datediff(MM, 0, @TD) - 2, 0) )
THEN 1
ELSE 0
END) AS MTHMINUS3
FROM tbl_invoices
WHERE PayInTerms = 'N'
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 9, 2011 at 6:42 pm
J Livingston SQL (10/9/2011)
different approach....may be give you some ideaspls note ...date range vailidity needs scrutiny
comments in code
USE [tempdb] --- a safe placeGO
--== condtionally drop table for retesting in SSMS
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_invoices]') AND type in (N'U'))
DROP TABLE [dbo].[tbl_invoices]
GO
CREATE TABLE [dbo].[tbl_invoices](
[InvoiceNumber] [int] IDENTITY(1,1) NOT NULL,
[InvoiceDate] [date] NOT NULL,
[PaidDate] [date] NULL,
[IsPaid] [bit] NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tbl_invoices] ON;
INSERT INTO [dbo].[tbl_invoices]([InvoiceNumber], [InvoiceDate], [PaidDate], [IsPaid])
SELECT 1, '20110614 00:00:00.000', '20110720 00:00:00.000', 1 UNION ALL
SELECT 2, '20110617 00:00:00.000', '20110802 00:00:00.000', 1 UNION ALL
SELECT 3, '20110618 00:00:00.000', '20110901 00:00:00.000', 1 UNION ALL
SELECT 4, '20110710 00:00:00.000', '20110901 00:00:00.000', 1 UNION ALL
SELECT 5, '20110730 00:00:00.000', NULL, 0 UNION ALL
SELECT 6, '20110730 00:00:00.000', '20110815 00:00:00.000', 1 UNION ALL
SELECT 7, '20110801 00:00:00.000', NULL, 0 UNION ALL
SELECT 8, '20110821 00:00:00.000', NULL, 0 UNION ALL
SELECT 9, '20110830 00:00:00.000', NULL, 0
SET IDENTITY_INSERT [dbo].[tbl_invoices] OFF;
--- the following add cols and update could be added into the results select statement
--- but for clarity of thought I have added them as a separate process
--== add a couple of new columns
ALTER TABLE dbo.tbl_invoices ADD
PayDueDate datetime NULL,
PayInTerms varchar (1) NULL
GO
--== update columns
UPDATE tbl_invoices
SET PayDueDate = dateadd(d,45,InvoiceDate),
PayInTerms = CASE when PaidDate < dateadd(d,45,InvoiceDate) then 'Y' ELSE 'N' END
---results
DECLARE @TD DATETIME; --- not necessary, used for testing, replace with GETDATE() in code below
SET @TD = Getdate(); --- see above
--- I have not had time to check the validity of the dates...but hopefully you will get the gist of what this is about.
--- ND...dependent on your data volume , pls check execution plan and create required indexes.
SELECT SUM(CASE
WHEN PayDueDate > ( Dateadd(MM, Datediff(MM, 0, @TD), 0) - 1 )
AND PayDueDate < ( Getdate() )
THEN 1
ELSE 0
END) AS CURMTH,
SUM(CASE
WHEN PayDueDate > ( Dateadd(MM, Datediff(MM, 0, @TD) - 1, 0) - 1 )
AND PayDueDate < ( Dateadd(MM, Datediff(MM, 0, @TD), 0) )
THEN 1
ELSE 0
END) AS MTHMINUS1,
SUM(CASE
WHEN PayDueDate > ( Dateadd(MM, Datediff(MM, 0, @TD) - 2, 0) - 1 )
AND PayDueDate < ( Dateadd(MM, Datediff(MM, 0, @TD) - 1, 0) )
THEN 1
ELSE 0
END) AS MTHMINUS2,
SUM(CASE
WHEN PayDueDate > ( Dateadd(MM, Datediff(MM, 0, @TD) - 3, 0) - 1 )
AND PayDueDate < ( Dateadd(MM, Datediff(MM, 0, @TD) - 2, 0) )
THEN 1
ELSE 0
END) AS MTHMINUS3
FROM tbl_invoices
WHERE PayInTerms = 'N'
I'm glad I read this whole thread before I started on a possible solution, JLS. Your CROSS TAB solution is pretty much where I was going to head on this one. Nicely done.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply