Dynamic Filtering Expression
Sep 18, 2007
I have a report which contains a parameter called SuppressZero which depending on its value I want to filter out certain data. This parameter can have 3 different values and for each value I need to have a different filter expression. What I would like to do is implement the following:
If SuppressZero = 1
Filter where Quantity <> 0
If SuppressZero = 2
No Filter
If SuppressZero = 3
Filter where Quantity <> 0 Or InStockFamily = "Y"
How can I do this in my report?
View 3 Replies
ADVERTISEMENT
Sep 1, 2015
I have a multi-tenant database where each row and each table has a 'TenantId' column. I have created a view which has joins on a CTE. The issue I'm having is that entity framework will do a SELECT * FROM MyView WHERE TenantId = 50 to limit the result set to the correct tenant. However it does not limit the CTE to the same TenantId so that result set is massive and makes my view extremely slow. In the included example you can see with the commented line what I need to filter on in the CTE but I am not sure how to get the sql plan executor to understand this or weather it's even possible.I have included a simplified view definition to demonstrate the issue...
ALTER VIEW MyView
AS
WITH ContactCTE AS(
SELECT Col1,
Col2,
TenantId
[code]....
View 4 Replies
View Related
Feb 3, 2008
I have a project where there are millions of records. For sake of example, let's use the White Pages.
One of the reports my client wants generated is a contact list. The contact list contains name, address, city, state, zip, phone, and several other key data elements.
Naturally, the client wants to be able to sort on each of those elements (easy to do.)
They also want to be able to filter the resultset the report shows, dynamically, at runtime (not so easy, I'm finding).
So, for example, one person may want to run the report and have the results only show people in PA. Another person may run a report and show all the people who are in Boston, MA. Yet another person may want everyone with the last name of "Smith".
Ultimately, the client wants to be able to filter results by virtually every day field displayed on the report (and some fields which are not!)
I've dug around the documentation for Reporting Services, and I readily admit I am a newbie. I cannot see any way to dynamically filter the resultset used to generate the report, other than using "Report Parameters".
The problem with Report Parameters, from what I can tell, is they also require me to have scalar expressions in a WHERE clause in the dataset. So, for example, if I wanted to let people filter on lastname, I would need a "WHERE LASTNAME LIKE @LastNameReportParameter" -- which works ok, as long as the parameter isn't null, of course, so I really have to do something like 'WHERE LastName LIKE ISNULL(@LastNameReportParameter, '%')"
With, literally, a dozen different fields on this report, I don't see where that is a real option -- I literally have to program a WHERE clause that has a dozen elements, some of which are numeric (not alpha), and thus LIKE doesn't work (AGE = 37) unless I get into completed gyrations (WHERE ISNULL(CAST(AGE AS VARCHAR), '') LIKE ISNULL(@AgeParameter, '%')
This seems to be incredibly complicated, and I cannot imagine there isn't an easier way to allow users to filter the resultsets on the reports generated by Report Server. Is there something I am missing?
Also, it has to be something I can address programatically, since the client wants a fairly 'seamless' front end (in C#) where users can simply click a radio button (to select a field to filter by) and type in a value (to filter on) prior to displaying the report in a reportviewer control.
Is there a way to dynamically program a filter expression and pass it to the report server, something akin to a filter expression on a BindingSource?
Any thoughts on this matter would be helpful, or pointers to online documentation, as I'm sort of out of ideas and cannot find anything in the docs. Thanks!
View 6 Replies
View Related
Nov 7, 2007
This is a very weird issue, and I can't seem to pinpoint the actual cause. I have a dynamic expression for Excel files that gets evaluated at run-time. One of the parameters for creating this path is pulled from the database - the column is nvarchar(10) and most of the values are only 2-characters long. I have tried LTRIM(RTRIM()) inside SQL Server as well as TRIM() in SSIS's Expression Editor, but nothing seems to work. At run-time the package fails, and the error I receive is because the value that gets pulled from the DB has trailing spaces. What's even annoying is the package runs fine on my XP machine, but fails on a W2K3 box - both setups are using the same back-end database.
Is this is a bug, or am I just screwing up something unknowingly? Thanks.
View 5 Replies
View Related
Jan 27, 2005
Hi I am trying to do something like the following:
DECLARE @Operator varchar(1)
DECLARE @Rate float
DECLARE @Quantity float
DECLARE @Converted float
SET @Quantity = 6
SET @Operator = '/'
SET @Rate = 2
SET @Converted = 0
@Converted = (@Quantity substituteTheValueOfThis(@Operator) @Rate)
PRINT @Converted
so that the output would be 3
The reason I need to do it like this is that @Operator will change at runtime...
Any suggestions appreciated, I have looked at EXEC sp_execsql but somehow can't get the syntax right.
View 5 Replies
View Related
Nov 13, 2006
I would like to run following T-SQL statement in the Derived Column:
declare @strData varchar(1000), @strSQL varchar(3000)
select @strdata = 'LANEX ~11/13/06 10:10:08 ~112233 9th ST S. Arlington, 22204, VA ~ 093300~XYZ '
select @strSQL ='select datalength('''+replace(@strData,'~','''),datalength(''') +''')'
--print @strSQL
exec (@strSQL)
Thank you!
View 4 Replies
View Related
Feb 8, 2007
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"
Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works
However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur
(Same error with other Excel source also:
Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
)
View 4 Replies
View Related
Oct 28, 2015
I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".
I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.
Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.
This is the lookup expression that I added for the 2nd column.
=Lookup(Fields!salesperson.Value,Fields!sales_person.Value,RunningValue(Fields!sales_amount.Value,
sum, " sales_person"),
"Largeappliances").
I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.
(processing): (SortExpression ++ m_context.ExpressionType)
View 7 Replies
View Related
Aug 25, 2007
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
View 12 Replies
View Related
Nov 23, 2007
Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.
So, is this possible using data driven subscriptions? Scenario is:
1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.
Any tips on how to get this working?
Thanks
Mark Smith
View 3 Replies
View Related
May 2, 2007
If anyone could confirm...
SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.
E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.
First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.
However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.
Question: I thought the filters would remain dynamic and be applied on each sync?
I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!
Question: I wonder why if parent records are supplied, why not child records?
If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...
Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
View 4 Replies
View Related
Mar 9, 2015
I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?
Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),
[code]....
View 1 Replies
View Related
Mar 24, 2007
I have a Stored Procedure for processing a Bill of Material.
One column on the Assembly Table is a Function Name that contains some busniess rules.
OK, now I'm doing a Proof of Concept and I'm stumped.
Huuuuh!
I will ultimately have about 100 of these things. My plan was using Dynamic SQL to go execute the function.
Note: The function just returns a bit.
So; here's what I had in mind ...
if isnull(@FnNameYN,'') <> ''
exec spinb_CheckYN @FnNameYN, @InvLineID, @FnBit = @FnBit output
CREATE PROCEDURE dbo.spinb_CheckYN
@FnNameYN varchar(50),
@InvLineID int,
@FnBit bit output
AS
declare @SQL varchar(8000)
set @SQL = '
if dbo.' + @FnNameYN + ' (' + convert(varchar(31),@InvLineID) + ')) = 1
set @FnBit = 1
else
set @FnBit = 0'
exec (@SQL)
GO
Obviously; @FnBit is not defined in @SQL so that execution will not work.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@FnBit'.
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@FnBit'.
So; is there a way to get a value out of a Dynamic SQL piece of code and get that value INTO my OUTPUT variable?
My many thanks to anyone who can solve this riddle for me.
Thank You!
Sigh: For now, it looks like I'll have a huge string of "IF" statements for each business rule function, as follows:
Hopefully a better solution comes to light.
------ Vertical Build1 - Std Vanes -----------
if @FnNameYN = 'fnb_YN_B1_14'
BEGIN
if dbo.fnb_YN_B1_14 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
------ Vertical Build1 - Scissor Vanes -----------
if @FnNameYN = 'fnb_YN_B1_15'
BEGIN
if dbo.fnb_YN_B1_15 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
.
.
.
etc.
View 10 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
