Now say I want to filter on this data by startDate and endDate, however I have to return any offer that is valid for a specific period.
E.g. if I filter by startDate = 2005-09-10 and endDate = 2005-10-01 then I want it to return the offers with the ID of 1 and 2 as both of these are valid for the dates I'm filtering on.
I can't for the life of me figure out how to do this at the moment, maybe it's too early in the morning to be thinking about this.
If i pass a date(03/01/2008) i need to get the startdate(31/12/2007) and enddate(05/01/2007) of the week. Is there any functionallity in SSRS 2005 to do this.
I want to display 2 weeks Events from Table 1 depends on repeat count
(if event scheduled on Tuesday(2014-11-04) and repeat count is 4 then it should repeat the 2 row of data)
We have to display only upcomming events, if the repeat count is 0 and the startdate and enddate falls by todays date then it should display as a single row.Data's Should be ordered by recent date
For some of my calculations it is mandatory to get the first and the last date, filtered by the slicer type "timeline".
This works well as long as the date is not used on rows or columns ...
As soon as I'm using theDate within the rows this very simplistic dax measure doesn't work ...
I'm wondering how to determine the StartDate (from the timeline) and the EndDate (from the timeline) when "thedate" is actively contributing to the filtercontext.
In my opinion it is not another example of the "event in progress" situation, due to the fact that start and end dates are actively selected by the user and can not be derived from within the date model.
Hi, I have created a report in SQL reporting using serveral parameters including a date parameters called StartDate EndDate
Now my requirement is, in ReportViewer I need to validate in such a way where "StartDate" should not be greater than "EndDate". So kindly help me out by giving me possible solution for the above issue.
I'm using some files to show certain pages on certain date for an example
File name : aa.doc start date: 10/02/00 end date : 10/03/00
But it expires on 10/02/00, here is the strored procedure:
Before the date comes, it expires the page Here is my stored procedure:
" SELECT startdate, enddate,archivedate and (startdate is null or (getdate() >= startdate and getdate() <= enddate)) and (archivedate is null or (getdate() <= archivedate)) group by startdate, enddate order by startdate desc "
Dim EventDataSource1 As New SqlDataSource()EventDataSource1.ConnectionString =
ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToStringDim dssa As New DataSourceSelectArguments()Dim EventID As String = ""Dim DataView = ""Dim dt As New Data.DataTableDim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToString())Dim cmd As New Data.SqlClient.SqlCommand("SELECT EventID FROM Event WHERE ([StartDate] = @StartDate)", conn)EventDataSource1.SelectCommand = ("SELECT EventID FROM Event WHERE ([StartDate] = @StartDate)")conn.Open()dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable()EventID = dt.Rows(0)(0).ToString()EventDataSource1.SelectParameters.Add("@StartDate",StartDate)EventID = cmd.ExecuteScalar()tbEventIDTest.Text = EventID
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
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.
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!
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.
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
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
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.
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,
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
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 %>
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')
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]
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
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...
Hi, all:I'm having trouble with something that probably has a simple solution.I have linking tables that can list a particular MemberID multipletimes. Is there a way to run a query so that a specific ID will showup once?Here is an example of the tables I've set up --MemberTable:==MemberIDMemberName1Dave2John3MichaelFruitTable:==FruitIDFruitName1Apple2Orange3PearVeggieTable:==VeggieIDVeggieName1Carrot2Celery3Potato....and these linking tables --Members2Fruits:==MemberIDFruitID1213223132Members2Veggies:==MemberIDVeggieID1221222331This is the query I'm using to retrieve the ID's:SELECT distinct m.*, m2f.*, m2v.*FROM ((MemberTable m INNER JOIN Members2Fruits m2fON 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
