Tag Info

New answers tagged

-1

SELECT COSTUMER_ID, SUM(AMOUNT) AS Total FROM TableName WHERE DATEPAID BETWEEN '20120101' AND '20121231' GROUP BY COSTUMER_ID HAVING SUM(AMOUNT) >10000


0

Do it with: update table1 set col1_temp = to_char(col1, '99.99')||'%'; Also be careful. Your col1 can hold bigger values than 99.99. These values will give ###### as a result and also the value will be rounded on 2 decimals by the to_char command.


3

% is not a valid format specifier for numbers. See the documentation below: Number Format Models


2

You can do this with a case statement: select flow, min(case when type = 'requester' then name end) as name, min(case when type = 'holder' then enterprise end) as enterprise from the_table group by flow order by flow; The aggregate function reduces the multiple rows for each flow value to just one and ignores null values - which is what ...


2

A foreign key can't be made conditional so that is out of the question. The business rule appears to be that an employee can work for one and only one physical store. Given that, the super type of store has two sub-types as you suggested: Physical and Online. Each physical store may be staffed by one or more employees, and each employee must be assigned ...


0

If this is a simple constraint issue on a FK then you might want to consider a CHECK CONTRAINT. Something similar to the below might work. CREATE TABLE Employee ( <Your Columns>, TransStoreId integer CHECK (TransStoreId != 2) -- Assuming 'ONLINE_TYPE' id is 2 );


0

Here is a working example. There is a very common function that gets used in SQL databases called fnSplit. It's not native, but I have seen it added to databases many times. It's a table-valued function that takes a string and returns a table (splitting the string on some delimiter). The code for the function is below: CREATE FUNCTION [dbo].[fnSplit]( ...


3

I use charIndex and patIndex to resolve it. CASE WHEN PatIndex('%, [0-9][0-9]% Stage%',Descr) > 0 AND LEN(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1))>=3 THEN RIGHT(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1),3) ELSE NULL END AS NewNo, CASE WHEN PatIndex('%, [0-9][0-9]% Stage%',Descr) > 0 AND ...


5

I would like to use the Rnk column in the WHERE clause You have to use CTE (Common Table Expression) USE SMSResults go ;with CTE as ( SELECT Student_No,Result,Module_Name,Semester,Year,RANK() OVER (PARTITION BY Student_No ORDER BY Semester DESC) AS Rnk FROM tbl_results WHERE Student_No = '201409') select * from CTE where rnk > 1 -- change here ...


4

One way to do this is do a select back to the table and sum the rows before the row, and then use that as an inner query to be able to use the sum in the where clause, something like this: select * from ( select P1.ID, P1.CUSTOMER_ID, P1.DATEPAID, (select sum(P2.AMOUNT) from PAYMENT P2 where P1.CUSTOMER_ID = P2.CUSTOMER_ID ...


1

If you want previousRank to end with a comma SET @r=0; UPDATE ranks SET previousRank=CONCAT(previousRank,rank,','), rank= @r:= (@r+1) ORDER BY points DESC ; If you do not want previousRank to end with a comma SET @r=0; UPDATE ranks SET previousRank=IF(previousRank='',rank,CONCAT(previousRank,',',rank)), rank= @r:= (@r+1) ORDER BY ...


5

You just need to use GROUP BY: SELECT mt.TeamMemberUserID ---, <all those crazy SUM expressions> FROM dbo.timesheet AS t INNER JOIN dbo.Managetasks AS mt ON t.[Task ID] = mt.TaskID -- ... <WHERE clause> ... GROUP BY mt.TeamMemberUserID; If you need the user's names, there are several ways to do this, here's one: SELECT u.UserName --, <all ...


1

You need to use the ESCAPE clause: SELECT * FROM table WHERE column LIKE '%\%%' escape '\'; You can use any character for escaping the % sign: SELECT * FROM table WHERE column LIKE '%~%%' escape '~';


5

For special characters (% and _), you need to escape them: WHERE column LIKE '%[%]%'; or WHERE column LIKE '%\%%' ESCAPE '\'; This is covered in the documentation for LIKE, in the sections "Using Wildcard Characters as Literals" and "Pattern Matching with the ESCAPE Clause." Your first stop should always be the official documentation.


3

This can be done with a partial index. A bit complicated in Oracle because it doesn't allow a where clause in the create index statement (as e.g. Postgres does). But you can still trick Oracle in only indexing a sub-set of the rows by exploiting the fact that entries where all index columns are null are not put into the index. So you create a a unique ...


7

I think it is a VERY bad plan to be generating HTML via SQL. There are many many ways this can go wrong, without even mentioning the maintenance nightmare that this will inevitably cause. All that being said your solution might be as simple as adding the following (hover over the yellow block to see answers): The reason why I think you are having ...


4

First, create a temporary table (not a table variable) to hold the results: SET NOCOUNT ON; GO CREATE TABLE #results ( db SYSNAME, [schema] SYSNAME, [table] SYSNAME, [column] SYSNAME, ColumnValue NVARCHAR(3640) ); GO Now, the following gets a little convoluted, because this is like peeling an onion. We need to build dynamic SQL commands that ...


3

Below code will help you : -- Ref: http://stackoverflow.com/a/7892349/1387418 -- for XML Path technique learned from Michael Eriksson : http://dba.stackexchange.com/a/61855/8783 set nocount on declare @SQL nvarchar(max) declare @tableName sysname = N'yourTableNameGoesHere' --- CHANGE HERE !! declare @schemaName sysname = N'dbo'------------------------- ...


6

Sounds like a simple cross join: select a.id, b.id from input_table a cross join input_table b where a.id <> b.id;


8

DECLARE @table SYSNAME = N'Personal Information', @schema SYSNAME = N'dbo'; CREATE TABLE #sz ( dbname NVARCHAR(255), fullname NVARCHAR(768), [rows] SYSNAME, reserved SYSNAME, [data] SYSNAME, index_size SYSNAME, unused SYSNAME ); DECLARE @sql NVARCHAR(MAX) = N'DECLARE @t NVARCHAR(512);'; SELECT @sql += N'IF EXISTS ...


4

I have a script that was easy to adapt and may work for your needs. You'll simply need to replace dbo.yourTableName with the appropriate table (including schema name). The script is commented, but the general summary is to loop over all databases where you have SELECT permissions, check the number of rows and size of the table (if it exists), and add a ...


7

Since you are using datetime datatype, you need to understand how sql server rounds datetime data. ╔═══════════╦═════╦═════════════════════════════╦═════════════════════════════╦══════════╦═══════════╗ ║ Name ║ sn ║ Minimum value ║ Maximum value ║ Accuracy ║ Storage ║ ...


1

It may not be the most efficient method but I can't see a problem with your SQL. Recommend you check your data..


12

As several others have mentioned in comments and other answers to your question the core issue is 2015-07-27 23:59:59.999 is being rounded to 2015-07-28 00:00:00.000 by SQL Server. Per the documentation for DATETIME: Time range - 00:00:00 through 23:59:59.997 Note that the time range can never be .999. Further down in the documentation it specifies the ...


6

Implicit Conversion I supposed posted_date data type is Datetime. However it does not matter whether the type on the other side is Datetime, Datetime2 or just Time because the string (Varchar) will be implicitly converted to Datetime. With posted_date declared as a Datetime2 (or Time), the posted_date <= '2015-07-27 23:59:59.99999' where clause ...


5

It is rounding select cast('2015-07-27 23:59:59.999' as datetime) returns 2015-07-28 00:00:00.000 .998, .997, .996, .995 all cast / round to .997 Should use select * from A where posted_date >= '2015-07-27 00:00:00.000' and posted_date < '2015-07-28 00:00:00.000' or where cast(posted_date as date) = '2015-07-27' See accuracy ...


9

The attacker will get the data unencrypted. The T in TDE stands for "transparent". The user will never see encrypted data. The database transparently decrypts it when it is read from disk and transparently encrypts it when writing to disk. If your application is insecure, TDE doesn't help you plug those application security holes. You need to fix those ...


3

SELECT location, employee, min(created_at) AS start_ts, max(created_at) AS end_ts FROM ( SELECT location, employee, created_at , row_number() OVER (PARTITION BY location ORDER BY created_at) - row_number() OVER (PARTITION BY location, employee ORDER BY created_at) AS grp FROM employee_logs ) sub GROUP BY location, employee, grp ...


3

I filled the database with LOTS of data and I did some interesting findings. Using these filtered indexes returns results in around 7 seconds in my system, compared to over 1 minute with the nonfiltered indexes suggested by Kin. create nonclustered index [fnc_PettyExpenses] on [dbo].[PettyExpenses] ( EventRegistrationId ) include ( ...


2

Does anybody have a better alternative than this? Something faster? Your original query will do table scans for all the 6 tables. You can remove the distinct eer.EventRegistrationId and use GROUP BY eer.EventRegistrationId, rest everything remains the same. Below indexes will help you avoid the TABLE SCAN and will do an INDEX SEEK : create ...


0

I would do it with 2 steps: I would create a table template allowing to create as many tables as you want based on that table. Advantage is you only need to modify the table template, you don't need to touch the SP. 1) Create a table template; CREATE table tableName_template( ID INT NOT NULL, SNAME VARCHAR(100) NOT NULL, SSTATE ...


2

Depending on your needs the answer might be to convert each CTE in your example to an indexed view. That would save the aggregations on every query at the expense of increased storage, and a touch slower CRUD operations. The basic format would be something like: CREATE VIEW dbo.AggregatedPettyExpenses WITH SCHEMABINDING AS SELECT ...


0

You need Dynamic SQL DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `create_tb_one`(In tableName varchar(255)) BEGIN SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS ',tableName,' '); SET @sql = CONCAT(@sql,'(ID INT NOT NULL,SNAME VARCHAR(100) NOT NULL,'); SET @sql = CONCAT(@sql,'SSTATE VARCHAR(100) NOT NULL,'); SET @sql = ...


2

Yes , TDE is in a database level and the encryption is not automatically synced to the subscriber. See : Transparent Data Encryption (TDE) - Transparent Data Encryption and Replication


4

From MSDN: Replication does not automatically replicate data from a TDE-enabled database in an encrypted form. You must separately enable TDE if you want to protect the distribution and subscriber databases. Snapshot replication, as well as the initial distribution of data for transactional and merge replication, can store data in unencrypted ...


1

Another solution, close one to this before: WITH AllData AS ( SELECT ID, Descr, Qty FROM A UNION ALL SELECT ID, Descr, Qty FROM B ) SELECT ad.ID, ad.Descr, ad.Qty FROM AllData ad JOIN A ON A.ID=ad.ID JOIN B ON B.ID=ad.ID GROUP BY ad.ID, ad.Descr, ad.Qty HAVING COUNT(*) = 1 ORDER BY ad.ID Or this one. Fastest of those three but could ...


3

If I understand you correctly, you are looking for a filtered (conditional) aggregate: SELECT a.agent_id as agent_id, COUNT(a.id) filter (where disposition = 'Completed Survey') as CompletedSurvey, count(a.id) filter (where disposition = 'Partial Survey') as partial_survey FROM forms a WHERE a.created_at >= '2015-08-01' AND ...


1

Try dumping the database, recreating, verifying it's empty, and re-importing? If it does it again then check the actual file you're importing and see if it has everything listed 2x.


5

That is the schema, a container that holds primary entities like tables, views, and stored procedures. It is often used for logical or security-related separation. I often tell people that schema reference should always be included whenever mentioning any object, especially when you're using schemas other than dbo: Bad habits to kick : avoiding the schema ...


4

In this case, FullAccess is the schema of the object. You can learn more about multipart names from Books Online (scroll down to "Multipart Names" section), and there is also a good discussion about schemas in this Stack Overflow question. How are you determining that you don't have a schema named FullAccess on the database?


1

SELECT [ID Code], statusCode FROM dbo.Table AS t WHERE NOT EXISTS ( SELECT 1 FROM dbo.Table WHERE [ID Code] = t.[ID Code] AND statusCode = 'A' ) GROUP BY [ID Code], statusCode;


0

I use your code and added some checks, regarding the COST_CHANGE = 0 For Sql Server 2012, you can use the LAG (this is not available in 2008,2008R2) --my source table DECLARE @tPROD_COST TABLE (ID INT /*IDENTITY(1,1)*/, Duration INT, Cost FLOAT, COST_NEXT FLOAT, COST_CHANGE FLOAT) INSERT INTO @tPROD_COST(ID,Duration,Cost,COST_NEXT,COST_CHANGE) ...


1

This is a simple SQL: select t2.category, t2.materials, t2.shipping from categories t1, raw_data t2 where t1.cid ='1-9' and t1.category=t2.category; or select t2.category, t2.materials, t2.shipping from categories t1 join raw_data t2 on t1.category=t2.category where t1.cid ='1-9';


0

This is a runtime error. The date function has a problem to convert the date string. so display the date string to see what you want to convert: select trunc(sysdate) || '13:00:00' from dual


1

SQL> alter session set nls_Date_format = 'DD-MON-YY HH24:MI:SS'; Session altered. SQL> select trunc(sysdate, 'DD') + 13/24 from dual; TRUNC(SYSDATE,'DD' ------------------ 29-AUG-15 13:00:00


2

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 ...


3

Problem This is a more complex problem than is obvious on a quick glance. You are sorting by two columns, each from a different table, while you join on two other columns. This makes it impossible for Postgres to use the provided indexes and it has to default to (very) expensive sequential scans. Here is a related case on dba.SE: Can spatial index help a ...



Top 50 recent answers are included