How Do I Get Unique Rows Based On StudentID

Oct 4, 2006

I am a beginner at SQL so thanks ahead of time.....
How do I get unique rows based on studentID? Distinct and group by don't seem to work
DESIRED RESULTS
StudentID  First Name Last Name  Other Columns...............................................
634565491 MARINA    BALDERAZ 
640484566 TERE        BALDERAZ

CURRENT SQL AND RESULTS.....
SELECT ClassRosterRecID, StudentDataRecID, StudentDataKey, StudentID, FirstName, LastName, CurrentGrade, Gender, Ethnicity, EconDisadvantaged, TitleI, Migrant, LEP, Bilingual, ESL, SpecialEducation, GiftedTalented, AtRisk, CareerTech, Dyslexia, LastName + ', ' + FirstName AS LastNameFirstName, EconDisadvantagedSort, TitleISort, MigrantSort, LEPSort, BilingualSort, ESLSort, SpecialEducationSort, GiftedTalentedSort, AtRiskSort, CareerTechSort, DyslexiaSort, DistrictID, CampusID FROM vClassDemographicsDetail WHERE (DistrictID = '057910') AND (CampusID = '057910101') AND (LastName LIKE '%BALDERAZ%')

StudentID  First Name Last Name  Other Columns...............................................
634565491 MARINA    BALDERAZ 
634565491 MARINA    BALDERAZ 
634565491 MARINA    BALDERAZ 
640484566 TERE        BALDERAZ
640484566 TERE        BALDERAZ
640484566 TERE        BALDERAZ

View 1 Replies


ADVERTISEMENT

Selecting The Rows Based Off Of Unique Columns

Mar 18, 2007

Hi there, im still learning SQL so thanks in advance.I have a table with columns of customer's information, [customerID], [customerFirst], [customerLast], , [program] ... other columns ...  There will be entries where there can be duplicate customerFirst and customerLast names.  I would like to just return a single entry of the duplicate names and all associated row information.  IE: [customerID], [customerFirst], [customerLast],            [ email],             [program]         01               Bill                 Smith             bill.smith@hotmail.com    ymca        02               Bill                 Smith             bill.smith@hotmail.com    Sports        03               jon                   doe                 jon.doe@hotmail.com    AAA        04               jon                   doe                 jon.doe@hotmail.com    Ebay          05               Paul                 Sprite             paul.sprite@hotmail.com    Rec Desired Returned result:        01               Bill                 Smith             bill.smith@hotmail.com    ymca        03               jon                   doe                 jon.doe@hotmail.com    AAA
         05               Paul                 Sprite             paul.sprite@hotmail.com    Rec So in my code i have this:dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr);         dAdapter.Fill(pocDS, "Data Set");        However this is throwing up an error when i build the app:  An expression of non-boolean type specified in a context where a condition is expected, near ')'.



Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException:
An expression of non-boolean type specified in a context where a
condition is expected, near ')'.

Source Error:




Line 52: //dAdapter = new SqlDataAdapter("SELECT DISTINCT * FROM [Poc_" + suffix + "] ORDER BY [CustomerLastName]", cnStr); Line 53: dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); Line 54: dAdapter.Fill(pocDS, "Data Set");Line 55: Line 56: //Dataset for name comparison  1: Can someone explain to me why this error is happening?2: Can soemone confirm that my intentions are correct with my code?3: If I'm completely off, can someone steer me in the right direction?Thanks alot!-Terry  

View 12 Replies View Related

Compare Values In Consecutive Rows And Print Rows Based On Some Conditions

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

Unique Constraint Based On Value In A Column

Nov 23, 2007

I don't immediately find if this is possible but hope someone can give me an answer:
is it possible to make a unique constraint over 2 columns but only when 1 column has a specific value ?

Example: table (tableid, instancetype, instancename, ..)
instancetype can be A or B
if it is A then instancename must be unique
but for B instancename is not unique as these are copies from A

only solution I can think of is to make a trigger on an insert to check what the instancetype is and do a select to see if the name already exists in the table or not..

are there other solutions to make a constraint like this ?

Aeneas.

View 1 Replies View Related

Generating Unique No Based On Certain Data

Oct 3, 2007

Hi,

I have the following tables :




Code Block
Create Table #Request ( [requestid] int , [customername] Varchar(30) , [stateno] nvarchar(5) , [cityno] int , Callid int, UniqueNo int);

Create Table #Call(Callid int,Calltype int,callDetailid int ) // CallType 1=New 2=Change 3=Delete

Create Table #CallDetail(callDetailId int,empid int)




The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on

I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType and insert into #Request table along with the other data. How do I do this?

SAMPLE DATA



Code Block
Insert into #CallDetail(12123,1)
Insert into #CallDetail(53423,1)
Insert into #CallDetail(6532,1)
Insert into #CallDetail(62323,1)
Insert into #CallDetail(124235,1)
Insert into #CallDetail(65423,2)
Insert into #CallDetail(56234,2)
Insert into #CallDetail(2364,2)
Insert into #CallDetail(34364,2)
Insert into #CallDetail(85434,2)

Insert Into #Call(111,1,12123)
Insert Into #Call(112,1,53423)
Insert Into #Call(114,1,6532)
Insert Into #Call(123,2,62323)
Insert Into #Call(134,1,124235)
Insert Into #Call(143,2,65423)
Insert Into #Call(145,1,56234)
Insert Into #Call(154,2,2364)
Insert Into #Call(185,1,34364)
Insert Into #Call(195,1,85434)

Insert Into #request Values('324234','Jack','SA023',12,111,0);
Insert Into #request Values('223452','Tom','SA023',12,112,0);
Insert Into #request Values('456456','Bobby','SA024',12,114,0);
Insert Into #request Values('22322362','Guck','SA024',44,123,0);
Insert Into #request Values('22654392','Luck','SA023',12,134,0);
Insert Into #request Values('225652','Jim','SA055',67,143,0);
Insert Into #request Values('126756','Jasm','SA055',67,145,0);
Insert Into #request Values('786234','Chuck','SA055',67,154,0);
Insert Into #request Values('66234','Mutuk','SA059',72,185,0);
Insert Into #request Values('2232362','Buck','SA055',67,195,0);




EXPECTED OUTPUT will be (See the last column for unique nos). :



Code Block
Insert Into #request Values('324234','Jack','SA023',12,111,1);
Insert Into #request Values('223452','Tom','SA023',12,112,2);
Insert Into #request Values('456456','Bobby','SA024',12,143,1); // Calltype = 1 empid= 1, but state is different, hence unique id is 1
Insert Into #request Values('22322362','Guck','SA024',44,114,1);
Insert Into #request Values('22654392','Luck','SA023',12,123,3);
Insert Into #request Values('225652','Jim','SA055',67,143,1);
Insert Into #request Values('126756','Jasm','SA023',69,134,1);
Insert Into #request Values('786234','Chuck','SA023',72,145,2);
Insert Into #request Values('66234','Mutuk','SA059',72,185,1);
Insert Into #request Values('2232362','Buck','SA055',67,195,2);





Please note that this will not be run as a batch query, but the no. has to be generated and inserted into #record table in realtime. I have given bulk of records for understanding of the problem

Plz help. I am stuck from 2 days on this :

Vidkshi

View 8 Replies View Related

Unique Results Based On Popularity Voting

Jun 18, 2008

I've managed to get my query to this: Product | Color | Votes
======== ======= =======
Bus Red 10
Bus Blue 5
Train Blue 1Car Red 1
Car Blue 1
 
I need the most popular color of the product based on the votes for each product, so MAX(Votes) GROUP BY Product solves the Bus and the Train, but I still need a result for the Car? Not really that bothered which Color is picked up, do I need to run another query, because I need another table to join to the results.
 
Any help would be greatly appreciated.
Thanks, Pete.

View 2 Replies View Related

Count Up Based On Unique Value -- Brain Freeze

Apr 17, 2008



Ok... I know this is something that I've seen and probably done before....

I have a value that will show up multiple times in a table. I want to order the table by that value and then count up from 1 to however many items that it shows up as.

The example below shows the results I want. Value1 and Value2 are in the table, LineNumber is what I need to generate.








Value1
Value2
LineNumber

1
Hey
1

1
Can
2

1
Someone
3

1
Help
4

1
With
5

1
This
6

2
Very
1

2
Simple
2

2
Issue
3

2
As
4

2
I
5

3
Have
1

3
A
2

3
Brain
3

3
Freeze
4



--Thanks--

View 4 Replies View Related

Transact SQL :: Create Unique ID Based On Existing Fields

Sep 1, 2015

I have the following table (Table does not have unique key id )

Last Name     First Name         DATE        Total-Chrg

Jaime               KRiSH           5/1/2015         -4150.66
Jaime               KRiSH           5/1/2015          1043.66
Jaime               KRiSH           5/1/2015          1043.66
Jaime               KRiSH           5/1/2015           4150.66
Jaime               KRiSH           5/3/2015           4150.66
Peter                Jason            5/1/2015           321.02
Peter                Jason            5/1/2015           321.02
Peter                Jason            5/23/2015         123.02

I want the results to be in following way

Uniq ID    Last Name    First Name          DATE         Total-Chrg

1                Jaime            KRiSH                 5/1/2015     -4150.66
2                Jaime            KRiSH                 5/1/2015     1043.66
2                Jaime            KRiSH                 5/1/2015     1043.66
3                Jaime            KRiSH                 5/1/2015      4150.66
4               Jaime             KRiSH                 5/3/2015      4150.66
5               Peter              Jason                5/1/2015       321.02
6               Peter              Jason                5/1/2015       321.02
7               Peter              Jason               5/23/2015     123.02

May be we may do by dense_rank or Row_Number, but I couldn't get the exact query to produce based on the above table values.  There are some duplicates in the table(which are not duplicates as per the Business). For those duplicated Unique ID should be same(Marked in Orange Color which are duplicates). 

View 4 Replies View Related

Help, Selecting Rows Based On Values In Other Rows...

Mar 25, 2002

I'm stuck. I have a table that I want to pull some info from that I don''t know how to.

There are two colomuns, one is the call_id column which is not unique and the other is the call_status column which again is not unique. The call_status column can have several values, they are ('1 NEW','3 3RD RESPONDED','7 3RD RESOLVED','6 PENDING','3 SEC RESPONDED','7 SEC RESOLVED').

i.e example, this is the existing data.

Call_id Call_Status
555555 3 3RD RESPONDED
235252 7 SEC RESOLVED
555555 7 3RD RESOLVED
325252 6 PENDING
555555 6 PENDING
325235 3 SEC RESPONDED
555555 1 NEW

This is the data I want...

Call_id Call_Status
555555 3 3RD RESPONDED
555555 6 PENDING
555555 7 3RD RESOLVED

The call_id could be any number, I only want the 6 PENDING rows where there are other rows for that call_id which have either 3 3RD RESPONDED or 7 3RD RESOLVED. If someone knows how it would be a great help.

Cheers,

Chris

View 1 Replies View Related

Transact SQL :: Finding Unique Counts Based On Consecutive Days?

Jul 9, 2015

I have a scenario here where the data looks like -

ID        Date
100      07/01
100     07/02
100    07/03
100   08/01
100   08/02
100   08/15

Now I need to find out unique occurrences of ID - 100 ( where count = unique only if the occurrences are in consecutive days, gap of even 1 day causes it to be a different instance ) - SO with the above data I should have unique occurrences as 3.efficient way to calculate this ?

View 5 Replies View Related

Getting Unique Rows

Jun 15, 2004

Is it possible to use the DISTINCT clause on just one field in the SELECT statement?

The following SQL statement causes an error:

SELECT DISTINCT appt.ref, appt.notes FROM Appointments appt

...because DISTINCT can't be used on the notes field as it of type 'text'.

How can I focus the DISTINCT keyword on just the ref field?

(I know ref is the primary key, so this example wouldn't need the DISTINCT keyword, but I've simplified a much more complex statement)


Paul

View 6 Replies View Related

SQL Server 2014 :: Query To Display Unique Comments Based On Certain Conditions

Jul 1, 2015

TABLE DEFINITION

Column NameComments
CustomerIDINT
SequenceNoINT
CommentsVARCHAR(MAX)

CUSTOMER TABLE

CustomerIDSequenceNoComments
11ABC D
12CDE
13ABC
14ABC D
15CDE
16abc
27ABC DEF
28
29ABC DEF
210DEF
211XYZ 123
212ABC
313PQ RST

OUTPUT

CustomerIDSequenceNoComments
13ABC
14ABC D
15CDE
16abc
28
29ABC DEF
210DEF
211XYZ 123
212ABC
313PQ RST

Records should be filtered by

1.Display only Unique Comments from Customer Table for all the customers,
2.If Comments are same then display the row which has maximum SequenceNo

View 2 Replies View Related

Duplicate Key But Unique Rows.

Jan 25, 2000

I have a large table that consists of the columns zip, state, city, county. The primary key "zip" has duplicates but the rows are unique.
How do I filter out only the duplicate zips.
Randy Garland

View 2 Replies View Related

How To Get Unique Combination Of Rows

Jun 18, 2004

Hi,
Following is my table:Bets

BetId GameID

500 108
500 109

501 108
501 109
501 110

502 108
502 109

I want BetId 500 and 502 to be returned as result if i give select
criteria where game id = 108,109.
Pls.Note: It should not return BetId 501 in the result, since it belongs to different combination(108,109,110).
Similarly if i give, select criteria where game id =(108,109,110) it should return
BetId 501.not the 500 and 502..which is different combination..

Hope i clarified my problem..pls help me in this regard.Thanks a lot...

View 2 Replies View Related

Getting Unique Rows From The Resultset

Nov 15, 2006

Hi,

I am trying to do a join which involves more than 3 tables. One is a parent table and the other two table have 1:n relationship with that parent table.

But duplicate records are being returned in the resultset. How do I eliminate duplicate records?

This is my query.

SELECT table1.* from table1 left outer join table2 on table1.id=table2.id
left outer join table3 on table1.id=table3.id

I tried doing DISTINCT here but with no success.

SQL with distinct clause.

SELECT distinct table1.id, table1.* from table1 left outer join table2 on table1.id=table2.id
left outer join table3 on table1.id=table3.id

This is the error I get:
The text data type cannot be selected as DISTINCT because it is not comparable

Please help.

View 4 Replies View Related

Retrieving Unique Rows

Jun 3, 2008

I have the following sql:

SELECT DISTINCT patient.patientID, patientFirstName, patientLastName, patientDOB, patientGender, completed_date
FROM patient
LEFT JOIN patient_record ON patient_record.patientID = patient.patientID
WHERE (sub_categoryID = 4 OR patient_record.allocated = 4)
AND (patient_status = 1 OR patient_status = 2 OR patient_status = 5)
GROUP BY patient.patientID, patientFirstName, patientLastName, patientDOB, patientGender, completed_date

This brings up duplicate records, my aim is to bring distinct records, now if I take out the other returned fields after patientID
and using the following sql:

SELECT DISTINCT patient.patientID
FROM patient
LEFT JOIN patient_record ON patient_record.patientID = patient.patientID
WHERE (sub_categoryID = 4 OR patient_record.allocated = 4)
AND (patient_status = 1 OR patient_status = 2 OR patient_status = 5)
GROUP BY patient.patientID

This bring up distinct results, but I need to retrieve the other fields from the database i.e. patientFirstName and patientLastName

Please can you help.

View 2 Replies View Related

New Rows Based On Existing Rows

Jul 25, 2007

Hi All,

I have the following Table

Type Name Value
x M1 5
x M2 10
x M3 20
y M1 10
y M2 15
y M3 30

Now, i need to add four more rows to the table

Type Name Value
x M1 5
x M2 10
x M3 20
y M1 10
y M2 15
y M3 35
z1 Total 15 (xM1+XM2)
z1 Diff 5 (xM3-xM1+XM2)
z2 Total 25 (yM1+yM2)
z2 Diff 10 (yM3-yM1+yM2)

Please help me.

Many Thanks,

View 2 Replies View Related

Unique Rows Of Data Query

Sep 21, 2006

How would I get the unique email addresses and its associated row of data from a SQL Server table that has no unique fields defined? If there is a duplicate email address then only show the first one and not the other rows with the same email address. Example table and data UserID             LastName        Email997249            MCCO-49       S.MCCO-49@SampleISD.org997462            BATE-62         A.BATE-62@SampleISD.org997605            DENS-05        B.DENS-05@SampleISD.org  997622            KAIS-22         A.KAIS-22@SampleISD.org997623            KAIS-22         A.KAIS-22@SampleISD.org997624            KAIS-22         A.KAIS-22@SampleISD.org997625            KAIS-22         A.ZKAIS-22@SampleISD.org997626            KAIS-22         AX.ZKAIS-22@SampleISD.org997627            KAIS-22         AX.KAIS-22@SampleISD.org   Result UserID             LastName        Email997249            MCCO-49       S.MCCO-49@SampleISD.org997462            BATE-62         A.BATE-62@SampleISD.org997605            DENS-05        B.DENS-05@SampleISD.org  997622            KAIS-22         A.KAIS-22@SampleISD.org997625            KAIS-22         A.ZKAIS-22@SampleISD.org997626            KAIS-22         AX.KAIS-22@SampleISD.org Thanks

View 13 Replies View Related

Selecting Unique Rows In A Join

Mar 20, 2008

I got the following query:SELECT TOP (8) ext.extID, ext.Quote, ext.sourceTitle, ext.extRating, gf_game.gameID, gf_game.catID, gf_game.URL, gf_game.TitleFROM         gf_game_ext AS ext INNER JOIN                      gf_game ON gf_game.gameID = ext.gameIDWHERE     (ext.Approved = 1)ORDER BY ext.extID DESC which is e.g. producing this output: 6000 -some text- Title 90 1960 2 tom-cl tom cl5999 -some text- title 90 1960 2 tom-clcl asdf5998 -some text- title 90 1959 2 tom-cl-cl asdfWhat I'd like to do now is to filter out the duplicate GameIDs (= 1960) so that just one unique row with the gameid 1960 is remaining. If I put in a SELECT DINSTINCT TOP(8) it just counts for the table ext, but I need it to count for gf_game.gameID - is that possible?Thanks a lot! 

View 9 Replies View Related

Select Unique Rows For All Columns

May 17, 2007

Using DISTINCT with SELECT have effect only for one column.
But when is needed to select (or to count) queries for all rows for all columns in a table without duplicates, doesn't work.

Select DISTINCT a1,a2,a3,a4 From Y ---> results 167 rows
Select DISTINCT a4 From Y ---> " 85 rows

Any thoughts?


Jorge3921

View 14 Replies View Related

Count Unique Rows In Each Field

Jul 24, 2013

I have a table in Access 2007 that has about 30 field names and I want to have a count of how many unique rows there are in each field. I want to have these results put into another table that will just have the field name and then the count of how many unique rows there are.

I have code in VBA that will loop through my SQL and change out the field name, but I can't seem to get the SQL right before I can start looping it. For just one field name this would be what I have to count the unique names...

So far I have this:

INSERT INTO newtable
COUNT(*) FROM (SELECT Raw_Table.FieldName, COUNT(Raw_Table.FieldName) AS CountOfFieldName
FROM Raw_Table
GROUP BY Raw_Table.FieldName);

And its not going too well.

View 1 Replies View Related

Selecting & Then Inserting Unique Rows

Sep 18, 2007

As a beginner i am having trouble with this.
i have two different tables , both have a name column, nvarchar datatype.
I would like to select from table B all the rows which contain a name which is not in table A.
Then insert these rows, into table A

tried a few different ways & just keep getting strange errors that refer to courier font ??

SQL Team Your my Hero !

View 11 Replies View Related

SQL Server 2014 :: Split Out A Field Of Comma Separated Values Based On Unique Code In Same Row?

Oct 21, 2014

I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.

E.g

Unique Code Comma Separated Field

14587934 1,5,17,18,19,40,51,62,70

6998468 10,45,62,18,19

79585264 1,5,18

These needs to be in column format or held in an array to be used as conditional criteria.

Unique Code Comma Separated Value

79585264 1

79585264 5

79585264 18

View 5 Replies View Related

SQL Server 2008 :: No Option To Automatically Partition Table Based On Unique Values Of Column?

Jun 17, 2015

A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.

View 4 Replies View Related

Insert Unique Rows In Temp Table

Nov 3, 2006

i have temp table name "#TempResult" with column names Memberid,Month,Year. Consider this temp table alredy has some rows from previuos query.  I have one more table name "Rebate" which also has columns MemberID,Month, Year and some more columns. Now i wanted to insert rows from "Rebate" Table into Temp Table where MemberID.Month and Year DOES NOT exist in Temp table.
MemberID + Month + Year should ne unique in Temp table

View 1 Replies View Related

Delete Duplicate Rows With No Unique Columns

Apr 3, 2000

I have 4 rows which are exactly the same. I want to delete one row but i do not have any unique identifing columns. How should i delete that row ?

View 1 Replies View Related

Unique Index Returns Duplicate Rows

Oct 25, 2004

We are running the following query, which has a unique index on Table_2 (col1 and sys1), and Column col1 from Table_1 is unique.

select top 100 s.*, x.col1
from Table_1 s
left outer join Table_2 x
on x.col1 = s.col1 and x.sys1 = 'SYSTEM0'

Unfortunately this query returns duplicate rows. And every time the result is different

But once we dbcc dbreindex the unique index on Table_2, the result will not have any dups.

Any ideas?

Thanks

Steve

View 1 Replies View Related

Getting Unique Rows From Multi-table Query

Mar 10, 2012

how to join these 3 tables but there are duplicate rows in the results. How can I get all the unique rows to only show up once.

My query is:

SELECT tbl_rate.rate_ID, tbl_rate.rate_site_IDref, tbl_rate.rate_rank_IDref, tbl_rate.rate_dollar, tbl_rate.rate_ot, tbl_rate.rate_dt, tbl_rate.rate_loa,
tbl_employee.employee_name, tbl_LEM.LEM_date, tbl_LEM.LEM_reg_hrs, tbl_LEM.LEM_ot_hrs, tbl_LEM.LEM_dt_hrs, tbl_LEM.LEM_tt_hrs, tbl_LEM.site_IDREF,
tbl_LEM.LEM_LOA, tbl_LEM.LEM_Expenseinfo, tbl_LEM.LEM_workorder, tbl_LEM.LEM_posted, tbl_LEM.LEM_Expense, tbl_LEM.LEM_tagnumberREF,
tbl_LEM.LEM_reg_rate, tbl_LEM.Lem_ot_rate, tbl_LEM.Lem_tt_rate, tbl_LEM.LEM_dt_rate, tbl_LEM.Lem_loa_rate, tbl_LEM.LEM_ID, tbl_LEM.LEM_equip_days

FROM tbl_rate INNER JOIN
tbl_employee ON tbl_rate.rate_rank_IDref = tbl_employee.employee_rankREF INNER JOIN
tbl_LEM ON tbl_employee.employee_ID = tbl_LEM.employee_IDREF

The results somehow need to be distinct by tbl_lems.lem_ID

View 4 Replies View Related

Select Latest Row When ID Has One Or More Rows And Each ID Unique From And To Date

Dec 8, 2013

I have a equipment table and the equipment has a coding for each place / location or custody it has had during its life. I need to select the latest (newest) row for each piece of equipment by getting the newest from_date and to_date field combination.The following is an example. I know how to get MAX date for one column but not with two columns (from and to DATES).

ID
NAME
FROM_DATE
TO_DATE

[code]...

View 1 Replies View Related

SQL 2012 :: Creating A Unique ID For Groups Of Rows?

Mar 20, 2015

We have a sorted data that looks like the first 3 columns below, and fourth colum is what I want to create)

Shift_start meal_break shift_endShift ID
1 0 0 1
0 0 0 1
0 0 1 1

1 0 0 2
0 0 0 2
0 0 1 2

I need to find a method to assign unique Shift IDS to rows that correspond to a single shift. For instance, the first shift would begin on the first row when shift_start flag is turned on, and end on the third row when shift_end flag is turned on.

Can I do this in SQL ?...some kind of grouping ?

View 2 Replies View Related

Merging Rows And Keeping Unique Values

Jan 14, 2015

I have this query and it works except for I am getting duplicate primary keys with unique column value. I want to combine them so that I have one primary key, but keep all the columns. Example:

Key column 1 column 2 column 3 column 4
A 1 1
A 2 2
B 2 3
B 5 5

it should look like:

A 1 1 2 2
B 2 3 5 5

Here is my query:

SELECT *
FROM [TLC Inventory].dbo.['2014 new$']
WHERE [TLC Inventory].dbo.['2014 new$'].mis_key LIKE '2%'
AND dbo_Product_Info#description NOT LIKE 'NR%'
AND dbo_Line_Info#description NOT LIKE 'OBSOLETE%'

Do I use a sum function?

View 7 Replies View Related

Deriving Unique Rows From Historical Data

Oct 25, 2005

My application is to capture employee locations.Whenever an employee arrives at a location (whether it is arriving forwork, or at one of the company's other sites) they scan the barcode ontheir employee badge. This writes a record to the tblTSCollected table(DDL and dummy data below).The application needs to be able to display to staff in a control roomthe CURRENT location of each employee.[color=blue]>From the data I've provided, this would be:[/color]EMPLOYEE ID LOCATION CODE963 VB002964 VB003966 VB003968 VB004977 VB001982 VB001Note that, for example, Employee 963 had formerly been at VB001 but wasmore recently logged in at VB002, so therefore the application is notconcerned with the earlier record.What would also be particularly useful would be the NUMBER of staff ateach location - viz.LOCATION CODE NUM STAFFVB001 2VB002 1VB003 2VB004 1Can anyone help?Many thanks in advanceEdwardNOTES ON DDL:THE BARCODE IS CAPTURED BECAUSE THE COMPANY MAY RE-USE BARCODE NUMBERS(WHICH IS DERIVED FROM THE EMPLOYEE PIN), SO THEREFORE THE BARCODECANNOT BE RELIED UPON TO BE UNIQUE.THE COLUMN fldRuleAppliedID IS NULL BECAUSE THAT PARTICULAR ROW HAS NOTBEEN PROCESSED. THERE ARE BUSINESS RULES CONCERNING EMPLOYEE HOURSWHICH OPERATE ON THIS DATA. ONCE A ROW HAS BEEN PROCESSED FORUPLOADING TO THE PAYROLL APPLICATION, THE fldRuleAppliedID COLUMN WILLCONTAIN A VALUE. IN THE PRODUCTION SYSTEM, THEREFORE, ANY SQL ASREQUESTED ABOVE WILL CONTAIN IN ITS WHERE CLAUSE (fldRuleAppliedID IsNULL)if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTSCollected]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblTSCollected]GOCREATE TABLE [dbo].[tblTSCollected] ([fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,[fldEmployeeID] [int] NULL ,[fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[fldTimeStamp] [datetime] NULL ,[fldRuleAppliedID] [int] NULL ,[fldBarCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOINSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (963, 'VB001', '2005-10-18 11:59:27.383', 45480)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (963, 'VB002', '2005-10-18 12:06:17.833', 45480)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (964, 'VB001', '2005-10-18 12:56:20.690', 45481)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (964, 'VB002', '2005-10-18 15:30:35.117', 45481)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (964, 'VB003', '2005-10-18 16:05:05.880', 45481)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB001', '2005-10-18 11:52:28.307', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB002', '2005-10-18 13:59:34.807', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB001', '2005-10-18 14:04:55.820', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB003', '2005-10-18 16:10:01.943', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (968, 'VB001', '2005-10-18 11:59:34.307', 98374)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (968, 'VB002', '2005-10-18 12:04:56.037', 98374)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (968, 'VB004', '2005-10-18 12:10:02.723', 98374)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (977, 'VB001', '2005-10-18 12:05:06.630', 96879)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (982, 'VB001', '2005-10-18 12:06:13.787', 96697)

View 4 Replies View Related

How To Assign Unique PKs And FKs On Split Of Txt Rows Into DB Tables

Mar 14, 2006

SSIS 2005

Ok, I have a task in SSIS that does the following and works:

1) Brings in a txt file

2) Using a conditional component, checks for a value in the row.

3) Based on the value, splits the row into one of 3 tables (Header, Maintenance, or Payment)

Here is a print screen of what I have so far which splits Header rows into it's own table, Maintenance rows into its own table, and Payment Rows into its own table:

http://www.webfound.net/qst_how_to_add_header_PK_and_FKs.JPG

Here is a print screen of the conditional split:

http://www.webfound.net/conditional_split.jpg

Please take a look at the txt file here before it's processed:

http://www.webfound.net/split.txt

http://www.webfound.net/rows.jpg

Notice that the pattern is a header row, followed by it's corresponding detail rows.  The detail rows are either Maintenance or Payment rows. 

I need to somehow during the Script component or some other way, to assign a unique HeaderID (PK) to each of the header rows and add that ID to it's corresponding Maintenance and Payment detail rows in their corresponding tables as a PK.  The problem is

1) I don't know how to do this in the flow of the components as I have it now

2) How do I tell it to create a new Header ID and Header FKs for the detail rows based off of each new Header row?

In the end (much later on in my entire package), the goal is to be able to run a stored proc to join and select the Header and Details rows back into a final table so I can then do more processing such as split each header and detail rows into their own txt files, etc....I don't need to go into details why but just know that this is the goal, therefore I need to relate each header row with their corresponding detail rows that are split off into a MaintenanceRow and PaymentRowTable

 

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved