Calculate The Growth Percentage Or Previous Dates Values

Feb 7, 2008

This code displays dates, File name, and File size for four seperate dates 11/20/2007 , 11/30/2007, 12/30/2007 and 01/31/2007 . I'm trying to show the percentage growth from date to date (ie 11/20/2007 -11/30/2007 percentage growth)

is there a way i can get the previous date file size for each entry, so i can have a variable for the calculation. Or i can get the calculate it within this code (ie database_size_mb / ((database_size_md ) where database_size_datetime -1) *100
or whatever the formula is for percentage growth.




Code Snippet
SELECT
Database_Size_Datetime,
Database_file_name,
Database_Size_Mb
FROM RC_STAT.dbo.Tbl_Database_Statistics AS Tbl_Database_Statistics_1
GROUP BY Database_Size_Datetime, Database_file_name, Database_Size_Mb

this is what it displays now:


2007-11-20 00:00:00.000 ACTReplication_Data 442.5000
2007-11-30 00:00:00.000 ACTReplication_Data 442.5000
2007-12-31 00:00:00.000 ACTReplication_Data 442.5000
2008-01-31 00:00:00.000 ACTReplication_Data 442.5000
2007-11-20 00:00:00.000 ACTReplication_Log 14.8125
2007-11-30 00:00:00.000 ACTReplication_Log 109.7500
2007-12-31 00:00:00.000 ACTReplication_Log 112.9375
2008-01-31 00:00:00.000 ACTReplication_Log 115.5625
2007-11-20 00:00:00.000 BAMArchive 0.6875
2007-11-30 00:00:00.000 BAMArchive 0.6875
2007-12-31 00:00:00.000 BAMArchive 0.6875
2008-01-31 00:00:00.000 BAMArchive 0.6875
2007-11-20 00:00:00.000 BAMArchive_log 0.4922
2007-11-30 00:00:00.000 BAMArchive_log 0.4922
2007-12-31 00:00:00.000 BAMArchive_log 0.4922
2008-01-31 00:00:00.000 BAMArchive_log 0.4922

View 1 Replies


ADVERTISEMENT

Calculate Growth Percentage

Feb 5, 2008

I want to be able to display the percent of growth for databases on the server. My query has the file sizes and names and the dates, its done monthly . How do i create a field with the growth % from one month to the next, or from one date entry , to the next. so it appears like this

2006 2007 2008

23000 % growth 3400 %growth 20000

keep in mind, this is how it is displayed in reporting services with a matrix(crosstab)

Here's my query



Code Snippet
SELECT RC_STAT.dbo.Tbl_Database_Statistics.AutoId, RC_STAT.dbo.Tbl_Database_Statistics.Server_Description,
RC_STAT.dbo.Tbl_Database_Statistics.Database_Size_Datetime, RC_STAT.dbo.Tbl_Database_Statistics.Database_Description,
RC_STAT.dbo.Tbl_Database_Statistics.Database_Size_Mb, RC_STAT.dbo.Tbl_Database_Statistics.Database_Location,
RC_STAT.dbo.Tbl_Database_Statistics.Database_File_Name, DbSTAT.MonthlyDBTotal
FROM RC_STAT.dbo.Tbl_Database_Statistics INNER JOIN
(SELECT Database_Size_Datetime, Server_Description, Database_Description, SUM(Database_Size_Mb) AS MonthlyDBTotal
FROM RC_STAT.dbo.Tbl_Database_Statistics AS Tbl_Database_Statistics_1
GROUP BY Database_Size_Datetime, Server_Description, Database_Description) AS DbSTAT ON
DbSTAT.Server_Description = RC_STAT.dbo.Tbl_Database_Statistics.Server_Description AND
DbSTAT.Database_Description = RC_STAT.dbo.Tbl_Database_Statistics.Database_Description AND
DbSTAT.Database_Size_Datetime = RC_STAT.dbo.Tbl_Database_Statistics.Database_Size_Datetime

View 4 Replies View Related

Reporting Services :: Matrix - Percentage Growth Same Period Previous Year?

Aug 4, 2015

I'm trying to creating the following matrix in SSRS 2008R2 (with more product categories than shown here)The matrix only shows the last two year per product.
                           
The matrix in the report builder looks like this:

For me it's unclear what the expression should be. (or perhaps I need to make a calculation in my TSQL?)

As Row Groups in the matrix I've got: 

Product; Year

As Column Groups i've got Month

I can't get it to work. Previous doesn't seem to work in this case (only when I got a total of each year (e.g. one column instead of 12))

View 6 Replies View Related

Reporting Services :: Calculate Sales Percentage Difference Between Selected Year And Previous Year In A Matrix

Mar 27, 2015

I'm trying to generate a report using matrix like this

                                                      Month
Product     PreviousYearSalesAmount    SelectedYearSalesAmount      %SalesDifference

I can populate year sales amount, but i cant calculate the percentage.

Note: Month and Year are passed as parameters.

View 5 Replies View Related

Analysis :: Calculate Percentage Difference Of Two Values From Selected Years

Aug 31, 2015

Developing a measure which displays the difference of two values from the selected years.

An example : Show the difference of the sales amount from 2013 and 2015.

Since i am not really into mdx or calculated members.

View 6 Replies View Related

Reporting Services :: How To Calculate Percentage And Difference Of Two Values In Matrix Report In Ssrs 2008

Dec 2, 2014

I am creating matrix report with grouping on WEEK and Fiscalyearweek,I need to calculate of difference between FY14W01,FY15W01 ande  percentage of those..how to calculate in ssrs level.

View 13 Replies View Related

SQL Calculate Percentage

May 6, 2008

Hello every one, I am trying to get the total percentage of a column









Regions
Workbooks Required
Workbooks Sent 
Workbooks Returned
Workbooks Complete

A
20
21
20
18

B
33
33
33
30

C
19
29
18
16

D
9
18
8
8

Totals




Thanks in advance for any tips/solutions.

View 5 Replies View Related

How To Calculate Percentage

Apr 28, 2004

b/w two colums?

i have two numberic columns
i want percentage of column2/column1
They are on separate table.. l am intended to creat view with my results.
thanks

View 8 Replies View Related

Calculate Percentage Value

Aug 29, 2013

I have requirement in which i need to calculate percentage value based on billamount and concession amount..to calculate the percentage part and show the o/p.

SELECT PP.KID_ID_NO_V,(PP.FIRSTNAME_V + SPACE(1) + PP.LASTNAME_V)AS [PATIENT_NAME],BM.TOTAL_AMOUNT_M AS [BILL_AMOUNT],BM.CONCESSION_AMOUNT_M AS [CONCESSION_AMOUNT],BM.BILL_AMOUNT_M AS [TOTAL_AMOUNT],
FROM BILL_MASTER BM
INNER JOIN PATIENT_PROFILE PP ON BM.PATIENT_ID_N=PP.PATIENT_ID_N
WHERE BM.BILL_SETTLED_C='Y'

[code]....

View 5 Replies View Related

Calculate Percentage In Sql

Dec 13, 2005

Hi, Please help.

I need to calculate percentage [COUNT(ALERT_RECEIVED_DATE) FRAUDCT,over SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK] I tried my best but I cant come up with the solution.

Please, help.

Thk


SELECT
CONVERT(nvarchar(10),dateadd(d,-day(ALERT_RECEIVED_DATE) + 1,ALERT_RECEIVED_DATE),101) PERIOD,
COUNT(ALERT_RECEIVED_DATE) FRAUDCT,
SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK,
SUM(CASE WHEN FRAUD_DECISION ='D' THEN 1 ELSE 0 END) DECLINED,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) PENDING_EXCEPTION,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION='D' THEN 1 ELSE 0 END) DECLINED_EXCEPTION
FROM
TBLFRAUDFINDER O

View 3 Replies View Related

Calculate Percentage In Sql

Dec 13, 2005

Hi, Please help.

I need to calculate percentage [COUNT(ALERT_RECEIVED_DATE) FRAUDCT,over SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK] I tried my best but I cant come up with the solution.

Please, help.

Thk


SELECT
CONVERT(nvarchar(10),dateadd(d,-day(ALERT_RECEIVED_DATE) + 1,ALERT_RECEIVED_DATE),101) PERIOD,
COUNT(ALERT_RECEIVED_DATE) FRAUDCT,
SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK,
SUM(CASE WHEN FRAUD_DECISION ='D' THEN 1 ELSE 0 END) DECLINED,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) PENDING_EXCEPTION,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION='D' THEN 1 ELSE 0 END) DECLINED_EXCEPTION
FROM
TBLFRAUDFINDER O

View 1 Replies View Related

Calculate Average Growth Rate

Apr 30, 2008

I've got a statistics table that I've been writing to for about 2 years now. Every saturday night, a size (in MB) snapshot of each DB file is taken and dumped into this table. I'm then emailed a copy for that week.

Now, I'm trying to figure out what the fastest growers are. Here's the table ddl

CREATE TABLE [dbo].[DBSizeStats] (
[statid] [int] IDENTITY (1, 1) NOT NULL ,
[LogDate] [datetime] NULL ,
[Server] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DBName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MDFName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MDFSize] [decimal](18, 0) NULL ,
[LDFName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LDFSize] [decimal](18, 0) NULL ,
[TotalSize] [decimal](18, 0) NULL
) ON [PRIMARY]
GO


What I'm trying to figure out is how to query the average monthly and yearly growth percentages per DB on the MDFSize column.

I'm usually pretty good at this sort of thing, but I just can't seem to wrap my head around how to solve this issue. I'm not having a very good math day.

what am I missing here?

View 10 Replies View Related

Need Sql To Calculate Percentage Of Difference

Oct 23, 2007



so I have some data that looks like this:
semester weekOfSemester counts
Fall 2006 4 1
Fall 2007 4 6



I want to eventually graphically represent this data over the 18 weeks of the semester in terms of Fall 2007. I need to show change weather positive or negative as a percentage against Fall 2006. Can someone help with the sql?


thanks

kam

View 3 Replies View Related

T-SQL (SS2K8) :: Calculate Sum Of Dates Minus Repetitive Dates

Jul 18, 2014

Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,

[code]....

View 9 Replies View Related

SSRS Report Question To Calculate Overall Percentage For Individual Employee

Feb 22, 2008

Hi All,

I have an SSRS reports that uses a list table to display the name of staff and the name of the department that he supports as well as the number of hours they support the department. Below would be a sample of the report:

Name Department Hours
Staff 1 Dept 1 2
Staff 1 Dept 2 4
Staff 1 Dept 3 1
Staff 2 Dept 1 2
Staff 2 Dept 4 1

Now I would like to add 1 more column to show the percentage (%) of time spent at the department for the employee. Therefore each employee % of time spent will be 100% spread over all the supported departments. So the report will be as below:


Name Department Hours Time(%)
Staff 1 Dept 1 2 28.57
Staff 1 Dept 2 4 57.14
Staff 1 Dept 3 1 14.29
Staff 2 Dept 1 2 66.67
Staff 2 Dept 4 1 33.33

Is this possible to be created in SSRS as this is really required. If not is there any thing I can do on the DB or data set to show the time %.

Thanks & Regards,
Fadzli

View 3 Replies View Related

Calculate A Sum For The Previous Three Months?

Sep 15, 2015

Is there an easy way to calculate a sum for the previous three months?

Data
Date,Area,PropertyID,Volume,DaysInMonth
197904,6,888888,9589,30
197905,6,888888,27403,31
197906,6,888888,17130,30
197907,6,888888,14321,31
197908,6,888888,15234,31

[Code] .....

Desired Output

Date,Area,PropertyID,Volume,DaysInMonth,3MonthSum, DaysInMonthSUm
197904,6,888888,9589,30,NULL,NULL
197905,6,888888,27403,31,NULL,NULL
197906,6,888888,17130,30,NULL,NULL
197907,6,888888,14321,31,54122,91
197908,6,888888,15234,31,58854,92

[Code] .....

View 2 Replies View Related

Variables And Grouping - Calculate Net Sales And Margin Percentage For Each Month Of Current Year

Mar 11, 2014

I need to calculate “NET_SALES” and “MARGIN_PERCENT” for each month of the current year … the following returns the same values for each month in the list, which are for the current month. Taking out the GROUP BY line works fine for an overall number.

SALES_MONTH, NET_SALES, MARGIN_PERCENT
January, 1246627.69, 24
February, 1246627.69, 24
March, 1246627.69, 24
-------------------------------------------------

DECLARE @NetSales DECIMAL(18,6)
DECLARE @Cost DECIMAL(18,6)

SELECT
@NetSales = sum(IL.MERCHANDISE+IL.TAX)
,@Cost = sum(IL.COST)
FROM INVOICELINE IL

[Code] .....

View 2 Replies View Related

SQL Query - DateTime - Percentage Between Two Dates For A Year (Jan. 01 - Dec. 31)

Jan 14, 2008

Hello, I need to find the percentage of a a given contract start and end date for the year given.For example, the contract_start date is 05/08/2000 and the contract_end date is 04/30/2010, of course this will be 100% if you want to find the percentage for year 2008    but if you wanted to find the percentage for year  2010, then the percentage would be something like 42% (appr. 5 months) (b/c it would for year 2010, the contract would be from 01/01/2010 thru 04/30/2010)I need to find the percent from the beginning of the year, to the end.a few examples:    start: 01/01/2007   end: 05/01/2007   if for year: 2007; this is 4 months @33.33%    start: 05/01/ 2006   end: 06/01/2007  if for year: 2007, then 6 months @ 50% (01/01/2007 - 06/01/2007); but if it was for year 2006 then it would be 7 months @ 58% (05/01/2006 thru 12/31/2006)     start: 01/01/2000   end: 03/01/2010 for year: 2008 then 12 months @ 100% Any help would be valued. Thank you! 

View 4 Replies View Related

T-SQL (SS2K8) :: Calculate And Return Previous Date At 18:00 Hours

Mar 4, 2014

How can I calculate and return the previous Date at 18:00 Hours?

Here is a miserable attempt:

DECLARE @RunDate SmallDateTime
DECLARE @CurrentDate SmallDateTime
DECLARE @RunDateWoTime SmallDateTime
SET @CurrentDate = GETDATE()

SET @RunDate = DATEADD(day,-1,@CurrentDate)-- AS CurrentDate
SELECT @RunDate AS RunDate

-- Desired Result is the following:

-- 2014-03-03 18:00

View 9 Replies View Related

Retrieve Dates Within The Previous Month

Apr 9, 2008

I have a CheckDate field and I only what to retrieve dates that fall in the month previous to when the query is run. I'm assuming it will involve DATEADD or DATEPART, but I'm not sure how to do it since the end of the month day will vary from month to month.

For example, today is 04/09/2008 so I want to retrieve checks with a date between 03/01/2008 and 03/31/2008.

View 13 Replies View Related

Calculate First And Last Dates From DATEPART

Aug 1, 2000

I'm kinda stumped....
How would one find the first and and last date in a date range defined by a DATEPART, regardless of the parameter?

For Example...
DATEPART(wk,7/31/2000) would return 7/30/2000 and 8/5/2000
DATEPART(qq,7/31/2000) would return 7/1/2000 and 9/30/2000
DATEPART(yy,7/31/2000) would return 1/1/2000 and 12/31/2000

TIA,
Charles

View 3 Replies View Related

Calculate Difference Between Two Dates

Oct 18, 2006

Hi, i'm trying to calculate the number of days between two dates, but within an UPDATE statement, so far I can't wrap my head around how I can update a field with the number of days.

I was thinking something like


Code:

Update #ClaimMaster
Set covered_days = (then insert select statement that subtracts the two dates)



Does that make any sense?

View 1 Replies View Related

Calculate Differences Between Two Dates

Sep 9, 2014

How can we calculate the difference between two dates (years, months and days)

example:

between '01 / 01/2011 'and '05 / 04/2014' I would have years, months and days

View 1 Replies View Related

Defaulting Start And End Dates To The Previous Month.

May 17, 2007

I have two parameters in my report (StartDate and EndDate). I want to default these parameters to the previous month.

For example... If today is 5/17/2007, I want StartDate to be 4/1/2007 and EndDate to be 4/30/2007. If today would be January 30th 2007, I would want StartDate to be 12/1/2006 and EndDate to be 12/31/2006.

How can I do this?

View 2 Replies View Related

Calculate Date Difference Excluding Dates

Aug 27, 2012

I have already seen stored procedures that can calculate a difference in dates, excluding the weekends. Any extension of such a SQL query to exclude not only weekends, but other dates as well. We have a table of "holidays" (not necessarily standard holidays), and I am wondering if there is a way to exclude them from the calculation.

View 7 Replies View Related

T-SQL (SS2K8) :: Calculate And Display Week Between Two Dates

Nov 4, 2015

I want to display week between two dates as below.

requirement is as:

suppose there are two dates.(which will comes dynamically, so no. of weeks varied)
10/20/2015 and 01/01/2016

Now between this two dates, i want to calculate number of weeks on another date which is coming from table.

Say for example the column date is coming as 10/23/2015 then it will fall in week-1

Same way if 11/01/2015 falls in week2.

View 3 Replies View Related

T-SQL (SS2K8) :: Pass Dates For Previous Months Start And End Date

Mar 16, 2015

I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1,
DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1

View 1 Replies View Related

SQL Server 2008 :: How To Subtract Dates From Previous Date On Same Patient

Jun 22, 2015

I have a list of patient encounter dates ordered by the date. I need to subtract the previous date in order to get the number of days between each date for the same patient.

create table TEST
(
MRN varchar(10),
EncDTTM datetime,
Sequence int
)
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-01-24','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-02-03','2')

[code]....

View 9 Replies View Related

SQL Server 2012 :: How To Calculate Dates Difference In A Same Table

Sep 4, 2015

I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.

Each customer is uniquely identified by a customer#

I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.

How can i do that in a query?

View 5 Replies View Related

Power Pivot :: Calculate Number Of Business Days Between Two Dates

May 14, 2015

I am looking for a formula to calculate the number of weekdays/business days between two dates in power pivot.I do the same in SQl using  the following query

    DATEDIFF(dd, Date1, GETDATE()) - (DATEDIFF(wk, Date1, GETDATE()) * 2) -
    CASE WHEN DATEPART(dw, Date1) = 1 THEN 1 ELSE 0 END +
    CASE WHEN DATEPART(dw, GETDATE()) = 1 THEN 1 ELSE 0 END END 

I am looking for a similar query in Power Pivot.

View 2 Replies View Related

SQL Server 2008 :: Calculate Number Of Months Between Dates For Multiple Records?

Oct 7, 2015

I have a challenge and I'm not sure the best route to go. Consider the following dataset.

I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.

CustomerNumber SalesDate
1234 05/15/2010
1234 10/24/2010
1234 02/20/2011
1234 05/02/2012
1234 12/20/2012

What I want to know is the average number of months between the purchases. And do this for each customer.

View 6 Replies View Related

Power Pivot :: Calculating Values For Future Dates Based On Past Values

Nov 13, 2015

I am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?

View 6 Replies View Related

Get Percentage With Variation Of Field Values (country Names)

Sep 14, 2006

Any help here would be greatly appreciated...

Unfortunately, data wasn't filtered prior to getting inserted into this table. Now I am stuck with cleaning it up. I have thought about writing a query to update all the values, but there are just too many variations, including spelling mistakes, so I've ruled that out as a possible solution.

I have a table which has a Country field but the values per record vary. For example US, U.S., USA, United States, UK, United Kingdom, Canada, Can, etc. I'm trying to find the percent of records per country.

Sample table data: mytable
Id  Name  Country
1  John     US
2  James  UK
3  Jane     United States
4  Mary    Canada
5  Jack     U.S.
6  Tony     United Kingdom
7  Jeff       US
8  Tom     Canada
9  Beth     UK
10  Mark   USA

I would like to show
US: 50%   --> (includes any variation of US ncluding US, U.S., USA, United States)
UK: 30%
CAN: 20%

I've made several attempts myself with no luck. Thanks in advance.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved