Is it possible to have multiple tables or matrixes under one header grouping. I'm having a case where two tables need to be under one grouping (like "Sports vehicle) and under that "sports vehicle" I have two very different tables and on it goes for each grouping (next one like "Off road vehicle), etc.
Is there anyway to do this. I can make this work with one table using the table grouping.
Any suggestions or ideas?
Thanks.
All,
Is it possible to Group data in a Matrix exactly similar to Table Control. For example, my table control would group data as follows:
Region Country City $ales
North America
USA
Chicago 4 MM
LA 10 MM
NYK 6 MM
Canada
Toronto 4 MM
while the matrix would display as:
North America USA Chicago 4MM
LA
NYK
Canada Toronto ...
Do you see the problem? The matrix is starting the subgroup at the same level as the parent group. How do I make a sub group start at the row below the parent group row in matrix just like in the table above?
Hi All,
I'm using a matrix in SSRS.
In the matrix i'm showing the client Name and count of items purchase.
I want to group those client Name having count less then 5 in a saparate row name as Others and there sum of count
How to do this. Please Suggest.
Regards
Ritesh kumar
My fiscal year starts from April. How can I group with fiscal year like this?
Select items,sum(sales), date from tableA
2007
2006
4
5
6
7
8
9
10
11
12
1
2
3
4
5
6
7
8
9
10
11
12
1
2
3
1
Books
10
20
0
0
0
0
0
0
0
20
50
0
25
10
10
0
0
5
0
25
15
10
10
20
2
Panel
10
10
10
20
20
10
10
20
10
10
10
10
20
20
20
20
30
30
10
10
10
30
30
30
3
Frame
I have a report with the Month attribute as the column group and specific measures as the row groupings. Now, here's my delima. The months are not being displayed in order. They look like this:
Jan Feb May Jun Jul Aug Sep Oct Nov Dec Mar Total
Why is it doing this?
Here's a view of my matrix in layout view...
Month_Name
Fatal Crashes =sum(fatal_crashes.value)
Injury Crashes =sum(injury_crashes.value)
Property Damage =sum(Prop_Damage.value)
Total Crashes =sum(Total_crashes.value)
Chicago Crashes =sum(Chicago_crashes.value)
Crashes Located =sum(located_crashes.value)
% Located =sum(percent_located.value)
any help would be greatly appreciated!! THANKS!
Hi,
I have a requirement from my users to be able to drill down for a single column in a matrix. I've been able to implement drilldown for all of the measures (all columns grouped at the same time) in my matrix but they now want to be able to group different columns independently of each other. In other words they want to be able to group the data in different columns by different things.
An example might make my question clearer, the report would need to look like this:
Client Name
Sales -
Costs +
Revenue -
Client A +
Europe +
Middle East +
Asia Pacific +
$12,000,000
Products +
Investments +
$12,000,000
$8,000,000
$4,000,000
$10,000,000
$22,000,000
Client B +
$77,000,000
$16,000,000
$9,000,000
$22,000,000
$8,000,000
$32,000,000
As you an see, the Sales measure has been expanded so it's grouped by region so the sales figures can be seen for all clients and the 3 regions. The revenue column has also been expanded/grouped so that you can see revenue figures for products and investments for all clients. Costs is not expanded but it could be by clicking on the '+' which would group the data in that column by something else. They have asked for more than 1 level of drilldown, so clicking on "Europe" above would allow another level for the sales measure which might be country. There will also be regular measure columns that are not drilldown/groupable columns.
Is it possible to implement this with SSRS 2005?
Thanks,
Lachlan
I have matrix with 3 row groupings
Parent, Child, Baby
I have a value in the data cell and also a dummy column grouping to show some % values.
The % is basically what the row subtotal represents as a % of all the data in the dataset.
The Parent row group also has a subtotal, so the % here will show all values within the parent as a % of all data.
I have interactive sorting on Parent, Child and Baby.
but what I want to do is this:
Interactive sorting of the parent-subtotal-percent column values to cause a physical reordering of the parents.
i.e. show me the parents in order of their "% as a total of the entire dataset"
Possible?
I have multiple different data regions on one report body. I need to be able to arrange the a few of the data regions so that they are grouped together and print together on same page. I've tried using a list around the data regions that I want to group together, but the list data region needs to have a data set specified and it only allows one to be specified.
Has anyone tackled this before?
I have several tables that I need to summarize data from two tables based upon a dates passed in and group that data. I have attached my table layout, some sample data, and how I would like the results to look.
View 2 Replies View RelatedConsider the following dataset:
ID|MD|TYPE
1|JOHN|A
2|JOHN|B
3|JOHN|B
4|BOB|A
5|BOB|A
6|BOB|B
7|BOB|B
8|BOB|B
I need to count the number of IDs for each MD and each TYPE like this:
MD|A|B
JOHN|1|2
BOB|2|3
I only know how to count everything by MD like this:
SELECT MD, COUNT(ID) AS TOTAL
FROM MY_TABLE
GROUP BY MD
ORDER BY MD
The query above results in:
MD|TOTAL
JOHN|3
BOB|5
I have a report with two datasets, DS1 and DS2, which contain the same data fields, but with different values. Like so:
DS1 = sales
salesperson sale_number amount
John Smith 1 $100
John Smith 2 $105
Mary Jane 3 $98
John Smith 4 $275
Mary Jane 5 $92
DS2 = sales with price overrides
salesperson sale_number amount
John Smith 1 $100
Mary Jane 3 $98
Mary Jane 5 $92
Now what I want to do is see how the salespeople are doing. I can use either dataset and get great results independently:
Sales Results:
Salesperson Number of Sales Total Amount
John Smith 3 $480
Mary Jane 2 $190
or
Sales results with price overrides:
Salesperson NumSales with Over Total Amount
John Smith 1 $480
Mary Jane 2 $190
Now what I really want to do is a combo table like so:
Salesperson NumSales with Over Number of Sales %Overrides
John Smith 1 3 33.3%
Mary Jane 2 2 100%
I can not figure out how to do this. If I create a table that has DS1 as its datasource, I need to access DS2 for a count. So I try this for the NumSales with Over:
= count((Fields!sale_number.Value,"DS2"))
This just repeates the total number of sales in DS2, which is 3, for each line; not separating them out by salesperson.
If I try something fancier such as:
=count((Fields!sale_number.Value,"DS2", (Fields!Salesperson.Value,"DS2") like =(Fields!Salesperson.Value))
The report won't even run.
I want to do something along those lines. Does anyone have any ideas how to do this? I've considered subqueries to use salesperson as a filter, but the datasets are so large that the reports end up taking forever to run. I've tried using iif, but it doesn't seem to like using a field from a second dataset. I even tried to use the embedded VB code box to write a function, but then I couldn't pass the full array from the secondary dataset to the function (I could pass it from DS1, but not DS2).
I know this is incredibly simple, but this noob can't figure it out. If anyone has any suggestions I would deeply appreciate it.
Thank you,
cmk8895
Hi, I am trying to write a query that gets the percentage of students in specific racial groups in specific schools. Some ethnicity values of students are null so I have to use left joins. My query is below, when I run it I get the error "join expression not supported", I've tried a couple different ways of doing it but I always get that error or "syntax error in from clause". Can anybody help me with formatting multiple and nested left joins in general?
Thanks in advance.
drop table percentMinorities;
create view percentMinorities as
select s1.schoolid,
round(count(s2.studentid)/count(s1.studentid),2) as percentWhite,
round(count(s3.studentid)/count(s1.studentid),2) as percentBlack,
round(count(s4.studentid)/count(s1.studentid),2) as percentHispanic,
round(count(s5.studentid)/count(s1.studentid),2) as percentAsian
from
students as s1 left join
(students as s2 left join
(students as s3 left join
(students as s4 left join students as s5
on s4.studentid is not null and s5.ethnicity = 'A')
on s3.studentid is not null and s4.ethnicity = 'H')
on s2.studentid is not null and s3.ethnicity = 'N')
on s1.studentid is not null and s1.ethnicity = 'O'
group by s1.schoolid
I have a table of Projects which have multiple Resources.
PROJ_ID, PROJ_NAME,RESOURCE1,RESOURCE2,RESOURCE3
01 Project1 001 005 088
02 Project2 002 004 005
How can I pull out a list of resources with the projects associated with them?
i.e. the above would return
001 01
002 02
004 02
005 01
005 02
008 01
or
001 01
002 02
004 02
005 01,02
008 01
Hi All,
Thanks for dropping by my post.
I have a table which is of this form.
ID
MS030_A
MS030_F
MS036_A
MS036_F
MS040_A
MS040_F
ZZ0023
2/16/06
2/16/06
8/10/07
8/10/07
11/21/05
11/21/05
ZZ0031
8/10/07
4/5/07
8/9/07
8/9/07
3/22/07
3/22/07
ZZ0077
8/9/07
9/7/07
8/10/07
8/10/07
8/10/07
9/7/07
ZZ0078
8/10/07
9/7/07
8/9/07
8/9/07
8/9/07
9/7/07
ZZ0079
8/9/07
8/10/07
10/26/05
10/26/05
8/10/07
8/10/07
ZZ1030
3/31/05
8/10/07
9/1/05
9/1/05
8/9/07
ZZ1033
3/24/06
8/9/07
8/9/07
8/9/07
3/31/05
ZZ1034
8/10/07
8/10/07
8/9/07
8/9/07
3/24/06
ZZ1037
8/9/07
8/9/07
9/24/07
9/24/07
ZZ1040
10/26/05
10/26/05
9/24/07
9/24/07
ZZ1041
9/1/05
9/1/05
9/24/07
9/24/07
ZZ1042
8/9/07
8/9/07
9/24/07
9/24/07
11/21/05
The goal is to group all this transactions by Month and Year.
Something like this....
MS030_A
MS030_F
MS036_A
MS036_F
MS040_A
MS040_F
Month
Year
3
2
2
2
2
2
1
2006
4
4
7
9
8
9
2
2006
10
10
6
8
8
3
2006
4
4
5
5
3
2
4
2006
5
6
8
3
7
1
5
2006
For just one date column it is pretty straight forward i.e., just do a select count and group by DATEPART ( Mm, DateField)
but for multiple columns i am in a total fix....
can please someone help me out...
appreciate your help
Hi i have always used views in my code to group common functionality in my sql expressions and then i can simply call these views in my data access layer by saing:
SqlCommand cmd = new SqlCommand("SELECT * FROM vw_Documents WHERE CategoryID = @CategoryID", cn);
However my view has become so complicated that i had to convert it to a stored procedure called sp_Documents. The problem now though is that is that i wish to do queries against the data returned but i can't simply say:
SqlCommand cmd = new SqlCommand("SELECT * FROM sp_Documents WHERE CategoryID = @CategoryID", cn);
The only way i can see to do it is to create a stored procedure for every single senario i have passing in the appropriate values as parameters. This seems a pretty messy solution to me because i would have repeated logic in all my stored procedures. Therefore i was wondering if there's a simpler way for me to do this or am i just being lazy :).
Appreciate if someone could help,
I would like to set up a report which would look simular to
Mon Tues Wed Thurs Fri Total Month to date total
salesguy1 10 5 11 10 9 45 120
SalesGuy2 9 1 15 0 0 25 89
I have created a matrix which shows the data upto total.
Is there a way to include the Month to date total after total?
Thanks
Hi All,
I have a project that deals with matrix or crosstab. I created an SP that looks like this in the RDL.
Month | Quarter
Channel | Revenue | Target_Revenue | Previous_Revenue | Revenue | Target_Revenue | Previous_Revenue
Channel2 | 100000 | 150000 | 1250000 | 100000 | 150000 | 12500000
Is there any way to get multiple subtotals in a matrix? For example, one that does a count and the 2nd that does an averages as per the desired result below ...
Code Block
A B C
A 1 2 3
B 2 3 4
C 3 4 5
Total 6 9 12
Avg 2 3 4
Hello and thank you for the help in advance.
I know this has to be possible maybe I am just missing somthing.
I am creating a matrix report which will compare year by year quotes to orders The issue is quotes and orders each have their own dataset. I will be pivoting on JobType which is in both datasets and spelled the same. Is there a way to do this or will I have to figure out how to union the tables? If not possible why does it allow you to name the dataset in the expression?
Thanks, Leo
We have a report that was created in SSRS 2008 R2 that has 3 tables with different datasets that share a common ID that I want to use to group them.
If we run the report passing only a single value for the grouped parameter then the report works perfectly. What we need is for this report to allow multiple values to be selected for this parameter and for the report to run as if the user had selected each value one at a time and run the report with page breaks in between. Currently, when we pass multiple selected values for the grouping parameter the report displays all values for table 1, then all values for table 2, then all values for table three as below:
Table 1:
Detail rows for Group Param Value 1 ...
Detail rows for Group Param Value 2 ...
Detail rows for Group Param Value 3 ...
....
Table 3:
Detail rows for Group Param Value 1 ...
Detail rows for Group Param Value 2 ...
Detail rows for Group Param Value 3 ...
But we want it to render like this:
Table 1:
Detail rows for Group Param Value 1...
Table 2:
Detail rows for Group Param Value 1...
Table 3:
Detail rows for Group Param Value 1...
[code]....
The page breaks are needed so that when the report is exported to excel each individual report (by group param) will be on its own uniquely named tab.The report must export cleanly to excel and currently does for the single value passed.
I have a report where in I want to show each record on a separate page.
So, to achieve that I took a single cell from table control, expanded it and used all the controls in that single cell. This looks nice so far.
Now, I also have to show a sub grid on each record. So I took a table control and added on the same single cell and tried to add a parent group to the table row.
When I preview, it throws this error.
"The tablix has a detail member with inner members. Detail members can only contain static inner members."
What am I doing wrong? How can I achieve table grouping inside a table cell?
I have one matrix with multiple row & column groups which is not looking nice.
See the below image. The reason for having a Matrix is they can create any no of Tests.
In the above image i have two row groups Test Type and Test Date and column groups as TestDesc and my required format is .
My team is using TFS for work item management and I'd like to provide a week schedule matrix that displays:
- weekdays on the x-axis
- persons on the y-axis
- and the assigned workitems in the cell.
This works fine if there's only one WIT (work item) per day but some WIT's are shorter and thus the resource is assigned more than one a day. My dataset may return something like this:
Date Person WIT
28/3 Mark Task1
28/3 Mark Task2
28/3 Hank Task3
29/3 Mark Task2
29/3 Hank Task3
In this case I'd like the matrix to look something like this, i.e. the two tasks Task1 and Task2 in the same cell:
Mark Hank
28/3 Task1 Task3
Task2
29/3 Task2 Task3
I've set up the matrix in the report but I only get the first item per day to show.
Mark Hank
28/3 Task1 Task3
29/3 Task2 Task3
The default definition in the cell was "=First(Field!TaskName.Value)", so I figured I'd solved the issue by changing it to "=Field!TaskName.Value", but it didn't.
...any idéas??
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????
I'd like to create a report with the folloiwng format:
DATE1 DATE2 DATE3 DATE4 DATE5 [fixed 5 dates across the top, from today to T+5]
THING1 x x x x
THING2 x x x x
THING3 x x x x
THING4 x x x x
my raw data looks like this:
THING1, DATE1, TEXT VALUE 1
THING1, DATE2, TEXT VALUE 2
&c&c.
Now: there may be 0, 1 or several (by which I mean 2-5 max) text values to display at each intersection. If there are zero I'd like it to be blank, if there are one or several, i'd like to display them in a little list within the cell.
Is this possible?
Your help on this could make my day a better one ....
I was trying to create groups on a table report item in SRS 2005 report. The criterion I'm using in one group is exact opposite of the criterion I have in the next group. My dataset has records which fulfill both criteria but my table displays only records for the first group.
Is there a limit on the number of groups we can use in SRS reports and/or on building criteria for filters?
Thanks,
Samtadsh
I€™m trying to group records that are in two collections and assign a Master Id. For example:
Example Base Table:
Collection Number Customer Id
--------------------------- -----------------------
3000001 244517
3000001 244518
3000002 244517
3000002 255519
3000002 244518
3000003 255520
3000004 266660
€¦
€¦
Since Customer Id 244517 is in collection 3000001 and 3000002 I want to group all customers in both collection and assign one master id. For example:
Example Results:
Collection Number Customer Id Master Id
--------------------------- ----------------------- --------------------------
3000001 244517 1
3000001 244518 1
3000002 244517 1
3000002 255519 1
3000002 244518 1
3000003 255520 2
3000004 266660 3
€¦
€¦
I€™m not sure how to perform this, can anyone direct me?
Hello all,
I have a problem in a report that must list agents with some extra info.
First of all, my query that i use, returns the following data:
AgentId, AgentAccount, AgentFN, AgentLN, AgentInfo, AgentGroup, TeamId, TeamDescription, SkillsId, SkillDescription, LanguageId and LanguageDescription.
My report should look like this in fully extended (drilled down)
--------------------------------------------------------------------------------------------------------------------
Account Lastname Firstname Info #Teams #Skills #Languages
--------------------------------------------------------------------------------------------------------------------
Uk
+ 101 xyz abc xxx 3 2 4
+ Team info
Team 1
Team 2
Team 3
+ Skill info
Skill 1
Skill 2
+ Language info
EN
FR
ES
DU
+ 102 xyy acd xxx 1 3 2+ Team info
Team 1
+ Skill info
Skill 1
Skill 2
Skill 3
+ Language info
EN
ES
Initial is should look like this:
--------------------------------------------------------------------------------------------------------------------
Account Lastname Firstname Info #Teams #Skills #Languages
--------------------------------------------------------------------------------------------------------------------
Uk
+ 101 xyz abc xxx 3 2 4
+ 102 xyy acd xxx 1 3 2
BXL
+ 105 ddd abc xxx 4 8 3
+ 106 rrr acd xxx 3 4 2
When i then would click on the agent detail it should look like this:
--------------------------------------------------------------------------------------------------------------------
Account Lastname Firstname Info #Teams #Skills #Languages
--------------------------------------------------------------------------------------------------------------------
Uk
+ 101 xyz abc xxx 3 2 4
+ Team info
+ Skill info
+ Language info
+ 102 xyy acd xxx 1 3 2
BXL
+ 105 ddd abc xxx 4 8 3
+ 106 rrr acd xxx 3 4 2
+ Team info
+ Skill info
+ Language info
This far i was only able to display the report with one subitem per agent.
So what i want to display is when you click on the agent the 3 subitem titles apear (team info, skill info, language info) but without the details. When you then click on one of the 3 sub items, the details should apear.
Could anyone help me out a bit ??
Greetings
V
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions
The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc
Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
SQL Server 2008.
From individual event logs I have generated a table where arrivals and departures at a location are registered per device. As there are multiple registration points, there might be multiple consecutive registrations per location.
If this is the case I need to filter those out and have one registration per location and in the result I need to get the earliest arrival and the latest departure of these consecutive rows.
Part of the table:
logIDdeviceIDArrivedDepartedLocationIDGrp1Grp2Grp
3485441082013-02-07 17:51:05.0002013-02-07 17:51:15.0005110
3492041082013-02-07 17:51:15.0002013-02-07 17:51:26.0005220
3500241082013-02-07 17:51:27.0002013-02-07 17:51:37.0003312
3508941082013-02-07 17:51:41.0002013-02-07 17:51:54.0004413
[Code] ....
So as long the field LocationID is the same in the next row, it needs to be grouped.
I have added the rows Grp1, Grp2, Grp in an attempt to get an unique grouping number with the following script in the select statement:
,ROW_NUMBER() OVER(PARTITION BY DeviceID
ORDER BY logID) AS Grp1
,ROW_NUMBER() OVER(PARTITION BY DeviceID, LocationID
ORDER BY logID) AS Grp2
,ROW_NUMBER() OVER(PARTITION BY DeviceID
ORDER BY logID)
-
ROW_NUMBER() OVER(PARTITION BY DeviceID, LocationID
ORDER BY logID) AS Grp
By subtracting Grp2 from Grp1 (Grp = Grp1 - Grp2) I hoped to get an unique group number for each set of equal consecutive locations, however the Grp2 column does not restart from 1 each time the LocationID changes: Grp2 in line 7 should have been 1 again, but it is 2 because this is the second row with LocationID = 3 in the list.
I have a table that will always return 6 records. I would like to group the table, such that the first 3 records, are GROUPED Together first, do the sub-total on these 3 records first. Then the last 3 records are grouped together. and then the sub-total on the last 3 records. Is there a way I can do that in SSRS 2005?
View 3 Replies View RelatedI have constructed my main report using the table item from the toolbox. In maintable I have two groupings.
In the maintable I have grouping2 set for a page break after each grouping.
I have set the subreport parameter to the field value of the maintable grouping2.
I want to display the contents of the subreport after grouping1 of the maintable on the same page.
I have achieved a result by placing the subreport in maintable_group2 footer. However when I save it as an excel sheet it says "Subreports within table/matrix cells are ignored".
I really don't like how the report displays like this in the report server however I will need to export this report to excel.
Have I explained my issue well enough?
I have a table with couple of hundred thousand records, i have added a new column to the table named MD_Group.
Now i need this column populated, so that every 10,000 row set in that table gets a numeric (incremental number) assigned starting from 1.
So the first 10,000 rows will have MD_Group = 1, next 10,000 rows will have MD_Group = 2 , next 10K MD_Group = 3 etc.
For testing purpose here is a results table with total of 11 rows where we want data grouped every 3 rows.
MD_Group
CustomerNumber
AmountBilled
1
12
15243
1
1231234
15243
[code]....
-- Create Sample Table
Declare @GroupRelation_Test TABLE (
MD_Group [varchar](20),
CustomerNumber [varchar](20) ,
AmountBilled [varchar](20) ,
[code]....
-- Test data
INSERT INTO @GroupRelation_Test
( CustomerNumber, AmountBilled,MinAmounttBilled)
SELECT'12','15243','' UNION ALL
SELECT'1231234','15243','' UNION ALL
SELECT'463','15243','' UNION ALL
SELECT'442','15243','' UNION ALL
[code]....