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 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 want to display records as per if else condition in ms sql query,for this i have used tables ,queries as follows
as per data in MS Sql
my tables are as follows
1)material
fields are -- material_id,project_type,project_id,qty, --
2)AB_Corporate_project
fields are-- ab_crp_id,custname,contract_no,field_no
3)Other_project
fields are -- other_proj_id,other_custname,po
for ex :
vales in table's are
AB_Corporate_project
=====================
ab_crp_id custname contract_no field_no
1 abc 234 66
2 xyz 33 20
Other_project
============
other_proj_id other_custname po
1 xxcx 111
2 dsd 222
material
=========
material_id project_type project_id qty
1 AB Corporate 1 3
2 Other Project 2 7
i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project
sample query i write :--
select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id,
ab.custname ,op.other_proj_id,op.other_custname,op. po
case if m.project_type = 'AB Corporate' then
select * from AB_Corporate_project where ab.ab_crp_id = m.project_id
else if m.project_type = 'Other Project' then
select * from Other_project where op.other_proj_id=m.project_id
end
from material m,AB_Corporate_project ab,Other_project op
but this query not work,also it gives errors
i want sql query to show data as follows
material_id project_type project_id custname other_custname qty
1 AB Corporate 1 abc -- 3
2 Other Project 2 -- dsd 7
so plz help me how can i write sql query for to show the output
plz send a sql query
I want to run a query that returns say 100 records...but I only want to return first 10 for first page on a web page, then on page 2 the webpage will return rows 11 to 20 of the same SQL statement...page 3 returns rows 21 to 30 rows etc....(eg. like Google or bulletin boards, browsing auctions in ebay etc.).
I could probably get my application logic to handle this (ASP.net), for instance I could possibly get a datareader to skip the first 10, output the next 10 then stop for page 2 (records between 11 amnd 20) but is there way to do this in SQL Server at the database level using an SQL Statement?
I can use TOP 10 to get the first set of records for the first page eg.
SELECT TOP 10* FROM Suppliers
...but how do I get between 11 and 20, 21 and 30 and so on?
I've already mentioned I could handle this in my application logic, but then each time the same SQL Statement is fetching all 100 records, even though the web page will only display a certain range. I'm building an intranet website that can potentially run queries that return 100'000s records, even though initially only top 20 or so records are display, each page they subsequently go to will rerun that same query that returns all 100'000 records. So handling this as part of the query would be better for performancr I reckon.
Someone gave me the syntax..
SELECT *
FROM employees
LIMIT 10,10;
..but it didn't seem to work.
Thanks for any help!!
I have the following code:
TRUNCATE TABLE [Temp_Export];
INSERT INTO [Temp_Export]
(
[Code]....
The issue I'm having is that I am getting more records in the VIEW than records updated. What can explain such a discrepancy? I am updating the records based on the PK/FK Temp_Import_ID column, which exists in both tables. where the view would yield more records than those matched by the update statement?
I have a form with a dropdown or combo box, the user can select <All>or pick a user name. If they pick a user name my where clause worksfine, buts what's the best way to write "Select All" if they choosethe <All>This is what I have so far, but I don't think I should be using theLIKE operator.WHERE tblCase.qarep LIKE CASE @myqarep WHEN '<All>' THEN '%' ELSE@myqarep ENDand tblOffice.officecode LIKE CASE @myoffice WHEN -1 THEN '%' ELSE@myoffice ENDthanks for your help!!
View 4 Replies View RelatedHi..
As everyone knows that there's a keyword in MySQL that is used to limit the records in SELECT statement as
SELECT * FROM Table LIMIT 40,10
means that select from record 40 to onward 10 records.
In SQL Server it is a long way with nested select ... can someone provide an alternate or we have to use the Nested SELECT?
Thanks
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
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 requirement to mark duplicate records when I pull them from the database.
However, I only want to mark the 2nd, 3rd, 4th etc record - not the first one.
The code I have below creates a column called Dupes but marks all the duplicates - including the first one.
Is there a way to only mark the 2nd, 3rd, 4th etc record ?
SELECT *, cs.CallStatusDescription as CSRStatusDesc, cs2.CallStatusDescription as CustomerStatusDesc, (Select MAX(CallAttemptNumber)From CallResults cr Where cl.Id = cr.CallLogId) as CallAttemptNumber,
Dupes = (select count(id) from CallLogswhere (CustomerHomePhone != '' AND cl.CustomerHomePhone = CustomerHomePhone)OR (CustomerBusinessPhone != '' AND cl.CustomerBusinessPhone = CustomerBusinessPhone)AND DealerId= 'hdsh' AND CSRStatus IS NULLand datediff(d, logdate, getdate()) <= 21),
FROM CallLogs cl left Join CallStatus cs on cs.Id = cl.CSRstatusleft Join CallStatus cs2 on cs2.Id = cl.Customerstatus Where SaleStage IN ('1', '2', '3', '4', '5', '6') And (LogProcessFlag = 1 Or LogProcessFlag = 0)And DealerId='hdsh'And Logdate Between '08/01/2007' And '08/31/2007'
I've searched high and low for info on how to do this... If anyone has an idea, I'd really appreciate it.
I have three tables: PEOPLE, PROJECTS, COMMENTS. I want users from the PEOPLE table to retrieve a list of PROJECTS and be able to add/edit COMMENTS on those projects.
The tables look like:
PEOPLE
people_id (primary key)
first_name
last_name
PROJECTS
projects_id (primary key)
project_title
project_summary
COMMENTS
comments_id (primary key)
projects_id (foreign key)
people_id (foreign key)
comment_detail
I'd like to be able to output something like what I have below, but I don't know how to loop over the comments/people within the select statement for the projects.
DESIRED OUTPUT
Project #1
Comment #1 by Person #1
Comment #2 by Person #3
Comment #3 by Person #8
Project #2
Comment #1 by Person #2
Comment #2 by Person #3
Comment #3 by Person #6
Etc...
I've done it before by just listing the projects and then providing a detail page with all the comments, but it's much less confusing to access all the comments from the same page, grouped by project.
Thanks in advance.
skip locked records in a MERGE statement and output the list of skipped records.
Through the documentation, internet posts and testing, I believe it is NOT possible. MERGE acts like a single atomic DML statement, and therefore cannot avoid locked records.
I can use the READPAST hint, which will skip the row-locked records. However, it could actually insert duplicate keys in certain cases (as it is ignoring records, i would guess), which would not be acceptable.
Hi...how can I write a dynamic sql statement to insert x number of child records? Thx in advance
View 4 Replies View RelatedHi,
I have a report with 5 filters which can be applied to it. The records are grouped by the Rotation programme there are on, with a subtotal for each unique programme name.
The report seems to work fine, but upon closer inspection - we noticed that 2 of the records are not being displayed. As a result, the total count is out by 2.
We tracked down the missing records so I ran the SQL query with a Where clause, and it was able to find the two records.
What could possibly cause this behaviour? Please see included SQL statement :
Code Block
SELECT Posts.PostNumber,COUNT(Posts.PostNumber) AS RPCount, Incumbents.Name AS IncumbentName, Grades.GradeTitle, Specialties.SpecialtyTitle,
Hospitals.Name AS Hospital, Genders.Gender, [Incumbent History].YearGraduated, COUNT([University Origins].Origin) AS OriginCount,
Incumbents.Nationality AS NationalityID, Countries.[Country Name] AS Nationality, [Rotation Programmes].[Programme Name],
Posts.[Post Approved for Training], [University Origins].Origin, Posts.OldPostNumber, [Rotation Programmes].[Programme ID]
FROM Posts INNER JOIN
Incumbents ON Posts.PostNumber = Incumbents.PostNumber INNER JOIN
[Incumbent History] ON Incumbents.[Incumbent ID] = [Incumbent History].IncumbentID INNER JOIN
Grades ON Incumbents.[Official Grade] = Grades.GradeID INNER JOIN
Specialties ON Posts.Specialty = Specialties.SpecialtyID INNER JOIN
Hospitals ON Posts.HospitalID = Hospitals.[Hospital ID] INNER JOIN
Genders ON Incumbents.GenderID = Genders.GenderID INNER JOIN
Countries ON Incumbents.[Country Of Birth] = Countries.[Country ID] INNER JOIN
[Rotation Programmes] ON Posts.[Rotation Programme] = [Rotation Programmes].[Programme ID] INNER JOIN
[University Origins] ON [University Origins].[Uni Origin ID] = Incumbents.[University Origin]
GROUP BY [Rotation Programmes].[Programme Name], Posts.PostNumber, Incumbents.Name, Grades.GradeTitle, Hospitals.Name, Genders.Gender,
[Incumbent History].YearGraduated, [University Origins].Origin, Incumbents.Nationality, Countries.[Country Name], [University Origins].Origin,
Posts.[Post Approved for Training], Posts.OldPostNumber, Specialties.SpecialtyTitle, [Rotation Programmes].[Programme ID]
Hi All,
I want to delete 5 lakhs records using delete...where statement. It is not deleting as a whole.
It is leaving 9300 records in the table.
Can you suggest me to delete 5 lakhs records in the table.
Thanks,
Padma
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!
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
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
I am renovating an existing application and am converting the existing passwords into hashed values using SHA1. I know how to compute the hashed values as a byte array for each record. What I don't know how to do easily is update all of the records i a single call to the database. Normally, I would just do the following:UPDATE HashedPassword = someValue WHERE UserID = 101;
UPDATE HashedPassword = someOtherValue WHERE UserID = 102;
...
What I don't know is what someValue and someOtherValue should be. How do I convert my byte array into string representation that SQL will accept? I usually execute multiple statements using Dim oCmd as New SqlCommand(sSQL, MyConn) and then call oCmd.ExecuteNonQuery().
Alternatively, I found the following code that uses the byte array directly but only shows a single statement. How could I use it to execute multiple statements as shown above?'FROM http://aspnet.4guysfromrolla.com/articles/103002-1.2.aspx
'2. Create a command object for the query
Dim strSQL as String = _
"INSERT INTO UserAccount(Username,Password) " & _
"VALUES(@Username, @Password)"
Dim objCmd as New SqlCommand(strSQL, objConn)
'3. Create parameters
Dim paramUsername as SqlParameter
paramUsername = New SqlParameter("@Username", SqlDbType.VarChar, 25)
paramUsername.Value = txtUsername.Text
objCmd.Parameters.Add(paramUsername)
Dim paramPwd as SqlParameter
paramPwd = New SqlParameter("@Password", SqlDbType.Binary, 16)
paramPwd.Value = hashedBytes
objCmd.Parameters.Add(paramPwd)
'Insert the records into the database
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
I am using a simple stored proc shown below which inserts a record and updates the same record field called SortID with the Primary Key Column data of the same record.Everything was working fine until few days back. The site has been deployed 2 years back and was LIVE ever since.
We have got thousands of records in this Credits table. The table field called SortID is used for moving the credits up and down.
My problem is now after 2 years of deploying the table has got duplicate records, suprisingly a same credit is appearing under different MemberID and with the same SortID.
How are these duplicate records inserted with a simple insert statement & update ??
Is this a Locking problem or Transaction problem I have no idea.
Any ideas will be of great help
Thanks in Advance
Stored Procedure Used
CREATE PROC SP_SC_INSERT (@memberID int,@title varchar(30),@dir varchar(30),@desc varchar(20))ASINSERT INTO [dbo].[Credits]([MemberID],[Title],[Director], [Description], )VALUES(@memberID,@title,@dir, @desc, )UPDATE Credits Set SortID = @@IDENTITY WHERE CreditID = @@IDENTITYGO
I have 2 tables in a 1: n relation. How can i get a select statement that the field in the n-relation with outputs, separated by a semicolon; Example: One person have many Job Titles
Table1 (tblPerson)
Table2 (tblTitles)
1, "John", "Miller", "Employee; Admin; Consultant"
2, "Joan", "Stevens", "Employee, Software Engineer, Consultant"
and so on .... 1 in select statement:
My example data as this:
ID code1 code2
1 a m
1 b n
1 c T
2 d m
2 e n
2 f n
3 g m
3 h n
3 i T
I would like to have a select stmt that check for all same ID and code2 does not contain T, then return the records
For example data: the query should return
2 d m
2 e n
2 f n
Is it possible to do this?
I want to update records in 1 table with the result of a select statement.
The table is called 'MPR_Portfolio_Transactions' and contains the following fields:
[PTR_SEQUENCE]
,[PTR_DATE]
,[PTR_SYMBOL]
,[PTR_QUANTITY]
,[PTR_ACUM]
And the select statement is like this:
SELECT SUM(PTR_QUANTITY) OVER (PARTITION BY PTR_SYMBOL ORDER BY PTR_DATE, PTR_SEQUENCE) AS 'ACUMULADO'
FROM MPR_portfolio_transactions
ORDER BY PTR_SYMBOL, PTR_DATE, PTR_SEQUENCE
This select statement generates one line per existing record. And what I would like to do next is to UPDATE the field 'PTR_ACUM' with the result of the 'ACUMULADO'
the key is PTR_SEQUENCE
I have a query that brings back the data below. I need to divide the BudgetTotal by the Count. Then I need to go to the records that make up those €œgroups€? and enter a Budget value = BudgetTotal/Count.
How could I write this in a stored procedure or a SQL statement if possible?
Thanks.
Kevin
SELECT TOP 100 PERCENT dbo.ReportTable.ProjectNo, dbo.ReportTable.Category, dbo.ReportTable.Type, COUNT(dbo.ReportTable.ProjectNo) AS count,
dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget AS Expr1
FROM dbo.ReportTable INNER JOIN
dbo.OracleDownloadBudget ON dbo.ReportTable.Category = dbo.OracleDownloadBudget.Category AND
dbo.ReportTable.ProjectNo = dbo.OracleDownloadBudget.Project AND dbo.ReportTable.Type = dbo.OracleDownloadBudget.Type
GROUP BY dbo.ReportTable.ProjectNo, dbo.ReportTable.ProjectName, dbo.ReportTable.Category, dbo.ReportTable.Type, dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget
HAVING (dbo.ReportTable.Budget < 1)
ORDER BY dbo.ReportTable.ProjectNo
ProjectNo
Category
Type
Count
Budget
BudgetTotal
100143
Travel
Travel, Meals, No Report IRS
2
0
300.27
100146
Travel
Travel Costs, Training (all)
1
0
300.27
100164
Supplies & Materials
Supplies, Educational
1
0
300.27
100167
Equipment
Eq NonCapital Desktop Comp
1
0
300.27
100170
Faculty Salaries
FB, Faculty
11
0
300.27
100170
Faculty Salaries
Salary, Faculty, T&R FT
11
0
300.27
100170
Wages
Wages, Student
2
0
300.27
100171
Faculty Salaries
FB, Faculty
19
0
300.27
100171
Faculty Salaries
Salary, Faculty, T&R FT
19
0
300.27
100176
Scholarships & Fellowships
Fell, Assist, Out, Grad
1
0
300.27
100177
Scholarships & Fellowships
Fell, Assist, In, Grad
1
0
300.27
I have this update statement that works for one record. How do I write it to include multiple records at once. Please see sample below.
update
mklopt
set
 FRMDAT =
'12/31/2014'
where
 JOBCOD =
'PH14789'Â
I also want to include the following instead of running it one at a time
PH17523Â Â Â Â
PH17524Â Â Â Â
PH17525Â Â Â Â
PH17553Â Â Â Â
PH17555Â Â Â Â
PH17556Â Â Â Â
PH17557Â Â Â Â
PH17558Â Â Â Â
PH17571Â Â Â Â
PH17573Â Â Â Â
PH17574Â Â Â Â
PH17575Â Â Â Â
PH17576Â Â Â Â
PH17577Â Â Â Â
PH1757
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