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.
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 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'm trying to calculate how much unused space i have on one datafile. My main goal is to determine the max space i can save by doing a dbcc shrink. Any help is greatly appreciated.
Hi people. I hope you can help with this. It has me completely stumped. I am trying to get SQLS 2000 to calculate days between dates, without any success at all. The two dates are as imported from a text file, and are as follows: - (@issueddate=) 3/01/2006 3:37:00 PM (@completiondate=) 04-FEB-2006 18:00
I cannot do much about the date formats, as they are as received from another party, as an email attachment.
I have tried just about everything I know to get this working, even tried commenting out each line till practically nothing is left, and still get the same message. Have also deleted the procedure and recreating it. I have tried removing "PM" from the first date; tried changing varchar to datetime; tried using 'dd' instead of 'd' in the DateDiff function Because of the "Aborted" response in Query Analyser, nothing is returned, and yet the Query Analyser's "Execute" Command reports it completed successfully. Should I ignore this "Aborted" response altogether and assume that it works? Is it a known bug in SQLS 2000?
Here is the Stored Procedure: ---------------------------- CREATE procedure proc_calcDeadline (@issueddate varchar(30)='',@completiondate varchar(30)='',@return int=NULL output) as declare @Period int --set @issueddate='3/01/2006 3:37:00 AM' --set @completiondate='04-FEB-2006 18:00' set @period=datediff(d,@issueddate,@completiondate) --print 'period=='+cast(@period as varchar) set @return=@period return @return GO
The DEBUG response in Query Analyser is as follows: -
"Aborted"
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification When I make changes in Query Analyser, (ie, "Alter Procedure', etc" then select "EXECUTE" option the response is: -
"The command(s) completed successfully."
PS I am STILL unable to get fully into Query Analyser for step-by-step processing, even though I created the database on a single workstation (and therefore am the dbowner, with full Admin rights).
(Homesick for VBA territory, where I can get date calcs like this done easily!!)
I use the NETWORKDAYS(start_date, end_date,holidays) function in Excel and Access regularly. I'm trying to find a similar function in TSQL to use on Server2005. NetWorkDays allows me to calculate the difference between my ticket open and close dates while excluding weekends and holidays, (we are nice to our staff and let them off).
I'm trying to find an easy way to do this in TSQL without writing a large amount of code.
I created a dbo.Calendar table that stores dates and a work day flag (1=work day, 0=non-work day) so I can use it to calculate the next business date from a date using a function. I'm using a while group to count only the work days and a couple other internal variables but I'm not sure if I can even use them in a function.
Assuming Sats & Suns are all non-work days in April 2014, if my @WorkDays = 10 for 10 work days and my @DateFromValue - 4/1/2014, I would expect my return date to be 4/15/2014.
------ Messages after I click execute on my query window that has my function ------------------------------------------------------ Msg 444, Level 16, State 2, Procedure FGetWorkDate, Line 19 Select statements included within a function cannot return data to a client. Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 20 Invalid column name 'WorkDay'. Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 22 Invalid column name 'Date'.
------ my function code ---------------------------- CREATE FUNCTION [dbo].[FGetWorkDate]( Â Â Â @WorkDays VARCHAR(5), Â Â Â @DateFromValue AS DateTime ) Â Â Â RETURNS DATETIME
Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.
Very new to SQL and trying to get this query to run. I need to sum the total trips and total values as separate columns by day to insert them into another table.....
My code is as follows;
Insert Into [dbo].[CombinedTripTotalsDaily] ( Year, Month, Week, DayNo, Day, Trip_Date,
I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob
I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month. I need to pull the difference of todays cumulative total less yesterdays. So when my total for today is 30,000 and yesterday's is 28,800, my sales for today would be 1,200. I want to write this to a new field but I just can't seen to get the net sales for the day. Here is some sample data. For daily sales for 6-24 I want to see 2,000, for 6-25 3,000, 6-26 3,500, and 6-27 3,500. I'm thinking a case when but can't seem to get it right.
I have created a Report using Visual studio-the report displays a subreport within it.
On the Subjective Report I have 12 values for each month of the year.
For the first month the value is =sum(Fields! Month_1.Value), and I have named this text box €™SubRepM1€™ The name of the subreport is €˜subreport1'.
On my Main Report, again I have 12 values for each month of the year. For the first month the value is =sum(Fields! Month_1.Value)*-1, and I have named this text box 'MainRepM1' The name of the main report is 'GMSHA Budget Adjustment Differentials'
The report displays both of the subreport and main report values but I now need to total these values together for each month in order to produce a grand total.
I have tried using the following to add the totals for Month 1 together, =subreport1.Report.SubRepM1 + MainRepM1 but this does not work and I get the following error message €˜The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.
I feel that it should be a simple matter of adding the two sets of values together but I€™m having major problems trying to get these totals to work.
do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like
SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name FROM... WHERE ... and calcd name in (@variable)
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?
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
Anyone has a "one sql statement" to get the total spaceused and totalspace allocated of an instance ? ie same as sum of relevance fieldsfrom sp_spaceused for each database in an instance, that works accrossversion of mssql from 6 onward.ThanksKD
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);
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'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?
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!
