SELECT Category, Tag1Value FROM table_name t1 WHERE EXISTS (SELECT 1 FROM table_name WHERE Tag2Value = t1.Tag1Value) UPDATE Try this : SELECT res.Category, res.tag, COUNT(res.tag) FROM (SELECT DISTINCT Category, Tag1Value tag FROM table_name UNION ALL SELECT DISTINCT Category, Tag2Value tag FROM table_name) res GROUP BY res.Category, res.tag HAVING COUNT(res.tag)>1 It return : category | tag ———————————– … Read more
If you store Ids as comma separated string – you always have TABLE/INDEX scan. If your table is small it can be enough. With SecondaryTable table which stores Ids associated with main table there a lot of other plans: You can leave as is and trust or not DB Engine optimiser query = query.Where(x => … Read more
It’s because a CASE WHEN can only return 1 value. And a STRING_SPLIT returns a resultset. I assume something like this is what you want. SELECT * FROM Facility f WHERE (@Facility IS NULL OR f.facilityCode IN (SELECT value FROM string_split(@Facility,’,’))) This will get all records if the variable is null. 2 solved Can I … Read more
This should do it. If CategoryNAME coming back as null, then you’re doing a LEFT OUTER join and have referential integrity issues. CREATE PROCEDURE usp_OrderDetails_SelectByOrderId @OrderID INT AS BEGIN SELECT P.ProductId, OD.Qty, P.Price, C.NAME AS CategoryNAME FROM tblProducts P INNER JOIN tblCategories C ON P.CategoryId = C.CategoryId INNER JOIN tblOrderDetails OD ON P.ProductId = OD.ProductId … Read more
OK. So you’ve solved the issue of WebAPI not running out of RAM. But the next concern is that you’ve got to pass the data to SQL Server. You can pipe the request stream through any of the .NET stream encryptors, but then it’s gotta go somewhere after that. I suggest you do something like: … Read more
There are several query patterns that will achieve that result. An example of one of those patterns, using an GROUP BY operation, with aggregation of a condition (to exclude the groupings where there’s a row in the grouping that has a status value of ‘AC’) SELECT t.id , t.state , t.group FROM mytable t GROUP … Read more
You could use a recursive common-table expression to walk up until you find a parent with a contact. For example: ; with CTE as ( select ID , ContactID , ParentID , ID BaseID , 1 as Level from Location where ID in (4,5) union all select parent.ID , parent.ContactID , parent.ParentID , child.BaseID , … Read more
If it’s an ordinary transactional database, you can go for a data warehousing solution for your reporting purposes. Data warehouses are usually more efficient in these type of situations. solved Database growth – How to handle with big tables [closed]
ORDER BY clause is used here as per desired output ordering. It’ll meet expectation. — SQL Server SELECT f.Primary_Brand_Key , t.Market , f.Food , t.TV_Spends , t.Print_Spends , COALESCE(t.TV_Spends, 0) + COALESCE(t.Print_Spends, 0) “Total_Spends” FROM Food f INNER JOIN ( SELECT Primary_Brand_Key , Market , SUM(CASE WHEN Medium = ‘TV’ THEN Spent END) “TV_Spends” , … Read more
declare @timediff table(id int identity(1,1),Name varchar(50),logtime datetime) insert into @timediff Select Name,logtime from timediff declare @datetimeDiff int declare @i int=1 while(@i<=(Select count(*) from @timediff)) Begin Select @datetimeDiff=datediff(hour,logtime, (Select logtime from @timediff where id=@i+1)) from @timediff where id=@i if(@datetimeDiff>=1) BEGIN Select @datetimeDiff –You can write your update code here END Set @i=@i+2 END 0 solved Need … Read more
This will help. how-to-read-data-from-an-excel-file-starting-from-the-nth-row-with-sql-server-integration-services Copying the solution here in case the link is unavailable Solution 1 – Using the OpenRowset Function Solution 2 – Query Excel Sheet Solution 3 – Google It Google it, The information above is from the first search result 3 solved How to handle this scenario in single SSIS package?
So you have Col1 in File1, Col2 in File2 and Col3 in File3 and you need to import them all Col1, Col2, Col3 into one table. You are correct, you need to use Merge Join which needs a column to join on. You do need to do some intermediate steps, there’s no direct way to … Read more
You can search for a table name in a single database like follows: USE YourDatabaseName SELECT * FROM sys.Tables WHERE name LIKE ‘%SearchText%’ You can also search for a table name in all databases like Pinal Dave describes here: CREATE PROCEDURE usp_FindTableNameInAllDatabase @TableName VARCHAR(256) AS DECLARE @DBName VARCHAR(256) DECLARE @varSQL VARCHAR(512) DECLARE @getDBName CURSOR SET … Read more
Use a sql select query similar to this…. select * from EmployeeDetails ed left join EmployeeEntries ee on ee.EmpId = ed.EmpId Note that this query will return all entries for each employee and each record will have the employee details as well. That means the employee details will be repeated for each entry the have … Read more
Both query are not similar query. Note: You have an error on your transcription the value of filter is ’10’ not ’19’. Query 1: works join the tables strategy and customgroup by groupid, so in this grup do you have all records from the result (ids:67,68,71,73,74,76,77), in the second join you create a subgroup of … Read more