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
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);
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.
...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.
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.
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?
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?
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?
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 ... ...
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..
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.
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.
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?
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.
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.
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.
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!!
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
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.
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?
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!
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????
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.
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.
(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
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.
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.
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,
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 ?
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...
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.
