Number Of Rows.

Jun 20, 2008

Hi,
I have the following issue:

Here is my query:
The result is below:
(SELECT 'Succesful' AS metric_value,
SUM(CASE WHEN Product_Cat_Tier_3__2_ IN ('S Infr', 'S Entitl', 'S Provi') THEN 1 ELSE 0 end) AS S,
SUM(CASE WHEN Product_Cat_Tier_3__2_ IN ('Ading', 'Bending Floor', 'Net Ops' ) THEN 1 ELSE 0 end) AS N,
SUM(CASE WHEN Product_Cat_Tier_3__2_ IN ('Management NET', 'erprise') THEN 1 ELSE 0 end) AS ENTERP,
SUM(CASE WHEN Product_Cat_Tier_3__2_ LIKE '%Sector%' THEN 1 ELSE 0 end) AS TS,
SUM(CASE WHEN Product_Cat_Tier_3__2_ LIKE '%NMS%' THEN 1 ELSE 0 end) AS MS,
SUM(CASE WHEN Product_Cat_Tier_3__2_ = 'Fading' ) AR TS,
month(DATEADD(second, Actual_End_Date, '1969-12-31 8:00:00 PM')) as month
FROM Change

WHERE YEAR(DATEADD(second, Actual_End_Date, '1969-12-31 8:00:00 PM')) = YEAR(GETDATE()) AND

Product_Cat_Tier_1_2_ = 'Network' AND
Product_Cat_Tier_2__2_ = 'RFC' AND (Change_Request_Status = 11 and Status_Reason = 6000) AND
Product_Cat_Tier_3__2_ NOT IN ('Installation', 'Voice AND Video')
GROUP BY YEAR(DATEADD(second, Actual_End_Date, '1969-12-31 8:00:00 PM')), month(DATEADD(second, Actual_End_Date, '1969-12-31 8:00:00 PM'))
)

=====================
Result
================
metric_value S N ENTERP TS MS AR month
------------ -- -- ------ -- -- -- ----
Succesful 0 1 0 0 0 0 5
Succesful 0 1 1 0 0 0 6

And I want the following output, because now is June so month is equal to 6.
metric_value S N ENTERP TS MS AR month
------------ -- -- ------ -- -- -- ----
Succesful N N N N N N 1
Succesful N N N N N N 2
Succesful N N N N N N 3
Succesful N N N N N N 4
Succesful 0 1 1 0 0 0 5
Succesful 0 1 1 0 0 0 6

WHERE , N = NULL
THANK YOU

View 1 Replies


ADVERTISEMENT

How To Enter More Number Of Rows In A Table Having More Number Of Columns At A Time

Sep 24, 2007

Hi

I want to enter rows into a table having more number of columns

For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?

Suppose i am having my data in .txt file (or ) in .xls

( SQL Server 2005)

View 1 Replies View Related

Getting Number Of Rows

Sep 16, 2006

Is there a simple way to get the number of rows of a table besides going through and counting all of the rows programmatically?

View 13 Replies View Related

Number Rows

Mar 14, 2007

I have wrote a query that returns product details for all orders that are at stage 6. Is there a way to number the product lines in each order no?!

Eg.
Order 1
Product 1 - 1
Product 2 - 2
Product 3 - 3

--counter restarts
Order 2
Product 1 - 1
Product 2 -2

thanks?

View 2 Replies View Related

Number Of Rows

Dec 27, 2006

Hello!

I try to figure out how to change the number of rows at each page, but I'm stuck.

I have no page break , but I get a number of pages when I view my report. Is there any way to set the number of rows shown at each page?

//Cissi

View 4 Replies View Related

Sql Help With Number Of Rows Return Value

May 14, 2008

hi,i have a stored procedure like this in SQL server ,it returns proper value if data is there for a given id.But if there is no data,it returns row/rows of NULL value and that is counted towards "number of row returned"..Shouldn't it be like,if there are null values in a row,that row should not be counted towards rows returned value .?Rightnow if no value returned from either of the select,it still returns as 2 rows instead of 0 rows.How do handle this situation in SQL? thanks for your help
SELECT     SUM(col1) AS SUM_COL1, SUM(col2) AS SUM_COL2, SUM(col3) AS SUM_COL3, SUM(col4) AS SUM_COL4FROM         TABLE1WHERE     (ID = nn)     UNION all
 SELECT      SUM(col22) AS SUM_COL22 ,cast(null as int) as c1,cast(null as int)as c2,cast(null as int) as c3FROM         table2WHERE     TABLE2 = nn)

View 1 Replies View Related

Set Number Of Returned Rows !!! Please Help Me.

Jun 3, 2004

I want to select data from a table with 5.000.000 rows. It's very slowly. Do you now, how I can select only a XY number of rows? I can't use TOP in select query. User see only 20-30 rows on his page, but he can use page_up, page_down. Is possible to something as lazy load?

View 2 Replies View Related

Number Of Rows Returned By Sp

Jul 9, 2004

Is it possible to get the number of Rows returned by a stored procedure in a profiler ?
if yes, what parameters shud I be looking for?

THanks

Harshal

View 7 Replies View Related

Count Number Of Rows In Asp.net

Nov 11, 2005

hey, how would i count the number of rows, with out using a loop??

thanks, Justin

View 2 Replies View Related

Get Number Of Rows Deleted

Feb 1, 2006

How do we get the number of rows deleted from a table?

@@rowcount is returning 0

View 2 Replies View Related

Discrepancy On Number Of Rows

Sep 18, 2006

Hi There,

Good Day :-)

How could I correct the erroneous value on the property window of an SQL Table.

My problem is that, if I am going to display the property window of Table1, the 'Rows' information displays 115. However, if I am going to execute - Select Count(*) from Table1 then it returns 117. How could I fix this glitch?

Please Help :-)

View 2 Replies View Related

Return Certain Number Of Rows

Apr 17, 2008

I have a Dataset that I am populating from a SQL Query. I am then using the dataset to populate a report in Reporting Services. What I want to do is return a standard number of rows in my dataset. (Let's say 10.) Even if my query does not have any rows in it, I want 10 empty rows returned to the dataset. If my query has 7 rows in it then I want to add on 3 empty rows and return it. I will not have more than the standard number of rows.
I cannot get the table in the report to show up if the dataset is empty, but still want the table to display with 10 empty rows. I have searched how to do this online but am getting nowhere. (I know how to add one empty row but not a set number.

View 6 Replies View Related

Selecting Top 'x' Number Of Rows Through A Sp

Jun 24, 2008

Hello all.

Im trying to write a stored procedure which i can stipulate the top number of rows it returns. I know i use the following syntax:

SELECT TOP 5

And i know i need to pass the number of rows i want as a variable to the stored proceudre like:


@Top as Integer

But when i try and do the following. SQL Server complains about the syntax:

SELECT TOP @Top

Can anyone advise me how i should be doing this?

View 3 Replies View Related

Selecting A Specified Number Of Rows

Jul 30, 2007

I know I can use "First" to specify a number of rows to return from a query but is it possible for the number of rows returned to be based on a parameter, something like this:

SELECT FIRST @someNumber name, age
FROM friends
WHERE age > @ageInput

View 3 Replies View Related

Number Of Rows In A Table

Nov 18, 2007

HI!
1.select count(*) from sales
2.select rows from sysindexes where id=object_id('sales') and indid<2

both queries return number of rows.
can anyone tell me which one is better?

View 2 Replies View Related

Very Large Number Of Rows

Jul 23, 2005

We are busy designing a generic analytical system at work that willhold multiple analytic types over time. This system is being developedin SQL 2000.Example of tableIDENTITY intItemId int [PK]AnalyticType int [PK]AnalyticDate DateTime [PK]Value numeric(28,15)ItemId - the item for which the analytic is being storedAnalyticType - an arbitrary typeThe [PK] tag indicates the composite primary key.Our scenario is the following:* For this time series data, we expect around 250 days per year(working days) and the dataset could extend to over 20 years* Up to 50 analytic types* Up to 20,000 itemsLooking at the combined calculation - this comes to roughly somethinglike25 * 20,000 * 50 * 250 or around 5 billion rows.We will be inserting around 50*20,000 or around 1 million rows each day(the inserts will take place in the middle of the night (outside themain query time) - this could be done through something like BCP orBULK INSERT.Our real problem is we have not previously worked with such largetables before and are nervous that our system is going to grind to ahalt. Our biggest tables are around 20 million rows at the moment.Scanning through google and microsoft's own site we have found aparititioning method that is available.http://www.microsoft.com/resources/...art5/c1861.mspxHaving experimented with the above system it seems rather quirky andlooking at the available literature it seems that this is not moreeffective than a clustered index as far as queries go.It needs to be optimized for queries like:Given the ItemID and the AnalyticType search for a specific date or aspecific range of dates.If anyone has any experience or helpful suggestions I would reallyappreciate it.ThanksA

View 4 Replies View Related

How To Count Number Of Rows

Jul 20, 2005

Hello, DeanTry this:select distinct c1, c2 into #tmp_1 from t1select count(*) as cnt from #tmp_1drop table #tmp_1With best regards.

View 1 Replies View Related

Large Number Of Rows.

Apr 3, 2007

Hi all,



A select query returns around 1 million rows. The column in the WHERE condition is indexed. This query takes nearly 1 minute for returning the all the records. Is this normal ?



Does the number of records returned affect the performance inspite of the indexing ?



Thanks,



DBLearner

View 3 Replies View Related

Get Number Of Rows Returned

Oct 18, 2007

I have a report that calls a stored procedure. I want to display the number of records that the stored procedure returned. I can't seem to find an expression that will do this. Is there an expression that will display the record count for a dataset?

Thanks,
Rob

View 1 Replies View Related

How To Get The Value Of Number Of Rows In Groupfooter

Jan 9, 2007

I want to show how many records populated in the groupby clause, i want to show the number of rows value in the group footer.

Thank you very much.

View 1 Replies View Related

What Is The Easiest Way To Get The Number Of Rows In A Table?

Jul 24, 2007

With classic asp I would do something like this:
set conn = server.createobject("ADODB.Connection")
conn.open "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;PWD=password;DATABASE=databasename "set rs = conn.execute("select count(*) from myTable")response.write rs(0)How can I do this with asp.net in the fewest number of lines?  

View 3 Replies View Related

ExecuteNonQuery To Count Number Of Rows??

Sep 5, 2007

My understanding from a previous thread was that ExecuteNonQuery() could be used to display the number of rows returned.
Does this also work when calling stored procedures and passing parameters?
I have code (shown) that perfectly calls and returns Distinct models downloaded by Country. Yet the rowCount variable displays a -1.
What should I do?Dim myCommand As New SqlClient.SqlCommand
myCommand.CommandText = "ap_Select_ModelRequests_RequestDateTime"
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.AddWithValue("@selectDate", dateEntered)
myCommand.Parameters.AddWithValue("@selectCountry", CInt(selectCountry))
myCommand.Connection = con
con.Open()
Dim rowCount As Integer = myCommand.ExecuteNonQuery()
numberParts.Text = rowCount.ToString
con.Close() Thank you.

View 6 Replies View Related

Count The Number Of Rows Returned - How??

Oct 13, 2007

hello,
i have a stored procedure SELECT CommentID, UserName, CommentingDate
FROM Comm
WHERE PictureID = @PictureID
ORDER BY CommentingDate DESC
 witch shows me the users who commented a Picture with PictureID = x
I need to add two rows at that stored procedure, one to show the number of total comments at that picutre (like counting the number of rows returned) and the second to show count the DISTINCT users who commented that picture
I tryied with COUNT but i have to use GROUP BY and i don't think this is good...
I hope you understand... please help me,
thanks

View 5 Replies View Related

Return Number Of Rows From A Table

Dec 9, 2007

Im am trying to return the number of rows in a table and i only can get a response of true thanks for any help 

View 3 Replies View Related

How Do You Delete (x) Number Of Rows From Database

Jan 10, 2008

I am setting up a database which schedules production and tracks inventory of items on a daily basis.  The scheduler may put in 100 identical entries (apart from the identity column) of an item with its corresponding quantity.  My problem is, if there is a shipment of product (a subtraction of quantity from the database), how can I delete a specified number of rows where the inventory listing is 100,000 pcs?  I think the DELETE TOP(r) command will work but I don't know how make the command into an actual variable.  Maybe there is another way too...
My current not-working try;  I look at the product desired to delete, figure out how many rows to delete, and since it is not always an integer, figure out a quantity to add back in.  The addition part works fine but delete command needs work.  Any help is appreciated.
    int InvRows = 0;    decimal RealInvRows = 0;    decimal AddQty = 0;    int preAddAmount = 0; protected void DelInv_Click(object sender, EventArgs e)    {        Label TotProdSum = (Label)DetailsView2.FindControl("TotProdSum");        Label RowQty = (Label)DetailsView3.FindControl("RowQty");        int SubQty = Convert.ToInt32(ShipQty.Text);        InvRows = SubQty / Convert.ToInt32(RowQty.Text) + 1;        RealInvRows = SubQty / Convert.ToDecimal(RowQty.Text);        AddQty = (InvRows - RealInvRows) * Convert.ToInt32(RowQty.Text);        IntLbl.Text = Convert.ToString(InvRows);        RealLbl.Text = Convert.ToString(RealInvRows);        preAddAmount = Convert.ToInt32(AddQty);        AddAmount.Text = Convert.ToString(preAddAmount);                for (int r = 0; r <= InvRows; r++)        {            forWhile.DeleteCommand = "DELETE TOP (r) FROM Inventory WHERE (Inventory = @Inventory)";            forWhile.DeleteParameters.Add("Inventory", RowQty.Text);            forWhile.Delete();            forWhile.DeleteParameters.Clear();        }        forWhile.InsertCommand = "INSERT INTO Inventory(Dte, Product, Inventory) VALUES (@Dte, @Product, @Inventory)";        forWhile.InsertParameters.Add("Inventory", AddAmount.Text);        forWhile.InsertParameters.Add("Product", InvProdDDL.Text);        forWhile.InsertParameters.Add("Dte", Date.Text);        forWhile.Insert();        forWhile.InsertParameters.Clear();    } 

View 1 Replies View Related

Pull A Set Number Of Rows Per Instructor

May 27, 2008

I am trying to get 10 appointment dates for people. I can pull the data but im pulling all appointments. I need to find a way to just pull 10 appointments per instructor, as I have multiple instructors. Here is what I have.
 DECLARE @STARTDATE AS DATETIME DECLARE @ENDDATE AS DATETIME
SET @STARTDATE = DATEADD(m, DATEDIFF(m,0,GETDATE())+1, 0)
SET @ENDDATE = DATEADD(m, DATEDIFF(m, 0, DATEADD(m, 1, GETDATE()))+1, -1)
SELECT INSTRUCTOR
STUDENT_NAME,
STUDENT_NUM,
CONVERT(CHAR(10),[DATE],101) AS Appt_Date,
APPOINTMENT_TIME,
TEL,ADDRESS1,
ISNULL(ADDRESS2,'') AS ADDRESS2CITYST, ZIP
 
FROM APPTS
WHERE [DATE] BETWEEN @STARTDATE AND @ENDDATE
 

View 2 Replies View Related

Select Fixed Number Of Rows

Jun 14, 2004

A table has a column of int type. I need to select a fixed number of rows for each value. For example, if data in that column (c) are 5, 6, 7, and the number I want to select is 2, then I need 2 rows from c=5, 2 from c=6, and 2 from c=7. How to write that query? Any idea?

Thanks.

View 6 Replies View Related

Getting The Number Pf Rows In A Query Result

Sep 1, 2004

My site have a complicated search, the search give the results in two stages- the first one giving the number of results in each section:

"In the forums there is X results for the word X
In the articles there is X results...."

And when the user click one of those lines, the list shows the specific results in that section.

My problem is that I don't know how to calculate the first part, for now I use dataset, and table.rows.count to show the number of results in each section. Since my site have more then ten, it looks like a great waste to fill such large dataset (in some words it can be thousands of rows in each section) only for getting the number of rows…

Are there is a sql procedure or key word that will give me only the number of results (the number of times that specific word showing in the columns?)

Great thanks

View 1 Replies View Related

Count The Number Of Rows Selected

May 27, 2005

This is my SQL :
Select p.patientid,p.patientname,p.patientIc,pvi.DateOfAdmission,pvi.visitid,pvi.ward,pvi.bedno,pf.status,pvi.SurgeonName,(f.Title + ' ( Ver ' + (CAST(f.Version as Char(10))) + ')') as Title FROM patient p, patientvarianceinfo pvi,patientForm pf,Form f where (p.PatientName LIKE '%" & Name & "%' or p.PatientIc LIKE '%" & ic & "%' or pvi.Ward LIKE '%" & ward & "%' or pvi.Bedno LIKE '%" & bed & "%') and (p.patientid = pvi.patientid) and (p.patientid = pf.patientid) and (pvi.patientid = pf.patientid) and (pf.FormID = f.FormID)and p.patientid in (select patientid from patientform pf)how do i get the number of rows?

View 1 Replies View Related

Select A Specific Number Of Rows

Aug 25, 2005

I am working on a multi-page datagrid that pulls data from a database. The issue I am running into is the SQL select query. What I have is a table to 55 items. What I need to do is grab the first 35 of those items and bind the resulting DataReader to the grid, print the page, then grab the remaining 20 items, bind to the grid and print the page.

I can use "SELECT TOP 35 FROM Table" to get the first 35 items, but I don't know how to get the remaining 20 items. Is there a way to say something like "SELECT TOP 20 FROM Table" but specify only the rows that begin after row 35?

I tried doing this with an ArrayList but couldn't bind it to the DataGrid.

Any suggestions?

View 10 Replies View Related

How To Find The Number Of Rows In A Table

May 12, 2006

I try to find the number of rows in a table with this commands:
CountRec = New SqlParameterCountRec.ParameterName = "@countrec"CountRec.SqlDbType = SqlDbType.IntCountRec.Value = 0MyCommand = New Data.SqlClient.SqlCommand()MyCommand.CommandText = "select count(*) as @countrec from Customer;"MyCommand.CommandType = Data.CommandType.TextMyCommand.Connection = MyConnectionMyCommand.Parameters.Add(CountRec)MyCommand.Connection.Open()MyReader = MyCommand.ExecuteReaderiRecordCount = CountRec.Value
This is the result:
Incorrect syntax near '@countrec'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '@countrec'.Source Error:




Line 39:
Line 40: MyCommand.Connection.Open()
Line 41: MyReader = MyCommand.ExecuteReader
Line 42: iRecordCount = CountRec.Value
Line 43:
Source File: E:DevelopWebASPwebAccessTimberSalesUserEntry.aspx.vb    Line: 41
What to do? I need a complete example to see how it works.
Thanks...

View 3 Replies View Related

Inserting Large Number Of Rows

Nov 24, 1999

Hi,

I need to insert a very large number of rows into a table (in SQL Server 7.0) using ADO.
Could you please tell me i there is a way for FAST insert, something similar to BCP ... or any other way of
inserting large number of rows efficiently

Thanks

View 2 Replies View Related

Number Of Rows In A Select Stmt

Aug 2, 2004

hey all,

I am writing a sproc:

select @strCourseNameRegFor = sal.CourseName , @strSectionNoRegFor = sal.SectionNo,
@dteStartDateRegFor = sal.StartDate, @dteEndDateRegFor = sal.EndDate,
@dteStartTimeRegFor = sal.StartTime, @dteEndTimeRegFor = sal.EndTime,
@strDaysOfWeekRegFor = sal.DaysOfWeek
from lars.dbo.tblSalesCourse as sal, lars.dbo.tblCourseCatalog as cat
where sal.SchoolYr = @intRegForYear and rtrim(sal.SchoolTerm) = rtrim(@strRegForTerm) and upper(rtrim(sal.CourseName)) = upper(rtrim(@strCourseNamePrev))
and cat.NewStuAllowed = 0 and sal.CourseName = cat.CourseName and sal.Cancelled <> 1 and cat.SchoolYr = sal.SchoolYr
and sal.MaxNoStudents > sal.CurrNoStudents

I want to check if the select returned an empty set or not. I cannot use @@rowcount because i am assigning the values to the local vars. I tried

if @strCourseNameRegFor is null
begin
set @err = 'No courses';
end

but for some reason even if there are any records in the set, the if condition is getting satisfied. Can anyone help?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved