Comparing Two Data Rows
Sep 25, 2013
create table #prints(id int IDENTITY(1,1) NOT NULL,
Printermarkersupplyid varchar(36),PrinterID varchar(10),Description varchar(10),SupplyLevel int,ModifiedDate datetime)
insert into #prints(Printermarkersupplyid,PrinterID,Description,SupplyLevel,ModifiedDate)
select newid(),'P1','D1',100,'2013-08-1 03:28:38.203'
union all
[code]....
my requirement is to get the difference between adjacent rows.ie difference between 2nd and 3rd or 6th and 7th but not 6th and 8th.if difference between 2nd and 3rd is less than zero and 3rd modified date > 2nd modified date,then i should get count as 1 against 3rd row.
View 2 Replies
ADVERTISEMENT
Jul 20, 2005
I'm trying to come up with an elegant, simple way to compare twoconsecutive values from the same table.For instance:SELECT TOP 2 datavalues FROM myTable ORDER BY timestamp DESCThat gives me the two latest values. I want to test the rate ofchange of these values. If the top row is a 50% increase over the rowbelow it, I'll execute some special logic.What are my options? The only ways I can think of doing this arepretty ugly. Any help is very much appreciated. Thanks!B.
View 22 Replies
View Related
Jan 31, 2006
This is a bit similar to the topic at
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56058
but with a bit of a twist...
I have
Batch Sample Value
0 0 A
0 1 A
1 0 A
1 1 B
2 0 B
2 1 B
...
I'm looking to get something that would provide a row comparison - something that looks like
Batch Different
0 FALSE
1 TRUE
2 FALSE
...
Any suggestions for a query that would do this? Thanks!
View 3 Replies
View Related
Feb 26, 2013
Code:
Room ID Hours StartTime EndTime
GR41908101700
GR42409101300
GR43317102000
LR111108100900
LR112109101000
LR113210101200
LR114112101300
LR115313101600
LR116216101800
LR117118101900
LR118119102000
LR119320102300
As you can see each room is assigned and id and once the room changes the id starts from 1 again. I don't want the 2nd row to appear because 9am - 1pm is in 8am - 5pm. How do I remove this row?
View 3 Replies
View Related
May 10, 2008
Hi,
I have a table that holds pay rate changes with a field for the rate start date and a field for the rate end date. When an employee gets given a new pay rate, the existing rate is given an end date and a new row is added with the rate start date being the day following the end date of the old pay rate.
I need to identify the staff who have had a rate change within the past month, therefore an end date on one row that is within one month of the current month, and a start date on another row that is one day after an end date on a separate row and within one month of the current month.
Is someone able to help me out please?
Thanks
View 8 Replies
View Related
Jun 29, 2012
I've been working with T-SQL in a MSSQL Server Management Studio (2005) for about a week now. I've been trying to convert some horribly written VB code from a MS Access DB over to SQL so it can be automated on a SQL backend.
Most of the learning process and coding has gone surprisingly well. The problem is with comparing some data to determine which one needs to be flagged.
Three tables to note in bold, with notable fields in italics below them:
EmployeeData
HRID (identity)
ResourceAllocation
ID (identity)
[Last Name] (linked to HRID)
Project
[Resource Start Date]
[Resource End Date]
[Percent Utilization]
tblHCvalues
RAID (linked to ResourceAllocation.ID)
a monthyear and quarteryear for every month and quarter from 2012-2014. IE january12, february12, 1q12, 2q13, etc...
And yes, there are probably a thousand ways to optimize that tblHCvalues, but I'll ask about that later. Just work with the structure I have
Here's how it works: Each employee's data and unique HRID is in the EmployeeData tableAn employee can be on one or multiple projects at any timeThose projects are stored per project in the ResourceAllocation table with a link to the Employee's HRID, and all the other information listed aboveEven though an employee might be on two projects, they can only count for headcount on one project.
We use rules that compare the percent of work being done on a project, and the start and end dates of the employee (resource) on that project to determine which project should be counted for Headcount. The code uses a cursor to go through each HRID, and then pull up all the ResourceAllocation records associated with it.Run the rules to determine which ResourceAllocation record counts toward headcountA stored procedure then runs that fills out the tblHCvalues in the way we want for the project we want
All of it works, except for the rules that compare the things, so that's what I want to focus on in this thread. How do I write these rules:
Here are the rules, and they should work for any number of multiple resource allocations for one employee:
Choose the ResourceAllocation with the greatest [Percent Utilization]If the top ResourceAllocations have equal [Percent Utilization], choose the ResourceAllocation with the earliest [Resource Start Date]If the [Percent Utilization] and the [Resource Start Date] are equal, choose the latest [Resource End Date]If all three fields are equal, choose the first ResourceAllocation (aka, screw it and pick one at random)
I'm sure I could use a bunch of IF statements to compare it all, but even that is complicated to think about. There has to be an easier way, right?
View 6 Replies
View Related
Jun 15, 2007
How do I only select rows with duplicate dates for each person (id)? (The actual table has approximately 13000 rows with approximately 3000 unique ids)
p_key id date pulse
--------------------------------------
1 32 5/25/2006 80
2 32 5/25/2006 85
3 32 4/26/2006 81
4 32 6/15/2006 82
5 15 1/20/2006 75
6 15 3/25/2006 80
7 15 3/25/2006 83
Result table I am looking for:
p_key id date pulse
------------------------------------
1 32 5/25/2006 80
2 32 5/25/2006 85
6 15 3/25/2006 80
7 15 3/25/2006 83
Thanks.
View 8 Replies
View Related
Sep 22, 2006
I want to do a login in vb.net, when the user enters username and password, it looks into ms sql to find user then compare password with database then select user in database and closes form to open another form with selected database. please help, i am stranded with my project and i am getting nowhere.
View 6 Replies
View Related
Sep 22, 2006
I want to do a login in vb.net, when the user enters username and password, it looks into ms sql to find user then compare password with database then select user in database and closes form to open another form with selected database. please help, i am stranded with my project and i am getting nowhere.
View 1 Replies
View Related
Oct 30, 2015
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
View 3 Replies
View Related
Jul 31, 2007
Hi,
I've a bunch of records that may contain data that I'm after. For example:
This is a fake title [electronic resource]. 1997.
I have a very small table (~10 rows) of things like '[electronic resource]'
Is there any way to see if my record contains any of the 'target' items in the other table?
View 1 Replies
View Related
Jun 27, 2007
Hello,I currently have Table1 and View1.View1 is a query from 2 or 3 tables that works fine on its own.However in my current query if I try to use it...something like...SELECT a.col1, a.col2, a.col3, b.col1, b.col2, b.col3FROM View1 a JOIN Table1 b on a.col1 = b.col1WHERE a.col2 <b.col2 OR a.col3 <b.col3It throws an error "Server: Msg 446, Level 16, State 9, Line 1 Cannotresolve collation conflict for not equal to operation."Clearly I need to use collation between Table1 and View1, But I dontknow where I need to use "COLLATE SQL_Latin1_General_CP850_CI_AI" andhow? this is the collation set on Table1.Thank you!Yas
View 1 Replies
View Related
Jun 9, 2015
I am trying to build various reports that compares data over time. I have one that measures Year Over Year % difference for number of incoming projects. I managed to do that easily by calculating the following
YTDProjects:=if(ISBLANK(SUM('TrendData'[Projects])),blank(),CALCULATE(SUM('TrendData '[Projects]),DATESYTD(CalendarDate[FullDate])))
PYProjects:=if(ISBLANK(SUM('TrendData'[Projects])),blank(),CALCULATE(sum('TrendData '[Projects]),SAMEPERIODLASTYEAR(DATESYTD(CalendarDate[FullDate]))))
YoYDifference:=[YTDProjects]-[PYProjects]
YoYPercProjects:=IF([PYProjects]=0, BLANK(), [YoYDifference]/[PYProjects])
Where Projects is the metric in question, TrendData is the table that contains project data and CalendateDate is the Date Table. But now I am trying to compare the same YTD projects data to number of projects that came in the last 6 months. How do our projects compare to average number of projects that came in last 6 month period.
I tried the the DATEADD function instead but got no luck and data came out wrong!
PrevProjects:=CALCULATE(SUM([Projects]),DATEADD(CalendarDate[FullDate],-1,QUARTER))
For some reason, this also returns blank in my model:
QTDProjects:=TOTALQTD(SUM('TrendData'[Projects]),CalendarDate[FullDate])
View 2 Replies
View Related
Oct 22, 2006
I made ahuge load script in SQL Server 2000 as i load data from manytables(select some of each one collumns) into one single table and iwant to test the loaded data against the selected data to make surethat the loaded data is the same the selected datais there a code or tool to make this test or monitoring ?? pleaseurgent ....
View 2 Replies
View Related
Apr 10, 2007
Hello,
I am looking for some "best practice" information on how to properly setup a comparison of SQL data -vs- an Excel spreadsheet. I've never performed this task before, and will likely have to do more in the future. How can I define the range of cells to be used from the xls? There are over 1000 rows in the xls, so a Select * from dbo.table where dbo.column = 'x' just isn't practical.
Any information/direction that's out there is most appreciated!
Many thanks
View 1 Replies
View Related
Oct 17, 2001
Hello,
I need to do a query to compare the same field in two different databases. How can I go about doing that? Can you use an if not exists and how does that work?
I also need to compare two numbers in two different databases and then convert that number to a decimal. For example I need to compare 22940 in one column and 22942 in a different column and insert the value of 2.1 in a third column. How can that be done?
Thanks in advance,
Anita
View 2 Replies
View Related
Dec 20, 1999
Hi, I would like to know if it is possible to compare data in two different databases (both SQL7.0),
with same exact schema. If possible, how could we do it?
Thanks
- Sharma
View 1 Replies
View Related
Apr 2, 2003
Hi
Is there a way of comparing the data contained in two separate databases (on the same server)?
I have two databases: DevDB and DB and all I want to verify is that the data in both are *exactly* the same.
Is there a nifty function to do this in SQL Server?
Thanks
Sara
View 8 Replies
View Related
May 6, 2008
Hi
I am relatively new to SQL. Basically i am trying to compare data that doesn't match properly. Firstly one set of data has spaces, secondly the first part of the data is missing.
After researching I am going to remove the spaces with the replace and only query the final 8 number - this would dow hat I want but I cannot get it to work.
Here is my code.
Code:
update aleads as a, leads as l
set a.lid = l.lid
where replace(right(a.htel,8), ' ', '') = replace(right(l.htel,8), ' ', '');
Of course this didn't work but I do not know why - or how to actually achieve this.
View 6 Replies
View Related
Jul 23, 2005
I have the following situation. One set of data has 274 rows (set2)and anther has 264 (set1). Both data sets are similar in structure aswell as values for both of them were extracts from the same parenttable. Hope the info would substitute DDL. I need to find the "gap"rows between these two sets.Attempted to run a query likeselect count(*)from set2where not exists(select *from set1)did not yield what I desired. What else to try?TIA.
View 12 Replies
View Related
Jul 23, 2005
I would like to compare data across two tables. I have partinformation in a table. I get a new set of information periodically.I would like to compare my new info to my old info. I recognize thatdoing a compare of every attribute of every part will take FOREVER. Isthere some way I can do a "diff" based on the columns that I careabout?Thanks!--gloria
View 2 Replies
View Related
Oct 31, 2006
Can anyone tell me how a program like this might work:http://www.red-gate.com/products/SQ...mpare/index.htmI want to backup databases into a central repository but I only wantthe records that have changed for that day. This program seems to do itefficiently. Does Sql Server, Oracle, etc offer any sort of built inway of doing this via metadata or a similar mechanism?I want to be able to mirror databases in such a way that I dont need tohave the admin password for them. I don't want to alter the databaseschema in any way.The only way I can think off the top of my head is to assign MD5checksum values to rows and then compare the checksums in the masterand copy, however, to do it efficiently, you'd have to save the MD5values as you go, which would involve altering the schema on the mastertable.
View 1 Replies
View Related
Jan 15, 2008
I need to compare two tables in two different databases and get data that is only in one table.
Table 1 in DB1 and Table A in DB_A
I need all distinct IDs in Table 1 in DB1 that are NOT in Table A in DB_A
and copy those IDs into Table A.
Is the sql below correct in obtaining data?
Can I add the copy part also to this?
select ID
from DB1.dbo.Table1 aaa
where not exists ( select empID
from DB_A.dbo.TableA bbb
where aaa.ID = bbb.empID)
View 5 Replies
View Related
Sep 29, 2015
I'm trying to create some records for a data table (Table1) based on values in another table (Table2).
Table1 has the simple structure of
ID (INT), PolicyID (INT) (this is a foreign key linked to a Policies table), Premium (FLOAT), StartDate (DATETIME), EndDate (DATETIME)
Here is the structure and some example data from Table2
- PremID - PolicyID - Year - Prem01 - Prem02 - Prem03 - Prem04 - Prem05 - Prem06 - Prem07 - Prem08 - Prem09 - Prem10 - Prem11 - Prem12 -
- 000001 - 00000001 - 2013 - 100.00 - 100.00 - 100.00 - 100.00 - 100.00 - 100.00 - 100.00 - 100.00 - 100.00 - 130.00 - 130.00 - 130.00 -
- 000002 - 00000001 - 2014 - 130.00 - 130.00 - 130.00 - 140.00 - 140.00 - 140.00 - 140.00 - 140.00 - 140.00 - 140.00 - 140.00 - 140.00 -
PremID is just the PrimaryKey. Then there's the year, and then a load of float fields, each representing a premium paid for each month of the year (01=Jan, 02=Feb etc).
Now what I am looking to achieve is to create separate rows for Table1 from this data.
Starting in January (Prem01), I wish to record a separate line each time the premium amount changes. The best way of exlaining this is to give an example based on the above data.
For the first row (2013), the premium at the start of the year (Prem01) is 100.00. This premium remains until it changes in Prem10 to 130.00. It then remains 130.00 for the rest of the year (Prem10 to Prem12). For this I would want to create 2 rows of data for Table1. The result should look like below.
- ID - PolicyID - Premium - StartDate - EndDate -
- 000001 - 00000001 - 100.00 - 01-Jan-2013 - 30-Sep-2013 -
- 000002 - 00000001 - 130.00 - 01-Oct-2013 - 31-Dec-2013 -
I'd then repeat this for each record in Table2, so based on the 2 lines I have shown above, the end result would be:
- ID - PolicyID - Premium - StartDate - EndDate -
- 000001 - 00000001 - 100.00 - 01-Jan-2013 - 30-Sep-2013 -
- 000002 - 00000001 - 130.00 - 01-Oct-2013 - 31-Dec-2013 -
- 000003 - 00000001 - 130.00 - 01-Jan-2013 - 31-Mar-2014 -
- 000004 - 00000001 - 140.00 - 01-Apr-2013 - 31-Dec-2014 -
The source data is inherited from another system which I'm trying to change the layout of how the data is recorded to fit in with the target database.
Would it be easier to create a view of some sort from Table2, then use some form of WHILE LOOP to do this?
View 6 Replies
View Related
May 13, 2008
I am looking for an efficient mechanism to compare data between 2 tables/views.
Rationale:
I use a proprietary tool to data transfer between 2 databases. The tool itself uses Microsoft SSIS (integration service) to transfer data. I want to make sure that the data is transfered properly. Both the source and target database are not live database. The source and target database are in seperate servers.
View 2 Replies
View Related
Jun 4, 2008
Hi everyone,
I have recently converted my DTS packages to SSIS and deployed them to the new server. I have the 2000 and 2005 server running concurrently, all that is left for me to do is compare the the tables generated by the DTS and SSIS packages to see if they are the same.
How do I go about comparing the tables, which are from two different servers using SQL server 2005?
Thank you inadvance:)
Comparing data and integrity between two tables
View 2 Replies
View Related
Jul 20, 2005
I have an entry form allowing customers to enter up to 15 skus (productid) at a time, so they can make a multiple order, instead of enteringone sku, then submitting it, then returing to the form to submit thesecond one, and so forth.From time to time, the sku they enter will be wrong, or discontiued, soit will not submit an order.Therefore, when they are done submitting their 15 skus through the orderform, I want a list showing them all of those skus that came back blank,or were not found in the database.I'm doing this by creating two tables. A shopping cart, which holds allthe skus that were returned, and a holding table, that holds all theskus that were submitted. I want to then delete all the skus in theholding page that match the skus in teh cart (because they are goodskus) which will then leave the unmatched skus in the holding table.I'll then scroll out the contents of the holding table, to show them theskus that were not found in the database.(confused yet?)So what I want to do is have some sql that will delete from the holdingtable where the sku = the sku in the cart. I've tried writing this, butit dosn't work.I tiried this delete from holding_table where sku = cart.skuI was hoping this would work, but it dosn't. Is there a way for me to dothis?Thanks!Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Mar 20, 2008
Hi,
In a table i have a column named 'Name'. It can have multi lingual data.
I have desinged a query in which i have a statement as
Select Col1
FROm TABLE1
WHERE
NAME IN (@NAme)
But for MultiLingual names is not compared.
I have declared that column as NVARCHAR.
View 4 Replies
View Related
Jun 28, 2006
Hi,
I have a common requirement in numerous SSIS processes to take my main input data set and to remove all rows from it that match a second input data set on a given key and output this as the main output. I also want to output (as a second output) all the rows from the main input data set that did match on the given key. However, I don't want to merge in data from the second input, nor am I interested in rows from the second input data set that have no match in the main input.
E.g. If I have the following data:
Main input:
Key Name
--- ----
1 Steve
2 Jamie
3 Donald
Second Input
Key DontCareAboutThisField1
--- -----------------------
1 ...
3 ...
4 ...
Then I would like the following output:
Main Output
Key Name
--- ----
2 Jamie
Second Output
Key Name
--- ----
1 Steve
3 Donald
Can I do this with a standard transform, or will I have to write my own? Any help on this would be greatly appreciated!
Thanks in advance,
Lawrie
View 1 Replies
View Related
Dec 18, 2013
Is there a performance impact (if so, how great is it) when comparing a date to a datetime data type?
An educated guess suggests yes, as there will be a type casting... but then again I don't believe there's an issue when comparing an int to a tinyint and there's an assumption these would play by the same rules?
This has only come about because I'm considering changing the data type used in my standard calendar script and this popped in to my head.
View 5 Replies
View Related
Aug 4, 2014
I have an excel spreadsheet that only has email addresses in a single columnar format on it (318 emails). I want to check and see if any of those emails are in the database. Is there a easier way than having to enter 300+ "OR" statements?
SELECT "Name"."FIRST_NAME", "Name"."LAST_NAME", "Name"."EMAIL", "Name"."ID", "Name"."MEMBER_TYPE"
FROM "APSCU_PROD"."dbo"."Name" "Name"
WHERE Name.EMAIL='marie@bahoo.com' OR Name.EMAIL='markg@ts.com' OR Name.EMAIL='mare@t.edu'
View 8 Replies
View Related
Mar 17, 2006
Hello,
I am designing a distributed application where a central SQL Server 2005 database will need to be synchronized with remote SQLExpress databases via a WebService. Data can be edited at the cental db (by our connected applications) or on the local SQLExpress dbs running on the users machines (by this disconnected application).
Now, how can I use the timestamp column to determine the most recent update. The most recent update to me is not the user that most recently invoked the syncing WebService, but the most recent time when the change was made to the data locally vs the change time at the central server. Because a user could make a change on his laptop on Monday but not synchronize it till Friday. I dont want the Mon data to overwrite data of Tues-Fri being overwritten simply based on assumption that a late sync is infact the most recent change.
The initial data will be downloaded to the local SQLExpress db via the WebService so the timestamp data in the table will come along with it.
When a user modifies data in this disconnected SQLExpress db, can that be compared to modifications on the central db using timestamps to determine if the users data being synced is older or newer than the data on the server? I understand timestamps are incremental values, but are they still sensitive to the users timezone since the central server will be a diff timezone then the user.
View 6 Replies
View Related
Jul 20, 2005
I have two tables of book information. One that has descriptions of thebook in it, and the isbn, and the other that has the book title,inventory data, prices, the isbn.Because of some techncal constraints I won't get into now, I can'tcombine them both into one table. No problem. Things are going fine aslong as there is a description in the one table to corrispond to theisbn and other data in the other table.However, about half of the products are not yet entered into thedescrition table. I'd like to run a sql query that pulls up all theisbns that don't exist in the other. In other words, I'd like to get aquery that tells me exactly which isbns do not yet have descrition datain them. I know there is some sql that says to search from one filewhere the number does not exist in the other, but it slips my mind. Cansomeone help me on this please?Thank you!Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
