I am attempting to optimize a process for creating a SQL report based upon our company's financial data. The end result I am looking to achieve is a new table that contains the compiled data for the past three years (current year plus last year and the year before). I will take this data and display it in a graph with each year getting its own line (thus showing the trends, etc.)
The problems are as follows:
- The data is in two different tables (one for current year and one for previous years).
- The data needs to be a cumulative SUM per week. Thus week two is a sum from day one through the end of week two while week three will be a sum from day one through the end of week three.
- The fiscal year starts June 1, which splits a calendar week number (thus if you operate by converting dates to week numbers and do a GROUP BY on the week numbers, you are going to throw off the data because data from the end of the year (week 23) gets put with data from the beginning of the year (week 23).
- The archive data is stored by year number, but that year number is the same for the entire year, which means the data stored under 2008 goes from 6/1/2008 to 5/31/2009. Thus, you can’t use the year number in the lookup as the year number of the values you are trying to extract (in case you decide to build dates in order to uniformly look for data).
While I am designing this so that it is run on a schedule (as a standard ETL process), I still want it to be efficient and manageable. Right now, I have a working solution. However, I am not comfortable with it. It is a cludge. I just got something to work (since I had to show the graph it is supporting to the president of our organization today). Now I need to do it right. What I have now is six update statements that write to my new table. Each statement pulls data for half a year. Two statements per year (thus six total). I do this because in my final table I have a list of months and days that I want to do the lookup based upon (6/7, 6/14, 6/21, etc.) This way each dataset is uniform and I don’t have the issue of week number 开发者_开发知识库rollover. I build the date off of this month and day number and add in the year number. Since the year number changes half way through (see problem #4), I go up to the end of the year with one SQL statement then pick up at the beginning of the calendar year with the next since one statement increments the year number for the date lookup and one does not.
Here is an example of my code (for those of you who might know, this is operating against a Microsoft Dynamics Great Plains database):
/* Second half of last year */
UPDATE ProfitLoss
SET ProfitLoss.monCashOneYearBack = upd.Balance
FROM ProfitLoss
INNER JOIN
(
SELECT (
SELECT (SUM(cumulative.CRDTAMNT) - SUM(cumulative.DEBITAMT)) AS Result
FROM GL30000 AS cumulative
INNER JOIN GL00100 ON cumulative.ActIndx = GL00100.ActIndx
WHERE GL00100.ActNumbr_1=10
AND GL00100.ActNumbr_2=1020
AND GL00100.ActNumbr_3=000
AND GL00100.ActNumbr_4=00
AND cumulative.HSTYEAR=2009
AND cumulative.TRXDATE <= CAST(CAST(2010 AS varchar) + '-' +
CAST(ProfitLoss.intMonthNumber AS varchar) + '-' +
CAST(ProfitLoss.intDayNumber AS varchar) AS DATETIME)
) AS Balance, intOrder, intWeekNumber
FROM ProfitLoss
WHERE intOrder > 30
) AS upd ON ProfitLoss.intWeekNumber = upd.intWeekNumber
In case you are wondering, this is just scratch code. The year number is still hard-coded. If I were to make this production code it would be a variable. The basic overview of this code is that I am doing a SELECT on my new table (ProfitLoss), doing a SUM of my credits minus a SUM of my debits from my history table based upon the date built using my new table’s month and date, then I am using that Balance data as the value to insert into the ProfitLoss table in the appropriate column (since I only operate on one column at a time, and only half of that).
All of this works, but it is terribly inefficient. What I want to know is if I am missing something obvious. Is there a better way to do this? I would love to create one statement that handled it all. I do have access to (and skill with) SSIS in case I need to bring in the big guns for a better method of ETL but I really think there is a better way of doing this using T-SQL. Any thought on what that way might be would be greatly appreciated.
Edit: The table schema that I am pulling from has only a few columns that matter. They are:
CRDTAMT, DEBITAMT, TRXDATE, and HSTYEAR (OPENYEAR for the current table)
The rest are just columns that I use for data filtering/etc. which is outside the scope of this question. Basically, the above fields hold the data I want. For example, when something is purchased on June 9th, we will get an entry here that has a zero in the CRDTAMT field, $100 in the DEBITAMT field, a TRXDATE of 6/9/2009, and a HSTYEAR of 2009. We will have multiple transactions like this. I want to collapse them down into one cumulative number each week. Thus, if this were the only transaction since June 1, we would have a zero for the week ending 6/7 and we would have a negative $100 for the week ending 6/14 and that negative $100 would carry over in 6/21 and on. This data would be one of the three years worth I would be capturing.
If you make a couple of auxiliary tables, then the problem becomes straight forward. I've prototyped the solution and from what information you've provided so far, I don't see why this doesn't meet your needs.
SQL containing DML and queries
Excel file used to create your weekly financial reporting structure
Cut to the chase - you wanted a simple query to replace your existing methodology. Here it is (with new schema that is defined in the attached SQL):
SELECT
W.YEAR,
W.WEEK_NUMBER,
SUM(NETAMT) AS NETAMT,
MIN(RT.YTDAMT) AS YTDAMT /* we only want one row from the cross apply return */
FROM #ProfitLossDaily PLD
INNER JOIN #Weeks W ON PLD.TRXDATE BETWEEN W.WEEK_STARTING AND W.WEEK_ENDING
CROSS APPLY (
SELECT SUM(NETAMT) AS YTDAMT
FROM #ProfitLossDaily PLD2
INNER JOIN #Weeks W2 ON PLD2.TRXDATE BETWEEN W2.WEEK_STARTING AND W2.WEEK_ENDING
WHERE W2.YEAR = W.YEAR
AND W2.WEEK_NUMBER <= W.WEEK_NUMBER
) as RT
GROUP BY W.YEAR, W.WEEK_NUMBER
ORDER BY W.YEAR, W.WEEK_NUMBER
The attached SQL file contains sample data and queries that populate the new table schema and return the results.
If you populate the ProfitLossDaily table via nightly job, then you can run the MERGE INTO right afterward (see the attached SQL file) to always have updated data ready.
SCHEMA (in case the attached SQL goes away sometime)
CREATE TABLE #Weeks (
YEAR INTEGER,
WEEK_NUMBER INTEGER,
WEEK_STARTING DATE,
WEEK_ENDING DATE,
PRIMARY KEY (YEAR,WEEK_NUMBER)
)
GO
CREATE TABLE #ProfitLossDaily (
TRXDATE DATE PRIMARY KEY,
CRDTAMT DECIMAL(38,8) NOT NULL,
DEBITAMT DECIMAL(38,8) NOT NULL,
NETAMT AS CRDTAMT - DEBITAMT
)
GO
CREATE TABLE #ProfitLoss (
YEAR INTEGER,
WEEK_NUMBER INTEGER,
NETAMT DECIMAL(38,8) NOT NULL,
YTDAMT DECIMAL(38,8) NOT NULL,
PRIMARY KEY (YEAR,WEEK_NUMBER)
)
GO
Nightly Job to populate ProfitLoss
MERGE INTO #ProfitLoss AS Target
USING (
SELECT
W.YEAR,
W.WEEK_NUMBER,
SUM(NETAMT) AS NETAMT,
MIN(RT.YTDAMT) AS YTDAMT /* we only want one row from the cross apply return */
FROM #ProfitLossDaily PLD
INNER JOIN #Weeks W ON PLD.TRXDATE BETWEEN W.WEEK_STARTING AND W.WEEK_ENDING
CROSS APPLY (
SELECT SUM(NETAMT) AS YTDAMT
FROM #ProfitLossDaily PLD2
INNER JOIN #Weeks W2 ON PLD2.TRXDATE BETWEEN W2.WEEK_STARTING AND W2.WEEK_ENDING
WHERE W2.YEAR = W.YEAR
AND W2.WEEK_NUMBER <= W.WEEK_NUMBER
) as RT
WHERE W.WEEK_STARTING >= DATEADD(DAY, -7, CURRENT_TIMESTAMP)
GROUP BY W.YEAR, W.WEEK_NUMBER
) AS Source ON Target.YEAR = Source.YEAR AND Target.WEEK_NUMBER = Source.WEEK_NUMBER
WHEN MATCHED THEN
UPDATE SET NETAMT = Source.NETAMT, YTDAMT = Source.YTDAMT
WHEN NOT MATCHED BY TARGET THEN
INSERT (YEAR, WEEK_NUMBER, NETAMT, YTDAMT) VALUES (YEAR, WEEK_NUMBER, NETAMT, YTDAMT);
Populate WEEKS (with your financial reporting structure)
/* Populate WEEKS table with the financial reporting period definitions */
INSERT INTO #WEEKS VALUES (2008,1,'2008-06-01','2008-06-07')
INSERT INTO #WEEKS VALUES (2008,2,'2008-06-08','2008-06-14')
INSERT INTO #WEEKS VALUES (2008,3,'2008-06-15','2008-06-21')
INSERT INTO #WEEKS VALUES (2008,4,'2008-06-22','2008-06-28')
INSERT INTO #WEEKS VALUES (2008,5,'2008-06-29','2008-07-05')
INSERT INTO #WEEKS VALUES (2008,6,'2008-07-06','2008-07-12')
INSERT INTO #WEEKS VALUES (2008,7,'2008-07-13','2008-07-19')
INSERT INTO #WEEKS VALUES (2008,8,'2008-07-20','2008-07-26')
INSERT INTO #WEEKS VALUES (2008,9,'2008-07-27','2008-08-02')
INSERT INTO #WEEKS VALUES (2008,10,'2008-08-03','2008-08-09')
INSERT INTO #WEEKS VALUES (2008,11,'2008-08-10','2008-08-16')
INSERT INTO #WEEKS VALUES (2008,12,'2008-08-17','2008-08-23')
INSERT INTO #WEEKS VALUES (2008,13,'2008-08-24','2008-08-30')
INSERT INTO #WEEKS VALUES (2008,14,'2008-08-31','2008-09-06')
INSERT INTO #WEEKS VALUES (2008,15,'2008-09-07','2008-09-13')
INSERT INTO #WEEKS VALUES (2008,16,'2008-09-14','2008-09-20')
INSERT INTO #WEEKS VALUES (2008,17,'2008-09-21','2008-09-27')
INSERT INTO #WEEKS VALUES (2008,18,'2008-09-28','2008-10-04')
INSERT INTO #WEEKS VALUES (2008,19,'2008-10-05','2008-10-11')
INSERT INTO #WEEKS VALUES (2008,20,'2008-10-12','2008-10-18')
INSERT INTO #WEEKS VALUES (2008,21,'2008-10-19','2008-10-25')
INSERT INTO #WEEKS VALUES (2008,22,'2008-10-26','2008-11-01')
INSERT INTO #WEEKS VALUES (2008,23,'2008-11-02','2008-11-08')
INSERT INTO #WEEKS VALUES (2008,24,'2008-11-09','2008-11-15')
INSERT INTO #WEEKS VALUES (2008,25,'2008-11-16','2008-11-22')
INSERT INTO #WEEKS VALUES (2008,26,'2008-11-23','2008-11-29')
INSERT INTO #WEEKS VALUES (2008,27,'2008-11-30','2008-12-06')
INSERT INTO #WEEKS VALUES (2008,28,'2008-12-07','2008-12-13')
INSERT INTO #WEEKS VALUES (2008,29,'2008-12-14','2008-12-20')
INSERT INTO #WEEKS VALUES (2008,30,'2008-12-21','2008-12-27')
INSERT INTO #WEEKS VALUES (2008,31,'2008-12-28','2009-01-03')
INSERT INTO #WEEKS VALUES (2008,32,'2009-01-04','2009-01-10')
INSERT INTO #WEEKS VALUES (2008,33,'2009-01-11','2009-01-17')
INSERT INTO #WEEKS VALUES (2008,34,'2009-01-18','2009-01-24')
INSERT INTO #WEEKS VALUES (2008,35,'2009-01-25','2009-01-31')
INSERT INTO #WEEKS VALUES (2008,36,'2009-02-01','2009-02-07')
INSERT INTO #WEEKS VALUES (2008,37,'2009-02-08','2009-02-14')
INSERT INTO #WEEKS VALUES (2008,38,'2009-02-15','2009-02-21')
INSERT INTO #WEEKS VALUES (2008,39,'2009-02-22','2009-02-28')
INSERT INTO #WEEKS VALUES (2008,40,'2009-03-01','2009-03-07')
INSERT INTO #WEEKS VALUES (2008,41,'2009-03-08','2009-03-14')
INSERT INTO #WEEKS VALUES (2008,42,'2009-03-15','2009-03-21')
INSERT INTO #WEEKS VALUES (2008,43,'2009-03-22','2009-03-28')
INSERT INTO #WEEKS VALUES (2008,44,'2009-03-29','2009-04-04')
INSERT INTO #WEEKS VALUES (2008,45,'2009-04-05','2009-04-11')
INSERT INTO #WEEKS VALUES (2008,46,'2009-04-12','2009-04-18')
INSERT INTO #WEEKS VALUES (2008,47,'2009-04-19','2009-04-25')
INSERT INTO #WEEKS VALUES (2008,48,'2009-04-26','2009-05-02')
INSERT INTO #WEEKS VALUES (2008,49,'2009-05-03','2009-05-09')
INSERT INTO #WEEKS VALUES (2008,50,'2009-05-10','2009-05-16')
INSERT INTO #WEEKS VALUES (2008,51,'2009-05-17','2009-05-23')
INSERT INTO #WEEKS VALUES (2008,52,'2009-05-24','2009-05-31')
INSERT INTO #WEEKS VALUES (2009,1,'2009-06-01','2009-06-07')
INSERT INTO #WEEKS VALUES (2009,2,'2009-06-08','2009-06-14')
INSERT INTO #WEEKS VALUES (2009,3,'2009-06-15','2009-06-21')
INSERT INTO #WEEKS VALUES (2009,4,'2009-06-22','2009-06-28')
INSERT INTO #WEEKS VALUES (2009,5,'2009-06-29','2009-07-05')
INSERT INTO #WEEKS VALUES (2009,6,'2009-07-06','2009-07-12')
INSERT INTO #WEEKS VALUES (2009,7,'2009-07-13','2009-07-19')
INSERT INTO #WEEKS VALUES (2009,8,'2009-07-20','2009-07-26')
INSERT INTO #WEEKS VALUES (2009,9,'2009-07-27','2009-08-02')
INSERT INTO #WEEKS VALUES (2009,10,'2009-08-03','2009-08-09')
INSERT INTO #WEEKS VALUES (2009,11,'2009-08-10','2009-08-16')
INSERT INTO #WEEKS VALUES (2009,12,'2009-08-17','2009-08-23')
INSERT INTO #WEEKS VALUES (2009,13,'2009-08-24','2009-08-30')
INSERT INTO #WEEKS VALUES (2009,14,'2009-08-31','2009-09-06')
INSERT INTO #WEEKS VALUES (2009,15,'2009-09-07','2009-09-13')
INSERT INTO #WEEKS VALUES (2009,16,'2009-09-14','2009-09-20')
INSERT INTO #WEEKS VALUES (2009,17,'2009-09-21','2009-09-27')
INSERT INTO #WEEKS VALUES (2009,18,'2009-09-28','2009-10-04')
INSERT INTO #WEEKS VALUES (2009,19,'2009-10-05','2009-10-11')
INSERT INTO #WEEKS VALUES (2009,20,'2009-10-12','2009-10-18')
INSERT INTO #WEEKS VALUES (2009,21,'2009-10-19','2009-10-25')
INSERT INTO #WEEKS VALUES (2009,22,'2009-10-26','2009-11-01')
INSERT INTO #WEEKS VALUES (2009,23,'2009-11-02','2009-11-08')
INSERT INTO #WEEKS VALUES (2009,24,'2009-11-09','2009-11-15')
INSERT INTO #WEEKS VALUES (2009,25,'2009-11-16','2009-11-22')
INSERT INTO #WEEKS VALUES (2009,26,'2009-11-23','2009-11-29')
INSERT INTO #WEEKS VALUES (2009,27,'2009-11-30','2009-12-06')
INSERT INTO #WEEKS VALUES (2009,28,'2009-12-07','2009-12-13')
INSERT INTO #WEEKS VALUES (2009,29,'2009-12-14','2009-12-20')
INSERT INTO #WEEKS VALUES (2009,30,'2009-12-21','2009-12-27')
INSERT INTO #WEEKS VALUES (2009,31,'2009-12-28','2010-01-03')
INSERT INTO #WEEKS VALUES (2009,32,'2010-01-04','2010-01-10')
INSERT INTO #WEEKS VALUES (2009,33,'2010-01-11','2010-01-17')
INSERT INTO #WEEKS VALUES (2009,34,'2010-01-18','2010-01-24')
INSERT INTO #WEEKS VALUES (2009,35,'2010-01-25','2010-01-31')
INSERT INTO #WEEKS VALUES (2009,36,'2010-02-01','2010-02-07')
INSERT INTO #WEEKS VALUES (2009,37,'2010-02-08','2010-02-14')
INSERT INTO #WEEKS VALUES (2009,38,'2010-02-15','2010-02-21')
INSERT INTO #WEEKS VALUES (2009,39,'2010-02-22','2010-02-28')
INSERT INTO #WEEKS VALUES (2009,40,'2010-03-01','2010-03-07')
INSERT INTO #WEEKS VALUES (2009,41,'2010-03-08','2010-03-14')
INSERT INTO #WEEKS VALUES (2009,42,'2010-03-15','2010-03-21')
INSERT INTO #WEEKS VALUES (2009,43,'2010-03-22','2010-03-28')
INSERT INTO #WEEKS VALUES (2009,44,'2010-03-29','2010-04-04')
INSERT INTO #WEEKS VALUES (2009,45,'2010-04-05','2010-04-11')
INSERT INTO #WEEKS VALUES (2009,46,'2010-04-12','2010-04-18')
INSERT INTO #WEEKS VALUES (2009,47,'2010-04-19','2010-04-25')
INSERT INTO #WEEKS VALUES (2009,48,'2010-04-26','2010-05-02')
INSERT INTO #WEEKS VALUES (2009,49,'2010-05-03','2010-05-09')
INSERT INTO #WEEKS VALUES (2009,50,'2010-05-10','2010-05-16')
INSERT INTO #WEEKS VALUES (2009,51,'2010-05-17','2010-05-23')
INSERT INTO #WEEKS VALUES (2009,52,'2010-05-24','2010-05-31')
INSERT INTO #WEEKS VALUES (2010,1,'2010-06-01','2010-06-07')
INSERT INTO #WEEKS VALUES (2010,2,'2010-06-08','2010-06-14')
INSERT INTO #WEEKS VALUES (2010,3,'2010-06-15','2010-06-21')
INSERT INTO #WEEKS VALUES (2010,4,'2010-06-22','2010-06-28')
INSERT INTO #WEEKS VALUES (2010,5,'2010-06-29','2010-07-05')
INSERT INTO #WEEKS VALUES (2010,6,'2010-07-06','2010-07-12')
INSERT INTO #WEEKS VALUES (2010,7,'2010-07-13','2010-07-19')
INSERT INTO #WEEKS VALUES (2010,8,'2010-07-20','2010-07-26')
INSERT INTO #WEEKS VALUES (2010,9,'2010-07-27','2010-08-02')
INSERT INTO #WEEKS VALUES (2010,10,'2010-08-03','2010-08-09')
INSERT INTO #WEEKS VALUES (2010,11,'2010-08-10','2010-08-16')
INSERT INTO #WEEKS VALUES (2010,12,'2010-08-17','2010-08-23')
INSERT INTO #WEEKS VALUES (2010,13,'2010-08-24','2010-08-30')
INSERT INTO #WEEKS VALUES (2010,14,'2010-08-31','2010-09-06')
INSERT INTO #WEEKS VALUES (2010,15,'2010-09-07','2010-09-13')
INSERT INTO #WEEKS VALUES (2010,16,'2010-09-14','2010-09-20')
INSERT INTO #WEEKS VALUES (2010,17,'2010-09-21','2010-09-27')
INSERT INTO #WEEKS VALUES (2010,18,'2010-09-28','2010-10-04')
INSERT INTO #WEEKS VALUES (2010,19,'2010-10-05','2010-10-11')
INSERT INTO #WEEKS VALUES (2010,20,'2010-10-12','2010-10-18')
INSERT INTO #WEEKS VALUES (2010,21,'2010-10-19','2010-10-25')
INSERT INTO #WEEKS VALUES (2010,22,'2010-10-26','2010-11-01')
INSERT INTO #WEEKS VALUES (2010,23,'2010-11-02','2010-11-08')
INSERT INTO #WEEKS VALUES (2010,24,'2010-11-09','2010-11-15')
INSERT INTO #WEEKS VALUES (2010,25,'2010-11-16','2010-11-22')
INSERT INTO #WEEKS VALUES (2010,26,'2010-11-23','2010-11-29')
INSERT INTO #WEEKS VALUES (2010,27,'2010-11-30','2010-12-06')
INSERT INTO #WEEKS VALUES (2010,28,'2010-12-07','2010-12-13')
INSERT INTO #WEEKS VALUES (2010,29,'2010-12-14','2010-12-20')
INSERT INTO #WEEKS VALUES (2010,30,'2010-12-21','2010-12-27')
INSERT INTO #WEEKS VALUES (2010,31,'2010-12-28','2011-01-03')
INSERT INTO #WEEKS VALUES (2010,32,'2011-01-04','2011-01-10')
INSERT INTO #WEEKS VALUES (2010,33,'2011-01-11','2011-01-17')
INSERT INTO #WEEKS VALUES (2010,34,'2011-01-18','2011-01-24')
INSERT INTO #WEEKS VALUES (2010,35,'2011-01-25','2011-01-31')
INSERT INTO #WEEKS VALUES (2010,36,'2011-02-01','2011-02-07')
INSERT INTO #WEEKS VALUES (2010,37,'2011-02-08','2011-02-14')
INSERT INTO #WEEKS VALUES (2010,38,'2011-02-15','2011-02-21')
INSERT INTO #WEEKS VALUES (2010,39,'2011-02-22','2011-02-28')
INSERT INTO #WEEKS VALUES (2010,40,'2011-03-01','2011-03-07')
INSERT INTO #WEEKS VALUES (2010,41,'2011-03-08','2011-03-14')
INSERT INTO #WEEKS VALUES (2010,42,'2011-03-15','2011-03-21')
INSERT INTO #WEEKS VALUES (2010,43,'2011-03-22','2011-03-28')
INSERT INTO #WEEKS VALUES (2010,44,'2011-03-29','2011-04-04')
INSERT INTO #WEEKS VALUES (2010,45,'2011-04-05','2011-04-11')
INSERT INTO #WEEKS VALUES (2010,46,'2011-04-12','2011-04-18')
INSERT INTO #WEEKS VALUES (2010,47,'2011-04-19','2011-04-25')
INSERT INTO #WEEKS VALUES (2010,48,'2011-04-26','2011-05-02')
INSERT INTO #WEEKS VALUES (2010,49,'2011-05-03','2011-05-09')
INSERT INTO #WEEKS VALUES (2010,50,'2011-05-10','2011-05-16')
INSERT INTO #WEEKS VALUES (2010,51,'2011-05-17','2011-05-23')
INSERT INTO #WEEKS VALUES (2010,52,'2011-05-24','2011-05-31')
INSERT INTO #WEEKS VALUES (2011,1,'2011-06-01','2011-06-07')
INSERT INTO #WEEKS VALUES (2011,2,'2011-06-08','2011-06-14')
INSERT INTO #WEEKS VALUES (2011,3,'2011-06-15','2011-06-21')
INSERT INTO #WEEKS VALUES (2011,4,'2011-06-22','2011-06-28')
INSERT INTO #WEEKS VALUES (2011,5,'2011-06-29','2011-07-05')
INSERT INTO #WEEKS VALUES (2011,6,'2011-07-06','2011-07-12')
INSERT INTO #WEEKS VALUES (2011,7,'2011-07-13','2011-07-19')
INSERT INTO #WEEKS VALUES (2011,8,'2011-07-20','2011-07-26')
INSERT INTO #WEEKS VALUES (2011,9,'2011-07-27','2011-08-02')
INSERT INTO #WEEKS VALUES (2011,10,'2011-08-03','2011-08-09')
INSERT INTO #WEEKS VALUES (2011,11,'2011-08-10','2011-08-16')
INSERT INTO #WEEKS VALUES (2011,12,'2011-08-17','2011-08-23')
INSERT INTO #WEEKS VALUES (2011,13,'2011-08-24','2011-08-30')
INSERT INTO #WEEKS VALUES (2011,14,'2011-08-31','2011-09-06')
INSERT INTO #WEEKS VALUES (2011,15,'2011-09-07','2011-09-13')
INSERT INTO #WEEKS VALUES (2011,16,'2011-09-14','2011-09-20')
INSERT INTO #WEEKS VALUES (2011,17,'2011-09-21','2011-09-27')
INSERT INTO #WEEKS VALUES (2011,18,'2011-09-28','2011-10-04')
INSERT INTO #WEEKS VALUES (2011,19,'2011-10-05','2011-10-11')
INSERT INTO #WEEKS VALUES (2011,20,'2011-10-12','2011-10-18')
INSERT INTO #WEEKS VALUES (2011,21,'2011-10-19','2011-10-25')
INSERT INTO #WEEKS VALUES (2011,22,'2011-10-26','2011-11-01')
INSERT INTO #WEEKS VALUES (2011,23,'2011-11-02','2011-11-08')
INSERT INTO #WEEKS VALUES (2011,24,'2011-11-09','2011-11-15')
INSERT INTO #WEEKS VALUES (2011,25,'2011-11-16','2011-11-22')
INSERT INTO #WEEKS VALUES (2011,26,'2011-11-23','2011-11-29')
INSERT INTO #WEEKS VALUES (2011,27,'2011-11-30','2011-12-06')
INSERT INTO #WEEKS VALUES (2011,28,'2011-12-07','2011-12-13')
INSERT INTO #WEEKS VALUES (2011,29,'2011-12-14','2011-12-20')
INSERT INTO #WEEKS VALUES (2011,30,'2011-12-21','2011-12-27')
INSERT INTO #WEEKS VALUES (2011,31,'2011-12-28','2012-01-03')
INSERT INTO #WEEKS VALUES (2011,32,'2012-01-04','2012-01-10')
INSERT INTO #WEEKS VALUES (2011,33,'2012-01-11','2012-01-17')
INSERT INTO #WEEKS VALUES (2011,34,'2012-01-18','2012-01-24')
INSERT INTO #WEEKS VALUES (2011,35,'2012-01-25','2012-01-31')
INSERT INTO #WEEKS VALUES (2011,36,'2012-02-01','2012-02-07')
INSERT INTO #WEEKS VALUES (2011,37,'2012-02-08','2012-02-14')
INSERT INTO #WEEKS VALUES (2011,38,'2012-02-15','2012-02-21')
INSERT INTO #WEEKS VALUES (2011,39,'2012-02-22','2012-02-29')
INSERT INTO #WEEKS VALUES (2011,40,'2012-03-01','2012-03-07')
INSERT INTO #WEEKS VALUES (2011,41,'2012-03-08','2012-03-14')
INSERT INTO #WEEKS VALUES (2011,42,'2012-03-15','2012-03-21')
INSERT INTO #WEEKS VALUES (2011,43,'2012-03-22','2012-03-28')
INSERT INTO #WEEKS VALUES (2011,44,'2012-03-29','2012-04-04')
INSERT INTO #WEEKS VALUES (2011,45,'2012-04-05','2012-04-11')
INSERT INTO #WEEKS VALUES (2011,46,'2012-04-12','2012-04-18')
INSERT INTO #WEEKS VALUES (2011,47,'2012-04-19','2012-04-25')
INSERT INTO #WEEKS VALUES (2011,48,'2012-04-26','2012-05-02')
INSERT INTO #WEEKS VALUES (2011,49,'2012-05-03','2012-05-09')
INSERT INTO #WEEKS VALUES (2011,50,'2012-05-10','2012-05-16')
INSERT INTO #WEEKS VALUES (2011,51,'2012-05-17','2012-05-23')
INSERT INTO #WEEKS VALUES (2011,52,'2012-05-24','2012-05-31')
INSERT INTO #WEEKS VALUES (2012,1,'2012-06-01','2012-06-07')
INSERT INTO #WEEKS VALUES (2012,2,'2012-06-08','2012-06-14')
INSERT INTO #WEEKS VALUES (2012,3,'2012-06-15','2012-06-21')
INSERT INTO #WEEKS VALUES (2012,4,'2012-06-22','2012-06-28')
INSERT INTO #WEEKS VALUES (2012,5,'2012-06-29','2012-07-05')
INSERT INTO #WEEKS VALUES (2012,6,'2012-07-06','2012-07-12')
INSERT INTO #WEEKS VALUES (2012,7,'2012-07-13','2012-07-19')
INSERT INTO #WEEKS VALUES (2012,8,'2012-07-20','2012-07-26')
INSERT INTO #WEEKS VALUES (2012,9,'2012-07-27','2012-08-02')
INSERT INTO #WEEKS VALUES (2012,10,'2012-08-03','2012-08-09')
INSERT INTO #WEEKS VALUES (2012,11,'2012-08-10','2012-08-16')
INSERT INTO #WEEKS VALUES (2012,12,'2012-08-17','2012-08-23')
INSERT INTO #WEEKS VALUES (2012,13,'2012-08-24','2012-08-30')
INSERT INTO #WEEKS VALUES (2012,14,'2012-08-31','2012-09-06')
INSERT INTO #WEEKS VALUES (2012,15,'2012-09-07','2012-09-13')
INSERT INTO #WEEKS VALUES (2012,16,'2012-09-14','2012-09-20')
INSERT INTO #WEEKS VALUES (2012,17,'2012-09-21','2012-09-27')
INSERT INTO #WEEKS VALUES (2012,18,'2012-09-28','2012-10-04')
INSERT INTO #WEEKS VALUES (2012,19,'2012-10-05','2012-10-11')
INSERT INTO #WEEKS VALUES (2012,20,'2012-10-12','2012-10-18')
INSERT INTO #WEEKS VALUES (2012,21,'2012-10-19','2012-10-25')
INSERT INTO #WEEKS VALUES (2012,22,'2012-10-26','2012-11-01')
INSERT INTO #WEEKS VALUES (2012,23,'2012-11-02','2012-11-08')
INSERT INTO #WEEKS VALUES (2012,24,'2012-11-09','2012-11-15')
INSERT INTO #WEEKS VALUES (2012,25,'2012-11-16','2012-11-22')
INSERT INTO #WEEKS VALUES (2012,26,'2012-11-23','2012-11-29')
INSERT INTO #WEEKS VALUES (2012,27,'2012-11-30','2012-12-06')
INSERT INTO #WEEKS VALUES (2012,28,'2012-12-07','2012-12-13')
INSERT INTO #WEEKS VALUES (2012,29,'2012-12-14','2012-12-20')
INSERT INTO #WEEKS VALUES (2012,30,'2012-12-21','2012-12-27')
INSERT INTO #WEEKS VALUES (2012,31,'2012-12-28','2013-01-03')
INSERT INTO #WEEKS VALUES (2012,32,'2013-01-04','2013-01-10')
INSERT INTO #WEEKS VALUES (2012,33,'2013-01-11','2013-01-17')
INSERT INTO #WEEKS VALUES (2012,34,'2013-01-18','2013-01-24')
INSERT INTO #WEEKS VALUES (2012,35,'2013-01-25','2013-01-31')
INSERT INTO #WEEKS VALUES (2012,36,'2013-02-01','2013-02-07')
INSERT INTO #WEEKS VALUES (2012,37,'2013-02-08','2013-02-14')
INSERT INTO #WEEKS VALUES (2012,38,'2013-02-15','2013-02-21')
INSERT INTO #WEEKS VALUES (2012,39,'2013-02-22','2013-02-28')
INSERT INTO #WEEKS VALUES (2012,40,'2013-03-01','2013-03-07')
INSERT INTO #WEEKS VALUES (2012,41,'2013-03-08','2013-03-14')
INSERT INTO #WEEKS VALUES (2012,42,'2013-03-15','2013-03-21')
INSERT INTO #WEEKS VALUES (2012,43,'2013-03-22','2013-03-28')
INSERT INTO #WEEKS VALUES (2012,44,'2013-03-29','2013-04-04')
INSERT INTO #WEEKS VALUES (2012,45,'2013-04-05','2013-04-11')
INSERT INTO #WEEKS VALUES (2012,46,'2013-04-12','2013-04-18')
INSERT INTO #WEEKS VALUES (2012,47,'2013-04-19','2013-04-25')
INSERT INTO #WEEKS VALUES (2012,48,'2013-04-26','2013-05-02')
INSERT INTO #WEEKS VALUES (2012,49,'2013-05-03','2013-05-09')
INSERT INTO #WEEKS VALUES (2012,50,'2013-05-10','2013-05-16')
INSERT INTO #WEEKS VALUES (2012,51,'2013-05-17','2013-05-23')
INSERT INTO #WEEKS VALUES (2012,52,'2013-05-24','2013-05-31')
INSERT INTO #WEEKS VALUES (2013,1,'2013-06-01','2013-06-07')
INSERT INTO #WEEKS VALUES (2013,2,'2013-06-08','2013-06-14')
INSERT INTO #WEEKS VALUES (2013,3,'2013-06-15','2013-06-21')
INSERT INTO #WEEKS VALUES (2013,4,'2013-06-22','2013-06-28')
INSERT INTO #WEEKS VALUES (2013,5,'2013-06-29','2013-07-05')
INSERT INTO #WEEKS VALUES (2013,6,'2013-07-06','2013-07-12')
INSERT INTO #WEEKS VALUES (2013,7,'2013-07-13','2013-07-19')
INSERT INTO #WEEKS VALUES (2013,8,'2013-07-20','2013-07-26')
INSERT INTO #WEEKS VALUES (2013,9,'2013-07-27','2013-08-02')
INSERT INTO #WEEKS VALUES (2013,10,'2013-08-03','2013-08-09')
INSERT INTO #WEEKS VALUES (2013,11,'2013-08-10','2013-08-16')
INSERT INTO #WEEKS VALUES (2013,12,'2013-08-17','2013-08-23')
INSERT INTO #WEEKS VALUES (2013,13,'2013-08-24','2013-08-30')
INSERT INTO #WEEKS VALUES (2013,14,'2013-08-31','2013-09-06')
INSERT INTO #WEEKS VALUES (2013,15,'2013-09-07','2013-09-13')
INSERT INTO #WEEKS VALUES (2013,16,'2013-09-14','2013-09-20')
INSERT INTO #WEEKS VALUES (2013,17,'2013-09-21','2013-09-27')
INSERT INTO #WEEKS VALUES (2013,18,'2013-09-28','2013-10-04')
INSERT INTO #WEEKS VALUES (2013,19,'2013-10-05','2013-10-11')
INSERT INTO #WEEKS VALUES (2013,20,'2013-10-12','2013-10-18')
INSERT INTO #WEEKS VALUES (2013,21,'2013-10-19','2013-10-25')
INSERT INTO #WEEKS VALUES (2013,22,'2013-10-26','2013-11-01')
INSERT INTO #WEEKS VALUES (2013,23,'2013-11-02','2013-11-08')
INSERT INTO #WEEKS VALUES (2013,24,'2013-11-09','2013-11-15')
INSERT INTO #WEEKS VALUES (2013,25,'2013-11-16','2013-11-22')
INSERT INTO #WEEKS VALUES (2013,26,'2013-11-23','2013-11-29')
INSERT INTO #WEEKS VALUES (2013,27,'2013-11-30','2013-12-06')
INSERT INTO #WEEKS VALUES (2013,28,'2013-12-07','2013-12-13')
INSERT INTO #WEEKS VALUES (2013,29,'2013-12-14','2013-12-20')
INSERT INTO #WEEKS VALUES (2013,30,'2013-12-21','2013-12-27')
INSERT INTO #WEEKS VALUES (2013,31,'2013-12-28','2014-01-03')
INSERT INTO #WEEKS VALUES (2013,32,'2014-01-04','2014-01-10')
INSERT INTO #WEEKS VALUES (2013,33,'2014-01-11','2014-01-17')
INSERT INTO #WEEKS VALUES (2013,34,'2014-01-18','2014-01-24')
INSERT INTO #WEEKS VALUES (2013,35,'2014-01-25','2014-01-31')
INSERT INTO #WEEKS VALUES (2013,36,'2014-02-01','2014-02-07')
INSERT INTO #WEEKS VALUES (2013,37,'2014-02-08','2014-02-14')
INSERT INTO #WEEKS VALUES (2013,38,'2014-02-15','2014-02-21')
INSERT INTO #WEEKS VALUES (2013,39,'2014-02-22','2014-02-28')
INSERT INTO #WEEKS VALUES (2013,40,'2014-03-01','2014-03-07')
INSERT INTO #WEEKS VALUES (2013,41,'2014-03-08','2014-03-14')
INSERT INTO #WEEKS VALUES (2013,42,'2014-03-15','2014-03-21')
INSERT INTO #WEEKS VALUES (2013,43,'2014-03-22','2014-03-28')
INSERT INTO #WEEKS VALUES (2013,44,'2014-03-29','2014-04-04')
INSERT INTO #WEEKS VALUES (2013,45,'2014-04-05','2014-04-11')
INSERT INTO #WEEKS VALUES (2013,46,'2014-04-12','2014-04-18')
INSERT INTO #WEEKS VALUES (2013,47,'2014-04-19','2014-04-25')
INSERT INTO #WEEKS VALUES (2013,48,'2014-04-26','2014-05-02')
INSERT INTO #WEEKS VALUES (2013,49,'2014-05-03','2014-05-09')
INSERT INTO #WEEKS VALUES (2013,50,'2014-05-10','2014-05-16')
INSERT INTO #WEEKS VALUES (2013,51,'2014-05-17','2014-05-23')
INSERT INTO #WEEKS VALUES (2013,52,'2014-05-24','2014-05-31')
GO
Populate #ProfitLossDaily with sample data
/* In production, this would come from GL30000 or the history table */
WITH integers(i) AS
(
SELECT 0 AS i
UNION ALL
SELECT i + 1 FROM integers WHERE i < 10
)
INSERT INTO #ProfitLossDaily (TRXDATE, CRDTAMT, DEBITAMT)
SELECT
DATEADD(day,i, WEEK_STARTING) AS TRXDATE,
ABS(CHECKSUM(NewId())) % 1100 AS CRDTAMT, /* random number */
ABS(CHECKSUM(NewId())) % 1000 AS DEBITAMT /* random number */
FROM #WEEKS, integers
WHERE DATEADD(day,i, WEEK_STARTING) BETWEEN WEEK_STARTING AND WEEK_ENDING
AND YEAR BETWEEN 2005 AND 2011
AND DATEADD(day,i, WEEK_STARTING) < CURRENT_TIMESTAMP
GO
![Interactive visualization of a graph in python [closed]](https://www.devze.com/res/2023/04-10/09/92d32fe8c0d22fb96bd6f6e8b7d1f457.gif)
加载中,请稍侯......
精彩评论