Hiding Subtotal Rows When There Is Only 1 Row In The Group

Feb 13, 2007

How could one do this? I understand you could use the COUNT() function, but I'm not sure which object's visibility would best support this. All that I've tried (subtotal area, group visibility) do not seem to work.

If you change the visible property on the subtotal textbox that RS adds, it will only 'blank out' the area where the subtotal row would have been - this doesn't achieve the desired effect of saving space.

View 5 Replies


ADVERTISEMENT

Hiding Header Row In A Group

Mar 21, 2007

I have a table with a group. I have one row of the group that is the header for the detail section. How can I suppress the row header in the group if there is no data in the detail section for a group value? I was thinking something along the line of setting the visibilty of the row header to an expression based on the existence of data in the detail, but don't know how to go about this.

Thanks.

View 2 Replies View Related

Group By / Hiding Report Items (Newbie)

Apr 3, 2008

Hi. First, I am VERY new to SQL Queries and Reporting. A co-worker is "mentoring" me, but I am trying not to fill his day with questions.

I HAVE read the help files, searched the forums, looked at books, and done general web searches, but any answers I have found have either no addressed my issue, or the answers are way over my head.

Furthermore, the (SQL 2000) DB is built into proprietary software (ISS Proventia Intrusion Prevention System), and the database may NOT be modified outside of the software.

With that said, I am querying multiple tables within the DB. I am using Business Intelligence Dev Studio, and placing my queries on a reporting server maintained by my co-worker. My goal is not only to get a solution, but also to UNDERSTAND it so I can continue to learn. Of course, the solution takes precedence over my understanding!

My Primary key is dbo.SensorData1.SensorDataID. dbo.SensorDataAVP.AttributeText returns a different number of rows, containing different data depending upon the value of dbo.SensorData1.AlertName. I need to return all rows, hence the Left Joins.

Depending upon my query, I might have 1000 events, and due to the many rows of data from dbo.SensorData1.AlertName I might return 20,000 rows (or more.)

I would like to return a report that "groups" events by dbo.SensorData1.SensorDataID., BUT, rather than simply providing these in groups, provides me single rows with a plus sign next to each even, that can be expanded for the additional data.

My co-worker has discussed sub-tables, but since I cannot modify the DB, it will be difficult / complex to do so, AND, for me to understand.

One of my queries follows. I have thirteen queries, total, that use various groupings of attributes. I have chosen one of the more complex combinations so I can generally apply the concept to the queries with fewer parameters more easily.

Note, I'll be asking the same question on www.sqlservercentral.com in the hopes of getting an answer I can understand one of these two places - If you answer here, there's obviously no need answering there answering there.

Thank you in advance.



SELECT
convert(nvarchar(20), AlertDateTime,120)
AlertDateTime,
AlertName,
AlertPriority,
AlertCount,
convert(varchar,(convert(bigint,SrcAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,SrcAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) % 256)))
SrcAddressInt,
SourcePort,
SourcePortName,
convert(varchar,(convert(bigint,DestAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,DestAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,DestAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,DestAddressInt) % 256)))
DestAddressInt,
DestPortName,
dbo.SensorData1.ObjectName,
SensorName,
SensorInterfaceName,
AlertTypeID,
convert(varchar,(convert(bigint,SensorAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,SensorAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,SensorAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,SensorAddressInt) % 256)))
SensorAddressInt,
ProtocolID,
Cleared,
VulnStatus,
dbo.SensorDataAVP.SensorDataID,
dbo.SensorDataAVP.AttributeName,
dbo.SensorDataAVP.AttributeDataType,
dbo.SensorDataAVP.AttributeText,
dbo.SensorDataAVP.AttributeValue,
dbo.SensorDataAVP.AttributeBlob,
ResponseTypeName,
ResponseName

from
dbo.SensorData

LEFT JOIN

dbo.SensorDataAVP
ON dbo.SensorDataAVP.SensorDataID =
dbo.SensorData1.SensorDataID

LEFT JOIN
dbo.SensorDataResponse
ON dbo.SensorDataResponse.SensorDataID =
dbo.SensorData1.SensorDataID

LEFT JOIN
dbo.ObjectView
ON dbo.ObjectView.ObjectName=
dbo.SensorData1.ObjectName


WHERE
convert(nvarchar(20), AlertDateTime,120) between @StartDate and @EndDate

AND
convert(varchar,(convert(bigint,SrcAddressInt) / 256 / 65536)) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) /65536) % 256)) + '.' +
convert(varchar,(convert(bigint,SrcAddressInt) /256) % 256) + '.' +
convert(varchar,((convert(bigint,SrcAddressInt) % 256)))

between @LowerIP and @UpperIP

AND
AlertName = @EventName

View 2 Replies View Related

Trouble Hiding A Group With A Subreport In A Table

Mar 17, 2008










Let me first describe my report: I have the following table

Header
Group 1 row--There is a textbox that in this row that when toggled makes visible the two below group 1 rows and the detail row.
Group 1 row
Group 1 row
Detail row
Group 2 row-- This row has a textbox that can hide the below group 2 row.
Group 2 row-- This row contains a subreport.

Currently, I have a parameter that allows the user to conditionally collapse or show the tables' information for printing purposes so the user doesn't have to go through and click on so many plus signs. My problem is I would like to use a parameter to conditionally hide both of the Group 2 rows so that the user cannot view or click on them. The information in Group 2 is extra and not always needed, so I would like to provide the user with a way to hide this information when they go to view the report. However, I imagined this would be easy enough. I went to group2's visibility tab through the table interface and set the expression to "=IIF(Parameter!ShowIndexes.Value, FALSE, TRUE)". However, when I view the report the report is hiding the detail information as well as the Group2 rows.

Anyone have any ideas?

View 1 Replies View Related

Hiding A Group Also Hides All Nested Groups

Mar 7, 2007

I have several nested sub-total groups. Depending on a report parameter, I may want to suppress one of the sub-totals. This one group is not the lowest group in the hierarchy and, when I hide it, all the groups "below" it become hidden too. I only want the one group to "disappear", not any others. Is there a way to accomplish this other than creating two versions of the report?

View 1 Replies View Related

Trying To Group && Subtotal By Year+Month

Feb 20, 2008

Using SQL 2000 Report Designer & Visual Studio 2003 and a report for MS CRM 3.0

I need to group by Year + Month, like now would be "2008 02". Am using the FilteredOpportunity.estimatedclosedate.
I have tried using YEAR(FilteredOpportunity.estimatedclosedate)+MONTH( FilteredOpportunity.estimatedclosedate), but does not work. Also YEAR( FilteredOpportunity.estimatedclosedate.Value). I don't store "2008 02" type data in the CRM.

Any ideas?

View 3 Replies View Related

Group Subtotal And Grand Total

Jun 12, 2007

how to add group subtotal and grand total in report? i try to add formula Sum(Field!Net_Weight.Value) in group footer and unable repeat footer on each page, it return same total on every pages. I hope to get subtotal on each page by group. the expected result would be like this:









Page1












1.Group 1





Date
D/no
Net Weight

6/1/2007
A00000100
10.45

6/1/2007
A00000101
10.95

6/1/2007
A00000102
11.45

6/1/2007
A00000103
11.95

6/1/2007
A00000104
12.45

6/1/2007
A00000105
12.95



Subtotal
70.2








Page 2












Date
D/no
Net Weight

6/1/2007
A00000100
20.15

6/1/2007
A00000101
20.25

6/1/2007
A00000102
20.35

6/1/2007
A00000103
20.45

6/1/2007
A00000104
12.45

6/1/2007
A00000105
12.95



Subtotal
106.6










Grand Total=
176.8















2.Group 2





Date
D/no
Net Weight

6/1/2007
A00000100
10.45

6/1/2007
A00000101
10.95

6/1/2007
A00000102
11.45

6/1/2007
A00000103
11.95

6/1/2007
A00000104
12

6/1/2007
A00000105
12.95



Subtotal
69.75
anybody know how to do it?

View 3 Replies View Related

Adding Subtotal To Group Columns

Mar 6, 2008



Hello Friends,
I have created a report using SSRS and in that report I am using group rows in one of the matrix. When I tried to display the subtotal of that group row by using the SSRS in-built feature the subtotal column is coming at the last of the matrix columns but the subtotal its showing is wrong . Its just giving the value of the first columns value.

Can anyone help me on this issue.

Thanks & Regards
Shivanandan Gupta

View 6 Replies View Related

SubTotal With Reportitems In Table Group

Oct 29, 2007

Hi,

Here is my problem,

I want to sum a textbox in my table group using =Sum(Reportitems!txt_erreur_cmt.Value,"table1_Group3")

but i always the same error as :

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope

But my sum is the table group footer.

I have no ideas how do that sum.
Does anyone can help me ?

Best regards

View 4 Replies View Related

Matrix Report 4th Row Group Subtotal Row Color

Oct 18, 2007

I'm dealing w/ SSRS 2005.
I have my main matrix report which has five row groups.

What I'd like to do is have the subtotal at the 4th level have a coloring for the whole row at run-time....so the user can follow from left to right what the 4th level subtotal actually is (the report can get fairly wide).

At design time, you don't even see the rows to the right of the subtotal, you just see the subtotal box.

Thanks!

View 1 Replies View Related

Hiding Succeeding Rows In A Table

May 20, 2008

My scenario is:
I got this ReportItems.Value which is an Integer lets name it Value1.
I got this rowcount and lets name is Value2.

Can you help me generate a query wherein:
1.Value1 = Value2
2. And the Row from Value2 and its succeeding Rows be deleted or be hiden.


ic3m@n

View 6 Replies View Related

Power Pivot :: Calculation Based Upon Subtotal In Group

Aug 4, 2015

PowerPivot 2013. In this example I am trying to get revenue per employee (highlighted in yellow)

In order to do that I need the numerator to appear in every row (red arrow in subtest)

I thought this would do the trick, but ...

=CALCULATE(sum('JOB COST DETAILS'[Job Line Income Amount]),ALLEXCEPT(Employee,Employee[Employee Name]))

View 7 Replies View Related

Reporting Services :: Subtotal By Group That Is Not Distinct In Data

May 14, 2015

Table here shows partial data extract in order I want to see the report. I need to report:

part-a   full,  8 layer, 7 layer, 6 layer....
Part-b   full, 8 layer, 7 layer, 6 layer....
part-a   full, 8 layer, 7 layer, 6 layer....

Part_Number 
 Pal_num
       MFGDate
Shifttime
WorkCen

[Code] ....

if I group by part number, I get a total.  I need a 'sub-total' by part as each group is produced.

Report should be:

Part                         FULL      8layer   7layer  6layer    5layer 
4layer.....
604-04043               6                                                            1
604-02057               14
604-04043                7

View 3 Replies View Related

OLAP Cube In OWC. Rows Hiding Problem.

May 24, 2007

Hi.The situation is:I'm showing OLAP Cube in OWC11. One of Parent-Child dimmensions (for example employees(menager->menager2->employee)) is viewed as Rows. Employees have attribute "City" (where they live). City can be selected from other dimmension. There are so meny of Employees that I have to hide empty rows. The problem is: When I select City in my filter dimmension, Parent members of Employees dimmension from other Cities are hiden even if their leaf members are from selected City. What Can I do to wiew all members of Employee dimmension who are friom selected City no mater in which level they are?? Any Ideas? I'm using Analysis Servicess 2000 SP3. Office web components are docked in web application so I can't programm it. MS Analysis Menager is handling this without problem (showing leaf members next to its parent even if parent is blank). This is realy urgent! Please help! BTW Sorry for my English ;)

View 2 Replies View Related

Matrix Column Problem - Need Multiple Subtotal Like Rows

Jan 18, 2008

I've got what I thought was a fairly simple matrix request, but just can't seem to do it in reporting services.

In the rows, things are grouped by campaign. In the columns, things are grouped by district. Something like this:



Area1 Area2 Area3 Total
CampaignType1 1 2 3 6
CampaignType2 2 4 1 7
CampaignType3 3 3 1 7
Total 6 9 5


Campaign and Area are all selectable by parameters, so the actual number of rows is dynamic.
What I would like to do is an additional aggregates other then sum for the total - things like average, percent to goal, etc.





Area1 Area2 Area3 Average % attained Total
CampaignType1 1 2 3 2 60% 6
CampaignType2 2 4 1 2.33 70% 7
CampaignType3 3 3 1 2.33 70% 7
Total 6 9 5



And this is where, either I'm completely missing something, or SSRS and I have a huge communication breakdown . I absolutely cannot seem to do this. I tried adding additional columns, but they are grouped under area, not after it - in the above example, it would result in three new columns, one for each area type, not 1. Confusing to describe, but looks something like:




Area1 Area2 Area3 Total
Sum Avg Sum Avg Sum Avg CampaignType1 1 xx
CampaignType2 2 yy
CampaignType3 3 zz
Total


I hope that conveys the idea w/o having to fill it all in.

I'm lost as to how to get this accomplished. All I can think of is adding a union dummy row into the actual stored proc to make a different area type (say, AreaAverage) just to add in an additional column and that make sure it sorts at the end. That screams hack to me. Any help????

View 2 Replies View Related

How?: Group By Date And Count Rows In Group

Jan 29, 2007

I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.

this is the best I can come up with:

Code:


SELECT
DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date



The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.

What am I doing wrong? Thanks!

View 9 Replies View Related

Show Child Subtotal In Parent's Subtotal Row

Apr 1, 2008



My report has two groups, company and error type for each company.
Company1

Functional Error
Data Error
Other Error
My goal is to show the subtotals for each error types when I show Company's subtotal/total; in the group 1 footer area.
Total for Functional Error:
Total for Data Error:
Total for Company1:

Please help me in figuring out how to do that.

Thank you in advance for your help.

View 3 Replies View Related

Get One Row From Each Group Of Rows

Jul 22, 2007

Hi,I'm trying to build a query that get only one row from a group ofrows, but I need the values from that row and not the results of onefunction group.I need one row for each idRef, with column2=2 and the bigger column1id |idRef | column1 | column21 1 0 12 1 1 23 1 2 14 2 0 15 2 1 26 2 2 17 2 3 2For these, I will take the rows with id=2 and id=7.Thank you, and sory for my english.

View 2 Replies View Related

TOP X Group Rows

May 31, 2007

Ok, I have searched the forums and the web, and I dont think this is available, but I am going to ask for any ideas...



I need to show a parameterized TOP x report with collapsible detail. For example:



+ CA

+ NY

+ GA

+ CO

+ PA

+ All Others



Again, the user is able to select how many group rows are shown.



Any suggestions or ideas?



Thanks!!



BobP

View 1 Replies View Related

How Do I Use Group By To Get Top Rows?

Sep 21, 2007

Okay, I am sure this is an easy question, but for some reason I cannot wrap my brain around it. I have a table that has data similar to the following:





IPAddress
VisitDate
URLVisited

192.154.21.554
9/18/2007
http://www.microsoft.com

192.154.21.554
9/19/2007
http://www.google.com

164.21.124.23
9/19/2007
http://www.microsoft.com

192.154.21.554
9/20/2007
http://www.yahoo.com


What I am trying to do is use a select query to find the most recent visit per IP address (but I also want the other row data like URL visited). I have something like this that is finding the most recent visit per IP address:

SELECT IPAddress, MAX(VisitDate) AS MaxDateVisited
FROM VisitorTable
GROUP BY IPAddress

How do I also pull in the URL that is associated with the MAX date visited? Thank you!

View 4 Replies View Related

Keep Group Rows Together

Dec 21, 2006

Hi,

Is it possible to keep a group in a table report on the one page if this group could be fitted into the rest of the page and start new page otherwise?

Thanks,
Igor

View 4 Replies View Related

Trying To Get The First Count Of A Group Of Rows

Dec 24, 2003

Hi, I am trying to get the first row of what might be a group of any size of rows within an id that may contain a specific field. For eg

Row A = NoteID 1, FK_JobID 1, UnRead

Row B = NoteID 2, FK_JobID 1, UnRead

Row C = NoteID 3, FK_JobID 1, UnRead

I need the sql to return to just one Job (row) even though the job as 3 UnRead fields. But its returning 3 because its only doing what I'm asking. What I need it to do is just get the one Job (row) where any of the notes = UnRead.

I tried using Top 1, but that will only ever return one row and since I need it to return more than one job (row) it won't work.

Heres my attempt

DECLARE @UserID INT

SET @UserID = 4


SELECT User_Notes.BeenRead, Master_Jobs.By_Who, Master_Jobs.Next_Action, Master_Jobs.Due_Time, Master_Jobs.Due_Date, Master_Jobs.Contact,
Master_Jobs.Job_Title, Master_Jobs.JobID
FROM User_Notes INNER JOIN
Note ON User_Notes.FK_UN_NoteID = Note.NoteID INNER JOIN
Master_Jobs ON Note.FK_JobID = Master_Jobs.JobID

WHERE Note.FK_UserID = User_Notes.FK_UN_UserID AND
BeenRead = 'UnRead'

Thanks in advance

View 6 Replies View Related

Counting Rows In A Group By...

Jul 26, 2000

Hi,

I'm using SQL Server 7.0. My problem is that I'd like to count how many rows are in a group by. For example, here is my data:

Agent Branch
1 1
2 1
1 1
2 1
1 2
1 2

If I do this:

Select Agent, Branch
From Table1
Group By Agent, Branch

I get:

Agent Branch
1 1
2 1
1 2

What I need to do is count the records (3) in the group by. I've tried a few things but I can't seem to come up with the number of rows in a group by.

Can anyone help me?

Thanks in advance,
Darrin

View 2 Replies View Related

Group Multiple Rows Into One

Jun 20, 2012

creating a query to group identical rows into one and placing corresponding data in appropriate columns for a table named items, what I mean is that I have a table structured as below

Itemcode, Description, Period1, Period2, Period3
001--------Desc--------233,---------,------- <<ROW1
001--------Desc ------------ ,100,------------ <<ROW2
001--------Desc ------,--------,-------,300 <<ROW3

Row one contains a value in Period1
Row two contains a value in Period 2
Row three contains a value in Period 3

I need to create a query so that the end result would look like this;

Itemcode, Description, Period1, Period2, Period3
001 --------Desc -------233 ----100 ---300

creating an sql query to get the above result?

View 2 Replies View Related

Comparing Rows In The Same Group

Feb 26, 2013

Code:

Room ID Hours StartTime EndTime
GR41908101700
GR42409101300
GR43317102000
LR111108100900
LR112109101000
LR113210101200
LR114112101300
LR115313101600
LR116216101800
LR117118101900
LR118119102000
LR119320102300

As you can see each room is assigned and id and once the room changes the id starts from 1 again. I don't want the 2nd row to appear because 9am - 1pm is in 8am - 5pm. How do I remove this row?

View 3 Replies View Related

How To Group Certain Rows And Get The Data.

Jul 2, 2007

Hi Folks,

I am stuck in forming a query.

My age wise employee count sample data (department wise) is as shown below.

Sample Data
-----------
Department [>55] [50-55] [<50] [<40] [<30]
---------- ----- ------- ----- ----- -----
Marketing 0 1 5 10 20
Op's Support 0 3 6 5 25
Op's Tech 0 0 0 3 10
Product Tech 0 0 2 4 12
Product Support 0 0 1 3 7

I would require the data (sum of employee count age wise) to be categorized at a boarder level. Each category comprising of one or more departments.

Operations [Op's Support + Op's Tech], Product [Product Tech + Product Support], Others [Marketing]
The expected result would be.

Category [>55] [50-55] [<50] [<40] [<30] [Total]
--------- ----- ------- ----- ----- ----- -------
Operations 0 3 6 8 35 52
Product 0 0 3 7 19 29
Others 0 1 5 10 20 36

Thanking you in anticipation.

Jabez.

View 4 Replies View Related

UPDATE TOP Row For A Group Of Rows?

Jan 6, 2008

My first post on the forum, I wish I had found this place sooner, looks to be full of good advice and knowledgeable posters.

I have tried searching and looking at the FAQ's but couldn't find an answer. So here goes, all help greatly appreciated.

SQL Server 2005

Table (many columns left out for simplicity)

ID - unique.
HdrID - key to the header record.
PTtimestamp - date, date/time the row was written to DB.
PType - integer, representing various states of the row.
etc
etc
etc (another 15 columns of data)....

Typically there is approximately 250 records per HdrID.

I am trying to do an SQL UPDATE without using my usual solution of writing vb code!

I want to update the PType of latest row (as per timestamp) for each HdrID to -9999.

ie;

ID HdrID PTimestamp
001 001 01/01/2008 09:00:00
002 001 01/01/2008 09:10:00
003 001 01/01/2008 09:20:00
004 002 01/01/2008 09:00:00
005 002 01/01/2008 09:10:00
006 002 01/01/2008 09:20:00

In the above example I want to update the PType field = -9999 for record ID=003 & ID=006. In reality the table has tens of thousands of rows.

Jake

View 6 Replies View Related

Count Rows In Group

May 1, 2007

Hello.

I built a report with one field as a group.
I want to count the number of rows in each field so I can add it to the group field or somw where in the report.

How can I count how many rows do I have in each group?

Thanks.

View 1 Replies View Related

Group By - Count Returns No Rows

Jun 12, 2007

Hi everyone:
I guess this should be a simple question but have not been able to find the answer, does anyone know how to make a SQL Sentence to return at least one row when counting?
SELECT COUNT(Id_Field), Field2  FROM Table1 WHERE Code_Field = 1 GROUP BY Field2
This will return 0 rows when the where criteria is not matched by any record on the Table1, but I would like to have one row counting 0 rows, in stead it returns no rows at all.
Thanks for any help.

View 4 Replies View Related

Counting Total Rows When Using GROUP BY

Oct 20, 2007

hi,
i have a stored procedure SELECT UserName AS Visitor, COUNT(VisitID) AS TotalVisit
FROM UserVisits
WHERE (ProductID = @ProductID) AND (AnonimIP IS NULL)
GROUP BY UserName
UNION
SELECT AnonimIP AS Visitor, COUNT(VisitID) AS TotalVisit
FROM UserVisits AS UserVisits_1
WHERE (ProductID = @ProductID) AND (UserName IS NULL)
GROUP BY AnonimIP
this will return something like:
zuperboy90 - 4 visits
ANONIMOUS - 6 visits
85.104.103 - 2 visits etc
how can i count the rows returned in both selections (4+6+2 = 12) ?
thank you

View 9 Replies View Related

MS SQL Query : Group By Returing Too Many Rows.

May 30, 2004

Hi,

I have posted this in the Experts Forum so I have put replies to questions in here as well (Hence the long post!!). Hope someone here can help!!

I am having trouble with a T-SQL query. I have three tables in a join and I need to limit the results returned by query using a group by.

All of the fields in the result group are the same except for two fields, a date field and a varchar field. What I want to achieve is to return the row that has the latest date, and I need the varchar field as that is the information I am after. The problem is if I include the varchar field in the group by, it is returned as a separate group, if I do not included it in the group by, Server: Msg 8120 (aggregate fn / group by) error occurs!

Any ideas how to get around this?

Sample code and results below:

Query:

select distinct s.id, [active-from], code, s.desc, [scheme], [market-id], [market-id]+'CODE'
from [coded] c join sec s on s.id = c.id
join [mkt-security] m on m.id = s.id
where ([market-id] = [scheme] or [market-id]+'CODE' = [scheme])
Group by s.id, [active-from], code, s.desc, [scheme], [market-id], [market-id]+'CODE'


Sample results:

id : [active-from] : code : desc : [scheme] : [market-id]
--------------------------------------------------------------------------------------------------------------------------
10449 :1993-07-21 : ADV : PXX Group Limited : ABCD : ABCD : ABCDCODE
10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10469 :1986-10-24 : AQL : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
10469 :2000-10-02 : AQL : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
10469 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE

The result I want to achieve is to only return these two rows:

10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10469 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE: DEFG : DEFG CODE

i.e. those with the latest [active-from] date, but I must have the corresponding code value.

I have tried a correlated sub query on the [active-from] field, but it is possible to have different id’s with the same [active-from] date so it did not work.

The primary key for the [coded] table is the combination of [active-from], code and [scheme]. The tables are truncated and then imported back into the SQL database from a Progress database daily, so restructuring the table(s) is not a possibility.


I have rewritten the query to "AND [ACTIVE_FROM] in (SELECT MAX([ACTIVE_FROM]) FROM CODED AND MAX([CODE]) = S.[CODE] AND MAX([SCHEME]) = S.[SCHEME]", (Which I think is the T-SQL for you query), but it returned the error "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."

Also I think the query need to use a Group by as the results I need returned are those with the latest [ACTIVE_FROM] date WITHIN the group of [ID]and [MARKET-ID]. I can't think of any conditions where I can filter the results in the where clause.

The [ ] in the T-SQL is to identify table names as some of the table names (from the Progress db) use characters (the "-") that are unsupported for table names in T-SQL.

I am using the group by as I want to return specific groups of results ( [ID] and [MARKET-ID] ), that have the latest active-from date. I have made a change to that posted before to illustrate:

All results:

id : [active-from] : code : desc : [scheme] : [market-id] : [market-id]+’CODE’

10449 :1993-07-21 : ADV : PXX Group Limited : ABCD : ABCD : ABCDCODE
10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10769 :1986-10-24 : AQL : CO Ordinary Shares : WXYZ CODE : WXYZ : WXYZ CODE
10769 :2000-10-02 : AQL : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
10769 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE

The result I want to achieve is to only return these three rows:

10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10769 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE: DEFG : DEFG CODE
10769 :1986-10-24 : AQL : CO Ordinary Shares : WXYZ CODE : WXYZ : WXYZ CODE


The reason the last row needs to be returned is that it has a different market id to the other row, despite its active-from date being earlier.

I have tried the self join earlier, but it would then only return 1 of the rows that have an id of 10769!!

Hope this makes sense!!

Cheers,
Guytz

View 2 Replies View Related

Table Group By Rows Then Columns

Oct 31, 2007

I have a table, Table1 with 3 columns as follows: colItemKey, colGrouping1, colGrouping2.
colItemKey is the primary key. Say colGrouping1 has 4 different types: Grp1A, Grp1B, Grp1C and Grp1D and colGrouping2 has 5 as follows: Grp2A, Grp2B, Grp2C, Grp2D and Grp2E. How do I setup my select so that the result set is as follows:

Grp2A Grp2B Grp2C Grp2D Grp2E

Grp1A nnn nnn nnn nnn nnn
Grp1B nnn nnn nnn nnn nnn
Grp1C nnn nnn nnn nnn nnn
Grp1D nnn nnn nnn nnn nnn

View 2 Replies View Related

How To Select Last Rows In Group By Clause

Nov 7, 2008

I have a table which stores datewise Transactions of different items. Fields and sample data is

RecID, ItemID, Date, Received, Issued, Stock
1, 5, 11-03-08, 10, 10
2, 5, 11-05-08, 3, 7*
3, 8, 11-15-08, 25, 25
4, 8, 11-16-08, 8, 33
5, 8, 11-18-08, 6, 27*

Now i want to select last row for each item (indicated by *). Is it possible in one single statement.

View 6 Replies View Related







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