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 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 am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
I got a situation that need to write a expression for doing if the value is negtive then display () around this value, and this expression should apply to 30 fields in my report. so i just wonder is that any way that i can create this expression as global variable , then i can use this expression in each field, instead of i write IIF function in every field expression area.
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 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.
Lets say I have a list of customers, order date, and an item they ordered. I want to grab the MIN order date by customer, and the associated product. If I do:
Code: SELECT customer,min(order date),item FROM mytable GROUP BY customer
I get an error because item is not aggregated. But I don't want to group on it and i don't want to select a min/max of it. i just want it to carry over the item that is associated with that min order date.
Hi, please do give me your expert advise and opinon on this matter:
I have a table name PerformaceRecords with a few columns, one of which is performance banding. i.e. PerformanceBanding ------------ Outstanding Good Average Good Poor
When I use a group by clause, i.e. Select PerformanceBanding, Count(PerformanceBanding) as ResultCount from PerformanceRecords group by PerformanceBanding
I got the result as
PerformanceBanding ResultCount --------------------------------- Good 2 Poor 1 Average 1 Outstanding 1
What I want to get is the PerformanceBanding as columns and the Result as rows
i.e.
Good Poor Average Outstanding ---------------------------------- 2 1 1 1
how do I go about modifying my SQL select statement to achieve this result?
I'd like to have all distinct recordIDs with relevant text associated with them. Each record has 3 text boxes in different languages. Each text in different language is defined by an AttributeDefinitionID. This is my query:
Select a.entryID, g.GroupName, c.CategoryName as ExperienceType, e.AttributeValue as EnglishWording, e1.AttributeValue as GermanWording, e2.AttributeValue as RussianWording, From Entry as a inner join entrycategory as b on b.entryid = a.entryid
[Code] ....
but in the results I get additional rows for each record even if the record doesnt have all three text boxes populated and there is only EnglishText for example.
EntryID GrouPName EnglishWording GermanWording RussianWording 1586 Red abc NULL NULL 1586 Red NULL NULL NULL 3566 Yellow NULL Hallo Welt NULL 3566 Yellow NULL NULL NULL 3566 Yellow Hello world NULL NULL 3566 Yellow Hello world Hallo Welt NULL
1586 should only return the first line with English wording. 3566 should return the last line that shows both English and German wording populated
Hi All,In Oracle, I can easily make this query :UPDATE t1 SET (f1,f2)=(SELECT AVG(f3),SUM(f4)FROM t2WHERE t2.f5=t1.f6)WHERE f5='Something'I cannot seem to be able to do the same thing with MS-SQL. There areonly 2 ways I've figured out, and I fear performance cost in both cases,which are these :1)UPDATE t1 SET f1=(SELECT AVG(f3)FROM t2WHERE t2.f5=t1.f6)WHERE f5='Something'and then the same statement but with f2, and2)UPDATE t1 SET f1=(SELECT AVG(f3)FROM t2WHERE t2.f5=t1.f6),f2=(SELECT SUM(f4)FROM t2WHERE t2.f5=t1.f6)WHERE f5='Something'Is there a way with MS-SQL to do the Oracle equivalent in this case ?Thanks,Michel
I am looking to sum the dollar amounts spent by customer for their last five visits (actually looking for avg. spend in the last five trips).
So we are dealing with three fields: CustID, Date, Cost
I've had to do it the HARD way -- Crystal Report with running total fields numbering the five most recent trips (essentially rowID by group) and summing the $ figure; export to Access the entire report (1,000,000 records), and delete every record where the running rowID <> 5
There has to be a straightforward way to do this.
Any Top N query I've seen doesn't seem to be able to return a field other than the one sorting on. This is most annoying.
INNER JOIN F1ADR_ADDRESS ON (GLF_CHART_ACCT.CHART_NAME = F1ADR_ADDRESS.ENTITY_KEY1) AND (GLF_CHART_ACCT.ACCNBRI = F1ADR_ADDRESS.ENTITY_KEY2)
GROUP BY GLF_CHART_ACCT.DESCR1, F1ADR_ADDRESS.ADDR1, F1ADR_ADDRESS.ADDR2, F1ADR_ADDRESS.ADDR3, F1ADR_ADDRESS.ADDR_CITY, F1ADR_ADDRESS.ADDR_STATE, F1ADR_ADDRESS.POST_CODE, F1ADR_ADDRESS.PHONE_NBR, F1ADR_ADDRESS.FAX_NBR, F1ADR_ADDRESS.EMAIL_ADDR_NAME, F1ADR_ADDRESS.CONTACT_NAME, GLF_CHART_ACCT.ACCNBRI, F1ADR_ADDRESS.CONTACT_TITLE, GLF_CHART_ACCT.CHART_NAME, F1ADR_ADDRESS.ENTITY_UNIQUE_NBR, GLF_CHART_ACCT.SELN_TYPE1_CODE
HAVING CHART_NAME='ARCHART' AND GLF_CHART_ACCT.DESCR1 <> '' AND GLF_CHART_ACCT.SELN_TYPE1_CODE = 'Trade' AND GLF_CHART_ACCT.DESCR1 LIKE '%" + Search + "%' ORDER BY GLF_CHART_ACCT.DESCR1;
I get errors if not all the fields are included in the group by clause.
what i dont get is why i have to create seperate groups for this query...or am i reading it wrong??
I've just written a query that successfully brings back the data from one table based on the information from another. Basically we have been given a table of information and need to update certain fields in our user_group table with the new info.
Here is the SELECT statement SELECT user_group.id, user_group.name, user_group.description, Consultants.description AS Expr10, user_group.btype, user_group.rootmenu, user_group.intra_user, user_group.primary_g_id, user_group.fname, user_group.lname, user_group.ntlogon, user_group.lang_id, user_group.[external], user_group.title, user_group.work_tel, user_group.work_fax, user_group.work_ext, user_group.mobile, user_group.sex, user_group.add2, user_group.add3, user_group.town, user_group.county, user_group.pcode, user_group.private_flag,
[code]....
We want to update the 'description' on the user_group table with the 'description' from the 'consultants' table. To test this, we only want to write the UPDATE so that it changes the description where the name is 'Adam Froth. The UPDATE statement that we've written is
UPDATE user_group SET user_group.description = Consultants.description FROM user_group INNER JOIN Consultants ON user_group.description = consultants.description WHERE name like 'Adam Froth%'
but it keeps erroring and saying that it could 'Not be bound'.
Guys ... got a puzzle kinda problem am stuck up with so asking for your help (after all .. you are the gurus)... What i need to do is append Src. to any column name that comes in the expression. Here we go ... and the last query is part of what I was coming up with ... not exactly working :) Create Table ColumnNames(ColumnName varchar(256))goInsert into ColumnNamesSelect 'name'UNIONSelect 'dbid'UNIONSelect 'sid'UNIONSelect 'mode'UNIONSelect 'status'UNIONSelect 'status2'UNIONSelect 'crdate'UNIONSelect 'reserved'UNIONSelect 'category'UNIONSelect 'cmptlevel'UNIONSelect 'filename'UNIONSelect 'version'goDeclare @Expression varchar(256)Select @Expression = 'dbid = 1 AND cmptlevel = 100' Select replace(@Expression,ColumnName,'Src.'+ColumnName) from ColumnNames Where charindex('Src.',replace(@Expression,ColumnName,'S rc.'+ColumnName)) <> 0
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.
How to identify different fields with in a group of records?
Example: create table #test (ID int, Text varchar(10)) insert into #test select 1, 'ab' union all select 1, 'ab'
[Code] ...
I want to show additional field as Matched as ID 1 has same Text field on both the records, and for the ID 2 I want to show Unmatched as the Text fields are different but with the same ID.
Hi, I'd like to perform a number of different operations on my Common Table expression but I seem to be limited to only one operation. For example I cannot both delete duplicate rows and then perform a select statement. I can only execute one of the statements referencing the common table expression.
What is wrong with my syntax?
;With OrderedTable
AS
(
select Row_number() OVER (partition BY SSNumber order by Department_Id desc ) AS ROWID,* from Employee
)
delete from OrderedTable where RowId != 1
SELECT COUNT(*),SSNumber FROM OrderedTable group by Department_Id order by count(*) desc
I'm trying to have two common table expression in my stored procedure, but I'm receiving errors when executing it, I found that they can't exist side by side,once I removed 1 of them, the stored procedure executed successfully.
The following are the errors
Code:
Msg 156, Level 15, State 1, Procedure GetProductsByCategory, Line 27 Incorrect syntax near the keyword 'With'. Msg 319, Level 15, State 1, Procedure GetProductsByCategory, Line 27 Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Msg 319, Level 15, State 1, Procedure GetProductsByCategory, Line 33 Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
I've done some simple sql's for searching a field using Like,But this one is different. I am adding a param named @searchText I would like to bring back all records in all the fields listedbelow that has that string in the field... WHERE a.manufacturer = b.manufacturerIDAND a.location = c.locationIDAND a.Status = d.statusIDAND a.EquipmentType = e.IDAND a.calLab = f.ID AND a.testTechnology = g.id AND (c.locationID = @location OR @location = 0) So, each line/field above I want to search for the string and includein the dataset. Anyone can point me in the right direction? Thanks, Zath
Please point me in the direction of a tutorial that will do help me do the following:
The database has been previously created and the users first & last names are in the respective columns. There is also a column that should contain the full name of the user but as such it only contains <NULL>. Is there a way to pull the first name and last name from the db then write both names to column3?
I have a table that tracks some dates, and I am looking for an SQL statemet that will check all of the fields to see if a prespecified data range is true. For exampe you enter a start and end date and then then the SQL statement would check about 12 different fields to see if any of the dates are within that range. I am trying to use an or statement, but to no avail. So if you have any help it would be greatly appreciated.
Hi all,I'm running into a road block, and I know I've done this before. I'mgetting fields from two tables, and I need to do a count of similaritems with it showing some extra info.Here's my fields:Log.LogId - IntLog.LogDispatcherID - IntOfficer.OfficerID - IntOfficer.OfficerFirstName - VarcharOfficer.OfficerLastName - VarcharI can get the info I need without a count with this:select a.LogID,a.LogDispatcherID,b.OfficerFirstname + ' ' + b.OfficerLastname as OfficerNamefrom[Log] a, Officer bwhere a.LogAssigned1 = b.OfficerIDBut when I try to add a count and group-by it errors out:select Count(a.LogID) as LogCount,a.LogDispatcherID,b.OfficerFirstname + ' ' + b.OfficerLastname as OfficerNamefrom[Log] a, Officer bwhere a.LogAssigned1 = b.OfficerIDGroup By a.LogIDI've done this before, but this isn't working. It's giving the error"it is not contained in either an aggregate function or the GROUP BYclause" for each field other then LogID.How can I do this? I want output similar to this:LogCountLogDispatchIDOfficerName334Tom Jones422John Smith.... EtcThanks for any suggestions or ideas...Sam Alex