Means If ID is same for two or more than two records then difference between first row's EndDate and second row's StartDate is 1 day then we should get one record as output. How can we built this logic in T-SQL ?
Which works fine, but what I need to calculate the total duration of a request based on the duration of the tasks completed in the request based on Req_ID. I would like to use the CASE statement I have to determine the SLA_Mins for each task and add them together to get total request SLA_Mins.
Below is the create table schema and data
GO /****** Object: Table [dbo].[MidrangeOtherSourceControl] Script Date: 06/03/2015 18:13:15 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[MidrangeOtherSourceControl]( [Req_ID] [float] NULL, [Service_Name] [nvarchar](255) NULL,
I have a table that holds log event records, that keep getting appended. I need to get the duration of the each event 0 which has other events before and after. How can I do this - to get event duration of event 0 and the cumulative.
ID Event  Date 1   1       2015-06-21 21:01:44.457 2   1       2015-06-21 21:01:44.457 3   0       2015-06-21 21:02:04.780 4   1        2015-06-21 21:02:32.600 5   0       2015-06-21 21:02:57.967 6   1       2015-06-21 21:03:30.513
I am storing duration of a lot of jobs in a column in a table per job. This duration is in seconds and an integer datatype.
Sample data: Job     Duration   date Job1    25      2015/9/23 Job2    30      2015/9/23 Job3    45      2015/9/23 Job4    1       2015/9/22
Now I need to get average duration per day for every month. Is this possible? I have a calendar table that has every single day month year microsecond millisecond  second minute and hour.
Hi, I have had this problem for a while and have not been able solve it.
What im looking at doing is looping thru my patient table and trying to organise the patients in to there admission sequence, so when patient "A" comes in and is treated at my hospital and is discharged and admitted to another Hospital within one day then patient "A" will get a code of 1 being there first admission.
then if patient "A" is admitted again but there admission date is greater than one day they get a code of 2 being for there second admission but will need to loop thru table looking for other admissions and discharges.
The table name is Adm_disc_Match_tbl
Basically what i have 4 fields. Index_key = which is the patient common link (text) ur_episode = this wil change for each Hospital (text) Admission_datetime = patient admission date and time (datetime) Discharge_datetime = patient discharge date and time (datetime)
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField from MyOptions where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField from MyTimetable where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
I have the table below, and I am trying to retrieve TileIDs that have the same ModelIDs.
ModelID TileID HP DL380 G3 120v Dual15400 HP DL380 G3 120v Dual15400 HP DL380 G3 120v Dual15400 HP DL380 G3 120v Dual15400 HP DL380 G3 120v Dual15400 HP DL380 G3 120v Dual15400 Sun SF 280R 120v 15401 Sun SF 280R 120v 15401 Sun SF 280R 120v 15401 Sun SF 280R 120v 15401 Lantronix MSS4 15401
So TileID ‘15400’ would be a keeper, since all ModelIDs are the same.
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT Student.StudentSurname + ', ' + Student.StudentForename AS Fullname, HomeworkLogLine.HomeworkLogLineTimestamp, HomeworkLog.HomeworkLogDescription, ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).Â
Hi All, I am trying to find records when searchProductCount = 2 AND when searchProductCount < 2 BUT productID not in (select pid from TableB) ... I have the query below ... but is there any other better way to do this? TableB has IDs: 100, 700 ...etc eg: searchProduct ID is 50,100 -- means returns everything when we found ALL productID (50,100) from TableA, count =2 Select productName, productID from TableA where searchProductCount = 2 AND productID IN (50,100) union all -- when not all productID found in TableA, we only return productsID from TableA which ID found in TableB, count < 2 Select productName, productID from TableA where searchProductCount < 2 and productID IN (select pid from TableB) -- in this case, pid found in TableB from searchProductID will be 100 ------------------------------------------------------------------ it comes out there are duplicates results (when first query is valid, we union all second query, so we have duplicates records). How can we eliminate the duplicates? Or is there better way to acheive this without using union all?
I was wondering if anyone can tell me an easy way to find duplicate records on sql. The thing is this, at work we have a database (table) which includes tracking numbers, I need a easy way to be able to search this table for duplicate tracking numbers and print them out. I currently access this table to edit some data by using the following path “Start > Programs > Microsoft SQL Server > Enterprise Manager” then work my down the tree to “Databases > Master > Tables” on tables I do a right click and “open table/query”. Any help would be most appreciated. Believe me I’m very “SQL illiterate”
I have this query that I have been using to find duplicate records works great except for now. The logic I am adding is pcs_rreas <> 'NG'. When I add this it does take out the NG, but it also excludes the reocords that have NULL data in this field. I don't want that to happen. How can I fix this? I tried adding (pcs_rreas <> 'NG' or pcs_rreas is null) but nothing is pulling now.
I have a question regarding SQL Server Performance and would be grateful for a tip. Let's say I have a DB with 50.000 records. These records belong to 1.000 different datasets, so there is 1 actual and 49 historical data records. For example a company with 1000 employees has a database where each year a new record is created for each employee so after 50 years they have 50.000 records (50 years x 1000 employees). 1 record is actual, and 49 are historical. What is the best way to store this? Main target is performance for the enduser, so when an employee clicks "See all my records" it should be fast. But on the other hand the application mainly works only with the current year. Additionally it should also be fast for the boss of business unit who wants to see the latest records of his e.g. 100 employees. I have some ideas and would like to get your opinion:
1. Retrieve by latest date Just store the records. To get the current year just select the record with the latest year. Disadvantage might be with larger databases: If the company switches to store the requests per month, each user would have 600 records (12 months x 50 years). Each time the latest record should be retrieved, 600 recards have to be compared regarding the latest date (or sorted by date descending using Top1, but this might be a problem for the boss then? Or could this be combined for a group if the boss wants to see all the latest records of his employees?).
2. Add a 'IsCurrent'-Flag Each time a new record is stored it should be compared to the latest record. If it is newer, the 'IsCurrent'-Flag should be removed and then checked on the new record. This should be fast processed (because on saving a new record it only needs to be checked against the currently 'IsCurrent'-flagged record), and for retrieving the current record no further comparison is necessary. But how could I do this? I need to update the "AddRecord"-SP with this comparison, but I don't know how to do this.
Currently number 2 is my favorite, I just don't know how to do it ;-) What is your opinion about it, and could you include an example?
I would like to find all the records that contain only digits. So far, I have this:
SELECT * FROM tmp1 WHERE (word LIKE N'[0-9]')
It returns only ten results, each containing a single digit. What I need is to find all the records of any length containing only digits, like '378', '2005', etc. but not records containing both digits and other stuff (e.g. letters) like 'I95' or 'P2P'.
Version: Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
Problem:
Server: Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 515532920. The text, ntext, or image node at page (1:377289), slot 1, text ID 897099563008 is not referenced. Server: Msg 8964, Level 16, State 1, Line 1 Table error: Object ID 515532920. The text, ntext, or image node at page (1:377447), slot 1, text ID 897100939264 is not referenced. <... and 4 same errors>
DBCC results for 'CC_Document'. The repair level on the DBCC statement caused this repair to be bypassed. <same messages>
The repair level on the DBCC statement caused this repair to be bypassed. There are 192 rows in 6 pages for object 'CC_Document'. CHECKTABLE found 0 allocation errors and 8 consistency errors in table 'CC_Document' (object ID 515532920). repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (crmproded.dbo.CC_Document repair_fast).
I won't run checktable with 'repair_allow_data_loss' before all records aren't find out
Child fragment at Page (1:377237) Slot 0 Offset: 8080
Child fragment at Page (1:377238) Slot 0 Offset: 16160
Child fragment at Page (1:377239) Slot 0 Offset: 24240
Child fragment at Page (1:377288) Slot 0 Offset: 32320
Child fragment at Page (1:377289) Slot 0 Offset: 36572
There are 5 links at Slot 1 , that's generate error. But why? What does it mean - "The text, ntext, or image node at page (1:377289), slot 1, text ID 897099563008 is not referenced" ???
I saw other pages (not 'broken') - there is: Blob Fragment don't contains links - links are in other pages.
What are these 'broken' records in my example - they are chunks of lost information, that i can't recover?
Or not? May be exists a way, that i find all 'broken' records? Because, my table contains 192 records. But there is BLOB field - and i can't check consistency of every file, that uploaded in my table. In addition I will fully appreciate this problem.
At last, if i find record, i run checktable with repair_allow_data_loss, then upload file again.
i have a table below like : Id   ,   Request  Â
int    nvarchar(100)   and in Request field i put below data : 1 <request><F3>353535</F3><F6></F6></request> 2 <request><F5>353535</F5><F6></F6></request>3 <request><F3>353535</F3><F6></F6></request>
now i need to a query that i can find records that exists <F3> and if exists , remove just the <F3> tag
 below like : 1 <request><F6></F6></request> 2 <request><F5>353535</F5><F6></F6></request>3 <request><F6></F6></request>
Hello friends, I have a one problem, i have a table in that some reocrds are duplicate.i want to find which records are duplicate. for exp. my table is as follows emp_id emp_name 1 aa 2 bb 3 cc 1 aa 3 cc 3 cc and i want the result is like emp_id emp_name 1 aa 1 aa 3 cc 3 cc 3 cc
On a webform, I have three button ... [7 days] [15 days] [30 days]When the user clicks one of the buttons, I want to return their orders for the past X days. The WHERE clause would include something like this (for 7 days):WHERE (Order_Date BETWEEN CONVERT(DATETIME, GETDATE() - 7, 102) AND CONVERT(DATETIME, GETDATE(), 102))How do I parameterize the number of days?Thanks,Tim
I want to be able to use a query to display all the records in the 6.5 database that have no data in the STATUS field. This is the query I thought would work....."SELECT * from travel_date WHERE status="''"
But, that is not working. Can someone please help me figure out the right way to wrtie this?
I need to be able to find certain data as the user has submitted that data twice and delete one record,except that have hundreds of tables and don't know the table where she would have submitted the data, but I have some other key info that I can start with.
My question is,how do i select a record from the database if I don't know the table it comes from?
Patients (patid) have multiple records. I am trying to find the lowest nadcd4 for each patient along with the associated cddate. I can do a min(nadcd4) and find the lowest nadcd4 by patid but I can't seem to get the associated cddate. Any suggestions? Thanks
I have 2 tables say table1 and table2 with the same structure. Each recordis identified by a field 'SerialNo'. Now there should be a total of 500000records in both tables with serialno from 1 to 500000. Either a record is intable1 or table2. I want to find records (or SerialNo's) that are inneither table (if deleted by accident etc). What would be the sql query?I'm using SQL 6.5thx
I have a table that has Serial numbers and MSDSID numbers and many other fields in the table.
What I need to figure out is if the table contains a distinct Serial number with different MSDSIDs.
So If I have in the table
Serial         MSDSID         Date         Size... 001                20            1/1/2015       5 002                 21            1/1/2015       3 001                22            2/1/2015       1 003                21           3/1/2015       1 004                23           1/15/2015      5 003                22            1/20/2015     6 004                23           2/21/2015      5 002                21           4/25/2015     4
I would like the results to show:
Serial           Count 001                 2          the count is 2 because Serial 001 has an MSDSID of 20 and 22 002                 1           the count is 1 because Serial 002 only has MSDSID 21 003                  2           the count is 2 because Serial 003 has an MSDSID of 21 and 22 004                 1          the count is 1 because Serial 002 only has MSDSID 23
It would be even better if the results just showed where the count is greater than 1.
I want to send an email twice a day, from database. So I have planned to make a storedproce which will be called by a job (which will select some record from one table and put it in other table based on a flag) but I want to run it in a transaction so that if email is send successfully then only it should commit else it should rollback.
How can i find that "Mail queued" now i should commit.
I have looked around quite a bit, but mostly what I have found is looking to see if a table is used or if a column is in a stored procedure and honestly most of what I have seen does not work.
I want to reduce our nightly import by removing any columns that are not being used. We insert into our staging tables, Stage1 for example. And say Stage1 has column1 and column2. If those columns are not being used, then I want to remove them from Stage1. The only catch is that every Stage1 table has a v_Stage1. v_Stage1 should have all the columns from Stage1, but doesn't always. So I need to know what columns from Stage1 are used somewhere other than v_Stage1 and what columns from v_Stage1 are not used.Â
