Summary Query

Jan 18, 2007

I have a table which contains a sports schedule:
team1_id,team2_id,result1,result2

result1 is for team1_id
result2 is for team2_id

I want to run a query to get summarize the two id fields with their assoiciated results:
For Example the dataset might look like this:
row 1 - id1=20,id2=30,result1=1,result2=3
row 2 - id1=30,id2=20,result1=2,result2=2
row 3 - id1=20,id2=40,result1=3,result2=1
row 4 - id1=40,id2=20,result1=1,result2=3

how do I build the query to merge the ids and thier respective results into one countable dataset

View 3 Replies

Summary Query In Access

Dec 9, 2004

I am trying to sum up sales for employees and get the employee with the highest sales in one query. The query I have below works but it doesnt get me the EmployeeID. Assume all the fields are in the same table. If I try to do something like this it bombs on me: "MAX(SUM(OrderAmount)"

SELECT Max(OrdersSum) AS MaxOrders FROM (SELECT SUM(OrderAmount) as OrdersSum
FROM Orders
GROUP BY EmployeeID);

Thanks in advance!

View 1 Replies View Related

Transact SQL :: Select Summary Query

Aug 2, 2015

I'm trying to make a summary daily production report on the data below:Want to summarize the data with the sum of the Correct Weight between start and end date.

eg. of summary.
Recipe Name Total Weight
Hedge Shears - Lasher/kudu
500
Grass Slasher
200
eg.

But it needs to summarize when selected between start and end date.

RecipeName
CorrectWeight
CurDateTime
Weight

[code]....

View 6 Replies View Related

Simple (for Some) 2 Table Summary Query

Jul 10, 2006

...but apparently not me, I'm very new with this T-SQL stuff and am seeking the advice of the seasoned pros at this forum.

Description on my SQL-5 Environment:

Table I Sales: Prod_ID, Prod_DT, Sales_DT, Buyer_Name, Buyer_State

Table II Repairs: Prod_ID, Prod_DT, Sales_DT, Repair_DT

These 2 tables are joined by the common key Prod_ID & also and share the product's production & sales dates. What I would like to do is produce a rate summary similar to description below.

Production_YYYY, Production_MM, Sales_Cnt, Repairs_Cnt, Repair_Rate((Repairs_CNT/Sales_CNT)*100)

Important to remember that not all products experience repairs, so the basis for Sales_CNT needs to be the Sales Table, even thou Prod_DT also appears in Repairs Table.

It's simple enough for novice like me to produce 2 tables independently and then merge back those resulting tables into the single table output described above. But my question is how do I write a single SQL "SELECT" request that will produce the results into just a single table.

Thanks in advance for your help!

View 6 Replies View Related

ID Who Didn't Visited A Summary Page Per Day (was Help With Query)

Mar 9, 2006

I have 1 table:
ID pagename datevisited
1default01/01/2006
1info01/01/2006
1default02/01/2006
1info02/01/2006
1summary02/01/2006
2default02/01/2006
2info02/01/2006
2summary02/01/2006

I need to run query for ID who didn't visited a summary page(per day)
How do I do this?

View 1 Replies View Related

SUMMARY TABLE

Apr 10, 2007

Trying to create a summary table of current product table, so that they is only 1 line for each product (even if the product is in both warehouses - in this case take table for warehouse 1) Therefore standard case statement becomes:

CASE WHEN exists (select product from stock where warehouse = '02' and product = stock.product and product in (select product from stock where warehouse = '01'))
ProductDescription = (select distinct long_description from stock where warehouse = '01' and product = stock.product)
ELSE
ProductDescription = (select distinct long_description from stock where product = stock.product and (warehouse = '01' or warehouse = '02'))
END

Is there another way of writting this, instead of repeating the above code for each column in the table?

THANKS

View 2 Replies View Related

Summary Data

Jun 18, 2008

Hi,

Once again some doubt!

I have a query as below -
Select TeamName ,
SUM(CASE WHEN Team_Date = '01/07/2007' THEN Team_Total ELSE 0 END) AS [Jul 07] ,
SUM(CASE WHEN Team_Date = '01/08/2007' THEN Team_Total ELSE 0 END) AS [Aug 07] ,
SUM(CASE WHEN Team_Date = '01/09/2007' THEN Team_Total ELSE 0 END) AS [Sep 07] ,
SUM(CASE WHEN Team_Date = '01/10/2007' THEN Team_Total ELSE 0 END) AS [Oct 07] ,
SUM(CASE WHEN Team_Date = '01/11/2007' THEN Team_Total ELSE 0 END) AS [Nov 07] ,
SUM(CASE WHEN Team_Date = '01/12/2007' THEN Team_Total ELSE 0 END) AS [Dec 07] ,
SUM(CASE WHEN Team_Date = '01/01/2008' THEN Team_Total ELSE 0 END) AS [Jan 08] ,
SUM(CASE WHEN Team_Date = '01/02/2008' THEN Team_Total ELSE 0 END) AS [Feb 08] ,
SUM(CASE WHEN Team_Date = '01/03/2008' THEN Team_Total ELSE 0 END) AS [Mar 08] ,
SUM(CASE WHEN Team_Date = '01/04/2008' THEN Team_Total ELSE 0 END) AS [Apr 08] ,
SUM(CASE WHEN Team_Date = '01/05/2008' THEN Team_Total ELSE 0 END) AS [May 08] ,
SUM(CASE WHEN Team_Date = '01/06/2008' THEN Team_Total ELSE 0 END) AS [Jun 08]
FROM dbo.uView_DimHC_Team_Details_View1 where TeamParentID < '3' GROUP BY TeamName ORDER BYTeamName

This basically creates a table where I have team names as rows, month names as columns and team strength as each value.
Now I want to add one row at the bottom which gives a summary which basically calculates all the values in that column. I am displaying this on web where I can do this using gridview but the problem is I am using the same gridview for 2-3 different queries which have different columns so the rowdatabound method can not be used.
How can I get aggregate row at the end of table from this table?

Thanks

View 12 Replies View Related

Sql Statement For Records Summary

Aug 8, 2004

hi
suppose the recordset like this, that shows the cell phone models of nokia, siemens,...

IDModel Mark ModelCaption
1 nokia 6100
2 nokia 6220
3 nokia 6600
4 siemens mc65
5 siemens sx1

now, i want to organize these records to somethin like this:

Mark Models
nokia 6100,6220,6600
siemens mc65,sx1

how can i do it?

View 7 Replies View Related

Resultset With Summary Rows

Feb 26, 2004

Hi,

I have problem with query/sp which need to return one resultset with strucure like:

summary row1
detail row1
detail row2
detail row3
summary row2
detail row4
detail row5
...

Tables:
T_HW (HW_ID INT, CatID INT, other cols (NVARCHARs, INTs, DATETIMEs, NTEXTs etc.)
T_Category (CatID INT, Manufact NVARCHAR (100), other cols)

The output I need is ( '|' means cols separator) :
Manufact1 | No of recs from HW for this Manufact | nulls ...
null | null | HW1 from Manufact1 details ...
null | null | HW2 from Manufact1 details ...
Manufact2 | No of recs from HW for this Manufact | nulls ...
null | null | HW3 from Manufact2 details ...
...

it needs to be one result recordset

Thx in advance,
MST78

View 4 Replies View Related

Need Help Calculating A Summary Report

Mar 16, 2008

Hello everyone,
I'm trying to build a report that calculates a summary of all my applicants based on the center they're enrolled in.

I have built a table that displays all applicants and sorts them by the center and I can use the count function to get a count of ALL applicants for all centers, but not a count of each center in one report:
example:

Applicant1 Field1 Field2 Field3 Center 1
Applicant2 Field1 Field2 Field3 Center 1
Applicant3 Field1 Field2 Field3 Center 1
Applicant4 Field1 Field2 Field3 Center 1
Applicant5 Field1 Field2 Field3 Center 2
Applicant6 Field1 Field2 Field3 Center 2
Applicant7 Field1 Field2 Field3 Center 2

I need the count of all applicants from center 1, center 2, etc..
In this example I need 4 for center 1, 3 for center 2 etc..

View 9 Replies View Related

Detail And Summary Sub Reports

Jan 10, 2008

Hello,

I am trying to create two subreports in the main report. One sub report should give detail data and other sub report shuold give sumamry data. Is detail and summary reports are possible in sub reports? Iappreciate your help on this.

Thanks,

View 13 Replies View Related

Group Total Summary Help

Apr 12, 2007

I hope someone can help me with this one. I can't seem to find a way to solve my problem. I am converting a report from Crystal to RS. In Crystal I am using global variables to keep track of group totals for a final summary. I need a similar result from RS. Data example

Group A

PK Field
Summary Data Field

1
250

2
300

Group A Total
550

Group B

3
100

4
50

Group B Total
150

Grand Total
700

The underlying query contains detail data and I am using a table with two group levels. All details are hidden.

To calculate the totals at the detail level I need to know what the total value for the entire group is. This leads me to my problem, it is not possible (as far as I can tell) to summarize a summary (I get an error). I have tried using the code window to store variables but the value returns a 0. I found a suggestion here http://msdn2.microsoft.com/en-us/library/bb395166.aspx under Distinct Sum, but I can't call the function using the Sum command given that the formula to calculate the value is already using the sum command. I hope this makes sense.

Thanks,

Simone

View 9 Replies View Related

Database Summary Reports

Mar 7, 2006

I'm not sure this is the correct forum for this, but it seemed to be the best place to start.

I have been trying to manage my SQL 2000 Databases using Microsoft SQL Server Management Studio. It works well for most everything. The problem is, however, that there is no equivalent to the SQL 2000 Taskpad View in SQL Server Management Sudio.

In the Summary screen when connected to a 2000 Database the Reports button is disabled. If I restore a 2000 Database into a 2005 DBE then I get the message that the compatibilty Mode is 80 and I must set it to 90 to get this report.

I could switch this to a 90 compatibility mode, but I don't think I should need to do this. Additionally, I don't want to have 2000 and 2005 both installed to quicly examine the used to free space ratio on a Database.

How do I get around this? Is there a switch that I missed somewhere? Is it possible to get the Disk Usage report to work from the Database Summary Page for a 2000 Database or a Database running in 80 Compatibilty mode?

Thank you,
Jeffrey Irish
jeff.irish@apisoftwareinc.com

View 1 Replies View Related

Calc A % Of A Summary Total

Jun 29, 2007

I have a report that requires 2 "tables". The first table summarizes total
lbs by a category and then provides a company total at the end:

cat 1 75
cat 2 100
cat 3 200
-------
total 375

The second table needs to display the % of the total for each category:

cat 1 20%
cat 2 27%
cat 3 53%
-------
total 100%

How can I reference the company total for doing the calculations for the
second table? I am working with Visual Studio 2003. My dataset pulls a
file from the AS400 using an sql statement.

Thank you, PB

View 4 Replies View Related

Summary Of Metrix Column

Jan 8, 2008

Hi.
I have a Metrix include CheckNbr, InvoiceNbr, InvoiceAmount, ItemNo, ItemQty and Group by
CheckNbr & InvoiceNbr. InvoiceNbr parent group is CheckNbr.
Does anybody knows how to make a summary of InvoiceAmount when CheckNbr changed?
I tried to use sum(InvoiceAmount, Group) but I got wrong amount, because the amount will duplicate count according to ItemNo record count.

Thanks

View 7 Replies View Related

Summary Of Replicated Records

Apr 14, 2007

Hi all

Our company is considering using replication to synchronize data between handheld devices and SQL Server 2005. One of our requirements is the ability to retrieve a summary of all updated records in the Tags table (only on the server) each time data is retrieved from one of the handhelds.

Is this easily accomplished? How can it be done?

Thanks.
-Kevin

View 3 Replies View Related

Need Help With Conditional Group Summary

Sep 13, 2007

I have a report that groups on company name, then has a sub group that lists all the invoice details for that company. I want to have the layout put a sum line for those companies that have more than one invoice shown for them, and nothing if only one invoice. I dont want to clutter the report with redundant individual sum totals when there is only one row in the first place.
.
My first attempt was to play with the visibility of the group footer (entire row) with this expression:

=Iif(RowNumber(Fields!Invno.value)>1, False, True)
I get this error:

The Hidden expression for the table ��datadetail�� has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

I can't think of another way to do this in the report layout. I could go back to the sql code and do a lot of sql gymnastics to create a row number for each company and base my visibility off of that, but I am really hoping there is a simpler way to do this in the report layout. Any help would be most appreciated!!

Carl Henthorn

View 1 Replies View Related

Summary Report Using Nested Sqlcommand

May 1, 2006

Let me try to be as clear as possible. I am using VWD c# code behind asp.net 2.0
A company has to track the types of calls multiple extensions receive.
Each extension receives hundreds of calls each day stored in a table.
I need to generate a Report that produces one row for each extension and it counts the types of calls that extension receives.
(I would like to use some type of Data control to do this)
I can display the extensions:
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString);
connection.Open();
string sql = "Select extension from View1 where DATEADD(d,0,DATEDIFF(d,0,insertDate))='5/01/06' group by extension";
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
string ext= (dr["extension"].ToString());
Response.Write ( ext + "<br>");
This is where I need do my counts.. I tried to create and run a count(*) queary while in the readerbut it craps out..........
int total;
string countcmd = "select count(id) as total from leads where ext='" + ext + "' and dateadd(d,0,datediff(d,0,insertdate))='5/01/06'";
sqlcommand cmd = new sqlcommand(countcmd, connection);
total = (int)cmd.executescalar();
Response.Write(total + "<br>");
gives me Error
3 The type or namespace name 'sqlcommand' could not be found (are you missing a using directive or an assembly reference?)
Any help would be greatly appreciated,
Doug

View 5 Replies View Related

How To Produce A Summary Row For Select With No Results

May 28, 2013

way to insert a summary row where there are no query results - in effect a row to say there were no results for today. I am using SQL Server 2008.

I have created a report which uses 2 tables to store all daily transactions & a one row summary of those daily transactions for upto 4 countries - both tables are updated nightly by 2 SP and the unique identifier is a field called ID which contains the country code and date;

table1_detail;
store all daily transactions for 4 countries and the unique identifier is an ID which stores the country code and date (XX_ddmmyy) eg GB_280513. There may be transactions for upto 4 countries(GB/FR/IE/DE) per day or there may be none.

Table1:

Code:
ID DATE CUST VALUE ERROR ...etc
GB_280513 280513 101 10.50 YES
GB_280513 280513 102 90.00 NO
FR_280513 280513 201 25.00 NO
IE_280513 280513 301 60.00 NO
FR_280513 280513 202 10.50 YES
FR_280513 280513 203 10.50 NO
GB_280513 280513 103 20.00 YES
GB_280513 280513 104 5.00 YES

table2_summary;
summary of daily transactions per unique identifier (XX_ddmmyy) in table1_detail. When there are transactions per unique identifier in table1 the SP inserts a row summarising quantity, value, error count etc.

Table2:

Code:
ID DATE NO OF TRANS VALUE NO OF ERROR ...etc
GB_280513 280513 4 125.50 3
FR_280513 280513 3 46.00 1
IE_280513 280513 1 60.00 0

I need the insert into table2_summary to be able to insert a row everyday for each country showing zeros where there were no transactions for that day (ID) in table1;

Table2 expected results:

Code:
ID DATE NO OF TRANS VALUE NO OF ERROR ...etc
GB_280513 280513 4 125.50 3
FR_280513 280513 3 46.00 1
IE_280513 280513 1 60.00 0
DE_280513 280513 0 0.00 0

When there are no transactions in table1 for a day (ID) then the summary needs 4 zero rows inserted.

View 3 Replies View Related

Creating Ratio From Summary Data

Dec 1, 2014

I am doing some work in microstrategy reports and using SQL data cube. I am creating a summary report (Counting on Unique ID) in MS where when I put in a particular attribute/column from cube it splits the data count down into 2 separate values that I have defined in the select case statement of the SQL query.

I want to work out a ratio of the count unique ID between these two values but can't do this in MS report as it is not a physical column but summary split of data.Is there a way I can do this in SQL? My summary looks something like this:

N E
====================== =========================
**Y** N **Y** N
========= ========== ========= =========
570 140 89 56

Where the Y/N field is the one I have split down against the N/E column. I want to work out ratio between the 2 "Y" fields but in SQL. Here is a SQL example snapshot of my data:

Unique_ID New/Existing Application Attend_Interview
========= ======================== ================
12554445 E Y
65766879 N N
53375654 N Y
44323224 E N
93656786 E Y

wondering if I might be able to do a procedure or similar or something more dynamic in SQL query?

View 1 Replies View Related

SQL Server 2005 Summary Window

Nov 15, 2007

Probably simple but I cannot find the property setting that allows me to change the default date format of the column "Created" in the Summary window. Default it is 2007-11-15. As some tables are changed more than one time in a day, I would like to see also the time as was the standard in SQL SERVER 2000.
This is a minor problem but an irritating one!

Regards

Janne H.

View 5 Replies View Related

Summary Of Data By Business Week

Sep 6, 2006

I have a large query that returns a list of records that are marked by day of the business week (Monday, Tuesday, Wed, etc).

I am running into a challenge where I need to provide summaries of data from those days. For example, it needs to look like this:

Date | Day of Week | Total Widgets |

09/01/06 | M | 4
09/02/06 | Tu | 5
09/03/06 | We | 6
09/04/06 | Th | 7
09/05/06 | Fri | 8

Total Widgets: 30

09/08/06 | M | 1
09/09/06 | Tu | 2
09/10/06 | We | 3
09/11/06 | Th | 4
09/12/06 | Fri | 5

Total Widgets: 15

I'm using Reporting Services to format and display the report.

I've got the group by working for the entire data set, but I need to separate it by these sections. Any assisntance is appreciated.

View 2 Replies View Related

Data Retrieval For Reports From Summary Table.

Feb 22, 2007

Hi,
I have a summary table like this

Field1
Field2
Field3
Field4
Field5

AAA11
value1
value2
value3
value4
value5

AAB23
value6
value7
value8
value9
value10

BCD14
value11
value12
value13
value14
value15

GFD12
value16
value17
value18
value19
value20

SDL25
value21
value22
value23
value24
value25

AUD56
value26
value27
value28
value29
value30

BER11
value31
value32
value33
value34
value35

Columns are obviously fixed, but not rows.
I want to show this data using lables and SqlDataReader for report purpose like;
Label1.text=dr("value16").toString( )
Label2.text=dr("value28").toString( )
Label3.text=dr("value31").toString( ) etc

Do you have any idea how i can do it or am I approaching it in the wrong way????

Thanks.
Michelle

View 1 Replies View Related

Create Summary Data From Multiple Tables

Aug 13, 2013

I've been struggling with this for about 2 weeks now and can't seem to get any further.I have two tables: orders and orders_extended. They can be joined by the common orderid field (example SELECT * FROM orders JOIN orders_extended ON orders.order = orders_extended.orderid WHERE 1=1)I need to create a report that sums the following fields:

SELECT

CONVERT(VARCHAR(12), orderdate, 101) As orderdate
, COALESCE (
CASE WHEN orders_extended.productprice < 0 THEN 'DISCOUNT' ELSE orders_extended.productnumber END
, CASE WHEN orders_extended.productnumber LIKE '%AB%' THEN 'PRODUCTGROUPAB' ELSE orders_extended.productnumber END
, CASE WHEN orders_extended.productnumber LIKE '%CD%' THEN 'PRODUCTGROUPCD' ELSE NULL END

[code]...

What I'm trying to accomplish is to get the total dolloar amount of sales for each day for each payment type on one line per productgroup.

View 12 Replies View Related

Exports To Excel The Details Of The Report But I Need Summary

Sep 25, 2007

The trouble I am having is that I have a drilldown report that exports the detail to Excel, but I want the summary exported to Excel.

I perform the following steps but get wrong results. Please help me identify the correct steps for the correct results. Thanks you.

1) Select Matrix.
2) Right click, select Properties.
3) Select tab Groups.
4) Select item in Columns list, click Edit.
5) Select tab Visibility.
6) Select Initial visibility: Hidden, and click okay to the Grouping and Sorting dialog box.

Now I can export summary to Excel okay, but now I can not expand the summary in the report itself, so I do the following:

7) Do one through six above (but do not close dialog box), then click visibility can be toggled by another report item.
8) Select the report item in the Report Item drop down list.

Now the report functions normally in the Reporting Services report web page, but when I export on the summary level, again it exports the detail to Excel, but what I want it to export is the summary.

Ideas?

View 8 Replies View Related

Display Summary Week Total Rows From Sql Database

Sep 22, 2007

(I moved this thread from datagrid area) I have a sql database that has individual records consisting of name, date, hours worked among other fields.Date and name is part of a unique identifier, so there can NOT be two records for the same person for the same date. My users need a grid view that displays days worked in ONE LINE per user. I have gotten close, but can't quite get the last part. Ive tried group by, distinct, and with rollup and no luck.TABLE:dan       12/13/2012 12:00:00 AM9.123dan       12/14/2012 12:00:00 AM3.123123cara      12/12/2012 12:00:00 AM4.222cara      12/16/2012 12:00:00 AM3.3333cara      12/17/2012 12:00:00 AM2   CODE: Select distinct(name), (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Sunday')as Sunday, (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Monday')as Monday, (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Tuesday')as Tuesday, (select
(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =
YT.hours and datename(dw, date)='Wednesday')as Wednesday, (select
(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =
YT.hours and datename(dw, date)='Thursday')as Thursday, (select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Friday')as Friday, (select
(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =
YT.hours and datename(dw, date)='Saturday')as Saturday,(select sum(hours)from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours) as Totalfrom dbo.testtime YTgroup by date, name, hours RESULTS: cara          NULL    NULL    NULL    4.222    NULL    NULL    NULL    4.222cara          NULL    2    NULL    NULL    NULL    NULL    NULL    2cara          3.3333    NULL    NULL    NULL    NULL    NULL    NULL    3.3333dan           NULL    NULL    NULL    NULL    NULL    3.123123    NULL    3.123123dan           NULL    NULL    NULL    NULL    9.123    NULL    NULL    9.123 Like I said, I am SO close, I just need it to look like;NAME    SUN    MIN    TU    WED    TH    FR    SA    TOTAL  cara          3.333    2                            4.222            9.555
dan                        9.125    3.125                            12.5  TIAdan

View 6 Replies View Related

Producing A Summary Table Of Amounts Per Status Per User

Jan 5, 2012

I want to produce a summary table of amounts per status per user.

I have 2 tables:

Invoices:

Code:
user_id, amount, status
1, £10, S
2, £20, P
3, £30, P
3, £40, E

Users:

Code:
user_id, name
1, user A
2, user B
3, user C

And I want to produce a summary table like this:

Code:
S P E Total
user A £10 £10
user B £20 £20
user C £30 £40 £70

What I have is:

Code:
SELECT Users.name,
(SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'S')),
(SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'P')),
(SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices.user_id AND (t1.status = 'E')),
(SELECT SUM(amount) FROM Invoices AS t1 WHERE t1.user_id = Invoices .user_id AND (t1.status IN ('S','P','E')))
FROM Invoices
LEFT JOIN Users ON Users.user_id = Invoices .user_id
GROUP BY Invoices.user_id, Users.name
ORDER BY Users.name

This does give me what I want, however the real situation has lots of status codes, many more fields in the Invoices table, hundreds of users and hundred of thousands of records in the Invoice table and I have run out of system memory.

View 9 Replies View Related

SQL Server 2012 :: Range Group Summary Of Particular Column

Jun 3, 2014

I have column which stores People count based on department, Now I want to keep them in the batch of 1000, If the running summary of (No of people) from departments reached 1000 then it should start sum(no of people) from 0 to 1000

Is there any running summary kind of function which can start sum record with in range of 0-1000

For Ex. My Data stored like this
Dept People Count
CSE 200
IT 250
EEE 312
ECE 214
MEC 337

Batch Grouping
Dept People Count BatchSum
CSE 200 200
IT 250 450
EEE 312 762
ECE 214 976
MEC 337 337 (Note here since its crossing 1000, its resetting and starting summary)

I implemented this with While Loop & if condition, But its very slow, is there any other way to achieve it in better way.

View 8 Replies View Related

Table Census Data - Summary Column Not Working

Jul 4, 2014

I have a table census data containing the number of people having ages ranging from 0 to 120 years. Year 0 is in a column called F_0 and year 120 is in a column called F_120 with everything in between.

For example, when I create a script for the table the column for year 0 appears as: [F_0] [numeric](38, 8) NULL

I've already created 2 summary columns (people 0 to 18 years old and people 19 to 59 years old).

I follow the same methodology to create a summary table for people from 60 to 100 years old, but all the calculated values are NULL.

So the following is successful:
[Under_19] AS (((((((((((((((((([F_0]+[F_1])+[F_2])+[F_3])+[F_4])+[F_5])+[F_6])+[F_7])+[F_8])+[F_9])+[F_10])+[F_11])+[F_12])+[F_13])+[F_14])+[F_15])+[F_16])+[F_17])+[F_18]) PERSISTED,

But this is not:
[Over_60] AS (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((([F_60]+[F_61])+[F_62])+[F_63])+[F_64])+[F_65])+[F_66])+[F_67])+[F_68])+[F_69])+[F_70])+[F_71])+[F_72])+[F_73])+[F_74])+[F_75])+[F_76])+[F_77])+[F_78])+[F_79])+[F_80])+[F_81])+[F_82])+[F_83])+[F_84])+[F_85])+[F_86])+[F_87])+[F_88])+[F_89])+[F_90])+[F_91])+[F_92])+[F_93])+[F_94])+[F_95])+[F_96])+[F_97])+[F_98])+[F_99])+[F_100])+[F_101])+[F_102])+[F_103])+[F_104])+[F_105])+[F_106])+[F_107])+[F_108])+[F_109])+[F_110])+[F_111])+[F_112])+[F_113])+[F_114])+[F_115])+[F_116])+[F_117])+[F_118])+[F_119])+[F_120]) PERSISTED,

View 2 Replies View Related

How To Generate Condition Based Aggregate/summary Data

Jun 14, 2006

Hi

I have a source table with App_Id, App_Type , Tier_Type, Date_Id as columns. I have a destination table with Date_Id, Total_App_Count, Booked_App_Type_Count, Approved_App_Type_Count columns.

I need to count the Total number of records , count of records with App_Type = "Booked" and count of records with App_Type = "Approved" and populate in my destination table.

What all the steps i need to create in SSIS to get this results ?

Thanks

Kumaran

View 2 Replies View Related

Classic Sales Summary With Royality Logic Has Me Stumped.

Nov 1, 2007

Hello everyone. I'm stuck with what must be a common sql challenge.

I've got this single table that looks like this:

Store, Original Store, Product, Sale type, Method, Amount

There will be two product types (prodA and prodB)

There will be two Sale Types (New and Recharge)

There will be two Method (Cash and Credit)

The tricky part is I also need to report sales activity when a store
was the Original Store for a customer Recharge at another Store.

The report needs to look like this:

Store , Product, Cash New Total, Cash Recharge Total, Credit New
Total, Credit Recharge Total, Outside Recharge Total

Grouping/Totally on Store/Product.

In the above "Outside Recharges" means the Store was the Original store
and another store performed a recharge on it's customer.

Thanks for any help or information

View 3 Replies View Related

Reporting Services Configuration Killed Several Summary Report Functions

Nov 22, 2006

I installed 2K5 developer on my box. I was impressed with the Summary tab report functions and the reports available there. Then I ran the "Reporting services configuration" tool to get RS running, but now several of the summary reports are broken. There are several different error messages given in the summary window, depending on the report selected.

For example, the "server dashboard" now displays

Summary Unavailable

The information needed to display a summary page for the selected object is not available.

Error:

"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

The next two reports work, but the "Scheduler Health" report displays

Summary Unavailable

The information needed to display a summary page for the selected object is not available.

Error:

Incorrect syntax near '.'.

"Memory Consumption" works GREAT, but "Activity - All blocking transaction" shows:

Summary Unavailable

The information needed to display a summary page for the selected object is not available.

Error:

Incorrect syntax near '.'.
Incorrect syntax near '.'.
Incorrect syntax near the keyword 'as'.
Incorrect syntax near the keyword 'as'.

What could be the cause of such errors? Is there an "easy fix"? I wouldn't know how to "unconfigure" reporting services and have a feeling that wouldn't help anyway...

View 11 Replies View Related

Power Pivot :: Creating A Summary Report Using Relationship Between Date Table And Two Fact Tables

Oct 19, 2015

I have below tables in my power pivot.Fact 1 & Fact 2 - connected directly to Mainframes - Data is from the same table broken up due to size of the data.Date Table - Relation ship between both the fact tables.How do i create a summary pivot to get the number of tasks that have been completed in each month.

Month Count
July
August
September
October.

View 3 Replies View Related