I have a simple table containing adresses.
A sample view of the table is
id name city
--------------------------------
100 Meier New York
101 Meier Tokyo
110 Olson Amsterdam
110 Olson Dublin
111 Paul Berlin
...
The view is ordered by (name,city)
Now my problem:
I want to see only the second half of the view
starting from "Olson" in "Dublin"
Using the constraint
where (name>="Olson") and (city>="Dublin")
does not the right thing. It eliminates i.e. row 111.
The only idea I have is to concatenate the fields to
simulate the compound index to be able to do
where name+"~"+city>="Olson~Dublin"
not to forget to struggle with null fields to get
the right results. This slows down the simple query
dramatically.
This seems to be a complicated solution for a simple
problem, given the fact, that the index is already
available at the server.
Why is Select * from [Merchandise] where [Product Name] like '[ABCD]%'the same as Select * from [Merchandise] where [Product Name] between 'A' and 'D'I can run Select * from [Merchandise] where [Product Name] like 'A%'and get Products that start with the letter "A" but they don't show up when I try to get all "A","B","C","D" Products.
Our current method of limiting what data a user can see is implemented solely through our Web based business intelligence tools. No filtering is enabled at the database level. This has become somewhat cumbersome as security is tied exclusively to these tools. The tools use one common logon to access the underlying database.
I would like to implement security at the database level (SQL Server 2000) and thereby produce a more flexible/portable solution. I was thinking of setting up individual database accounts for each user and then tying these into our company structure table by passing system_user result to a constraint.
For example System User name 'Store 2' would reference Store '2' in the structure table. Depending on the user, different columns will need to be referenced to filter the rows. A store user would be validated against the store column, an Area Manager user would be validated against the Area Manager column and Head Office users would not be valiadated at all i.e. they are not filtered.
1) What is the best method to implement such a look up. Can or should I use Check constraints for such a solution?
2) Would a UDF be useful?
Any ideas on the best approach to take would be greatly appereciated.
Hi, I have following query. I want to insert the value MBR_COV_EFF_DATE to table fixed_MM if the function dbo.CheckTheDate2 returns correct date and if it returns NULL I want to insert it on error_MM table. How can I do this?
select dbo.CheckTheDate2(MBR_COV_EFF_DATE,'MBR_COV_EFF_DATE') AS MBR_COV_EFF_DATE from [unfixed_MM]
Expression: =Fields!PRACTICE_ID.Value Operator: = Value: 20 and get this error: The comparison failed. Check the data type returned by filter expression.
Practice_id has int data type. How else can I check the data type returned by filter expression?
I tried to filter on table level the same way, and get the same error. Could anybody help, please, to make it work?
Hi, In my application datas are downloaded from "http://www.amfiindia.com/portal/upload/downloadnav.txt;" this website and written on a text file. Downloadnav.txt contens looks like this ------------- Scheme Code;Scheme Name;Net Asset Value;Repurchase Price;Sale Price;Date Open Ended Schemes ( Income ) ABN AMRO Mutual Fund102652;ABN AMRO Monthly Income Plan-Regular Plan-Growth Option;12.8907;12.8907;12.8907;4-Apr-2007102653;ABN AMRO Monthly Income Plan-Regular Plan-Monthly Dividend Option;10.6359;10.6359;10.6359;4-Apr-2007102654;ABN AMRO Monthly Income Plan-Regular Plan-Quarterly Dividend Option;10.5377;10.5377;10.5377;4-Apr-2007102622;ABN AMRO Flexi Debt Fund-Regular Plan-Daily Dividend Option;10.0000;10.0000;10.0000;4-Apr-2007102619;ABN AMRO Flexi Debt Fund-Regular Plan-Growth Option;11.6182;11.6182;11.6182;4-Apr-2007 Birla Sun Life Mutual Fund101843;Birla Bond Index Fund-Dividend Plan (Plan A);10.1607;10.1353;10.1607;4-Apr-2007101844;Birla Bond Index Fund-Growth Plan (Plan B);11.6557;11.6266;11.6557;4-Apr-2007101315;Birla Bond Plus-Insititutional Dividend;10.4411;10.4150;10.4411;4-Apr-2007 ---------------------------------------- I cut .copied datas alone in another text file datafile1.txt which looks like this ------------------------------------------ 102652;ABN AMRO Monthly Income Plan-Regular Plan-Growth Option;12.8907;12.8907;12.8907;4-Apr-2007102653;ABN AMRO Monthly Income Plan-Regular Plan-Monthly Dividend Option;10.6359;10.6359;10.6359;4-Apr-2007102654;ABN AMRO Monthly Income Plan-Regular Plan-Quarterly Dividend Option;10.5377;10.5377;10.5377;4-Apr-2007102622;ABN AMRO Flexi Debt Fund-Regular Plan-Daily Dividend Option;10.0000;10.0000;10.0000;4-Apr-2007102619;ABN AMRO Flexi Debt Fund-Regular Plan-Growth Option;11.6182;11.6182;11.6182;4-Apr-2007101843;Birla Bond Index Fund-Dividend Plan (Plan A);10.1607;10.1353;10.1607;4-Apr-2007101844;Birla Bond Index Fund-Growth Plan (Plan B);11.6557;11.6266;11.6557;4-Apr-2007101315;Birla Bond Plus-Insititutional Dividend;10.4411;10.4150;10.4411;4-Apr-2007 I am bulkinserting this datafile1.txt datas in to the Sqlserver table "Indian stocks".Since Downloadnav.txt has extra description,title That file is not able to bulk insert. Now i have to read/filter downloadnav.txt so that file will look like datafile1.txt. Bulk insert command which i use : Bulk insert INDIAN_STOCKS from 'C:Documents and SettingsAmrithaMy DocumentsKarthikaRobertRobertdatafile1.txt' with (FIELDTERMINATOR=';',ROWTERMINATOR=''); Can anyone tell me how to read txt file and filter it out(elimates titles,spaces).I am trying so hard for the past 1 week couldn't do. Thanks, Karthika
Hi,I have three dropdown lists branch, month & year. Based on these I need to retrieve data in my Datagrid.I have written the following SQL statement & code in my Refreshgrid(): SqlCmd.CommandText = " SELECT * " SqlCmd.CommandText += " FROM HSECData" SqlCmd.CommandText += " where branchno=" &Branchno SqlCmd.CommandText += " And yearno=" &YearNo SqlCmd.CommandText += " And monthno=" &monthNoThis is working fine, initially the datagrid is empty and then displays data when I select say, branch- Melbourne, Year-2005, Month-October and other exisiting combinations.The problem I am stuck at is when the page loads, initially I want all the data to be displayed in datagrid, i.e. for 'All' branches, 'All' years and 'All' months and then when I select combinations of these dropwdown lists as above the data should be filtered and displayed in those selected combinations. but as of now I get and empty datagrid when I select 'All' from these 3 drop down lists or when the page opens the first time.Can someone kindly guide me with the correct SQL cinstruct to achieve both the requirements?Thanks,Aartee.P.S. I am sorry if I have posted this question in a wrong category in the forum, I was unsure whether I should post it in Datagrid category or SQL server category, I thought this was my best bet.My aplogies.
I have a database table which is growing 100,000 recs per day. It has data from 06/01/1999 to current. I need to keep data only from 08/01/1999. I BCP'd out around more than 10 million rows(entire table) out into an OS flat file. Now I need to BCP in rows only after 08/01/1999. How do I filter those rows during BCP'ing in, or is there a way of doing it while BCP'ing out. The table has a field named proc_date smalldatetime(4) NOT NULL.
I have a stored procedure, which returns a list of results/data.
Some of that data includes a count.
What I would like is to filter the data where we see all the count data that has 0 or all the count data greater than zero.
Now it is entirely possibly for me to go back to the stored procedure and amend it so that that is entirely possible, however i was wondering if it was also possible to produce this by filtering data at the dataset.
I provide a filter at the datset by clicking on dataset and then filters and then the expression for the field for the search count and having a parameter that provides the value.
Now it is straightforward if i just want to show all results where the searchcount = 0.
The problem lies where i want to show all results that have a searchcount > 0. The problem being it only allows me to choose one operator = or >, not a combination of both, meaning I cannot have the option to have either results.
select distinct  bl_rec_ref  ,freq_cd  from hgmprty1 where(bl_rec_ref is not null and bl_rec_ref not like '%SCH%')
That produces the following output (part shown)
However what I need to show is only bl_rec_ref values where there is no corresponding freq_cd of QTLY.
Looking at the example data above Rows 45 and 46 have the same bl_rec_ref value with a MTHLY and a QTLY value in freq_cd so therefore I don't want this to appear in my output. Similarly with Rows 47 and 48 were the bl_rec_ref has both QTLY and MTHLY values so again should be excluded.
However looking at Rows 51 to 55 which has a bl_rec_ref only showing once for each value of MTHLY and no value for QTLY so these Rows are required in the output.
I can't find any info on what the 'IN' operator does when filtering data. What's the syntax? I'm hoping this operator will behave similar to the IN operator in sql. I want to pass in 1 or more values that I'd like my data filtered by. If I say <field> IN <single value> it works, but if I say <field> IN <value1>,<value2> I get back nothing at all. Any claification on the use of this operator and the correct syntax for my value list would be greatly appreciated!!
I have to filter my some of the data using below sql
select * from dbo.tmp_date where issue_dt <'042015' but its not filtering out because of NVARCHAR an if suppose i used datatype to int its removing 0 (zero) from the begning
My data is in formate MMYYYY formate
CREATE TABLE dbo.tmp_date (issue_dt NVARCHAR(10)); INSERT INTO dbo.tmp_date (issue_dt) SELECT '062012' UNION SELECT '062079' UNION SELECT '062015' UNION SELECT '062014' GO select * from dbo.tmp_date where issue_dt <'042015' go
I have a big presentation tomorrow and I have a query I'm trying to make a little more presentable. query text itself formatted (filtering data)
Tables: --tpeople --tsynclog
The query:
select Firstname+' '+Lastname as [FirstName LastName],synctime as [Nestle synctime],sum(DurationInMinutes) from tpeople join tsynclog on people_id = id where synctime > '2006-05-05' group by tpeople.FirstName,tsynclog.people_id,tpeople.LastName,tsynclog.SyncTime having sum(DurationInMinutes) > 100 order by synctime desc
As you can see I need some alias added and maybe the whole thing formatted a little different. Can anyone help? Thanks.
We are pulling data from a sharepoint list and we have created a report using business development studio in sql 2005 and have created a table with data that we want to display. We are trying to filter this data so that we can get the report to show entries for the day or for a week, but im not sure how to achieve this? Thanks in advance.
I have what at first site should be a simple reporting services issue but cannot resolve:-
I have a complex report comprising over 90 pages of various sections but when analysed in detail , 80% of it follows a simple pattern i.e. it comprises around 100 instances of the same sub-report bound to the same data source BUT grouped and filtered on different groupings and filter values.
The pattern is as follows:-
Each sub-report instance is bound to an SSAS cube which has fields L1, L2...L7
Each sub-report instance groups the data dynamically by one or more groups G1,G2..G7, the actual fields to be used being defined by the parent report (i.e. one instance may group by fields L7, L3, L4, another by L2,L3 etc)
Each sub-report instance has up to 7 filters defined (F1..F7) may filter the data i.e. the parent may pass a filter stipulating that L7="A", L3="B". To do this, the parent sets filter parameters F7="A" and F3="B"
The approach I have adopted is to set up parameters in the sub-report for dynamically grouping the data i.e. the sub-report has 7 parameters defined G1..G7 and the parent report populates G1..G7 with the grouping breakdown required for any given instance. This works fine!!!!
However what I am trying to do is prevent each sub-report performing major round trips to the underlying cube for each sub-report instance by specifying the filter as part of the MDX query by defining the 7 filter parameters as query parameters. (I could of course not filter the query and place the filter in the report but this would almost certainly lead to performance issues). Instead what I am struggling to do is to create the MDX query in such a way that when a filter is not supplied by the parent, the MDX query does not filter the data.
Can anyone advise on this. I guess my question is more of an MDX question than a reporting services one.
Hello, I am trying to use a boolean parameter to filter data in a table, but there is something I am missing.
Basically I want something like this: I have a boolean parameter, "EP", and I have a filter set on my table as:
Expression Operator Value =Fields!REFERRAL_SOURCE.Value = IIF (Parameters!EP.Value, "1297", ????)
Using the filter tab, I can't specify an expression for the 'Operator' so I was trying to work it out using either '=' or 'like'. What needs to go into the ???? in order for the referral source to be "not 1297" (i.e. the inverse of the filter)?
Or, am I completely missing an easy way to do this?
We have set up reporting in sql 2005 so that we can report from a MOSS 2007 list. At the moment the report is created with all the data from within the list. How could i create a filters so that the user can generate a report by name and date?
I am using SQL 2005. I have a SELECT query in a stored proc with 3 parameters: @subaccount,@numDaysCutoff,@numDaysPcts. The proc needs to be modified to return data when subaccount values are any of these:
FRRIJ FRRIC FRMM ROBECO FRJV MAIL FRUKV FRICE
Currently I use a WHERE condition and am able to get data correctly. However, for a NULL value I should get everything including those not in the above list. Should I use CASE statement instead? How?
@subaccount VARCHAR(8) = NULL , @numDaysCutoff INT = 1 , @numDaysPcts INT = 1
SELECT Subaccount = ISNULL(h.subaccount, lo.subaccount) , SecurityID = ISNULL(h.security_id, lo.security_id) , SecurityName = s.name , QtyHeldAndPending = ISNULL(h.quantity, 0) + (CASE WHEN lo.type = 1 THEN lo.resulting_quantity * (-1) WHEN lo.type = 2 THEN lo.resulting_quantity ELSE 0 END ) , L.AverageDailyVolume , XDaysVol = L.AverageDailyVolume * @numDaysPcts , CutoffVol = L.AverageDailyVolume * @numDaysCutoff , DaysVolHeld = h.quantity / NULLIF(L.AverageDailyVolume, 0) , HeldPctNDaysVol = h.quantity / NULLIF((L.AverageDailyVolume * @numDaysPcts), 0) * 100 , TargetedHoldingsUSD = tm.ApprovedPortfolioTarget * iv.value_usd , CutoffVolUSD = L.AverageDailyVolume * @numDaysCutoff * s.price_usd , TargetedPctNDaysVol = (tm.ApprovedPortfolioTarget * iv.value_usd) / NULLIF((L.AverageDailyVolume * @numDaysPcts * s.price_usd), 0) * 100 , DaysVolTargeted = (tm.ApprovedPortfolioTarget * iv.value_usd) / NULLIF((L.AverageDailyVolume * s.price_usd), 0) , NDaysCutoff = @numDaysCutoff , NDaysPcts = @numDaysPcts FROM subaccount_positions_table h --vGlobalHoldings h JOIN iv_subaccount_table iv ON iv.subaccount = h.subaccount FULL OUTER JOIN LiveOrders lo ON lo.subaccount = h.subaccount AND lo.security_id = h.security_id FULL OUTER JOIN TM_DerivedSecurityTargetDetail tm ON tm.Subaccount = h.subaccount AND tm.SecurityID = h.security_id LEFT JOIN dbo.security_table s ON s.security_id = COALESCE(h.security_id, lo.security_id) LEFT JOIN dbo.SecurityLiquidity L ON L.SecurityID = h.security_id AND SourceID = 99 WHERE (h.subaccount = ISNULL(@subaccount, h.subaccount) OR lo.subaccount = ISNULL(@subaccount, h.subaccount) ) AND status = 1 AND ( h.quantity > (L.AverageDailyVolume * @numDaysCutoff) -- qtyHeld > XDaysVol OR -- Targeted Vol exceeds cutoff ISNULL((tm.ApprovedPortfolioTarget * iv.value_usd), 0) > ISNULL((L.AverageDailyVolume * @numDaysCutoff * s.price_usd), 0) -- Target > XDaysVol ) ORDER BY ISNULL(h.subaccount, lo.subaccount), ISNULL(h.security_id, lo.security_id)
We are using xml to pull data from a custom sharepoint list into sql 2005. We have set a parameter that allows the user to filter the data by surname, however when the user tries to filter the list the drop down box brings up a list of every record, so there are duplicate entries for each surname.
Is there a way of filtering this so that there is only one instance of the users surname instead of it showing all the records?
I have a problem.. I have a report, with subreports, which does the job it was supposed to do. Unfortunately, subreports does not export to Excel very well, so I have to come up with a solution without the use of subreports.
The scenario (keeping it simple)
I have a list of departments. Each department can have multiple costs attached to it. The cost happens on a specific date. Fairly straightforward and simplified
I want to view each departments costs, sorted by month, like this:
Jan - Feb - March - April.... Dep SUM COST
Dep FOR EACH MONTH Dep Dep
... ...
I did this in the old report by passing the department ID and the date interval as parameters to a subreport and have the subreport return the sum of all the fields, which worked fine.
Now that the subreports can no longer be used, I'm struggling on how to sort my output on that date interval. I can group my result pr. department, but how to filter the result so that I get the right numbers in the right month... no luck yet.
As far as I know; I can't use different datasets in one table? I could have made a different dataset for each month...
Anyone know of a way to design this? I'm sure the problem isn't unique. Does one have to aggregate the sums inside the SQL query instead?
I need help with filtering a specific set of numbers. I have a Sql database that is connected to my sql report I have created a tsql statement that pulls a clients name, PO, and invoice number. The prblem I am having is I have 2 different types of invoice numbers one number looks like 123456-1234-T the other looks like 123455-1234-L I need to beable to pull only the invoices with T on one report and L on another report can some on show me how I can sort these in a tsql script
This may be simple, but I can't figure a way to do this. I have the following data returned to a table and need to sum only the items where HDMethod=0 in the table footer. For some reason, something like:
=Sum(Iif(Fields!HDMethod.Value=0, Fields!BDExtended.Value, Nothing)) returns all the rows. There is a LEFT OUTER JOIN: dbo.[Billing Detail].Item = dbo.[History Detail].Item between the tables touched in the query if that helps.
a text input for filtering a gridview that displays the data an SqlDataSource that contains the query. Users can either enter something into the text input or leave it blank. Depending on that, the gridview should either display all data (unfiltered, because nothing was entered into the text field) or filtered data (when something is entered). Now my problem is in defining the query in the SqlDataSource. I could do something like this: SELECT * FROM myTable WHERE myField = @p1; and then add in the appropriate <asp:ControlParameter /> under the <SelectParameters> tag. However, this sorta "fixes" the filter. Regardless of whether users actually type something in or not, the filter is in effect. I want it in such a way that if users do not type in anything, the query essentially becomes: SELECT * FROM myTable; Is there any way to achieve this? Thanks in advance, jason
I'm not sure if this is possible and have been having trouble figuring out the code to do this. I am assigning row_number to a gridview. I then want to filter the results with a dropdown. I am able to get the filter to filter the status but it either renumbers the gridview or it leaves the row numbers blank. Is there a way to have the row_numbers stick to the gridview when I filter? Example below. Thanks Normal:IssueNumber(row_number), Status1, Open2, Open3, Closed4, Open5, Closed "Open" Filter:IssueNumber(row_number), Status1, Open2, Open4, Open "Closed" Filter:IssueNumber(row_number), Status3, Closed5, Closed
I think I might be missing something here. Here is what I'd like to do:1. Retrieve a list of data from SQL Server.2. Display that data in a gridview.3. Have the user click on a button to then see a subset of that data. (filtering) I can't seem to make this work. When the user clicks the button, I need the GridView to update to show only the specified data. In 1.1 I would created a DataView for the filtering, but am trying to use the latest and greatest. I've seen examples online of people using DropDownLists to act as the dynamic filter parameter. How can I programatically assign this to make it work?Thanks!
