I Need Help Coding A Query In SQL? (filtering Data

May 17, 2006

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.

View 2 Replies


ADVERTISEMENT

SQL - How To - I Need A Query Solution, No Coding....

Nov 1, 2007

Hi. I'm looking at a problem and I can't find any solution short ofcoding.I have a pool of, say, 1000 PINS. I have 7 tables (buckets). Any ofthe 1000 PINS can be in 0, 1, 2, or... or all 7 buckets. So let's saythat-bucket A has 100 PINS-bucket B has 300 PINS-bucket C has 600 PINS-bucket D has 200 PINS-bucket E has 500 PINS-bucket F has 350 PINS-bucket G has 700 PINSI need to know, for each PIN, the number of buckets (tables) itbelongs to, and which ones, i.e:- PIN 1 belongs to A, C, D, so it belongs to 3 buckets- PIN 2 belongs to A, C, D, F, so it belongs to 4 buckets- PIN 3 belongs to A, so it belongs to 1 bucket- PIN 4 belongs to A, B, C, D, G, so it belongs to 5 buckets- PIN 5 belongs to ..., so it belongs to 0 bucketsetc, etcWhat would be the simplest way to achieve that, please ?Thank you very muchAlex.

View 5 Replies View Related

SQL Server 2012 :: Alter Query To Avoid Hard-coding?

May 25, 2015

using below query to raplace the string values (REPLACE abc with T1223), how to use the query without hard coding.

i want to store the values in another temp table and access in main query.

'abc', 'T1223',
'def', 'T456',
'ghi', 'T789',
'jkl', 'T1011',
'mno', 'T12'
select id,name,
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(name,
'abc', 'T1223'),
'def', 'T456'),
'ghi', 'T789'),
'jkl', 'T1011'),
'mno', 'T12'))) New_id
from TAB

View 4 Replies View Related

Need Coding For Inserting Data Into SQL Server Using Stored Proecdure.

Dec 19, 2007

Hi to all..
I am new to ASP.Net, I want coding for inserting data in to SQL server 2005 that code contain the following criteria.
  1.ASP.Net using C#.Net.
  2.Using Stored Procdure.
  3.the connetion string ,command , Data reader is in the same class. this class will be reusable for other page also without modifying any thing in it.
Please Help me.
  Advance thanks.

View 2 Replies View Related

Newbie - Filtering A Query

Feb 17, 2006

Hi All

I am writing a query to list all users in 2 countries but exclude users which their name starts with _inactive_5#4$66899, holder_....

I wrote

Select position, subsidiaryName
from position
where subsidiaryname = 'country one' OR subsidiaryname= 'country 2'

I get a result but there are some names which are fake accounts

eg. names start with inactive_#@$%&*ijkgfhg

or with holder_uhgfjgj

how can I do write into the query so that only the proper names without the rows that are fake showing up. In other words i want all results for those 2 countries except ones starting with inactive or with holder......?

thank you:)

View 2 Replies View Related

I Need Sql Query Filtering Option

Mar 12, 2007

My table have many column.........One for Message(column).My table updating every seconds......

But I need last Message.........

Ex

Message(Column) ..............
-------
firstmsg
nextmsg
middlemsg
jsjfsjmsg
jhsffmsg
lastmsg

In table i need lastmsg--- How to get

View 7 Replies View Related

Query Filtering Problem

Apr 27, 2006

I do not want results with combinations like this:

d.workphone is null and m.workphone = '0'

m.workphone is null and d.workphone = '0'

but my query below still brining in records like this, how can I fix this:

select m.number, m.workphone as master_workphone, d.workphone as debtor_workphone

FROM master m

INNER JOIN debtors d ON d.number = m.number

where (d.workphone <> m.workphone)
AND NOT (d.workphone = '' AND m.workphone = '')
AND NOT ((d.workphone = '0' AND m.workphone = '') OR (d.workphone = '' AND m.workphone = '0'))

View 3 Replies View Related

Filtering Rows In Server Query

Sep 10, 2015

I am new to SQL,I wrting a query which I will at the later stage create a report but my query is creating duplicate records,I have tried the group by and the sub selects but this still bring all uneccessary columns I do not need, 

I have a query that goes 

Select 
name
Date from 
date from
date to
summary 
Estimated value

I know in this database i only have 1 record containing that information but immediately  when i ass the estimated value field a lot of duplicate,I have tried using group by the same thing happens,or even when i tried having count then all the columns disappear.

View 3 Replies View Related

Is There A Site Or A Document For Query Optimization Tips And TSQL Coding Tips?

May 9, 2008

Hi.

Me any my team are soon going to work on a performance critical application. My team has some experience of writing SQL, however we have not done performance oriented coding.

I am looking for a comphrehensive document which lists information for writing good SQL with performance. Please guide if there is such a document or web site.


Thanks,
Prasad

View 1 Replies View Related

Help On Filtering Data

Nov 9, 2004

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.

View 2 Replies View Related

Filtering Data

Nov 13, 2001

Hi,

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.

Thanks

Rob

View 1 Replies View Related

Filtering Bad Data

Sep 24, 2007

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]

Thanks
Sanjeev

View 2 Replies View Related

Filtering Data

Feb 19, 2007

I need to filter data.

In dataset/ Filter tab, I have entered:

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?

Thank you

gndsp

View 3 Replies View Related

Query Questions, Filtering Or Sorting Of Columns

Oct 26, 2004

I have a database of automobiles. I have many many columns, it is blank of course until I can start filling it with information.
I will have four main rows. Yearnum, Make, Model, and VehicleStyle columns
I will use Honda Accord as an example.
Honda has made the Accord since probably the 80's
I know that if I
SELECT yearnum
FROM YearNum
ORDER BY yearnum

I am using C++ Builder too..
it will put all my years in order, in a combo box. But I believe it will also have duplicates, like for example They may have made a 1995 Honda Accord and then made a 1995 Honda accord LS which may have different wiring colors and speakers sizes than the regular accord.
Is there anyway to filter out multiple years, so I could just have the regular order of years?

View 1 Replies View Related

Filtering Data Using Sqldatasource

Oct 12, 2006

i HAVE TRIED THE FOLLOWING CODE BUT ITS NOT WORKING AS I WANT TO FILTER IT ACCORDING TO THE VALUE OF DROPDOWNLIST  I HAVE TRIED CONFIGURE THE SQLDATASOURCE. DATASOURCE MODE PROPERTY IS SET TO THE DATASERSTILL IT IS NOT SHOWING ANY RESULTS <%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">   <html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server">    <title>Untitled Page</title></head><body>    <form id="form1" runat="server">    <div>        <h3>Search Jobs</h3>         <table cellspacing="10">                      <tr>            <td valign="top" style="height: 162px">              <table border="0">                <tr>                  <td valign="top" style="width: 70px">                      Location</td>                  <td><asp:DropDownList runat="server" id="CountryListBox" AppendDataBoundItems="True"                                        DataSourceID="CountrySqlDataSource"                                         DataTextField="location" DataValueField="location" AutoPostBack="True" >                        <asp:ListItem Selected="True" >(Show All)</asp:ListItem>                      </asp:DropDownList>                  </td>                </tr>                <tr>                  <td style="width: 70px">                      Skills</td>                  <td><asp:TextBox runat="server" id="LastNameTextBox" Text="*" /></td>                </tr>                <tr>                  <td style="width: 70px"></td>                  <td><asp:Button runat="server" id="FilterButton" Text="Filter Results" /></td>                </tr>              </table>            </td>             <td valign="top" style="width: 587px; height: 162px;">                              <asp:GridView ID="EmployeesGridView"                DataSourceID="SqlDataSource2"                 DataKeyNames="employeeID"                AutoGenerateColumns="False"                AllowSort="True"                RunAt="server" Height="143px">                                <HeaderStyle backcolor="Navy"                  forecolor="White"/>                                  <RowStyle backcolor="White"/>                                <AlternatingRowStyle backcolor="LightGray"/>                                <EditRowStyle backcolor="LightCyan"/>                  <Columns>                      <asp:BoundField DataField="employeeID" HeaderText="employeeID" ReadOnly="True" SortExpression="employeeID" />                      <asp:BoundField DataField="employeeName" HeaderText="employeeName" SortExpression="employeeName" />                      <asp:BoundField DataField="companyName" HeaderText="companyName" SortExpression="companyName" />                      <asp:BoundField DataField="jobSkills" HeaderText="jobSkills" SortExpression="jobSkills" />                      <asp:BoundField DataField="experiance" HeaderText="experiance" SortExpression="experiance" />                      <asp:BoundField DataField="location" HeaderText="location" SortExpression="location" />                  </Columns>              </asp:GridView>            </td>                          </tr>                    </table>                    <asp:SqlDataSource ID="CountrySqlDataSource"           SelectCommand="SELECT DISTINCT location FROM tlbEmployee"          EnableCaching="True"          CacheDuration="60"          ConnectionString="<%$ ConnectionStrings:ConnectionString %>"          RunAt="server" />         <asp:SqlDataSource ID="EmployeeDetailsSqlDataSource"           SelectCommand="SELECT * FROM [tlbEmployee] WHERE (([location] LIKE '%' + @location + '%') AND ([jobSkills] LIKE '%' + @jobSkills + '%'))"          EnableCaching="True"          CacheDuration="60"          ConnectionString="<%$ ConnectionStrings:ConnectionString %>"          FilterExpression="location LIKE '{0}' AND jobSkills LIKE '{1}'"          RunAt="server">           <FilterParameters>            <asp:ControlParameter ControlID="CountryListBox"   PropertyName="SelectedValue" />            <asp:ControlParameter ControlID="LastNameTextBox" PropertyName="Text" />          </FilterParameters>            <SelectParameters>                <asp:ControlParameter ControlID="CountryListBox" Name="location" PropertyName="SelectedValue"                    Type="String" />                <asp:ControlParameter ControlID="LastNameTextBox" Name="jobSkills" PropertyName="Text"                    Type="String" />            </SelectParameters>        </asp:SqlDataSource>        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"            SelectCommand="SELECT DISTINCT location FROM tlbEmployee"></asp:SqlDataSource>        </div>        <asp:SqlDataSource ID="SqlDataSource2" runat="server"></asp:SqlDataSource>    </form></body></html> 

View 1 Replies View Related

Filtering Downloaded Data

Apr 4, 2007

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
 

View 2 Replies View Related

SQL Construct For Filtering Data

Nov 15, 2005

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.

View 1 Replies View Related

Filtering Data While BCP&#39;ing Into SQL Server

Sep 28, 1999

Hi Nerds

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.

Anyone can please suggest a way of doing this.

Thanks

Pran.

View 1 Replies View Related

Problem With Filtering Data

Jan 14, 2006

Hello,I have a simple table containing adresses.A sample view of the table isid name city--------------------------------100 Meier New York101 Meier Tokyo110 Olson Amsterdam110 Olson Dublin111 Paul Berlin...The view is ordered by (name,city)Now my problem:I want to see only the second half of the viewstarting from "Olson" in "Dublin"Using the constraintwhere (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 tosimulate the compound index to be able to dowhere name+"~"+city>="Olson~Dublin"not to forget to struggle with null fields to getthe right results. This slows down the simple querydramatically.This seems to be a complicated solution for a simpleproblem, given the fact, that the index is alreadyavailable at the server.Does anyone has an idea or suggestion?-Hubert

View 5 Replies View Related

Filtering Data Using = And &&> As An Operator?

Feb 11, 2008

Hi all,

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.

Where am i going wrong?

View 6 Replies View Related

Transact SQL :: Filtering Out Data

Aug 31, 2015

I have a simple query

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.

View 4 Replies View Related

Filtering Data On A Table...

Jun 12, 2007

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!!

View 1 Replies View Related

Filtering On Duplicate Data

May 2, 2008

Hello,

I have a dataset which I would like to remove data from, but I can't seem to find out how to do this.

The dataset contains the following columns:

SCode, NIn, SIn, AIn, NOut, SOut, AOut and TagNumber.

I would like to remove data from the dataset when the following occurs:

("SC123", "NIn123", "s-in-323", "a-in-342", "NOut43", "s-out-231", "a-out-45", "tagnumber12")
("SC123", "NIn123", "s-in-xyz", "a-in-xws", NULL, NULL, NULL, "tagnumber12")

This is when NIn occurs with the same value more than once, and I would like to remove (or ignore, filter) the row when NOut, SOut and AOut are null.

I am new to SSIS and can't see how I could do this (although I'm sure it's possible).

If anyone could show me how I would appreciate it.

Thanks.

View 11 Replies View Related

SQL 2012 :: Data Not Filtering Properly

Apr 2, 2015

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

View 2 Replies View Related

Filtering Sharepoint Data In Sql 2005

Mar 18, 2008

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.

View 12 Replies View Related

MDX Filtering And Grouping Data Within Subreports (help)

Aug 28, 2007

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.

View 1 Replies View Related

Filtering Data Using A Boolean Parameter

Apr 24, 2008



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?

Thanks.

View 4 Replies View Related

Data Mining Slicing / Filtering

Mar 13, 2007



Is there a way to dynamically slice / filter data when running a data mining query?

(Dynamically means - not deployed as part of the structure)



For example - I€™d like find associations to products sold in different geographic locations.

Does the answer differ if the structure is based on a cube or on a rational DB?

View 1 Replies View Related

SQL Server 2012 :: Filtering Query Using CASE Statement Within WHERE Clause

Aug 21, 2014

How I am using a CASE statement within a WHERE clause to filter data:

CREATE PROCEDURE dbo.GetSomeStuff
@filter1 varchar(100) = '',
@filter2 varchar(100) = ''
AS
BEGIN
SELECT

[Code] .

What I want, is to be able to pass in a single value to filter the table, or if I pass in (at the moment a blank) for no filter to be applied to the table.

Is this a good way to accomplish that, or is there a better way? Also, down the line I'm probably going to want to have multiple filter items for a single filter, what would be the best way to implement that?

View 5 Replies View Related

Filtering Data Based On Logged In User

Mar 10, 2008



Is this level of security possible in RS 2005? if so how?

Any guidance would be appreciated.

Thanks.

View 1 Replies View Related

Filtering Report Data From A Sharepoint List

Mar 16, 2008



Hello,

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?

Thanks.

View 3 Replies View Related

SQL Server 2014 :: Indexes And Views - Manage Date Filtering Within A Query?

May 21, 2014

I have a dynamic SQL query that uses various indexes and views.

When a user wants to filter records based on last one day, last one week, last 30 days ...etc.. i use the following code:

@date is an integer.

begindate is datetime format.

begindate > cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20))

The above code slows my query performance by at least 400%!

Would it be faster to add a computed column to my table, using the suggested method in the link below?

[URL] ....

Then i could add an indexed view to improve performance, or can this be done another way?

View 9 Replies View Related

Filtering Data Based On Select Parameter Values

Jun 6, 2006

Hi,

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)

Thanks in advance!!!
sqlnovice123

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved