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.
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.
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
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
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
I am trying to write a query to calculate the running difference between data on different dates. Below is what my table of data looks like. Basically I want to calculate the difference between the total_completed for each state and date.
DateStatesTotal_Completed
08/27/15CA 19,952
09/11/15CA 26,336
10/02/15CA 35,444
10/08/15CA 38,278
08/27/15CO2797
09/11/15CO3264
10/02/15CO4270
10/08/15CO4297
below is what I am trying to achieve:
DateStatesTotal_CompletedCompleted_Difference
08/27/15CA 19,952 0
09/11/15CA 26,336 6,384
10/02/15CA 35,444 9,108
10/08/15CA 38,278 2,834
08/27/15CO27970
09/11/15CO3264467
10/02/15CO42701,006
10/08/15CO429727
below is my code (I almost have what I need) I just can't figure out how show 0 as the completed_difference for the first Date for each state since there is no prior date to calculate against.
MRR_TOTALS_WEEK_OVER_WEEK AS
(
SELECT
T1.[Date]
,T1.States
,T2.Total_Completed
,ROW_NUMBER() OVER(PARTITION BY T1.States ORDER BY T1.States,T1.[Date]) AS ORDERING
FROM TOTAL_CHARTS T1
LEFT JOIN TOTAL_COMPLETED T2 ON T1.[Date] = T2.[Date] AND T1.States = T2.States
)
[code].....
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |...
Orders -> id | idCustomer | date | ...
Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
WHERE year(date)=2014
GROUP BY customers.name
ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015)
--------
customer A | 1000$ | 2000$ | 3000$
customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
What would be the most straight forword to Calculate 90 days and 3 Years ago from an Effective Date in a table?
as in
SELECT EffectiveDate
from FL.CEFHistory
I need to return the effective date - 90 days and 1 year from that.
[URL]
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.
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
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]....
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
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
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
Using 'TAIL([Time].[Time].[Year].MEMBERS,1)' I can get the current year dynamically. My question is how do I get the previous two years dynamically as well. I've tried a few different ways with no luck. As you can see below I'd like to replace 2014 and 2013 with current year -1 and current year -2.
SELECT
{
[Time].[Time].[Year].&[2013],
[Time].[Time].[Year].&[2014],
TAIL([Time].[Time].[Year].MEMBERS,1)
}ON COLUMNS,
NON EMPTY
{[Branches].[Branches].[Region]*[Measures].[Ship Resale S&D Run Rate]} ON ROWS
FROM SALES
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
calculating a rolling median over a period of 3 years.
I already calculate median and I've tried to calculate rolling median over a period of 3 years as below.
MEDIAN([Date].[Year].CurrentMember.Lag(3):[Date].[Year].CurrentMember,[Measures].[median])
What this does is, it calculates the median of the medians over the period of 3 years. But, what I'm looking for is the overall median of the underlying measure over a period of 3 years.
What I have now:
Year1 - 41,52,73; Median1 - 52
Year2 - 6,9,12; Median2- 9
Year3 - 24,68,89; Median3 - 68
Overall Median of 9,52,68 - 52
What I need:
Year1 - 41,52,73; Median1 - 52
Year2 - 6,9,12; Median2- 9
Year3 - 24,68,89; Median3 - 68
Overall Median of 41,52,73,6,9,12,24,68,89 is 41
I have the need to calculate a distinct count over a period of 3 years. I use the MDX
SUM([Year].[Year].CurrentMember.Lag(2):[Year].[Year].CurrentMember,[Measures].[CNT])
Which works fine for all my other calculations except this, where I need a distinct count. CNT is a calculated measure. The browser would look like this:
Category Year1
Year2 ..... MDX what I have now
MDX what I need
A A1
1 1 2 1
A2 1
0 1 1
A3 0
1 1 1
How can I achieve this?
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] .....
I am having trouble creating a sp for the following situation:
The database contains a record of the mileage of trucks in the fleet. At the end of every month, a snapshot is collected of the odometer. The data looks like this:
Truck Period Reading
1 1/31/03 55102
2 1/31/03 22852
1 2/28/03 62148
2 2/28/03 32108
1 3/31/03 69806
2 3/31/03 52763
How can I calculate the actually miles traveled during the month in a query?
TIA,
Rob
Hi All,
I have the following table "Project"
-------------------------------------------------------------------------------------------
ID Name Start Date End Date
--------------------------------------------------------------------------------------------
001 Project 1 2-2-2003 2-3-2007
002 Project 2 1-24-2003 2-6-2007
003 Project 3 4-10-2005 2-10-2008
004 Project 4 5-20-2006 6-6-2008
...
015 Project 15 2-20-2006 3-3-2009
----------------------------------------------------------------------------------------------
What I want is the the following output.
Output 1:
------------------------------------------------------------------------------------------------------------------------------------------------
Year Projects No. Of Projects Starting Months
------------------------------------------------------------------------------------------------------------------------------------------------
2003 Project 1 , Project 2 2 February, January
2005 Project 3 1 April
2006 Project 4, Project 15 2 May , Feb
------------------------------------------------------------------------------------------------------------------------------------------------
(the order displayed in the months shoudl be in accordance with the order of the projecs in the projects column...)
and also the following
Output 2: (this is optional view...)
-----------------------------------------------------------------------------------------
Year Start Date Project Name
-----------------------------------------------------------------------------------------
2003
February 2 Project 1
January 24 Project 2
2005
April 4 Project 3
2006
May 5 Project 4
February 20 Project 15
-------------------------------------------------------------------------------------------
I am very much in need of Output 1. Could someone help me,
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?
Hi,
i have a matrix, and in that matrix i need to have one column which calculates the percentage change between a value on the current row and the same value on the previous row.
Is this possible? The RunningValue() function isn't of help as it can't help me calculate the change between two rows, and Previous() doesn't work in a matrix (why???!!!!!). Also calculating this as part of the query isn't possible as there is a single row group on the matrix, and the query is MDX.*
Thanks,
sluggy
*for those who are curious, the matrix is showing data an a per week basis, the row group is snapshot date, i am trying to measure the change in sales at each snapshot.
I have table that contains below data
CreatedDate ID Message
2015-05-29 7:00:00 AOOze abc
2015-05-29 7:05:00 AOOze start
2015-05-29 7:10:00 AOOze pqy
2015-05-29 7:15:00 AOOze lab
2015-05-29 7:20:00 AOOze lmn
2015-05-29 7:30:00 AOOze start
2015-05-29 7:35:00 AOOze stop
2015-05-29 7:40:00 AOOze pqy
2015-05-29 7:45:00 AOOze stop
2015-05-29 7:50:00 AOOze lmn
I need to Find Maximum interval time for between message like Start and Stop as per order of createdDate.
For example:
OccuranceCount MinDate MaxDate DurationInSeconds
1 2015-05-29 7:05:00 2015-05-29 7:35:00 30
2 2015-05-29 7:30:00 2015-05-29 7:45:00 15
am making a CV program and i need a way to count the experience the user has:
i have his begin date and end Date as datetime in an sql server.
i can do it programicly but i prefer to do it at the sql side
the question:
how can i get how much exp he has aka :
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate ))
but for all the datarow
(he has more than one BeginDate and EndDate (for each job he has one))
P.S i want to be able to use it in a where clause :
select * from jobap
where -- or HAVING
JobAp.ind = JobApExp.JobAp AND
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) > CONVERT(int,@Exp)
thanks in advance
i am using this expression to get the time difference between two times.
{%Z.elapsed.time(@AK.VD.depart.date,@AK.VD.depart.time,@AK.VD.depart.date,@DV.VD.arrival.time,"hh.hh")*60} as [LOS (min)]
When Arrival time and depart time both are on same day above expression working to get the diference .
But if arrival date 2013-09-20 00:00:00.000 and arrival time 0800 and depart date 2013-09-21 00:00:00.000 and depart time 0050 when i calculate the time difference(using above expression) between these two i am getting -429.60 which is wrong. i have to get around 990.
i have a table
a1
-----
id t1
How can I calculate the difference between getdate() and t1 how many days.
Suppose I have these two days fields
ddold 1/1/2005 12:00:00 AM
ddnew 2/1/2007 12:00:00 AM
How can i get the DateDifference of these two dates in days.
I have a table of sample data
Samples(sample_no, sample_date..)
I have no idea how to do the following in sql server or if its even possible:
1. Calculate the difference in days between all samples.
2. Select the median result
Any trick to get this done would be really helpful
thanks,
DB
I have a table of sample data
Samples(sample_no, sample_date..)
I have no idea how to do the following in sql server or if its even possible:
1. Calculate the difference in days between all samples.
2. Select the median result
Any trick to get this done would be really helpful
thanks,
DB
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 RelatedI would like to calculate difference between end_date and current date in Months.And also how we can calculate the sum of difference in months between start_date and end_date for each ID?
CREATE TABLE datedifference (
id INT
,start_date INT
,end_date INT
)
INSERT INTO datedifference VALUES (10,20091202,20100629)
INSERT INTO datedifference VALUES (20,20071202,20090330)
INSERT INTO datedifference VALUES (30,20051202,20101031)
I have a table like this.
CREATE TABLE Table1
([S_ID] varchar(7), [S_ACTV_CODE] varchar(4), [S_USER] varchar(5), [S_DATETIME] varchar(19), [S_ACT_IND] int)
;
INSERT INTO Table1
([S_ID], [S_ACTV_CODE], [S_USER], [S_DATETIME], [S_ACT_IND])
VALUES
('AAA-111', NULL, 'USER1', '2015-06-15 00:21:06', 0),
('AAA-111', '2', 'USER1', '2015-06-15 00:21:07', 0),
[code]....
Basically I want to calculate the time spent by S_Users on a particular S_ACTV_CODE:
- S_ACTV_CODE_PREV means the previous active records.
- S_START_TIME is the time of S_DATETIME when a S_ACTV_CODE starts
- S_END_TIME is the time before a S_ACTV_CODE changes to another S_ACTV_CODE
- For the first record, S_ACTV_CODE is null, so there is no S_ACTV_CODE_PREV, so S_ACTV_CODE_PREV is NULL
- For the second record S_ACTV_CODE has some value, but S_ACTV_CODE_PREV is NULL for first record. So second record S_ACTV_CODE_PREV is also NULL
- For the last record (means S_ACTV_IND = 1), the user is currently working on it and S_ACTV_CODE is not changed. So S_END_TIME is a open time and we want to keep it as NULL
So the result should be as below:
S_ID S_ACTV_CODE_PREV S_ACTV_CODE_CURR S_USER S_START_TIME
S_END_TIME TIME_SPENT (in Sec)
AAA-111 NULL NULL USER1 2015-06-15 00:21:06
2015-06-15 00:21:07 1
AAA-111 NULL 2 USER1 2015-06-15 00:21:07
2015-06-17 03:20:33 183566
AAA-111 2 4 USER2 2015-06-17 03:20:33
[code]....