Filtering Duplicate ID's?
Jul 23, 2005
Hi, all:
I'm having trouble with something that probably has a simple solution.
I have linking tables that can list a particular MemberID multiple
times. Is there a way to run a query so that a specific ID will show
up once?
Here is an example of the tables I've set up --
MemberTable:
==
MemberIDMemberName
1Dave
2John
3Michael
FruitTable:
==
FruitIDFruitName
1Apple
2Orange
3Pear
VeggieTable:
==
VeggieIDVeggieName
1Carrot
2Celery
3Potato
....and these linking tables --
Members2Fruits:
==
MemberIDFruitID
12
13
22
31
32
Members2Veggies:
==
MemberIDVeggieID
12
21
22
23
31
This is the query I'm using to retrieve the ID's:
SELECT distinct m.*, m2f.*, m2v.*
FROM ((MemberTable m INNER JOIN Members2Fruits m2f
ON m.MemberID = m2f.MemberID)
INNER JOIN Members2Veggies m2v ON m.MemberID = m2v.MemberID)
WHERE ...
By the way, I know of the GROUP BY clause, but it didn't work for me.
Thanks for any help.
J
View 2 Replies
ADVERTISEMENT
Jul 3, 2001
hi,
I am trying to fetch data from 2 tables, say TABLE1 and TABLE2, both of which got columns like id and num. Then i want all the rows from TABLE1 where id1=id2 and num1 != num2.
but it is showing all the rows for an id1 twice, if there are two records in TABLE2 with same id and num.
is there any way to filter those records without using the distinct keyword.
regards
Rajeev.
View 1 Replies
View Related
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
May 2, 2014
I am new in SQL programming world, following is query that i had created
select interfaces.nodeid as 'Node Id',
nodes.caption as 'Node Name',
netflowsources.Lasttime as NetflowLastTime
from interfaces inner join nodes on interfaces.nodeid = nodes.nodeid
inner join netflowsources on interfaces.interfaceid = netflowsources.interfaceid
where netflowsources.LastTime NOT LIKE GETDATE()
which is from that query i get a return successfully, but i just noticed that, for column nodeid was showed me multiple duplicated records, for example
nodeid value = a,a,a,b,b,c,c,c,c,d,d,d
But what i expected was to get a return without any duplicate record within it. and i also have tried using "distinct" command, but that only impacted on "a" value, but others value not change at all.
View 9 Replies
View Related
Oct 18, 2001
There's some SQL below (T-SQL) & I'm wanting to have this result set
grouped by Venue_ID in order to remove rows where there are duplicate values contained in just one column.
The columns BCOM_ID contain unique values, but Venue_ID can have duplicate
values. I only want to get rows for one instance of the Venue_ID (per
BCOM_ID) - doesn't matter which instance but basically, no duplicates.
Oh yes, one of the columns is a Bit column.
Any ideas would be welcome & appreciated!
Many thanks,
Darren
darren@darrenbrook.fsnet.co.uk
SQL:-
SELECT Booking_Header.BH_ID,
Booking_Header.Booking_Header_Description,
Booking_Header.BStat_ID, Booking_Header.BT_ID,
Booking_Header.Tagged, Booking_Header.Status_Timestamp,
Booking_Header.Start_Date, Booking_Header.Days_Qty,
Proposal.PPL_ID, Proposal.PPL_Status,
Booking_Component.BCOM_ID,
Booking_Component.Component_Description,
Booking_Component.Venue_ID, Venue.Venue_Code,
Venue.Description, Address.Address_ID, Address.Town,
Booking_Status.BStat_Description,
Booking_Type.Type_Description
FROM dbo.Booking_Header INNER JOIN
dbo.Proposal ON
dbo.Booking_Header.BH_ID = dbo.Proposal.BH_ID INNER JOIN
dbo.Booking_Component ON
dbo.Proposal.PPL_ID = dbo.Booking_Component.PPL_ID INNER
JOIN
dbo.Venue ON
dbo.Booking_Component.Venue_ID = dbo.Venue.VE_ID INNER JOIN
dbo.Address ON
dbo.Venue.VE_ID = dbo.Address.VE_ID INNER JOIN
dbo.Booking_Status ON
dbo.Booking_Header.BStat_ID = dbo.Booking_Status.BStat_ID INNER
JOIN
dbo.Booking_Type ON
dbo.Booking_Header.BT_ID = dbo.Booking_Type.BT_ID
WHERE (dbo.Proposal.PPL_Status = 1) AND
(dbo.Booking_Header.BH_ID = 10)
Thanks,
Darren
View 2 Replies
View Related
Nov 25, 2014
I want to filter out the duplicate rows based on three columns. I got this quick query from Microsoft site to filter out the duplicate rows, but I am getting the result that filters out the non-duplicate one too. Below is the query
;With Temp as (
SELECT row_number() over (partition by [id],[p_date], order by [id],[p_date],) as Row,
[code]...
In the above case id is null, but in some rows id is not null . The above is obviously not duplicate.
View 8 Replies
View Related
Oct 2, 2007
Hello Everyone:
I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
When I try to create the tables, for the query, I am getting the following error:
Msg 2714, Level 16, State 4, Line 12
There is already an object named 'UserID' in the database.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint. See previous errors.
I have duplicated this error with the following script:
USE [testing]
IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users1]
CREATE TABLE [testing].[dbo].[users1] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users2]
CREATE TABLE [testing].[dbo].[users2] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users3]
CREATE TABLE [testing].[dbo].[users3] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
I think that the schema is only allowing a single UserID primary key.
How do I fix this?
TIA
View 4 Replies
View Related
Aug 17, 2007
I've begun to get the above error from my package. The error message refers to two output columns.
Anyone know how this could happen from within the Visual Studio 2005 UI? I've seen the other posts on this subject, and they all seemed to be creating the packages in code.
Is there any way to see all of the columns in the data flow? Or is there any other way to find out which columns it's referring to?
Thanks!
View 3 Replies
View Related
Apr 18, 2007
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
View 8 Replies
View Related
Aug 29, 2006
I have table with the following columns.
ID, DearlershipLocation, VehicalMake, VColor, VType, VYear
1, London, Buick, Red, Sedan, 2000
2,
2006, Windsor, Ford, Blue, Jeep, 2002
My question is, how do I write a query to filter fron all Dealership location a speciif car like Ford with a red color and a sedan type?
Please help.
Thanks.
Juvan
View 1 Replies
View Related
Apr 4, 2008
Hello,
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.
Thanks for any help you can offer.
:
BDBilling BDExtended HDMethod BDDate
----------- --------------------- -------- -----------
14965 30.00 0 2008-03-24
14965 25.00 NULL 2008-03-24
14965 28.00 NULL 2008-03-24
14965 45.00 NULL 2008-03-24
14966 30.00 0 2008-03-24
14966 50.00 NULL 2008-03-24
14966 20.00 NULL 2008-03-24
14966 45.00 NULL 2008-03-24
14966 42.00 NULL 2008-03-24
14966 60.00 NULL 2008-03-24
14967 30.00 0 2008-03-24
14967 25.00 NULL 2008-03-24
14967 28.00 NULL 2008-03-24
14967 45.00 NULL 2008-03-24
14968 30.00 0 2008-03-24
14968 25.00 NULL 2008-03-24
14968 28.00 NULL 2008-03-24
14968 45.00 NULL 2008-03-24
14969 30.00 0 2008-03-24
14969 25.00 NULL 2008-03-24
14969 28.00 NULL 2008-03-24
14969 45.00 NULL 2008-03-24
14969 42.00 NULL 2008-03-24
14969 60.00 NULL 2008-03-24
14970 30.00 0 2008-03-24
14970 25.00 0 2008-03-24
14970 28.00 0 2008-03-24
14970 45.00 0 2008-03-24
14970 60.00 0 2008-03-24
View 3 Replies
View Related
Aug 29, 2006
I have a table with the following columns
ID, Dealershiplocation, VehicalMake, VColor, Vtype and VYear.
1, London, Buick, Red, sedan, 2001
------
20, Windsor, Ford, Blue, pickup, 2004
My question is how do I write a query so I can filter from all dealership location a specific vehical like
Ford with a red color and Sedan type?
Please help.
Thanks
Juvan
View 3 Replies
View Related
Aug 3, 2006
Hi All,
I have following:
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
View 5 Replies
View Related
Apr 1, 2008
does anyone one know how to filter this 01/23/2008 to 2008. i just want the year and stored it to a column in my sql database.
thanks
View 3 Replies
View Related
Jun 20, 2008
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
View 4 Replies
View Related
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
Mar 27, 2006
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!
View 1 Replies
View Related
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
Sep 1, 2003
Hello all,
I'm new to SQL Server so if the following sounds stupid then apologies. I am trying to design a query which compares columns and filters according to the result of the comparison. We are a UK based charity that provides financial help to families with disabled children (www.familyfund.org.uk). We have a large database (250,000 entries) which we know contains some duplicate/split files from a recent migration. We need to identify these files but not delete them. Currently I am using the following:
SELECT dbo.Families.famId, dbo.Address.street, dbo.Children.childId, dbo.Address.postcode
FROM dbo.Children INNER JOIN
dbo.Families ON dbo.Children.family_no = dbo.Families.famId INNER JOIN
dbo.Persons ON dbo.Families.famId = dbo.Persons.famId INNER JOIN
dbo.Address ON dbo.Persons.addressId = dbo.Address.addressId
WHERE (NOT (dbo.Children.eligStatus IN (3, 4)))
GROUP BY dbo.Children.childId, dbo.Address.postcode, dbo.Families.famId, dbo.Address.street
HAVING (dbo.Address.street IS NOT NULL)
ORDER BY dbo.Address.street
Obviously this returns all 250,000 records and then we have to search manually. We would like to run a query which compares families.famID to address.street so that where famId has more than one address attched it is returned to the results grid. Does this make sense? is it possible? Any help would be gratefully received
Thanks in advance
Mark
:confused:
View 8 Replies
View Related
Jun 15, 2001
to any who can help:
Here are some rows in a table with their lettered columns:
A B C D E
012345Ae2001-01-01 00:00:00.0002001-01-02 00:00:00.0000
012345Ae2001-01-02 00:00:00.0002001-01-03 00:00:00.0000
012345Ae2001-01-03 00:00:00.0002001-01-04 00:00:00.0000
012345Ae2001-01-04 00:00:00.0002001-01-05 00:00:00.0000
012345Ae2001-01-19 00:00:00.0002001-01-20 00:00:00.0000
012345Ae2001-01-20 00:00:00.0002001-01-21 00:00:00.0000
012345Ae2001-01-24 00:00:00.0002001-01-25 00:00:00.0000
012345Ae2001-01-25 00:00:00.0002001-01-26 00:00:00.0000
012345Ae2001-01-25 00:00:00.0002001-01-26 00:00:00.0001
012345Ae2001-01-26 00:00:00.0002001-01-27 00:00:00.0000
if you notice on the 8 and 9th rows the only difference between them is in
the E column(0 and 1). What I am trying to do here is to display all with
max(E). So in the above example, I should display rows 1-7,9,10 (8th row
will not display because the 9th row has 1 in the E column). this is the
query I have been using on SQL Server 2000 but I keep on displaying all the
rows:
SELECT A,B,C,D,max(E)
FROM <table>
WHERE ( A = '012345A' ) AND
( B >= '01/01/2001' ) AND
( C <= '01/31/2001' )
GROUP BY A,
B,
C,
D
any solutions?
TIA
View 1 Replies
View Related
Mar 19, 2007
Please can someone assist me which this simple query:
Im using MSSQL Server 2005:
select * from ex_messagelog where DateCreated = '2007-03-19'
The query above does not return any results, but my data looks as follow: Note, the DateCreated is a DATETIME Datatype field.
239test 2007-03-19 08:42:19.00000
240Hallo Frank2007-03-19 08:45:43.00000
241spring 123 2007-03-19 08:49:41.00000
242testing 1232007-03-19 08:51:15.00000
243testing 1232007-03-19 08:52:39.00000
244as 2007-03-19 08:55:30.00040
View 4 Replies
View Related
Feb 23, 2005
Hi,
ok, i'm building a page to display a list of courses, a user rating and 'last visited' date.
I have 3 tables -
course (a list of all courses)
review (a list of all ratings)
visit (user visits to each course)
I've put together an SQL statment that returns everything i need, however its not quite right.
SELECT course.courseID, course.courseName, course.courseURL, avg(review.fldRating) AS fldAverage, visit.visitDate
FROM course
LEFT OUTER
JOIN review
ON course.courseId = review.fldcourseId
LEFT OUTER
JOIN visit
ON course.courseId = visit.courseId and visit.userId = 2
GROUP BY course.courseId, course.courseName, course.courseURL, visit.visitDate
ORDER BY course.courseId, visit.visitDate DESC
The problem lies with the fact that each time a user enters a course a new record is inserted into the visit table - so the visit table will show how many times a user has entered a course and on which dates.
this SQL statment returns something like this:
------------------------------------------------
course1 | 5 stars | 10/02/05
------------------------------------------------
course1 | 5 stars | 03/02/05
------------------------------------------------
course2 | 4 stars | 01/01/05
because the user has entered course 1 twice, the list is now showing 2 course1's - how can I change the statemtent to only select the most recent user visit, but still keep the complete list of courses?
I'm a bit of an SQL novice, so appologies if I've not explained this very well,
Thanks in advance,
-------------
injureFish
View 2 Replies
View Related
May 26, 2008
Hi everybody..
have this table and I want to filter only those records that has it's id's appearing more than one.
table
id field1
1 ! first
1 ! second
2 ! first
3 ! first
3 ! second
4 ! first
the result should be
id field1
1 ! first
1 ! second
3 ! first
3 ! second
am using this query
select field1, id, count(id) as countid
FROM table1
GROUP BY field1
HAVING count(id) >1
the countid column gives me always the value of one (don't know the reason) so I couldn't get the results I want
thanks
View 4 Replies
View Related
Apr 20, 2004
Hi,
This is a really complicated issue and is hard to explain but i have the following:
select name, MAX(table2.time) from table1 INNER JOIN table2 on table1.id = table2.id GROUP BY name
which is fine and brings up the correct results but if I want to find out from those records what another field is in table 2 for each record it pulls up too many results (i want just the one result from table 2 and then find what user it is)
if I do..
select name, table2.username MAX(table2.time) from table1 INNER JOIN table2 on table1.id = table2.id GROUP BY name, table2.username
.. it pulls up too many results cos there are different usernames
if i dont group by table2.username then it give an error
View 5 Replies
View Related
Jun 13, 2008
Hello I am trying to gilter a table by getdate() (i also tried now()) but I cannot seem to be able to do it I place my code below if anyone can help. am grateful, my db is sql 2005.
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = getdate()
If (Request("MM_EmptyValue") <> "") Then
Recordset1__MMColParam = Request("MM_EmptyValue")
End If
%>
<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows
Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_connpeepeek_STRING
Recordset1_cmd.CommandText = "SELECT usr_image1, dateimage_usr FROM diddle.ps_usr_image WHERE dateimage_usr = ?"
Recordset1_cmd.Prepared = true
Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param1", 135, 1, -1, Recordset1__MMColParam) ' adDBTimeStamp
Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>
if any one can help I would be grateful
k
View 11 Replies
View Related
Mar 22, 2007
I am trying to filter data from columns and this is just not working. If I select all the criteria below and try to run it - I do not get any records returned.
WHERE (DropDt >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 13, 0)) AND (DropDt <= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0))
and Type IN ('Employee', 'Refinance')
and Chan IN ('XM', 'BN', 'RS', 'MM')
and Seg IN ('Hoc','LeftOver', 'COnly')
View 4 Replies
View Related
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
Dec 12, 2007
I have a View with a structure like this
VisitTimes Company
2007-07-10 14:24:38.000 Microsoft
2007-03-10 11:14:38.000 Microsoft
2007-12-01 13:04:56.000 SQLTeam
2007-12-13 12:54:52.000 GoldMan Sac
2007-08-11 02:15:38.000 Oracle
2007-02-11 12:45:04.000 SAP Ltd
I am asked to write a stored procedure that get a count of each count of visit on a START and END date
I wrote the below SP but am not getting the right result
I think the **where VisitTime >= @Start AND VisitTime <= @End)** is not being evaluated. Help pls
CREATE procedure dbo.GetVisits
@Start varchar(50),
@End varchar(50)
as
SELECT TOP 100 PERCENT COUNT(company) AS VisitCount, company
FROM visits.dbo.IViewVisits where EXISTS
(SELECT * FROM VISITS.dbo.IViewVisits where VisitTime >= @Start AND VisitTime <= @End)
GROUP BY company ORDER BY COMPANY ASC
View 6 Replies
View Related
Mar 2, 2008
Hi Friends, i have a question table and i want to filter this table using the following stored procedure but i cannot take a resultset ; (there is no error)
Stored Procedure is here:
CREATE PROCEDURE [dbo].[SP$AllQuestion]
@Session nchar(10),
@Class nchar(10),
@Unit nchar(10)
AS
BEGIN
SELECT L.QID, L.CLASS, L.SESSION, L.UNIT, L.CONTENT as QUESTION
FROM tblQUESTION L
WHERE @Session IS NULL OR L.SESSION = @Session)
AND (@Unit IS NULL OR L.UNIT = @Unit
AND (@Class IS NULL OR L.CLASS = @Class)
AND ((@Session IS NULL AND @Unit IS NULL ) OR (L.SESSION = @Session AND L.UNIT = @Unit))
AND ((@Session IS NULL AND @Class IS NULL ) OR (L.SESSION = @Session AND L.CLASS = @Class))
AND ((@Unit IS NULL AND @Class IS NULL ) OR (L.UNIT= @Unit AND L.CLASS = @Class))
AND (L.UNIT = @Unit AND L.SESSION = @Session AND L.CLASS = @Class)
END
i dont know what my fault is ; to your opinion what i have to do?
thanks from now on...
MC
View 2 Replies
View Related
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
Jul 2, 2007
I might be looking at this from the completely wrong angle, making this way harder than it needs to be, so maybe some fresh perspectives will help.
Let's say I have a simple SELECT that returns the total number of Orders for a given date, grouped by Order Status and Order Date. So my columns returned are as follows:
Date - Status - NumOrders
Now, what I want to do is create another very simple table that has essentially 3 columns:
Date - ShippedOrders - TotalOrders
07/02 23 100
Now the Shipped Orders column is simply just one of the many status' that I have in my recordset, i.e.
07/02/2007 - Shipped - 23
My first thought would be to simply filter on my Shipped Orders column, however, it appears that SSRS only supports filters at the data region level, which wouldn't help my situation.
It feels like I'm missing something very basic here, so perhaps my brain is a bit cloudy from the weekend. I know I can achieve what I'm looking to do by easily manipulating the SQL, but I'm trying to increase my comfort level with SSRS. Any insight is appreciated.
Thanks.
View 2 Replies
View Related
Dec 27, 2007
I am using the following SQL query in a SSRS 2005 report (see below).
I need dbo.FilteredNew_Assets.new_assettypename = "Computer" ONLY when
the value of dbo.FilteredNew_Assets.new_assignedemployeeid is NOT
null. In other words, only when
dbo.FilteredNew_Assets.new_assignedemployeeid has a value do I need
dbo.FilteredNew_Assets.new_assettypename to be filtered.
Is this a task that needs to be accomplished within my SQL query or
somewhere within the context of the actual report? Either way, how do
I accomplish this?
SELECT dbo.FilteredNew_Employee.new_employeeid,
dbo.FilteredNew_Assets.new_assignedemployeeid,
dbo.FilteredNew_Employee.new_employeetypename,
dbo.FilteredNew_Employee.new_firstname,
dbo.FilteredNew_Employee.new_lastname,
dbo.FilteredNew_Assets.new_assettypename,
dbo.FilteredNew_Assets.new_computertypename,
dbo.FilteredNew_Assets.new_manufacturer,
dbo.FilteredNew_Assets.new_model,
dbo.FilteredNew_Assets.new_modelnumber,
dbo.FilteredNew_Assets.new_assetsid,
dbo.FilteredNew_Assets.new_name
FROM dbo.FilteredNew_Employee LEFT OUTER JOIN
dbo.FilteredNew_Assets ON
dbo.FilteredNew_Employee.new_employeeid =
dbo.FilteredNew_Assets.new_assignedemployeeid
ORDER BY dbo.FilteredNew_Employee.new_lastname
View 3 Replies
View Related
Dec 6, 2007
I have the following below which works fine. However I would also like to filter on the group to say where the SumAmount is greater than 0 after the grouping occurs...
How would I do that? I guess I could put the results in a table variable and filter on that, but is there any other way?
Thanks
select grdproductvariant, psproduct, preAllocationBusUnit, preallocationdept, glaccountid, Sum(Cast(Amount AS Decimal(18,2))) As 'SumAmount'
from tblTransaction tbl
where periodinfile= '200710' and datasourceIdinfile = 'ADJ'
group by grdproductvariant, psproduct, preAllocationBusUnit, preallocationdept, glaccountid
View 4 Replies
View Related
