November 5, 2018 at 11:15 am
Hi,
Please consider the following tables. Invoices are issued when products are sent to customer. for each customer could be one or more invoices issued each month. At the end of the month, statement is created and customer pays for monthly statement. I would like to add StatementDate column to #Statements table and use MonthValue and YearValue for month & year and also last day of each month for day of date to insert into StatementDate when inserting data into #Statements.
Thank you.
--======= If the first test table already exists, drop it
IF OBJECT_ID('TempDB..#Invoices', 'U') IS NOT NULL
DROP TABLE #Invoices
--======= Create the first test table with
CREATE TABLE #Invoices
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
InvoiceDate DATETIME,
Price DECIMAL(18,2),
CustomerID INT
)
--======= Setup any special required conditions specifically where dates are concerned
SET DATEFORMAT DMY
--======= Allow Inserts into IDENTITY column
SET IDENTITY_INSERT #Invoices ON
--======= Insert the test data into first test table
INSERT INTO #Invoices
(ID, InvoiceDate, Price, CustomerID)
SELECT 1, 'JUL 3 2018 12:00AM', 185.00, 3 UNION ALL
SELECT 3, 'JUL 5 2018 12:00AM', 98.00, 5 UNION ALL
SELECT 8, 'JUL 5 2018 12:00AM', 125.00, 7 UNION ALL
SELECT 9, 'JUL 7 2018 12:00AM', 90.00, 3 UNION ALL
SELECT 12, 'JUL 10 2018 12:00AM', 205.00, 5 UNION ALL
SELECT 23, 'JUL 10 2018 12:00AM', 165.00, 3 UNION ALL
SELECT 27, 'AUG 8 2018 12:00AM', 385.00, 5 UNION ALL
SELECT 32, 'AUG 9 2018 12:00AM', 222.00, 3 UNION ALL
SELECT 35, 'SEP 4 2018 12:00AM', 402.00, 3
--======= Do not allow IDENTITY insert
SET IDENTITY_INSERT #Invoices OFF
--===================================================================
--======= If the second test table alrady exists, drop it
IF OBJECT_ID('TempDB..#Statements', 'U') IS NOT NULL
DROP TABLE #Statements
--======= Create the second test table
CREATE TABLE #Statements
(
StatementID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
StatementSum DECIMAL(18,2),
Paid BIT DEFAULT (0),
MonthValue INT,
YearValue INT,
CustomerID INT
--, StatementDate DATETIME
)
--====== Insert the test data using #Invoices table into second test table
INSERT INTO #Statements
(StatementSum, MonthValue, YearValue, CustomerID)
SELECT DISTINCT
SUM(Price) OVER(PARTITION BY CustomerID),
MONTH(InvoiceDate),
YEAR(InvoiceDate),
CustomerID
FROM #Invoices
WHERE MONTH(InvoiceDate)=7
ORDER BY CustomerID
--====== Display test table data
SELECT * FROM #Invoices
SELECT * FROM #Statements
November 5, 2018 at 3:22 pm
rahimazer - Monday, November 5, 2018 11:15 AMHi,
Please consider the following tables. Invoices are issued when products are sent to customer. for each customer could be one or more invoices issued each month. At the end of the month, statement is created and customer pays for monthly statement. I would like to add StatementDate column to #Statements table and use MonthValue and YearValue for month & year and also last day of each month for day of date to insert into StatementDate when inserting data into #Statements.
Thank you.
--======= If the first test table already exists, drop it
IF OBJECT_ID('TempDB..#Invoices', 'U') IS NOT NULL
DROP TABLE #Invoices--======= Create the first test table with
CREATE TABLE #Invoices
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
InvoiceDate DATETIME,
Price DECIMAL(18,2),
CustomerID INT
)--======= Setup any special required conditions specifically where dates are concerned
SET DATEFORMAT DMY--======= Allow Inserts into IDENTITY column
SET IDENTITY_INSERT #Invoices ON--======= Insert the test data into first test table
INSERT INTO #Invoices
(ID, InvoiceDate, Price, CustomerID)
SELECT 1, 'JUL 3 2018 12:00AM', 185.00, 3 UNION ALL
SELECT 3, 'JUL 5 2018 12:00AM', 98.00, 5 UNION ALL
SELECT 8, 'JUL 5 2018 12:00AM', 125.00, 7 UNION ALL
SELECT 9, 'JUL 7 2018 12:00AM', 90.00, 3 UNION ALL
SELECT 12, 'JUL 10 2018 12:00AM', 205.00, 5 UNION ALL
SELECT 23, 'JUL 10 2018 12:00AM', 165.00, 3 UNION ALL
SELECT 27, 'AUG 8 2018 12:00AM', 385.00, 5 UNION ALL
SELECT 32, 'AUG 9 2018 12:00AM', 222.00, 3 UNION ALL
SELECT 35, 'SEP 4 2018 12:00AM', 402.00, 3--======= Do not allow IDENTITY insert
SET IDENTITY_INSERT #Invoices OFF--===================================================================
--======= If the second test table alrady exists, drop it
IF OBJECT_ID('TempDB..#Statements', 'U') IS NOT NULL
DROP TABLE #Statements--======= Create the second test table
CREATE TABLE #Statements
(
StatementID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
StatementSum DECIMAL(18,2),
Paid BIT DEFAULT (0),
MonthValue INT,
YearValue INT,
CustomerID INT
--, StatementDate DATETIME
)
--====== Insert the test data using #Invoices table into second test table
INSERT INTO #Statements
(StatementSum, MonthValue, YearValue, CustomerID)SELECT DISTINCT
SUM(Price) OVER(PARTITION BY CustomerID),
MONTH(InvoiceDate),
YEAR(InvoiceDate),
CustomerID
FROM #Invoices
WHERE MONTH(InvoiceDate)=7
ORDER BY CustomerID--====== Display test table data
SELECT * FROM #Invoices
SELECT * FROM #Statements
You just need to learn some date math to calculate the last day of the month.
WITH cteInvoices AS(
SELECT CustomerID,
DATEADD( MM, DATEDIFF( MM, -1, InvoiceDate), -1) AS StatementDate,
Price
FROM #Invoices
WHERE InvoiceDate >= '20180701'
AND InvoiceDate < '20180801'
)
INSERT INTO #Statements
(StatementSum, MonthValue, YearValue, CustomerID, StatementDate)
SELECT
SUM(Price),
MONTH(StatementDate),
YEAR(StatementDate),
CustomerID,
StatementDate
FROM cteInvoices
GROUP BY CustomerID,
StatementDate;
November 5, 2018 at 3:34 pm
rahimazer - Monday, November 5, 2018 11:15 AMHi,
Please consider the following tables. Invoices are issued when products are sent to customer. for each customer could be one or more invoices issued each month. At the end of the month, statement is created and customer pays for monthly statement. I would like to add StatementDate column to #Statements table and use MonthValue and YearValue for month & year and also last day of each month for day of date to insert into StatementDate when inserting data into #Statements.
Thank you.
--======= If the first test table already exists, drop it
IF OBJECT_ID('TempDB..#Invoices', 'U') IS NOT NULL
DROP TABLE #Invoices--======= Create the first test table with
CREATE TABLE #Invoices
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
InvoiceDate DATETIME,
Price DECIMAL(18,2),
CustomerID INT
)--======= Setup any special required conditions specifically where dates are concerned
SET DATEFORMAT DMY--======= Allow Inserts into IDENTITY column
SET IDENTITY_INSERT #Invoices ON--======= Insert the test data into first test table
INSERT INTO #Invoices
(ID, InvoiceDate, Price, CustomerID)
SELECT 1, 'JUL 3 2018 12:00AM', 185.00, 3 UNION ALL
SELECT 3, 'JUL 5 2018 12:00AM', 98.00, 5 UNION ALL
SELECT 8, 'JUL 5 2018 12:00AM', 125.00, 7 UNION ALL
SELECT 9, 'JUL 7 2018 12:00AM', 90.00, 3 UNION ALL
SELECT 12, 'JUL 10 2018 12:00AM', 205.00, 5 UNION ALL
SELECT 23, 'JUL 10 2018 12:00AM', 165.00, 3 UNION ALL
SELECT 27, 'AUG 8 2018 12:00AM', 385.00, 5 UNION ALL
SELECT 32, 'AUG 9 2018 12:00AM', 222.00, 3 UNION ALL
SELECT 35, 'SEP 4 2018 12:00AM', 402.00, 3--======= Do not allow IDENTITY insert
SET IDENTITY_INSERT #Invoices OFF--===================================================================
--======= If the second test table alrady exists, drop it
IF OBJECT_ID('TempDB..#Statements', 'U') IS NOT NULL
DROP TABLE #Statements--======= Create the second test table
CREATE TABLE #Statements
(
StatementID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
StatementSum DECIMAL(18,2),
Paid BIT DEFAULT (0),
MonthValue INT,
YearValue INT,
CustomerID INT
--, StatementDate DATETIME
)
--====== Insert the test data using #Invoices table into second test table
INSERT INTO #Statements
(StatementSum, MonthValue, YearValue, CustomerID)SELECT DISTINCT
SUM(Price) OVER(PARTITION BY CustomerID),
MONTH(InvoiceDate),
YEAR(InvoiceDate),
CustomerID
FROM #Invoices
WHERE MONTH(InvoiceDate)=7
ORDER BY CustomerID--====== Display test table data
SELECT * FROM #Invoices
SELECT * FROM #Statements
How is this:
--======= If the first test table already exists, drop it
IF OBJECT_ID('TempDB..#Invoices', 'U') IS NOT NULL
DROP TABLE #Invoices;
go
--======= Create the first test table with
CREATE TABLE #Invoices
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
InvoiceDate DATETIME,
Price DECIMAL(18,2),
CustomerID INT
);
go
--======= Allow Inserts into IDENTITY column
SET IDENTITY_INSERT #Invoices on;
go
INSERT INTO #Invoices
(ID, InvoiceDate, Price, CustomerID)
VALUES -- using ISO standard for date/time values
(1, '20180703 00:00:00.000', 185.00, 3)
,(3, '20180705 00:00:00.000', 98.00, 5)
,(8, '20180705 00:00:00.000', 125.00, 7)
,(9, '20180707 00:00:00.000', 90.00, 3)
,(12, '20180710 00:00:00.000', 205.00, 5)
,(23, '20180710 00:00:00.000', 165.00, 3)
,(27, '20180808 00:00:00.000', 385.00, 5)
,(32, '20180809 00:00:00.000', 222.00, 3)
,(35, '20180904 00:00:00.000', 402.00, 3);
go
--======= Do not allow IDENTITY insert
SET IDENTITY_INSERT #Invoices off;
go
select * from [#Invoices];
go
--======= If the second test table alrady exists, drop it
IF OBJECT_ID('TempDB..#Statements', 'U') IS NOT NULL
DROP TABLE #Statements;
go
--======= Create the second test table
CREATE TABLE #Statements
(
StatementID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
StatementSum DECIMAL(18,2),
Paid BIT DEFAULT (0),
MonthValue INT,
YearValue INT,
CustomerID INT
, StatementDate DATETIME
);
go
--====== Insert the test data using #Invoices table into second test table
insert into [#Statements]
(
[StatementSum]
, [MonthValue]
, [YearValue]
, [CustomerID]
, [StatementDate]
)
select distinct
sum([Price]) over (partition by [CustomerID])
, month([InvoiceDate])
, year([InvoiceDate])
, [CustomerID]
, dateadd(month, datediff(month,0,[InvoiceDate]) + 1,-1)
from [#Invoices]
where [InvoiceDate] >= '20180701'
and [InvoiceDate] < '20180801'
order by [CustomerID];
go
--====== Display test table data
SELECT * FROM #Invoices
SELECT * FROM #Statements
November 5, 2018 at 3:51 pm
How about EOMONTH(InvoiceDate)? Probably won't notice any performance issues with this - but if you do then revert to the DATEADD methods above.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 5, 2018 at 8:25 pm
Jeffrey Williams 3188 - Monday, November 5, 2018 3:51 PMHow about EOMONTH(InvoiceDate)? Probably won't notice any performance issues with this - but if you do then revert to the DATEADD methods above.
Just a personal opinion but why pick up a bad habit if it can be a performance issue?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2018 at 9:13 pm
Thank you all for taking your time and contributing to this. I struggled with this for about 2 weeks now. Luis Cazares and Lynn Pettis Thank you so much.
Regards,
Savalan
November 5, 2018 at 10:59 pm
Jeff Moden - Monday, November 5, 2018 8:25 PMJeffrey Williams 3188 - Monday, November 5, 2018 3:51 PMHow about EOMONTH(InvoiceDate)? Probably won't notice any performance issues with this - but if you do then revert to the DATEADD methods above.Just a personal opinion but why pick up a bad habit if it can be a performance issue?
The EOMONTH function outperforms all other methods for calculating the end of month, with datediff months to 0 date coming next, the latter is more than 10% slower than the former.
😎
November 6, 2018 at 6:35 am
Eirikur Eiriksson - Monday, November 5, 2018 10:59 PMJeff Moden - Monday, November 5, 2018 8:25 PMJeffrey Williams 3188 - Monday, November 5, 2018 3:51 PMHow about EOMONTH(InvoiceDate)? Probably won't notice any performance issues with this - but if you do then revert to the DATEADD methods above.Just a personal opinion but why pick up a bad habit if it can be a performance issue?
The EOMONTH function outperforms all other methods for calculating the end of month, with datediff months to 0 date coming next, the latter is more than 10% slower than the former.
😎
True. Actually I was just playing with dates yesterday, creating billing cycles for an AR system that I am building. Built in functions are generally highly optimized and nearly never NOT the way to go 🙂 I can't think of a case where not using a built in function would not be the most efficient but I am sure there is some example of this.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
November 6, 2018 at 6:51 am
Jeffery Williams - Tuesday, November 6, 2018 6:35 AMEirikur Eiriksson - Monday, November 5, 2018 10:59 PMJeff Moden - Monday, November 5, 2018 8:25 PMJeffrey Williams 3188 - Monday, November 5, 2018 3:51 PMHow about EOMONTH(InvoiceDate)? Probably won't notice any performance issues with this - but if you do then revert to the DATEADD methods above.Just a personal opinion but why pick up a bad habit if it can be a performance issue?
The EOMONTH function outperforms all other methods for calculating the end of month, with datediff months to 0 date coming next, the latter is more than 10% slower than the former.
😎True. Actually I was just playing with dates yesterday, creating billing cycles for an AR system that I am building. Built in functions are generally highly optimized and nearly never NOT the way to go 🙂 I can't think of a case where not using a built in function would not be the most efficient but I am sure there is some example of this.
I have tested 15 different methods and the EOMONTH leaves the competition behind every time.
😎
There are quite few posts out there stating that EOMONTH does not perform well, just wondering what kind of T#@mp tweeted that fake news😀
November 6, 2018 at 9:48 am
Eirikur Eiriksson - Tuesday, November 6, 2018 6:51 AMJeffery Williams - Tuesday, November 6, 2018 6:35 AMEirikur Eiriksson - Monday, November 5, 2018 10:59 PMJeff Moden - Monday, November 5, 2018 8:25 PMJeffrey Williams 3188 - Monday, November 5, 2018 3:51 PMHow about EOMONTH(InvoiceDate)? Probably won't notice any performance issues with this - but if you do then revert to the DATEADD methods above.Just a personal opinion but why pick up a bad habit if it can be a performance issue?
The EOMONTH function outperforms all other methods for calculating the end of month, with datediff months to 0 date coming next, the latter is more than 10% slower than the former.
😎True. Actually I was just playing with dates yesterday, creating billing cycles for an AR system that I am building. Built in functions are generally highly optimized and nearly never NOT the way to go 🙂 I can't think of a case where not using a built in function would not be the most efficient but I am sure there is some example of this.
I have tested 15 different methods and the EOMONTH leaves the competition behind every time.
😎There are quite few posts out there stating that EOMONTH does not perform well, just wondering what kind of T#@mp tweeted that fake news😀
So, not a Trump fan. Too bad, I have more money in my pocket since his tax cuts.
FORMAT, a do not use function until they fix it.
November 6, 2018 at 5:43 pm
Eirikur Eiriksson - Monday, November 5, 2018 10:59 PMJeff Moden - Monday, November 5, 2018 8:25 PMJeffrey Williams 3188 - Monday, November 5, 2018 3:51 PMHow about EOMONTH(InvoiceDate)? Probably won't notice any performance issues with this - but if you do then revert to the DATEADD methods above.Just a personal opinion but why pick up a bad habit if it can be a performance issue?
The EOMONTH function outperforms all other methods for calculating the end of month, with datediff months to 0 date coming next, the latter is more than 10% slower than the former.
😎
Hmmmm... haven't actually testing it myself. Since I typically use Closed/Open criteria for dates, I'll have to test for EOMONTH+1 to see if it's still faster than the "traditional" DATEADD/DATEDIFF method for finding the first of the next month.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2018 at 6:20 am
Jeff Moden - Tuesday, November 6, 2018 5:43 PMEirikur Eiriksson - Monday, November 5, 2018 10:59 PMJeff Moden - Monday, November 5, 2018 8:25 PMJeffrey Williams 3188 - Monday, November 5, 2018 3:51 PMHow about EOMONTH(InvoiceDate)? Probably won't notice any performance issues with this - but if you do then revert to the DATEADD methods above.Just a personal opinion but why pick up a bad habit if it can be a performance issue?
The EOMONTH function outperforms all other methods for calculating the end of month, with datediff months to 0 date coming next, the latter is more than 10% slower than the former.
😎Hmmmm... haven't actually testing it myself. Since I typically use Closed/Open criteria for dates, I'll have to test for EOMONTH+1 to see if it's still faster than the "traditional" DATEADD/DATEDIFF method for finding the first of the next month.
What about DATEFROMPARTS(yearvalue, monthvalue, 1) ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 12, 2018 at 7:02 am
sgmunson - Monday, November 12, 2018 6:20 AMJeff Moden - Tuesday, November 6, 2018 5:43 PMEirikur Eiriksson - Monday, November 5, 2018 10:59 PMJeff Moden - Monday, November 5, 2018 8:25 PMJeffrey Williams 3188 - Monday, November 5, 2018 3:51 PMHow about EOMONTH(InvoiceDate)? Probably won't notice any performance issues with this - but if you do then revert to the DATEADD methods above.Just a personal opinion but why pick up a bad habit if it can be a performance issue?
The EOMONTH function outperforms all other methods for calculating the end of month, with datediff months to 0 date coming next, the latter is more than 10% slower than the former.
😎Hmmmm... haven't actually testing it myself. Since I typically use Closed/Open criteria for dates, I'll have to test for EOMONTH+1 to see if it's still faster than the "traditional" DATEADD/DATEDIFF method for finding the first of the next month.
What about DATEFROMPARTS(yearvalue, monthvalue, 1) ?
The same, faster than all other methods I've tested.
😎
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply