Calculating Numbers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Calculating Numbers

Author

Topic

BenBen
Starting Member

10 Posts

Posted - 2010-04-21 : 15:10:23

Hello All,

I'm looking for some help. I am trying to calculate the following:

Year over Year Change
Month over Previous Year's Month
Week over Previous Year's Week
Day over Previous Year's Day

Month over Previous Month Same Year
Week over Previous Week Same Year

The Years: 2008, 2009, 2010

Change In: The amount of requests from a ticketing system.

Date Field: DateOfRequest

What would be my best approach for a result set with the above calculations?

Thank you in advance.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-04-21 : 15:11:37

It would help if you can provide the table structure, sample data and expected output.

BenBen
Starting Member

10 Posts

Posted - 2010-04-21 : 16:02:50

I would like to use the results in sql reporting services when I create charts.

Here is the data.

Let me know if you need anything else.

Query for each below was from variations of the following query:

SELECT COUNT(DATEPART(yy, DateRequest)) AS "New Requests",
DATEPART(yy, DateRequest) AS Year
FROM [DB].[dbo].[TicketSystem]
GROUP BY DATEPART(yy, DateRequest)
ORDER BY Year asc

New Requests, Week, Month, Year
14,49,12,2008
26,50,12,2008
48,51,12,2008
23,52,12,2008
21,53,12,2008
4,1,1,2009
53,2,1,2009
89,3,1,2009
74,4,1,2009
65,5,1,2009
77,6,2,2009
76,7,2,2009
59,8,2,2009
73,9,2,2009
99,10,3,2009
96,11,3,2009
93,12,3,2009
100,13,3,2009
29,14,3,2009
60,14,4,2009
88,15,4,2009
104,16,4,2009
96,17,4,2009
72,18,4,2009
8,18,5,2009
81,19,5,2009
89,20,5,2009
93,21,5,2009
86,22,5,2009
101,23,6,2009
109,24,6,2009
85,25,6,2009
103,26,6,2009
37,27,6,2009
54,27,7,2009
104,28,7,2009
87,29,7,2009
110,30,7,2009
102,31,7,2009
100,32,8,2009
101,33,8,2009
123,34,8,2009
92,35,8,2009
12,36,8,2009
90,36,9,2009
98,37,9,2009
117,38,9,2009
129,39,9,2009
83,40,9,2009
53,40,10,2009
133,41,10,2009
121,42,10,2009
114,43,10,2009
119,44,10,2009
124,45,11,2009
110,46,11,2009
125,47,11,2009
55,48,11,2009
32,49,11,2009
109,49,12,2009
98,50,12,2009
121,51,12,2009
69,52,12,2009
58,53,12,2009
99,2,1,2010
117,3,1,2010
108,4,1,2010
129,5,1,2010
126,6,2,2010
109,7,2,2010
102,8,2,2010
124,9,2,2010
140,10,3,2010
144,11,3,2010
142,12,3,2010
99,13,3,2010
68,14,3,2010
48,14,4,2010
85,15,4,2010
103,16,4,2010
59,17,4,2010

New Requests, Month, Year
132,12,2008
285,1,2009
285,2,2009
417,3,2009
420,4,2009
357,5,2009
435,6,2009
457,7,2009
428,8,2009
517,9,2009
540,10,2009
446,11,2009
455,12,2009
453,1,2010
461,2,2010
593,3,2010
295,4,2010

New Requests, Year
132,2008
5042,2009
1802,2010

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 23:51:37

is this how data is stored or is this your reqd o/p format?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BenBen
Starting Member

10 Posts

Posted - 2010-04-22 : 01:17:39

They are just the results of three queries I ran to show you what data I have.

I guess I don't know what else you need?.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 04:55:36

what we need is how you want output to appear out of data above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-04-22 : 07:19:17

Follow the HOW TO ASK link in my signature, and it will tell you exactly what we need to help you.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

BenBen
Starting Member

10 Posts

Posted - 2010-04-22 : 15:22:19

Step 1.

How do I calculate the following calculations from a table?

Year over year
Month over same month previous year
Week over same week previous year
Day over same day previous year

Month over previous month
Week over previous week
Day over previous day

Step 2.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[eForm](
[TaskID] [int] IDENTITY(20000,1) NOT NULL,
[DateRequest] [smalldatetime] NULL
CONSTRAINT [PK_eForm] PRIMARY KEY CLUSTERED
(
[TaskID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Step 3.

INSERT INTO [LForm].[dbo].[eForm]
,[DateRequest])
VALUES
,01/01/2008

INSERT INTO [LForm].[dbo].[eForm]
,[DateRequest])
VALUES
,01/01/2009

INSERT INTO [LForm].[dbo].[eForm]
,[DateRequest])
VALUES
,02/01/2008

INSERT INTO [LForm].[dbo].[eForm]
,[DateRequest])
VALUES
,02/01/2009

INSERT INTO [LForm].[dbo].[eForm]
,[DateRequest])
VALUES
,01/01/2010

INSERT INTO [LForm].[dbo].[eForm]
,[DateRequest])
VALUES
,02/01/2010

Step 4.

SELECT COUNT(DATEPART(yy, DateRequest)) AS "New Requests",
DATEPART(ww, DateRequest) AS Week,
DATEPART(mm, DateRequest) AS Month,
DATEPART(yy, DateRequest) AS Year
FROM [LForm].[dbo].[eForm]
WHERE DateRequest IS NOT NULL
GROUP BY DATEPART(yy, DateRequest), DATEPART(mm, DateRequest),DATEPART(ww, DateRequest)
ORDER BY Year asc, Month asc, Week asc

Step 5.

The result can be in any form and even from separate queries comparing different parts of the date. Or it can be all in one if that is even possible.

For example: For Year over Year

2009 2008
500 Requests 250 Requests

The YOY would be 500-250=250

250/250=1

1*100=100 percent

YOY for 2009-2008 was 100 percent

The similiar for month and day of previous year.

And if possible the percentage difference from the same year. For example, this week there was a percentage change increase of 25 percent compared to the previous week of this year.

Again if such an analysis needs to be done in different queries, that is ok. Please execuse my low knowledge of SQL. Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 02:07:45

will output be conditional based on some parameter? or all analysis shown in parallel?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BenBen
Starting Member

10 Posts

Posted - 2010-04-23 : 02:54:46

all analysis shown in parallel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 04:18:05

i'll show for year. rest you can do similarly


SELECT COUNT(DATEPART(yy, DateRequest)) AS "New Requests", 
DATEPART(ww, DateRequest) AS Week,
DATEPART(mm, DateRequest) AS Month,
DATEPART(yy, DateRequest) AS Year
INTO #Temp
FROM [LForm].[dbo].[eForm]
WHERE DateRequest IS NOT NULL
GROUP BY DATEPART(yy, DateRequest), DATEPART(mm, DateRequest),DATEPART(ww, DateRequest) 
ORDER BY Year asc, Month asc, Week asc

SELECT Year,CntYr,CntPrev,(CntYr-CntPrev)*100.0/CntPrev
FROM
(
SELECT Year, SUM([New Requests]) AS CntYr
FROM #Temp
GROUP BY Year
)m
CROSS APPLY (SELECT SUM([New Requests]) AS CntPrev
             FROM #Temp
             WHERE Year=m.Year-1
            )n

DROP TABLE #Temp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BenBen
Starting Member

10 Posts

Posted - 2010-04-23 : 04:49:10

Thank you for the above.

Question:

I think that I need to be able to place two parameters into the script. As it stands it provides for data for one full year, but I don't know which year it is for. The YOY works fine. Im talking about when I do the MOM and WOW comparisons.

For the MOM and WOW, how would you do it with two paramters (years)?

I think that would do it.

You are great.

BenBen
Starting Member

10 Posts

Posted - 2010-04-23 : 05:01:03

Im all set sir with the parameters.

How would I compare one month over the previous year's same month?

The script does great for YOY. MOM for months in same year, but not from the previous year.

Does that make sense?

BenBen
Starting Member

10 Posts

Posted - 2010-04-23 : 06:04:25

Also, does the use of the temp table required? What I like to do it is use this as a dataset in SR so I can create a chart. I need fields to do that. Currently, it runs fine but no fields are actually there to use in design mode.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 06:10:18

quote:
Originally posted by BenBen

Thank you for the above.

Question:

I think that I need to be able to place two parameters into the script. As it stands it provides for data for one full year, but I don't know which year it is for. The YOY works fine. Im talking about when I do the MOM and WOW comparisons.

For the MOM and WOW, how would you do it with two paramters (years)?

I think that would do it.

You are great.

you need only singe parameter for MOM and WOW. you will always compare data for @Year with @Year-1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BenBen
Starting Member

10 Posts

Posted - 2010-04-23 : 09:13:34

Thanks for your expertise sir.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 09:19:51

no problem. you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources