This is too long for a comment. You can do this by defining a function to calculate the count and using that function in the computed column definition. However, I don't think this is a good idea for frequently used columns, because you will be doing a lot of counting...

The reason you get an error is probably since option is a reserved word in MySql. to escape reserved words in MySql use this char `: UPDATE question_table SET `option` = Replace(`option`, 'cm2','cm<sup>2</sup>') WHERE `option` LIKE '%cm2%' Here is a list of reserved words in MySql Here is my favorite...

First you will need to unpivot your table and after that pivot again. This is the key idea: select * from TableName unpivot(v for xyz in([Atest],[Btest],[Ctest]))u pivot(max(v) for yyyymmdd in([20150525],[20150526],[20150527]))p Fiddle http://sqlfiddle.com/#!3/a675c/1 As for dynamic sql, you can find many example of how you can use STUFF function to concatenate...

You need to use this syntax when updating with a join: UPDATE s SET s.[columnName] = l.[columnName] FROM [Server].[ServerDB].[dbo].[tableName] s INNER JOIN [LocalDB].[dbo].[tableName] l ON l.id = s.id ...

As a best practice you should write the insert statement as: Insert into [PFTracking].[dbo].[UserInput](Col1,Col2,...) -- list the names of columns explicitly in which data is to be inserted SELECT [PTAID] ,[RequestDate] ,[ProvName] ,[Amount] ,[INorOUT] ,[Supplier] ,[Customer] ,[Program] ,[IssueDesc] ,[Chargeable] ,[Company] FROM [PFTracking].[dbo].[TempTable] You can omit the column names that get...

I would try not to use a function at all. Using straight SQL is usually a better solution. Based on the information you gave we have the following tables. (Your function does compile with these tables.) CREATE TABLE emp_task (emp_id NUMBER PRIMARY KEY ,grade_id NUMBER ,sal NUMBER); CREATE TABLE sal_inc...

This code should be like this : sp_id = 'SP602' to sp_id = ''SP602'' and this '2' to ''2'' your final code should be like this Update RATOR_MONITORING_CONFIGURATION.SYSTEM_SQL_CHECK SET CHECK_SQL = 'select count(*) as CNT from O2_SDR_Header where id = (select max(id) from O2_SDR_Header where id > 2012000000000000 and sp_id...

You could use CTE to define your null values and then pivot the data something like this: ;WITH t AS ( SELECT isnull(jan, 0) AS jan ,isnull(feb, 0) AS feb ,sum(data) AS amount FROM your_table --change this to match your table name GROUP BY jan,feb ) SELECT * FROM (...

Have you checked both the Tablix Properties and the Column Group properties for sorting? The Column Grouping's sort takes Priority...and additionally will automatically include a clause to sort on the same field that the Group is on, which usually accounts for confusion like this in my experience.

Whenever I have to calculate a field and use the results in a second field, I use a common table expression to make the first calculation. Given this query, it could look like this: with cte_preprocess as ( SELECT tbl_ShiftSched.Shift, tbl_ShiftSched.SortInd, [ShiftStart]+ case when @dteFTm>[shiftstart] then DateAdd(day,1,@dteShf) else @dteShf end...

It's called a "one-to-zero-or-one" relationship, as one Line might be associated to zero or one TestPacks. You can implement it by using a FK that allows NULL values. CREATE TABLE TestPack (id INT, PRIMARY KEY (id)) CREATE TABLE Line (id INT, TestPackId INT NULL, FOREIGN KEY (TestPackId) REFERENCES TestPack(id)) By...

This is simple minimal example. You can use INSERT EXEC statement. The key is to have table variable declared inside and outside dynamic query. At the end of dynamic query just select from table variable and insert resultset into outside table variable: DECLARE @t TABLE ( id INT ) DECLARE...

SQL Server will implicitly convert data types according to data type precedence rules as documented in the Books Online https://msdn.microsoft.com/en-us/library/ms190309.aspx. Integer has a higher precedence so string literals will be converted to integer in your examples. The downside is a little more work for SQL Server to do but the...

While there are other ways and other improvements that could be made, you should be able to simple move part of your logic into a common table expressions (CTE): WITH cte AS ( select case when username in (select username from database0254.dbo.userinformation) then 1 else 0 end As [Active User]...

Having zeroth row is related to business rule or specifications. For example, you have a data warehouse where you are loading data for customer and by mistake your customer haven't provided any country name then you might need to provide him a default value (which could be anything, in your...

Have a look at this and tell me if this works for you - I've added a try catch to check if the command is timing out. Just few tips: I would always use a try catch because it'll tell you exactly what you need to know and why your...

Well the error messages are clear, no ? SELECT Transport.ID, Transport.Address, SUM(CASE WHEN Active='True' THEN 1 ELSE 0 END) FROM Cars LEFT JOIN Transport ON Cars.TransportFrom=Transport.ID --add all the fields from select clause which are not in an aggregation function GROUP BY Transport.ID, Transport.Address ORDER BY address ...

I just found I missed setting up hibernate dialect on LocalContainerEntityManagerFactoryBean. After setting up org.hibernate.dialect.SQLServer2008Dialect as hibernate dialect, the GenerationType.IDENTITY works fine as Neil mentioned. Below is my spring data configuration and application.properties. FYI I have multiple datasource, one is H2 embedded datasource for internal use and the other is...

Could you try this: MERGE TableA AS [Target] USING TableB AS [Source] ON [Target].[ID] = [Source].[ID] AND [Target].[Name ] = [Source].[Name] WHEN NOT MATCHED BY TARGET THEN UPDATE SET NameMod = 1; It is using the MERGE clause. If you do not like the clause, you can use CTE like...

Consider using extended properties to store meta-data. This is much cleaner than parsing the module text. EDIT: The example below returns all parameters plus descriptions for those parameters with extended properties. In your code, you can pass the schema and object names as parameters instead of the local variables used...

You can use FOR XML to concatenate update x set attended_conf = (select e.key_value + '; ' as "text()" from TX_CUST_KEYWORD e where e.customer_no = x.customer_no and e.keyword_no = 704 order by e.key_value for xml path('')) from #work x ...

you can use case statements in an update statement as so: update acc1152 set [columnName] = case [columnName] when '7007' then '4007' when '7008' then '4008' -- etc when '4512' then '4012' else [columnName] end Or a better way (IMO) would be to use a temp/variable table to keep track...

my reputation not reach 50 so cant add comment. you said your other order is correct then priority = 7 is also correct. you can compare priority 2 and 4 with 7. its the same thing. i think all of your loop for orderqty only reach 10 times where priority...

I'm assuming that shift 1 is suppose to start at midnight and shift 2 starts at noon, but its sometimes off by some amount of time. This query should work if those assumptions are true. I've made a variable called @ShiftFudgeHours to account for how off the shift start times...

Found out what was occurring, when I programmed the datagridview to show the searched data it did not change the bindingsource but left it the same from the Form Load (). this.log5TableAdapter.Fill(this.roboticlineDataSet.Log5); To resolve this I found the RoboticLineTable in the Dataset.xsd location in the Soultion Explorer. Here I selected...

You're not using Cake's standard naming conventions so you need to make sure you override model attributes that Cake would normally auto-generate. Cake expects the primaryKey to be id which it uses to determine whether to insert or update a record. As you're using eventDate_id you need to tell Cake...

Try this: SELECT Sizes FROM Items ORDER BY CAST(LEFT(Sizes, PATINDEX('%[a-z]%', Sizes)-1) as numeric(9, 2)) that's assuming your data will always be number followed by at least one alphabetic char. sql fiddle (thanks to matt!)...

You can do a JOIN operation in an UPDATE statement. For example: UPDATE communication c JOIN commLocation l ON l.timestamp = c.timestamp AND l.fromidnumber = c.fromidnumber SET c.fromx = l.x This assumes that (timestamp,fromidnumber) is unique in commLocation. For developing a query like this, we usually start with a SELECT...

Update d Set totalenteredtoday = te.IdCount FROM prodinformation As d JOIN ( Select [someJoinAttribute] ,Count(ID) As IdCount From @location Where entrytype IN ( Select validentrytype From vet With (Nolock) Where ltrim(rtrim(entrydate)) = @entrydate ) Group By [someJoinAttribute] ) As te On d.[someAttribute] = te.[someJoinAttribute] here [someJoinAttribute] would be the column/attribute...

At least one of your issues is this line: SELECT /*snip*/ sum(InitialActualCount + (KeyCardCount * x.Activity)) OVER (partition BY CustomerId ORDER BY RowNumber) AS TotalActualCount /*snip*/ The ability to add an ORDER BY clause in an OVER clause with an aggregate (SUM in you case) was added in SQL Server...

OK so if the SQL query does not have results then NULL is returned and, in essence, nothing is added to the $dbResults array. Instead lets append the results to a custom object. I don't know what PowerShell version you have so I needed to do something that I know...

Based on the schema you are providing, I will assume that you find all the products of each document based on 1. which document_group the document is in 2. which product_type the document_group is associated with If that is the case, this is what your query would look like: SELECT...

The answer is that if you are going to use any format then make sure its an ISO-type format such as yyyy-MM-dd. where END_DATE between '" + asOfDate.AddDays(-30).ToString("yyyy-MM-dd") + "' and However, the far more important point is use parameterised queries, not string concatenation. When you do this, a date...

Check for constraints or triggers that would attempt to insert a value too large for a given column. This can happen when over time schema changes occur, and constraints or triggers escaped the scope of impact review. In this case (varchar(3)) column status had a default constraint that was attempting...

The problem is, that the time is in the SQL server stored as datetime. So the field in the linked table is a datetime. When the time is stored as a time(7) in the SQL server, the field in the linked table will get a text. And then the select...

As Mark Sinkinson wrote in his comment, the IN() operator expects a comma separated list of arguments, but you have supplied it with a single argument that contains a comma separated list of values. Change this part of the stored procedure: and (recv_email in (@recvemail ) ) to this: and...