I have a query that I'm filtering using Customer ID, CustomerID = '12345', even though I need the query to filter that data, I don't need to see that column in my results. I tried removing it from my Select Distinct group but I'm guessing it needs to be there or the filter won't work(like I said, very green). Is there something that I can add to hide this column?
What are driving criteria for creating filtered indexes on SQL server. I am trying to analyze the index stats through DMV,histogram and have to analyze if the filtered indexes should be created on tables. This exercise has to be done for all the transaction tables on the database. What are the approaches I should be looking on?
There was a deadlock on the DB because of huge writes on one of the big tables. Having filtered index on this table for the effected column would reduce the time taken for write operations. Hence we are looking for creating filtered indexes appropriately
Could you please tell me removing table columns with null value will reduce the datafile size.
Data file size needs to be reduced to make free space on the disk, Could please suggest what data type can be choosen to be removed from the existing table so that more space can be made(Char,varhchar,int,date etc )
I have a Report I need to hide subtotals and total and include detail only when I am exporting to excel. So, is there a way to capture that it is exporting to Excel? Possibly a way to capture the rs:Format=Excel?
I apologise if this question has been asked before but I have spent ages searching these forums and the internet for an answer and I am yet to find one.
My problem is that I have a package which imports a column, lets call it 'Column A'. Column A is used to create other columns, lets say Columns B, C & D. This is done in a script using asychronous input and once completed column A is no longer used. Other tranformations occur to B, C & D including a split and then finally a merge together again, but all the time A seems to remain a valid input on all processes yet I never choose to use or output it. When I come to the merge process I am required to merge columns B, C & D but also A, surely this is inefficient. Furthermore when the package has run I get a warning telling me that Column A is not required and should be removed, but I can not seem to find anywhere to remove it from the pipe.
I am hoping that I am just missing something obvious here but I have been tearing my hair out so any help would be much appreciated!
Seems obvious but I can't see how. How would I remove columns from a data flow so that columns which have been used earlier but are not needed for insert/update are taken out of the flow.
I'm asking because the data ends up in a update statement and the flow has got so big it is unreadable.
I need to pull all records from the Item table and then I need to populate the most recent OrderNo and O.DateCreated. I got this far but if there is a part in the item table that does not have an order against it, I do not get a value and my goal is to see any parts that have not been ordered in the last year. Something like this:
SELECT I.PartNumber, I.Description, I.DateCreated FROM item I CROSS APPLY (SELECT TOP 1 O.OrderNo, O.DateCreated FROM Orders O WHERE O.PartNumber = I.PartNumber ORDER BY O.DateCreated DESC) O PartNumberOrderNoO.DateCreated 1A1XXX 1CHXX1 1/8/2014 2A2XXX 1CHXX3 1/20/2014 3A3XXX NULL NULL 1B1XXX 2CHXX1 2/10/2014 2B2XXX 2CHXX3 2/22/2014 3B3XXX NULL NULL
I have a query which contains 12 left outer join. I remove some of the joins that don't have parameters. The result is coming same but usually when we remove joining it should take less exec time but for me it is taking more time. What could be the reason?
I wanted to remove an extra transaction log file that was no longer required, and ran the following against the database...
DBCC Shrinkfile (DB_Name_log2, Emptyfile); go alter database [Db_Name] remove file DB_Name_log2; go
I got a successful removal message. But if I go into the properties of the database, and click on files, it still shows up. Why is this and how can I get rid of it?
I have found a bunch of duplicate records in our housing database that ideally I need to delete.There are two tables that I need to remove data from ih_cml_log_entry and ih_cml_log_notes. There is no unique identifier between the tables for a log entry. So I have had to join on the person_ref, log_seq and the date/time of entry.How do I go about deleting the data - I've used the script below to identify what I need to delete -
SELECT * FROM ( select cml.person_ref, cml.open_date + open_time as 'datetime',cml.open_user,cml.log_type ,ROW_NUMBER() OVER (PARTITION BY cml.person_ref, cml.open_date + cml.open_time,cml.open_user,cml.log_type ORDER BY (SELECT 0)) AS RowNo ,n.note FROM ih_cml_log_entry cml
I usually do this through Access so I'm not too familiar with the string functions in SQL. My question is, how do you remove characters from the middle of a string?
Ex: String value is 10 characters long. The string value is X000001250. The end result should look like, X1250.
I've tried mixing/matching multiple string functions with no success. The only solution I have come up with removes ALL of the zeros, including the tailing zero. The goal is to only remove the consecutive zeroes in the middle of the string.
I have a varchar field which contains some Greek characters (α, β, γ, etc...) among the regular Latin characters. I need to replace these characters with a word (alpha, beta, gamma etc...). When I try to do this, I find that it is also replacing some of the Latin characters.
I have a sql server 2012 server and I need to prevent the users from creating new schemas by mistake. Is there any way to revoke that permission alone but still letting the user to create their own objects in dbo (yes I know that shouldn't be in dbo but that is another issue).
We have a multi-site AG and are demoting one of the remote sites out of the AG. In doing so, we've discovered that no logic exists to remove an IP address from the listener.
It seems that the ALTER AVAILABILITY GROUP MODIFY LISTENER logic lacks functionality other than ADD. [URL] ....
I'm afraid to just remove the IP address from the cluster object as that IP is also stored in the HADR systables.
select * from sys.availability_group_listener_ip_addresses select * from sys.availability_group_listeners
I setup SQL Server 2012 on Windows Server 2012 with the service accounts in the local Administrator group, but now that I'd like to remove the accounts from this group I'm finding they don't have the appropriate access to the network storage. notes on setting the per-service SID's for SQL (SQL Engine, Analysis Services, Reporting Services, and Agent Service) so they can read the Data, Log, and TempDB mount points?
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select Test_Name AS 'Test_Name', Table_Bird.Animal AS 'Birds', Table_Mammal.Animal AS 'Mammal', Table_Reptile.Animal AS 'Reptile, Table_Fish.Animal AS 'Fish' From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
I need to include pre k kids in the is script for one school and exclude them for the others. I have them all excluded with the last statement in the where clause. How would I go about accomplishing this.
Code: SELECT DISTINCT C.Student_ID, isnull(left(dbo.capfirst(rtrim([first_name])),12),' ') as First_Name, isnull(left(dbo.capfirst(rtrim([last_name])),17),' ') as Last_Name, LEFT(Middle_Name, 1) as Middle_Initial, RTRIM(CONVERT(CHAR,Birth_Date,101)) AS DOB,
I am having some difficulty in constructing outer joins. I havesimplified what I need to do and have included sample SQL statements:create table tab_a (id int, descr varchar(10), qty int)insert into tab_a values (1, 'item one', 10)insert into tab_a values (2, 'item two', 20)insert into tab_a values (3, 'item three', 30)insert into tab_a values (4, 'item four', 40)create table tab_b (id2 int, descr2 varchar(10), qty2 int)insert into tab_b values (1, 'item one', 10)insert into tab_b values (2, 'item two', 20)insert into tab_b values (3, 'item three', 30)insert into tab_b values (4, 'item four', 40)Here is the statement that I have:SELECT tab_a.id,tab_a.descr,tab_a.qty,tab_b.id2,tab_b.descr2,tab_b.qty2FROM tab_a LEFT OUTER JOIN tab_bON (tab_a.id = tab_b.id2 )WHERE tab_a.qty <= 30 ANDtab_b.qty2 > 20What I am trying to do is left outer join between tab_a and tab_b afterthey have been filtered based on the qty column. (for tab_a: qty <=30; and for tab_b: qty > 20).How would I go about that? I would like to do this efficiently sincethe two tables have about a million records and several other columnseach.
Hi all,I’m a bit new to this so hope this is not too obvious!I am running a query like so (simplified form)Select Cus_no, Cust_Name, course_idWhere Cust_id = 2From course tableHowever not all the Cust_Id’s have been entered therefore so using a twofilters out the information I might need.Is there a way I can get around this?Many thanksSam*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
In asp.net 3.5..... To get the number of rows returned when the select is executed....... Protected Sub SqlDataSource1_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Selected Session("MessageText") = e.AffectedRows & " Profile(s)"End Sub How do you get the number of rows returned when you apply a filter expression to a selection of rows? Thanks Craig
I'm just trying something that I haven't tried before, and I'm wondering if anyone has done it before. I'm building a report with three charts. This chart is going to go inside of Microsoft CRM. One of the charts has to use CRM filtered views, so that a manager can see his whole team's numbers, but a team member will only see his numbers. I can't do this any other way because of the complexity of the query, which has 3 select statements joined together as tables. It would be too complex to try to use a parameter throughout all of those tables, and I'm not sure how I would set up a parameter to show all of the data if the user were the manager, and only the single user's data if it were someone else.
The other two charts use filtered views as well, but they have similar joins, and I had to hard code a column with meaningless data into each SELECT table so that I could use it to join, as the tables had no other similarities.
The problem is that when you upload a report into CRM, if it uses filtered views, you don't have to go into Report Manager and change the data source from a shared to a custom data source. But on reports where I don't use the filtered views, I find that they always break when upload them, because they use the shared data source. This is the case even when I have created the report without the shared data source. So I usually have to go in and change the data source from shared to custom every time I upload a report without filtered views.
Because of the situation I described, this report uses both filtered views and non-filtered views (for the hard-coded columns). When I upload it into CRM, it won't work either way, with a shared or a custom data source.
I am relatively new to SSRS and having some problems showing the right results from my subreport. I am using a cube for my datasets. I have created a report parameters where I can filter out year and months to see the sales in certain month. I also want to add a calculation to display the sum for the choosen period.
f.exs. if I choose january 2004 my result look like this:
Key Product Amount 41999 prod. x 5,000 42999 prod.y 2,000
Totals: [results from subreport showing total of 7,000]
What I would like to do is to add a total that calculates the total according to the filter. I tried to add a subreport beneath, but I don´t know how to link the filtered condition to the subreport, so it displayes always the total amount for all years from the cube.
When you dril down on MonthYear you get the detail data:
Month Number of Sales Total Sales
- Jan 2007 10 $610.00
1 $10.00
1 $20.00
1 $30.00
1 $40.00
1 $50.00
1 $60.00
1 $70.00
1 $80.00
1 $100.00
1 $150.00
My question is. I added a filter to the detail data to give the Bottom % =75 of sales. So My detail data only displays the following rows:
Month Number of Sales Total Sales
- Jan 2007 10 $610.00
1 $10.00
1 $20.00
1 $30.00
1 $40.00
1 $50.00
My problem is the group still displays the total of my dataset (as seen above), but I want it to display the total of the detail data group, like below:
Month Number of Sales Total Sales
- Jan 2007 5 $150.00
1 $10.00
1 $20.00
1 $30.00
1 $40.00
1 $50.00
If I change the fields in the group to look at the detail data ,for instance =count(Fields!NumberofSales.Value,"Details_Group") I get an scope error.
How can I display the totals of the detail data in the parent group after I added a filter to the detail data?
