Hot answers tagged

5

@Lennart's answer can be simplified to a single Group By: SELECT LogDate, StartOfDay, EndOfDay, DATEDIFF(s, StartOfDay, EndOfDay) AS ElapsedSeconds FROM ( SELECT -- instead of FORMAT better use a Standard SQL CAST: CAST(LogDateTime AS DATE) AS LogDate, MIN(CASE WHEN EventCode = 1 THEN LogDateTime END) AS StartOfDay, ...


5

Both queries are exactly the same, should return exactly the same result set and should produce exactly the same Execution plan. BUT the second query is using ANSI-92 SQL syntax . The first one is using the older (very old) version of SQL Joins. I can think of a few reasons why you should use the ANSI-92 SQL Syntax. It separates the Join Conditions ...


4

Based on your image, something like the following will be fine (using a RDBMS solution): create table articles ( article_id integer PRIMARY KEY, title varchar(256) NOT NULL, url varchar(256) NOT NULL, summary varchar(256) NOT NULL, body varchar(4000) NOT NULL ); create table article_comments ( comment_id ...


3

This is not really database administration. The way you do it cannot be done in most programming languages too. Calculate the discount as 95% of the drink price: 2.75*0.95


3

You have a couple of options: 1) try the XML datatype and its methods (eg .nodes, .query, .value) or use OPENXML to drill down to the lowest level then then drill back up using the parent axis operator ('..'). There are pros and cons to each method (which you could research) but try both options with your data and see which works best for you. OPENXML ...


2

You can do it with: SELECT u.id, u.given_name, u.family_name, u.email, COUNT(1) as num FROM users u JOIN tasks t ON (u.id=t.user_id) WHERE t.created_at BETWEEN %s and %s GROUP BY u.id, u.given_name, u.family_name, u.email, (DAYS_AGO_7_CONFIG,YESTERDAY)) To address the question in the comments, you can add a HAVING clause after GROUP BY ...


2

The query posted by @mrdenny didn't seem to return reliable data for me. I had more luck this way. select b.TABLE_TYPE, a.* from INFORMATION_SCHEMA.COLUMNS a LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES b ON a.TABLE_CATALOG = b.TABLE_CATALOG AND a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.TABLE_NAME = b.TABLE_NAME


2

Since you don't mention any specific DBMS I'll assume first that there are no window functions: select A.Id, A.ColA, B.Id, B.ColA from T as A join T as B on A.Id + 1 = B.Id where A.ColA = 'A1' and B.ColA = 'A2' Assuming that window functions do exists: select Id, ColA, Lead_id, LeadColA from ( select Id, ColA , Lead(id) over ( order by ...


2

It's almost certainly going to be full backups or indexing. The queries won't show up as a bunch of memory allocated but rather as intensive disk IO. If you are tracking those counters then have a look. Some other easy ways to confirm that these are the cause of that disk IO: Looking at agent job schedules. If it wasn't checkdb, or indexing, then apps ...


1

Lennart's first query produces a result like this (i.e. side-by-side): id|cola|id|cola 1|A1|2|A2 8|A1|9|A2 If you want a result like this (i.e. in a more "tabular" format), which might be better? id|cola 1|A1 2|A2 8|A1 9|A2 then use this query: SELECT t1.id, t1.cola FROM tab1 t1 JOIN tab1 t2 ON t1.id + 1 = t2.id WHERE t1.cola = 'A1' AND t2.cola = 'A2' ...


1

Try Something like if you wish to use joins : select name from table1 t1 inner join table2 t1 on t1.id=t2.id where t2.status = 'closed'; or select name from table1 t1, table2 t2 where t1.id=t2.id and t2.status = 'closed';


1

What is probably happening is that SQL Server wants to download the whole remote table to your tempdb before attempting to join to the local table, including the XML column. Try something like this: SELECT TA2.ID FROM [DB].[dbo].[table1] TA2 INNER JOIN ( SELECT col1, col2, col3, CAST(xmlCol AS varchar(max)) AS xmlCol FROM [linkedsrv].[DB].[dbo].[...


1

Make your where clause operate conditionally based on your input value like this: SELECT URYRR_FORWARDS_REC_H.CB_LINE as "CB_LINE", URYRR_FORWARDS_REC_H.CB_LINE_DESCRIPTION as "DESCRIPCION", URYRR_FORWARDS_REC_H.UYU_BALANCE_FW as "MONEDA LOCAL", URYRR_FORWARDS_REC_H.USD_BALANCE_FW as "MONEDA EXTRANJERA", URYRR_FORWARDS_REC_H.UYU_BALANCE_COA as "MONEDA LOCAL"...


1

You could do both a union of the tables and an intersection of the tables and then get the difference between these. create table table1 as (select 1 id, 'Aron' name, 23 age from dual); insert into table1 values (2,'Bary',30); create table table2 as (select 1 id, 'Aron' name, 21 age from dual); insert into table2 values (2,'Bary',30); ( select * from ...


1

It would appear so. I have not tried these, but the following is the list of items that can be added to an SSDT SQL Server Database Project: Broker Priority Contract Endpoint (ServiceBroker over TCP) Endpoint (SOAP over HTTP) Event Notification Message Type Queue Remote Service Binding Route Service


1

You can modify your query to: SELECT LogDate, MAX(StartOfDay), MAX(EndOfDay), DATEDIFF(s, MAX(StartOfDay), MAX(EndOfDay)) FROM ( Select LogDate, StartOfDay = MIN(case when EventCode = 1 THEN LogDateTime END), EndOfDay = MAX(case when EventCode = 2 THEN LogDateTime END), EventCode FROM ( Select ...



Only top voted, non community-wiki answers of a minimum length are eligible