This is my problem, i have 2 tables: one table that hold data, having id as identity, say table (id, content), the other table have 2 columns: (id, number). The second table number column refer to the id in the first table. i want to build a query that get the data from the first table that correspond to a specific id in the second table, not only this i want to get the previous, the current and the next item.
For example: if table_data, table_info is first and second tables, something that can do it is :
DECLARE @i int
SELECT * FROM table_data WHERE id in (@i -1, @i +1, @i)
The problem here if @i, @i+1 or @i-1 doesn't exist the column will not be returned, i want to get a result similar to
id, content
25 null
26 null
27 #some content#
or
34 #content1#
35 #content2#
36 #content3#
so my problem is that nulls doesn't appear, i thought about using OUTER JOIN, but the problem is that outer join take tables, not (@i -1, @i +1, @i), so if only i can make somehow the outer join use these values, i think it works.
Hi I have a query, what I would like to do is create a column that takes the results in two coulms and add them together: Col A Col B Col C Row1 1 1 2 Row2 2 3 5 Here is the query declare @t table( player_name varchar(100), BuyIn int, TopUp int, ReBuy int, Winnings int, Events int, Test int) INSERT INTO @t (player_name, TopUp) SELECT Player_name, SUM([Top-ups]) AS TOPUPS FROM (SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value, Events.Top_up * Event_data.Transaction_value AS [Top-ups] FROM Event_data INNER JOIN Events ON Event_data.Event_id = Events.id INNER JOIN Players ON Event_data.Player_id = Players.Player_id WHERE (Event_data.Transaction_type = 2)) AS Topups GROUP BY player_name INSERT INTO @t (player_name, ReBuy ) SELECT Player_name, SUM([Re-buys]) AS REBUYS FROM (SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value * Events.Rebuys AS [Re-buys] FROM Event_data INNER JOIN Events ON Event_data.Event_id = Events.id INNER JOIN Players ON Event_data.Player_id = Players.Player_id WHERE (Event_data.Transaction_type = 3)) AS REBUYS GROUP BY Player_name Insert into @t (player_name, BuyIn) SELECT dbo.Players.Player_name, SUM(dbo.Events.Buy_in) AS BuyIn FROM dbo.Players INNER JOIN dbo.Event_data ON dbo.Players.Player_id = dbo.Event_data.Player_id INNER JOIN dbo.Events ON dbo.Event_data.Event_id = dbo.Events.id GROUP BY dbo.Players.Player_name, dbo.Event_data.Transaction_type HAVING (dbo.Event_data.Transaction_type = 1) ORDER BY SUM(dbo.Events.Buy_in) DESC
Insert into @t (player_name, Winnings) SELECT dbo.Players.Player_name, SUM(dbo.Event_data.Transaction_value) AS Winnings FROM dbo.Players INNER JOIN dbo.Event_data ON dbo.Players.Player_id = dbo.Event_data.Player_id GROUP BY dbo.Players.Player_name, dbo.Event_data.Transaction_type HAVING (dbo.Event_data.Transaction_type = 1) insert into @t (player_name, Events) SELECT dbo.Players.Player_name, COUNT(dbo.Event_data.Place) AS Expr1 FROM dbo.Players INNER JOIN dbo.Event_data ON dbo.Players.Player_id = dbo.Event_data.Player_id INNER JOIN dbo.Events ON dbo.Event_data.Event_id = dbo.Events.id GROUP BY dbo.Players.Player_name HAVING (NOT (COUNT(dbo.Event_data.Place) IS NULL)) insert into @t (player_name, test) select player_name, ((TopUp) + (Rebuy)) as Test from @t SELECT player_name, min (BuyIn) as BuyIn, min(TopUp) as TopUps, min(ReBuy)as ReBuy, min(Winnings) as Winnings, min(Events) as Events, min(test) as test FROM @t GROUP BY player_name ORDER BY BuyIn DESC --ORDER BY TOPUPS DESC
END
THis is where I attempt to add the coloms but I get a null result
insert into @t (player_name, test) select player_name, ((TopUp) + (Rebuy)) as Test from @t
I am trying to emulate the "Find unmatched Records" Access Query using DTS. I have a small table with a list of codes that I need to use to exclude records in my text export.
I have created a left outer join and set the criteria for the joined field in the 'right' table to be null. I am repeatedly getting no records as a result.
Wouldn't the joined field in the right table for unmatched records by default be null? I'm stumped as to why this seamingly simple process won't work for me. I have attached the select statement for your review.
I have a table called "alert" and in this table, for each columns there are data as 1 , or 0 or NULL. how do I write a query to just give me data's with "1" in them not the NULLS or 0?
Is there a way to set SQL Server 2005 Express so that I can return null values? For example, the following query will not return any values:
SELECT * FROM tbl_form_values where fldVALUE IS NULL;
it does return values with:
SELECT * FROM tbl_form_values where fldVALUE = '';
I have an Oracle background and all null values are true nulls not empty strings. I would like to be able to use the first query. Also other functions such as COALESCE work very nice with nulls. I can do the following in Oracle but not in SQL Server:
SELECT fldID, fldMID, fldFID, COALESCE(fldVALUE, 'n/a'); FROM tbl_form_values
This will return the values in fldVALUE if they are available and n/a for all NULL values.
When i do a select on my emplee table for rows with null idCompany i dont get any records
I then try to modify the table to not allow a null idCompany and i get this error message:
'Employee (aMgmt)' table - Unable to modify table. Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails. The statement has been terminated.
is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?
I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
I'm having a bit of a trouble explaining what I'm trying to do here.
I have 3 "source" tables and a "connecting" table that I'm going to use
tblContacts - with contactID, ContactName etc tblGroups - with GroupID, GroupName tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)
They are related in a table called tblContactsGroupConnection - with ContactID, GroupID and SubGroupID
One contact can be related to many subgroups. What I want is a list of all contacts, with their IDs, names and what groups they are related to:
Hi, I got a problem. I installed Microsoft SQL Server Management Studio Express 2005 version. And I created a Compact database. I created an connection in SSMSE to connect the database and opened a query form. then, i run the following sql:
Select * from Table1
It returned 3 records to me. After that, I used program to insert record into this table. Then i ran this sql again, it still show me 3 records. I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me.
Why? It's very strange and difficult to operate, right? Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation?
How can I know whether a query or a stored procedure is successfully executed? I mean like in delete case? here is an example
sql_delete = "DELETE FROM UserData where U_ID='" & tempID & "'" rstIDChk.Open sql_delete, cnn, adOpenStatic, adLockOptimistic
How can I make sure that the record is deleted so that I can proceed with other jobs? How can I catch it in the program like from VB? Is there any return like true or false in SQL? :(
Hi! create table testReturn(id int identity(100,1),name varchar(10)) How can I return the value of identity column after inserting the value. Dim objConn As SqlConnection Dim SQLCmd As SqlClient.SqlCommand Dim ds As New DataSet Dim strsql As String Try objConn = New SqlConnection objConn.ConnectionString = _ "Network Library=DBMSSOCN;" & _ "Data Source=localhost;" & _ "Initial Catalog=mydb;" & _ "User ID=userid;" & _ "Password=pass" objConn.Open() strsql = "insert into testReturn values ('a')" SQLCmd = New SqlClient.SqlCommand(strsql, objConn) Dim rowsAffected As Integer = 0 rowsAffected = SQLCmd.ExecuteNonQuery Dim rv As String rv = SQLCmd.Parameters(0).Value.ToString() Response.Write(rv) Catch ex As Exception Response.Write(ex.ToString) End Try
Hii I am Varun i have a problem with the dynamic stored procedure This is my stored procedureALTER PROCEDURE dbo.sp_TimeTableAdjustment1 (@TeacherID_OnLeave numeric(9), @DateFrom datetime ,@DateTo datetime , @UserID numeric(9) ) AS declare @flag as numeric(9) declare @year as varchar(4) set @year=(select batch from batchmaster where iscurrent=1 and isdeleted=0) if( @year=null or len(@year)=0) set @year = year(getdate())exec ('if not exists(select * from timetableadjustments_'+@year+' where datefrom='''+@datefrom+''' and dateto='''+@dateto+''' and teacherid_onleave='+@teacherid_onleave+') begin insert into TimeTableAdjustments_'+@year+' (TeacherID_OnLeave,DateFrom,DateTo,UserID) values ('+@TeacherID_OnLeave+','''+@DateFrom+''','''+@DateTo+''','+@UserID+'); end') else set @flag=(select timetableadjustmentid from timetableadjustments_2007 where datefrom=@datefrom and dateto=@dateto and teacherid_onleave=@teacherid_onleave) return @flag --exec('select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+' --') --return @flag --exec('@flag=select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+'')
This has got to be a simple one, but I could not find out how I have this simple proc for test purposes only...
ALTER PROCEDURE dbo.RotoTest( @strSSN VARCHAR(11), @blnUseACHDate BIT = 0, @intInvestorId int = 0)ASreturn 5
Thats right it does nothing but return 5 and thats because I removed all the real code to simplify my question. When I hit this proc in SQL Query Analyzer.. exec RotoTest '123-45-6789', 0, 1 No return value is displayed in Query Analyzer. When I try something like this exec RotoTest '123-45-6789', 0, 1, intRetValue Output (in this situation I get wrong number of arguments) How can I get a return value displayed on my screen in SQL QUERY ANALYZER with out modifying the proc? I do not want to modify the proc in anyway (because I can't), I just want to display the return value
Hi I have written a query for viewing the results of an on-line survey. I have three tables involved in this query: answers, answerpossibilities and users. So I use a few joins and made this query: ALTER PROCEDURE dbo.GeefAntwoordenMeerkeuze ( @question_id int ) AS SET NOCOUNT ON; SELECT answerpossibilities.answerpossibility_content AS[Answerpossiblity], COUNT(answers.answers_id) AS [Times chosen] FROM answers right OUTER JOIN answerpossibilities ON answers.answerpossibility_id = answerpossibilities.answerpossibility_id left join users on answers.user_id = users.user_id WHERE ((answerpossibilities.question_id = @question_id AND nswerpossibilities.answerpossibility_content!='-- choose answer --')) GROUP BY nswerpossibilities.answerpossibility_content ORDER BY [Times chosen] desc The above query works fine. The data returned by this query is shown in a gridview. When an answerpossibilty was never chosen it shows 0 as times chosen. So that's fine. But the problem is, only answers of users who completed the survey should be shown. In the users table there's a field user_completed. So the query should check whether this field is 1 (true). ALTER PROCEDURE dbo.GeefAntwoordenMeerkeuze ( @question_id int ) AS SET NOCOUNT ON; SELECT answerpossibilities.answerpossibility_content AS[Answerpossiblity], COUNT(answers.answers_id) AS [Times chosen] FROM answers right OUTER JOIN answerpossibilities ON answers.answerpossibility_id = answerpossibilities.answerpossibility_id left join users on answers.user_id = users.user_id WHERE ((answerpossibilities.question_id = @question_id AND nswerpossibilities.answerpossibility_content!='-- choose answer --') and users.user_completed = 1) GROUP BY nswerpossibilities.answerpossibility_content ORDER BY [Times chosen] desc Using this query only answers of users who completed the survey are shown but answer possibilities that were never chosen are no longer shown with 0 as times chosen. The gridview simply doesn't show them anymore. Thanks for helping me!
Something went wrong by posting this message I guess, all blank lines were gone.. maybe because I used Safari on my iMac
Does sql server have a mechanism (aside from count()) that for any given SELECT query will tell you only how many rows it will return without actually returning the data?
The reason for this is that we have a generic lookup form in an application that is used on almost every screen (we have a lot of screens, so it gets a lot of different, sometimes complicted, queries passed to it to use for the lookup, and having to manually edit the query to use count over all the select clauses doesn't seem like the best way to handle this. If we could do a kind of 'trial run' against the server just to get the number of rows and use that to help set up the form, that would be ideal.
i want a query which returns all the date between 2 dates . its like an calender.....for example i selected 2-1-2006(dd-mm-yyyy) to 18-03-2006 ....it should returns like this 2-1-2006 3-1-2006 4-1-2006 . . . 16-03-2006 17-03-2006 18-03-2006
Hello,I'm trying to something that just works in Oracle, but does not in SqlServer.How can I get the percentage of two counts to be returned on each rowof the query?select count(sid), /* all not the not null */count(*),(count(sid) /count(*) ) as percent_not_null,4 as four,(3/4) as three_over_fourfrom dbo.sysusers7082040Incredibly, it changes even 3/4 into a zero!For efficiency, I want the percentage returned in the query.And to not use variables and coding. Efficiency,both of the server, and of my time.Note: I am using dbo.sysusers as an EXAMPLE only. My realquery will be on user defined application tables.What is the solution please?
i need to retrieve the most recent timestamped records with uniquenames (see working query below)what i'm having trouble with is returning the next-most-recent records(records w/ id 1 and 3 in this example)i also need to return the 3rd most recent, 4th, 5th and 6th most recent- i figure if i can get the 2nd working, 3rd, 4th, etc will be cakethanks,brett-- create and populate tabledrop table atestcreate table atest(id int not null, name char(10), value char(10),timestamp datetime)insert into atest values (1,'a','2','1/1/2003')insert into atest values (2,'a','1','1/1/2004')insert into atest values (3,'b','2','1/1/2003')insert into atest values (4,'b','3','1/1/2002')insert into atest values (5,'b','1','1/1/2004')-- select most recent records with distinct "name"sselect a.* from atest as awhere a.id = (select top 1 b.id from atest as bwhere b.name = a.nameorder by timestamp desc )/*query results for above query (works like a charm)2a 1 2004-01-01 00:00:00.0005b 1 2004-01-01 00:00:00.000*/
I have encountered some problems creating MDX query. There are two input parameters on report, both dropdown list and from query. The first parameter will check "State", and the second parameter "Store Name" depends on previous' result. If there's no "State" found, I manage to show a "No Data" in the "State" droplist. But with "No Data" in first parameter, the second parameter simply is not enabled. I want the second parameter shows " No Data" as well but not succeed.
Here's code
...
SELECT {[Measures].[A1], [Measures].[B1], [Measures].[C1]} ON COLUMNS ,
[Store].[Store Name].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@State) ) ON COLUMNS FROM [Cube]) ... The query need to look at dataset that contains [store name] on rows, and some measures on columns. It also restricted by parameter @State.
Store Name that satisfies @State will be displayed. But if nothing from SELECT ( STRTOSET(@State) ) ON COLUMNS FROM [Cube], there's nothing in result. I'd rather like show something rather than nothing. I try codes like ... MEMBER [Store].[Store].[NA] AS '"N/A"' IIF(<Parameter empty>, (SELECT ... ON COLUMNS, [Store].[Store].[NA] ON ROWS FROM ...), (SELECT ...<Original statement >) ) ... It shows "Subselect support only Column axis".
Any one has an idea? Please help me out Many thanks!
Hi, I am trying to get all the databases from MS SQL into a dropdownlist. However, I am not sure how to do that in query ... is there a way to retrieve all databases name using a query in MS SQL? Thanks.
I want the following query to return a row even when table 'X' is empty. How would I do this? SELECT TOP 1 @Var1, @Var2, @Var3 from X The parameters @Var1, @Var2 and @Var3 are passed to the stored procedure in which the above query is included. When table is empty, it reurn nothing. It only return a row when table is not empty.
Hi i am trying to use this query to pull all the publications stored in the database and all the authors contributing to that publication (1 to many relationship). I am trying to use a sub query so that i can display the results on one row of a gridview (including a consecutive list of all the authors). However i am recieving this error: Incorrect keyword near the word SET. ?
Maybe i need to add a temp column in the sub query to pull all the related authors for a single publication - but i dont know the sql for this? can anyone help?
Thanks SELECT ISNULL(Publication.month, '')+ ' ' + ISNULL(convert(nvarchar, Publication.year), '') as SingleColumn, Publication.publicationID, Publication.title FROM Publication WHERE Publication.publicationID IN (SELECT (convert(nvarchar, Authors.authorName)) FROM Authors INNER JOIN PublicationAuthors ON Authors.authorID = PublicationAuthors.authorID) AND Publication.typeID IN (SELECT PublicationType.typeName FROM PublicationType INNER JOIN PublicationType ON Publication.typeID = PublicationType.typeID
For each blog entry viewed in my web app, a (IPNumber, Date, BlogID) row is inserted into table "BlogViews" How can I fetch the BlogID which has been most often visited, that is has the most number of rows?
I run a query in QA which return funny characters (suppose to be chinese characters). I try saved as CSV and open in excel, still it remain as those funny characters... What can I do to get those output into the chinese character. It doesn't need to be in QA... but I need it in Excel. Thanks
i have a column of dates. i make an index or key for this column so that i can perform binary search i hope. If the search is not succesful how do i get the query to return the two dates surrounding the date i was searching for.
When querying any of our database tables (returning all rows from within the enterprise manager) we are getting the following error message: "The query cannot be executed because some files are either missing or not registered" Can anyone help me with this....
I hope someone can help, I usually just need to do simple queries, and am fine with that, but this is a bit over my head without a few pointers in the right direction.
I have a table that stores log results from pieces of hardware that contact an internet server periodically.
For simplification, this contains:
LogID SystemID DeviceID ContactDateTime Notes
Each device contacts the server about once every 5 minutes, and there are about 100 devices in 6 systems.
What I need to do is find out, for a certain date time range, things like:
1. the average time between comunication for any one device 2. the longest time between communication for any one device 3. average time between communications for all devices in a system.
We currently do this by downloading all the logs and working through them programmatically on the server, but as you can imagine, it is a very large amount of information, and it would be much more efficient if this could be done on the SQL server.
Hi. I have a query that returns several averages and percentages. Is there some way to set up the query so the results only go to 2 decimal places? Here is a sample of the query I am using: Code:
$query = "SELECT COUNT(deal_id), SUM(vs), SUM(vs)/COUNT(deal_id)*100, AVG(fin) FROM sales GROUP BY salesperson";
It works great,except the results are several decimal places long, and I need it to be only 2.
I am trying to create a query to return the latest record from a dataset. The code created so far returns multiple records, where I need a single record to be returned.
