Looking to see if thier is a better way to find the last record entered in a group of records.
What I'm doing now is finding the max for the secound column and then doing a sub query to find the max of the third column having the second columns equal.
Table example using simplied data.
PolId
CoveragId
EffDate
Status
Limit1
2
1
9/7/2007
a
10000
2
2
9/7/2007
a
150000
2
2
10/1/2007
a
200000
3
1
9/7/2007
a
10000
The parent program addes a row every time the data is changed. To make things worst; the records arn't always in sqenal order like the above table and some time edits the row instead.
The current query returns a single value. from a single table.
Current code used in the select protion on a larger query. bpi = basicpolicyInformation.
( Select c1.limit1 From AFW_Coverage as c1 Where c1.PolId=bpi.PolId and c1.CoverageId = (select max(CoverageId) as CoverageId From AFW_Coverage as c where c.PolId = c1.PolId and c.CoverageCode = 'Dwelling' and status <> 'D' ) and c1.effDate = (select max(Effdate) as Effdate From AFW_Coverage as c where c.PolId = c1.PolId and c.CoverageID = c1.CoverageId )
Explain the current code. It uses the two sub queries to find the correct record ID that has the data needed.
If I have a sorting which gives me multiples of column C1 but which can be uniquely identified by C1+C2+C3, and I want to query for the first C1 in each of C1's unique values, how would I go about doing that?
A small problem here, I have the below table and I need to group and display the record that has the minimum value in the table (this table is derived from a query that permutates some records to give me this result).
F1 F2 F3 QQQ C 2 QQQ B 1 QQQ A 3
expected result: QQQ B 1
my result: when I group by F1, First(F2) and MIN(F3): QQQ C 1
when I group by F1, MIN(F2) and MIN(F3): QQQ A 1
when I group by F1, F2 and MIN(F3): QQQ C 2 QQQ B 1 QQQ A 3
Select Date, Item_Code, SUM(In_Quantity) as In_Quantity, SUM(Issue_Quantity) as Issue_Quantity, (SUM(In_Quantity)-SUM(issue_Quantity)) as BalanceQty from
( select tbl_add_product.Date as Date, tbl_add_product.Item_Code, tbl_add_product.In_Quantity, 0 as Issue_Quantity from tbl_add_product where Item_Code = 'pen' union ALL select tbl_issue_product.Date as Date, tbl_issue_product.Item_Code, 0 as In_Quantity, Issue_Quantity from tbl_issue_product where Item_Code = 'pen' ) X group by Item_Code, Date
I want to 100 in second row. the logic is that balance Qty from first row should be added to In_Qty so that when Issue_Quantity is subtracted from it, it gives BalanceQty
I have been attempting to figure out - in Report Builder - how to print only the last record in each group, i.e. the last activity. I see no Last function available anywhere.
Would someone please be so kind as to advise how this can be done?
If this cannot be accomplished in Rpt Builder, it looks as if the Last function is available within Report Designer. Unfortunately, although documentation states it's available, it does not state where this function can be utilized. I'm certainly having no luck.
select 'PURCHASE' as EntityName, d.PartnerName, h.* from ( select MAX([TimeStamp]) [Data import], COUNT(1) [Numar de inregistrari], StartDate, EndDate, DistributorId from DataImport.PurchaseHistory group by DistributorId, StartDate, EndDate ) h inner join Partner d on d.PartnerId = h.DistributorId where d.Active = 1 order by DistributorId, StartDate desc, EndDate desc
I'm looking for some sql syntax that will return the last entry per group in a secondary table. MEANING: have 2 tables, one with names and the other with visits. I need to be able to display all the patients with there last visits.
I have for example a table with columns name, surname, id, ..., weight, age. I need to choose from each age group (GROUP BY age) entire record of the person who has the greatest weight. How to construct such a query?
The earlier versions will still exist on the database, but the latest version will be 2.1.
There may be several different documents, with different DocumentID’s (e.g. DocumentID = “1”, DocumentID = “2”), etc., and each of these documents may have many versions.
I’m trying to write a query to display a list of documents showing ClientID, ProjectID, DocumentID, MinorVersion, MajorVersion, Name… but the list should only display the latest version of each document.
So, if the database contained the following records:
ClientID, ProjectID, DocumentID, MajorVersion, MinorVersion, Name 1,1,1,0,1,My Document 1,1,1,0,2,My Document 1,1,1,0,3,My Document 1,1,1,1,0,My Document 1,1,1,2,0,My Document 1,1,1,2,1,My Document 1,1,2,0,1,My Second Document 1,1,2,0,2,My Second Document 1,1,2,0,3,My Second Document
My query should return:
ClientID, ProjectID, DocumentID, MajorVersion, MinorVersion, Name 1,1,1,2,1,My Document 1,1,2,0,3,My Document
… where 2.1 is the latest version of Document 1 and 0.3 is the latest version of Document 2.how to do it.
Hendra writes "I'd like to know is there any way or any statement that can help me to get first or last record from the group by query ? I'm using sql Server 2000 Thank's
What I would like to do is obtain a summary of timecard hours for each phase.
So for example, in Phase 1, Planning, this has two tasks based on PhaseID=1, Planning and Management which have task number prefixes of 1.01 and 1.99 respectively.
What I would like to is sum all the timecard records are LIKE 1.01% or 1.99% so my final results based on the above data would provide the phase name and total hours.
Planning Phase total Hours Planning 23 (Sum of records 1,2,3,8) Analysis 12 (Sum of records 4,7) Coding 8 (Sum of records 5,9,11) Testing 4 (Sum of record 10) Documentation 6 (Sum of record 6)
I have tried various select statements, but helps up when I want to have TaskNumberPrefix LIKE taskNumber%.
I have a question about selecting only the first record in a group. Example: I have table A with primary key = 999. Table B has multiple records with primary key = 999. How can I match Table A 999 with the first occurrence of 999 in Table B, and then extract other field data (such as street address) from the Table B record. I have tried using the Count() function, but it seems that I can only do this using cursors.
and now I need to update the field col1 of the first records with different col2 value. that means I need to update the col1 filed of the * marked record.
any suggestion about how to do it with SQL server 2000?
CREATE TABLE #MyTable ( Pk INT, GroupID INT, Value VARCHAR(10)
[code]...
I am looking to retrieve any GroupID in which every Value of that GroupID is either (a) null, (b) an empty string, or (c) "XYZ". So in the above example, GroupID #1 would not be returned in my query because there is a Value of "ABC", but GroupID #2 would be returned since it consists of only nulls, "XYZ"'s, and empty strings.What would be the most efficient way to write such a query?
I have a table from cars GPS positions: CREATE TABLE Positions ( Ident VARCHAR(20) NOT NULL, Valid BOOL, Date DATETIME NOT NULL, Latitude FLOAT, Longitude FLOAT, Speed INT }
Sorry about the poor english... :) I like select the last position where Ident=DEMO1 and DEMO2.... How can I make that? Practically I sould like to now, where is the last positions of selected cars! I try this, but not good: SELECT MAX(date), ident, latitude, longitude, speed FROM positions WHERE valid=1 && (ident='DEMO1' || ident='DEMO2'|| ident='DEMO3') GROUP BY ident ;
I get 3 record, the dates is the last, but the lat. and long. is wrong...
a record value instead of aggregated value with GROUP BY?
Assume that I have a PRODUCT_COMMENT table defined as below. It logs the multiple comments for products. A product may have multiple comments logged at different time.
Code Block
CREATE TABLE [dbo].[PRODUCT_COMMENT]( [COMMENT_ID] [int] IDENTITY(1,1) NOT NULL, [PRODUCT_ID] [int] NOT NULL, [COMMENT] [nvarchar](50) NULL, [UPDATED_ON] [datetime] NOT NULL, CONSTRAINT [PK_PRODUCT_COMMENT] PRIMARY KEY CLUSTERED ( [COMMENT_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO ALTER TABLE [dbo].[PRODUCT_COMMENT] WITH CHECK ADD CONSTRAINT [FK_PRODUCT_COMMENT_PRODUCT] FOREIGN KEY([PRODUCT_ID]) REFERENCES [dbo].[PRODUCT] ([PRODUCT_ID]) GO ALTER TABLE [dbo].[PRODUCT_COMMENT] CHECK CONSTRAINT [FK_PRODUCT_COMMENT_PRODUCT]
I would like to use the following SQL statement to get the latest comments for all products.
Code Block
SELECT PRODUCT_ID, COMMENT, UPDATED_ON FROM PRODUCT_COMMENT GROUP BY PRODUCT_ID HAVING UPDATED_ON = MAX(UPDATED_ON)
But this leads to the following error:
Code Block
Column 'PRODUCT_COMMENT.UPDATED_ON' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
--I want to get only duplicate records, so SQL should eliminate uniq recod.Total will --get total number of records per ID and date, but want to display only two reacords by --Id and Testdate. --my result set should contain following data
--Total of first byID and by Date ----id code TestDate Total ----12 ABC 2007-01-11 00:00:00.000 3 ----12 ABC 2007-01-11 00:00:00.000
I have a table where I am grouping on one field and would like an individual (separate) count of values from another field of same table. So for example, I have following data:
instance_id, area, values 101 North 1 102 North 2 103 East 2 104 East 2
I would like to report on Area, and count of rows with different Values types, for example:
Area Value - 1, Value - 2, Value - 3 North 1 1 0 East 0 2 0
I am not sure what the technical term is for such report, but I can group by Area column, and but its aggregating counts on different Value types that I am having difficulty in performing in SSRS.
CREATE TABLE #TestTable ( Pk INT, GroupID INT, Enabled BIT
[code]..
I need to write a select query that will retrieve any GroupID in which every record has an Enabled value of 1.In the example I've provided, only GroupID 1 and 3 will be returned since GroupID 2 has a record with an Enabled value of 0.What would be the most efficient way to write such a query?
I've a problem with the following stored procedure Select . It does compile and run, but doesn't return the sorted result I wanted, which was to have the records from tblPieces (alias Pcs) sorted by (in order) Pcs.fkBatchId, Pcs.fkProfileCode, Pcs.Colour. What happens instead, I think, is that the correct records are selected, but in the record creation order. CREATE PROCEDURE dbo.LoadOneBatch ( @BatchId int, -- the pkBatchId of the batch required @OnlyNew bit -- If true, only consider batches that haven't ever been loaded. ) AS SET NOCOUNT OFF SELECT Bat.pkBatchId, Pcs.* FROM tblBatches AS Bat JOIN ( SELECT Bat1.pkBatchId, Pcs1.fkProfileCode, Pcs1.Colour FROM tblBatches AS Bat1 JOIN tblPieces AS Pcs1 ON Pcs1.fkBatchId = Bat1.pkBatchId WHERE Bat1.pkBatchId = @BatchId GROUP BY Bat1.pkBatchId, Pcs1.fkProfileCode, Pcs1.Colour ) SubQ ON SubQ.pkBatchId = Bat.pkBatchId JOIN tblPieces AS Pcs ON Pcs.fkBatchId = Bat.pkBatchId WHERE (@OnlyNew = 1 And Bat.IsLoaded = 0 And Bat.IsCompleted = 0 And Bat.pkBatchId = @BatchId) Or (@OnlyNew = 0 And Bat.pkBatchId = @BatchId) GO EXEC LoadOneBatch @BatchId = 1, @OnlyNew = 0
The DDL for the two tables is:CREATE TABLE [tblBatches]( [Stamp] timestamp NOT NULL, [pkBatchId] int IDENTITY(1,1) NOT NULL, [BatchNo] varchar(50) NULL, [SubmitTime] [datetime] NULL, [FinishTime] [datetime] NULL, [IsLoaded] bit NULL, [IsCompleted] bit NULL, PRIMARY KEY ( [pkBatchId] ASC ) ) CREATE TABLE [tblPieces]( [Stamp] timestamp NOT NULL, [pkPieceId] int IDENTITY(1,1) NOT NULL, [fkBarId] int NULL, [fkBatchId] int NULL, [PieceNo] varchar(12) NOT NULL, [Descrip] varchar(48) NULL, [Position] real NULL, [LeadPrep] char(1) NOT NULL, [TailPrep] char(1) NOT NULL, [Length] real NOT NULL, [fkProfileCode] varchar(10) NOT NULL, [Colour] varchar(5) NOT NULL, PRIMARY KEY ( [pkPieceId] ASC ) )
The data records output are roughly:pkBatchId Stamp pkPieceIdfkBarIdfkBatchIdPieceNo Descrip PositionLeadPrepTailPrepLengthfkProfileCodeColour 1 0x00000000000036B21 NULL1 00000000000118960 /003/F>1 N2NULL / 913 6000 wht 1 0x00000000000036B32 NULL1 00000000000218960 /003/F<1 N2NULL / 913 6000 wht
I am using the following query (which works fine):
select min(timex) as start_date ,end_date ,entityid ,entityname ,locationid
[code]....
However I would like to not use the delta (it takes effort to calculate and populate it); instead I am wondering if there is any way to calculate it as part / whilst running the query.
Problem 2:I have the following table which shows the location of different people at 1 hour intervals
select DayRank = ROW_NUMBER() OVER(ORDER BY a.datedel DESC), a.order,a.line,a.datedel,a.recpt,b.status, b.item,b.t_sup from historytbl a inner join order b on a.order = b.order and a.line = b.line and a.status =4 group by a.order,line,a.datedel,a.recpt,b.status,b.item,b.sup
I'm bulk loading employees into an etl table, each employee has a unique ID number, but they have multiple records in the data. Sometimes their name or birthdate will change and I want to identify those records and only insert the newest version into production. I can do this with a series of temp tables, but I'm sure there's a better way. The SQL below updates the etl table with the flag I want to mark the inserts, but it seems convoluted. (Jon's birthday changes, Jane's birthday changes, Bill's gender changes, Amy nothing changes(I handle those inserts later))
