The subqueries you are using in your select statement should return single value for your query to run without error. The following query will run without any issue, but it won’t give you the result you are expecting. SELECT TOP 10 (SELECT g_name FROM vgsales$ x WHERE g_platform = ‘X360’ AND a.g_rank = x.g_rank) AS … Read more
i use this SELECT COALESCE(ORK2_RESULT.K1, ORK3_RESULT.K12) AS K1 , ORK2_RESULT.SUM_K11 AS SUM_K11 , ORK3_RESULT.SUM_K3 AS SUM_K3 FROM ( SELECT K1 AS K1, SUM(K11) AS SUM_K11 FROM ORK2 GROUP BY K1 ) AS ORK2_RESULT FULL OUTER JOIN ( SELECT K1 AS K12, SUM(K3) AS SUM_K3 FROM ORK3 GROUP BY K1 ) AS ORK3_RESULT ON ORK2_RESULT.K1 = … Read more
You can use a recursive CTE: with dates as ( select cast(getdate() as date) as dte union all select dateadd(day, -1, dte) from dates where datediff(day, dte, getdate()) <= 4 ) select * from dates order by dte desc; Obviously, you can reference any other date you want instead of getdate(). Your example suggests that … Read more
You need to use JOIN to join both tables. SELECT * FROM dbo.bac A INNER JOIN dbo.data B ON B.counter = A.counter this should do it, but you need to filter your records as needed. 0 solved Merging tables in SQL Server
First, figure out which records need to be updated: select * from tbl_order o inner join tbl_group g on g.grp_id = o.grp_id inner join tbl_indicator i on i.grp_nbr = g.grp_nbr and i.sect_nbr = g.sect_nbr where g.indicat != i.indicat Now, modify the query to update those records with the correct grp_id. Notice that I’ve added an … Read more
The FROM clause in the NOT EXISTS subquery is using the source table instead of the target table. Change the table name in the NOT EXISTS subquery to the target table name: INSERT INTO swi (co, na, ci, ac, id, version, add) SELECT co, na, ci, acc, id, ?, address FROM swi_tmp WHERE NOT EXISTS … Read more
Constraint is on the table not a single statement Kind of ugly but Put it in a transaction and take a tablock begin transaction ALTER TABLE branchOffice NOCHECK CONSTRAINT ALL insert into branchOffice with (tablock) — Re-enable the constraints on a table ALTER TABLE branchOffice WITH CHECK CHECK CONSTRAINT ALL commit transation; 3 solved T-SQL … Read more
‘Transaction’ is a pair of take + return. It’s identity is computed from source data so OPERATORs could be grouped the way you need. The query may fail on data with unpaired OPERATORs. declare @tbl table ( OPERATOR int, PRODUCT varchar(50), [USER NAME] varchar(100), [TIME STAMP] datetime); insert into @tbl(OPERATOR, PRODUCT, [USER NAME], [TIME STAMP]) … Read more
Try this one – CREATE FUNCTION dbo.udf_getCountCallOnDate ( @DateFrom DATETIME , @DateTo DATETIME ) RETURNS INT AS BEGIN RETURN ( SELECT SUM(CallsTakenOnDate) FROM dbo.PhoneCalls WHERE DateOfWork BETWEEN @DateFrom AND @DateTo ) END SELECT DateOfWork = CONVERT(VARCHAR(10), t.DateOfWork, 111) , [Count] = SUM(t.CallsTakenOnDate) FROM dbo.PhoneCalls t WHERE DateOfWork BETWEEN @DateFrom AND @DateTo ORDER BY t.DateOfWork 0 … Read more
A MS SQL Server database, by default, only has a single schema (dbo). You can add schemas to group things for either security or organizational purposes. In your case, the schema apbd was created and Student was created on that schema not the dbo schema. So, to reference that table, you need to use [apbd].[Student]. … Read more
Thanks a lot, problem was – I can’t create xsd with above xml, please help me to create it. Answers were – use another version, it was created and then deleted, encoding is wrong. I found the answer myself, I needed just headings which are usually put above the xml. That much. solved Validating xml … Read more
@dlatikay! based on your first comment i changed the datatype from DateTime to string and it worked. dTable.Columns.Add(“created_on”, typeof(string)); When the datatype is DateTime,the result is coming as expected,but on insertion to sql the value is changing.Now, while iam sending the value as string to sql,it is converting that string to DateTime and it worked. … Read more
I think DATEDIFF would be your best option. I know you are looking for the difference in time within the same day, but couldn’t you just pass DATEDIFF two DateTime types with the same date, but different times? // Should return 300 DATEDIFF(mi, ‘2015-02-19 08:00:00’, ‘2015-02-19 13:00:00’) 2 solved How do I get the minute … Read more
Using max(ship_num) is a good idea, but you should use the analytic version (with an OVER clause). select * from ( select t.*, max(ship_num) over (partition by order_id) as orders_max_ship_num from table1 t1 ) with_max where ship_num = orders_max_ship_num order by order_id, item_code; 0 solved Joins and/or Sub queries or Ranking functions
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