SQL Server 2012 :: Select Top X% From Group Based On Total?
Mar 21, 2014
What I need to do it select the top 80 percent of records per group based on the group total. To be clear I am not trying to just grab the top x percent of rows.
Table 1:
DealerID RepairID
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
2 11
2 12
2 13
Table 2:
RepairID Tire
1 225/65R17 102T
2 225/65R17 102T
3 225/65R17 102T
4 235/60R18 102V
5 235/60R18 102V
6 235/60R18 102V
7 235/60R18 102V
8 205/55R16 89H
9 205/70R15 89H
13 225/65R17 102T
Table 1 has the total number of repair orders per dealer. This can be obtained by simply grouping on DealerID and counting the number of RepairIDs.
Table 2 has information on some of the repair orders and it is needed to select the top 80% of tire sizes. This table will be joined to Table 1 and grouped by DealerID and Tire.
Desired Output:
DealerIDTire RepairsOfThisTireRepairCount PercentOfTotalRepairOrders
1 235/60R18 102V4 10 40
1 225/65R17 102T3 10 30
1 205/55R16 89H1 10 10
2 225/65R17 102T1 3 33
The equation I am given to calculate the top tires per dealer is as follows: Total # of tires for the size / Total # of repair orders per dealer.
Here is what I have so far though I think I might have to rewrite all of it.
SELECT
DealerID ,
COUNT(RepairID)
INTO #TotalDealerRepairOrders
FROM
dbo.Table1
GROUP BY
DealerID
SELECT
[code].....
View 9 Replies
ADVERTISEMENT
Jun 7, 2015
I am creating a query that shows the consumption of stock against Manf Orders (M/O) and struggling on the last hurdle. I am having difficulties calculating a running total based on an Opening Balance. The first line returns the correct results but the following lines do not. I have tried other variants of the "Over Partition" but still no joy?
SELECT CASE WHEN ROWNUMBER > 1 THEN ''
ELSE A.Component
END AS Component ,
CASE WHEN ROWNUMBER > 1 THEN ''
ELSE A.SKU
[Code] ....
Please see attachment to view output.
View 7 Replies
View Related
Sep 25, 2015
In report builder 3.0 I have row groups. I want a total at the end of each row but I want the total to be broken down by 3 columns based on 3 possible values of a field in the dataset. The report expands as the date range entered is increased. I want the total of clinic id + service id + program id + protocol id + appointment date but I want the total column to be broken down by appts that have shown or not shown or canceled.
See screenshots below for seeing how I have it configured. Is this possible? I have tried every combination of possibilities but I keep getting the row total in each of the 3 columns comprised of the total column.
and
The results look like:
The last Total column displays the entire row count NOT separated by the show, no show, and cancel status'. I have tried filters and different expressions but keep getting the same output. Is this even possible?
View 9 Replies
View Related
Jul 31, 2014
I have a data set that looks something like like this:
Row# Data
1 A
2 B
3 B
4 A
5 B
6 B
7 A
8 A
9 A
I need wanting to assign a group ID to the data based on consecutive values. Here's what I need my data to look like:
Row# Data GroupID
1 A 1
2 B 2
3 B 2
4 A 3
5 B 4
6 B 4
7 A 5
8 A 5
9 A 5
You'll notice that there are only two values in DATA but whenever there is a flip between them, the GroupID increments.
View 2 Replies
View Related
Jul 27, 2014
I want to create the following scenario. I have a table that stores employees working on projects and their project hours by week, but now I also need a running total per week for each of those projects. For example take a look below:
EmployeeID, Project, Sunday, Monday, Tuesday,....Saturday, ProjectHours, TotalProjectHoursPerWeek(this is the column I am trying to derive), FiscalWeek
101, ProjectABC, 5,5,5,...5, 20, 40,25
102, ProjectXYZ 4,4,4,....4, 20 ,40,25
103,ProjectQWE, 2,2,2,...2, 8, 32,26
104, ProjectPOP, 6,6,6,...6, 24, 32,26
What I have tried so far:
Correlated Subquery:
SELECT EmployeeID,Project, Sunday, Monday,....Saturday, ProjectHours, SELECT(SUM(ProjectHours) FROM dbo.TableABC ap GROUP BY FiscalWeek),
FROM
dbo.TableABC a
I got this to work one time before, but now I am getting the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
View 2 Replies
View Related
Aug 17, 2015
I am trying to select records based on a year.
DECLARE @Date
SET @Date = 2012
DECLARE @Year int
SET @Year = (SELECT DATEPART(yyyy,@Date))
SELECT @Year AS Year
--SELECT *
-- FROM [Orders].[dbo].[Orders] od
-- WHERE .Orderl_Date < @Date;
----WHERE DATEPART(yyyy,@Date)= @Year
----WHERE DATEPART(yyyy,od.Order_Date)= @ArchiveYear
View 5 Replies
View Related
Aug 17, 2015
DECLARE @Date
SET @Date = 2012
DECLARE @Year int
SET @Year = (SELECT DATEPART(yyyy,@Date))
SELECT @Year AS Year
--SELECT *
-- FROM [Orders].[dbo].[Orders] od
-- WHERE .Orderl_Date < @Date;
----WHERE DATEPART(yyyy,@Date)= @Year
----WHERE DATEPART(yyyy,od.Order_Date)= @ArchiveYear
View 1 Replies
View Related
Jul 31, 2015
Basically I'm trying to produce a report that shows qualified employees for each program. Each employee can possess many qualifications. There will be no programID parameter submitted by the user. I just want to produce the report which shows the programs and the qualified employees for each. I thought I had a query that was working but once I added a different ProgramID into the ProgramModules table things went south.
Here are my tables:
tblEmployees (table of employees)
- EmployeeID
- EmployeeName
tblQualifications (table of qualifications)
- Qualification_ID
- QualificationTitle
tblEmployeeQualification (table of all employees qualifications)
-EmpQualificationID
-EmployeeID (fk for tblEmployees)
-QualificationID (fk for tblQualifications)
tblPrograms (table of programs)
-ProgramID
-ProgramTitle
tblProgramModules (table of qualifications required by each program)
-ProgramModuleID
-ProgramID (fk for tblPrograms)
-QualificationID (fk for tblQualifications)
Here is the query I was working with that works when there are only records in the ProgramModules table that use the same ProgramID
SELECT
tblProgramModules.TrainingProgramID,
tblEmployees.EmployeeID,
tblEmployees.EmployeeName
FROM
tblEmployees
[Code] .....
View 6 Replies
View Related
Mar 23, 2015
I need a new field added 'Field1' which will add SEQUENCE number 1,2,.. based ON GROUP BY MasterID..AND another field TotalCount which will COUNT total masterID (here it will be 2)
CREATE TABLE #Temp1
( MasterID INT,
ClientName VARCHAR(10),
ProductName VARCHAR(50)
[code]...
View 0 Replies
View Related
Jul 11, 2014
I have Table Like this
t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail
[Code] ....
View 1 Replies
View Related
Jul 20, 2005
I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob
View 4 Replies
View Related
Oct 26, 2015
For some reason my Add Total is grey out, when i tried to add grand total using some expression.
I have two row & two column groups?
Is there any alternative or how can i enable add total? using expression..as you can see in my Attached Image
I'm using iff condition in my expression..
View 15 Replies
View Related
Oct 21, 2015
I am trying to move files to directories based on the file status. If the file contains the Flag='E' then I want to move it to an "Error" folder, otherwise it will go to a "Processed" folder.
Here is my current code:
DECLARE @cmd varchar(500)
SELECT
CASE
WHEN Processed_Flag = 'E'
THEN
SET @cmd='MOVE /Y C: empmyfiles*.dat C: empmyfilesError'
ELSE
SET @cmd='MOVE /Y C: empmyfiles*.dat C: empmyfilesProcessed'
END
FROM#TEMP
PRINT @Cmd
View 2 Replies
View Related
Oct 31, 2015
Despite the products are different, I want to get the sum of all products according to customer card.(client ref)
select (ORF.PRICE) *( ORF.AMOUNT - ORF.SHIPPEDAMOUNT) from [.dbo.LG_032_01_ORFLINE ORF JOIN [.dbo.LG_32_01_ITEMS ITM ON ORF.STOCKREF = ITM.LOGICALREF where ORF.CLIENTREF = XXX
View 1 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Nov 27, 2007
Hi!
I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311679
Provide Individual Page Numbering per Group and Total Pages per Group
Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.
I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.
Thanks!
Karen
View 1 Replies
View Related
Sep 10, 2015
Copy and paste the code to review sample:
Create table #temp(
ID varchar (20),
ID_BegAttach varchar (20),
ID_EndAttach varchar (20),
ID_AttachLvl varchar (20),
ID_NumofAttach varchar (20)
[Code] ...
I'd like to get the column ID_NumofAttach to be populated by the total number of ID_Attachlevel column by the same ID_BegAttach or ID_EndAttach and populate where ID_Attachlevel is 0.
View 9 Replies
View Related
Jan 10, 2014
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
View 3 Replies
View Related
Dec 18, 2013
I have a table with the Group name and Total Count by group . I need to add a 'Total' and summation of all counts at the end .
CREATE TABLE #TempTable
(GroupName VARCHAR(10),NumberOfCases INT
INSERT INTO #Temp Table (GroupName,NumberOfCases)
VALUES ('Grp A',10)
INSERT INTO #Temp Table (GroupName,NumberOfCases)
VALUES ('Grp B',20)
[code]....
View 5 Replies
View Related
Jul 25, 2014
Table A has day to day transactions, Table B has beginning balance. I'd like to get a running total balance day to day. Really what I want to do is use the previous days total to add the current days transaction to, but I don't know how to do it. The basic layout is below, but as you can see, I'm not getting the totals correct.
create table #current(acctNum int,
dates date,
transtype char(10),
amt INT
)
insert into #current(acctNum, dates, transtype, amt)
[Code] .....
View 4 Replies
View Related
Mar 18, 2015
I have a data table in below format and the need the desired output in 2nd table format
TABLE1
RANKBOOLEANREVENUE
1TRUE100
2FALSE150
3FALSE200
4FALSE250
5FALSE300
[code]....
Desired Output to be:
RANKBOOLEANREVENUERUNNINGTOTAL
1TRUE 100250
2FALSE150400
3FALSE200600
4FALSE250850
5FALSE3001150
[code]....
View 3 Replies
View Related
Sep 14, 2015
I have update schema and I upload image with my desire result.
CREATE TABLE #NONAMETABLE(
sinGroup NVARCHAR(10)
,column1 INT
,column2 int
[Code] .....
View 7 Replies
View Related
Dec 22, 2014
I need to calculate running total which resets when reached to 16, and also needs to calculate remaining amount as paid.
I have attached sample data, so we have two columns Earned, and Used. And we need to calculate Balance, and Paid columns.
View 4 Replies
View Related
Nov 25, 2014
SQL query to calculate the total on-peak and off-values for a month as well as the Max/highest on-peak/offPeak hourly value for that month.
On a daily basis i store the hourly values of the meter in a SQL table.
On-Peak
Summer: Apr-Oct hours(7-22) on weekdays (M-F)
Winter: Nov-Mar hours(8-23) on weekdays (M-F)
off-Peak
Summer: Apr-Oct hours(0-6,23,24); Weekends (Saturday & sunday) ; all public holidays during those months as to be considered as off peak
Winter: Nov-Mar hours(0-7,24);Weekends (Saturday & sunday); all public holidays during those months as to be considered as off peak
Here is the DB Table Structure:
Column Name & Data Types
HourId - Uniqueidentifier
CustomerName - nvarchar(50)
Readingdate - datetime
IntegratedHour - TinyInt
Load - decimal(18,4)
Generation - Decimal (18,4)
LastModified - Datetime
ModifiedBy - nvarchar(50)
View 9 Replies
View Related
Jan 21, 2014
How do I add column names as Total and SubTotal for NULL values.
SELECT DISTINCT
--[Group]
[Month]
,[Market]
,[Environment]
,[type]
, COUNT(*)
[code]....
View 9 Replies
View Related
Mar 21, 2015
I have the table below and like to create a view to show the no of days the property was vacant or void and rent loss per month. The below explanation will describe output required
For example we have a property (house/unit/apartment) and the tenant vacates on 06/09/2014. Lets say we fill the property back on 15/10/2014. From this we know the property was empty or void for 39 days. Now we need to calculate the rent loss. Based on the Market Rent of the property we can get this. Lets say the market rent for our property is $349/pw. So the rent loss for 39 days is 349/7*39 = $1944.43/-.
Now the tricky part and what im trying to achieve. Since the property was void or empty between 2 months, I want to know how many days the property was empty in the first month and the rent loss in that month and how many days the property was empty in the second month and the rent loss incurred in that month. Most of the properties are filled in the same month and only in few cases the property is empty between two months.
As shown below we are splitting the period 06/09/2014 - 15/10/2014 and then calculating the void days and rent loss per month
Period No of Void Days Rent Loss
06/09/2014 - 30/09/2014 24 349/7*24 = 1196.57
01/10/2014 - 15/10/2014 15 349/7*15 = 747.85
I have uploaded a screenshot of how the result on this link: [URL] ....
Declare @void Table
(
PropCode VARCHAR(10)
,VoidStartDate date
,LetDate date
,Market_Rent Money
[Code].....
View 4 Replies
View Related
May 28, 2007
Hi folks,
I have a Projects , each project have many tasks now i want to display tasks replated to each project:
for example:
Project1-------------------->task1
task2
task3
task4
Project2----------------------->task4
task5
task6
.............................................projectN.....................
how to write query for this
i have 2 tables:
Project .......>columns are projectid
Task------------->columns are projectid, taskid
|
View 4 Replies
View Related
Oct 22, 2007
we have following table sturcture
CREATE TABLE [PPE_STOCK] (
[REC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PPE_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PPE_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATE_RECEIVED] [datetime] NULL ,
[QUANTITY_PPE] [int] NULL ,
[LOGIN_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [PPE_ITEM_ISSUE] (
[PII_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EMP_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[QUANTITY_ISSUE] [int] NULL ,
[REMARKS] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOGIN_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [EMPLOYEE] (
[REC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EMP_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FIRST_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LAST_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NAME] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOGIN_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
data insert like this
insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY_PPE) values
('SH01','SAFETY HELMET',100)
insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY_PPE) values
('SH02','SAFETY GLOVES',100)
insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY_PPE) values
('SH03','SAFETY SHOES',100)
insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY_PPE) values
('SH04','SAFETY GLASSES',100)
.....
insert into ppe_item_issue (EMP_ID,PPE_ID,QUANTITY_ISSUE) values
('p0006367','sh01',2)
insert into ppe_item_issue (EMP_ID,PPE_ID,QUANTITY_ISSUE) values
('p0006365','sh01',1)
insert into ppe_item_issue (EMP_ID,PPE_ID,QUANTITY_ISSUE) values
('p0006366','sh02',5)
insert into ppe_item_issue (EMP_ID,PPE_ID,QUANTITY_ISSUE) values
('p0006367','sh01',5)
.....
insert into EMPLOYEE(EMP_ID,NAME,POSITION_code) values
('p006367','Martin','1')
insert into EMPLOYEE(EMP_ID,NAME,POSITION_code) values
('p006365','Peter','2')
insert into EMPLOYEE(EMP_ID,NAME,POSITION_code) values
('p006366','Jones','3')
insert into EMPLOYEE(EMP_ID,NAME,POSITION_code) values
('p006368','Peter','4')
.....
How can retrived the data by the query like this group by sum
and total.
PPE_STOCK : SAFETY HELMET total QUANTITITY_PPE 100
-------------------------------------------------------
EMP_ID, NAME, QUANTITY_ISSUE, SUM......%
-------------------------------------------------------
P0006367..Martin.....1...
p0006365..Peter......2
p0006366..Jones......1
-------------------------------------------------------
..............................4......4%
PPE_STOCK : SAFETY GLOVES total QUANTITITY_PPE 100
-------------------------------------------------------
EMP_ID, NAME, QUANTITY_ISSUE, SUM......%
-------------------------------------------------------
P0006367..Martin.....5...
p0006365..Peter......2
p0006366..Jones......5
-------------------------------------------------------
..............................12......12%
PPE_STOCK : SAFETY SHOES total QUANTITITY_PPE 100
-------------------------------------------------------
EMP_ID, NAME, QUANTITY_ISSUE, SUM......%
-------------------------------------------------------
P0006367..Martin.....4...
p0006365..Peter......4
p0006366..Jones......6
-------------------------------------------------------
..............................14......14%
...
...
Regards
Martin
View 5 Replies
View Related
May 21, 2008
Can somebody please help me how to show a total per 'group' in a table, rather than the total sum?
I have a report with a list that's grouped per project. This list has a table showing amounts paid to this project. As a footer to the table, I'd like a sum that shows the total amount paid per project.
Unfortunately if I run the report on more than one project, the list and table will still be grouped correctly, but the 'sum' will be the total sum rather than the sum of just that given project.
Eg:
Project 1
1-7-2007, 20,000
2-7-2007, 40,000
Total, 100,000
Project 2
2-7-2007, 40,000
Total, 100,000
I've tried all I can think of to get the 'group sum' rather than the total sum, and am sure I'm missing something really basic. I hope somebody here can help me.
Thanks in advance,
Maria
View 6 Replies
View Related
Apr 12, 2007
I hope someone can help me with this one. I can't seem to find a way to solve my problem. I am converting a report from Crystal to RS. In Crystal I am using global variables to keep track of group totals for a final summary. I need a similar result from RS. Data example
Group A
PK Field
Summary Data Field
1
250
2
300
Group A Total
550
Group B
3
100
4
50
Group B Total
150
Grand Total
700
The underlying query contains detail data and I am using a table with two group levels. All details are hidden.
To calculate the totals at the detail level I need to know what the total value for the entire group is. This leads me to my problem, it is not possible (as far as I can tell) to summarize a summary (I get an error). I have tried using the code window to store variables but the value returns a 0. I found a suggestion here http://msdn2.microsoft.com/en-us/library/bb395166.aspx under Distinct Sum, but I can't call the function using the Sum command given that the formula to calculate the value is already using the sum command. I hope this makes sense.
Thanks,
Simone
View 9 Replies
View Related
Sep 20, 2007
I have a report that looks like below. It's grouped by Product then by Year. I want to include within the Product grouping and item called "All products". If I swapped order of Grouping to Year then Product, I could simply add a SUM in the group header, but the users don't want it displayed that way round.
Any suggestions for "All products" ?
Thanks
Richard
Revenue
Product 1
2007
2008
2009
Product 2
2007
2008
2009
etc
I want to add
All products
2007
2008
2009
View 2 Replies
View Related
Apr 5, 2008
I have a table that I want to summarize the amount column and have it show the sub totals for the employee for each pay period. For example:
EMPLOYEE PerEndDate EarnCode Amt
A 8/1/2007 Hourly 100
A 8/1/2007 Peices 250
B 8/1/2007 Hourly 75
B 8/1/2007 Pieces 300
A 9/1/2007 Hourly 50
A 9/1/2007 Pieces 200
B 9/1/2007 Hourly 100
B 9/1/2007 Pieces 200
What I want to show is
Employee PerEndDate Amt
A 8/1/2007 350
A 9/1/2007 250
B 8/1/2007 375
B 9/1/2007 300
I'm using:
SELECT PayPeriodNo, EMPLOYEE AS PayEmpNo, MIN(PerEndDate) AS PerEndDate, SUM(AMT) AS Amt
FROM dbo._vPayroll
GROUP BY PayPeriodNo, EMPLOYEE
ORDER BY PayPeriodNo, PayEmpNo
And the quantity is showind grand totals for each instance
Employee PerEndDate Amt
A 8/1/2007 600
A 9/1/2007 600
B 8/1/2007 675
B 9/1/2007 675
Can anyone help me with this?
Thanks
Leo
View 2 Replies
View Related
May 18, 2006
How do I sort on a group total item?
View 6 Replies
View Related