Use TextBox Values In WHERE Clause To Perform Date Search?
Mar 17, 2008
I want to have a FromDateTextBox and a ToDateTextBox where the user can enter in dates (most likely in mm/dd/yy format, although intelligently handing other formats might be a plus). Then I want to use these dates as the basis for a WHERE clause like:
<some sql...> WHERE start_date BETWEEN 'FromDateTextBox.Text' AND 'ToDateTextBox.Text'
(Note this WHERE clause will be used as the basis for an SqlDataSource FilterExpression).
1. I believe the date strings need to be in the format 'yyyy-mm-dd' to search SQL server is this correct?
2. What's a decent way to convert the strings from the textboxes to the required format?
3. How can I avoid an SQL injection attack?
View 4 Replies
ADVERTISEMENT
May 24, 2007
I want to add up the values in a couple of text boxes in another textbox. How do I refer to the textboxes?
fields!textbox1.value doesn't work..what does?
View 1 Replies
View Related
Aug 31, 2007
Hello,
i am using Full text search for my web application search SP.
i have used Contains for Exact search and Freetext for Any word kind of search.
now i want to perform Like search using Contains.
same behaviour which we can get using LIKE word. (e.g. Select * from tbl where field like 'abc%')
even i have tried using contains(title,'"*Documen*"') . but its not working.
Kindly Let me know how to perform this.
Thanks.
View 3 Replies
View Related
Jul 3, 2006
I enter a id in the textbox and based on that it give me a list of all entries in gridview that use that id.This works.But if I leave it blank it gives me nothing. I want to make it so if I leave it blank it give me all the id's.Do I have to modify the sqldatasource on pageload?
View 1 Replies
View Related
Nov 28, 2006
hello..i need your help to provide me the idea and code how to view the data from database based on search criteria from user's input?the user's may fill up textbox and choose a criteria from dropdownlist...i am so no idea how to code it since i am fairly new in asp.net
View 5 Replies
View Related
Apr 19, 2007
All,
Please can someone advise how to use SQL select statement with where clasue which is based on a textBox.text value.
ex. below example I set the textbox.text value to a C# variable called TextBoxValue1 but I receive error this is not a valid
This is all done in Page_Load event backend code.
string strCommandtext = "Select Type.TypeName, Type.TypeDesc FROM Type Where Type.TypeName = TextBoxValue1";
View 2 Replies
View Related
Jan 25, 2008
hi, I have a question regarding calling sql table columns dynamically? workflow would go as:1. user enters search term into a textbox2. user checks a checkbox to search by column in sqldb (eg.. firstname or surname) pseudo sql would go like......SELECT +%column1(checkbox1.value)%+ OR +%column2(checkbox2.value)%+ OR +%column3(checkbox3.value)%+WHERE column1 = +%TextBox.Text%+ OR column2 = +%TextBox.Text%+ 3. display results in gridview my sql needs to improve greatly so any code insight(good book or link) would be terrific . thanks
View 10 Replies
View Related
Feb 20, 2007
Using a reporting services model/report builder we have two related tables:
- Fundings, (parent)
- Draws, (child)
Report Builder reports that subtract "Total Fundings.Amount", (which is SUM(FundingAmount)) from "Total Draw Amount", (which is SUM(DrawAmount)) to get a balance work as expected except when there are no Draw rows, in which case a NULL is returned. Obviously we want to convert NULL values of "Total Draw Amount" to zero so that when subtracted from "Total Fundings.Amount" the correct value is displayed. I've searched for a function similar to COALESCE (Transact-SQL) in report builder but found nothing.
Can anybody help me with this?
Thanks
Bruce
View 11 Replies
View Related
Apr 15, 2007
Is there a way in rs to have a textbox more than the first value when it is dragged on the rs form when creating a report? currently when I drag a text box on the rs form, it only shows the first record on a page/form. There are twenty records on the table where the first value came from. How can I make so that twenty text boxes representing twenty records appear on twenty different pages. In other words, I am trying to build a report with a non - tabular structure that can repeat on different pages.
View 4 Replies
View Related
Feb 8, 2007
Is there a way to reference a value from a textbox in a matrix? In other words I want to pull the value in the textbox that is the column header into a cell in the matrix under certain conditions.
View 3 Replies
View Related
Aug 6, 2007
Hi,
1. I have one combobox for supplierID, it displays 1,2,3...
Now i want to display the corresponing SupplierDescription in the textbox placed in the Page Header Section. If it is multiple selection then it has to be seperated by ',' (Comma).
Ex: IF i select 1,2,3 in the combobox then in the TextBox should look like
Suplliers: Ram, Don, Krish
Can you please help me in this
Thanks
Dinesh
View 5 Replies
View Related
Oct 6, 2007
the error message I get is
{"Object reference not set to an instance of an object."}
and it points to < Tickr As String = CType(FindControl("TickerTextbx"), TextBox).Text >
this is my code":
Protected Sub TickMastBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles TickMastBtn.Click
REM Collect variablesDim Tickr As String = CType(FindControl("TickerTextbx"), TextBox).Text
Dim Comp As String = CType(FindControl("CoTextbx"), TextBox).TextDim Exch As String = CType(FindControl("ExchTextbx"), TextBox).Text
REM Create connection and command objectsDim cn As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataVTRADE.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")Dim cmd As New SqlCommand
cmd.Connection = cn
REM Build our parameterized insert statementDim sql As New StringBuilder
sql.Append("INSERT INTO TickerMaster ")sql.Append("(Ticker,Company,Exchange,) ")sql.Append("VALUES (@Tickr,@Comp,@Exch,)")
cmd.CommandText = sql.ToString
REM Add parameter values to command
REM Parameters used to protect DB from SQL injection attacksWith cmd.Parameters
.Add("Tickr", SqlDbType.Int).Value = Tickr.Add("Comp", SqlDbType.VarChar).Value = Comp
.Add("Exch", SqlDbType.VarChar).Value = Exch
End With
REM Now execute the statement
cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
End Sub
View 3 Replies
View Related
Mar 23, 2006
Hi All,
I developed a report with some values in textboxes. I want the output not to wrap around to the next line but to be truncated if it is more that the size of the textbox. Is there any setting that i can do b/c values are going to the second line when their size is more like printing name and last name will cause last name to go to the second line ?
Thank you in advance.
View 5 Replies
View Related
Oct 25, 2006
Hi, it is few days I posted here my question, but received no answer. Maybe the problem is just my problem, maybe I put my question some strange way. OK, I try to put it again, more simply. I have few textboxes, their values I need to transport to database. I set SqlDataSource, parameters... and used SqlDataSource.Insert() method. I got NULL values in the database's record. So I tried find problem by using Microsoft's sample code from address http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.insert.aspx. After some changes I tried that code and everything went well, data were put into database. Next step was to separate code beside and structure of page to two separate files followed by new test. Good again, data were delivered to database. Next step: to use MasterPage, very simple, just with one ContentPlaceHolder. After this step the program stoped to deliver data to database and delivers only NULLs to new record. It is exactly the same problem which I have found in my application. The functionless code is here:http://forums.asp.net/thread/1437716.aspx I cannot find any answer this problem on forums worldwide. I cannot believe it is only my problem. I compared html code of two generated pages - with maserPage and without. There are differentions in code in ids' of input fields generated by NET.Framework:Without masterpage:<input name="NazevBox" type="text" id="NazevBox" /><span id="RequiredFieldValidator1" style='color:Red;visibility:hidden;'>Please enter a company name.</span><p><input name="CodeBox" type="text" id="CodeBox" /><span id="RequiredFieldValidator2" style='color:Red;visibility:hidden;'>Please enter a phone number.</span><p><input type="submit" name="Button1" value="Insert New Shipper" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("Button1", "", true, "", "", false, false))" id="Button1" /> With masterpage:<input name="ctl00$Obsah$NazevBox" type="text" id="ctl00_Obsah_NazevBox" /><span id="ctl00_Obsah_RequiredFieldValidator1" style='color:Red;visibility:hidden;'>Please enter a company name.</span><p><input name="ctl00$Obsah$CodeBox" type="text" id="ctl00_Obsah_CodeBox" /><span id="ctl00_Obsah_RequiredFieldValidator2" style='color:Red;visibility:hidden;'>Please enter a phone number.</span><p><input type="submit" name="ctl00$Obsah$Button1" value="Insert New Shipper" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$Obsah$Button1", "", true, "", "", false, false))" id="ctl00_Obsah_Button1" />In second case ids' of input fields have different names, but I hope it is inner business of NET.Framework.There must be something I haven't noticed, maybe NET's bug, maybe my own. Thanks for any suggestion.
View 2 Replies
View Related
Feb 29, 2008
Hi all,please have a look of code i am unable to perform save operation onthe asp.net web page.I ahve written a stored procedure. the same code works if all aretextboxes, but some of textbox replaced with dropdownlist box thenthis save operation doesn't occurs. please let me know where is themistake in coding .vb.net code :- Protected Sub btnSave_Click(ByVal sender As Object, ByVal e AsEventArgs) Dim employmentID As Integer =Request.QueryString("employmentID") Dim resourceID As Integer = Request.QueryString("resourceID") Dim projectID As Integer = Request.QueryString("ProjectID") Dim dbconsave As SqlConnection dbconsave = New SqlConnection(HRISDBConnectionString) Dim dbcomsave As New SqlCommand("sp_save_NewHireEmailnotify",dbconsave) dbcomsave.CommandType = CommandType.StoredProcedure dbcomsave.Parameters.Add(New SqlParameter("employmentID",SqlDbType.Int)) dbcomsave.Parameters("employmentID").Value = employmentID dbcomsave.Parameters.Add(New SqlParameter("resourceID",SqlDbType.Int)) dbcomsave.Parameters("resourceID").Value = resourceID dbcomsave.Parameters.Add(New SqlParameter("ProjectID",SqlDbType.Int)) dbcomsave.Parameters("ProjectID").Value = projectID dbconsave.Open() dbcomsave.Parameters.Add("@PreferredFirstName",SqlDbType.VarChar) dbcomsave.Parameters.Item("@PreferredFirstName").Value =txtPreferredFirstName.Text.ToString() dbcomsave.Parameters.Add("@PreferredLastName",SqlDbType.VarChar) dbcomsave.Parameters.Item("@PreferredLastName").Value =txtPreferredLastName.Text.ToString() dbcomsave.Parameters.Add("@CellPhone", SqlDbType.VarChar) dbcomsave.Parameters.Item("@CellPhone").Value =txtCellPhone.Text.ToString() dbcomsave.Parameters.Add("@HomePhone", SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomePhone").Value =txtHomePhone.Text.ToString() dbcomsave.Parameters.Add("@HomeAddressLine1",SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomeAddressLine1").Value =txtHomeAddressLine1.Text.ToString() dbcomsave.Parameters.Add("@HomeAddressLine2",SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomeAddressLine2").Value =txtHomeAddressLine2.Text.ToString() dbcomsave.Parameters.Add("@HomeAddressState",SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomeAddressState").Value =txtHomeAddressState.Text.ToString() dbcomsave.Parameters.Add("@HomeAddressCity",SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomeAddressCity").Value =txtHomeAddressCity.Text.ToString() dbcomsave.Parameters.Add("@HomeAddressZIP", SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomeAddressZIP").Value =txtHomeAddressZIP.Text.ToString() dbcomsave.Parameters.Add("@HomeAddressCountry",SqlDbType.VarChar) dbcomsave.Parameters.Item("@HomeAddressCountry").Value =txtHomeAddressCountry.Text.ToString() dbcomsave.Parameters.Add("@ArrangementType",SqlDbType.VarChar) dbcomsave.Parameters.Item("@ArrangementType").Value =ddlArrangementType.SelectedItem.ToString() dbcomsave.Parameters.Add("@PracticeGroup", SqlDbType.VarChar) dbcomsave.Parameters.Item("@PracticeGroup").Value =ddlPracticeGroup.SelectedItem.ToString() dbcomsave.Parameters.AddWithValue("@EquipmentNeeds",txtEquipmentNeeds.Text.ToString()) Try dbcomsave.ExecuteNonQuery() lblMessage.Text = "Record saved successfully" Catch ex As Exception End Try dbconsave.Close() End Sub==============================================================================.aspx code :-<table> <tr> <td > <asp:LabelID="lblPreferredFirstName" runat="server" Text="Name(Preferred FirstLast) :" ForeColor="Blue"></asp:Label> </td> <td <asp:TextBoxID="txtPreferredFirstName" runat="server" Text="" BorderStyle="None"></asp:TextBox> <asp:TextBoxID="txtPreferredLastName" runat="server" Text="" BorderStyle="none" ></asp:TextBox> </td> </tr> <tr> <td > <asp:Label ID="lblCellPhone"runat="server" Text="CellPhone :" ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBox ID="txtCellPhone"runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:Label ID="lblHomePhone"runat="server" Text="HomePhone :" ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBox ID="txtHomePhone"runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:LabelID="lblHomeAddressLine1" runat="server" Text="HomeAddressLine1 :"ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBoxID="txtHomeAddressLine1" runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:LabelID="lblHomeAddressLine2" runat="server" Text="HomeAddressLine2 :"ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBoxID="txtHomeAddressLine2" runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:LabelID="lblHomeAddressState" runat="server" Text="HomeAddressState :"ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBoxID="txtHomeAddressState" runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:LabelID="lblHomeAddressCity" runat="server" Text="HomeAddressCity :"ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBoxID="txtHomeAddressCity" runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:LabelID="lblHomeAddressZIP" runat="server" Text="HomeAddressZIP :"ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBoxID="txtHomeAddressZIP" runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:LabelID="lblHomeAddressCountry" runat="server" Text="HomeAddressCountry :"ForeColor="Blue"></asp:Label> </td> <td > <asp:TextBoxID="txtHomeAddressCountry" runat="server" Text="" ></asp:TextBox> </td> </tr> <tr> <td > <asp:Label ID="lblArrangement"runat="server" Text="Arrangement :" ForeColor="Blue"></asp:Label> </td> <td > <asp:DropDownListID="ddlArrangementType" runat="server" Width="160px"DataSourceID="ObjectDataSourceArrangementType"DataTextField="ArrangementType" DataValueField="ArrangementType"OnDataBound="ddlArrangementType_DataBound"> </asp:DropDownList> <%--<asp:TextBoxID="txtArrangement" runat="server" Text="" ></asp:TextBox>--%> </td> </tr> <tr> <td > <asp:LabelID="lblPracticeGroup" runat="server" Text="Practice Group :"ForeColor="Blue"></asp:Label> </td> <td > <asp:DropDownListID="ddlPracticeGroup" runat="server" Width="160px"DataSourceID="ObjectDataSourcePracticeGroup"DataTextField="PracticeGroup" DataValueField="PracticeGroup"OnDataBound="ddlPracticeGroup_DataBound"> </asp:DropDownList> <%--<asp:TextBoxID="txtPracticeGroup" runat="server" Text="" ></asp:TextBox> --%> </td> </tr></table>please help me to solve the issue.Thanks in advance
View 3 Replies
View Related
Jan 2, 2008
I have a field called "Owners", and it's a child to an "Activities" table.
An Activity can have on or more owners, and what I'd like to do is some how comma separate the values that come back if there are more than one owners.
I've tried a subreport, but because the row is colored and if another field, title, expands to a second row (b/c of the length) and the subreport has just one name, then the sub-report has some different color underneath due to it being smaller in height.
I'm kinda stuck on how to do this.
Thanks!
View 3 Replies
View Related
Oct 20, 2015
Let's say if the date is 01/01/2015 till 01/01/2016
I want split these dates monthly format wise and then use them in variable in cursors to loop
For an example Monthly date should be 01/01/2015 and 01/31/2015 and use these two values in 2 variables and make a loop until it never ends whole date range which is 01/01/2016
View 2 Replies
View Related
Apr 12, 2007
I created a reoport using wizard.In my quesry i took two date parameters.But when i run my report i get two texboxes StartDate and EndDate.How do i get calender button in front of the text boxes.Below is my query.
SELECT dbo.tblReferralTransferStatus.RefId
FROM dbo.tblReferralTransferStatus INNER JOIN
dbo.tblDownloadTracking ON dbo.tblReferralTransferStatus.VendorId = dbo.tblDownloadTracking.VendorId AND
dbo.tblReferralTransferStatus.DateModified < dbo.tblDownloadTracking.DateModified INNER JOIN
dbo.tblVENDOR ON dbo.tblReferralTransferStatus.VendorId = dbo.tblVENDOR.VendorId
WHERE (dbo.tblVENDOR.VendorName = @vendorname)AND (dbo.tblReferralTransferStatus.DateModified >= CAST(@StartDate AS varchar)) AND (dbo.tblReferralTransferStatus.DateModified < CAST(@EndDate AS varchar))
View 1 Replies
View Related
Nov 7, 2007
Hello
I wonder if someone could suggest a way to obtain the following. Using SQL Server 2005 I want to create some stored procedures. I want to query the DB with various filter arguments and combinations of these. One way would be to create one stored procedure for each function signature. However, as the number of combinations of filter is large, if possible I'd rather have a generic input to the each stored procedure that corresponds to the entire WHERE clause' search condition.
The stereotype behavior I'm looking for is:
SELECT myField
FROM myTable
WHERE @mySearchCondition
Does any one have some good suggestion, code samples and/or links?
Kind regards
Jens Ivar
View 2 Replies
View Related
Jun 20, 2006
i want to limit the data being stored in a table to the past 6 months only.
data in the table is refreshed each night.
how do i do this without hard coding a date?
View 3 Replies
View Related
Aug 3, 2007
First of all I€™m a VB beginner, real beginner, like today is my first time hahaha
My problem is that I don€™t know how to put my question into VB code. Here goes.
I€™m working in Visual Studio and Reporting Services with a chart. I have a chart that shows the amount of trips an opportunity has in my CRM system. The opportunity goes from Open to Won €¦ or lost but I don€™t want to see those.
What I want to do in this chart is to separate all the Open Opportunities from the Won ones. I want to use a Stacked Column chart so Open Opportunities is on the upper part and Won is on the lower part of the column of course.
I can get the trips data from the CRM database, but I don€™t know how to put it in VB code €œSum the trips where Opportunity=Won€? for one series and for the other €œSum the trips where Opportunity=Open€?. In the database I have fields like statecodename='Won'. So far I have in the Data Values, were I write my expression:
Opportunity Open Trips €¦ =Sum(Fields!trips.Value)
And the same for Opportunities Won Trips €¦ but that isn€™t right of course.
So how do I say €œ=Sum(Fields!trips.Value) €¦ Where statecodename='Won'€??
View 6 Replies
View Related
Jan 13, 2015
I've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
Is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
View 8 Replies
View Related
Oct 14, 2007
Hi, I have a unique problem that I am currently unable to figure out. I need to populate a where clause in a SQL statement that has multiple values, however those values always change because they are in another table. The end result that I want to end up with is a list of subs that belong to all of the UCI's that were selected for a particular bid number.
I have the following tables
tblBid with two columns. Bid_ID, and Uci_ID . This table contains multlple rows with the same Bid_ID but the Uci_ID is never the same for the current Bid_id. For example. If I had a Bid_ID of 123, I might have mutliple records listing
bid_id Uci_id
123 1000
123 2000
123 1050
tblSubs_By_Uci that has two columns. Sub_ID, and Uci_ID . This talbe contains a list of Uci_id's that Subs belong to. So I will have only multiple Sub_id and mulitple UCI_ID's because a sub can belong to mulitple Uci_ID's.
Uci_ID Sub_ID
1000 456
1000 2345
2000 456
1050 2345
2000 2345
This is the statement I am using to return the Uci's from the Bid table with bid_id of 123. For example. when I run the following sql statement, it will list all of the UCI's for bid_id 123. SELECT Uci_ID from tblBid where Bid_ID = 123 . That produces a list of UCI's. Now I want to find each sub that belongs to each of the UCI's using that list.
SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID = (SELECT Uci_ID from tblBid WHERE Bid_ID = 123) . I of course get an error from sql saying that I can not pass multiple values to the Where clause.
Can someone please help point me in the right direction. I have been searching on the net for days trying to figure this out. I am open to any suggestions.
View 4 Replies
View Related
Jul 26, 2006
how can i use multiple values in IN CLAUSE in a SQL query, that too when the number of values are changing at runtime.
complete SQL code required...............
I have following picture in mind but the values(in IN clause) are changing at Runtime
DECLARE @groups TABLE (group_id int)
SELECT * FROM abc WHERE abc_id IN (SELECT group_id FROM @groups)
View 4 Replies
View Related
Mar 2, 2006
Greetings,
I've search around quite extensively on the net and found a few examples that touch on this subject, but the only definitive one that seemed to solve this problem used a temp table in the UDF, which, to my knowledge, is impossible...
The problem is thus:
I want to create either a stored procedure or a user defined function to return a list of values I can intersperse to use in a WHERE AccountID IN (<values>). This way, if someone were to create a new stored procedure and they wanted to either only select accounts with those IDs or perform a NOT IN and use it to filter.
The Solution I'm attempting:
My idea is best represented in psuedo-code:
- Create a Function that stores all account Ids we relate to a particular account type, in this case, let's say accountsids "100, 101, 102, 407" are all accounts we want to consider "cash".
- The function would look something like:
CREATE FUNCTION CashAccountIDs()
RETURNS TABLE
AS
BEGIN
DECLARE TABLE @t1 (account INT)
INSERT INTO @t1 VALUES (100)
INSERT INTO @t1 VALUES (101)
INSERT INTO @t1 VALUES (102)
INSERT INTO @t1 VALUES (407)
RETURN @t1
END
Then I could call this function by doing something such as:
SELECT *
FROM Accounts
WHERE AccountId IN (dbo.CashAccountIds())
I would presumably do this for other collections of accounts as well, so that I would end up with say 5 functions I could call to filter various types of accounts.
Not too certain if I am approaching this the correct way or not, I've been receiving a myriad of errors trying different methods. If I use the function above it tells me "Must declare @t1", so I modified it so @t1 is declared in the RETURNS statement, and the syntax checks then work, but when I attempt to save the function it tells me "Cannot perform alter on fn_cashaccountids because it is an incompatible object type"
(The code I use to generate this error is:
CREATE FUNCTION fn_cashaccountids ()
RETURNS @t1 TABLE (i INT)
AS
BEGIN
INSERT INTO @t1 VALUES (100)
RETURN
END
Hopefully I've provided enough but not too much info to sift through, it seems to me this would be something encountered a bit before.
Any help is very much appreciated.
- Jeff
View 3 Replies
View Related
Sep 28, 2006
Hello everyone. This is my first post here, so be gentle =)
I need to construct a query, that would return roughly 6000 rows of data.
There are some conditions, or joins, that I can't figure out. Maybe you could help me?
This is the first one.
Invoice.ID-DocParty.DocumentID -> DocParty.OtherID-Party.ID -> Party.IDNumber
This can be achieved with inner join, no problem. Pretty simple.
However, there's a catch =)
DocParty.Role can have four different values in the 'where' clause. Is there a
way to fetch all of these four values without returning four duplicates with
only one field differing?
There are multiple fields in the query that are to be fetched in similar ways. Therefore,
using a IN('value1','value2','value3','value4') would increase the number of selected rows
a lot.
In addition, there is another type of condition that needs to be fullfilled.
Invoice.Type1Account-Account.ID -> Account.Number
Invoice.Type2Account-Account.ID -> Account.Number
Basically, there two fields in the 'main' table that are joined to the same field in another table
with different conditions. Can this be fetched with the same row as all the other data without duplicates?
Should I use a view somehow? How can I construct a view with these complex conditions if I can't
construct an SQL query, that would return no duplicates (pseudo-du
View 13 Replies
View Related
Aug 27, 2015
I have a QA Deployment Date field that is being returned in a custom report I created. I also found a sample date range parameter:
What I want to accomplish:
I want to select a From and To Date and filter the report to only display the rows that have the QA Deployment Date within the selected range.
For example.. I want to select From Date (8/1/2105) and To Date (8/31/2015) and I only want to return only the results that have a QA Deployment date between that selected range.
View 3 Replies
View Related
Jan 4, 1999
My process return a date in char format like "3/12/1998",
How could I put this data ( in char ) directly to date ISQ Server field ?
Thank's
View 1 Replies
View Related
Apr 7, 2008
hi friends,i need to select some of the employees from the EmpMaster using in clause. I tried to pass a string with the comma delemeters. it didn't produce all the records except the first in that string.shall i try with string functions in TSQL or any other options? Thanks and Regads,Senthilselvan.D
View 4 Replies
View Related
Jun 25, 2004
how does one specify multiple values for a single column in a where clause?
example:
SELECT fname, lname
FROM tblContacts
WHERE (state = 'MI','CA','AZ','TN','NJ')
if my memory serves me there is an IN() value list operator but I can't remember the syntax :confused:
View 2 Replies
View Related
Feb 9, 2007
Hi,
Is there a way to search for numeric values in the field.
For eample my table is
create table test (sector varchar(20))
insertinto test
values ('Hybrid 3/1')
values ('ARM')
values ('20yr')
is it possible to display
3/1
NULL
20
for the above?
Thanks.
View 3 Replies
View Related
Dec 22, 2006
Hi all. Fairly new to all this, but I just came across a problem today with SQL 2000. I need to get a report on all data in a table for the previous 12 months. So when ever the report gets run, say it gets run on Decemebr 4th, I would get all data from the beginning of last december up til the end of this november.
I tried saying WHERE dbo.Table.Date BETWEEN ((MONTH(GETDATE())-13) AND (MONTH(GETDATE())-1)) but that gave me everything between between December and November regardless of year.
Anyone know a good way to word this? I can't just ask for everything older than GETDATE()-365 because it needs to be Start of Month to End of Month regardles of when it's run.
Any help would be greatly appreciated.
View 5 Replies
View Related
Mar 14, 2008
I am working on query where i have to get data from 1 October 2007 till Current month - 1.
i.e
Current month is March 2008 then the data should be between 1 October 2007 till 29 Feburary 2008, and so on and so forth for every month.
I have written a query and It working fine but for some reason I think it can be done in some simple way.
Code Snippet
Select
datepart(mm,Tce1lsc0.budat) as 'Month'
,datepart(yyyy,Tce1lsc0.budat) as 'Year'
, Tmarketsector.market as 'Market'
, Tmarketsector.sector as 'Sector'
,Tce1lsc0.ARTNR AS 'Material_Number'
,Tce1lsc0.KNDNR AS 'Customer_Number'
from Tce1lsc0
left outer join TMarketSector on TMarketSector.[Kndnr]= Tce1lsc0.[KNDNR]
and TMarketSector.[Artnr] = Tce1lsc0.[ARTNR]
where convert (varchar (10) ,tce1lsc0.budat , 112) > 20070930
and ltrim (rtrim (datepart (mm,tce1lsc0.budat)+ datepart (yyyy,tce1lsc0.budat))) <> ltrim (rtrim (datepart(mm,getdate()) + datepart(yyyy,getdate())))
View 6 Replies
View Related
