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%.
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
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.
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.
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.
I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).
I have a table of items (i.e. products). In some cases, items can be components of another item (called "Kits"). In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". In our Items table, we have a field called "Parent_Item_Id". Records for Child Items contain the Item Id of the parent. So a sample of my database would be the following:
Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.
So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. So an example of the report would be the following:
Name | Available Qty -------------------------- Kit A | 15 Kit B | 25 Item 4 | 100
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.
Provide Individual Page Numbering per Group and Total Pages per Group
Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.
I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.
I have an SSRS report with groups that when exported to excel contains drill-in's (plus marks on left side). The issue I have is that for all the groups in the drill-in, those cells become merged. I want to keep the group drill-in but have the cells UNMERGED. I have heard this can be done with the RDL XML but I don't know what to modify to accomplish this.
Hi I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.
To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.
On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.
I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks, Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful: http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have been able to create a sample file with two of my record types.
I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.
does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?
Thanks in advance for any help you might be able to provide.
