Hi, i wanted to take all the orderID that fall between a certain date. I was thinking of using this syntax... Dim sql as string = "SELECT orderID " & _
"FROM orders " & _
"WHERE orderDate BETWEEN 11/11/2007 AND 15/11/2007"
in my database i set the date to nvarchar, not date... so will this syntax works??
I need a hand in this script. SELECT a.id ,value = CASE when (x1-x0) = 0 then ((Y2-Y0)-(Y1-Y0))/(0.000000001*(X2-X0)) ELSE ((-111.48277-Y0)-(Y1-Y0))/((X1-X0)*(32.72383-X0)) END ,value2 = CASE when (x1-x0) = 0 then ((Y2-Y0)-(Y1-Y0))/(0.000000001*(X2-X0)) ELSE ((-111.48277-Y0)-(Y1-Y0))/((X1-X0)*(32.72383-X0)) END FROM table_name a where value > 0 ORDER BY VALUE
The problem I'm having is in the where clause. Because the value is not a column name sql server is giving me an error. I need to evaluate the value returned. How can I get around this problem? Any sort of advise will help! Thank you in advance!
proj_name res_name yr SumOfHours x Mike 2007 1 x Mike 2008 2 x Mike 2008 3
Here is table dbo._LastWeekTotalHours
proj_name res_name yr td_hours Week x Mike 2007 1 1 x Mike 2008 2 8 x Mike 2008 3 9
now here is what the output should look like
Lets assume the user well enter 8 for week number
proj_name res_name totalHours LastWeekHours x Mike 6 2 Y AJ .. €¦.
I am trying to write a select statement for that:
Here is what I have:
SELECT a.proj_name, a.res_name,a.TotalHours,g.TotalHours as LastWeekhours FROM
Line 1( SELECT r.proj_name,r.res_name, SUM(r.td_hours) AS TotalHours FROM line 2 dbo._LastWeekTotalHours as r, _vw_Employee as e Line 3 WHERE e.RES_NAME = r.res_name and r.Week = 5 and r.yr = 2008 Line 4 GROUP BY r.proj_name, r.res_name ) as g
Line 5(SELECT r.proj_name, r.res_name, r.TotalHours Line 6 FROM Line 7 (SELECT proj_name, res_name, SUM(td_hours) AS TotalHours FROM line 8 dbo._LastWeekTotalHours line 9 GROUP BY proj_name, res_name ) r , line 10 (SELECT proj_name, res_name, SUM(SumOfHours) AS TotalHours FROM dbo.vw_TimebyProjectAll line13 GROUP BY proj_name, res_name )w )a
if i excute line 1 - 4 i will get the last week hour correctly if i excute line 5 - 13 i will get the totalhours correctly
my problem i can't put these two pieces work togather. feel free to re-write the select statment thanks
I have a stored procedure in an Informix-database that returns a string. Its used in a SELECT statement like this. SELECT t1.id, t2.id, sp_name(t1.id, t2.id) FROM table1 t1, table2 t2
I want to write it in SQLserver. I have tried this syntax but get error. Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 't'.
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are:
Field02
----------------------
22810 8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760 22810
Is there any good suggestion? Thanks in advance for any help, Aldo.
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E
can any one help me in this? please give me a sample query.
Thank you for your help but when I put the NOT IN like you suggested I am still getting the error of:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Here is the code again:
--If a request is not equal to the request from Jason's table then populate the table. if (select REQUEST from TEAM3B_PULL_TOTAL_TST) Not In (select REQUEST from REQUEST_BUS_REQ_TST) begin Insert into REQUEST_BUS_REQ_TST (b.request,b.business_req_id,b.bus_test,b.test_cas e_id,b.test_case_source,b.test_case_descr) select a.request,a.business_req_id,a.bus_test,a.test_case _id,a.test_case_source,a.test_case as test_case_description from TEAM3B_PULL_TOTAL_TST as A JOIN REQUEST_BUS_REQ_TST AS B ON A.REQUEST = B.REQUEST Order by b.REQUEST,b.BUSINESS_REQ_ID,b.TEST_CASE_ID end
Hi,I have a table that lists all attempts at logging in to a server.Columns are:userid, firstname, lastname, login_date, login_timeIf a user attempted to login 5 times, then I want to return the row(with all columns)......BUT I only want to return the row with the 5th time attempted.something like this:select * from mytablejoinselect userid, login_date from mytablegroup by userid, login_date having count(*) > 4BUT, how do I get just the 5th time attempted??Thanks!Skiz
I have 3 tables, with this relation: tblChats.WebsiteID = tblWebsite.ID tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
Hi, I am trying to insert the Source name and clientId to a table called clientSource...
The User will send in some Dbf File.... So in a particular file called PlanDbf.. I have the following fields PlanNumber, Name, SRC1Name, SRC2Name, SRC3Name.... SRC20Name 170234 Constructions Employee Deferral Employer Discretionary Employer Matching....
And in another table called SourceDBF i have the following fields with data PlanNumber PARTID SOURCE_NUM etc... 170234 123456789 1 170234 123456789 3 170234 451231334 1 So how do i match the Source_NUM with SRCnames when i insert it into the table.. INSERT INTO Statement..ClientSource(@ClientId, SourceName)SELECT s.SOURCE_NUM FROM SourceDBF .. but i am stuck.. any help will be appreciated. Regards Karen
Hi.. I have inserted couple of data in a particular table which is as follows.. Portfolio Table PortId PlanId PortfolioName PorfolioDescription ClientPortolioId
771 17838 BALPORT NULL NULL
772 17838 HIGHGROW NULL NULL
773 17838 MODGROW NULL NULLMy FundDBF is as follows RowNumber FUND_ID f.ASSETDESC Import
20 BALPORT
Balanced True
21 MODGROW
Moderate Growth True
22 HIGHGROW
High Growth True and this is my Update statement UPDATE Statements..PlanPortfolio SET PlanId = pm.PlanId, PortfolioName = pm.FUND_ID, PortfolioDescription = pm.ASSETDESC FROM Statements..PlanPortfolio p Join ( SELECT DISTINCT p.PlanId, pd.FUND_ID, f.ASSETDESC ---pd.FUND_ID FROM PartDBF pd INNER JOIN Statements..ClientPlan p on pd.PLAN_NUM = p.ClientPlanId INNER JOIN FundDBF f on pd.FUND_ID = f.FUND_ID WHERE pd.Import = 1 AND NOT ( pd.FUND_ID IS NULL OR Len(pd.FUND_ID) = 0 OR pd.FUND_ID NOT IN ( SELECT PortfolioName FROM Statements..PlanPortfolio pp Where pp.PlanId = p.PlanId ) ) ) pm on p.PlanId = pm.PlanId
I am trying to put the above table with f,AssetDesc in the PorfolioDescription field.. Any help will be appreciated.. Regards Karen
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
FROM Statements..ParticipantFundBalances pfb JOIN ( Select cp.PlanId, p.ParticipantId, @PeriodId Period, CASE WHEN a.FUND_ID = 'LOAN' Then 0 ELSEf.FundId END FundId, a.ACT_ID1, a.TOT_ACT1, a.ACT_ID2, a.TOT_ACT2, a.ACT_ID3, a.TOT_ACT3, a.ACT_ID4, a.TOT_ACT4, a.ACT_ID5, a.TOT_ACT5, a.ACT_ID6, a.TOT_ACT6, a.ACT_ID7, a.TOT_ACT7, a.ACT_ID8, a.TOT_ACT8, a.ACT_ID9, a.TOT_ACT9, a.ACT_ID10, a.TOT_ACT10, a.ACT_ID11, a.TOT_ACT11, a.ACT_ID12, a.TOT_ACT12, a.ACT_ID13, a.TOT_ACT13, a.ACT_ID14, a.TOT_ACT14, a.ACT_ID15, a.TOT_ACT15, a.ACT_ID16, a.TOT_ACT16, a.ACT_ID17, a.TOT_ACT17, a.ACT_ID18, a.TOT_ACT18, /*a.ACT_ID19, a.TOT_ACT19, a.ACT_ID20, a.TOT_ACT20, */ a.UNIT_OP, a.PRICE_OP, a.UNIT_CL, a.PRICE_CL, Cast(Rtrim(i.ALLOC_PER1) as decimal) as ALLOC_PER1 FROM ASDBF a -- Derive the unique PlanId from the Statements ClientPlan table INNER JOIN Statements..ClientPlan cp ON a.PLAN_NUM = cp.ClientPlanId AND cp.ClientId = @ClientId -- Derive the unique ParticipantId from the Statements Participant table INNER JOIN Statements..Participant p ON a.PART_ID = p.PartId--Derive the unique FundID from the Statements Fund Table...Left Outer JOIN Statements..Fund f ONa.FUND_ID = f.Cusip OR a.FUND_ID = f.Ticker OR a.FUND_ID = f.ClientFundId -- get the allocation percent from the INVSRC LEFT Outer JOIN INVSRC i ONa.FUND_ID = i.INV_ID AND a.PLAN_NUM = i.Plan_Number AND a.PART_ID = i.PART_ID
WHERE a.Import = 1 )a ON pfb.PlanId = a.PlanId AND pfb.ParticipantId = a.ParticipantId AND pfb.PeriodId = PeriodId AND pfb.FundId = a.FundId
While i insert data in my table i am checking if there are any loans in the ASDBF table and if there i am inserting a 0 in the particular i am trying to up date the with in 3 different plans in the same table..
I have coded an alter statement for adding a column to a temp table inside an sp,but it skips the alter statement while executing the sp! This happens even if I run the same code on query analyzer too. If I use 'GO' statement before the alter command,then it works fine on Query Analyzer.But, I can't use 'GO' in an sp. I am using the sa account. Any ideas on how to fix this?? Thanks. Di.
Hello How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement. <% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>" ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)"> <SelectParameters> <asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" /> </SelectParameters> </asp:SqlDataSource>any help would be appreciated
Hi, I have a query as follows:select transactionDate, (select count(transID) from ......)I am having a problem which is that dates are not shown uniquely.For example,oct 1, 2005 00:12:00 - 12oct 1, 2005 01:30:00 - 12oct 1, 2005 01:13:00 - 12I want only to have:oct 1, 2005 01:13:00 - 12The select is working on time and not on a date, can you help me please?thank you.
hey friends! pleaze help, i am using sql server 2000.i have tried to fix my problem for more than five weeks just to solve for one problem and just still now it is unsolved, opps my due date is almost approaching, i don't know what to do more than what i did, i have search through the net, but still i did not get the correct answer to my problem, friends please just forward your answer to me, it may be best answer to my question . create table mytable( english varchar(120), tigrigna Nvarchar(120)) insert into mytable values('peace',N'sälam') insert into mytable values('kiss',N'samä ') insert into mytable values('to kiss each other',N'täsasamä ') then select * from mytable where english='peace'; this works fine but select * from mytable where tigrigna=N'sälam'; this doesnot work; select * from mytable where tigrigna='sälam'; this also doesnot work, so what should i do to select this unicode select statement?
tigrigna is one of the spoken language in east africa(ethiopia). Hopefully, i have joined this forum today, and just looking for your reply
Code Snippet use master select top 25 percent id from ( select top 112 * from syscolumns ) d order by id desc
Script below returns 57 rows, must return 56!
Code Snippet use master select top 50 percent id from ( select top 112 * from syscolumns ) d order by id desc
Reproducable on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
and
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Could anyone from MS SQL server team clarify what's happened and WHAT TO DO?
I am using three tables in this query, one is events_detail, one is events_summary, the third if gifts. The original select statement counted the number of ids (event_details.id_number) that appear per event_name (event_summary.event_name).
Now, I would like to add in another column that counts the number of IDs that gave a gift who attended an event that were also listed in the event_ details table. So far I have come up with the following. My main issue is linking the subquery properly back to the main query. how to count in the sub-query and have the result placed within the groups results in the main query.
SELECT es.event_name, es.event_id, COUNT(ed.id_number) Number_Attendees, ( SELECT COUNT(gifts.donor_id) AS Count2 FROM gifts WHERE gifts.donor_id = ed.id_number ) subquery2
Hi there friend...I'm in a midle of a program(vb.net). and i've got this problem, I've got do compare two tables from two diferent sql servers. Is this possible??? How can I do this???