Tag Info

New answers tagged

0

There are any number of ways to express this in T-SQL. One that works (without using APPLY - which you should learn to love, by the way) is below, with comments inline: WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE) AS ( SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1' ...


-1

For SQL 2008, we can use OUTER APPLY. Question about the order in which the LEAVE1 and LEAVE2 appear, if they have a particulare order ? Any how , here a sample of it. WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE) AS ( SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1' UNION ...


-1

DECLARE @SQL varchar(8000) SET @SQL = '' SELECT @SQL = @SQL + ' CAST(' + COLUMN_NAME + ' as ' + ISNULL(DATA_TYPE + '(' + CAST(CHARACTER_OCTET_LENGTH as varchar(20)) + ') )','') + ' as ' + COLUMN_NAME + ',' FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'YourPermTable' SET @SQL = SUBSTRING(@SQL,1,LEN(@SQL)-1) SET @SQL = 'SELECT ' + @SQL + ' FROM ...


0

I resolved: SELECT COUNT(achou) as total FROM ( SELECT h.acctstarttime, h.acctstoptime, h.radacctid, h.calledstationid, ( SELECT calledstationid FROM radius.radacct_historical as sub WHERE sub.callingstationid = '54:9F:13:27:31:7F' AND sub.acctstarttime < h.acctstarttime ORDER BY sub.acctstarttime DESC ...


4

It gives an error: ORA-01790: expression must have same datatype as corresponding expression From the documentation for UNION: The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same datatype group (such as numeric or character). Tested at SQLFiddle


0

Your model has two different relationships between User and Trip. They should be modeled separately. They have different cardinalities. One is the "created" relationship which is one-to-many. This is modeled by the foreign key in Trip, as you say in your question. The second is "engaged," which is many-to-many. This can be handled by creating a new table, ...


2

You can use a CTE to supply the values: with data (street, city, user_id) as ( values ('street1','LA', 2) ) insert into addresses(street, city, user_id) select * from data where exists (select * from users where users.id = data.user_id and users.storeaddress = true); Or alternatively a derived table: insert ...


0

To remove database mirroring Connect to the Database Engine of either mirroring partner. From the Standard bar, click New Query. Issue the following Transact-SQL statement: ALTER DATABASE database_name SET PARTNER OFF where database_name is the mirrored database whose session you want to remove. The following example removes database mirroring from the ...


4

Assuming you want randomish ordering of your output you can just ORDER BY NEWID(). That would jumble up the ordering of the results reasonably well. If you only need to randomize after a certain order is established then you can still use the NEWID() trick. Below are some quick examples: -- Fully random ordering SELECT * FROM dbo.Foo F INNER JOIN ...


3

This answer is wrong, because the value for rownum is assigned before the order by is executed. Below is the correct one, SELECT start_date, end_date, resort_id, ROWNUM FROM (SELECT start_date, end_date, resort_id FROM employee_activity WHERE employee_id = 27 ...


0

I would break these queries and have result stored in temporary tables. I would use a stored proc. Have sub-queries like this among many tables will create constrain and potentially some lockings. Also using > on a column in query will for Mysql to do a scan on that column (even if it is indexed). Avoid >,>= or < <= (use between).


0

I think you're looking at something like this: Select A.Max_id, harddrives.id As id1, harddrives.serial_number, brands.name, harddrives.size, location.name As name1, encryption_type.name As name2, resource.name As name3, backups.start_time, backups.end_time FROM ( Select backups.resource_id, Max(backups.id) As Max_id FROM ...


4

To give you a flavour of JOINs and SQL, I created two tables - Customer and Cust_Order as shown. I then loaded these tables with data (see end of post for DML). These examples use both PostgreSQL and MySQL. A note on table names. I use singular names - you can, of course, use plural (as many do) - but decide and stick to one! A word of advice (and see ...


0

harddrives.id As id1 Update it to (Max)harddrives.id As id1 I think that should work.


1

I just read this article adressing this topic and I suppose you might be interested, because it uses the lag function to solve exactly your Task: https://www.mssqltips.com/sqlservertip/3690/identify-sql-server-database-growth-rates/ The Author created the following Script using the LAG Window function: SELECT DISTINCT A.[database_name] , AVG( ...


0

I just read this article adressing this topic and I suppose you might be interested: https://www.mssqltips.com/sqlservertip/3690/identify-sql-server-database-growth-rates/ The Author created the following Script using the LAG Window function: ----------- --Script 3: ----------- SELECT DISTINCT A.[database_name] , AVG( A.[Backup Size (MB)] - ...


0

Create a table with a select * from a large table and where 1=0. Gather stats, save them using dbms_stats and then apply those stats to the large table. You will essentially tell Oracle that the large table has no data, which would favor full table scans. You can also try switching the optimizer mode to first_rows_1 or invalidating some indexes.


1

Without a lot more info, etc. couldn't you just create an EngageTrips table, and create an CreatedTrips table and hook a foreign key into those and then join the data once the function is run to bring that data for view to the end-user? Also meaning control how the data is entered into each table at the data entry level based on criteria when the data is ...


0

select 1 from dual where regexp_like('%.,"''?!:#$&)(*;+-/<>=@[]\^_{}|~', '%\.,"''\?!:#\$&\)\(\*;\+-/<>=@\[\]\\\^_\{\}\|~'); This gives, as expeced, 1. You must escape .?$)(*+[]\^{} and | You do not need to escape , (but it works nonetheless): select 1 from dual where ...


2

There's probably something more elegant than this, but since you are doing a kind of pivot (transforming from columns to rows, i.e. metadata to data) it is never going to be pretty. Assuming a table like: create table t ( ts timestamp not null , EngOilP_sd smallint not null , CompOilLVL_sd smallint not null ) engine = innodb; insert into t (ts, EngOilP_sd, ...


3

Given the additional information that Aaron Bertrand didn't have access to when he posted his answer I would suggest a different tack. Instead of putting logic/business significance in table names I would have general table names and put the logic/business significance in attributes/data in the tables. This should make it easier to expand functionality, ...


2

Probably easiest would be an INSTEAD OF INSERT trigger. CREATE TRIGGER dbo.FixTable1 ON dbo.Table1 INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; DECLARE @offset INT; -- decimal? Something else? SELECT TOP (1) @offset = offset_m FROM dbo.Table2 -- WHERE ...? ORDER BY [timestamp] DESC; -- terrible column name btw INSERT ...


4

Another reason why you should use CONSTRAINTs instead of some inside-application-code: What happens if a developer / dba use an insert / update / delete statement to modify the data direct in the DB? In this case all your nice application based referential integrity will be useless. I know, some devs like the possibility to modify data direct without ...


3

Something like this (untested) should work for you: select *, case q20 when '8' then 1 when '9' then 1 else case q46 when '8' then 1 when '9' then 1 else 0 end end as promoter from ##csat_resp; You could also write the same logic as: case when q20 in ('8','9') or q46 in ('8','9') then 1 else 0 end or in SQL ...


1

Here's how I've done it. I don't check every server, but it could be adapted to do so without much hassle I guess. -- I used the same method as written by Brent Ozar Unlimited in sp_Blitz to get the DBCC date. -- All credit for that goes to them fof that. -- I wrote the rest of it, so similarities to code, living or dead, is unintentional. declare ...


4

Method 1 : Comes with a cost You can use a third party software which will do everything for you in terms of gathering the data and presenting the reports for database growth and predicts the same depending upon the gathered data as explained here Method 2: Create table, run the stored proc using SQL agent job and scheduled accordingly to gather the data ...


1

The way you have the database setup seems fine. I am not sure this is actually a DB admin question as much as it is a front end question. MySQL or MariaDB would be a fantastic DB to use this on. You can also use MySQL Workbench, which is free, to manage the DB if you want. I think for something this simple, I would just use the command line MySQL ...


0

The operator I think you're looking for is the UNION. SELECT a.column1 b.column2 FROM table1 a LEFT JOIN table2 b ON a.table1=b.table2 WHERE a.column=10 AND b.date>'2014-10-12'---(This condition should apply only for a.column1) UNION SELECT a.column1 b.column2 FROM table1 a LEFT JOIN table2 b ON a.table1=b.table2 WHERE b.column=11 AND ...


0

It's not clear what you actually want, but this is a direct translation: select a.column1 b.column2 from ( select column1 from table1 a where a.column=10 and b.date> date '2014-12-10' --(This condition should apply only for a.column1 ) a left join ( select column2 from table2 where b.column=11 and d.date> date '2012-12-12' ...


0

You have 2 different searches so you have 2 different queries. Quite simple. This works only if you have a single row. SELECT MAX(x.column1) AS column1, MAX(x.column2) AS column2 FROM ( SELECT a.column1, NULL AS column2 from table1 a join table2 b on a.table1=b.table2 where a.column=10 and ...


0

You want to move the WHERE clause for the OUTER JOIN table into the ON clause. Like this: SELECT a.column1 b.column2 FROM table1 a LEFT JOIN table2 b ON a.table1=b.table2 AND b.column=11 AND b.date>12/12/2012 AND b.date>10/12/2014 WHERE a.column=10 When you have an outer join if you put the conditions on the OUTER table ...


0

SELECT (SELECT a.column1 FROM table1 a LEFT JOIN table2 b on a.table1=b.table2 WHERE (a.column=10 and b.date>10/12/2014)), (SELECT c.column2 FROM table2 c WHERE c.table2 = a.table2 and c.column=11 and c.date>12/12/2012) FROM table1 You could try an nested SELECT.


6

The following assumptions have been made (some of them possibly repeating parts of your description): SF/FLEX is always followed either by a number immediately or by a space character and then a number. There is always one space character before SF/FLEX, unless the item is at the beginning of the string. There is always a comma or a space after the number ...


2

Is it possible to redesign your schema? It feels like you are making life harder for yourself by basically trying to pivot the data you're importing from the excel spreadsheets. CREATE TABLE dbo.Hardware -- hw? ( [Event_Num] INT NOT NULL PRIMARY KEY, [Name] NVARCHAR(100) NOT NULL, [Install_Date] DATE NULL, -- Install after pulling? ...


2

Well, apparently this is it: SELECT *, SUM(amount) OVER ( ORDER BY tx_date DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS running_balance FROM transactions ORDER BY tx_date DESC; Output: id | memo | amount | tx_date | running_balance ----+-----------------+--------+------------+----------------- ...


4

I would begin with a little tweak: In your current design, there is no need to have "id" column on vocabularylists table. I will write it from top of my head, so apologies in advance for any syntactic mistakes. :) First change: CREATE TABLE IF NOT EXISTS `vocabularylists` ( `name` varchar(20) UNIQUE NOT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, ...


6

Instead of thinking about "type of join based relationship multiplicity" you should start thinking about the type of join based on what you want to happen if there isn't a corresponding relationship. This is a much more useful place to start. See this post by Jeff Atwood. As you can see in the sample results next to his queries, inner joins reduce the ...


0

MATCH(...) AGAINST('+tommy' IN ...) AND ... LIKE '%tommy -%' That is, use FULLTEXT to do what it does best (search for words), then check the results against something more complex via LIKE or REGEXP. Granted, it will take some intelligence in your app code to construct the suitable combination of MATCH and [R]LIKE. But the result is fast and cover a ...


2

If Shanooooon's suggestion is not enough, then SELECT (stuff from x, plus picture stuff) FROM ( SELECT ... (everything except `picture` stuff) LIMIT 30 ) x LEFT JOIN pictures ON x.picture_id = pictures.id This helps because it will reach into pictures only 30 times, not 1000 times.


1

SELECT u.usrid, u.username, u.rank, u.extras, x.dtime FROM ( SELECT userid, MAX(datetime) dtime FROM log GROUP BY userid HAVING dtime < NOW() - INTERVAL 1 YEAR ) AS x JOIN users u ON u.usrid = x.userid WHERE ( u.rank = 'P' OR u.extras LIKE '%W%' ) ORDER BY ...


1

All you need to do is ORDER BY startDate ASC first, then featured DESC: SELECT a.* --, -- c.id as catid, -- c.cat_name FROM eventlisting a -- LEFT JOIN categories c ON a.cat_num=c.id WHERE a.startDate >= '2015-08-21' AND a.endDate <= '2016-08-21' AND a.status=1 GROUP BY a.id ORDER BY a.startDate ASC, a.featured DESC LIMIT ...


-1

SELECT *, case when town='Townsville' then 'Townsville' else null end as Town FROM Users All towns besides Townsville would be null. Problem solved.


1

If my understanding of your question is correct and you are only wanting to return records from tbloffer if there are also records in tblRecord matching those buyer ids, then you should be able to add a clause within your join so that it will only return those records. I think this will cover it for you. SELECT COUNT(DISTINCT r.RecordID) AS Lines , ...


1

I agree with NReiligh but if you still want to extract values use combination of CHARINDEX and SUBSTRING functions of SQL SERVER SELECT [PEvent] ,[PDate] , [PDescription] ,CASE ------------------------------------------For SF Values WHEN [PDescription] LIKE '%SF%' THEN CASE substring([PDescription], ...


13

Another way. SELECT U.* FROM dbo.Users U CROSS APPLY (SELECT Town INTERSECT SELECT 'Townsville' ) CA; (Repurposing Paul White's example data)


5

Here is a idiotic entirely logical way to do it that I don't see yet.... SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- Our important work should be all the database cares about GO BEGIN TRANSACTION DECLARE @MyTableVar table(<all columns in order from the user table>, oldtown VARCHAR(50)); UPDATE users SET town = N'Townsville' OUTPUT ...


5

Well you could do this: SELECT A.* FROM Users A INNER JOIN Users B ON A.Id = B.Id AND B.town = 'Townsville' Strictly speaking you're not using the WHERE clause


5

Here is an example using a common table expression (CTE). with Town as ( select 'Townsville' as Town ) select * from Users u join Town t on u.Town = t.Town


16

Data DECLARE @Example AS table ( UserName varchar(30) NULL, Town varchar(30) NULL ); INSERT @Example (UserName, Town) VALUES ('Aaron', 'Not Townsville'), ('Bob', 'Not Townsville'), ('Charles', 'Townsville'), ('Charles', 'Townsville'), ('Charles', 'Townsville'), ('Charles', 'Townsville'), ('Dan', 'Townsville'), ...


6

You have two different things here. SELECT * FROM Users WHERE town = 'Townsville' Will restrict the number of rows you get back to just those where town=Townsville SELECT *, town('Townsville') FROM Users Is going to pass the literal Townsville to a function called town. It will not restrict the rows returned by the query and in fact if the function ...



Top 50 recent answers are included