December 16, 2011 at 3:53 am
Hi,
I am trying to calculate YTD totals for the following data, at the moment I'm getting a varchar field for my date e.g. '201101'. I will therefore be relieving data on a monthly basis with the data every month loaded with a new date depending on when it was loaded. I need to calculate the YTD, I dont want to hardcode this and was wondering if there was a way of doing this? as I dont have a date field in the data. But want to calculate all YTD for the current year, and want this figure to automatically calculate everytime new data comes in.
e.g. data
CompanyID Finance Month Zone total GWP
1 201101 Tango 555
1 201101 Tango 892
2 201102 Tango 888
2 201103 Apple 555
3 201104 Apple 9534
3 201104 Zulo 934
3 201105 Zulo 713
Also need to calculate the YTD figure for the last year?
December 16, 2011 at 3:59 am
apatel 80451 (12/16/2011)
Hi,I am trying to calculate YTD totals for the following data, at the moment I'm getting a varchar field for my date e.g. '201101'. I will therefore be relieving data on a monthly basis with the data every month loaded with a new date depending on when it was loaded. I need to calculate the YTD, I dont want to hardcode this and was wondering if there was a way of doing this? as I dont have a date field in the data. But want to calculate all YTD for the current year, and want this figure to automatically calculate everytime new data comes in.
e.g. data
CompanyID Finance Month Zone total GWP
1 201101 Tango 555
1 201101 Tango 892
2 201102 Tango 888
2 201103 Apple 555
3 201104 Apple 9534
3 201104 Zulo 934
3 201105 Zulo 713
Also need to calculate the YTD figure for the last year?
Sounds like you might need a view.
Can you also include what your desired results will look like?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 16, 2011 at 4:36 am
I want my YTD to be summed up by Zone column YTD:
Zone 2011 YTD
Tango 2335
Apple 10089
Zulo 1647
and preferably to have a 2010 YTD so I can calculate the variance between 2010 vs 2011.
December 16, 2011 at 5:17 am
apatel 80451 (12/16/2011)
I want my YTD to be summed up by Zone column YTD:Zone 2011 YTD
Tango 2335
Apple 10089
Zulo 1647
and preferably to have a 2010 YTD so I can calculate the variance between 2010 vs 2011.
Something like this
select zone, left([YearMonth],4) [Year], Sum(Total) [Total]
from table
group by zone, left([YearMonth],4)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 19, 2011 at 5:42 pm
Phil's given you a basic "group by" structure that will return the totals for each year. You gave us only 2011 data, but it'll break out other years too. If you wanted a bit more detail with that, you could let the group-by work on the finance month directly, but add a "with rollup" to get totals. This query returns group-by totals for each zone and month, then gives subtotals by year in each zone, another total for the zone for all years, and then a final total for all entries. Look in BOL for more on using "Group by" and "with Rollup".
Declare @myTable Table
(CompanyID int
,[Finance Month] varchar(6)
,[Zone] varchar(20)
,[Total GWP] int
)
Insert @myTable (CompanyID, [Finance Month], [Zone], [Total GWP] )
Select 1, '201101', 'Tango', 555 Union All
Select 1, '201101', 'Tango', 892 Union All
Select 2, '201102', 'Tango', 888 Union All
Select 2, '201103', 'Apple', 555 Union All
Select 3, '201104', 'Apple', 9534 Union All
Select 3, '201104', 'Zulo', 934 Union All
Select 2, '201003', 'Apple', 555 Union All
Select 3, '201004', 'Apple', 9534 Union All
Select 3, '201004', 'Zulo', 934 Union All
Select 3, '201105', 'Zulo', 713
select zone
,[Finance Month]
,left([Finance Month],4) [Year]
,Sum([Total GWP]) [Total]
from @mytable
group by zone
,left([Finance Month],4) -- [Year]
,[Finance Month]
with rollup
If your intention was truly a running total, showing each zone's YTD number as the sum of the current month and all prior months in the year, then you'll want to study Jeff Moden's excellent article on that topic at http://www.sqlservercentral.com/articles/68467/.
December 20, 2011 at 4:21 am
So how would I be able to select totals for the year from the query? If it only returns 'NULL's?
December 20, 2011 at 9:47 am
Hi Phil,
Im successfully getting the YTD figure for the current year, but how can i compare this figure to data for the for the equivalent month the year before?
December 20, 2011 at 9:56 am
apatel 80451 (12/20/2011)
Hi Phil,Im successfully getting the YTD figure for the current year, but how can i compare this figure to data for the for the equivalent month the year before?
Please post your query so far.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 20, 2011 at 11:57 am
apatel 80451 (12/20/2011)
Hi Phil,Im successfully getting the YTD figure for the current year, but how can i compare this figure to data for the for the equivalent month the year before?
The problem of comparing a month to the same month in the prior year is fairly simple if you use a calendar table. Here's an article explaining how to set up a monthly calendar table and the last example shows how to compare figures from a particular month to the same month in the prior year.
http://www.sqlservercentral.com/articles/T-SQL/70482/
This next article explains how to use a calendar table as the anchor for a query. That means the time period will show up based on the calendar table even if there are no matching rows for the time period.
http://www.sqlservercentral.com/articles/T-SQL/70743/
Between these 2 you should be able to come up with what you want.
Todd Fifield
December 20, 2011 at 12:38 pm
apatel 80451 (12/20/2011)
So how would I be able to select totals for the year from the query? If it only returns 'NULL's?
As Phil suggested, it would be helpful to see what you've got for a query so far, but also perhaps a mock-up of desired results. I (and perhaps others) am a bit fuzzy on what exactly you're trying to achieve.
Meanwhile, if the query using "with rollup" gives you the basic structure that you're looking for, then you could use the grouping() function with case statements to replace those NULL results with labels. This may or may not be appropriate if your goal is to pair monthly totals by zone with the corresponding month in the prior year. Different problems.
So, please post an example of what you're looking for.
December 21, 2011 at 5:35 am
I think creating a calender table is going to be too tricky for me.
here is my data:
company yearmonth totalGWP
AIUA201001600.00
AIUA2010021666.00
AIUA2010031300.00
AIUA2010041136.00
AIUA2010051500.00
AIUA2010062800.00
AIUA2010071300.00
AIUA20100813200.00
AIUA20100910000.00
AIUA20101010000.00
AIUA20101112000.00
AIUA20101210000.00
AIUA20110122626.00
AIUA20110222826.00
AIUA201103582402.00
AIUA201104333842.00
AIUA20110547977.00
AIUA201106382950.00
AIUA20110717578.00
AIUA20110872243.00
AIUA20110917664.00
AIUA201110171336.00
AIUA201111169590.00
AIUA201112162118.00
so I have two years worth of data. I have the YTD total by getting year(getdate()). for my first column, 2nd column i want to show the 2010 YTD - 2011 YTD. How would I be able to get this because the results would need to reflect on which ever month the user is running the query. So if we are in may, I need to show 201005 YTD - 201105 YTD.
I have the first YTD query done:
select tpl_zone, Tpl_Month, sum(Tpl_TotalGWP)
from dbo.Tpl_zonegwpExtensionBase
where
left(tpl_month, 4) = year(getdate())
group by Tpl_Month,tpl_zone
but struggling to get the figure for same month YTD figure for 2010.
December 21, 2011 at 7:15 pm
apatel,
Sorry this took so long - I've been buried with work the last couple of days. I'm going to give you an example of using the calendar table described in the first article here:
http://www.sqlservercentral.com/articles/T-SQL/70482/
In that article I didn't have the Year in the table. So after creating the table and before creating the indexes run this code:
ALTER TABLE CalMonth
ADD YearNum INT
GO
UPDATE CalMonth
SET YearNum = DATEPART(year, MonthStart)
GO
Here is the code to create your table:
CREATE TABLE GWP
( Company VARCHAR(20)
, YearMonth INT
, TotalGWP DEC(15, 2)
)
GO
INSERT INTO GWP
( Company, YearMonth, TotalGWP )
SELECT 'AIUA', 201001, 600.00 UNION ALL
SELECT 'AIUA', 201002, 1666.00 UNION ALL
SELECT 'AIUA', 201003, 1300.00 UNION ALL
SELECT 'AIUA', 201004, 1136.00 UNION ALL
SELECT 'AIUA', 201005, 1500.00 UNION ALL
SELECT 'AIUA', 201006, 2800.00 UNION ALL
SELECT 'AIUA', 201007, 1300.00 UNION ALL
SELECT 'AIUA', 201008, 13200.00 UNION ALL
SELECT 'AIUA', 201009, 10000.00 UNION ALL
SELECT 'AIUA', 201010, 10000.00 UNION ALL
SELECT 'AIUA', 201011, 12000.00 UNION ALL
SELECT 'AIUA', 201012, 10000.00 UNION ALL
SELECT 'AIUA', 201101, 22626.00 UNION ALL
SELECT 'AIUA', 201102, 22826.00 UNION ALL
SELECT 'AIUA', 201103, 582402.00 UNION ALL
SELECT 'AIUA', 201104, 333842.00 UNION ALL
SELECT 'AIUA', 201105, 47977.00 UNION ALL
SELECT 'AIUA', 201106, 382950.00 UNION ALL
SELECT 'AIUA', 201107, 17578.00 UNION ALL
SELECT 'AIUA', 201108, 72243.00 UNION ALL
SELECT 'AIUA', 201109, 17664.00 UNION ALL
SELECT 'AIUA', 201110, 171336.00 UNION ALL
SELECT 'AIUA', 201111, 169590.00 UNION ALL
SELECT 'AIUA', 201112, 162118.00
GO
I try to avoid using GETDATE() in complex views and procedures. GETDATE() is not deterministic and it can lead to scans rather than seeks. I prefer to use a 1 row date table that describes the current date (today). It relates directly to the other calendar tables. Here's an example:
CREATE TABLE Today
( TheDate SMALLDATETIME
, YearMonth INT
, MonthID INT
, MonthNum INT
, YearNum INT
)
GO
I have a job that runs at midnight each day that updates the table. You have to already have the CalMonth table to use the procedure. It looks something like:
-- Procedure run just after midnight to get the correct day for today.
CREATE PROCEDURE SetToday
AS
IF NOT EXISTS (SELECT 1 FROM Today)
INSERT INTO Today
( TheDate, YearMonth, MonthID, MonthNum, YearNum )
SELECT
DATEADD(day, DATEDIFF(DAY, 0, GETDATE()), 0)
, YearMonth, MonthID, MonthNum, YearNum
FROM CalMonth
WHERE GETDATE() BETWEEN MonthStart And NextMonth
ELSE
UPDATE T
SET T.TheDate = DATEADD(day, DATEDIFF(DAY, 0, GETDATE()), 0)
, T.YearMonth = CM.YearMonth
, T.MonthID = CM.MonthID
, T.MonthNum = CM.MonthNum
, T.YearNum = CM.YearNum
FROM Today T
CROSS APPLY
(SELECT YearMonth, MonthID, MonthNum, YearNum
FROM CalMonth
WHERE GETDATE() BETWEEN MonthStart And NextMonth
) AS CM
GO
Just run the code:
EXEC SetToday
GO
To demonstrate the fact that we only want months up to the current month we'll set it back to some date in Oct 2011.
DECLARE
@TheDate SMALLDATETIME
SET @TheDate = '10/15/2011'
UPDATE T
SET T.TheDate = @TheDate
, T.YearMonth = CM.YearMonth
, T.MonthID = CM.MonthID
, T.MonthNum = CM.MonthNum
, T.YearNum = CM.YearNum
FROM Today T
CROSS APPLY
(SELECT YearMonth, MonthID, MonthNum, YearNum
FROM CalMonth
WHERE @TheDate BETWEEN MonthStart And NextMonth
) AS CM
We now have the Today table set properly to 15 Oct 2011.
Here is the code that should return what you want. Please read the part about triangular JOINs.
-- This technique uses a triangular JOIN. It works fine when the number
-- of rows in each partition is relatively small, and by that
-- I mean 40 - 50 rows. I've seen performance go into the
-- proverbial toilet at around 50 rows in each partition.
; WITH ThisYear AS
( SELECT GWP.Company, GWP.TotalGWP, CM.YearMonth, CM.MonthNum
FROM Today T
INNER JOIN CalMonth CM ON
CM.YearMonth <= T.YearMonth
AND CM.YearNum = T.YearNum
LEFT JOIN GWP AS GWP ON
GWP.YearMonth = CM.YearMonth
), LastYear AS
( SELECT GWP.Company, GWP.TotalGWP, CM.YearMonth, CM.MonthNum
FROM Today T
INNER JOIN CalMonth CM ON
CM.YearNum = T.YearNum - 1
AND CM.MonthNum <= T.MonthNum
LEFT JOIN GWP AS GWP ON
GWP.YearMonth = CM.YearMonth
)
SELECT TY.Company, TY.YearMonth, TY.TotalGWP
, TYYTD.YTD AS ThisYearYTD
, LY.YearMonth AS LastYearMonth
, LYYTD.YTD AS LastYearYTD
FROM ThisYear AS TY
LEFT JOIN LastYear AS LY ON
TY.Company = LY.Company
AND TY.MonthNum = LY.MonthNum
-- These 2 OUTER APPLY operations are the triangular JOINs.
OUTER APPLY
(SELECT SUM(TY1.TotalGWP) AS YTD
FROM ThisYear AS TY1
WHERE TY1.Company = TY.Company
AND TY1.MonthNum <= TY.MonthNum
) AS TYYTD -- This Year YTD
OUTER APPLY
(SELECT SUM(LY1.TotalGWP) AS YTD
FROM LastYear LY1
WHERE LY1.Company = TY.Company
AND LY1.MonthNum <= TY.MonthNum
) LYYTD -- Last Year YTD
I actually tested this and it seems to work properly.
Hope this helps.
Todd Fifield
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply