We have a table setup to track changes that are made to another table, for auditing purposes. How do we compare the most recent record in the change table with the previous record in the change table? Particularly, we have a column named DUE_DATE in the change table and want to identify when the most recent change has a different DUE_DATE than the previous change made.
I am fairly new to SQL and writing queries so bear with my faults. I am learning on the job, which is good and bad. Below is a query that I have written to obtain some information. The problem arises when we have a patient who goes from Patient Type '1' to Patient Type '2'. This needs to be considered a singular visit and the only way I can think that this may work is if: for any specific medical record a dsch_ts is equal to the Admit TS on the next row.
How to complete something like this and my google searches have been fruitless. I attached a spreadsheet with an example of what I am getting.
SELECT DISTINCT TPM300_PAT_VISIT.med_rec_no, TSM040_PERSON_HDR.lst_nm AS 'Last Name', TSM040_PERSON_HDR.fst_nm AS 'First Name',
Table 1 has "Gender" field with "Male" and "Female" in it, table 2 has "Gender" field with "M" and "F" in it. a query to compare data and list the differences.
create table #myfirst (id int, city varchar(20)) insert into #myfirst values (500,'Newyork') insert into #myfirst values (100,'Ediosn') insert into #myfirst values (200,'Atlanta') insert into #myfirst values (300,'Greenwoods') insert into #myfirst values (400,'Hitchcok') insert into #myfirst values (700,'Walmart') insert into #myfirst values (800,'Madida')
-- My Second Data
create table #mySecond (id int, city varchar(20),Sector varchar(2)) insert into #mySecond values (1500,'Newyork','MK') insert into #mySecond values (5500,'Ediosn','HH') insert into #mySecond values (5060,'The Atlanta','JK') insert into #mySecond values (7500,'The Greenwoods','DF') insert into #mySecond values (9500,'Metro','KK') insert into #mySecond values (3300,'Kilapr','MK') insert into #mySecond values (9500,'Metro','NH')
--Third Second Data
create table #myThird (id int, city varchar(20),Sector varchar(2)) insert into #myThird values (33,'Walmart','PP') insert into #myThird values (20,'Ediosn','DD') select f.*,s.Sector from #myfirst f join #mySecond s on f.city = s.city /* idcitySector 500NewyorkMK 100EdiosnHH */
i have doubt on two things
1) How Can i compare the City names, by eliminating 'The ' at the beginning (if there is any in second tale city) between first and second
2) after comparing first and second if there is no match found in second them want to compare with third table values for those not found
--i tried below to solve first doubt, it is working but want to know any other wasys to do it
select f.*,s.Sector from #myfirst f join #mySecond s on replace (f.city, 'THE ','')= replace (s.city, 'THE ','')
--Expected results wull be
create table #ExpectResults (id int, city varchar(20),Sector varchar(2)) insert into #ExpectResults values (200,'Atlanta','JK') insert into #ExpectResults values (100,'Ediosn','HH') insert into #ExpectResults values (300,'Greenwoods','DF') insert into #ExpectResults values (500,'Newyork','MK') insert into #ExpectResults values (700, 'Walmart','PP') insert into #ExpectResults values (800, 'Madidar','')
i would like to see the 2014-06 matched results (3rd query), if the same ssn and acctno is exist in 2012-06 and 2013-06 and 2014-06 then eliminate from results, otherwise show it
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06' select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06' select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06'
i have written the below query but it shows only matched across three queries, but i want to display / delete from 2014-06 records if the ssn and acctno is exist in 2012-06 and 2013-06
select c.* from ( (select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06' ) a join (select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06' ) b on a.SSN = b.SSN and a.acctno = b.acctno join (select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06' ) C on a.SSN = c.SSN and a.acctno = c.acctno join )
Is there a efficient way to compare two different columns of 2 different rows in a data set as shown below.
For eg: I would like to DateDiff between Date2 of RowID 1 and Date1 of RowID 2 of IDNo 123. After this comparision , if datediff between two dates are <=14 then i want to update 1 else 0 in IsDateDiffLess14 of RowID1 . In below example its 0 because datediff of two dates >=14. So, want to compare the Date2 and Date1 in this sequence for the same IDNo. For RowID 6 there is only 1 row and no other row to compare, in this case IsDateDiffLess14 should be updated with 0.
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState 1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE 1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
RID, RType, GID 001, m, g01 002, m, g01 002, m, g02 002, m, g03 003, m, g01 003, m, g03 a, T, g01 a, T, g02 a, T, g03 b, T, g02 b, T, g03 b, T, g04
4. Group
GID g01 g02 g03 g04
I'd like to find the record in table #1 "Matter" which has exact record of "GID" in table #3 "Security Assignment" compare with table #2 "Category"
In this case, it is record of "002" bacause "002" in table#1 "Matter" and the record "a" in table #2 "category" both has exact GID records(g01, g02, g03) in table #3, "Security Assignment"
How can I create qury to find all the possible record in the table #2?
1) TableA : Which contains 5 columns(Column1,..........Column5) 2)TableB : Which contains 10 columns(Column1,..........Column10)
TableB contains millions of data.Now I want select all 5 columns from tableA but combination of Column1,Column2,Column3 if present in tableB, then i want exclude that records.I am doing as below:
select * from TableA a join TableB b a.column1!=b.column1 and a.column2!=b.column2 and a.column3!=b.column3 )
But query is taking almost 5 minutes. Is there is another approach?
Now i want to compare Result and dislay prevterm where student fail:
Now my output would be as: Now I want to compare latest term i.e. Term5 with prev Terms and if found Mismatch in result then i want to display as below:
I am asked to compare the address fields (three columns of nvarchar(100) ) of a customer database (around 10,000 records) and find any duplicates. If it is a character by character match, I could have just GROUPed and get the result.
But, I am expected to produce a list with similar addresses which the guys who entered may have use slightly different spelling or more or less characters, or a "." here and there.
I want to compare ONLY 1 Column values from 2 tables having more than 4.9 million records. There is a difference of 4000 rows between the 2 tables.
SELECT ID From TABLE1 where ID not in (SELECT DISTINCT ID From TABLE2)
My above query took nearly 4.5 hours to run and I had to cancel it. Is there a better way to write the query . I just want to compare the ID - column values which are missing in TABLE2
I have two tables I am trying to compare as I have created a new procedure to replace an old one and want to check if the new procedure produces similar results.
The problem is that when I run my compare I get false matches. Example:
CREATE TABLE #ABC (Acct VARCHAR(10), Que INT); INSERT INTO #ABC VALUES ('2310947',110), ('2310947',245);
[Code] ....
Which gives me two records when I really do not want any as the tables are identical.
--drop table #temp create table #temp (id int, idvalue int) insert into #temp(id,idvalue) select 1095,75
[code]...
I need to take the id value from maximum's id, and compare the rest id value from the table. i need to check the diffrence , if diffrence is more than 18, then i need to raise the flag as failure otherwise the whole test is success. i need to take 63 and compare rest 69,65,61,75.check the diffrence less than 18 or not.
I want to display records from @table1 only when combination of col2,col3 and col4 are present in @table2.In Below case I want output as: below two records only.
'test1', 'need this record', 25, {d '1901-01-01'} 'test3', 'some longer value', 23, {d '1900-01-01'} declare @table1 table ( col1 varchar(10) not null, col2 varchar(200) null, col3 int not null,
;WITH ctePreAgg AS ( select top 500 act_reference "ActivityRef", row_number() over (partition by act_reference order by act_reference) as rowno, t3.s_initials "Initials" from mytablestuff order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
JobRequirements (A) JobID int QualificationTypeID int
EmployeeQualifications (B) EmployeeID int QualificationTypeID int
Employee (C) EmployeeID int EmployeeName int
I need to return a list of all employees fit for a specific job ... The criteria is that only employees who have all the JobRequirements are returned. So if a job had 3 requirements and the employee had just 2 of those qualifications, they would not be returned. Likewise, the employee might have more qualifications than the job requires, but unless the employee has all the specific qualifications the job requires they are not included. If an employee has all the job qualifications plus they have extra qualifications then they should be returned...
How to only return those records where all the child records are present in the other table..
I want to compare the tables using first name, and I have a log variable which I want to have the value as per the differences in the table that is if the first name matches and second name and dob dont match it shows log value for that FN as 'LN and DOB dont match'.
similarly if First name matches and dob matches then @log is 'LN not match'.
And in case all three match it should show 'match'as log value.The query I use is a s follows:-
Code: USE testing GO DECLARE @NR int DECLARE @log varchar(200) SELECT @NR = COUNT(*) FROM a WHILE @NR>0
i have two tables. 1st table gets the information from as400, then moves the data to table 2 at sql 2000 server. I need to see when as400 updateschanges or inserts new information to table 1 how to updatechange or insert into table 2?
i have this variable called order1 in the table called datetable and i want to display out the duplicate orders in sql. how do i do that? do i need to use group by ?
thanks in advance.! any help?
well i actually get the answers by doin this... select order1 from datetable group by order1 having count(*) > 1
however now i want to compare the values date1 in the first duplicate orders to the second duplicate order...like compare aaaa of the first row and aaaa of the second row ...how to do that? i need to compare the date1 in minutes..i only wan duplicate orders with minutes difference less than 30mins eg
v1 value of a row must be grater than v2 value of previous row, other wise I need to display that row, in above example, I need to display 2nd & 4th rows.
I running a query that returns a information from many tables. I need a way to compare the row returned to the previous row. This query currently returns the requested information for everytime an item placed. I need the query to return only the most recent order information. I can do this by doing a basic loop comparing the jobmatl.item and po.order_date. If the jobmatl.item match between the current row and last row compare the dates and keep the oldest.
Declare @assembly ItemType
SET @assembly = '1110-2014'
SET @assembly = ISNULL(@assembly, '%')
SELECT DISTINCT jobmatl.item , item.description , item.drawing_nbr , jobmatl.matl_qty , itemloc.qty_on_hand , vendaddr.name , po.order_date FROM jobmatl with (nolock) inner join item with (nolock) on item.item = jobmatl.item inner join job with (nolock) on jobmatl.job = job.job inner join poitem with (nolock) on item.item = poitem.item inner join po with (nolock) on poitem.po_num = po.po_num inner join vendaddr with (nolock) on vendaddr.vend_num = po.vend_num inner join itemloc with (nolock) on itemloc.item = item.item WHERE jobmatl.job < ' 990000' AND job.item LIKE @assembly ORDER BY jobmatl.item , po.order_date
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState 1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE 1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Table1 Col1 Col2 Col3 1      a    aa 2     b    bb
Table2 Col1 Col2 Col3 1       a   aa 2      b    bb
Notice Table2.Col2 first row has an extra space " a", where Table1.Col2 value is "a".I need to compare all the columns, not just 1 column (any of the column can be different). Do we use CHECKSUM for this, or is there a better way to compare the 2 tables (in this case resulted in Table2 row 1 col 2 being the difference ?
Hi folks, I've got a fairly easy one here me thinx. I'm looking for the best way to compare two entire rows from two seperate tables which have the same primary key.
Here's the basic lowdown:
I get some data every night from an external system (cache') via DTS. This is more or less my "master" data which drives my application. I have just been informed of an interesting constraint. If any of the data changes in the external system, those changes do not become effective until the first day of the ensuing fiscal quarter.
I'm solving this by running the DTS as normal, but populating a "duplicate" table which I will evaluate once per quarter for any changes. This is also the preferred solution because they would like to see a snapshot between current cache' data and the data my application is currently working with.
So, I end up with two identically structured tables. both tables have the same primary key and can be linked by an id field with relative ease. What I would like to do is a full row comparision once this join is established.
Right now I explicitly check the value of each column. ie:
WHERE t1.field1 <> t2.field1 OR t1.field2 <> t2.field2 OR t1.field3 <> t2.field3 ... etc
I'm hoping there is something buried in TSQL that I just don't know about that can handle comparing entire rows and tell me if they're different. Or perhaps there's another approach all-together.
I need to determine if a value for a group of rows is the same or different and I am not sure where to begin. I am fairly new to writing T-Sql code. I am working with SQL Server 2005. This is a work assignment; I am not a student.
I have two tables: One for Course and another for CourseDays. As the Course table implies, it lists courses for a school. The CourseDays tables has a row for each day a course is held. For instance, one course maybe held on Monday, Wednesday, and Friday, so there would be three rows, one row for each day. A given course could be held in a different room for each day. I am trying to write a query that returns one row for each course, and if the room number is the same for each day the class is held, return that room number in the row, else return 'Various'.
Course table: CourseID, Desc 1 English 2 History 3 Science
CourseDays table: CourseID, Day, RoomID 1 M 320 1 W 445 1 F 680 2 T 123 2 Th 123 3 M 514 3 T 514 3 W 521 3 Th 901 3 F 521
Expected results: CourseID, Desc, RoomID 1 English Various 2 History 123 3 Science Various
I simplified the example, as there are about 30 columns in the table and about half a million rows.
I need to fill the cell background color in the Matrix Table SSRSÂ report based on below requirement -
When For A resource 'Capacity' equals to 'WorkExcludingCapacityAndAvailbility' and Project Status equals to OPEN then GREEN When For A resource 'Capacity' equals to 'WorkExcludingCapacityAndAvailbility' and Project Status equals to PENDING then RED
Here is the below mock-up data -
Looking for solution based on above mockup data how I can compare different rows value by resource considering Project Status Column value in Matrix Table report to set the background color.
I run the script below once a day to keep track of row count over time. I would like to compare the results from today and yesterday to see if anyone deleted more than 20% of data from any given table. How would I do this? I really don't need the data anymore than a day just to compare the results.
Mon - Run script to collect row count Tues - Run script to collect current row into temp table ,compare all row count in both tables ,purge records from Monday and insert current Wed - Run script to collect current row into temp table ,compare all row count in both tables
What I need to be able to find is any records where the Discontinue_Date is greater than the Effective_Date on the next row for a given Customer ID and Part_ID. This is a customer pricing table so the Discontinue_Date of row 53 for example should never be greater than the Effective_Date of row 54130, these are the records I'm looking to find. So I'm looking for a SELECT query that would look for any records where this is true. Obviously the last Discontinue_Date row for a Customer_ID will not have a next row so I wouldn't want to return that.
