i would like to know how to perform more than 1 group by in one SELECT. reason is i need to group by 1-to calculate totalx, and perform subgroup to calculate totalq. Say, this is the eg :-
From DB
storageid originqty cartonid
P00110C001
P00110C002
P0015C003
P00220
Result
Storageid TotalCtnInPalletTotalCartonPcsInCarton
P0013210
P001315
P0020020
*TotalCtnInPallet = Total of cartonID contain in 1 storageid
*TotalCarton = Total cartonID that has same OriginQty
*PcsInCarton = OriginQty
thanks.. ive tried this using nested select but the result very slow.. like my other topic- temp table or nested select..
so id like to know second opinion to achieve this group by calcualation.. thankss
partition with single file group or multiple file group which one best.
we have some report running from partition table, few reports don't have any partition Key and after creating 400 partition with 400 file group it is slow.what is best practices to crate 400 file group or single file group.
I am creating a statistics page for our site. Using a very basic select statement, my query currently returns: Select DateAdded, MTCreds, HICreds from tblStudentsAndCredits DateAdded-MTCreds-HICreds-----------------------------------------1/1/2008 - 2 - 01/1/2008 - 0 - 41/2/2008 - 3 - 01/2/2008 - 2 - 41/3/2008 - 2 - 01/3/2008 - 0 - 3 Instead, I would like it sum up MTCreds and HICreds for each day and group them into something more usable like this: DateAdded-MTCreds-HICreds-----------------------------------------1/1/2008 - 2 - 41/2/2008 - 5 - 41/3/2008 - 2 - 3 Thanks for any help - SQL is not my strong point.
creating a query to group identical rows into one and placing corresponding data in appropriate columns for a table named items, what I mean is that I have a table structured as below
I have two issues I'm trying to deal with in my code.
1. I'm trying to group by first and last name, which are in two different columns 2. I'm trying to take an average of pay per miles.
Neither of these is working well. Actually, neither is working at all.
This is the code!
SELECT OD.DriverID, (W.FirstName + W.LastName AS 'Driver'), DATEADD(dd,(DATEDIFF(dd,0,O.ReadyTimeFrom)),0) AS Date, DATENAME(dw,O.ReadyTimeFrom) AS DayOfTheWeek, Count(OD.OrderID) AS 'Total Orders', SUM(O.Distance) AS OrderMiles,
I have 4 archive tables and 1 active table that are created the same, but contain different data based on the date. I need to get results that have three columns: AuthorName, Month, Total. This is currently working, but through my research I can't find how to start going about dealing with the fact that each Author has some of his results from one month in one table and some in another table and how to add those together into one row. Example:
(What I'm Getting) AuthorName Month Total Test, Fred 3 43 Test, Fred 3 12 Test, Fred 2 56 Test, Fred 5 35
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.
Hi, I started working with SRS from the past one month. I am breaking my head to do this. My report has a single stored procedure. I made it to work all values of a parameter like exec sp_employee 'All' (Returns data for all employees) exec sp_employee '1111' (Returns data for an employee with id 1111) exec sp_employee '1111,2222' (Returns data for an employee with id 1111 as well as 2222)
This part works perfectly. I could successfully pass the values as mentioned above using a code block.
Now my challenge is how to layout the data on the report.
I need to show the report as follows:
Employeeid Employee Name Address etc....... Skills: Skill# Skill Name 1 11111 2 22222 3 33333 4 44444 Contacts: Contact Type Contact# Main 111-1111 Emergency 222-1111 Eductation: Name University Degree UofU Masters UofU
I tried using group by employeeid. But i can only show one group of data either Skills/Contacts/Education. I do not know if I use sub-report, how to pass the parameter from the main, when I pick multiple values for Employeeid.
Any idea how to do this? I appreciate your help, Thank you,
I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:
COL1 | COL2 | COL3 | COL4 1 | FD | DR. A | Y 2 | FD | DR. A | Y 3 | FD | DR. A | N 4 | FD | DR. A | Y 5 | FD | DR. A | Y 6 | PF | DR. A | Y 7 | FD | DR. B | Y 8 | PF | DR. B | N
Consider the script below:
SELECT COL2, COL3, COUNT(COL1) AS TOTALS FROM CASES GROUP BY COL2, COL3 ORDER BY COL3, COL2
The script above produces the following output:
COL2 | COL3 | TOTALS FD | DR. A | 5 PF | DR. A | 1 FD | DR. B | 1 PF | DR. B | 1
I need to add one more column to the script that counts records with 'Y' in COL4 for each COL1 category (FD, PF). The final dataset would look like this:
COL2 | COL3 | TOTALS | NEWCOL FD | DR. A | 5 | 4 PF | DR. A | 1 | 1 FD | DR. B | 1 | 1 PF | DR. B | 1 | 0
I am having a hard time trying to use COUNT() on multiple columns with the GROUP BY restrictions that exist.
If I have a domain user DOMAINuser1 who belongs to multiple window groups say DOMAINLookupConfigUsers and DOMAINAuditConfigUsers. In sqlserver, I would create two logins - DOMAINLookupConfigUsers and DOMAINAuditConfigUsers and matching users in the database. Then I grant LookupConfig role to the LookupConfigUsers user and AuditConfig role to the AuditConfigUsers user in the database. When DOMAINuser1 logs in, will it have both roles? I try to set this up but it does not seem to work. The domain user only picks up one of the role. Am I on the right track? If not, what is the proper way to grant multiple roles to a user when it belongs to multiple groups and each group has different privileges in the database.
I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem.
Consider that we have Product Category, Product and its monthly sales information retrieved as follows:
I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses.
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode order by CurrencyCode
But of course this codes gives an error, but how can I get my desired result??
I am setting up a new pair of SQL 2014 enterprise servers in HA using Availability Groups. One of the servers is located here in our local datacenter (10.0.1.x) and the other SQL server is in our remote datacenter(172.16.1.x). I was able to setup the Windows Failover Custer without much issue. I setup the AG but when I try to setup the listener. I get the following error. I have setup an IP for both networks on the listener. I have confirmed that there is not any DNS records created for AG listener name. But I still get this error.
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, ...
I've 2 tables QuestionAnswers and ConditionalQuestions and fetching data from them using CTE join and I'm seeing repetitive rows (not duplicate) like, If you have multiple answers for 1 question, the output is like
where london where paris where toronto
why us why japan why indonesia
I want to eliminate the repetitive question and group them as parent child items.
with cte as ( select cq.ConditionalQuestionID from ConditionalQuestions cq inner join QuestionAnswers qa on cq.QuestionID=qa.QuestionID where cq.QuestionID=5 and qa.IsConditional='Y') select distinct q.Question, a.Answer from QuestionAnswers qa inner join Answers a on a.AnswerID = qa.AnswerID inner join Questions q on q.QuestionID = qa.QuestionID inner join cte c on c.ConditionalQuestionID = qa.QuestionID;
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.
A little background on what I am trying to achieve first. We are moving to Azure virtual machines and we will have 8 disks on the SQL Server box. I am adding more files to the primary file group and each file will go on its own drive. I am then rebalancing data across these files by rebuilding all of the indexes on the tables which is working fine. No problems so far all is good.
I now have an additional problem. If there is a lob or blob column on the table, rebuilding the clustered index and all the non clustered indexes doesn't rebalance the blob or lob data across the disks such as it does with in row data.
I cannot find any articles on rebalancing lob or blob data because all the articles say to move to a new file group. I do not want a new file group, I just want to use the primary file group where the data already resides, and just redistribute it evenly in the same way that I can in row data which is working fine.
One solution I thought about was to BCP data out of the table, truncate the table and then BCP back into the table which I imagine would have the desired effect of distributing the data evenly over the files.
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1]( [TestTable1_pk] [int] IDENTITY(1,1) NOT NULL, [Source_ID] [int] NOT NULL, [Edition_fk] [int] NOT NULL, [Key1_fk] [int] NOT NULL, [Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
Request ID Parent ID Account Name Addresss 1452 1254789 Wendy's Atlanta Georgia 1453 1254789 Wendy's Norcross Georgia 1456 1254789 Waffle House Atlanta Georgia
Why shrinkfile empty file does not redistribute data evenly in the primary file group with multiple files:
Please run the script attached to see what the end result is.
This is what I set up last night on my test machine.
1) Create database [FGTest] size 200MB 2) Create table called TEST on primary 3) Insert 40MB of data into test 4) Create another file group called temp in primary size 200MB 5) Shrinkfile('FGTest',emptyfile) so that all data is transfered from FGTest into temp file group. 6) Add another 2 files called DATA2 and DATA3. Both are 200MB. 7) We now have 3 empty files that I want data distributed evenly on. FGTest, DATA2 & DATA3 8) Shrinkfile('temp',emptyfile) to move all the data from temp over the 3 file groups evenly
I would expect at this stage to have the following:
FGTest = 13MB, DATA2 = 13MB, DATA3 = 13MB
(40MB of data over 3 files should be about 13 MBish in each file)
What I actually end up with is this:
FGTest = 20MB DATA1 = 10MB DATA2 = 10MB
It looks as though SQL Server is allocating 50% of all data to the original file and then 50% evenly over the remaining files in PRIMARY.
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.
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.
I have an SSRS report with groups that when exported to excel contains drill-in's (plus marks on left side). The issue I have is that for all the groups in the drill-in, those cells become merged. I want to keep the group drill-in but have the cells UNMERGED. I have heard this can be done with the RDL XML but I don't know what to modify to accomplish this.
I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year Month Date
And the following row groups:
Region Product SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
select top 15 count(*) as cnt, state from table group by state order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
I have one domoain in the forest. The domain level is set to Windows 2000 native mode and forest level is set to mixed mode. My SQL server 2005 server joined to this domain. I added a brand new domain local group and add a normal user account to this domain local group. I login to the SQL server 2005 server and make a query "SELECT * FROM sys.login_token". I cannot see my domain local group in sys.login_token. However, if I add my account to a global group, I can see it there.
Then, I setup another forest. This time, I have domain level set to Windows 2003 mode and forest level is set to Windows 2003 native mode. I do the same testing. This time, I can see my domain local group in sys.login_token.
Why does SQL server 2005 has this limitation? Is it a bug?
HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned. DECLARE @StartDate varchar(12)DECLARE @EndDate varchar(12)DECLARE @Region varchar(20) SET @StartDate = '01/01/2002'SET @EndDate = '12/31/2008'SET @Region = 'Central' SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName FROM dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid WHERE (A.createdon >=@StartDate AND A.createdon <= @EndDate)AND (B.new_RegionName = @Region)AND (A.casetypecode = 2)
