I get records from a query whose status is "Marked". I need another column to be added to result set which just says "Marked" for every record. Is that possible in query?
View 6 RepliesI have a table like the following (with much more data, but the concept is the same) with Dates and Actions for People and a column called Action with beginning Dates and end dates.
(I attached a picture because I could not figure out how to Format it)
begin Date end Date Name
begin 2014-10-15 end 2014-10-31 phil
begin 2014-09-18 end 2014-09-30 phil
begin 2014-08-21 end 2014-08-23 John
I need the query to be like this. The idea is to have the query grab the next 'END' not all Ends, which my attempts have done i.e. I get not just the closest end to the begin date, but ALL Ends with the same Person.
I Need it to look like this:
begin Date end Date Name
begin 2014-10-15 end 2014-10-31 phil
begin 2014-09-18 end 2014-09-30 phil
begin 2014-08-21 end 2014-08-23 John
There can be different People so the query Needs to return the beginning and end rows for the Person in sequential order.I can't figure out how to select only the 'next' end. My query always gets 'end' values that have a 'begin'. I
I need to remove "rows affected" text from results as shown below from posted Sp. I am using set nocount on but its not working as expected.
Create Procedure DailyCheckList
As
SET NOCOUNT ON
Declare @EmailSub varchar(500),@dt varchar(100),@Msg varchar(max),@M varchar(max)
set @dt= convert(varchar(20),GETDATE(),107)
[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.
i'm writing below this code..
exec msdb..sp_send_dbmail
@profile_name = 'local',@recipients='chintu@www.com'
, @subject ='Log Shipping Job succeeded'
, @QUERY = 'exec sp_executesql @stmt=N'exec sp_help_log_shipping_monitor',@params=N'
I want Log shipping Status Through Mail. Last back up transaction in the day and last restoration trn file in the day...
i have 70 SQL database servers and i setup DB Mail on the 70 Servers, i want to know is there a way to find the status of all the jobs which i assigned the DB Mail and if its working/failing... is there a script i can run on powershell or SQL to find out that information
View 1 Replies View RelatedI was running a stored procedure it was suspended for about 11 hours so I decided to kill it now its in Killed/Rollback stage for 12 hours and when check the status of roll back it says "Estimated rollback completion: 0%. Estimated time remaining: 0 seconds." its using up CPUTIME 380000 and DiskIO 970000. How to do I stop this co.mpletely
View 4 Replies View RelatedI need a script that will return the replication status as small int.
View 1 Replies View RelatedWe have a difficult requirement.
When records are initially created, they are given statuses of New, Pending, Closed
By default, the status is New.
If no action has been taken after 15 days since the records were created, users are sent a reminder.
I have this part working.
The problem is that as long as the status is not equal to closed, start sending reminders every 10 days.
Here is what I am working with:
SELECT [ID] , [Name], [Email],
Status
FROMmyTable
WHERE Status_Name != 'Closed' AND Status_Name IS NOT NULL and Status_Name = 'New' and Status='Pending'
AND CONVERT(Char,DateAdd(day,-15,getdate()),101) = CONVERT(Char,Date_Entered,101)
I have a table called Signatures
It contains the fields
Parentobject and [View As]
there can be more than one Parentobject so I want to concatenate them so I have them on one line
for example
901 Joe Dow
901 Jane Dow
I want one line - | 901 | Joe Dow, Jane Dow
I found something similar as below but I'm getting dups like
901 |Joe Dow , Joe Dow
901 | Jane Dow, Jane Dow
DECLARE @Delimiter VARCHAR(10) = ' '; -- this is the delimiter we will use when we concatenate the values
SELECT DISTINCT
ParentObject,
(SELECT STUFF(
(SELECT @Delimiter + s1.[View As]
FROM Signatures s2
[Code] .....
I am not able to understand why just appending blank string in below code removes '<item>' from xml result.Is it converting to varchar datatype ?
This question is just for my understanding
CREATE TABLE #tbl
(id INT IDENTITY(1,1),
item varchar(100))
INSERT #tbl
SELECT 'This'
UNION ALL
[Code] .....
I've got a piece of code that returns 53 records when using just the SELECT section.When I change it to INSERT INTO ..... SELECT it only inserts 39 records into the receiving table.There are no keys/contraints/indices or anything else on the receiving table (it's just a dumping ground for some data that will be processed later).
The code for creating the table is here:-
USE [CDSExtractInpatients6.2]
GO
/****** Object: Table [dbo].[CDS_Inpatients_CDS_Feeds_Import] Script Date: 22/05/2015 15:54:15 ******/
SET ANSI_NULLS ON
GO
[code]...
I know most of the date fields are being created as varchar on here, but this is something I inherited and the SELECT is outputting the dates as text.Don't know if it makes any difference, but the server is running SQL2008.
I am struggling with doing what should be a fairly simply transpose of columns to rows. I have found some examples but nothing I could get working.
I have data in the form of:
Brand, Model, Color1, Color2, Color3, Color4, Color5, Color6
Honda, Accord, Red, Grey, Black, White,,,
Toyota, Corolla, White, Black,,,,,
The output should look like:
Brand, Model, Color
Honda, Accord, Red
Honda, Accord, Grey
Honda, Accord, Black
Honda, Accord, White
Toyota, Corolla, White
Toyota, Corolla, Black
A simple Query to select alternate rows from a table ?
View 9 Replies View RelatedCurrently I have a table that looks like the one below and I need to concatenate the description column and keep the rest of the row the same.
current:
IDSeq Desc DateOpen DateClose
1 AA description 1 1/1/2015 12/31/2015
1 AB description 2 1/1/2015 12/31/2015
Desired outcome:
ID Desc DateOpen DateClose
1 description 1,description 2 1/1/2015 12/31/2015
I am building a query. I have a table with 4 columns and need to try and put the times together. There are some inconsistencies with this, and i'm hoping to exclude them.. Here is a sample table:
Function | Employee | DateTime
--------------------------------------------
1 | 1 | 1/30/2015 1:47 PM
2 | 1 | 1/30/2015 1:49 PM
2 | 1 | 1/30/2015 1:50 PM
3 | 2 | 1/30/2015 1:37 PM
3 | 2 | 1/30/2015 1:39 PM
3 | 1 | 1/30/2015 1:40 PM
4 | 1 | 1/30/2015 1:42 PM
4 | 1 | 1/30/2015 1:45 PM
Function 1 = Clock In Type 1
Function 2 = Clock Out Type 1
Function 3 = Clock In Type 2
Function 4 = Clock Out Type 2
Basically what I need to do is take the time from rows with Function 1 and match it with Function 2 so I can get a total time of the clock in. Function 3 rows need to match up with Function 4 rows so I can get another set of total times. There may be more clock in rows then clock out rows or more clock out rows then clock in rows, and there may be multiple clock ins & outs per day per employee. I'm basically trying to get totals for each Clock In/Out type.
I need the requirements of merging multiple rows of same ID as single row.
My Table Data:
IDLanguage1Language2Language3Language4
1001NULL JAPANESENULL NULL
1001SPANISH NULL NULL NULL
1001NULL NULL NULL ENGLISH
1001NULL NULL RUSSIAN NULL
Required Output Should be,
IDLanguage1Language2Language3Language4
1001SPANISH JAPANESERUSSIAN ENGLISH
How to achieve this output. Tried grouping but its not working also producing the same result.
I have the following information in a table. What I would like to do is pull out all the visits for each customer that are less than 30 days apart.
Customer# VisitDate
9082012-07-28 00:00:00.000
9082013-09-20 00:00:00.000
9082013-12-23 00:00:00.000
9082014-01-10 00:00:00.000
9082014-01-27 00:00:00.000
9082014-02-16 00:00:00.000
9082014-05-21 00:00:00.000
9082014-05-30 00:00:00.000
9082014-10-01 00:00:00.000
9082015-02-28 00:00:00.000
9082015-03-22 00:00:00.000
9272012-02-16 00:00:00.000
9272014-12-14 00:00:00.000
9272014-12-23 00:00:00.000
How to combine multiple rows to single rows for the below sql query.
SELECT dbo.AccessLog.RCDID, dbo.AccessLog.EMPLOYEEID, dbo.AccessLog.LOGDATE, LEFT(dbo.AccessLog.LOGTIME, 5) AS LOGTIME,
dbo.AccessLog.INOUT
FROM dbo.AccessLog LEFT OUTER JOIN
dbo.LogType ON dbo.AccessLog.INOUT = dbo.LogType.INOUT LEFT OUTER JOIN
dbo.viwEmployee ON dbo.AccessLog.EMPLOYEEID = dbo.viwEmployee.Employee_ID
WHERE dbo.AccessLog.EMPLOYEEID='10763' AND (dbo.AccessLog.LOGDATE BETWEEN '01/04/2015' AND '01/04/2015')
ORDER BY dbo.AccessLog.EMPLOYEEID
The reult for the above query is:
RCDID | EmployeeID | LOGDATE | LOGTIME | INOUT
1 10763 01/04/2015 08:00 0
1 10763 01/04/2015 19:46 1
I need the result like the below
RCDID | EmployeeID | LOGDATE | IN | OUT
1 10763 01/04/2015 08:00 19:46
Merge two rows into one (conditions or Pivot?)
I have Temptable showing as
Columns:EmpName, Code, Balance
Rows1: EmpA, X, 12
Rows2: EmpA, Y, 10
I want to insert the above temp table to another table with column names defined below like this
Empname, Vacation Hours, Sicks Hours
EmpA, 12, 10
Basically if it is X it is vacation hours and if it is Y it is sick Hours. Needs a simple logic to place the apprpriate hours(Balance) to its respective columns. I'm not sure how I can achieve this in using Pivot or Conditions.
Is there is 'fairly' simple query to delete duplicate rows from ANY table ?
A script which removes duplicates from a table, when a table_name is supplied.
I have a table where I would like to update the document number row for 3k rows. The problem I have is that the documents come in sets of two (version 1 and 2) but both have different numbers. Picture it like this below:
DOCNUM: 4445787 Version 1
DOCNUM: 4445790 Version 2
It should be the same docnum (ie 4445787 Version 1, 4445787 Version 2).
The challenge is how can we assign the new docnum for version 1 to be also for version 2 as well. Basically in SQL we need a way to
1. Find a way to distinguish the pair of documents in the target db that are the same even though they have different docnums.
2. Update them so that the docnums match.
I am trying to BULK INSERT csv files using a stored procedure in SQL SERVER 2008R2 SP3. Although the files contain several thousand lines and BULK INSERT returns no errors, no data is actually imported into the table. Every field in the table is a NVARCHAR(50) datatype.
Here is the code for the operation (only the parameters for the insert itself):
set @open = 'bulk insert [DWHStaging].[dbo].[Abverkaufsquote] from '''
set @path = 'G:DataStagingDWHStagingSourceAbverkaufsquote'
set @params = ''' with (firstrow = 2
, datafiletype = ''widechar''
, fieldterminator = '';''
, rowterminator = ''
''
, codepage = ''1252''
, keepnulls);'
The csv file originates from a DB2 database. Using exactly the same code base I can import several other types of CSV files without problem.
The files are stored on the local server with as UCS2 Little Endian and one difference is that the files that do not import do not include a BOM. The other difference is that the failed files are non-UNICODE files.
I have one view which is based on couple of tables. Here is the definition of view. Which are the options i can use to optimize the view for better performance. This is one of the view which causing issue on database.
CREATE VIEW [dbo].[V_Reqs]
WITH SCHEMABINDING
AS
SELECT purchase.Req.RequisitionID, purchase.Req.StatusCode AS Expr2, purchase.Req.CollectionDateTime,
purchase.Req.ReportDateTime, purchase.Req.ReceivedDateTime, purchase.Req.PatientName, purchase.Req.AddressOne,
purchase.Req.AddressTwo, purchase.Req.City, purchase.Req.PostalCode, purchase.Req.PhoneNumber,
[code]....
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.
Created Prod order status report, in status, we have different status
created =0
start =4
released =3
reported as finished =5
ended =7
I have the report, in report don't want to show the Prod order for ended status, how can I add the filter for this so it can show for all the other status not ended status. when I did on filter <7 , it did not work
I am trying to find an easy way to create multiple of just two date in a single sql statement.
E.G.
A statement using the parameters
@StartDate = '2015-01-01'
@EndDate = '2015-01-05'
Ends up with rows:
'2015-01-01'
'2015-01-02'
'2015-01-03'
'2015-01-04'
'2015-01-05'
What would be the best way to do this ?
I have the table as
|start || end1 |
1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 2:00 || 1/06/2015 3:00
1/06/2015 3:20 || 1/06/2015 4:00
1/06/2015 4:00 || NULL
I want the output as : -
|start || end1 |
1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 1:30 || 1/06/2015 2:00
1/06/2015 2:00 || 1/06/2015 3:00
1/06/2015 3:00 || 1/06/2015 3:20
1/06/2015 3:20 || 1/06/2015 4:00
1/06/2015 4:00 || NULL
I am trying the below mentioned code but it is not giving me the desired output..
with cte as
(select
start
,end1
,ROW_NUMBER() over (order by (select 1)) as rn
[Code] .....
I am getting wrong output as -
| start || end1 |
1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 1:30 || 1/06/2015 2:00
1/06/2015 2:00 || 1/06/2015 4:00
1/06/2015 4:00 || 1/06/2015 4:00
I have several databases to deal with, all with + 250 tables. The databases are not identical and do not conform to a specific naming convention for table names. Most but not all tables have a column called "LastUpdated" containing a date/time (obviously). I'd like to be able to find all rows within a whole database (table by table) where the date/time is greater than a specified date/time.
I'm looking for a reliable query that will return all the rows in each of the tables but without me having to write hundreds of individual scripts "SELECT * FROM [dbo.xyz] WHERE LastUpdated > '2015-01-01 09:00:00:000'", or have to look through each table first to determine which of them has the LastUpdated field.
I am trying to join two tables and looks like the data is messed up. I want to split the rows into columns as there is more than one value in the row. But somehow I don't see a pattern in here to split the rows.
This how the data is
Create Table #Sample (Numbers Varchar(MAX))
Insert INTO #Sample Values('1000')
Insert INTO #Sample Values ('1024 AND 1025')
Insert INTO #Sample Values ('109 ,110,111')
Insert INTO #Sample Values ('Old # 1033 replaced with new Invoice # 1544')
Insert INTO #Sample Values ('1355 Cancelled and Invoice 1922 added')
Select * from #Sample
This is what is expected...
Create Table #Result (Numbers Varchar(MAX))
Insert INTO #Result Values('1000')
Insert INTO #Result Values ('1024')
Insert INTO #Result Values ('1025')
Insert INTO #Result Values ('109')
Insert INTO #Result Values ('110')
[Code] ....
How I can implement this ? I believe if there are any numbers I need to split into two columns .
I have a table like this :
ID Description ParentID Level
B.01 Parent 1 H
B.01.01 Parent 1.1 B.01 H
B.01.01.01 Parent 1.1.1 B.01.01 H
B.01.01.01.01 Detail 1 B.01.01.01 D
B.01.01.01.02 Detail 2 B.01.01.01 D
[Code] .....
That means, only select Level=H, and display the last record of H with concatenated the description of each rows delimited with comma.
that violates the targets referential integrity?I am getting error Msg 2601, Level 14, State 1, Line 1Cannot insert duplicate key row in object XXX with unique index YYY.The statement has been terminated.I would like to know if there is a way to examine or determine what source rows are not conforming to the unique index.I'm fine with dropping and reestablishing the index, and i know its cataloged somewhere because during index creation, the error message does tell you the row details clobbering index creation. Ideally i would like to be able to trap all the failing rows and see what i can do about rehabilitating them or ignoring them or managing them some other way, but id like to know what the server knows when it will not create the index.
View 2 Replies View RelatedI have the table with the similar set of records which mentioned below, find the time difference between two rows of record. By Using the MsgOut column i have to find time taken b/w PS & PV and some record doesnt have PV .
LogID LocIDClientCert MsgType MsgOutMessageTimeStamp System
1151334934NOT SPECIFIEDQ_T12PS 2015-10-01 00:00:40.980AHR
1151335243NOT SPECIFIEDD_T12PV 2015-10-01 00:00:53.800AHR
1151342944NOT SPECIFIEDQ_T12PS 2015-10-01 00:05:40.957AHR
1151343281NOT SPECIFIEDD_T12PV 2015-10-01 00:05:53.670AHR
1151350046NOT SPECIFIEDQ_T12PS 2015-10-01 00:10:40.970AHR
1152760563759NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:29.617AHR
1152760739690NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:33.633AHR