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]....
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 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 have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB Â Â Â Â 123
colA Â Â Â Â XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
[Code] ....
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!
...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!
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?
I have created a sample Table with inserting below data,
ID   Subject   CreatedDate
1    Test     2015-09-20 22:59:07.373
2    Test     2015-09-21 09:16:58.290
3    Test     2015-09-21 09:18:17.500
I am loading a dropdown with Subject. The dropdown list shows the 3 items with same subject name since i have three records with same subject. I need a SQL select query  for - if subject 'Test' is selected from the dropdown, need to be able to pull its corresponding associated ID from the table based on CreatedDate?Â
I have the follwing function in my SQL data base which is hosted in Azure.
All date and time field in my db are store as UTC and I have a function define as below :
ALTER FUNCTION [dbo].[func_GetCurrentLocalTimeFromUTC]
(
)
RETURNS datetime
AS
BEGIN
DECLARE @OffSet as int = 1
DECLARE @CurrentDate as datetime = getUTCdate()
[Code] ...
Now what I have trying to do is to perform a select statement on a table where I have a dateTime value field and add the proper offset value to the field in order it is display based on where user will run the query. For example if I run this simple query :
SELECT INVENTORYDATE from Inventory
Will return the UTC dateTime value.
How can I use the select statement in order to get the field format with proper offset based on user pc timezone/. Please note that the query will be called from a rrs.
How Can I select Table Dynamically from in Side SQL Query
i.e.,
Select * from (Here I want Select the Dynamically from other Query)
I have a SELECT query that also needs to call a sproc with a column name passed in as a parameter.
Unfortunately I cannot use a TVF, as the sproc code references a TVF on a linked server.
What is the best option?
SELECT
u.UserID,
u.Username,
u.Address,
EXEC dbo.[GetResult] u.UserID AS 'Result'
FROM dbo.UserTests u
How I can get the desired result using query. I don't want any
'@' variable in the result.
DECLARE @ST AS varchar(10)='AA'
DECLARE @desc  AS int=8
DECLARE @STIDÂ AS int=4
DECLARE @PP ASÂ Â Â Â Â int=63
DECLARE @SS ASÂ Â Â Â int=22
/* Desired Result */
Update #RT Set ST='AA', desc=8, STID=4 Where PP=63 and SS=22
I have a stored procedure in which I'll select some rows based on a condition and I need to update the status of those rows within the same stored procedure.For e.g.
Create Procedure [dbo].[myProcedure] As  BEGIN  BEGIN TRAN T1  SET NOCOUNT ON  SELECT TOP 5 * INTO #TempTable FROM myTable WHERE ENABLED = 1 AND FetchDate<=GetDate();  UPDATE myTable SET [Status] = 'Locked' From myTable Inner Join on #TempTable myTable.id = #TempTable.id;  SELECT * FROM #TempTable;  DROP Table #TempTable;  COMMIT TRAN T1  END
The Stored Procedure works fine when I debug in SQL. I'm accessing the StoredProcedure through C# like this.
private ProcessData[] ReadFromDb(string StoredProcedure, SqlConnection Connection) {        List<ProcessData> Data = new List<ProcessData>(); SqlCommand Command = new SqlCommand(StoredProcedure, Connection); Command.CommandType = System.Data.CommandType.StoredProcedure;  try        {          Command.CommandTimeout = CONNECTION_TIMEOUT; Â
[code]....
The problem is I'm getting the required rows in C# but the update query in stored procedure is not working.
I am trying to fetch records from excel sheet using Select Query but I am getting the error message saying
"Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"."
Here is my Query,
sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go
reconfigure
SELECT *
FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:M2MworkedworkedBOS.xlsx;HDR=YES', 'select * from [Sheet1$]') AS A;
The select command below will output one patient’s information in 1 row:
Patient id
Last name
First name
Address 1
OP Coverage Plan 1
OP Policy # 1
OP Coverage Plan 2
[code]...
This works great if there is at least one OP coverage.  There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table.  The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill.  The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date. Â
select src.pat_id, lname, fname, addr1,
max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1,
max(case when rn = 1 then src.policy_id end) as OP_Policy1,
code]...
I've got a select as follows:
select computer, count(*) as MissedCount from WInUpdates_Neededreq
WHERE LoggedDate BETWEEN DATEADD (DAY, - 5, GETDATE()) AND GETDATE() and LastReportTime !< DATEADD (DAY, -5, GETDATE())
group by computer
I need to make a join onto another table but don't want to lose the coutn(*) as MissedCount.
How can I join to another table and still keep the count form the original table. I want ot join to tblogons.workstationname and return computer from the original query...
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
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
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
Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return.
This is implemented as a stored procedure
declare @OwnerIdent varchar(7)
set @OwnerIdent='A12345B'
SELECT table1.val1 FROM table1 INNER JOIN table2
ON table1. Ident = table2.Ident
WHERE table2.Ident = @OwnerIdent
'Now for each result of the above I need to run the below query
SELECT Clients.Name , Clients.Address1 ,
Clients.BPhone, Clients.email
FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey
WHERE Growers.PIN = @newpin)
'@newpin being the result from first query
Any help appreciated
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?
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
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..
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,
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
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
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
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
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
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
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
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