SELECT Average Values FROM Another Table
So I have this table called "listings"... there are 100 unique listings with an integer ID for each.
I have another table called "ratings"... in there are multiple entries that have a listing_id field and a rating field. The rating field is a value from 0-10.
I want to select ALL "listings" from the listings table... and then sort based on the average number that the multiple rating fields in the ratings table has for that listing.
I CAN NOT figure it out!! Any help would be greatly appreciated. Please respond if I have not explained this properly. Thanks in advance.
View Complete Forum Thread with Replies
Related Forum Messages:
Average Of Previous Values
How to write Stored Procedure to convert All seconds to Minutes AND finding average. Ex: My Table1: SYMBOL TIME PRICE EUR A0-FX 2008-05-09 11:37:31.203 1.54035 EUR A0-FX 2008-05-09 11:37:30.030 1.54034 EUR A0-FX 2008-05-09 11:37:28.860 1.54033 EUR A0-FX 2008-05-09 11:37:41.673 1.54032 EUR A0-FX 2008-05-09 11:37:59.720 1.54031 EUR A0-FX 2008-05-09 11:38:09.000 1.54033 EUR A0-FX 2008-05-09 11:38:35.877 1.54032 EUR A0-FX 2008-05-09 11:38:59.767 1.54041 OutPut: SYMBOL TIME PRICE EUR A0-FX 11:37 1.54031 EUR A0-FX 11:38 1.54041 I know this how to write .. ;WITH cte AS ( SELECT SYMBOL, [Time], Price, ROW_NUMBER() OVER(PARTITION BY CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) ORDER BY CAST(Time AS DATETIME) ASC) AS rn_1, ROW_NUMBER() OVER(PARTITION BY CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) ORDER BY CAST(Time AS DATETIME) DESC) AS rn_2 FROM Table1 WHERE SYMBOL='EUR A0-FX' ) SELECT SYMBOL='EUR A0-FX',CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) AS [Time],MAX(CASE WHEN rn_2 = 1 THEN Price ELSE NULL END) AS [Close] FROM cte GROUP BY CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) ORDER BY CAST(CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) AS DATETIME); But I want to add some additional code in my procedure like AVERAGE of Previous 5 Price Values. Ex: SYMBOL TIME PRICE EUR A0-FX 11:37 1.54031 ß1 EUR A0-FX 11:38 1.54041 ß2 EUR A0-FX 11:39 1.54021 ß3 EUR A0-FX 11:40 1.54081 ß4 EUR A0-FX 11:41 1.54071 ß5 Previous 5 Average PRICE Values. (1.54061) EUR A0-FX 11:42 1.54091 ß6 EUR A0-FX 11:43 1.54021 ß7 EUR A0-FX 11:44 1.54081 ß8 My Final Out Put: SYMBOL TIME PRICE AVERAGE EUR A0-FX 11:42 1.54091 ß6 (1.54061) EUR A0-FX 11:43 1.54021 ß7 (1.54091) EUR A0-FX 11:44 1.54081 ß8 (1.54071) At 11.42 time average is 1-5 price values At 11.43 time average is 2-6 price values At 11.44 time average is 3-7 price values
View Replies !
Average Of Privious 10 Values..
Hi i am traying to convert privious 10 values. How to write a query to convert privious 10 Values. My table Columns like this... SNO PRICE AVG I have 10 values in my table, in the 11 th row i want to diaplay avg of 10. i.e average of 1-10 values. Simlarly sno(2-11) of average and sno(3-12) of average.. .e.t.c Any one please help me on this problem
View Replies !
Select All Table Values By Default
Hi, I have something similar to the following: CREATE PROCEDURE dbo.MySproc @columnVal int = nullASBEGIN SELECT * FROM MyTable WHERE MyTable.column = @columnVal END If columnVal is not passed into the stored proc i want it to just select everything from 'MyTable' (without the WHERE clause)....how can I do this in as few lines of code possible?thanks
View Replies !
Select 3 Values From One Table To Return In One Rs
I need to access a table and return 3 values from it in the samerecordset - ie one iteration of the recordset will have 3 values fromthe same database, I have looked at sub queries but they dont seem tobe able to do what i want.I would be grateful for any guidanceS
View Replies !
Select 3 Values From One Table To Return In One Rs
I am having trouble accessing a table and am seeking out some adviseon how to do it.I have a select statement that joins several tables and everythingworks ok , however I want to access a further table to retrieve 3 rsvalues, so basically I want to retrieve 3 values from the same table -identifying each by a value in that table.Any help or pointer would be very welcome.Simon ChristiestrSearch is a dynamically created search stringCopy of select statement belowsql = "SELECT Master.Master_Key,Master.First_App,Original_Info_D ate,Last_Info_Date,Area.Area_NameAS Area_Name, State_Cntry.State_Cntry_Name, Company.Oper_Name1 ASOper_Name1, Company.Oper_Name2 AS Oper_Name2, " _& " Company.Oper_Name3 AS Oper_Name3, Location.City AS City,Projects.Project_Name AS Project_Name, " _& " Expansion.Expansion_Code AS Expansion_Code,Master.Cap_Size AS cap_size, Cap.Cap_Unit AS Cap_Unit, Master.Cost ASCost, " _& " Master.Status AS Status, Master.Estimated_Year_CompletedAS Estimated_Year_Completed " _& " FROM Master INNER JOIN " _& " Area ON Master.Area_Key = Area.Area_Key INNER JOIN " _& " Company ON Master.Company_Key = Company.Company_Key INNERJOIN " _& " Location ON Master.Location_Key = Location.Location_KeyINNER JOIN " _& " Expansion ON Master.Expansion_Key =Expansion.Expansion_Key INNER JOIN " _& " Cap ON Master.Cap_Key = Cap.Cap_Key INNER JOIN " _& " Projects ON Master.Project_Key = Projects.Project_KeyINNER JOIN " _& " State_Cntry ON Location.State_Cntry_Key =State_Cntry.State_Cntry_Key " _& " WHERE " & strSearch _& " ORDER BY Area.Area_Key, State_Cntry_Name,Oper_Name1,City"The table I need to access is called LECLEC_Key (PK)Master_Key - Database Master KeyCompany_Key - matches the companyLEC_Type_Key - 3 types per company (values L,E or C)Each Master Key will have 3 values in the table, one each of L,E and CHope this makes some sort of senseThanks in advanceSimon Christie
View Replies !
Select 3 Values From One Table To Return In One Rs
strSearch is a dynamically created search stringCopy of select statement belowsql = "SELECTMaster.Master_Key,Master.First_App,Original_Info_D ate,Last_Info_Date,Area.Area_NameAS Area_Name, State_Cntry.State_Cntry_Name, Company.Oper_Name1 ASOper_Name1, Company.Oper_Name2 AS Oper_Name2, " _& " Company.Oper_Name3 AS Oper_Name3, Location.City AS City,Projects.Project_Name AS Project_Name, " _& " Expansion.Expansion_Code AS Expansion_Code,Master.Cap_Size AS cap_size, Cap.Cap_Unit AS Cap_Unit, Master.Cost ASCost, " _& " Master.Status AS Status, Master.Estimated_Year_CompletedAS Estimated_Year_Completed " _& " FROM Master INNER JOIN " _& " Area ON Master.Area_Key = Area.Area_Key INNER JOIN " _& " Company ON Master.Company_Key = Company.Company_Key INNERJOIN " _& " Location ON Master.Location_Key = Location.Location_KeyINNER JOIN " _& " Expansion ON Master.Expansion_Key =Expansion.Expansion_Key INNER JOIN " _& " Cap ON Master.Cap_Key = Cap.Cap_Key INNER JOIN " _& " Projects ON Master.Project_Key = Projects.Project_KeyINNER JOIN " _& " State_Cntry ON Location.State_Cntry_Key =State_Cntry.State_Cntry_Key " _& " WHERE " & strSearch _& " ORDER BY Area.Area_Key, State_Cntry_Name,Oper_Name1,City"The table I need to access is called LECLEC_Key (PK)Master_Key - Database Master KeyCompany_Key - matches the companyLEC_Type_Key - 3 types per company (values L,E or C)Each Master Key will have 3 values in the table, one each of L,E and CHope this makes some sort of senseThanks in advanceSimon Christie
View Replies !
How To Select Non English Values From A Table?
CREATE TABLE product ( product_id integer , translated_name NVARCHAR2(50) ); insert into product values(1,N'kenenisa') insert into product values(2,N'Ethiopia') Note that i have used N becuase it is unicode data then select * from product; this works fine. select * from product where product_id=1; this also works fine, but select * from product where translated_name=N'kenenisa'; this doesnot work correctly, so how can i fix this problem ?
View Replies !
SELECT INSERT INTO Other Table With Extra Values
Hi, I've got a table with trialsubscriptions. When someone orders a trialsubscription he has to fill in a form. With the values in the form I like to fill a database with some extra fields from the trialsubscription table. I get those extra fields with a datareader. But when I insert I can't use the same datareader (its an insert), I can't make two datareaders because I have to close the first one etc. Does someone had the same problem and has some example code (or make some :-)) Some keywords are also possible! Thanks! Roel
View Replies !
Select Distinct Column Data With Other Values From The Table
I have a table 'wRelated' with the following columns [related_id] [int] [channel_id] [int] [mui] [varchar] [price_group_id] [type_id] [int] [related_mui] [varchar] (100) [date_started] [smalldatetime] [date_ended] [smalldatetime] [date_entered] [datetime] [deleted] [tinyint], [rank] [int] data in column [mui] is repeated as the table has more than one entries for the same [mui], The requirement is to select the distinct[mui] but value in all the other columns for the same mui should be select in the next row with null for the same [mui] The recordset expected should be something like this. [mui],[related_mui],[price_group_id],[date_entered],[date_ended] m123,rm345,'pr','12-10-2003',12-12-2004' null,rm789,'ar','12-1-2003',26-2-2004' null,rm999,'xy','14-12-2002',12-2-2004' m777,rm889,'pr','12-12-2004',12-12-2004' null,rm785,'yy','1-10-2002',12-12-2004' m888,rm345,'pr','2-8-2003',12-12-2004' null,rm345,'tt','30-7-2002',12-12-2004' I have tried Unions and temporary table inserts.
View Replies !
Select Duplicate Rows And Average Them.
I have a table with four columns: id, value1, value2, and name. I need to select all duplicate rows with the same name in the name field. Once selected I need to average the value1 and value2 rows with each other. Then I would like to delete the duplicate rows and replace with the new averaged values. Should I first move the duplicate rows to a temp table and then average? Any help would be appreciated.
View Replies !
Moving Average Using Select Statement Or Cursor Based?
ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE) 1 01/01/2007 PURCHASE 10 1000 2 01/01/2007 PURCHAES 5 1100 3 01/01/2007 SALES -5 *TobeCalculated 4 02/01/2007 Purchase 20 9000 5 02/01/2007 SALES -10 *TobeCalculated 5 02/01/2007 purchase 50 8000 6 03/01/2007 Sales -10 *TobeCalculate 7 01/01/2007 Purchase 20 12000 I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here) In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly. When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly. The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated. Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time. Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?
View Replies !
User-Defined Functions To Calculate Average And Relative Percent Difference(RPD):How To Designate The Variables Of Average/RPD
Hi all, This is my first time to do T-SQL User-Defined Functions programming. I just learned some basic DDL and DML statements. But I do not know how to designate the variables of the User-Defined Function. I have 3 dbo tables in SQL Server Management Studio Express (SSMSE): dbo.Projects: ProjectID ProjectName LabName 1 Blueriver ALSI 2 Greentree GPL 3 Redrock STL-NJ NULL NULL NULL dbo.Samples: SampleID SampleName Matrix SampleType Chemical ProjectID 1 Blueriver01 Water Primary VOCs 1 2 Blueriver02 Water Duplicate VOCs 1 3 Blueriverr03 Water QA VOCs 1 4 Greentree11 Soil Primary VOCs 2 5 Greentree12 Soil Duplicate VOCs 2 6 Greentree13 Soil QA VOCs 2 NULL NULL NULL NULL NULL NULL dbo.LabTests: AnalyteID AnalyteName Result Unit SampleID 1 Acetone 120.80 ug/L 1 2 Benzene 25.60 ug/L 1 3 Trichloroethene 13.00 ug/L 1 4 Xylenes 0.00 ug/L 1 5 Acetone 90.70 ug/L 2 6 Benzene 31.40 ug/L 2 7 Trichloroethene 19.20 ug/L 2 8 Xylenes 2.00 ug/L 2 9 Acetone 140.30 ug/L 3 10 Benzene 21.50 ug/L 3 11 Trichloroethene 22.20 ug/L 3 12 Xylenes 0.00 ug/L 3 13 Acetone 222.10 ug/Kg 4 14 Benzene 10.30 ug/Kg 4 15 Trichloroethene 30.20 ug/Kg 4 16 Xylenes 50.70 ug/Kg 4 17 Acetone 211.90 ug/Kg 5 18 Benzene 16.40 ug/Kg 5 19 Trichloroethene 34.70 ug/Kg 5 20 Xylenes 60.00 ug/Kg 5 21 Acetone 220.30 ug/Kg 6 22 Benzene 13.20 ug/Kg 6 23 Trichloroethene 32.00 ug/Kg 6 24 Xylenes 55.50 ug/Kg 6 NULL NULL NULL NULL NULL The Average of chemical/analyte in 2 samples is defined as: Average = Abs(the result of primary sample + the result of duplicate sample)/2. Average = Abs(the result of primary sample + the result of QA sample)/2. Average = Abs(the result of duplicate sample + the result of QA sample)/2. The RPD of chemical/analyte in 2 samples is defined as: RPD = Abs(the result of primary sample - the result of duplicate sample)/ Average RPD = Abs(the result of primary sample - the result of QA sample)/ Average RPD = Abs(the result of Duplicate sample - the result of QA sample)/ Average I want to calculate the average and RPD of each chemical/analyte for the following 3 pairs of the related samples: (i) Between the primary and duplucate samples (ii) Between the primary and QA samples (iii) Between the duplucate and QA samples. In the Table "dbo.Projects", ProjectID is the primary key. In the Table "dbo.Samples", SampleID is the primary key and ProjectID is the foreign key. In the Table "dbo.LabTests", AnalyteID is the primary key and SampleID is the foreign key. I do not know how to designate the variable of each result of analyte in a sample and use it to calucalte the Average and RPD for each pair (i.e. for (i), (ii), or (iii)). For example: I do not know how to designate the following 2 results: dbo.LabTests: AnalyteID AnalyteName Result Unit SampleID 1 Acetone 120.80 ug/L 1 5 Acetone 90.70 ug/L 2 Please help and advise me how to designate the 2 results in T-SQL User Defined Functions to calculate the Average and RPD. Thanks in advance, Scott Chang
View Replies !
Latches - Average Waits, Average Duration?
I have been monitoring the average number of latch waits and the average duration of each latch wait on my primary SQL box. On average, I see around 30 latch waits per second with an average duration of under 1 second. What type of average are you seeing on your production machines? What is a normal average? I have tried researching for suggested values on these two counters, but I haven't turned up any information. Thanks, MV
View Replies !
Getting An Average From A Table
I have two tables, table a holds all the votes by users of each element in table b. I was wondering, how do I get the average of all those votes in table a that relate to that each instance of a element in table b. For example table b has two elements created by a certain user that has been voted 5 times each by users with scores like 2, 5, 4, 2 , 2 for both of them. I just need to get the average of those numbers that pertain to those elements in table b. Thanks for any help.
View Replies !
Average Size Of A Row In A Table
How do I find the average size of a row in a table? I need to calculate a row size in a number of tables, then sum those to find the average size of one record ( a hotel guest in this case), which includes entries in a dozen tables. Thanks
View Replies !
Average Row Size For A Table
I want to find out the average rwo size of a table in sql server 2000. How can i achive this requirment? Please let me know how can i do this? Thanks & regards, Dishant Sharma
View Replies !
Average Multiple Columns In A Table
Is this possible with the following scenario? I have 5 groups and 1 details group. The details group is the lowest level of granularity. I have two columns that have averages in them. I have a third column called GPA. Is it possible to average the averages in columns 1 & 2 and show that product in the third column (GPA)? Column 1 Avg Column 2 Avg GPA Group 1 Group 2 Group 3 Group 4 Group 5 Details 1 So it looks something like the above
View Replies !
Need An Average By Year Of An Average By Month
I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged... I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25. The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement. ?confused? Here's the original query. accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): ' SELECT name, country_cd from weather_station where platformId=&&platformId; SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS FROM daily WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime GROUP BY to_char(datetime,'MM') ORDER BY to_char(datetime,'MM'); with a result of: NAME_________________CO -------------------- -- OFFUTT AFB___________US MO______MAX_T _____AVG_T__MIN_TEMTP_TOTAL_YEARS -- ---------- ---------- ---------- ----------- 01_________21______-5.31________-30__________60 02_________26______-2.19______-28.3__________61 03_______31.1_______3.61______-26.1__________60 04_______35.6______11.07______-12.2__________60 05_______37.2_______17.2_______-3.3__________60 06_______41.1______22.44__________5__________60 07_______43.3______24.92________7.2__________60 08_______40.6______23.71________5.6__________60 09_________40______18.84_______-2.2__________59 10_______34.4_______12.5_______-8.9__________59 11_________29_______4.13______-23.9__________60 12_________21______-2.52______-28.3__________60
View Replies !
Returning Average Of Multiple Rows In A Table Join
Hi,I'm am looking for a little help. I need to create a SQL view whichjoins a few tables, and I need to return an average for a particularcolumn where a second duplicate ID exists...Heres an example of how the results could be returned...ID | Name | Order No. | Value---+------+-----------+---------5 | test | 1234 | 35 | test2| 1234 | 45 | test3| 1234 | 35 | void | 1235 | 55 | void2| 1235 | 65 | void3| 1235 | 55 | void4| 1235 | 7ID is my main join which joins the tablesName is a unique nameOrder No is the same for the different names, I only need to return onerow with this order no, and the first name (the rest are irrelevant)Value is the field which I wish to return as an average of all 3, 4 orhowever many rows is returned and share the same order no. This iswhere I get totally lost as I am pretty new to SQL. Can anyone provideany help on how I would go about limiting this query to the uniqueorder no's and returning the average of the value field, and I can takeit from there with my own tables.Thanks for your helpstr8
View Replies !
Declaring A Table Variable Within A Select Table Joined To Other Select Tables In Query
Hello, I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post. I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table. This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off. So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining? Thanks in advance, Andy Select * from ( declare @years table (years int); insert into @years select CASE WHEN month(getdate()) in (1) THEN year(getdate())-1 WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate()) END select u.fullname , sum(tx.Dm_Time) LastMonthBillhours , sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage from Dm_TimeEntry tx join systemuserbase u on (tx.owninguser = u.systemuserid) where Month(tx.Dm_Date) = Month(getdate())-1 and year(dm_date) = (select years from @years) and tx.dm_billable = 1 group by u.fullname ) as A left outer join (select u.FullName , sum(tx.Dm_Time) Billhours , ((sum(tx.Dm_Time)) / ((day(getdate()) * ((5.0)/(7.0))) * 8)) perc from Dm_TimeEntry tx join systemuserbase u on (tx.owninguser = u.systemuserid) where tx.Dm_Billable = '1' and month(tx.Dm_Date) = month(GetDate()) and year(tx.Dm_Date) = year(GetDate()) group by u.fullname) as B on A.Fullname = B.Fullname Left Outer Join ( select u.fullname , sum(tx.Dm_Time) TwomosagoBillhours , sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage from Dm_TimeEntry tx join systemuserbase u on (tx.owninguser = u.systemuserid) where Month(tx.Dm_Date) = Month(getdate())-2 group by u.fullname ) as C on A.Fullname = C.Fullname
View Replies !
NULL Values In A SELECT In Another SELECT
Hi,I have a query like this :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...The problem is that I don't want to return the results where x3 isNULL.Writing :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ... AND x3 IS NOT NULLdoesn't work.The only solution I found is to write :SELECT * FROM((SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...) AS R1)WHERE R1.x3 IS NOT NULLIs there a better solution? Can I use an EXISTS clause somewhere totest if x3 is null without having to have a 3rd SELECT statement?There's probably a very simple solution to do this, but I didn't findit.Thanks
View Replies !
Checking To See If Values Are In A Table Or Not -- If Not Then Inserting The Values.
I'm trying to checking my production table table_a against a working table table_b (which i'm downlading data to)Here are the collumns i have in table_a and table_bDescription | FundID (this is not my PK) | Money I'm running an update if there is already vaule in the money collumn. I check to see if table_a matches table_b...if not i update table a with table b's value where FundID match up.What i'm having trouble on is if there is no record in table_a but there is a record in table_b. How can I insert that record into table_a? I would like to do all of this (the update and insert statement in one stored proc. if possible. )If anyone has this answer please let me know.Thanks,RB
View Replies !
'Insert Into' For Multiple Values Given A Table Into Which The Values Need To Go
Please be easy on me...I haven't touched SQL for a year. Why given; Code Snippet USE [Patients] GO /****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Patients]( [PID] [int] IDENTITY(1,1) NOT NULL, [ID] [varchar](50) NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [DOB] [datetime] NULL, CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED ( [PID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF do I get Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ','. for the following; Code Snippet INSERT INTO Patients (ID, FirstName,LastName,DOB) VALUES ( '1234-12', 'Joe','Smith','3/1/1960'), ( '5432-30','Bob','Jones','3/1/1960'); Thank you, hazz
View Replies !
Add Values To A Table Based On Values In Another Table
When a record is inserted or updated records in Table1 I want a record to be inserted into table3 for each record ID that is in table2 if the table2.id does not already exist in table3. What is the best way to do this? Could this be done with a trigger? If it could how would I write such a trigger. I have never written one before and this sounds like a hand full for my first effort. Your help will be greatly appreciated. Thanks
View Replies !
Select Some Values From A Row And Insert On Another Row
I have a table wih multiple records and some has common values. For example, new child row need to inherit some of parent's data. Here is how I want to do: Select [parent data1], [parent data2] from [TABEL] where id = parent Insert INTO [TABLE] ([child id], [child data1], [child data2] ) VALUES [child id] [parent id] [parent id]........... I was thinking about using Selected and Inserting events but not sure how. Please help? Thanks,
View Replies !
Select 'many Values' From One Column
I'm building a textbox with search functionality towards SQL 2000. The textbox is to include various search phrases, like "phrase1 phrase2 phrase3". Sort of like search engines where all words are considered. Do keep in mind that it's SQL2000 and not 2005. I've got all the strings I want to perform the search against in one column but various values in this column. The only thing I've come up with is to create "WHERE columnName LIKE @phrase1 OR columnName LIKE @phrase2" where I inject % as apropiate and chop up the string, but I'm hoping this is not the solution because it doesn't seem really clean (with 10 search words) and today I'm not having a 'bad-hack' day so I want it better. Whats is the best way of doing this? Cheers! /Eskil
View Replies !
How To Select From A List Of Values
Hi all. I need to create a select query in my program that will select from a list of values that are stored in a dataset. Let see this example: selectcmd = "Select * from mytable where myfile =" ??????? cmd = New SqlCommand(selectCmd, da.SelectCommand.Connection) The values I need to put on ????? are stored in a dataset. For example if the dataset is populated with the following values: A B C D E I would like to build a query like that: selectcmd = "Select * from mytable where myfile = ‘A’ or ‘B’ or ‘C’ or ‘D’ or ‘E’ “ How can I do that? Thanks.jsn
View Replies !
Query To ADD/SELECT Values From An SQL
Hello. I need a query that will RETRIEVE a value from a database if it is present, but if the data isn't present, then the data will be INSERTed into the table. Either way, I need the row returned at the end of the query. I can do SELECT queries, but I don't have a clue as to how to proceed with branching statements. For example: User runs a query for "Canada". Canada exists in the database, so the database returns Canada along with its ID. Next user runs a query for "Chile". Chile isn't in the database so a record is created and the ID (an IDENTITY field) is returned. Does anyone know how I may accomplish this?
View Replies !
Select Minimum Between Two Values
Hello,I need to select the minimum between the result of a function and anumber, but i can't find a smart way. By now I'm doing like thefollowing, but I think is very expensive because it evaluates thefunction twice:select case when (myfunction())<100 then (myfunction()) else 100 endAny idea is appreciated.Thank youRegards--elimina carraro per rispondere via email
View Replies !
Select Row Values To Be Column Name
I am new to sql query and have a question.I would like to display row column values as column names.questions table consisted ofquestion_id, group_id, questionresponse table consisted ofresponse_id, group_id, question_id, answerI would like to display the answer responses under the columns ofquestions : such asresponse_id, question(1), question(2), question(3)...It will be greatly appreciated if someone help me to get this down.Jason
View Replies !
BCP Help -Null Values In Select
I am using a bcp command to cretae a text file. set @bcpcommand = 'bcp "select med + replicate('' '',10-datalength(med)),ml+ replicate('' '',20-datalength(ml)),iname + replicate('' '',10-datalength(iname)) from copy_tbl" queryout "'+ @filename + '" -U -P -c' exec master..xp_cmdshell @bcpCommand Some of the values in the select statement are Null values and are getting skipped in the text file .My output looks like At A02 At1E AtE A03 At2E c100 c1230 I want them to allign but the third row has a null in the middle so it skips it and put the third value in the seconds place
View Replies !
Max Values In A Select Statement
I have a table which has 4 fields, patientid,testdate,testtype,results. I want to select the most recent testdate by a patient regardless of the results, or the testtype. I do however need those fields for my query. I tried the below, but I get more than 1 record if the person has had two different types of tests. For instance if patient 100 has the following 2 records I just want the most recent patientid testdate testype results 100 01/02/2002 TBI ASYMP 100 02/02/2001 PPD 00000 select max(testdate)as testdate,testtype, other_id_number from vw_cms_tb_lasttest What am missing? Thanks group by other_id_number,testtype order by other_id_number,testtype
View Replies !
How To Select Not Null Values
hi, i have 5 columns in my table name age phone sex salary dani 21 1233 m see this output, there salary is null. actually i want to use * (ie) select * from emp, if i use this the following output has to come name age phone sex dani 21 1233 m becos there is no value in salary column. so please tall me how to do this,please give me query to do this.
View Replies !
Select Does Not Return Values
I am new to SQL and I am trouble-shooting a problem with a home-grown app someone else wrote using PERL. It has a web interface with names of boards. I found the program where i need to add the board names into and did that. The new board names show up in the drop-down list in the Web page for the app. Alerts are sent to the new board names and show up on the new boards. Users are granted access to the new boards and they can clear items off the new boards. Yet when i try to use a report function for the new boards i added, nothing is returned. I even ran a simple SQl select statement specifying the new board names and nothing is returned. The older board names, some of which i added myself, return values. I don't know what is going on. Any help is appreciated.
View Replies !
Getting Different Values From Select Statement
I am running a select statement against a function that is giving me different values depending on how it is called. When I run it through sql server management studio, I get the proper results, the default value column has the parameters default value. When I call it through my web app, I get this in the default value column: *** ERROR OCCURRED level 2 (this is not a default value) *** Why would the same sql statement get different results? Here is the call: select * from dbo.f_GetSProcParameters('webservices_BENEFICIAL_USES_DM_SELECT') Here are the two functions: --------------------------------------------------- USE [si_training_db] GO /****** Object: UserDefinedFunction [dbo].[f_GetSProcParameters] Script Date: 06/13/2008 09:29:21 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO ALTER function [dbo].[f_GetSProcParameters](@StoredProcedureName VARCHAR(128) ) Returns @sProcParameters Table(ParmName VarChar(128), DefaultValue VarChar(128), HasDefault Bit, IsInput Bit) /* * DESCRIPTION : This function returns a table listing all the parameters of a stored *procedure and the default values of those parameters. * * RETURNS : table * * * ORIG AUTHOR : Josh Kinder * DATE WRITTEN : 3/14/2006 * * REVISIONS LOG * *ID/Date PC# Description *------- --- ---------------------------------------------------------- */ As Begin Declare @Count SmallInt, @Index SmallInt, @CurParm VarChar(128), @DefaultVal VarChar(128), @IsInput BIT /*----------------------------------------------------------------------------------------------------------------------------------*/ --EDIT CHECK -sProc only /*----------------------------------------------------------------------------------------------------------------------------------*/ -- Check that the proc name is valid If OBJECT_ID(@StoredProcedureName, 'P') Is Null Begin Goto ScriptErr End /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ Declare @ParmTable Table (Id SmallInt Identity Primary Key Clustered, ParmName VarChar(128)) Insert Into @ParmTable Select a.Name From SysColumns a Inner Join SysObjects b On b.Id = a.Id Where b.Name = @StoredProcedureName Select @Count = Count(Id), @Index = 1 From @ParmTable While (@Index<=@Count) Begin Select @CurParm = ParmName From @ParmTable Where Id = @Index Set @DefaultVal = dbo.f_GetsProcParamDefaultValue(@StoredProcedureName, @CurParm) SELECT@IsInput = CASE WHEN params.is_output = 1 THEN 0 ELSE 1 END FROMsys.procedures AS procs INNER JOINsys.all_parameters AS params ON params.object_id = procs.object_id LEFT JOINsys.types AS types ON types.system_type_id = params.system_type_id AND types.user_type_id = params.user_type_id WHEREprocs.is_ms_shipped = 0 AND params.name = @CurParm AND procs.name = @StoredProcedureName Insert Into @sProcParameters ( ParmName, DefaultValue, HasDefault, IsInput ) Values ( @CurParm, @DefaultVal, Case When @DefaultVal = 'NoDefaultExists' Then 0 Else 1 End, @IsInput ) Set @Index = @Index + 1 End ScriptErr: Return End --------------------------------------------------- USE [si_training_db] GO /****** Object: UserDefinedFunction [dbo].[f_GetsProcParamDefaultValue] Script Date: 06/13/2008 09:30:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER function [dbo].[f_GetsProcParamDefaultValue] ( @StoredProcedureName VarChar(128), @ParamName VarChar(128) ) ReturnsVarChar(128) /* * DESCRIPTION : This function returns a table listing all the parameters of a stored *procedure and the default values of those parameters. * The following copyright info is for the parsing algorithm to get the default value. I obtained the code from SQL Farms Solutions and their only stipulation for use is that the copyright info remain with the code. Although I customized it for us, it is still their algorithm. ================================================================================== Copyright © SQL Farms Solutions, www.sqlfarms.com. All rights reserved. This code may be used at no charge as long as this copyright notice is not removed. ================================================================================== * RETURNS : table * * * ORIG AUTHOR : Josh Kinder * DATE WRITTEN : 3/14/2006 * * REVISIONS LOG * *ID/Date PC# Description *------- --- ---------------------------------------------------------- */ As Begin Declare @minRow Int, @maxRow Int, @tmpInt Int, @tmpText VarChar(4000), @tmpCharPos1 Int, @tmpCharPos2 Int, @tmpCharPos3 Int, @ParameterDefault VarChar(128), @sProcTextVarChar(4000), @DelimiterChar(1), @Count SmallInt, @Index SmallInt, @CurTextVarChar(4000) Select @minRow = Null, @maxRow = Null, @Delimiter = char(13) Declare @ProcText Table (Id Int Identity(1, 1) Primary Key Clustered, ProcText VarChar(4000)) Insert Into @ProcText Select a.Text From SysComments a Inner Join SysObjects b On b.Id = a.Id Where b.Name = @StoredProcedureName Select @Count = Count(Id), @Index = 1 From @ProcText While (@Index<=@Count) Begin Select @CurText = Coalesce(@CurText,'') + ProcText From @ProcText Where Id = @Index Set @Index = @Index + 1 End /*----------------------------------------------------------------------------------------------------------------------------------*/ --EDIT CHECKS /*----------------------------------------------------------------------------------------------------------------------------------*/ -- Check that the parameter name is valid for the proc If Not Exists( Select 1 From INFORMATION_SCHEMA.PARAMETERS Where SPECIFIC_NAME = @StoredProcedureName And PARAMETER_NAME = @ParamName ) Begin Set @ParameterDefault = '*** ERROR OCCURRED level 1 (this is not a default value) ***' Goto ScriptErr End /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ --Get sProc into a workable temporary table /*----------------------------------------------------------------------------------------------------------------------------------*/ Declare @ProcContent Table (Idx Int Identity(1, 1) Primary Key Clustered, ProcText VarChar(4000)) Insert Into @ProcContent Select Value From dbo.f_Split(@CurText, @Delimiter, 1, 0) -- Make sure that some rows were returned successfully If @@ROWCOUNT = 0 Begin Set @ParameterDefault = '*** ERROR OCCURRED level 2 (this is not a default value) ***' Goto ScriptErr End /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ --Get location of parm and get ready to parse /*----------------------------------------------------------------------------------------------------------------------------------*/ -- Get the first line where the parameter is referenced in the proc code. -- (the LIKE here is a little complex since it is possible that multiple parameters -- will start with the same string. Most cases of interest are covered by the -- conditions listed below). Select @minRow = Min(Idx) From @ProcContent Where ProcText Like '%' + @ParamName + ' %' Or ProcText Like '%' + @ParamName + Char(9) + '%' Or ProcText Like '%' + @ParamName + Char(10) + '%' Or ProcText Like '%' + @ParamName + Char(13) + '%' Or ProcText Like '%' + @ParamName + '=%' Or ProcText Like '%' + @ParamName + '%=%' Or ProcText Like '%' + @ParamName + ',%' -- Check that the parameter is referenced in the code If @minRow Is Null Begin Set @ParameterDefault = '*** ERROR OCCURRED level 3 (this is not a default value) ***' Goto ScriptErr End -- Get the proc line where the word 'AS' is declared. 'AS' is required -- upon proc creation to complete the variable declaration. -- Note: This cover most cases of interest. There could be scenarios where -- additional condition should be applied. Select @maxRow = Min(Idx) From @ProcContent Where ProcText Like '% AS' Or ProcText Like '% AS ' Or ProcText Like '% AS' + Char(9) Or ProcText Like '% AS' + Char(10) Or ProcText Like '% AS' + Char(13) Or ProcText Like 'AS %' Or Upper(RTrim(LTrim(ProcText))) = Char(10) + 'AS' Or Upper(RTrim(LTrim(ProcText))) = 'AS' Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(10) Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(13) Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(13) + Char(10) Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(10) + Char(13) -- Check that the 'AS' string was found successfully If @maxRow Is Null Begin Set @ParameterDefault = '*** ERROR OCCURRED level 4 (this is not a default value) ***' Goto ScriptErr End /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ --Parse and get the default value /*----------------------------------------------------------------------------------------------------------------------------------*/ -- Get the first proc line of code where the parameter is referenced, for string processing, -- and append to it all proc rows until the 'AS' string Select @tmpText = LTrim(RTrim(Replace(Replace(ProcText, Char(10), ''), Char(13), ''))) + ' ' From @ProcContent Where Idx = @minRow While @minRow < @maxRow Begin Set @minRow = @minRow + 1 Select @tmpText = @tmpText + ' ' + LTrim(RTrim(Replace(Replace(ProcText, Char(10), ''), Char(13), ''))) + ' ' From @ProcContent Where Idx = @minRow End -- Find the position of the parameter name. Delete all text before that position. Set @tmpInt = Null Set @tmpInt = PatIndex('%' + @ParamName + '%', @tmpText) - 1 Set @tmpText = Right(@tmpText, Len(@tmpText) - @tmpInt) -- At this point we are nearly done: -- We check whether the character '=' comes before the 'AS' or ',' string -- If not- the parameter has no default value. -- If so, we continue to find the value of the default parameter Set @tmpCharPos1 = PatIndex('%=%', @tmpText) Set @tmpCharPos2 = PatIndex('%,%', @tmpText) Set @tmpCharPos3 = PatIndex('% AS %', @tmpText) If @tmpCharPos1 <= 0 Or (@tmpCharPos1 > @tmpCharPos2 And @tmpCharPos2 > 0) Or (@tmpCharPos1 > @tmpCharPos3 AND @tmpCharPos3 > 0) Begin -- The column does not have a default Set @ParameterDefault = 'NoDefaultExists' End Else Begin -- Column has a default and it is left to find it. -- First chop the string until the '=' character Set @tmpInt = NULL Set @tmpInt = PatIndex('%=%', @tmpText) - 1 Set @tmpText = LTrim(Right(@tmpText, Len(@tmpText) - @tmpInt)) -- Now, we p*** the remaining string until we get a ',' or a ' ' character Set @tmpCharPos1 = NULL Set @tmpCharPos2 = NULL Set @tmpCharPos1 = PatIndex('%,%', @tmpText) Set @tmpCharPos2 = PatIndex('% %', @tmpText) Set @tmpInt = NULL If @tmpCharPos2 > @tmpCharPos1 AND @tmpCharPos1 > 0 Set @tmpInt = @tmpCharPos1 - 1 Else Set @tmpInt = @tmpCharPos2 - 1 If @tmpInt <= 0 Begin Set @ParameterDefault = '*** ERROR OCCURRED level 5 (this is not a default value) ***' Goto ScriptErr End Set @ParameterDefault = SubString(@tmpText, 1, @tmpInt) -- If the parameter default is a string, then we will have an '' at each side of it. -- These last lines of code will get rid of the ''. If Len(@ParameterDefault) >= 1 If Right(@ParameterDefault, 1) = '''' Set @ParameterDefault = Left(@ParameterDefault, Len(@ParameterDefault) - 1) If Len(@ParameterDefault) >= 1 If Left(@ParameterDefault, 1) = '''' Set @ParameterDefault = Right(@ParameterDefault, Len(@ParameterDefault) - 1) End /*----------------------------------------------------------------------------------------------------------------------------------*/ /*----------------------------------------------------------------------------------------------------------------------------------*/ Goto ScriptExit ScriptErr: ScriptExit: Return @ParameterDefault End
View Replies !
Connecting To A Database And Select Values
Hello there i try to retrieve some data from my table but i cannot get it to get me the ItemID from the table here is the connection string it jumps right at the start of the loop strconnection = New SqlConnection("Data source = REPORT-SRVR; Initial Catalog=Cincinnati_Tan_Company;Persist Security Info=True;User ID=basic_user;Password=ctc_user;") Dim cmdstring As String = "select ItemID, Price FROM Office_Items WHERE Description ='" & item(INTINDEX) & "'" ' strconnection.Open()CMD = New SqlCommand(cmdstring, strconnection) mYReader = CMD.ExecuteReader() If mYReader.Read = True ThenWhile mYReader.Read ItemID = mYReader.Item("ItemID")Session("ItemID") = mYReader.Item("ItemID") Session.Add("ItemID", ItemID)price = mYReader.Item("price") Session("price") = mYReader.Item("price")Session.Add("price", price) End While End If mYReader.Close()
View Replies !
Updating Values During SELECT Using CTE And Row_Number()
Hi, I'm very new to stored procedures and I've been searching on google to find a way to custom page my results using SQL Server 2005. I'm trying to use Row_Number() and a CTE to keep things efficient and scaleable but I keep getting an error next to my UPDATE statement. I get the following error: "[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'UPDATE'." The sproc works without the UPDATE statement. Does anyone know where I need to put the UPDATE statement to update the "searched" field for each record selected? CREATE PROCEDURE [zk_update_request_england](@property_type tinyint,@market_status tinyint,@price int,@bedrooms tinyint,@search_location varchar(30),@search_district varchar(30),@PageSize int,@PageIndex int) AS BEGIN WITH SearchResults AS( UPDATE dbo.zk_request_england SET searched = searched + 1 WHERE property_type = @property_type AND market_status = @market_status AND bedrooms = @bedrooms AND search_location = @search_location AND search_district = @search_district AND min_price <= @price AND max_price >= @price SELECT user_id, min_price, max_price, property_description, searched, ROW_NUMBER() OVER (ORDER BY max_price DESC) AS RowNumber FROM dbo.zk_request_england WHERE property_type = @property_type AND market_status = @market_status AND bedrooms = @bedrooms AND search_location = @search_location AND search_district = @search_district AND min_price <= @price AND max_price >= @price ) SELECT user_id, min_price, max_price, property_description FROM SearchResults WHERE RowNumber BETWEEN (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize END Thanks in advance for any help.
View Replies !
Select Statement When Values Are Not Null
Hi. I have an sql table which contains a number and a name. I would like to create a select statement that will display these two fields in the format : "number | name", but if there is a null value in the number it will display only the name and vice versa. How can I do it ? Any help is appreciated.
View Replies !
Use Listbox Values To Do Select Statement
Hi, I have SQL database 2000 which has one table Sheet1, I retrieved the columns in the ListBox, then chosed some of them and moved it to ListBox2. The past scenario worked great, and I checked the moved values, it was succesfully moved, but when I tried to copy the values in ArrayList to do a select statement it didn't worked at all. public string str;protected void Button3_Click(object sender, EventArgs e) {ArrayList itemsSelected = new ArrayList(); string sep = ","; //string str;for (int i = 0; i < ListBox2.Items.Count; i++) {if (ListBox2.Items[i].Selected) { itemsSelected.Add(ListBox2.Items[i].Value); } int itemsSelCount = itemsSelected.Count; // integer variable which holds the count of the selected items. str = ListBox2.Items[i].Value + sep; Response.Write(str); } SqlConnection SqlCon = new SqlConnection("Data Source=AJ-166DCCD87;Initial Catalog=stat_rpt;Integrated Security=True;Pooling=False"); String SQL1 = "SELECT " + str + " from Sheet1"; SqlDataAdapter Adptr = new SqlDataAdapter(SQL1, SqlCon); SqlCommandBuilder CB = new SqlCommandBuilder(Adptr);DataTable Dt = new DataTable(); Adptr.Fill(Dt); //return Dt; GridView1.DataBind(); SqlCon.Close(); } I did some changes and the new error message is Incorrect syntax near the keyword 'from'. Thank you
View Replies !
Select Several Values Into A Single Variable
Can somebody please tell me whether the following syntax is supportedor whether it's a "feature" that will someday stop working. It works inboth SQL Server 2000 and 2005 at the moment.declare @var varchar(1000)set @var = ''select @var = @var + colx from some_table where col1 = some_valuecolx is a varchar or at least is cast to one as part of the selectstatement. If the where clause would normally return more than one row,all returned values for colx are concatenated into @var.I've not seen this syntax before but that doesn't make it wrong ;-)Malc.
View Replies !
SELECT Question, Grouping Values
What is the best way to build a SQL query to extract data from a table in this manner: COLA COLB COLC DATE 01 01 01 04/15/1988 01 01 01 11/23/1997 01 01 01 02/02/2005 01 02 01 6/14/1990 01 02 01 10/30/1999 01 02 02 4/24/2001 How would I select Row 3, 5, and 6 above (In other words, from each set where COL A B & C are equivalent, grab the most recent date?) Thanks!!!
View Replies !
Insert Proc With Both Select And Values
I'm trying to write a Stored Proc to Insert records into a table in SQL Server 2000. The fields in the records to be inserted are from another table and from Parameters. I can't seem to figure out the syntax for this. I created a test in MS Access and it loooks like this: INSERT INTO PatientTripRegionCountry_Temp ( CountryID, RegionID, Country, PatientTripID ) SELECT Country.CountryID, Country.RegionID, Country.Country, 2 AS PatientTripID FROM Country This works great in Access but not in SQL Server. In SQL Server 2 = @PatientTripID ANY SUGGESTIONS ON HOW TO HANDLE THIS?
View Replies !
How To Select NULL Values For My Report
Hi All, I use a parameter for product type in my report so that end user can select different product types from a drop down menu. Now my problem is that for some fields product type is blank(NULL). But end user can not see these NULL value when he click drop down menu. I can not use Allow Null value option in report paramter , because I have used multi value option. Is there any other way to select these NULL values? Thanks
View Replies !
|
TRACKER
