Like this? It seems easiest just to do it as three separate statements. DELETE FROM Products WHERE SubCatId IN (SELECT SubCatID FROM SubCategory WHERE MainCatId = @mainCatId); DELETE FROM SubCategory WHERE MainCatId = @mainCatId; DELETE FROM MainCategory WHERE MainCatId = @mainCatId; 8 solved Delete query to delete data from multiple tables in sql server and … Read more
You are overcomplicating things with the use of the aggregate function MAX(). This part of your code: EXISTS ( SELECT 1 FROM ( SELECT MAX(TourCompletionDateTime) AS TourCompletionDateTime FROM Details WHERE TourCompletionDateTime < ‘2022-07-26T09:36:00.730589Z’ ) WHERE TourCompletionDateTime IS NOT NULL ) is equivalent to just: EXISTS ( SELECT 1 FROM Details WHERE TourCompletionDateTime < ‘2022-07-26T09:36:00.730589Z’ ) … Read more
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
You have to count only owners id with DISTINCT word, which counts only unique owners. SELECT owners.city, count(DISTINCT owners.id) AS ‘Owners’ FROM owners INNER JOIN dogs ON (owners.id = dogs.owner_id) GROUP BY owners.city 1 solved Mysql Query [0001]
Problem is in your odbc connection goto ControlPanel->AdministrativeTools->DataSource(ODBC)->System DSN->ADD->SqlServer-> then in the name field give the Source Name. you have to use this name instead of testdb in your DriverManager.getConnection method. Because getConnectionMethod take the source name not the database name. so your code is not working. However after filling the source name fill the … Read more
The method I have used here is a Tally Table to create extra rows. I JOIN onto the tally table where the number of hours / 24 (integer maths is useful) is greater than the tally number, and then can use that to calculate the hours. WITH YourTable AS( SELECT * FROM (VALUES(1,CONVERT(date,’2018/01/24′,111),4 ), (2,CONVERT(date,’2018/03/21′,111),40), … 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
The solution is to join on the common fields you already indentified: SELECT item_details.* FROM item_details JOIN item_detail_addon USING(Item_Details_Id) JOIN item_addon USING(Item_Addon_Id) If some fields on a table have the same name of a field on another table, you can get both by using aliases: SELECT table1.field1 as table1_field1 , table2.field1 as table2_field1 [ .. … Read more
Use ANSI SQL’s || instead to concat: SELECT FIRST_NAME || ‘,’ || LAST_NAME as full_name FROM EMPLOYEES; (CONCAT() function takes two arguments only.) 2 solved CONCAT in sql developer [duplicate]
Using CASE Condition and Row_number we can achieve the above Output It’s Purely based on your sample Data DECLARE @Table1 TABLE (projName varchar(1), percentage int) ; INSERT INTO @Table1 (projName, percentage) VALUES (‘A’, 10), (‘A’, 25), (‘B’, 20), (‘B’, 30) ; Select CASE WHEN RN = 1 THEN projName ELSE NULL END projName, percentage from … Read more
Select t2.account_number,t2.proposedaddress, t1.currentaddress From table2 t2 left outer join table1 t1 on (t1.account_number=t2.account_number and Upper(Trim(t1.currentaddress)) like Upper(Trim(t2.proposedaddress))) 5 solved Combine each column from 2 different table into one table
Thanks to all, i have worked out with my same query, although seed was slow. query is mentioned below:- select Item, Location,[Transaction Date],Price,Qty, Price_change , (select isnull(sum(qty),0) from #temp c where c.item=p.item and c.Location=p.Location and c.[Transaction Date] < p.[Transaction Date]) [Opening Stock] from #temp p solved MS SQL Query for Opening Stock calculation
To use a resultset in a query condition for a set of queries you need a cursor. Please check out basics of cursor usage here and in the docs DELIMITER $$ CREATE PROCEDURE group_results_by_date BEGIN DECLARE v_finished INTEGER DEFAULT 0; DECLARE cdate DATE DEFAULT “2015-01-01”; — declare cursor for getting list of dates DEClARE date_cursor … Read more
Is this what you are looking for? select ProdID, min(Qualified), case when min(Qualified) <> max(Qualified) then ‘YES’ else ‘NO’ end as Has_different_results from VarAnn_Data.dbo.tblOwnership group by ProdID solved Sql Query condition based on column can holded bit