I've always user over with partitioning however recently I am looking at some other code and they have calculating percentages using over. I'm at a lost on why you would do this.
Example would be
CAST(TABLE1.UNIT AS DECIMAL (6,2)) / SUM(TABLE2.UNIT) OVER () AS UNIT_%
I have a query which provides the total number of each of 2 invoice flags per month, starting 1st Feb this year. I also need a percentage calculated for each of the 2 flags by month. However, it is showing the totals correctly for each of the flags by month, but the percentage is using the total of both flags for all months, and so it is wrong if I only want to look at one month. The query is as follows along with a sample output including % which are incorrect.
How can I correct this so that it shows the right % for each of the flags for each month?
Select sk.Period Period, sk.[Invoice Flag], count(*) * 100.0 / Sum(count(*)) Over () Percentage, count(*) Total1 From (Select wh.worknumber [Work Order],
[code].....
Output at present:
Period Invoice Flag Percentage Total1 --------------------------------- ------------------------------- --------------------------------------- ----------- 2015-02 Invoiced after 7 days 5.704697986577 136 2015-03 Invoiced after 7 days 2.097315436241 50 2015-02 Invoiced with Customer Approval 59.563758389261 1420 2015-03 Invoiced with Customer Approval 32.634228187919 778
I have been trying to calculate age and the results either round the age up one year or down one year. I have tried CASE, DATEDIFF, FLOOR functions but nothing works.
Is there a way to generate an 'Others' field/item for a Field in the dataset and add it at the bottom of the group which is filtered for TOP N, for that field? For example, I have a table report which has a group on a field called Commodities, and I'm adding a TOP N filter to the group for getting the Top 30 commodities, and I need an Others item appended at the end of the group that gives me the total of all the commodities other than the top 30, and this group in turn has a subgroup that has top 10 Shippers for each commodity and also an Others item for the Shippers other than the top 10. My challenge is to create a row for the Others item of Commodities and calculate the Top N Shipper for this row which has the 'Others' Shipper item. I hope this is not too confusing to understand. Please feel free to ask me any questions to clarify.
I want to make the total the same amount as the 2008 total per period. (everything in yellow is the group 1 (outer group and the red is the inner group. When i tried to get the value for the year 2008 and put it in the total field, i put Last(Fields!PeriodTotal.value), but it doesnt work, because sometimes it gives me the 2007 total, and sometimes it gives me the 2008 total. I dont know why. Any suggestions on how i can get the 2008 value everytime.
I have a sum , which is filtered within its table group, and then i have a fields in the footer. How would i aggregate these too together and use the result in another row within the footer.
[rsInvalidAggregateScope] The Value expression for the textbox €˜textbox129€™ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
electric: id_electric indentity primary key, id_room varchar(4), number_first int, number_last int, Sum_Number int, money_electric money, status bit
Water: id_Water indentity primary key, id_room varchar(4), number_first int, number_last int, Sum_Number int, money_water money, status bit
Now what I want to do are statistics on how much money I got in a year or month. Here is my code to calculate incomes of year.
Select Year(day_register) as 'Year' , Sum(money_per_month * month(day_end-day_register)) + sum(b.money_electric+c.money_water) as 'Incomes' From lodgings_Contract a , electric b , Water c Where a.id_room = b.id_room And a.id_room = c.id_room And b.status = 1 And c.status = 1 Group by Year(day_register)
Code Block WITH YesterdayCTE AS ( SELECT type = 'Members Joined Yesterday' , Borrowers = (select count(*) from LoanApplication INNER JOIN Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (LoanApplication.SubmittedOn >= GETDATE()-1) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name ) , Depositors = (select count(*) from CDOrder INNER JOIN Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (CDOrder.SubmittedOn >= GETDATE()-1) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name )
),
MonthlyCTE AS
( SELECT type = 'Members Joined Last Month' , Borrowers = (select count(*) from LoanApplication INNER JOIN Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (LoanApplication.SubmittedOn >= GETDATE()-30) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name )
, Depositors = (select count(*) from CDOrder INNER JOIN Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (CDOrder.SubmittedOn >= GETDATE()-30) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name )
),
YearlyCTE AS
( SELECT type = 'Members Joined Last year' , Borrowers = (select count(*) from LoanApplication INNER JOIN Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (LoanApplication.SubmittedOn >= GETDATE()-360) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name ) , Depositors = (select count(*) from CDOrder INNER JOIN Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (CDOrder.SubmittedOn >= GETDATE()-360) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name )
),
combinedCTE AS
( SELECT * FROM YesterdayCTE UNION ALL SELECT * FROM MonthlyCTE UNION ALL SELECT * FROM YearlyCTE
)
SELECT * , Members = Borrowers + Depositors FROM combinedCTE
But I get the following error message.
An error occurred while reading data from the query result set. Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (Microsoft Report Designer) =================================== Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (.Net SqlClient Data Provider)
I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table
GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end and in the second Temp Table, I have select ,GrpSize=sum(grpsize) ,NoofGrp=count(distinct grpsize) From Temp1
The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two.
I have an exisitng report that lists unit name, provider, runday, shift, patient. The report groups by unit name and there is a page break after each unit name. So in the current environment the report prints one page per unit that contains all of the providers, rundays, shifts, and patients for that unit name its grouping on. So the report would like like this when it prints:
Unit A Provider 1 Runday 1 Shift 1
patient 1 patient 2 Provider 1 Runday 1 Shift 2
patient 1 patient 2 Provider 1 Runday 2 Shift 1
patient 1 patient 2 Provider 1 Runday 2 Shift 2
patient 1 patient 2 Provider 2 Runday 1 Shift 1
patient 1 patient 2 Provider 2 Runday 1 Shift 2
patient 1 patient 2 Provider 2 Runday 2 Shift 1
patient 1 patient 2 Provider 2 Runday 2 Shift 2
patient 1 patient 2 PAGE BREAK Unit B............(repeat data from page 1) PAGE BREAK Unit C............(repeat data from page 1
The end user would like the ability to keep the report as is but would like to also be able to print the report as one page per each unit name, provider, runday and shift. so it would look like this
Unit A Provider 1 Runday 1 Shift 1
patient 1 patient 2 PAGE BREAK Unit A Provider 1 Runday 1 Shift 2
patient 1 patient 2 PAGE BREAK Unit A Provider 1 Runday 2 Shift 1
patient 1 patient 2
So I created a boolean parameter with a prompt of Page break by Unit, Provider, Runday & Shift? My thought is if the users sets this to False, the report will group on just Unit Name (the first example). If the user sets this to True, the report will group on Unit Name, Provider, Runday & Shift.
I set the grouping expression for this data table as: =iif(Parameters!Grouping.Value = "False", Fields!unit_Name.Value,(Fields!unit_Name.Value,Fields!Lname.Value,Fields!Rundays.ValueFields!Shift.Value))
Within the expression editor window it displays a syntax error and the report will not run.
I am working to move an application from MySQL to SQL Server. The person who developed the MySQL application has little database experience, and took some shortcuts that the lax nature of MySQL allows. One query with which I am struggling looks something like this:
SELECT StartTime + Offset, min(Sensor), max(Sensor) FROM SensorData WHERE SensorID = @SensorID AND StartTime + Offset > @BeginTime AND StartTime + Offset < @EndTime GROUP BY (StartTime + Offset) / 100 ORDER BY StartTime + Offset
What we are trying to accomplish is to return minimum and maximum sensor values over a number of periods between the BeginTime and EndTime. When I run this query in MySQL on a sample dataset, it returns a small number of rows, with each one being the min/max values for a portion of the overall period.
Under MS SQL Server 2008, SP3, I get the two following error messages: Column 'SensorData.StartTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Column 'SensorData.Offset' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
i have the percenatge that 10%...now i want to check that whether this 10 % is in between the frompercentage and topercentage..if it is there i want to return the the scale value..how can i do this in sql.
i know comparing when it is integer using where and and operator..but i dont know how use if it has the percentages.
most my fields that I am interested in getting percentage values for have to be counted first.... I am having problems converting the counted values to percentages. I don't think I am doing it right...
I would greatly appreciate an example of a very simple matrix table with a counted value that displays percentages. I have found examples online but are part of a much more complex table and I can't seem to get thier formating to work on mine.
Hello,I'm trying to something that just works in Oracle, but does not in SqlServer.How can I get the percentage of two counts to be returned on each rowof the query?select count(sid), /* all not the not null */count(*),(count(sid) /count(*) ) as percent_not_null,4 as four,(3/4) as three_over_fourfrom dbo.sysusers7082040Incredibly, it changes even 3/4 into a zero!For efficiency, I want the percentage returned in the query.And to not use variables and coding. Efficiency,both of the server, and of my time.Note: I am using dbo.sysusers as an EXAMPLE only. My realquery will be on user defined application tables.What is the solution please?
I have two values and I want to express a third derived value as apercentage of the other two values. I thought it would be a simpledivision of the first two numbers and then a multiplication by 100 togive me a percentage, but all I get is 0.Here is my select statement,SELECT dbo.Eligble.GRADETotal,dbo.nil1234_Faculties_Totals.FACTotal,dbo.nil1234_Faculties_Totals.FACTotal /dbo.Eligble.GRADETotal * 100 AS [PERCENT]FROM dbo.Eligble CROSS JOINdbo.nil1234_Faculties_TotalsCan anyone point out where I'm going wrong here?Thanks in advance
Trying to create a dataset for a report. I need to bring back percentage in the result set. The fields that I am using to get the percentage have valid data but the result field is 0.00%. Is there reason I cant bring back a percentage field?
Code: SELECT JobNum, Mailed, LT_7, BT_6_10, GT_10, Format([LT_7]/[Mailed], 'p') AS PctScaned1, Format([BT_6_10]/[Mailed], 'p') AS PctScaned2, Format([GT_10]/[Mailed], 'p') AS PctScaned3,
SELECT DISTINCT CASE WHEN SM.SERVICE_TYPE_N = 1 THEN 'LABORATORY' WHEN SM.SERVICE_TYPE_N = 2 THEN 'PODIATRY' WHEN SM.SERVICE_TYPE_N = 3 THEN 'ADMINISTRATION' WHEN SM.SERVICE_TYPE_N = 4 THEN 'DIET' WHEN SM.SERVICE_TYPE_N = 5 THEN 'DENTAL'
[Code] ....
In the above query i need to calculate 100%,30% and percentage other than 100 and 30 and show them in separate columns how to do that?
1)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [100_PERCENT] WHERE BM.BILL_AMOUNT_M=BM.CONCESSION_AMOUNT_M
2)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [30_PERCENT] WHERE AND BM.CONCESSION_AMOUNT_M=BM.BILL_AMOUNT_M * 0.30
3) ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [OTHER_CONCESSION_PERCENT] WHERE BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.001 and BM.BILL_AMOUNT_M*0.299 OR BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.301 and BM.BILL_AMOUNT_M*0.999
I have two tables that look like this (below). One tells me the name of my product, the Amazon Category it is in & the amount that I want to sell it for. The other tells me the Category & the fee for that category. So far so good. Though it gets tricky in the sense that some categories have two tiers. So in Electronics, the fee for $0.00 - $100.00 is 15%. But from $100 and up it is 8%.
Since it has two columns & both of the new columns pertain to the fee of my product, I can't figure out how to use both at once. For my $599.99 example it would be ($100 * 0.15) + ($499.99 * 0.08) = $55.00. Would I pivot the data? If not, how would I group it to be considered together?
I've created a report that has cells in it that I want to be formatted as percentages. I set the format code in the cell properties to "P1" and everything appears to work fine until I export to Excel. Some cells show up with only one digit after the decimal and some show up with two digits after the decimal. I looked at the formatting in Excel (right click, Format Cells...) and it is set to "[$-1010409]#,##0.0#%". I don't have the first clue what that is "supposed" to do but what it does is this:
If I enter 100.00 in the cell it shows up as 100.0%. Expected If I enter 100.10 in the cell it shows up as 100.1%. Expected If I enter 100.15 in the cell it is displayed as 100.15%. Not expected
I have formatted the report to only show one digit after the decimal, but RS exports to Excel with some funky formatting that sometimes shows one digit and sometimes two digits after the decimal. Why wouldn't it simply format the cell as a percent? How can I get it to only show a single digit after the decimal?
This is Excel 2007 but Excel 2003 has the same behavior.
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls as connectionString works
However - I'm trying to deploy the package - and trying to use expression: @[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls" which causes the same error to occur
(Same error with other Excel source also: Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1 )
I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".
I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.
Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.
This is the lookup expression that I added for the 2nd column.
I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' Â values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) Â must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName          Clm ABC-X
X
Page2:
GroupName          Clm ABC-Y
Y
Page3:
GroupName          Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
