I have a table with a column named measurement decimal(18,1). If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return 2.5. So if the value after the decimal point is 0, I only want the stored proc to return the integer portion. Is there a sql function that I can use to determine what the fraction part of the decimal value is? In c#, I can use dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.
I want to build a string that contains today's date (without the time).
For example, if select GETDATE() returns 2008-04-28 14:18:34.180, I want to end up with either
(1) 2008-04-28 or (2) 20080428. (I always want the month to be represented by a 2 digit number)
Is there a datepart parameter that will give me (1) in one shot? I don't think so, since datepart returns an integer and (1) is not in the form of an integer.
So, I was trying to build (2) by using the yyyy, mm, and dd parameters to extract out the appropriate parts and casting them to a string and contatentating them. However, when I do the month portion, (using mm) it gives me the integer that represents the month, and this number can be 1 or 2 digits depending on the month. I always want it to be a 2 digit number. For example, if I'm in Apr, I want to end up with "04", not "4". This would also apply to the day portion. (although the date that I'm writing this post is on the 28th so I'm not certain what GETDATE() will return on dates that are 1 digit.)
The only way I can get the single digit month to write out as a 2 digit month is by doing a LEN() function on the string and if it is a length 1, then concatenate a "0" in front of it. I started doing this and the expression became too crazy, so I wanted to first check to see if someone can come up with something cleaner.
I am currently working on an app and have an issue with a table in the database. The table has 10,000 records in it and a column is added that is to use a 4 digit in sequence. The datatype for the new column is varchar since no math will ever be done on the added column. If necessary, I can change the datatype but would prefer not to. The 4 digit would start with 0000 at ID 1 and go to 9999 at ID 10000. I'm thinking some type of update statement since it is updating each record but how would it be done sequentially?
'code'- varchar 255 (Unique number) data : chr456Umx 'Packs'- integer data : 6
Is it posible to generate 13 digit number using the above two columns, The reason is if I run the procedure I will get same 13 digit all the time depending on the above two colums
below is the sample procedure I am using
CREATE PROCEDURE AMZSelCen @imglink nvarchar(255)
AS
Select code as sku, PdtBarCode as [standard-product-id], 'EAN' as [product-id-type],
--generate 13 digit number
make+' '+model+' ' +', Price for '+cast(NumPacks as varchar(8)) +' '+'Packs' as title, make as manufacturer,'
I need to change the first digit (0) in a number, like 05678494 and replace it with 0046 so it would look like 00465678494 when it is done. I have tried to use the REPLACE function without any success. Can anyone help me?
There are 3 columns in the result set - part num, Qty and MO num. Each MO num has part numbers.So there might be same part numbers in MO's. Each part num has qty. So, if I group by part num, I get Qty.
1.There are duplicates of part.num and I want to remove duplicates and add quantities of those duplicates into one single quantity. For example, xxxx is a part num, then xxxx=1,xxxx=3,xxxx=5. I want xxxx=9. I want to sum those. Another question is, each MO has a user. I want to join user and MO num in MO.
Heres the code,
part.num , (woitem.qtytarget/wo.qtytarget) AS woitemqty, (SELECT LIST(wo.num, ',') FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id WHERE moitem.moid = mo.id) AS wonums FROM mo INNER JOIN moitem ON mo.id = moitem.moid LEFT JOIN wo ON moitem.id = wo.moitemid LEFT JOIN woitem ON wo.id = woitem.woid AND woitem.typeid = 10 LEFT JOIN (Select sum(woitem.qtytarget) as labor, woitem.woid, uom.code as uom from woitem JOIN part on woitem.partid = part.id and part.typeid = 21 JOIN uom on woitem.uomid = uom.id group by 2,3) as labor on wo.id = labor.woid LEFT JOIN part ON woitem.partid = part.id
declare @NumberToCompareTo int set @NumberToCompareTo = 8 declare @table table ( number int ) insert into @table select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
Ok I have the following SQL, I have a subquery in the SELECT part but also the same subquery in the WHERE part as well.What I'm trying to do is get all parents that have children OR get all parents with no children OR just get all parents regardless (@HasResponses is a BIT that can be 1, 0 or null). At the same time I want to count the total number of children in the select list, but I'm having to copy the same subquery in the SELECT and WHERE parts which doesn't seem terribly optimal to me (maybe it is, that's why I'm asking). I've tried referencing the column alias in the select list (AS [Responses]) for the where part (@HasResponses = 0 AND [Responses] = 0), but it doesn't seem to work.Is this the most optimal way to do it? Is there a better way? I'm working with SQL Server 2005.SELECTf.FeedbackText AS [Feedback Comment],u.Name AS [Feedback Author],f.CreatedDate AS [Created On],(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) AS [Responses]FROM Feedback fINNER JOIN [User] u ON f.StaffID = u.StaffIDWHERE f.CreatedDate >= @DateFromAND f.CreatedDate <= @DateToAND(@HasResponses IS NULLOR(@HasResponses = 1 AND(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) > 0)OR(@HasResponses = 0 AND(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) = 0))
I know this is an easy one and I know I've read it somewhere, but I can't seem to write the correct format to run correctly. I am trying to build a SELECT statement base on the selected values of a dropdown list on a webform. The selected values will be part of the Table name.. ("client_info" & location_option.selecteditem.value) Can someone show me the correct syntax for adding a form variable into a SELECT statement? Thanks
ALTER PROCEDURE [dbo].[sp_SelectMostRecentArticle]
AS BEGIN
DECLARE @article_id INT SELECT @article_id = ( SELECT TOP 1 article_id FROM article ORDER BY article_id DESC )
DECLARE @comment_count INT SELECT @comment_count = ( SELECT COUNT(comment_id) FROM comment JOIN article ON article_id = comment_article_id GROUP BY article_id HAVING article_id = @article_id )
SELECT TOP 1 article_id, article_author_id, article_title, article_body, article_post_date, article_edit_date, article_status, article_author_id article_author_ip, author_display_name, category_id, category_name--, comment_count AS @comment_count
FROM article
JOIN author ON author_id = article_author_id JOIN category ON category_id = article_category_id
GROUP BY article_id, article_title, article_body, article_post_date, article_edit_date, article_status, article_author_ip,article_author_id, author_display_name, category_id, category_name
HAVING article_id = @article_id
END GO
as you can see, im trying to return a comment_count value, but the only way I can do this is by defining the variable.
I have had to do it this way, because I cannot say COUNT(comment.comment_id) AS comment_count or it returns an error that it cant reference the comment.comment_id.
But when change it to FROM article, comment; I get errors about the article_author_id and article_comment_id.
And i cant add a join, because it would return the amount of rows of the comment...
unless someone could help with what i Just decribed (as i would prefer to do it this way), how would i return the variable value as part of the select statement?
I have an xml stored in a coulum of a table and I use the following query to extract an xml element :
select CONVERT(XML,CONVERT(NVARCHAR(max),Response)).value('(/Quote/error)[1]','nvarchar(max)') as Excepiton .The result of the expression is :
TL43:The product has no marked price.;I would like to select only the code : TL43and then separately I would like to select The product has no marked price.Is there a way I can do it?
I'm just getting started with using SQL Server Mobile while creating a handheld app using Visual Studio 2005. So far, I have succeeded in RETRIEVING data from the mobile database, but I don't understand why my INSERT nor UPDATE statements will not work. (I'm assuming they don't work, because after I run my code via the debugger, I oddly get no error message, but when I "open" the table in the Server Explorer, I do not see my expected results. Do I need to do some kind of "commit" or something? Here's my code. Thanks for any help! - Sue
-------------------------------------------
'Create SQL statement: Dim strSQL As String strSQL = "INSERT INTO MyTable (col1, col2) VALUES ('yucky', 'poo')"
'Create DB connection: Dim connDB As New System.Data.SqlServerCe.SqlCeConnection connDB.ConnectionString = ("Data Source =" _ + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.GetName.CodeBase) + "SueUWM.sdf;"))
'Create DB command: Dim cmndDB As New System.Data.SqlServerCe.SqlCeCommand(strSQL, connDB)
'Open the connection: connDB.Open()
'Execute the command: Dim intRecordsAffected As Integer Try intRecordsAffected = cmndDB.ExecuteNonQuery()
If intRecordsAffected <> 1 Then MessageBox.Show("Unable to insert into database.") End If Catch ex As Exception MessageBox.Show(ex.ToString, "Insert Error") End Try
I need to do a Select and after a Insert during a Transaction. But when i want to commit this transaction i've the following error message :
The transaction can not be committed if there are any opened cursors in the scope of this transaction. Make sure all data readers / result sets are explicitly closed before committing the change.
I don't understand this error message. How can i close my cursors.
Before my Insert i'm closing my SqlCeDataReader and i dispose it. My SqlCeCommand are also closed and dispose.
I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table. Here is my select statement: SELECT employee.emp_id, DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) * employee.emp_accrual_rate - (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft, employee.emp_lname + ', ' + employee.emp_fname + ' ' + employee.emp_minitial + '.' AS emp_name, department.department_name, location.location_name FROM employee INNER JOIN request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN department ON employee.emp_department = department.department_id INNER JOIN location ON department.department_location = location.location_id GROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_name ORDER BY location.location_name, department.department_name, employee.emp_lname
The section below is the part that may or may not contain information: SELECT (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft
So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!! TIA
The select command below will output one patient’s information in 1 row:
Patient id Last name First name Address 1 OP Coverage Plan 1 OP Policy # 1 OP Coverage Plan 2
[code]...
This works great if there is at least one OP coverage. There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table. The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill. The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.
select src.pat_id, lname, fname, addr1, max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1, max(case when rn = 1 then src.policy_id end) as OP_Policy1,
i am facing a bizarre problem, accessing data from a remote server which has been linked to my sql. My SQL server is 2005 and the remote server is SQL 2000. i have linked the remote server (called LinkedServer) so that when i run this query: SELECT * FROM LinkedServer.SomeDB.dbo.SomeTable executes successfully! However, when i run a similar query like this: SELECT Column1 AS Col1, LinkedServer.SomeDB.dbo.SomeTable.Column2 as Col2, Alias.Column3 as Col3 FROM LinkedServer.SomeDB.dbo.SomeTable INNER JOIN LinkedServer.SomeDB.dbo.SomeOtherTable1 ON LinkedServer.SomeDB.dbo.SomeTable.Column3 = LinkedServer.SomeDB.dbo.SomeOtherTable1.Column3
INNER JOIN LinkedServer.SomeDB.dbo.SomeOtherTable2 AS Alias ON LinkedServer.SomeDB.dbo.SomeTable.Column3 = Alias.Column3
It gives me this error: The multi-part identifier "LinkedServer.SomeDB.dbo.SomeTable.Column2" could not be bound. The multi-part identifier "LinkedServer.SomeDB.dbo.SomeTable.Column3" could not be bound. I have noticed that this error is generated only for the selected columns whose path has either been repeated (e.g. "LinkedServer.SomeDB.dbo.SomeTable.Column2" as opposed to "Column2") or for columns which are from aliased tables.
What is going on?!?!?!??!?!?!
Any Help would be tremendously appreciated!!!! 8..)
Below. I have also pasted the current result of this query and the desired result.
Query can be updated to get the desired result as given below?
Query: Select c.OTH_PAYER_ID, c.PAID_DATE, f.GROUP_CODE, f.REASON_CODE, f.ADJUSTMENT_AMOUNT From MMIT_CLAIM_ITEM b, mmit_tpl c , mmit_attachment_link d, MMIT_TPL_GROUP_RSN_ADJ f where b.CLAIM_ICN_NU = d.CLAIM_ICN and b.CLAIM_ITEM_LINE_NU = d.CLAIM_LINE_NUM and c.TPL_TS = d.TPL_TS and f.TPL_TS = c.TPL_TS and b.CLAIM_ICN_NU = '123456788444'
Current Result which I am getting with this query
OTH_PAYER_ID PAID_DATE GROUP_CODE REASON_CODE ADJUSTMENT_AMOUNT 5501 07/13/2015 CO 11 23.87 5501 07/13/2015 PR 12 3.76 5501 07/13/2015 OT 32 33.45 2032 07/14/2015 CO 12 23.87 2032 07/14/2015 OT 14 43.01
Desired/Expected Result for which I need updated query
I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN*** Example Record 2. Theale, Nr Reading, Berkshire Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0 THEN LEN(Address ) ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0 THEN LEN(Address) ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
I have SSRS 2005 SP2 configured to work in Sharepoint integration. Everything works fine except that I am not able to programmatically change any property of report viewer web part (instance) that I have added on on home page of my sharepoint site. I can do same thing via sharepoint UI but not through program. When my programs runs it fetches all web parts been added on home page, then I need to iterate through each one and find report viewer web part. While iterating, as soon as I arrive to report viewer web part it is named as "Error web part" with error message as "Windows SharePoint Services cannot deserialize the Web Part. Check the format of the properties and try again"
If someone has a solution, please respond at your earlist.
I don't understand why I am having this error. It does create the database on AddSubscription() method but it is failing opening the database on Synchronise(). I have also tried uninstalling and then reinstalling all the SQL Mobile components in the following order.
suggested that this problem could be circumvented by getting the replication object early and then continuously using the same object for subsequent replications.
I did this and it works a lot better now but ever so often I get another error which is
Native Error 28559 SQL Mobile encountered problems when opening the database
My only option at this point is to stop and start the application which cures the problem.