I hope this will help you. SELECT * FROM (SELECT ID,NAME,GENRE,COUNT(LAN1)+COUNT(LAN2)+COUNT(LAN3) AS KNOWN_LANG FROM YOUR_TABLE GROUP BY NAME) WHERE KNOWN_LANG >=2 solved sql query how to get who knows morethan two lanuguage
. It’s working fine when I pass 10-15 input but taking more than 5 minutes for 30 input. I need to make it work for 100 inputs solved . It’s working fine when I pass 10-15 input but taking more than 5 minutes for 30 input. I need to make it work for 100 inputs
Something like this might work. I’m not sure on the efficiency yet – it may depend on the amount of hits that you’re looking for: create type HitBlocks as table ( HitIndex int not null ) go create procedure FindMaxCover @Hits HitBlocks readonly as ;With DecomposedBlocks as ( select (HitIndex/8)+1 as ByteIndex,POWER(2,(HitIndex%8)) as BitMask from … Read more
This can be done with a single update statement. delete from question where id = 2; with new_order as ( select row_number() over (partition by survey_id order by question_no) as new_question_no, question_no as old_question_no, id from question ) update question set question_no = nq.new_question_no from new_order nq where nq.id = question.id and survey_id = 44; … Read more
Define the business scenarios that need to be audited. Identify the code entry points where those scenarios happen Design the audit data model based on what data you want/need to store Write data in your audit table/tables on the previously identified code entry points This answer is intentionally vague. Auditing is not something that ASP.NET … Read more
Yes, the wrong part is AUTO_INCREMENT. there is no AUTO_INCREMENT in SQL Server. It should be IDENTITY. Again, you are trying to create two IDENTITY field in your table. that’s not allowed. You can have only one IDENTITY field. start and end are reserve word. You need to escape them using [] (square brackets) You … Read more
Introduction This article provides a solution to the issue of not being able to create any tables in SQL Server 2012. It explains the cause of the issue and provides a step-by-step guide on how to resolve it. It is important to note that this issue can occur due to a variety of reasons, so … Read more
Picture paints a thousand words No! Don’t post pictures of data or code! Your question is not at all clear, even with the picture. And – looking at my attempt to answer – you see, that I had to type in a test scenario. This was your job actually… For the next question please provide … Read more
create the UFN_STRING_SPLIT function suggested by @Dave here link create FUNCTION [dbo].[UFN_STRING_SPLIT] ( @List NVARCHAR(MAX), @Delimiter NVARCHAR(255) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT Item = y.i.value(‘(./text())[1]’, ‘nvarchar(max)’) FROM ( SELECT x = CONVERT(XML, ‘<i>’ + REPLACE(@List, @Delimiter, ‘</i><i>’) + ‘</i>’).query(‘.’) ) AS a CROSS APPLY x.nodes(‘i’) AS y(i) ); test your code: … Read more
I think you just need this. There is no need for the subquery. SELECT DISTINCT T0.project_number_ext as ProjectNumber ,T0.status_desc as SDesc ,T0.Project_name as PName From trimergo.rpt_getProjectPOC T0 WHERE T0.sproject_number IS NULL AND ( T1.SDesc LIKE ’10 – In Proposal%’ OR T1.SDesc like ’90-Lost Opportunity%’ ) 3 solved How to filter twice using subquery? [closed]
I think it is easier to translate SQL using query comprehension syntax instead of lambda syntax. General rules: Translate inner queries into separate query variables Translate SQL phrases in LINQ phrase order Use table aliases as range variables, or if none, create range variables from table names abbreviations Translate IN to Contains Translate SQL functions … Read more
this is Ugly, but will eventually work: COLUMN = ‘jksjdksls#$#$@@kskjfjf,”123,456,789″ lsnslkdswfnslsjfls’ left( right(COLUMN,len(COLUMN)-instr(COLUMN,””””)), instr( right(COLUMN,len(COLUMN)-instr(COLUMN,””””)), “”””) -1 ) –> 123,456,789 This is what is done: We take this string ‘jksjdksls#$#$@@kskjfjf,”123,456,789″ lsnslkdswfnslsjfls’ find the first occurence of ” with instr(COLUMN,””””) –> returns 24 take the right end of the string with. Therefore we need to take … Read more
First check datatypes: SELECT ‘0.458441’, system_type_name FROM sys.dm_exec_describe_first_result_set(N’SELECT 0.458441′, NULL, 0) UNION ALL SELECT ‘10.000000’, system_type_name FROM sys.dm_exec_describe_first_result_set(N’SELECT 10.000000′, NULL, 0) UNION ALL SELECT ‘5.000000’, system_type_name FROM sys.dm_exec_describe_first_result_set(N’SELECT 5.000000′, NULL, 0); ╔═══════════╦══════════════════╗ ║ value ║ system_type_name ║ ╠═══════════╬══════════════════╣ ║ 0.458441 ║ numeric(6,6) ║ ║ 10.000000 ║ numeric(8,6) ║ ║ 5.000000 ║ numeric(7,6) ║ ╚═══════════╩══════════════════╝ Query … Read more
Here’s how I’d go about it – I wrote it in SQL because I’m WAY better at that than I am at LINQ, but converting from one to the other should be straightforward: select t1.id , t1.value from table1 as t1 inner join table2 as t21 on t21.idTable1 = t1.id and t21.number = 1 inner … Read more