ypercube’s suggestion is exactly correct. Here is an implementation: DECLARE @searchText VARCHAR(100) SET @searchText=”Assumed Life Claims” DECLARE @sqlText VARCHAR(8000) DECLARE @MaxId INT DECLARE @CurId INT DECLARE @possibleColumns TABLE (Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY ,sqlText VARCHAR(8000)) INSERT INTO @possibleColumns(sqlText) SELECT ‘IF EXISTS (SELECT * FROM ‘ + c.TABLE_NAME + ‘ WHERE ‘ + … Read more
It looks like you are trying to get a count of all users who registered on a certain day. If so, I think you are best off using the group keyword to group together all users by registration date. You can then create a new anonymous type to contain by the date, and the count, … Read more
Take a look in syscomments for the text and join to sysobjects for the object type and name sysobjects syscomments solved How can I find all occurrences of particular text within all stored procedures in my database? [closed]
A subquery in the SELECT can only select one attribute. I’m not sure what you are trying to do, but you’ll have to re-write to something like: SELECT NUMBER , STATUS , ID , SUM(CASE WHEN STATUS like ‘closed’ and OPEN_TIME >= @DateFrom and CLOSE_TIME <= @DateTo THEN 1 ELSE 0 END) as closen , … Read more
This meets the stated requirement (last full week of previous two months): SET DATEFIRST 1; DECLARE @s DATE = GETDATE(), @s1 DATE, @s2 DATE; SET @s = GETDATE(); — last day of last month: SET @s1 = DATEADD(DAY, -DAY(@s), @s); — last day of previous month: SET @s = DATEADD(MONTH, -1, @s); SET @s2 = … Read more
You need to group by both month and year parts of ad.xDate. There are only twelve months of the year and you should be seeing the earliest two (14 – 2) month of your results with totals that are too large because they actually represent a combination of two calendar months. It worked in your … Read more
1.create a table with some GUID as primary Key 2.do inserts and check fragmentation 3.Rebuild check Fragmentation you also can do search like below for more SO Examples sql server fragmentation site *.stackexchange.com simple Demo: create table test1 ( id varchar(255) primary key default newid() ) insert into test1 default values go 100 –check fragmentation … Read more
You could create a table-valued function to parse words and join it to your query against qQuestion. In your schema, I recommend using varchar(8000) or varchar(max) instead of text. Meanwhile, the following should get you started: create function [dbo].[fnParseWords](@str varchar(max), @delimiter varchar(30)=’%[^a-zA-Z0-9\_]%’) returns @result table(word varchar(max)) begin if left(@delimiter,1)<>’%’ set @delimiter=”%”+@delimiter; if right(@delimiter,1)<>’%’ set @delimiter+=’%’; … Read more
I’m going to take a stab at this despite there being incomplete information in your post. A chain of just AND operators will require every expression to evaluate true for every row returned. This is as fundamental as gravity. In short, AND works, it just doesn’t work as you expect it to. What is likely … Read more
Use SUM and GROUP BY. SELECT Empid, SUM([No of Days]) AS Days FROM leavetable GROUP BY Empid Read more here GROUP BY and here SUM. This would give you: Empid Days 100 25 Or if you mean count the amount of times an employee has been off use Count. SELECT Empid, Count(Leaveid) AS LeaveTotal FROM … Read more