How Do I Put Records Number In Union

Dec 21, 2007



Hi i have sql statement like this :

SELECT row_number() over (ORDER by a.empid) as rec_num, empname
FROM employee_a
UNION
SELECT row_number() over (ORDER by a.empid) as rec_num, empname
FROM employee_b

the problem is the rec_num repeat for each statement like this :




rec_num empname

1 john
2 maggy
1 lee
2 mary
3 louis

How do i make the rec_num continue for the next statement after union.

View 1 Replies


ADVERTISEMENT

Using Top To Get Set Number Of Results From A Union

Jan 30, 2008



I would like to get the top 5000 records from a union. The oringinal query is

SELECT * from tbl_A WHERE fld_Id = 0
UNION ALL
SELECT * from tbl_B where fld_Id = 0

I thought that the I could do:
SELECT TOP 5000 * FROM
(

SELECT * from tbl_A WHERE fld_Id = 0
UNION ALL
SELECT * from tbl_B where fld_Id = 0
}

This gives me a syntax error that I cannot figure out.

Any ideas?

View 3 Replies View Related

Union Of 2 Tables With Differnt Number Of Rows

May 24, 2006

I have 2 tables

tblOpenSiteDates
Site: int
OpenDate: smalldate
Comment: VarChar


tblCloseSiteDates
Site: int
CloseDate: smalldate
Comments: VarChar
newLocationID: int

I am trying to get a view which would display a site with the open and close dates. Null is ok for a close date for those opens that are still open.. not every office has an open date and close date.. it is possible to have just a close date and not an open date (ie unsure of open date but I know its closing)

so the output would be

viewOpenCloseSites
Site: int
opendate: smalldate
closedate: smalldate

PLEASE HELP i just can not figure it out

thanks...

View 1 Replies View Related

Query With Row Number,Union And Dynamic Orderby.

Dec 19, 2007

Hi,
Can some one help me to complete this query.
My requirement is to convert the existing stored proc(given below) to one that allows dynamic paging as well as sorting(sql server 2005). I gave a try but invein. I am running out of time.

Existing procedure:


ALTER PROCEDURE [dbo].[spGetAllItems]
@condition varchar(7500),
@ModelId int =0,
@CategoryId int=0,
@SourcingType int=0,
@StartRow as int = 1,
@StopRow as int = 100000
AS
declare @erow int
declare @Cond varchar(255)

select @Cond=''

if @CategoryId<>0
select @Cond= ' and tbItemMaster.Category=' + cast(@CategoryId as varchar)
if @SourcingType<>0
select @Cond= ' and tbItemMaster.SourcingType=' + cast(@SourcingType as varchar)

if @StopRow=-1
select @StopRow=60
set @erow=@StartRow + @StopRow


exec('Declare @t_table table
(
[SpecialId] [int] IDENTITY (1, 1) Primary key NOT NULL,
[id] [int],
[category] [int] NULL ,
[model] [int] NULL ,
[mtype] [varchar] (50) NULL,
[itemclass] [varchar] (50) NULL,
[itemcode] [nvarchar] (25) NULL ,
[ItemCondition] [tinyint] NULL ,
[SourcingType] int,
[Title] [nvarchar] (50) NULL ,
[Description] [nvarchar] (4000) NULL ,
[InternalUse] [nvarchar] (4000) NULL,
[CompatibleModels] [varchar] (8000) NULL ,
[Quantity] [int] NULL ,
[MinOrder] [int] NULL ,
[Warranty] [tinyint] NULL ,
[Price1] [decimal](6, 2) NULL ,
[Price2] [decimal](6, 2) NULL ,
[Price3] [decimal](6, 2) NULL ,
[Price4] [decimal](6, 2) NULL ,
[LotSale] [bit],
[PricingRule] int ,
[Weight] [decimal](7, 2) NULL ,
[PackageSize] [int] NULL ,
[promo] [int] NULL ,
[SmallImage] [nvarchar] (255) NULL ,
[BigImage] [nvarchar] (255) NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Info1] [nvarchar] (50) NULL ,
[Info2] [nvarchar] (50) NULL ,
[Info3] [nvarchar] (50) NULL ,
[Info4] [nvarchar] (50) NULL ,
[Info5] [nvarchar] (50) NULL ,
[Info6] [nvarchar] (50) NULL ,
[Info7] [nvarchar] (50) NULL ,
[Info8] [nvarchar] (50) NULL ,
[Info9] [nvarchar] (50) NULL ,
[Info10] [nvarchar] (50) NULL ,
[Info11] [nvarchar] (50) NULL ,
[Info12] [nvarchar] (50) NULL ,
[Info13] [nvarchar] (50) NULL ,
[Info14] [nvarchar] (50) NULL ,
[Info15] [nvarchar] (50) NULL ,
[Info16] [nvarchar] (50) NULL ,
[Info17] [nvarchar] (50) NULL ,
[Info18] [nvarchar] (50) NULL ,
[Info19] [nvarchar] (50) NULL ,
[Info20] [nvarchar] (50) NULL ,
[Info21] [nvarchar] (50) NULL ,
[Info22] [nvarchar] (50) NULL ,
[Info23] [nvarchar] (50) NULL ,
[Info24] [nvarchar] (50) NULL ,
[Info25] [nvarchar] (50) NULL ,
[Status] [tinyint] NULL ,
[AllowBuy] [char] (1) NULL ,
[PageName] [varchar] (200) NULL ,
[Locality] [int] NULL ,
[Location] [int] NULL ,
[CreatedBy] [int] NULL ,
[CreatedOn] [datetime] NULL ,
[UpdatedBy] [int] NULL ,
[UpdatedOn] [datetime] NULL,
[BrandId] int,
[CategoryId] int,
[ModelId] int,
[ModelName] varchar(255),
[BrandName] varchar(255),
[CategoryName] varchar(255)
);
DECLARE @EndRow int
DECLARE @reccount int


select @reccount=count(*) from (SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + '
Union
SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + ') tbl


set @EndRow= + ' + @StartRow + '+' + @StopRow + '
--Set RowCount @EndRow;
insert @t_table
(
[id] ,
[category] ,
[model] ,
[mtype],
[itemclass],
[itemcode] ,
[ItemCondition] ,
[SourcingType],
[Title] ,
[Description] ,
[InternalUse],
[CompatibleModels],
[Quantity] ,
[MinOrder] ,
[Warranty] ,
[Price1] ,
[Price2] ,
[Price3] ,
[Price4] ,
[LotSale] ,
[PricingRule],
[Weight] ,
[PackageSize] ,
[promo] ,
[SmallImage] ,
[BigImage] ,
[StartDate] ,
[EndDate] ,
[Info1] ,
[Info2] ,
[Info3] ,
[Info4] ,
[Info5] ,
[Info6] ,
[Info7] ,
[Info8] ,
[Info9] ,
[Info10] ,
[Info11] ,
[Info12] ,
[Info13] ,
[Info14] ,
[Info15] ,
[Info16] ,
[Info17] ,
[Info18] ,
[Info19] ,
[Info20] ,
[Info21] ,
[Info22] ,
[Info23] ,
[Info24] ,
[Info25] ,
[Status] ,
[AllowBuy] ,
[PageName] ,
[Locality] ,
[Location] ,
[CreatedBy] ,
[CreatedOn] ,
[UpdatedBy] ,
[UpdatedOn] ,
[BrandId] ,
[CategoryId] ,
[ModelId],
[ModelName],
[BrandName],
[CategoryName]
)
select * from (SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,

tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + ' Order By tbItemMaster.UpdatedOn desc) tbl
Union
SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,

tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + @Cond + ' Order By UpdatedOn desc;

SELECT *,@reccount as ItemsCount FROM @t_table WHERE SpecialId >=' + @StartRow + ' and SpecialId<@EndRow
ORDER BY UpdatedOn DESC
')


RETURN


can anybody help me pls. ITs very urgent.

Thanks in advance.

View 1 Replies View Related

Count The Number Of Rows In A UNION ALL Statement

Jan 8, 2007

Hi,Should be quite simple but can someone please tell me the best way tocount the number of rows in an UNION ALL statement.I tried using @@ROWCOUNT but that doesn't seem to contain the correctnumber.Also, I assume that running the query again but just returning count(*)instead of the data is horribly inefficient (plus the code is thenbloated.)?Thanks,Mark

View 1 Replies View Related

Error Using Union All With Row Number Transformation From SQLIS(.com)

May 14, 2008

Has anyone else experienced similar problems? I've been trying to use "Row number" from sqlis.com site with "Union All" but no lucks.

[DTS.Pipeline] Error: component "Union All" (1840) failed the pre-execute phase and returned error code 0x80070057.


thanks

View 6 Replies View Related

How To Number Records / Duplicates Receiving Same Number

Feb 19, 2013

I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.

View 8 Replies View Related

Union Parent Child Records

Feb 24, 2014

I have an application that has an existing query which returns org units (parent and child) from organization table with orderby on createddate + orgid combination.

Also I added another log table Organization_log with exact columns as Organization table and additional 'IS_DELETED' bool column.

WITH Org_TREE AS (
SELECT *, null as 'IS_DELETED', convert (varchar(4000), convert(varchar(30),CREATED_DT,126) + Org_Id) theorderby
FROM Organization WHERE PARENT_Org_ID IS NULL and case_ID='43333'

[code]...

I need to modify the query:

1. To display the records both from the Organization table and Organization_Log table.
2. The orderby should be sorted on 'Organization Name' asc and it should follow the child order in alpha sort as well.

E.g.:

aaa
==>fff
==>ggg
bbb
==> aaa
==> hhh
eee
==> ccc
==> ddd
==> fff

View 5 Replies View Related

SQL Union Problems When Trying To Retrieve Random Records

Jun 6, 2006

I have a situation where i am trying to run two query's and retrieveone record from each query, then using Union combine them into onerecordset. The First Query is strait forward and should just returnthe matching record however the second query needs to find a randomrecord and return it. The union is causing me some trouble. It seemsthat any method i try to find a random record just causes an error.Here is an example of a query that's causing the problem.--------------------------------------------------------------------------------------------------------declare @variable1 smallintdeclare @variable2 intset @variable1 = 10002set @variable2 = 1001211720select col1, col2, col3, col4, col5, col6from table1where col1 = @variable1 and col2 = @variable2unionselect col1, col2, col3, col4, col5, col6from table2where col2 = @variable2order by newID()--------------------------------------------------------------------------------------------------------I have also tried the following which does not provide an error but icannot find a way only to return one record from the second query--------------------------------------------------------------------------------------------------------declare @variable1 smallintdeclare @variable2 intset @variable1 = 10002set @variable2 = 1001211720select newID() as colID, col1, col2, col3, col4, col5, col6from table1where col1 = @variable1 and col2 = @variable2unionselect newID() as colID, col1, col2, col3, col4, col5, col6from table2where col2 = @variable2order by colIDIf anyone has any ideas, fixes, or thoughts i would appreciate them...thanks in advance...FYI i am fairly inexperienced in SQL so please feel free to let me knowif you need more of an explination.

View 9 Replies View Related

Duplicates Again! UNION Join - Remove Records With Column Diff.

Sep 9, 2004

Hello All,

We all were new at one point.... any help is appreciated.

Objective:

Combining two 49,000 row tables and remove records where there is only 1 column difference. (keeping the specified column value removing the one with a blank.)

Reason:

I have 2 people going through a list, coding a specific column with a single letter value. They both have different progress on each sheet. Hence I am trying to UNION them and have a result of their combined efforts without duplicates.

My progress/where I'm stuck:

Here is my first query/union:

SELECT * FROM [Eds table]
UNION SELECT * FROM [Vickis table];

As shown above, I have unioned these 2 tables and my results removed th obvious whole record duplicates, but since 1 column is different on these, a union without criteria considers them unique.....

an example of duplicates that I must remove are as follows:

142301 - Product 5000 - 150# - S (Keep)
142031 - Product 5000 - 150# - "" <--- Blank (Remove)

I am trying to run another query on my first query results so I don't mess my first query up. Here it is:

SELECT DISTINCT [Prod #], [Prod Name], [Prod Description], [Product Type]
FROM [Combined Tables]
WHERE [Product Type]<>" ";


Please Help! Thank you in advance.

--------------------

5 minutes away from pulling my last one!

BaldNAskewed

View 7 Replies View Related

Page Number &&amp; Records Number

Jun 20, 2006

1. how to show page number & total page number in report body?

2. how to show total records number?

View 25 Replies View Related

Group / Union Statement - Pull Unique Records From A Large Table

Sep 22, 2014

I am trying to use SQL to pull unique records from a large table. The table consists of people with in and out dates. Some people have duplicate entries with the same IN and OUT dates, others have duplicate IN dates but sometimes are missing an OUT date, and some don’t have an IN date but have an OUT date.

What I need to do is pull a report of all Unique Names with Unique IN and OUT dates (and not pull duplicate IN and OUT dates based on the Name).

I have tried 2 statements:

#1:
SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#))
GROUP BY tblTable1.Name, tblTable1.INDate
ORDER BY tblTable1.Name;

#2:
SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#))
UNION SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#));

Both of these work great… until I the OUT date. Once it starts to pull the outdate, it also pulls all those who have a duplicate IN date but the OUT date is missing.

Example:

NameINOUT
John Smith1/1/20141/2/2014
John Smith1/1/2014(blank)

I am very new to SQL and I am pretty sure I am missing something very simple… Is there a statement that can filter to ensure no duplicates appear on the query?

View 1 Replies View Related

Specify The Number Of Records

Apr 26, 2001

Hi,
I want to the users to specify the number of TOPn records to be returned by the query.It has to be a query and not a stored proceedure.
Thanx

View 3 Replies View Related

How To Get Each Number Of Records...

Dec 2, 2006

HI EVERYBODY
This is my procedure
"
CREATE PROCEDURE SP_SAMPLE_SEARCH
@Title nvarchar(256)
AS

SELECT ID,Title,Price FROM [tbl_Sim] WHERE ([Title] LIKE '%' + @Title + '%') Order by Price desc
GO
"
I exec procedure and it returns 12 results with diffirents ID
and want to get these values 1,2,3....12
How do I get these...
I am a beginner.
Thanks for help..

View 6 Replies View Related

Get TOP Number Of Records

May 29, 2015

I'm using SQLCE. I'm using this sql statement:

sSQL = "Select Top (5) fldStatus,
Count(fldStatus) As CountOfSummaryItem From tbl_Records_DR GROUP BY
fldStatus;"

It gets the top 5 of fldStatus but I need the top 5 of CountOfSummaryItem.

View 2 Replies View Related

Number Of Affected Records

Feb 12, 2007

Okay..i have this problem ...i am using SQL server 2005 standard ,C#,VS2005
--i am inserting some record in DB .. using ExecuteNonQuery...i want to know how many records are getting inserted..so in my DB class i did something like this : numRecords = commandObject.ExecuteNonQuery() ,assuming  that ExecuteNonQuery returns the number of affected records.i am retriving this numOfRecords in my code behind and printing it but it always prints 1,even though more then one records are inserted.What is wrong here?
-i also have returnValue defiend like this.Could this tell me anything about how many records are inserted or affected during update,select ?if so,how?
cmd.Parameters.Add(new SqlParameter("@returnVal", SqlDbType.Int));
cmd.Parameters["@returnVal"].Direction = ParameterDirection.ReturnValue;
Please help me out with this.Thanks

View 14 Replies View Related

How To Count Number Of Records

Feb 26, 2008

hi
can anyone tell me how to count number of records(rows) in a table without using "COUNT" function.for practise iam trying to implement it through queries.

View 6 Replies View Related

Count Number Of Records

May 13, 2008

Using linq what would be the most efficient method of counting the number of users in the users table? 

View 2 Replies View Related

How Can I Now The RecordSource Number Of Records?

Dec 16, 2005

Hi...
I just begining to use the asp.net 2.0 and have tow littel problems... in my project the user makes a "list of problems on a house" when hi post the house number the page shoud to generete a master recod with a keynumber, the date an hour and the state of problems... and a form to insert a list of problems... but if the house dosn´t exist this forms are hidden... then i need to know the nomber of records that gets a recordsource if its more than cero show the forms else hiden it... if show the forms i need to store in a variable the keynumber to store it in the problem details table... ¿How i can khow the number of records of a RecordSource gets?... and ¿How I can store in a variable a field value retrived for a RecordSource?... I´m using VWD and SQL Express that means ADO.NET 2.0...
cheers.

View 1 Replies View Related

Total Number Of Records

Sep 27, 2001

Hi everbody,

I want find out Total number records in one table without using select statment.
Some body as told to me there is system table you can find total number of records. Any body give me systable name.
Thanks
Jack

View 2 Replies View Related

Total Number Of Records

Sep 27, 2001

Hi everbody,

I want find out Total number records in one table without using select statment.
Some body as told to me there is system table you can find total number of records. Any body give me systable name.
Thanks
Jack

View 1 Replies View Related

Getting Number Of Records Modified

Oct 20, 2004

Hi all

My manager ask me to provide him with the total number of records which have been added, deleted or modified on a certain database in SQL Server 2000 during the month of September. Is there away to get that information from the transaction log or by any how?

please some one guide me how to do that?

Your help is highly appreciated

View 8 Replies View Related

Selecting A Certain Number Of Records?

Apr 16, 2008

Hi,

Here is a basic example of the issue I am having:

Table 1 columns - name, address, zipcode, favorite food

For table 2 I want to find how many zipcodes exists and also take 20% of the count

Table 2 columns - based off Table 1 contains zipcode, count(zipcode) as ct, count(zipcode) * .20 as perc_ct

For example:

zipcode ct perc_ct
83746 10 2
93847 20 4

I want to run a query that will pull any 2 records for 83746 and any 4 records for 93847 from Table 1.

Is this possible?

View 1 Replies View Related

My Number Of Records Are Different From Views

Apr 20, 2008

Why is it my number of records are different from my view. this are the following code that that i used in my code and in my view.

my code in my program: this code show all the records i know

If iConn.State = ConnectionState.Open Then iConn.Close()
iConn.Open()
Dim rsBills As New Data.DataSet
rsBills = New DataSet
Dim daBills As New SqlDataAdapter
daBills = New SqlDataAdapter
rsBills.Clear()
Dim cmBills As New SqlCommand
cmBills = iConn.CreateCommand ' "Select U_Code, U_Name, U_Level, P_Word From EPassword ",
cmBills.CommandText = "Select OR_no, Billing_mo From dbo.vwBilling Where Month(dbo.vwBilling.Billing_mo)= " & Month(Me.dtto.Value) & " And Year(dbo.vwBilling.Billing_mo) = " & Year(Me.dtto.Value) & " ORDER BY OR_no "

daBills.SelectCommand = cmBills
rsBills.AcceptChanges()
rsBills.Clear()
daBills.Fill(rsBills, "Bills")
Me.DataGridView1.DataSource = Nothing
Me.DataGridView1.DataSource = rsBills.Tables("Bills")

my code in views:

SELECT OR_no, Billing_mo, Account_no, Name, Address, Cno, Pres_read, Mprev_read, Sub_read, Pres2, Prev2, SRead2, Mtr_cons, Amount, NPC, Dmem,
Cmem, Tot_bill, D_Pmnt, Class, Newbill, Prd_fr, Prd_to, Type_Pmnt, Type_Date, Type_Docs, wtax
FROM dbo.Billing


hope you can help me guys

View 1 Replies View Related

Limit On Number Of Records

Apr 21, 2008

is there a limitations of number of records that shows in the table when you use show data table?

View 1 Replies View Related

Sum Up An Unknown Number Of Records

Mar 19, 2007

With this algorithm you can sum up an unkown number of records, so that an aggregation matches a fixed value.
If there is not an exakt match available, the algorithm returns the nearest possible value!-- Initialize the search parameter
DECLARE@WantedValue INT

SET@WantedValue = 349

-- Stage the source data
DECLARE@Data TABLE
(
RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
MaxItems INT,
CurrentItems INT DEFAULT 0,
FaceValue INT,
BestUnder INT DEFAULT 0,
BestOver INT DEFAULT 1
)

-- Aggregate the source data
INSERT@Data
(
MaxItems,
FaceValue
)
SELECTCOUNT(*),
Qty
FROM(
SELECT 899 AS Qty UNION ALL
SELECT 100 UNION ALL
SELECT 95 UNION ALL
SELECT 50 UNION ALL
SELECT 55 UNION ALL
SELECT 40 UNION ALL
SELECT 5 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 50 UNION ALL
SELECT 250 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 90 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 50 UNION ALL
SELECT 350 UNION ALL
SELECT 450 UNION ALL
SELECT 450 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 50 UNION ALL
SELECT 50 UNION ALL
SELECT 50 UNION ALL
SELECT 1 UNION ALL
SELECT 10 UNION ALL
SELECT 1
) AS d
GROUP BYQty
ORDER BYQty DESC

-- Declare some control variables
DECLARE@CurrentSum INT,
@BestUnder INT,
@BestOver INT,
@RecID INT

-- If productsum is less than or equal to the wanted sum, select all items!
IF (SELECT SUM(MaxItems * FaceValue) FROM @Data) <= @WantedValue
BEGIN
SELECTMaxItems AS Items,
FaceValue
FROM@Data

RETURN
END

-- Delete all unworkable FaceValues
DELETE
FROM@Data
WHEREFaceValue > (SELECT MIN(FaceValue) FROM @Data WHERE FaceValue >= @WantedValue)

-- Update MaxItems to a proper value
UPDATE@Data
SETMaxItems =CASE
WHEN 1 + (@WantedValue - 1) / FaceValue < MaxItems THEN 1 + (@WantedValue - 1) / FaceValue
ELSE MaxItems
END

-- Update BestOver to a proper value
UPDATE@Data
SETBestOver = MaxItems

-- Initialize the control mechanism
SELECT@RecID = MIN(RecID),
@BestUnder = 0,
@BestOver = SUM(BestOver * FaceValue)
FROM@Data

-- Do the loop!
WHILE @RecID IS NOT NULL
BEGIN
-- Reset all "bits" not incremented
UPDATE@Data
SETCurrentItems = 0
WHERERecID < @RecID

-- Increment the current "bit"
UPDATE@Data
SETCurrentItems = CurrentItems + 1
WHERERecID = @RecID

-- Get the current sum
SELECT@CurrentSum = SUM(CurrentItems * FaceValue)
FROM@Data
WHERECurrentItems > 0

-- Stop here if the current sum is equal to the sum we want
IF @CurrentSum = @WantedValue
BREAK
ELSE
-- Update the current BestUnder if previous BestUnder is less
IF @CurrentSum > @BestUnder AND @CurrentSum < @WantedValue
BEGIN
UPDATE@Data
SETBestUnder = CurrentItems

SET@BestUnder = @CurrentSum
END
ELSE
-- Update the current BestOver if previous BestOver is more
IF @CurrentSum > @WantedValue AND @CurrentSum < @BestOver
BEGIN
UPDATE@Data
SETBestOver = CurrentItems

SET@BestOver = @CurrentSum
END

-- Find the next proper "bit" to increment
SELECT@RecID = MIN(RecID)
FROM@Data
WHERECurrentItems < MaxItems
END

-- Now we have to investigate which type of sum to return
IF @RecID IS NULL
IF @WantedValue - @BestUnder < @BestOver - @WantedValue
-- If BestUnder is closer to the sum we want, choose that
SELECTBestUnder AS Items,
FaceValue
FROM@Data
WHEREBestUnder > 0
ELSE
-- If BestOver is closer to the sum we want, choose that
SELECTBestOver AS Items,
FaceValue
FROM@Data
WHEREBestOver > 0
ELSE
-- We have an exact match
SELECTCurrentItems AS Items,
FaceValue
FROM@Data
WHERECurrentItems > 0With references to
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73540
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73610
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78015
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79505


Peter Larsson
Helsingborg, Sweden

View 3 Replies View Related

DISTINCT And Number Of Records

Mar 19, 2008

Wondering if there is a way to do this...

I want to select the DISTINCT user name from each record in a table, and then have another field tell me how many records it found..

is there any way to do this with SQL?


SELECT DISTINCT user_Name FROM table ORDER BY user_Name


is what I have now....


need something that will return:

user_Name numRecs
bob 3
fred 6
sam 1
linda 2

View 1 Replies View Related

Getting The Number Of Records With Like Values

Jun 14, 2006

I have a resultset that looks something like this:

Anzahl users_statdata_hobbies
---------------------
499 Andere
266 Essen
60 Essen,Andere
127 Essen,Musik
10 Essen,Musik,Party,Andere
30 Essen,Party
4 Essen,Party,Andere
51 Kunst
4 Kunst,Andere
13 Kunst,Essen
4 Kunst,Essen,Andere

I get this with this query which might be altered somehow:



SELECT COUNT(*) AS Anzahl, users_statdata_hobbies

FROM vgetAuswertung2

GROUP BY users_statdata_hobbies

ORDER BY users_statdata_hobbies

Of course this is not normalized but I can't change this.

Nevertheless I need to get the full number of each Hobby and not only the combination of them.

So instead or in addition to the existing recordset I need e.g

357 Essen which ist the sum of all records containing 'Essen' in the above example

The list of individual hobbies is defined therefor I could loop through the list manually and search for 'WHERE Hobbies LIKE '%ESSEN%' and count but since it's quiet a big resultset and there are several other similar tasks already I'm looking for a more performant way and I'm sure it could be done in SQL directly.

Any ideas someone?

View 4 Replies View Related

Restricting Number Of Records

May 19, 2006

I want to restrict the number of records coming from an OLEDB source. I have 500 records in my source table and I want to process one record at a time

I have I set the MaxBufferRows parameter to 1and it l sends 8 records from OLEDB source

Any help is appreciated.

View 9 Replies View Related

Sequence Number For Records

Nov 15, 2007

Hi...

I have Sql statement more like this

SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeA a
UNION
SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeB a


rec_num employeeID employeeName employeeDepartment


1 777 Mike HR
2 888 Susy HR
1 111 Smith TECH
2 222 John TECH
3 333 Lenny TECH



How do i get sequence number for all of this records. The rec_num reset for every statement. I want the records numbering for second statement continue from first statement so that it can be like this :

rec_num employeeID employeeName employeeDepartment


1 777 Mike HR
2 888 Susy HR
3 111 Smith TECH
4 222 John TECH
5 333 Lenny TECH

View 4 Replies View Related

T-SQL (SS2K8) :: Using Union ALL Or Union Kills Performance On Stored Proc

Jun 12, 2014

SQL Server 2008 r2...

I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).

Any way to join the result sets together without using Union All.

Each result set has exactly the same structure returned...

Query below [for reference]...

WITH cte AS (
SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum
FROM[UT_Pole] A
LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]

[Code] .....

View 4 Replies View Related

Number Of Records In SQLDataSource/GridView

Aug 21, 2006

What is the easiest way to obtain number of records in SQLDataSource (using select statement)/GridView. All that I've found in forums seems to be very difficult for such trivial task... Thank you!

View 4 Replies View Related

Search For Records That Begins With A Number (0-9)?

Aug 2, 2007

Hi all,For now I can use this code to display all the records that begins with a Letter:(WHERE SONG_TITLE LIKE @SONG_TITLE + '%') Now how do I search for records that begins with a number (from 0-9), as an add-on to the above query?Thank you very much,Kenny. 

View 6 Replies View Related







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