I have huge export files in a DB and i need to check if there are any datasets that have the same value in the first column, but a different in another one, via a query of course.
Like this:
ID IS NULL
1 1
2 1
3 0
1 0
The expected ID i get as a result of my query should be 1 in this case.
I need to select specific values from all rows where the value of a specific column is "Active"
This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client
But I want to add: WHERE StatusType = (Active) and how to do this.
I am looking for SQL query which uses 2 tables CASH and BALANCE.
eg: Need Tablename, ColumsList and data in the results set.
eg: 10 rows shown below and ordered based on Acct_number
Row1,CASH,ACCT_NUMBER,AMOUNT,DEBIT_CREDIT_FLAG,ENTITY,BUSINESS_DATE,CURRENCY,REFERENCE,TRADE_TYPE,SUB ACC CODE
Row2,BALANCE,ACCT_NUMBER,OPENING_BALANCE,CLOSING_BALANCE,CLOSING_BAL_DEBIT_CREDIT_FLAG,BUSINESS_DATE,CURRENCY
Row3,CASH,10,500,CR,ABC,12/12/2014,USD,INTL,,US05
Row4,CASH,10,1000,DR,DEF,12/12/2014,USD,DOM,,US07
Row5,CASH,10,75,DR,XYZ,12/12/2014,USD,DOM,,US05
Row6,BALANCE,10,500,750,DR,12/12/2014,USD
Row7,CASH,20,500,CR,ABC,12/12/2014,USD,INTL,,US05
Row8,CASH,20,1000,DR,DEF,12/12/2014,USD,DOM,,US07
Row9,CASH,20,75,DR,XYZ,12/12/2014,USD,DOM,,US05
Row10,BALANCE,20,500,750,DR,12/12/2014,USD
I have a table with following rows.
FY REVCODE Jul Jun
2015 BNQ 1054839 2000000
2015 FNB 89032 1000000
2015 RS 1067299 3000000
I am looking to convert it to
Month BNQ FNB RS
JUL 1054839 89032 1067299
JUN 2000000 1000000 3000000
I tried with the following and result is coming for one month i.e. JUL but not with the second Month i.e Jun
SELECT 'Jul1' AS MON, [BNQ], [FNB], [RS]
FROM
(SELECT REVENUECODE, SUM(ROUND(((Jul/31)*30),0)) AS JUL
FROM RM_USERBUDGETTBL
WHERE USERNAME='rahul' AND FY=2015
GROUP BY REVENUECODE, USERNAME
) AS SourceTable
PIVOT
(SUM(JUL) FOR REVENUECODE IN ([BNQ], [FNB], [RS])) AS PivotTable
Results:
MONTHBNQ FNB RS
Jul11054839 89032 1067299
I have a DB with some tables and, on certain tables, i've a column named "ID_COMPUTER".
I need to find all the rows where id_computer=<specific_value> from all the tables of my database, when column "ID_COMPUTER" exists.
Pseudo-code would be: select * from db.* where id_computer=<specific_value>
how to write this ?
how to do a check for 2 columns. As long as there is data for at least one of the columns I want to return rows.
Example Data
create table test
(
ID int,
set1 varchar(50),
set2 varchar(50),
[code]....
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
EMP_ID INT,
Title varchar(50),
DateValue DATETIME,
TITLE_YEAR INT,
[code]....
I am new to this level of coding in SQL SERVER 2012, but I am looking to update the TITLE_YEAR field in the temp table with the Year the employee is in that title. For example for employee 11127 the data should look like this:
EMP_IDTitle DateValue TITLE_YEAR
3 Senior Consultant 2009-01-01 00:00:00.0001
3 Director 2010-01-01 00:00:00.0001
3 Director 2011-01-01 00:00:00.0002
3 Director 2012-01-01 00:00:00.0003
3 Director 2013-01-01 00:00:00.0004
3 Senior Director 2014-01-01 00:00:00.0001
I have a table :
id + A + B
1 |0,11| 0
2 |0,45| 0,5
3 |0,85| 0,75
I need to calculate the following :
F = 0,85 * ( 1 - 0,75 * ( 1 - 0,45 * ( 1 - 0,5 * ( 1 - 0,11 * ( 1 - 0 )))))
In which F = A3 * ( 1 - B3 * ( 1 - A2 * ( 1 - B2 * ( 1 - A1 * ( 1 - B1 )))))
It seemed quite easy at first glance. I Built it up via string concatenation and thought to execute the dynamic sql with sp_exec and get the result. As I don't like dynamic sql I was wondering If there is any other way..
ALTER PROC [dbo].[Tools_Serial_FE]
AS
BEGIN
DECLARE @IP FLOAT,
@IF FLOAT,
@FE FLOAT,
[Code] ....
I have this query
SELECT top 100 Ltrim([text]),objectid,total_rows,total_logical_reads , execution_count
FROM sys.dm_exec_query_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
where last_execution_time >= '2015-04-07 10:01:01.01'
ORDER BY execution_count DESC
But the result of execution count is from the first. I want to know it only one day.
I need to do some operations on a table when any user dont work with it.How do i do it?
Do i lock the table? and is it possible?(I dont want to deny permission from a user because cause error)
Can i do it with a open transaction ?
I created a new login and then created a new user [COM] in DB with default schema pointing to [COM]
I created then schema [COM] WITH AUTHORIZATION [COM]
I want this [COM] user to have all permissions it needs on [COM] schema only. How do I do that? When I try to create table [Com].Table it gives me permission denied.
What am I missing?
I need to do some operations on a table when any user dont work with it.How do i do it?Do i lock the table? and is it possible?
View 8 Replies View RelatedI'm looking for a quick script that someone has already written to update statistics (not to rebuild or re-organise) on specific indices in specific databases - I guess loop though a table comprising of a list of databases and the indices.
I know Ola has one but I'm not look for something that is that complicated. If I cannot find one I'm going to have to write one myself - I want to try and avoid re-inventing the wheel as tomorrow I have to do this work and it's about 7K plus indices in about 10+ databases.
This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.
I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.
CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2
DECLARE @stoptime DATETIME = NULL;
[Code] ....
I have a user who needs access to views like(dbo.viewnameabc1,dbo.viewnameabc2 and so on...) dbo.viewnameabc* and anytime the user creates the view he already have the permission to view those views....
View 3 Replies View RelatedI have the following code to send out an email if the row count of a table exceeds a certain number. But it seems to fire an email alert no matter what. condition check part of the code?
use msdb
go
create table ##resultset (
total int)
[code]...
I am trying to learn how to store a web form password and than check it when the user log in. So far none of the code I can find works.
Why the following test does not work and what the correct code should be?
Insert Into [user]
values ('name', 'email', HashBytes('SHA1', 'bob'))
GO
Why does the following produce no rows?
SELECT *
From [user]
Where HashBytes('SHA1', password) = HashBytes('SHA1', 'bob')
I've been running the Ola Hallengren maintenance script for the last five months without missing a beat. Today I find an error stating the UserDatabase Integrity check job failed last night. This is running on SQL Server 2014 BI edition w/64 Gigs.
I ran a DBCC CHECKDB on each database manually and all worked until I tried it on the biggest one that is about 18 gbs. It just keeps running and I eventually stopped it so I'm guessing it is memory, but doesn't make sense considering it has 64 gbs. I have it set to 64/4 max / min. Again, this was never an issue until last night.I've been looking up all morning, but not seeing much on this error "The operating system returned error 1453"?
usual way to check if file exists
DECLARE @File_Exists INT
EXEC Master.dbo.xp_fileexist 'serverBSQL_Backupfile.bak', @File_Exists OUT
print @File_Exists
1
And if check folder, can use "nul", but it doesn't work for UNC path
DECLARE @File_Exists INT
EXEC Master.dbo.xp_fileexist 'serverBSQL_Backupul', @File_Exists OUT
print @File_Exists
0
If use xp_subdirs like:
EXEC master.dbo.xp_subdirs 'serverBSQL_Backups'
If the folder doesn't exists, Msg 22006, Level 16, State 1, Line 3 xp_subdirs could not access 'ServerBSQL_Backups*.*': FindFirstFile() returned error 67, 'The network name cannot be found.'
how to check if UNC folder exists in Backup? in my code I want to check if the unc folder exists before doing backup, the unc path is retrieved from other table or backup history.
Running Frequently Transaction logbackup during Integrinty check DB /optimization job will cause any issue /impact as duration will extend ...
View 2 Replies View RelatedI have the following setup:
- An MSSQL 2014 Standard server that houses multiple small databases (in excess of a hundred).
- These databases are frequently dropped and restored by an application that uses this SQL Server.
- There is a business need for this setup at this time, so I can't get away from it. Therefore answers like "don't have so many small databases that are frequently dropped and restored" would be somewhat unuseful
This is the problem I have:
- When I connect SSMS 2014 to the server and expand the "Databases" node, it takes forever to display. In comparison, SSMS 2008 connected to SQL 2008R2 server with the same number of databases displays the Databases tree very quickly.
I ran a trace to see what exactly SSMS 2014 is doing. When the "Databases" node is expanded, it runs a query that checks each database for Memory-Optimized Tables (new and wonderful feature of SQL 2014 for sure, but I'm not using it, at least yet). Naturally, when you have to loop through over a hundred DBs, it takes time. Worse yet, if one of these DBs is in process of being restored, the query sits and waits to time out before proceeding to the next DB. Sometimes this causes outright timeouts. Here is the query:
use [MyDatabase]
SELECT
ISNULL((select top 1 1 from sys.filegroups FG where FG.[type] = 'FX'), 0) AS [HasMemoryOptimizedObjects]
To be sure, this is NOT a SQL Server performance issue. This server processes a rather heavy workload and has been doing so for over a month, and the workload completes within expected time limits or better. Even so I've done some basic performance measuring, and the server itself is quite all right.
Moreover, if I connect SSMS 2008 to it, I get an error message (Index out of bounds or somesuch), but SSMS 2008 does connect, and displays the Databases tree much faster than SSMS 2014.
I'd like to turn off the option to check for Memory Optimized Objects altogether, as I'm not using the feature.
I have a table with some examples below. I need to identify records where:
1. the person has 3 or more consecutive months ordered.
2. I then need to exclude the first and last month and capture only those Months in between.
PERSON ID MONTH ORDERED
JD12345 4
JD12345 7
JD12345 8 Note: JD12345 should be excluded entirely
[code]....
Here's what my table looks like;
UniqID | Code |
1 | ABC
1 | 123
2 | ABC
3 | ABC
4 | ABC
4 | ABC
I only want to UniqIds that only have the CODE of ABC... and if it contains ANYTHING other than ABC then It doesnt return that UniqID... Now keep in mind there's multiple different codes.. I'm just looking for a bit of code that drops any ID's that don't have my criteria.
How can i calculate datediff (in minutes) between two consecutive rows without using CTE & Temp tables?
I was using this successfully, but i need something without CTE and temp tables (both of them are not supported in the tool i am trying to generate a report using custom query).
WITH CTE AS (SELECT ROW_NUMBER() OVER (ORDER BY Id desc) AS RowNo, * FROM MyTable)
SELECT t1.*, ISNULL(DATEDIFF(mi, t2.CreateDate, t1.CreateDate), 0) AS Duration
FROM CTE t1
LEFT JOIN CTE t2 ON t1.RowNo = t2.RowNo - 1
ORDER BY t1.Id desc
Select statement joining file1 to file2. File 1 may have 0, 1, or many corresponding rows in file2. I need to count the corresponding rows in table2. Table2 also has a Boolean column and I need to count the number of rows where it is true. So I need to count the total number of matching rows and the count of those that are set to true. This is an example of what I have so far. I had to add each column being selected into a Group by to make it work, but I do not know why. Is there some other way this should be set up.
SELECT c.CarId, c.CarName, c.CarColor, COUNT(t.TrailerId) as trailerCount, (add count of boolian, say t.TrailerFull is true)
FROM Car c
LEFT JOIN Trailer t on t.CarId = c.CarId
GROUP BY c.CarId, c.CarName, c.CarColor
I have the below table :
IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/2015NULL 23:00.0 NULL
10xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0
11xxxxx@gmail.com 15/06/2015NULL 00:00.0 NULL
12xxxxx@gmail.com NULL 15/06/2015 NULL 00:00.0
13xxxxx@gmail.com 14/06/2015NULL 00:00.0 NULL
14xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0
Then i need to replace second row with value with first row with null and so on :
IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/201514/06/2015 23:00.0 00:00.0
10xxxxx@gmail.com NULL NULL NULL NULL
11xxxxx@gmail.com 15/06/201515/06/2015 00:00.0 00:00.0
12xxxxx@gmail.com NULL NULL NULL NULL
13xxxxx@gmail.com 14/06/201514/06/2015 00:00.0 00:00.0
14xxxxx@gmail.com NULL NULL NULL NULL
I need to compare two consecutive rows (if BEGDA of second row is 1 day greater than ENDDA of first row then I need to pick First row BEGDA and 2nd row ENDDA)
Input Table Data:
PERNRSUBTYBEGDAENDDA
1010429001/1/20081/15/2015
10104210001/1/200812/31/2008
10104210001/1/200912/31/2009
10104220001/1/20081/15/2008
10104220001/16/200812/31/2008
10104220001/1/200912/31/2009
10104230001/1/200812/31/2008
10104230001/1/200912/31/2009
10104230001/5/201012/31/9999
101042DDPP5/16/200712/31/2007
101042DDPP2/16/20075/15/2007
101042MAPP2/1/200712/31/2007
101042VISI3/1/200712/31/2007
Output should be like this:
PERNRSUBTYBEGDAENDDA
1010429001/1/20081/15/2015
10104210001/1/200812/31/2009
10104220001/1/200812/31/2009
10104230001/1/200812/31/2009
10104230001/5/201012/31/9999
101042DDPP2/16/200712/31/2007
101042MAPP2/1/200712/31/2007
101042VISI3/1/200712/31/2007
I'm trying to append rows horizontally - I'm using the "xml path" approach
SELECT
E.[USER_NAME] As 'User Name',
(
SELECT ',' + C.[PERMISSION_NAME]
FOR XML PATH('')
) As [Associated Groups]
FROM TABLEA As A
JOIN TABLEB AS B ON A.PK_OBJ_ID = B.FK_APP_OBJECT_REF
JOIN TABLEC AS C ON C.PK_PERMISSION_ID = B.FK_PERMISSION_REF
JOIN TABLED AS D ON D.FK_PERMISSION_REF = C.PK_PERMISSION_ID
JOIN TABLEE AS E ON E.PK_PERSONNEL_ID = D.FK_PERSONNEL_REF
WHERE A.[OBJECT_NAME] = 'MyObjectName'
It's not working. I'm getting:
User nameAssociated Groups
A. SmithG1
A. SmithG2
A. SmithG3
etc...
What I'm looking for is:
User NameAssociated Groups
A. SmithG1, G2, G3
etc...
Hi,
I have two FormView, and I need to know if the DataSet contains specific fields to display it in the first FormView or in the Second FormView.
Is it possible?
Hi,
I would like to now how can I check if a specific package is running now, at the moment.
the reason I need it is because I enable in a web page to run ssis package using web servise. I would like to be sure that no more than 1 person is starting the package until it completes.
Does any one has any idea?
Iram
I have a table of languages, identified by a lang_id column as follows:
LANG_IDLANG_NAME
deDeutsche
enEnglish
plPolski
trTurkish
czCzech
I also have a RESOURCES table of phrases (for translation purposes) similar to this:
res_id res_lang res_phrase
AccessDenied en Access Denied
For some rows in the resources table I do not have all language codes present so am missing some translations for a given res_id.My question is what query can I use to determine the RESOURCE.RES_IDs for which I do not have a translation for.
For example I might have a de, en, cz translation for a phrase but not a pl phrase and I need to identofy those rows in order that I can obtain translations for the missing RESOURCE rows.
I'm a web developer who writes transact-SQL to make my web applications run properly. I'm not real strong in other areas of SQL. Let me explain our set-up and then I'll explain what I want to do:
We have an ecommerce web site and all sales are saved in a SQL Server 2008 R2 database at our hosting company. We also have a local Windows 2012 network that has SQL Server 2014 Express installed.
Here is what I want to do:
I want to copy sales rows from the SQL Server 2008 database at our hosting company and save them in the SQL Server 2014 Express database on our local Windows 2012 server. I'd like to automate this if possible so that it happens each night perhaps. I know there is a way to schedule SQL jobs but I've never actually done this. I also would need to know how to attach to our hosting company DB as well as our local network DB.
I have a Job that creates .CSV files at predetermined intervals (the delimiter is a tabstop) and every 5 minutes I have to retrieve These files one at a time and Import them into the relevant database.
My solution was to create a list of the files and insert this list into a temporary table where a Cursor would extract each filename one at a time and insert that Name into a BCP Statement. The BCP Statement is constructed using dynamic SQL.
Here the Cursor:
create table #filelist(filename nvarchar(100))
insert into #filelist exec xp_cmdshell 'dir C:BCP_Test /A-D /B'
declare @filename nvarchar(100)
declare @path nvarchar(100)
declare @bcp_open nvarchar(100)
[Code] ....
The code seems to work but although there are around 50000 lines in the .csv file, None of the lines are inserted.