I have a table that has unit id, date, time, etc. I would like to select each unit id with the last date it has in the table. The result should have each unit listed once with the latest date in the table.
For Example:
unit id Date Time
00100 01/12/2007 8:00
00100 01/12/2007 8:45
00200 01/12/2007 8:50
00100 01/13/2007 13:30
00300 01/13/2007 13:45
00100 01/14/2007 11:00
00200 01/14/2007 11:30
Hi I am new to MSSQL and have encountered the following create statement. However I am not able to determine what the constraints like DF__work_home__name__1AF3F935 DEFAULT '' NOT NULL really mean ?
I am not getting it...
where is this name__1AF3F935 coming from ??
Code:
CREATE TABLE dbo.work_home ( wh_id numeric(18,0) IDENTITY, pid numeric(18,0) NULL, name varchar(50) CONSTRAINT DF__work_home__name__1AF3F935 DEFAULT '' NOT NULL, [position] varchar(80) CONSTRAINT DF__work_home__posit__1BE81D6E DEFAULT '' NOT NULL, computer varchar(15) CONSTRAINT DF__work_home__compu__1CDC41A7 DEFAULT '' NOT NULL, [connection] varchar(15) CONSTRAINT DF__work_home__conne__1DD065E0 DEFAULT '' NOT NULL, access_to_files varchar(15) CONSTRAINT DF__work_home__acces__1EC48A19 DEFAULT '' NOT NULL, access_to_apps varchar(15) CONSTRAINT DF__work_home__acces__1FB8AE52 DEFAULT '' NOT NULL, comment text NULL, disabled char(1) CONSTRAINT DF__work_home__disab__49E3F248 DEFAULT 'N' NOT NULL, CONSTRAINT pk_wh_id PRIMARY KEY CLUSTERED (wh_id) )
HelloCan anyone help me translate this from access so that it can work in mssql(i need to get next value, but cannot use identity as if row is deleted,another must get new next column number which would be same as deleted one)Access;INSERT INTO tableSELECT(IIF(code<>Null,MAX(code)+1,1) AS code,0 AS usercodeFROM tableI tried this in mssql but will not work:INSERT INTO tableSELECTCASEWHEN code IS NULL THEN 1ELSE MAX(code)+1ENDAS code,0 AS usercodeFROM table
Seasons greetings to everyone,A simple question. Could someone show me the syntax to produce multiple (2 or 3) result sets in a stored proc and how you access those sets from a c# program (ASP.NET)..Couldn't find a reference on Google, maybe I was asking the wrong question! Thanks for any help regardsDavej
Hi,I can SELECT TOP 9 from a mssql table.I now want to SELECT TOP 9 (except for the first 9), or in other words SELECT SECOND TOP 9... or SELECT MIDDLE 9 I have seen solutions in mysql such as: LIMIT 9,9 would not select the first 9 but select the second nine. Is there a similar solution to this for mssql? Thanks,Jon
I have 2 tables. One table MASTER has a MasterID,First_Name,Last_Name, address etc.. (One)The other table DEPENDANTS has an ID,MasterID,First_Name,Last_Name, etc. (Many)The Tables can be joined by the MasterID Is there a way to run a query on the MASTER so that the result would show all of the fields in the master and the last field showed the first name of all of the associated DEPENDANTS first_name in one field separated by some delimiter like a comma. I need this to be able to export to a spreadsheet for reporting purposes and this is the requirement. For Example TOM | JONES | 123 4TH STREET | SUE,MEGAN,JEFF If that can't be done does anybody have an idea on an easy way to get this result? Thanks in advance
I am trying to select distinct rows from a mssql database view.
The query uses 4 tables to generate the data and I would like to select the distinct invoice numbers (INV No) to display on an HTML page (using PHP). I have tried adding the "DISTINCT" function but I can't seem to get it to work correctly.
Thanks for the help!
Here is the query for the view:
Code:
SELECT dbo.ARHEADER.[INVOICE NO] AS [INV No], dbo.ARHEADER.[INVOICE DATE] AS [INV Date], dbo.ARGLTRAN.[BATCH NO] AS [Bat. No], dbo.ARGLTRAN.[BATCH YEAR] AS Year, dbo.ARGLTRAN.[BATCH PERIOD] AS Month, dbo.DEBTOR.NAMES AS [Customer Name], dbo.ARGLTRAN.[DESC] AS [Details/Services], dbo.SALESREP.NAME AS [Sales Rep], dbo.DEBTOR.CSR, dbo.ARHEADER.[JOB NUMBER] AS [Job No], dbo.ARGLTRAN.[SETUP CHARGE], dbo.ARGLTRAN.[MIN CHARGES], dbo.ARHEADER.FREIGHT, dbo.ARGLTRAN.[UNIT PRICE], dbo.ARHEADER.TOTQTY, dbo.ARGLTRAN.[STANDARD RATE], dbo.ARHEADER.TOTDISC, dbo.ARHEADER.TOTTAX, dbo.ARHEADER.TOTNET, dbo.ARGLTRAN.[BATCH TYPE] FROM dbo.ARHEADER INNER JOIN dbo.ARGLTRAN ON dbo.ARHEADER.[INVOICE NO] = dbo.ARGLTRAN.[INVOICE NO] INNER JOIN dbo.DEBTOR ON dbo.ARHEADER.[DEBTOR ACCT NO] = dbo.DEBTOR.[AC NO] INNER JOIN dbo.SALESREP ON dbo.ARHEADER.[SALESREP RECNUM] = dbo.SALESREP.[DATAFLEX RECNUM ONE] WHERE (dbo.ARGLTRAN.[BATCH TYPE] = 'DI')
In a select statement select_list, if one of the columns in the result set is NULL, I need to substitute a constant value else I want to return the value in the column. Would I use an IF_THEN_ELSE or CASE expression and how would that look? Thanks, Robert
I need to use IF-THEN in a SQL Select statement. Using Google I have found a couple of obscure references to this but nothing that I can use. Tried various combinations without luck. Can anyone point me to a good resource or supply a simple example of proper syntax? Thanks in advance for any help provided.
I have the following statement that I am trying to convert to a "like" statement in a SqlDataSource for a web application. I can't seem to get the syntax correct. Would someone be able to assist with this? Thanks! SELECT * FROM [Employees] WHERE ([LName] = @LName) ORDER BY [LName], [FName] Something like below. SELECT * FROM [Employees] WHERE ([LName] LIKE '@LName%') ORDER BY [LName], [FName]
What is the syntax for making a query using like, the below is my code <asp:SqlDataSource ID="Search" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>" ProviderName="<%$ ConnectionStrings:DatabaseConnectionString.ProviderName %>" SelectCommand="SELECT * FROM [Products]WHERE category LIKE %@category%">
<SelectParameters> <asp:QueryStringParameter Name="category" QueryStringField="category" Type="string" /> </SelectParameters> </asp:SqlDataSource> Its giving a syntax error but I dont know how to change it. Please advise, thanks!
Hi everyone, I'm looking for a way to select the default value of a particular column but can't seem to find the syntax for this anywhere. It's a rather peculiar situation where it's needed... Thanks
I am having trouble with this statement. I am returning multiple rows because I am doing the select statement within the loop. I need to keep the loop somehow because of the where clause of the select statement:
'AND @start not in (select sh_istart from casemas where sh_istart in (select sh_istop from casemas where sh_serial in (53565,53588,53597))) and @start between sh_istart and sh_istop'
Is there anyway that I can maintain the ability to use the loop but not do mutiple select statements like below:
Also I'm trying really hard not to use temp tables in this example
Result from select statement below
sh_serial ----------- 53565 53597
sh_serial ----------- 53565 53597
sh_serial -----------
sh_serial ----------- 53588 53597
Desired results:
sh_serial ----------- 53588 53597 53565
Syntax:
declare @start int select @start = 580 declare @stop int select @stop = 900
while @start <= @stop begin select sh_serial, from casemas, schilin WHERE (schi_shser = sh_serial) and (schi_itemno = '004852') and (sh_serial <> 600000) and sh_serial in (53565,53588,53597) and sh_serial in
(select distinct sh_serial from casemas, schilin WHERE (schi_shser = sh_serial) and (schi_itemno = '004852') and sh_serial in (53565,53588,53597) AND @start not in (select sh_istart from casemas where sh_istart in (select sh_istop from casemas where sh_serial in (53565,53588,53597))) and @start between sh_istart and sh_istop group by sh_serial having (sum(schi_qty) + 1 < 4 ))
I looked up select case statements and have used them for returning single values, but can't seem to get it working when returning a select statement..
I have my SPROC configured as below, can anyone help me out as to why its not working?
btw previously I had been using something like below, but in this case it wont work as I have to make more changes than just the WHERE genderID = @genderID (hmm, hopefully that makes sense, if not ignore my example lol)
IF @genderID > 2 .. ELSE ........WHERE genderID = @genderID
I'm trying to order a varchar column first numerically, and secondalphanumerically using the following SQL:SELECT distinct doc_numberFROM doc_lineWHERE product_id = 'WD' AND doc_type = 'O'ORDER BY CASE WHEN IsNumeric(doc_number) = 1THEN CONVERT(FLOAT, doc_number)ELSE 999999999END,CASE WHEN IsNumeric(doc_number) = 1THEN 'ZZZZZZZZZ'ELSE doc_numberEND;When try executing this statement, I get the following error:Server: Msg 145, Level 15, State 1, Line 1ORDER BY items must appear in the select list if SELECT DISTINCT isspecified.If I take the "distinct" out, it works just fine, except for the fact that Iget many duplicates.Does anyone have any suggestions?Thanks,Frank
Hi expert.. I am doing a project that have function to capture the Day and Night time talking duration of caller.... I make use of MSSQL to store the date time file..Let say I have this date: "2006-10-02 00:02:09".. How di I select "00:02:09" only???? Thanks in advancesuigion
Is there a way to do a SELECT TOP # using a variable for the #? In other words I'm doing a SELECT TOP 50* FROM DATATABLE If I pass an @value for the number SELECT TOP @value* FROM DATATABLE doesn't work I am generating a random sampling of data and I want to allow the user to select the number of results they choose to have. Thanks in advance.
Hello, I have the following query. When I run the query I get the following error message: "Incorrect syntax near keyword SELECT"--------------------------------------------------- SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS rownum, * FROM SELECT TOP (100) PERCENT Videos.VideoId, Videos.UserId, Videos.UserName, Videos.Title, Videos.Description, Videos.Tags, Videos.VideoLength, Videos.TimesHeard, Videos.ImageURL, Videos.RecType, Videos.Language, Videos.Category, Videos.DateAdded, Videos.RewardProgram, Videos.EditorChoice, TB2.hitsFROM Videos LEFT OUTER JOIN (SELECT TOP (100) PERCENT VideoId, COUNT(*) AS hits FROM (SELECT TOP (100) PERCENT UserId, VideoId, COUNT(*) AS cnt1 FROM Hits GROUP BY VideoId, UserId) AS TB1 GROUP BY VideoId) AS TB2 ON Videos.VideoId = TB2.VideoIdORDER BY TB2.hits DESC) AS T1WHERE rownum <= 5----------------------------------------- If I run the query that is in BOLD as: SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS rownum, * FROM Videos) AS T1 WHERE rownum <=5the query runs just fine. Also if I run the query that is NOT bold (above), it also runs fine. What can I do to run them both together as seen above? Thank in advance,Louis
I'm trying to help out a beginning DBA and come up with a SQL query. Here is the information I've been given so far.
"I have a SQL database (TEST1_new) with several tables. I need to have some values updated in one of the tables HARR2APP.CUSTOMER_ORDER_LINE_TAB1). I need the value that exists in the QTY_SHIPPED field to be divided by 250. I also need to include a WHERE statement for the PLANNED_SHIP_DATE greater than 11/15/2004 and a CATALOG_NO =18053185292 or 18053185285.
Basically, what I need to do is take the value for QTY_SHIPPED for Catalog_NO 18053185285 & 18053185282 and divide the result by 250"
After reading through her statement above about 10 times and guessing a bit, here's what I was able to come up with. I'm sure the syntax is not correct but maybe it's close to being what's needed?
INSERT INTO HARR2APP.CUSTOMER_ORDER_LINE_TAB1()
(SELECT dbo.TEST1_new.HARR2APP.CUSTOMER_ORDER_LINE_TAB.QTY_SHIPPED LIMIT 1) /250),PLANNED_SHIP_DATE from dbo.TEST1_new.HARR2APP.CUSTOMER_ORDER_LINE_TAB
WHERE CATALOG_NO = ‘18053185292’ OR CATALOG_NO= ‘18053185285’
This (demo) statement is fine in Access, and so far as I can see, shouldbe OK in SQL Server.But Enterprise Manager barfs at the final bracket. Can anyone helpplease?select sum(field1) as sum1, sum(field2) as sum2 from(SELECT * from test where id < 3unionSELECT * from test where id 2)In fact, I can reduce it to :-select * from(SELECT * from test)with the same effect - clearly I just need telling :-)cheers,Jim--Jima Yorkshire polymoth
I hope I'm posting this in the correct forum. If not I apologize. I have a nested select query that I imported from Oracle:
Oracle Version:
Code Snippetselect avg(days) as days from ( select dm_number, max(dm_closedate) - max(comment_closed_date) as days from dm_data where dm_type = 'prime' and dm_closedate <= '31-dec-2007' and dm_closedate >= '1-dec-2007' and program = 'aads' group by dm_number)
SQL Version:
select round(abs(avg(days)), 0) as days from (select dm.dm_number, abs(datediff(DAY,max(dm.dm_closedate), max(dm.comment_closed_date))) as days from dm_data dm, ProgramXref px where px.Program_Name = 'aads' and dm.Program_Id = px.Program_Id and dm.dm_type = 'prime' and dm.dm_closedate <= '31-dec-2007' and dm.dm_closedate >= '1-dec-2007' group by dm.dm_number)
In Oracle the query runs fine. In SQL I am getting a "Line 10: Incorrect syntax near ')'." error. If I run just the nested portion of the query, there are no errors. It only happens when the first query tries to query the nested query. Can anyone help me get the syntax correct?
SELECT @idAgency = idAgency FROM tblAgencies WHERE strAgencyId = @strAgencyId;
SELECT strName, strAddress1, strAddress2, strCounty, strCountry, strPostcode, strTelephone, strFax, bitActive, bitHeadOffice, bitFinanceBranch FROM tblBranches WHERE fk_idAgency = @idAgency
SELECT strFirstName, strSurname, strUsername, bitActive FROM tblUsers WHERE fk_idAgency = @idAgency
GO
It basically first declare's and sets @idAgency using the first small select statment, then uses that parameter to run two more selects queries which I want sending to a dataset. Now within the Query Analyzer this works fine. But in my asp.net page it trows up this error:
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'spAdminAgencyActivate_Select'
Now the code im using in the asp.net page is as follows:
Dim objSqlConnection as New SqlConnection(ConfigurationSettings.AppSettings("strCon")) Dim objSqlCommand_Select as New SqlCommand("spAdminAgencyActivate_Select", objSqlConnection)
Can anyone help? I believe the error is in the stored procedure somewhere, because if I change the stored procedure so no parameters are being passed to it then it starts working. This is what I comment out and change for it to to get it working, but obviously this is not satisfactory as a final result because the parameter is hard coded in the stored procedure. Im just showing this to see if it gives anyone a clue!!
In Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB I created the following parameter:Dim strName As String Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50) parameterBName.Value = strName myCommand.Parameters.Add(parameterBName) I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER " error is:Line 1: Incorrect syntax near 'V'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'V'. Source Error: Line 59: Line 60: Line 61: myCommand.ExecuteNonQuery() 'Execute the query
What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT 2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end 3 FROM abc 4 Anyone can help? Thanks a lot.
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?
i want to make the cursor fast in any time whatever how many results returned
