Compare Values In Consecutive Rows And Print Rows Based On Some Conditions
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 Complete Forum Thread with Replies
Related Forum Messages:
Compare Values In Consecutive Rows
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 Replies !
Help, Selecting Rows Based On Values In Other Rows...
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 Replies !
Select Rows Based On Multiple Date Conditions
Hi, I have 1 table with 5 rows. One of the rows has dateTime values. I want to know how many rows there are with a value in that column < today AND how many rows there are with a value in that column > today. I'm not sure how to do this. SELECT Count(*) WHERE dateColumn <= today AND dateColumn > today gives me everything. I like to end up with a column containing the count of rows <= today and a column with rows where date > today. Is this possible in SQL or do I have to retrieve all rows and then loop over the resultset and check each row? Thanks, Marc
View Replies !
How To Compare Values In Different Rows?
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 Replies !
How Compare Values Of 2 Different Rows In Same Tbl
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 Replies !
Consecutive Rows
Hello, I have a stored procedure that inserts 5 rows into a table. The execution of the SP is inside a transaction like in the code below: Code Block SqlConnection conn = new SqlConnection(ConnectioString); conn.Open(); SqlTransaction trans = conn.BeginTransaction(); try { // execute stored procedure... insert 5 rows trans.Commit(); } catch (Exception ex) { trans.Rollback(); } finally { conn.Close(); } Suppose that N users are executing the code, one independent of the other, in the same time and they both commit the transaction at the same time. Can I suppose that the rows inserted in the table by one user will be consecutive? Thanks!
View Replies !
Insert Multiple Rows To Table Based On Values From Other 2 Tables.
I have a form to assign JOB SITES to previously created PROJECT. The JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT. I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES" which has only 2 columns: "ProjectId" and "SiteId". What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked. The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display. Sample: ProjectId - SiteId 1 - 5 1 - 9 1 - 16 1 - 18 1 - 20 1 - 27 1 - 31 ProjectId stays the same, only values for SiteId are being different. I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.
View Replies !
Comparing Data In Two Consecutive Rows From A Single Table
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 Replies !
New Rows Based On Existing Rows
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 Replies !
How To Find Conditions Across Rows (attendance)
Hello,I need to find students that have 4 consecutive absences. When astudent is absent 4 times in a row, they can be dropped from the class.My class attendance file contains each attendance by date and whetherthey were present or not. When the student has 4 consecutive value 1(absent) for a given session and a given class the are considered to bedropped.If I needed to know the total number of absences, I know I could groupand summarize, but this one has the consecutive twist.Table:CREATE TABLE "dbo"."clsatt"("FULL_CLASS_ID" CHAR(15) NOT NULL,"STUDENT_ID" CHAR(20) NULL,"SESSION_ID" CHAR(10) NULL,"MEETING" SMALLINT NOT NULL,"PRESENT" CHAR(2) NOT NULL);Present value of 1 is absent, value of 2 is present (3 means holiday)Classes typically meet 12 times.I would want something likeFULL_CLASS_ID, STUDENT_ID, SESSION_ID, 'Dropped'as the output.Notice in the example the first student was absent the last 4 meetingsThe second student 5 absensesand the third student was totally absentIn these three examples, they are flagged as dropped.TIARobInserts:---------------------------------------------------------------------------------insert into clsatt values ('BUS100','1675812194','200203',1,'2')insert into clsatt values ('BUS100','1675812194','200203',2,'2')insert into clsatt values ('BUS100','1675812194','200203',3,'2')insert into clsatt values ('BUS100','1675812194','200203',4,'2')insert into clsatt values ('BUS100','1675812194','200203',5,'2')insert into clsatt values ('BUS100','1675812194','200203',6,'2')insert into clsatt values ('BUS100','1675812194','200203',7,'2')insert into clsatt values ('BUS100','1675812194','200203',8,'2')insert into clsatt values ('BUS100','1675812194','200203',9,'1')insert into clsatt values ('BUS100','1675812194','200203',10,'1')insert into clsatt values ('BUS100','1675812194','200203',11,'1')insert into clsatt values ('BUS100','1675812194','200203',12,'1')insert into clsatt values ('BUS100','1712400537','200203',1,'2')insert into clsatt values ('BUS100','1712400537','200203',2,'2')insert into clsatt values ('BUS100','1712400537','200203',3,'2')insert into clsatt values ('BUS100','1712400537','200203',4,'2')insert into clsatt values ('BUS100','1712400537','200203',5,'2')insert into clsatt values ('BUS100','1712400537','200203',6,'2')insert into clsatt values ('BUS100','1712400537','200203',7,'2')insert into clsatt values ('BUS100','1712400537','200203',8,'1')insert into clsatt values ('BUS100','1712400537','200203',9,'1')insert into clsatt values ('BUS100','1712400537','200203',10,'1')insert into clsatt values ('BUS100','1712400537','200203',11,'1')insert into clsatt values ('BUS100','1712400537','200203',12,'1')insert into clsatt values ('BUS100','1801704805','200203',1,'1')insert into clsatt values ('BUS100','1801704805','200203',2,'1')insert into clsatt values ('BUS100','1801704805','200203',3,'1')insert into clsatt values ('BUS100','1801704805','200203',4,'1')insert into clsatt values ('BUS100','1801704805','200203',5,'1')insert into clsatt values ('BUS100','1801704805','200203',6,'1')insert into clsatt values ('BUS100','1801704805','200203',7,'1')insert into clsatt values ('BUS100','1801704805','200203',8,'1')insert into clsatt values ('BUS100','1801704805','200203',9,'1')insert into clsatt values ('BUS100','1801704805','200203',10,'1')insert into clsatt values ('BUS100','1801704805','200203',11,'1')insert into clsatt values ('BUS100','1801704805','200203',12,'1')insert into clsatt values ('BUS100','1922287588','200203',1,'1')insert into clsatt values ('BUS100','1922287588','200203',2,'1')insert into clsatt values ('BUS100','1922287588','200203',3,'2')insert into clsatt values ('BUS100','1922287588','200203',4,'2')insert into clsatt values ('BUS100','1922287588','200203',5,'2')insert into clsatt values ('BUS100','1922287588','200203',6,'2')insert into clsatt values ('BUS100','1922287588','200203',7,'2')insert into clsatt values ('BUS100','1922287588','200203',8,'2')insert into clsatt values ('BUS100','1922287588','200203',9,'2')insert into clsatt values ('BUS100','1922287588','200203',10,'2')insert into clsatt values ('BUS100','1922287588','200203',11,'1')insert into clsatt values ('BUS100','1922287588','200203',12,'2')insert into clsatt values ('BUS100','2188469657','200203',1,'1')insert into clsatt values ('BUS100','2188469657','200203',2,'1')insert into clsatt values ('BUS100','2188469657','200203',3,'2')insert into clsatt values ('BUS100','2188469657','200203',4,'2')insert into clsatt values ('BUS100','2188469657','200203',5,'2')insert into clsatt values ('BUS100','2188469657','200203',6,'2')insert into clsatt values ('BUS100','2188469657','200203',7,'2')insert into clsatt values ('BUS100','2188469657','200203',8,'2')insert into clsatt values ('BUS100','2188469657','200203',9,'1')insert into clsatt values ('BUS100','2188469657','200203',10,'1')insert into clsatt values ('BUS100','2188469657','200203',11,'1')insert into clsatt values ('BUS100','2188469657','200203',12,'2')insert into clsatt values ('BUS100','2515197431','200203',1,'1')insert into clsatt values ('BUS100','2515197431','200203',2,'1')insert into clsatt values ('BUS100','2515197431','200203',3,'2')insert into clsatt values ('BUS100','2515197431','200203',4,'2')insert into clsatt values ('BUS100','2515197431','200203',5,'1')insert into clsatt values ('BUS100','2515197431','200203',6,'2')insert into clsatt values ('BUS100','2515197431','200203',7,'2')insert into clsatt values ('BUS100','2515197431','200203',8,'1')insert into clsatt values ('BUS100','2515197431','200203',9,'2')insert into clsatt values ('BUS100','2515197431','200203',10,'2')insert into clsatt values ('BUS100','2515197431','200203',11,'1')insert into clsatt values ('BUS100','2515197431','200203',12,'2')
View Replies !
How To Insert Rows To Table B Based On Values In Table A.
I need Insert rows in the OrderDetails Table based on values in the Orders Table In the Orders table i have a columns called OrderID and ISale. In the OrdersDetails i have columns called OrderID and SaleType For each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 1, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value K and another row with the value KD. That is a row will be added and the value in the SalesType column will be K, also a second row will be added and the value in the SalesType column will be KD Also for each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 0, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value Q and another row with the value QD That is a row will be added and the value in the SalesType column will be Q, also a second row will be added and the value in the SalesType column will be QD. I need a SQL Script to accomplish this. thanks
View Replies !
Compare Based On Table Values.
HiBelow is a table with sample data to explain what I want to achieve.trackID member markerallele1------- ------ -------------------------------------------------------------------------------------------------------4734 4577 01-D8S1179 13.54734 4577 02-D21S11 12.64734 4577 03-D7S820 2.04734 4577 04-CSF1PO 3.04734 4577 06-TH01 4.04734 4577 07-D13S317 5.04734 4577 08-D16S539 9.04734 4577 11-vWA 6.04734 4577 12-TPOX 7.04734 4577 13-D18S51 1.04734 4578 01-D8S1179 13.04734 4578 02-D21S11 12.64734 4578 03-D7S820 2.04734 4578 04-CSF1PO 4.04734 4578 06-TH01 3.04734 4578 07-D13S317 5.04734 4578 08-D16S539 9.04734 4578 11-vWA 7.04734 4578 12-TPOX 6.04734 4578 13-D18S51 1.0Okay I am trying to compare allele1 values for differences for two orthree different member having the same trackID. The list of markerswould always be same for all members belonging to a particulartrackID.So in the above sample data. The trackID is same which is 4734. Thereare two members 4577 and 4578. The markers are same for each membersbut the corresponding allele1 values are different.I want to compare these two and state whether they are same or not. Iwould like to do this on a form containing subforms.Feel free to ask for clarification.Thank you.
View Replies !
Compare Rows As Returned
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
View Replies !
Compare Rows In Sql 2000
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?
View Replies !
Compare Rows In 2 Tables, Return Them If Different
Rows in table A that have a business status of 'open' are written to table B (on a different db) on a daily basis. I have managed to extract the relevant rows in table A and table B. What I now need to do is compare table A, row N with table B row N and return them both if the values differ. What is the best way of doing this e.g. Table A customerId amount 1 200 2 106 *return this row* 3 412 Table B (filtered by MAX(audit date)) customerID amount 1 200 2 100 *return this row* 3 412 ?
View Replies !
Best Way To Compare Two ENTIRE Rows In Seperate Tables?
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. Any thoughts?
View Replies !
Rows Based On Columns
I know this may have been asked before but can someone pls hel mw out here. i even tried to use the Crosstab SP that i found out on this site but it is not for what i need. I have a Table/View called [Shipment] with the data below. ShipNo Supplier ================= 1 CFA 1 TFA 2 LRA 2 LRB 3 ABC 4 TFA I want the following as my result. ShipNo Supplier1 Supplier2 ========================== 1 CFA TFA 2 LRA LRB 3 AB 4 TFA Thx. Rav
View Replies !
How Do I Get Unique Rows Based On StudentID
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 workDESIRED RESULTSStudentID First Name Last Name Other Columns...............................................634565491 MARINA BALDERAZ 640484566 TERE BALDERAZCURRENT 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 BALDERAZ640484566 TERE BALDERAZ640484566 TERE BALDERAZ
View Replies !
Permutate Rows Based On Table
Hi,I wish to create new rows of data based on a source table. Example: Ihave a file that contains a SESSION (time roughly a calendar quarterfor a University), START_DT, END_DT.I want to create rows that would be for each session and each day, sofor session 200102 that starts 09/10/2000 and ends 12/15/2000 I want 96rows, with 200102 for Session, and the 96 days in DAY.-- Source table:CREATE TABLE [F___Example_Date] ([SESSION_ID] [numeric](19, 0) NOT NULL ,[START_DT] [datetime] NULL ,[END_DT] [datetime] NULL) ON [PRIMARY]-- Sample data :INSERT INTO [DS_V5_Source].[dbo].[F___Example_Date]([SESSION_ID],[START_DT], [END_DT])VALUES(200102, '2000-09-10', '2000-12-15')INSERT INTO [DS_V5_Source].[dbo].[F___Example_Date]([SESSION_ID],[START_DT], [END_DT])VALUES(200103, '2001-01-04', '2001-03-26')-- Example Target file to be populated:CREATE TABLE [Target_Date] ([SESSION_ID] [numeric](19, 0) NOT NULL ,[Day] [datetime] not null) ON [PRIMARY]GO-- Example of inserts to that represent the desired results (my insertif for illustration only, not intended to be elegant)INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])VALUES(200102,'2000-09-10')INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])VALUES(200102,'2000-09-11')INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])VALUES(200102,'2000-09-12')INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])VALUES(200102,'2000-09-13')-- <... and so forth for all days between 9/10/2000 and 12/15/2000...>INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])VALUES(200102,'2000-12-15')My need is getting into a new area of my SQL experiance and I'm notsure how to approach solving this problem. I'm confident once I learnhow to solve this, I will be able to do a lot more with SQL.TIARob
View Replies !
Rollback Based On # Of Rows Changed
I am trying to create a stored proc that will update exactly one row. Simple. For insurance purposes, I want to create some logic that will rollback the entire transaction if more than one row is updated. I know that I could force the primary key into the WHERE clause, but I was looking for some logic that will allow me to bypass that. Thanks, Tom
View Replies !
Reordering Rows Based On A Condition
Hi, I have two tables : Students and StuHistory. The structure of the Student table is as follows : IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Student]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Student]( [RID] [int] NOT NULL, [Class] [int] NULL, [Section] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [SubSection] [int] NULL, [RollNo] [int] NULL, [DesiredRoll] [int] NULL, [TrackingNo] [int] NULL, [Original_rollno] [int] NULL, [StudentStatus] [int] NULL ) END GO A section has subsections where students are allocated rollno's. Every student has a unique roll no in that subsection. However he is also given a choice to enter his desired roll no. If more than one student choose the same desired roll no in that subsection/section, there is a [TrackingNo] field that then starts keeping a count. For the first unique desired roll no in that subsection/section the tracking no is always 0. [StudentStatus] represents the following : (-1 for deleted, 0 for edited, 1 for newly inserted). After every fortnight, i have to run a batchquery that does the following: 1. all students marked with -1 are moved to a table called StuHistory which has the same structure as that of Student. 2. Now oncethe -1 status students are moved, there will be a gap in the roll no. I want to reallocate the rollnos now, where rollnos = desired roll no taking into consideration the trackingno So if 4 students have chosen the desired roll no as 5 and their current roll no is scattered in a subsection lets say 7, 10, 14,16, then while rearranging they will be together(grouped by subsection/section) and will be allocated roll no's 5,6,7,8. The other students will be moved down based on their desired roll nos. Over here i have to also fill the gaps caused because of the students who were deleted. How do i write query for this? I have been struggling. I thought of posting this as a new post as it was mixed in the previous post. Script : INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (1, 1, N'A', 1, 1, 1, 0, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (2, 1, N'A', 1, 2, 2, 0, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (3, 1, N'A', 1, 3, 1, 1,0,1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (4, 1, N'A', 12, 1, 1, 0,-1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (5, 1, N'A', 12, 2, 1, 1, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (6, 1, N'A', 12, 3, 2, 0, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (7, 1, N'B', 5, 1, 3, 0, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (8, 1, N'B', 5, 2, 3, 1, 0 ,1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (9, 1, N'B', 5, 3, 3, 2, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (10, 1, N'B', 5, 4, 2, 0, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (11, 1, N'B', 5, 5, 2, 1, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (12, 1, N'B', 10, 1, 1, 0, 0, 1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (13, 1, N'B', 10, 2, 1, 1, 0, 1 ) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (14, 1, N'B', 10, 3, 1, 2, 0, -1) INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] ) VALUES (15, 1, N'B', 10, 4, 2, 0, 0, 1) Thanks.
View Replies !
Output Several Columns Based On Rows
I'll show my schema first, then I'll explain what I'm doing: -------------------------------------------------- Events -------------------------------------------------- ID | E_Title -------------------------------------------------- -------------------------------------------------- EventOptionGroups -------------------------------------------------- ID | EOG_EventID | EOG_OptionGroupID -------------------------------------------------- -------------------------------------------------- OptionGroups -------------------------------------------------- ID | OG_Title -------------------------------------------------- -------------------------------------------------- Options -------------------------------------------------- ID | O_OptionGroupID | O_Description -------------------------------------------------- -------------------------------------------------- EventRegistration -------------------------------------------------- ID | ER_EventID | ER_Name -------------------------------------------------- -------------------------------------------------- RegistrantOptions -------------------------------------------------- ID | RO_EventRegistrationID | RO_OptionGroupID | RO_Selection -------------------------------------------------- There are several events. Each event has several different sessions (stored in EventOptionGroups), and each session has a certain number of options (stored in Options). A user can sign up for an event, and their information is stored in EventRegistration. They can choose an option for each session in the event. For each option they choose, a new row is added to RegistrantOptions. For each row in EventRegistration, I want to output the user's information, and then the option they chose for each session in the event. Like this: ---------------------------------------------------------------------- E_Title | ER_Name | OG_Title1 | OG_Title2 | OG_Title3 ---------------------------------------------------------------------- Event | Bob | O_Description1 | O_Description2 | O_Description3 So in that example, that event had 3 sessions. Right now, I can only output E_Title and ER_Name, I don't know how to output the session information
View Replies !
How To Get Additional Rows Based On Column Val
Hello, I am unable to figure out how to proceed after trying for more than a day. Should I add a parameter to the stored proc? How do I proceed? I need to be able to show data for EdgeID 2,3,5,6,20,21 and so on...Right now I am showing data for 1, 4, 19 and so on based on the ReltTotID based on the result set below. This is because the table that the query below is selecting from adds up all common EdgeIDs to give one row for example EdgeID Desc TermType ReltTotID 1Global Edge Model w/ Fwd Earn II T 1 2Short Term Global Edge Model w Fwd Earn IIS 1 3Long Term Global Edge Model w Fwd Earn IIL 1 4Emerging Market Edge Model w Fwd Earn T 4 5Short Term EM Edge Model w Fwd Earn S 4 6Long Term EM Edge Model w Fwd Earn L 4 19SmallCap Edge Model w/ Fwd Earn T 19 20SmallCap Short Term Edge Model w/ Fwd EarnS 19 21SmallCap Long Term Edge Model w/ Fwd EarnL 19 35Global+EM Edge Model w Fwd Earn T 35 The final query result is : EdgeID Description Short Desc PerID UnivID DefID 1Global Edge Global Developed 500622355938 4Emerging Market Emerging Markets 500632356039 19SmallCap Edge Small Cap Edge 500642364244 I would like it to be : 1Global Edge Global Developed 500622355938 2Short Term Global Developed NULL2355938 3Long TermGlobal Developed NULL2355938 4Emerging Market Emerging Markets 500632356039 5Short Term Emerging Markets NULL2356039 6Long Term Emerging Markets NULL2356039 19SmallCap Edge Small Cap Edge 500642364244 19Short Term Small Cap Edge NULL2364244 19Long Term Small Cap Edge NULL2364244 The stored proc query is as below: SELECT EdgeModelID = em.EdgeModelID --, EdgeModelID = em.EdgeModelID , Description = m.Description , ShortDescription = ISNULL(emdn.ParameterValue, m.ShortDescription) , ViewPermissionID = emdp_perm.ParameterValue , EdgeUniverseID = univ.UniverseID , EdgeDefinitionID = univ.MemberID FROM OptMod..GO_EdgeModels em JOIN OptMod..GO_Models m ON em.EdgeModelID = m.ModelID AND m.ModelType = 'E' AND Status = 1 JOIN OptMod..GO_EdgeModelDisplayParameters emdp ON emdp.EdgeModelID = em.EdgeModelID AND emdp.ParameterName = 'NewEdge32 Screening' LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdn ON emdn.EdgeModelID = em.EdgeModelID AND emdn.ParameterName = 'NewEdge32 Display Name' LEFT JOIN OptMod..GO_ModelUniverses mu ON em.EdgeModelID = mu.ModelID LEFT JOIN OptMod..vUniverses univ ON mu.UniverseID = univ.UniverseID LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdp_perm ON emdp_perm.EdgeModelID = em.EdgeModelID AND emdp_perm.ParameterName = 'NewEdge32 Permissions' WHERE em.EdgeModelID = em.RelatedTotalEdgeModelID Thanks in advance!!! sqlnovice123
View Replies !
Selecting The Rows Based Off Of Unique Columns
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 Replies !
Combining Multiple Rows Based Off Criteria
Hello again, Another combining multiple rows teaser, during a few routines I made a mistake and I would like to combine my efforts. Here is my data: Code: Table A ID DSN VN AX Diag 1111296.54 3212318.00 Both DSNs share the same Patient_id in a seperate table which holds the DSN numbers and their corresponding patients. Code: Table B DSN Patient_id 100000001 200000001 So what I need to do is maintain their unique 'ID' number in Table A but update their DSN numbers to reflect the first instance in Table B. So my data would look like this in both tables. Code: Table A ID DSN VN AX Diag 1111296.54 3112318.00 Note: The second rows DSN changed to 1 from 2 Code: Table B DSN Patient_id 100000001 (Duplicate row removed with same patient_id) The result would look like the above but as you noticed I need to remove the duplicate row that had the different DSN in Table B so that only one DSN remains that can map to multiple rows (IDs) in Table A. Table A: DSN can map to multiple rows (IDs) IDs must be unique (aka kept to what they are currently) Table B: Second row with same DSN must be removed. Any takes, ideas? I need to do this on a couple thousand rows.... Thanks, and im happy to clarify if needed.
View Replies !
Duplicating Rows Based On A Integer Column
I have a table that holds a number of offers made to a orginization for placements at a lecture. what im wanting to do is have each of the rows for a orginization repeated so that the names of people attending can be put into the database. The first table would looks something like this: id___|__orginization_| lecture | offers 1 | orga | lec1 | 5 2 | orgb | lec1 | 3 3 | orga | lec2 | 3 the result im looking to get is something like this where the name of the attendess would be inputed in an application. id | orginization | lecture | nameofattende 1 | orga | lec1 | j. blog 2 | orga | lec1 | s. smith 3 | orga | lec1 | h. samual 4 | orga | lec1 | j. sams 5 | orga | lec1 | b.j. james 6 | orgb | lec1 | m. curry 7 | orgb | lec1 | k. murry 8 | orgb | lec1 | g. hansen Can anyone help with this cheers colin
View Replies !
Deleting Mutiple Rows Based On Info
Is it possible to delete multiple rows from multiple tables based on information specified. Can you write a query that would pull the information if you knew what tables it would need to look in? If anyone know I would greatly appreciate any help I am not sure of this. Thanks, Traci
View Replies !
Creating Rows Based On Date Range From Another Table
I wish to build a table based on values from another table.I need to populate a table between two dates from another table. Usingthe START_DT and END_DT, create records between those dates.I need a new column that is the days between the date and the MID_DTThe data I wish to end with would look something like this:PERIOD DATE DAY_NO200602 2005-07-06 -89200602 2005-07-07 -88200602 2005-07-08 -87<...>200602 2005-10-02 -2200602 2005-10-03 -1200602 2005-10-04 0200602 2005-10-05 1<...>200602 2005-12-18 75CREATE TABLE "dbo"."tblDates"("PERIOD" CHAR(6) NOT NULL,"START_DT" DATETIME NULL,"MID_DT" DATETIME NULL,"END_DT" DATETIME NOT NULL)INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)
View Replies !
Split One Row Into Multiple Rows Based On Time Elements
I'm dealing with a problem. The record information example DateTimeStart , DateTimeEnd , action , duration (seconds) 2007-02-02 10:30:22 , 2007-02-02 11:30:22 action1 , 600 what i want is for every half hour between start and end a record 10.30 action1 11.00 action1 11.30 action1 how can i create this, i'm a little stuck on this
View Replies !
Updating Multiple Rows Based On Sums From Another Table
Hello All I am trying to figure out if what i am attempting to do is possible and whether or not my approach is wrong to begin with. I am trying to build a custom report for our accounting system which is Traverse from Open systems. This is what i have done in the stored procedure thus far SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO ALTER PROCEDURE rptArFLSalesByCustItemized_sp @custId pCustID, @dateFrom datetime, @dateThru datetime, @itemIdFrom pItemId, @itemIdThru pItemId as set nocount on -- define some variables for previous year declare @LYqty int, @LyAmt money, @LYfrom datetime, @LYthru datetime -- set defaults SET @itemIdFrom=ISNULL(@itemIdFrom,(SELECT MIN(itemId) FROM tblInItem)) SET @itemIdThru=ISNULL(@itemIdThru,(SELECT MAX(itemId) FROM tblInItem)) SET @LYfrom=DATEADD(YEAR,-1,@dateFrom) SET @LYthru=DATEADD(YEAR, -1, @dateThru) -- create small temp table to hold customer info Create Table #tmpArCustInfo ( custId pCustID, custName VARCHAR (30), ) -- populate customer temp table with info Insert into #tmpArCustInfo select custId, custName from tblArCust WHERE custId = @custId -- create a temp table to hold the Data for each Item Create Table #tmpArSalesItemized ( itemId pItemId, productLine VARCHAR (12), pLineDesc VARCHAR (35), descr VARCHAR (35), LYQtySold int, LYTDQtySold int, QtySold int, LYTDsales money, totalSales money, LastInvDate datetime, ) -- populate the temp table with all of the inventory items insert into #tmpArSalesItemized select ii.itemId, ii.productLine, ip.Descr, ii.Descr, 0,0,0,0,0, NULL from tblInItem ii, tblInProductLine ip where ip.productLine = ii.productLine AND ii.itemId BETWEEN @itemIdFrom AND @itemIdThru -- update table with this years quantities update #tmpArSalesItemized SET QtySold = (select SUM(QtyOrdSell) from tblArHistDetail hd where TransId IN (select TransId from tblArHistHeader where custId = @custId) AND orderDate IN (select OrderDate from tblArHistHeader where OrderDate BETWEEN @dateFrom AND @dateThru) AND hd.partId BETWEEN @itemIdFrom AND @itemIdThru GROUP BY hd.partId ) -- Return the temp tables results select * from #tmpArSalesItemized, #tmpArCustInfo drop table #tmpArSalesItemized, #tmpArCustInfo return GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO My problems begin where i want to start updating all of the Qty's of the QtySold field. I have managed to get it to write the same sum in every field but i cannot figure out how to update each row based on the sum of the qty found for that item in the tblArHistDetails table, trouble is too that there is no reference to the custId in that table either. The custId resides in tblArHistHeader and is linked to the details table via the TransId column. So really i need to update many rows based on criteria from 2 other tables. Can anyone please help? I dont have a clue how to make this work, and most of what i have learned about sql thus far has been from opening other stored procs etc in the accounting system and just reading to see how the developers have done things. Thanks Jamie
View Replies !
Dicard Rows From Flatfile Based On Highest Rownumber
I have to import data from a flatfile into our datawarehouse. The supplier of the flat file isn't able to give me a delta, he just gives me a flat files with all the changes on a certain table. So for instance when a row in a table is updated 3 times, I get 3 rows in my flatfile with the 3 updates and I only need the last one. The determine wich record is updated I need to combine 6 columns to be sure if I'm looking at the right row in the source database. Is their a way to accomplish this? I'm pretty new to those advanced SSIS things and I tried to look for a way to do it with conditional splits etc... But honestly, I don't have a clue where to start. Any help is appreciated. Regards Zekske
View Replies !
Toggling Visibility Of Rows In A Table Based On The Value Of A Subreport
i have a report that contains a sub-report that is embeded in one of the textboxes of the table. what i am trying to do is have the main table toggle visilibity based on the value of the sub-report...in my case the toggle is very simple - if the sub-report returns any value at all, then i need the row in the parent table to be 'visible' any help would be greatly appreciated. thanks! -dk
View Replies !
Delete Multiple Rows One At A Time Based On A Condition
Hi, I have the following scenario : CustomerDetail customerid customername status app_no [status = 0 means customer virtually deleted] CustomerArchive archiveno [autoincrement] customerid customername status At the end of the month, I have to physically delete customers. I have written two stored procs: proc1 create proc spoc_startdeletion as declare @app_no int select @app_no = (select app_no from customerdetail where status=0) EXEC spoc_insertcustomerarchive @app_no -- After transferrin, physically delete delete from customerdetail where status=0 proc2 create proc spoc_insertcustomerarchive @app_no int as insert into customerarchive(customerid,customername,status) select customerid,customername,status from customerdetail where app_no = @app_no It works fine if there is only one row with status=0, however the problem is that when there are multiple rows in customerdetail with status=0, it returns 'Subquery returned more than one value' How can i transfer multiple rows one by one from the customerdetail to customerarchive and then delete the rows once they are transferred. Vidkshi
View Replies !
Finding Mismatched Rows Between Identical Tables Based On 2 Or More Cols
CREATE TABLE [RS_A] ([ColA] [varchar] (10)[ColB] [int] NULL)CREATE TABLE [RS_B] ([ColA] [varchar] (10)[ColB] [int] NULL)INSERT INTO RS_AVALUES ('hemingway' , 1)INSERT INTO RS_AVALUES ('vidal' , 2)INSERT INTO RS_AVALUES ('dickens' , 3)INSERT INTO RS_AVALUES ('rushdie' , 4)INSERT INTO RS_BVALUES ('hemingway' , 1)INSERT INTO RS_BVALUES ('vidal' , 2)I need to find all the rows in A which do not exist in Bby matching on both ColA and ColBso the output should bedickens 3rushdie 4So if i write a query like this , I dont get the right result setSELECT A.ColA, A.ColBFROMRS_A AINNERJOIN RS_B BONA.ColA <B.ColAORB.ColB <B.ColBBut if i do the following, i do get the right result, but followingseems convoluted.SELECT A.ColA, A.ColBFROMRS_A AWHERE ColA + CAST(ColB AS VARCHAR)NOT IN (SELECT ColA+CAST(ColB AS VARCHAR) FROMRS_B B)
View Replies !
Compare Two Tables For The Following Conditions
Have a table A and B. Theye have the same structure. How would l write a procedure or function that can be used to compare these two tables to check for the following conditions? a) sum of transaction amount and records between '2002-01-01' and '2002-01-02' if its different by how much,if okay then print balanced b) sum of transaction amount and records between '2002-01-01' and '2002-05-31' if its different by how much,if okay then print balanced This is the script that l'm currently usin but l'm there is a clever way of doing this cause its a monthly procedure Select accountno, sum(transactionamount) as Tot_Tran_Amt From A Where effectivedate > '2002-01-01 00:00:00.000' and effectivedate < '2002-05-31 23:59:00.000' and product in ('PR060','PR061','PR082','PR091','PR096','PR111',' PR121','PR122') and transactiontype in ('TR001','TR003','TR011','TR013','TR027','TR028',' TR042','TR043','TR044','TR045','TR998','TR999') and (journaltype not in ('JT000','JT720','JT721','JT722','JT723','JT725',' JT726','JT729','JT730','JT737','JT738', 'JT739', 'JT740','JT743','JT746','JT751') or journaltype is null) group by accountno
View Replies !
Column Values To Rows?
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 Replies !
Concatnate Values For Different Rows
Hi , I have a situation where i need to concatnate values from different rows and store it a one string. sample dealid date 1 1/5/2007 1 2/4/2009 2 5/5/2004 2 8/5/2006 2 4/8/2006 so for one particular deal how many ever dates there are , i need to concatnate them all separated by a comma(,) and return and one string. Is ther any way i could do it, Any suggestions appreaciated Thanks Ashish
View Replies !
Spilting Values In Rows.
Hi guys, I have a table like this. Declare @tbl as table(Invoiceid int,Dc varchar(100)) Insert into @tbl Select 1,'101,102,104'union all Select 2,'105,106,107' union all Select 3,'110,111,112,113,114' union all Select 4 ,'115' I want to split the values in the column Dc.The output should be Invoiceid Dc 1--------- 101 1--------- 102 1--------- 104 2--------- 105 2--------- 106 2--------- 107 ... and so on. Thanks for any help.
View Replies !
Adding Values Of Rows In Ms Sql 2000
I am building a website in asp.net 1.1 with vb.net 2003 which will have the standings of the teams in our baseball league. Below is the database table I have created. ID(int) home_team (nvarchar) away_team(nvarchar) win_teampf(nvarchar) lose_teampf(nvarchar) 1 Elmwood Murdock 7 22 Louisville Manley 4 33 Manley Elmwood 9 8 ID is the primary key. What I am attempting to do is add each instance of Elmwood from the win column to output the total number of wins from Elmwood and do the same for Elmwood in the losing team to output the total number of losses. The result will look something like this: Elmwood: 1 Win 1 Loss. .500 Thanks for your reply.
View Replies !
Get Rows With Duplicate Values In Certain Columns
Hi there,I would like to know how to get rows with duplicate values in certaincolumns. Let's say I have a table called "Songs" with the followingcolumns:artistalbumtitlegenretrackNow I would like to show the duplicate songs to the user. I considersongs that have the same artist and the same title to be the same song.Note: All columns do not have to be the same.How would I accomplish that with SQL in SQL Server?Thanks to everyone reading this. I hope somebody has an answer. I'vealready searched the whole newsgroups, but couldn't find the solution.
View Replies !
Checking Obligated Values In Different Rows
Hi there!I have a problem I can't get rid of... I hope anyone can help me with it!For a room-booking application I need to select available rooms for a givenperiod. First I do the availability check which is no problem, than I needto find out if there is a tariff available for every night of the givenperiod. If not, I will not show the room as 'available' in the systembecause I can't make a tariff calculation.All tariffs are entered in a table which can hold different night-tariffsfor different periods. The table looks like this:========CREATE TABLE [dbo].[TARIEVEN] ([TARIEF_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[FK_OBJECT_ID] [numeric](18, 0) NOT NULL ,[STARTDATUM] [datetime] NULL ,[EINDDATUM] [datetime] NULL ,[TARIEF_PRIJS] [decimal](10, 2) NULL ,) ON [PRIMARY]GOFK_OBJECT_ID is the Room IDSTARTDATUM is the startdate of a tariff periodEINDDATUM is the enddate of a tariff periodTARIEF_PRIJS is the tariff per night in the tariff period========What I do at the moment is:--------SELECT fk_object_id FROM TARIEVENwhere ('15-oct-2004' BETWEEN STARTDATUM AND EINDDATUM)AND ('16-oct-2004' BETWEEN STARTDATUM AND EINDDATUM)AND ('17-oct-2004' BETWEEN STARTDATUM AND EINDDATUM)--------This will work if all nights are in the same tariff period, but it will notproduce the right results if the tariffs are in different periods because nosingle row contains a startdate and enddate in between which all dates are.I can't use an OR statement because I absolutely need a tariff per night.So, what my question is in short:How can I for example select OBJECT 1 from the following table consideringthat I want to return all rooms which have a tariff available for a periodfrom 15th of october to 17th of october?-----------------FK_OBJECT_ID STARTDATUM EINDDATUM TARIEF_PRIJSFK_OBJECT_ID STARTDATE ENDDATE TARIFF1 10-oct-2004 16-oct-20041001 17-oct-2004 18-oct-2004125In words: object 1 costs 100 per night in the period from 10 to 16 octoberand 125 in the period from 17 to 18 october------------------I hope anyone can help me. Thanks a lot in advance!!Robert
View Replies !
Subtract Values From Different Rows Within Query
I have a query that uses rollup to get totals and top 101 to restrict the rows to top 100 rows plus the total column. The total column shows total for all rows not just the top 100 which is exactly what i need. The only thing I need now is a row that shows total for all rows that have been excluded, or the total row - total of the top 100. Here's the query: (Biggest factor i have to keep in mind is query speed.) Code Block WITH CTE_name AS ( SELECT TOP 101 b.name, SUM(ISNULL(CAST(b.launched AS BIGINT),0)) AS quantityPrepared, SUM(ISNULL(CAST(b.bounced AS BIGINT),0)) AS bounced, SUM(ISNULL(CAST(b.delivered AS BIGINT),0)) AS delivered FROM dimension tt INNER HASH JOIN batch b ON (b.batch_id = tt.batch_id) WHERE tt.datelaunched >= '11/19/2007' AND tt.datelaunched < '11/20/2007' AND tt.id = 1 GROUP BY b.name WITH ROLLUP ORDER BY SUM(ISNULL(CAST(b.launched AS BIGINT),0)) DESC ) SELECT name, quantityPrepared bounced, delivered FROM CTE_name ORDER BY 2
View Replies !
Query To Get Rows Which Match With All Given Values
Hi all, I would like have your help about a query. In fact, I have a query to retrieve the rows for specific ID. Like that: SELECT * FROM TblUser u WHERE EXISTS ( SELECT * FROM TblScore s WHERE s.FKIDUser = PKIDUser ) With this query, I retrieve all users for which ones there are some scores. Now, I need to get only users with specific score. In the table TblScore, there is a column ScoreValue. This column contains a value between 1 and 15 I would like to retrieve the users having score equal to 2,4 and 6 I could add a where clause like that: "and scorevalue in (2,4,6)" But I want only users having these and only these scores, not less, not more. So if an user has the following scores: 2,4,6,8, I don't want to retrieve it If an user has the following scores: 2;4, I don't want to retrieve it. If an user has the following scores: 2,4,6, I want it. Someboy would have an idea at my problem ? Thanks in advance Jerome
View Replies !
How Do I Insert Values Into CE From A DataGridView Row/rows?
Hi all... can someone tell me why this does not work and how to make it work???? SELECT SQLkey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList, PropDCost, PropDGMDollar, PropDGM, PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus, create_timestamp, update_timestamp, update_originator_id, create_date FROM ProposalDetail INSERT INTO temp ( SQLKey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList, PropDCost, PropDGMDollar, PropDGM, PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus, create_timestamp, update_timestamp,update_originator_id, create_date ) VALUES ( '1', '3/20/2007 8:13:10 AM', 'SomeUser', 'b1', '4', '0761046', 'APP', 'Miscellaneous Options', '54.000', '27.00', '27.00', '50.00', '54.00', '54.00', '54.00', '1', 'Active', '3/20/2007 8:13:10 AM', '3/20/2007 8:13:10 AM', '0', '3/20/2007 8:13:10 AM' ) WHERE (PropDNumb = 'b1') AND (PropDRevNumb = '1') Thanks a ton!
View Replies !
Calculating Change Of Values Between Rows
Hello forum. I have three columns in my table with the following datatypes: Date - DateTime Height - Decimal Change_From_last_Height - Decimal I am using "SQL Server 2005 Express Edition". I'm fairly new to SQL and would greatly appreciate any help or advice I can get. The "date" column increases by an extra day in every new row and I then enter the new height of the plant. What I want to know is how I can get SQL server express to automatically enter the difference in height between the current row's height and that of the previous row. Is it possible to automate the entry in the Change_From_Last_Height column in SQL? Put another way, I know how to find the difference between two values in the same row but different columns, but how do I calculate the difference between values in adjacent Rows (ie. Rows next to each other)? Thank you.
View Replies !
|
TRACKER
