Compare Rows To Determine If Column Values Are Different?
Mar 2, 2011
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.
View 14 Replies
ADVERTISEMENT
May 8, 2008
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
Can someone please help me here?
Thanks,
Romakanta
View 1 Replies
View Related
Nov 17, 2005
Hi
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
id Order1 date1
1 aaaaa 20051001 12:45
2 aaaaa 20051001 12:10
3 bbbbb 20031002 15:05
4 bbbbb 20031002 15:50
View 9 Replies
View Related
Jul 30, 2007
Hi
I€™ve a table like this (in SQL Server 2000).
v1 v2
2 8
7 10
11 15
13 17
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.
please advise
Thanks
View 9 Replies
View Related
May 8, 2008
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
Can someone please help me here?
Thanks,
Romakanta
View 2 Replies
View Related
Feb 17, 2008
I have a table with one column. The values can be Y or N. How do I compare the 2 values and return a result depending on the comparison. For example, if I my column data looks like this.
Y
N
I need to compare them and if they match return a specific value and if they are different return a specific value.
View 12 Replies
View Related
Apr 24, 2015
How to select a column to compare two values in it with AND
Select distinct c.reg#, a.fname, a.lname, to_char(b.L#) as "L"
from employee a, driver b, trip c
where a.e#=b.e#
and b.l#=c.l#
and (c.reg#='PKR3344'AND c.reg#='WWF1155')
order by c.reg#;
error no rows selected..I want c.reg# for the two values inclusive
View 4 Replies
View Related
Jul 20, 2005
Hey,This is what I would like to do:===========Declare @chvBOLNumberSet @chvBOLNumber='0001234'Select * from BOL where BOLNumber=@chvBOLNumberI want to return the row/rows when BOLNumber=1234============The problem is the leading zeros. @chvBOLNumber can be 01234 or 001234 or ...Hope the above makes sense. How can I do this ? (probably using wildcards)Thanks, John
View 2 Replies
View Related
May 18, 2015
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.
View 3 Replies
View Related
Jun 25, 2015
I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
View 28 Replies
View Related
Feb 1, 2008
Good morning all,
I have a table from which I need to select every nine values in one column and insert them into another table as a row with nine different columns. For example, I need:
WERT
1A08
2A08
3A08
56999
25555
25666
12444
55699
55698
To become:
SN1 SN2 SN3 GRN1 GRN2 GRN3 GRN4 GRN5 GRN6
1A08 2A08 3A08 56999 25555 25666 12444 55699 55698
And so on and so forth for the every nine values in the column from the first table. Can somebody please help me with the correct T-SQL query to accomplish this please?
I may also need to do the same thing for other tables where I may have fifteen or so values to be extracted into a different table as rows.
Thanks in advance,
Paul
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
Nov 3, 2014
select pr.birthdate, pr.operationdate from patientrecord pr
Total no.of rows = 24420
select distinct pr.birthdate, pr.operationdate from patientrecord pr
Total no.of rows = 23528
It seems there are some rows with same birthdate and operationdate.
I want to get the list of rows(with all columns) that has got same birthdate and operationdate
View 1 Replies
View Related
Apr 6, 2014
I joined these two tables and it pulled up the proper amount of records. If you check out the image you will see what the results are for this query.
Now all I need for this part would be to roll these up where I have one row per ProgramID and all the AttributeNames' together in a AttributeNames column for each id.
EXAMPLE: All in one row.
ProgramID | AttributeNames
887 | Studydesign, Control Groups, Primary Outcomes.
I have attached an image of the SQL VIEW that I need to modified so it does this.
THE QUERY:
SELECT TOP (100) PERCENT dbo.tblProgramAttributes.ProgramID, dbo.tblProgramAttributes.AttributeID AS PAattributeID, dbo.tblAttributes.AttributeID,
dbo.tblAttributes.AttributeName
FROM dbo.tblProgramAttributes INNER JOIN
dbo.tblAttributes ON dbo.tblProgramAttributes.AttributeID = dbo.tblAttributes.AttributeID
WHERE (dbo.tblProgramAttributes.AttributeID NOT LIKE '%ProgramType%')
ORDER BY dbo.tblProgramAttributes.ProgramID DESC
View 5 Replies
View Related
Aug 12, 2015
I need to merge column values (#Status.Status) based on OrderID onto #Orders.NewStausCombined field separated by commas .
CREATE TABLE #Status
(
ID INT IDENTITY (1,1) PRIMARY KEY,
OrderID INT,
Status VARCHAR(20)
)
INSERT INTO #Status ( OrderID, Status )
[code].....
View 3 Replies
View Related
Mar 27, 2008
Hi All,
I have following problem
please help me to resolve it.
There is table for shipping
as follows:
City
Kg
Rate(rs)
Rajkot
1
25.00
Rajkot
2
30.00
Rajkot
3
42.50
Ahemedabad
1
42.50
Ahemedabad
2
55.00
Ahemedabad
3
67.50
Ahemedabad
4
80.00
Goa
1
90.00
Goa
2
105.00
Goa
3
120.00
Gondal
1
25.00
Gondal
2
30.00
Gondal
3
42.50
Morbi
1
25.00
Morbi
2
30.00
Morbi
3
42.50
Gandhinagar
1
42.50
Gandhinagar
2
55.00
Gandhinagar
3
67.50
Gandhinagar
4
80.00
We need to display
the table in html page using any Asp.Net language.
The result table should be:
City(s)
1
2
3
4
Rajkot,Gondal,Morbi
25.00
30.00
42.50
-
Ahemedabad,Gandhinagar
42.50
55.00
67.50
80.00
Goa
90.00
105.00
120.00
-
Baruch
70.25
80.00
92.00
120.50
Description:
- Display all the cities
together for which all the shipping charges for (1-4) kgs are same. The range
of kgs is not fixed.
Reply as soon as possible.
Thanks
View 3 Replies
View Related
Jun 6, 2015
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
View 1 Replies
View Related
Jan 23, 2008
Hi All,
I receive the input file with some 100 columns and some 20k+ rows and I want to check the incoming input row is existed in the database or not based on 2 key columns. If the row is existed then I need to check all the columns (nearly 100 columns) values in input and the database are equal or not. If both are equal I need to treat them seperately if not there is a seperate logic. How Can I do that check for each row and for each column?
Basically the algorithm is like this, if the input file row is not existed in the database then treat that as new row else if the input row is existed in the database then check all the columns are equal or not. If all the columns are equal then treat that as existing row and do nothing else if some columns are not equal then treat this row seperately.
I found some thing to achieve the above thing.
1. Take the input row and check in the database.
2. If the row is not found in the database then treat it as new row.
3. If row is found in the database then
a) Take the source row and prepare a concatenated string for all the columns
b) Take the database row and prepare a concatenated string for all the columns
c) Find out the hash code for the 2 strings and then compare hash codes for equal.
The disadvantage of this is running a loop 2*m*n times where m is the number of rows and n is the number of columns. It should be done 2 times for input file row and database row.
Can anybody suggest a good method to do this?
What does the function "GetHashCode" for InputBuffer in method "Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)" will do?
Will it generates hash code based on all the columns values?
Pls clarify.
Regards
Venkat.
View 1 Replies
View Related
Dec 19, 2006
What query should I run?
1. To determine whether a column is a rowguid or not using C# .NET 1.1
2. To add/modify column information and be able to set/change:
- Primary key
- Column Name
- Data Type
- Length
- Allow Null
- Default value
- Precision
- Scale
- Identity
- Identity Seed
- Identity Increment
- Row guid
Thanks a lot!
View 6 Replies
View Related
Jun 9, 2007
I have a table of machine data that captures fault codes, time machine stopped and time machine started. I can easily calculate the downtime, but how do i take the last start time and subtract it from the next stop time (1 row from the next row) to get an up time. I need it to group by short date and fault as I intend to use Excel as a reporting tool and pulling in all data will not fit. What is the most efficient way?
Thanks,
Lee
View 8 Replies
View Related
Sep 7, 2015
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
          red   blue  green
  pen       2    0    1
  marker    0    1    0
  pencil      1    3    0
  highlighter  1    1    1Â
We tried Sum but not able to display in single row.
View 2 Replies
View Related
Sep 3, 2015
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.Â
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.Â
View 3 Replies
View Related
Feb 28, 2008
HI,
Thanks in advance for taking your time to read this post.
I am trying to write a SQL query using MS SQL 2005 that will read the value of a field and tell if it is alpha or numeric. I have tried the following but it does not work:
select field1 from table1 where left(field1,2)='[0-9]'
select field1 from table1 where isnumber(left(field1,2) tried with a =1 at the end and without and =1 at the end
the goal is to read through a field and format it so if a field looks like this 12xxx111xx I can change it to look like 12-xxx-111-xx.
Any help is greatly apprecaited
View 4 Replies
View Related
Feb 12, 2014
I want to add $ symbol to column values and convert the column values to western number system
Column values
Dollar
4255
25454
467834
Expected Output:
$ 4,255
$ 25,454
$ 467,834
My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application
COST is the int datatype and needs to be changed.
View 2 Replies
View Related
May 23, 2015
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.
View 4 Replies
View Related
Jul 26, 2014
I am relatively new to SQL and as a project I have been asked to create the SQL for a simple database to record train details. I want to implement a check constraint which will prevent data from being inserted into a table if the weight of the train is more than the maximum towing weight of the locomotive. FOr instance, I need to add the unladen weight and maximum capacity of each wagon (located in the wagon type table) and compare it against the locomotive maximum pulling weight (the locomotive class table). I have the following SQL but it will not work:
check((select SUM(fwt.unladen_weight+fwt.maximum_payload) from
hauls as h,freight_wagon as fw,freight_wagon_type as fwt,train as t where
h.freight_wagon_serial_number = fw.freight_wagon_serial_number and
fw.freight_wagon_type = fwt.freight_wagon_type and
h.train_number = t.train_number) <
(select lc.maximum_towing_weight from locomotive_class as lc,locomotive as l,train as t where
lc.locomotive_class = l.locomotive_class and l.locomotive_serial_number = t.locomotive_serial_number))
The hauls table is where the constraint has been placed and is the intermediary table between train and freight wagon.
I may not have explained this very well; but in short, i need to compare the sum of two values in one table against a values located in another table...At present I keep getting a message telling me the sub query cannot return more than one row.
View 2 Replies
View Related
Nov 11, 2006
Here is my statement:
Dim MyCommand As SqlDataAdapter
MyCommand = new SqlDataAdapter("select * from Request T1, Users T2 where RunID= "+ intRun_Num +" and T1.UserID = T2.UserID ORDER By Username", MyConnection)
I'm getting error saying:
Input string was not in a correct format.
the problem is the intRunNum. When I put intRunNum.toString(), there is no error but no results are coming up.
How do I get past this?
View 3 Replies
View Related
Oct 22, 2007
hello there,
i have a matrix which displays data for different products.i have grouped them by year and month.now i want to compare each month to the previous month and show an indicator that this month the sales was down or up.for that i need to compare a month with the previous month.how can i do this in reporting service.please help.
cheers
zolf
View 1 Replies
View Related
Oct 22, 2007
hello there,
i have a matrix which displays data for different products.i have grouped them by year and month.now i want to compare each month to the previous month and show an indicator that this month the sales was down or up.for that i need to compare a month with the previous month.how can i do this in reporting service.please help.
cheers
zolf
View 2 Replies
View Related
Aug 2, 2004
I'm stuck trying to compare date values from users selection against a database. I also need to add a condition statement on if the results return no match. How would I do that?
Also, I'm not even sure if my SELECT statement is right. The user will hit a date that will be in format m/d/yyyy and that will be compared to see if it exist in the database. The datebase column "date_created" is default with date/time( 8 ). But when I compare the to together, nothing returns back. The page loads successfully, but no results from the database.
Here is my code:
dim conpubs as sqlconnection
dim cmdSelectAuthors as sqlcommand
dim dtrAuthors As sqlreader
conpubs = New sqlconnection(configurationsettings.appSettings("STD"))
conpubs.open()
cmdSelectAuthors = New sqlcommand("Select * From HotNews WHERE date_created=" & y, conpubs)
dtrAuthors = cmdSelectAuthors.ExecuteReader()
Response.Write("<table><tr><td>")
While dtrAuthors.Read()
Response.Write(dtrAuthors("title") & "<br>")
End While
Response.Write("</td></tr></table>")
dtrAuthors.close()
conPubs.close()
View 1 Replies
View Related
Aug 2, 2004
I'm stuck trying to compare date values from users selection against a database. I also need to add a condition statement on if the results return no match. How would I do that?
Also, I'm not even sure if my SELECT statement is right. The user will hit a date that will be in format m/d/yyyy and that will be compared to see if it exist in the database. The datebase column "date_created" is default with date/time( 8 ). But when I compare the to together, nothing returns back. The page loads successfully, but no results from the database.
Here is my code:
dim conpubs as sqlconnection
dim cmdSelectAuthors as sqlcommand
dim dtrAuthors As sqlreader
conpubs = New sqlconnection(configurationsettings.appSettings("STD"))
conpubs.open()
cmdSelectAuthors = New sqlcommand("Select * From HotNews WHERE date_created=" & y, conpubs)
dtrAuthors = cmdSelectAuthors.ExecuteReader()
Response.Write("<table><tr><td>")
While dtrAuthors.Read()
Response.Write(dtrAuthors("title") & "<br>")
End While
Response.Write("</td></tr></table>")
dtrAuthors.close()
conPubs.close()
View 1 Replies
View Related
Feb 2, 2006
guys this is kind of a biggie for me.
i've only used sql server to compare the Date Portion of date(s), not the whole string (mm/dd/yyyy hh:mi).
i have a table called Time_Check which has two fields with timestamps,
"TimeLastRun & TimeNextRun". i was trying to compare the Current TimeStamp that i pass from a vb.net form to a stored procedure and compare that DateTime to the TimeNextRun. I'd like to then Update the two Time Columns if rows are found. Should i maybe be using the DateDif Func if i know ahead of time how much time should be elapsed(say five minutes)?
Any Code help is appreciated
Create Procedure dbo.My_Time_Check
@myDate smalldatetime
as
select alertnumber,alertname,TimeLastRun,TimeNextRun
from time_check
where timeNextRun >= @myDate
--would like to update these two columns if rows are found
thanks again
this forum is the best
rik
View 5 Replies
View Related
Sep 20, 2013
I am trying to compare values from two columns but I don't know what I am doing, I tried select custom5,skipcount substr( custom5, 1, 4) as 'mynumber' from songs where custom5 > '0000%' where skipcount > cast( substr( custom5, 1, 4) as int ); But that is obviously wrong?
View 4 Replies
View Related