Record Count Percentage
Jul 19, 2007
How do I write a query to give me the total records in databaseA.tableA divided by the total records in databaseB.tableA
This is what I have so far:DECLARE @today as datetime
DECLARE @past as datetime
/* set the dates */
set @today = getdate()
set @past = getdate() - 3 /* Go back 3 days */
/* create temp table for holding total cases*/
CREATE TABLE #CaseTotalCount
(
TotalCount int
)
/**/
CREATE TABLE #FlashCycleCount
(
FlashCount int
)
/**/
INSERT INTO #CaseTotalCount (TotalCount)
SELECT COUNT (DataBaseA.dbo.tblProcedureData.CaseID) as TotalCount
FROM DataBaseA.dbo.tblProcedureData
WHERE DataBaseA.dbo.tblProcedureData.Date < @today AND DataBaseA.dbo.tblProcedureData.Date > @past
/**/
INSERT INTO #FlashCycleCount (FlashCount)
SELECT COUNT (DataBaseB.dbo.tblFlashLog.FlashLogID) AS FlashCount
FROM DataBaseB.dbo.tblFlashLog
WHERE DataBaseB.dbo.tblFlashLog.LoadDate < @today AND DataBaseB.dbo.tblFlashLog.LoadDate > @past
/*function call*/
SELECT dbo.percentage(#FlashCycleCount.FlashCount, #PeriOpTotalCount.TotalCount)
FROM #FlashCycleCount
JOIN #PeriopTotalCount
ON #PeriOpTotalCount.TotalCount != 0 This works but when I try to bind this to a formview a get nothing. any ides?
View 6 Replies
ADVERTISEMENT
Jul 21, 2013
I have a program which brings in Skill ratings for an assesmnet people with the title of "Worker have to take aolng with a file number they are assigned. The program also being in the reporting line that each worker is a part of.
SELECT distinct
o.VP,
o.AVP,
o.Director,
o.Supervisor,
o.Worker,
bs.File_NBR,
[Code] ....
The rating levels are 1 through 5. What I need to do is to do is create a table that shows the count and percentage of each rating giving to the workers for each skill grouped by Vp, AVP, Supervisor and Director. So all the works that are Ultimately under a AVP and all the wokers that are Ultimately under a director and so on.
NameRoleSkillCount of % of Count of % of
Rating 1 Rating 1 Rating 2 Rating 2
Gerald VPA1100 29% 13033%
Gerald VPB195 28% 9524%
Gerald VPB2120 35% 7018%
Gerald VPB330 9% 10025%
KimVPA1
KimVPB1
KimVPB2AND SO ON
[Code] ....
View 1 Replies
View Related
Jul 27, 2006
Hello folks,
I am stuck at a problem, not sure on how to go about writing a query that will return as a percentage the number of fields in a row that are null.
For instance, a row from my table:
Row1 : field1 field2 field3
If field3 is empty or null, my query should return 67%.
So far I have gotten the number of fields:
select count(1) from information_schema.columns where table_name='myTable'
I could loop through the fields but I am sure there is a simpler way of doing it, I have seen something simpler in the past with some builtin SQL functions. I am using MS SQL 2005.
Thanks for your help
Mike
View 2 Replies
View Related
Jan 22, 2015
What is the impact of fragmentation when my tables fragmentation is 99% but page count is 300 only?
View 6 Replies
View Related
Dec 4, 2014
I'm trying to get a calculation based on count(*) to format as a decimal value or percentage.
I keep getting 0s for the solution_rejected_percent column. How can I format this like 0.50 (for 50%)?
select mi.id, count(*) as cnt,
count(*) + 1 as cntplusone,
cast(count(*) / (count(*) + 1) as numeric(10,2)) as solution_rejected_percent
from metric_instance mi
INNER JOIN incident i
on i.number = mi.id
WHERE mi.definition = 'Solution Rejected'
AND i.state = 'Closed'
group by mi.id
id cnt cntplusone solution_rejected_percent
-------------------------------------------------- ----------- ----------- ---------------------------------------
INC011256 1 2 0.00
INC011290 1 2 0.00
INC011291 1 2 0.00
INC011522 1 2 0.00
INC011799 2 3 0.00
View 5 Replies
View Related
Jan 31, 2006
I've been looking for examples online to write a SPROC to get some data. Here are the tables.
Album_Category
AlbumCategoryID (PK, int, not null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)
Albums
AlbumID (PK, int, not null)
AlbumCategoryID (int, null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)
I need to return:
-[Album_Category].[AlbumCategoryID]
-[Album_Category].[Caption]
-[Albums].[Single AlubmID for each AlbumCategoryID]
-[Count of Albums in each AlbumCategory]
I hope I was fairly clear in what I'm trying to do. Any tips or help would be appreciated. Thanks.
View 3 Replies
View Related
Nov 30, 2004
I have a dts package that export data from a database to a text file. I could like to create a validation script to coun the data in the database that should be exported to file and then re-count again after the data is in the text file. Also count the number of columns that is exported.
Is there a way to do that within a dts package?
Help!
Lystra
View 1 Replies
View Related
Sep 17, 2007
Hello all
How to stop a report from being displayed if the record count is 0 in Reporting services.
Thanks
Ram
View 4 Replies
View Related
Nov 12, 2007
I would like to count rows by grouping of companys. So for one company when the report display the company employee they are associated a number. Example:
Company: Xrite
User: Tom Smith
User # 1
User: Jane Doe
User # 2
Company: Xwrong
User: George Green
User # 1
User: Mary Jane
User #2
I found the function RowNumber(). This function used as follows RowNumber(Nothing) Returns a row number for each record of the table instead of by company name (which is the group by).
Is there a way to use this function RowNumber() or any way in SRS to get the results I'm looking for?
Thanks,
Max
View 5 Replies
View Related
Apr 2, 2008
I I have a GridView with paging enabled which is bound to a SqlDataSource. I'd like to be able to tell the user how many records were returned in total by the SqlDataSource. Is there any way to do this without re-running the query?
View 3 Replies
View Related
Jun 2, 2008
hello friends
i have table1 in aspnetdb.mdf. my cloumname usernumber and my datatype is int.
how may can be usernumber ? what is the max usernamber ?
or there is bigint as datatype.. what is the max usernumber for bigint ?
if you tell me about int32 and int 64, i couldnt find int32 and int64 as datatype in table1 in aspnetdb.mdf
cheers
View 1 Replies
View Related
Feb 16, 2006
Hi,
I would like to know how many records are selected in sqldatasource after select command
Thanks
View 5 Replies
View Related
Aug 25, 2004
Hello.
I am trying to limit the number of records to start in a position and show me "n" records. Here is an example query:
SELECT TOP 60 * FROM Entries WHERE Id NOT IN ( SELECT TOP 45 Id FROM Entries ORDER BY Id DESC ) AND Static = False AND Authorized = False ORDER BY Id DESC
It's not working correctlly. Any ideas?
Thanks in advance.
View 4 Replies
View Related
May 30, 2007
I have been assigned to fix a script which i did not write and that is not in SQL, but calls to SQL to get it's results.
It reads from a payroll table in SQL with 943 total records (of which about 280 are active employees).
When i run the script, i receive a series of errors listing the field that the error is in (ie: Badge or PayRule) and the record # causing the error.
My problem is that the 1st record # that returns an error is #946. I get a total of 45 records that generate errors of this sort. (ie: #946-957 Badge, 1154-1159 Badge, 1482-1497 PayRule, 1562-1568 Badge, 2083-2086 PayRule).
IF i can figure out which employee those records belong to I will have a much better chance of finding the cause of the errors and correcting them.
When it gives a record #, i always thought it was the row in the table that was referred to, but since the record #'s i am receiving are larger than the number of rows in the table, i am utterly confused!
So...i guess my question is, how do I count the records if not by row?
thanks in advance, and i hope this makes sense!
ed c
View 2 Replies
View Related
Jan 11, 2006
There a stored procedure or query that will count all the recordsin all my tables in a database.One of these two -exec sp_MSforeachtable N'SELECT "table" = ''?'', cnt = COUNT(*) FROM ?'select object_name(id), rows from sysindexes where indid in (1, 0)Is there a way to add columns to the second query? I would like to seehow many columns and their names as well.Thanks.
View 1 Replies
View Related
Mar 21, 2007
I think I am asking this in the right place.
I'm trying to get a record count from a table with 30million items and it takes forever.
Here is my code:
SELECT COUNT(f_id) AS 'ROWCOUNT' FROM tablename
Is there a faster way.
BTW f_id is primary key indexed.
Thanks
View 5 Replies
View Related
Nov 28, 2005
Please tell me the 4 ways to know the record count of a table?
View 14 Replies
View Related
Jun 7, 2006
Hi.
My boss would like a list of all the tables in a specific database and their record counts. There are over 80 tables in this database, so doing one by one is not something I have time for.
Does anyone know of a system table that stores this information or an easy way of doing this?
Please help.
Thanks,
ODaniels
View 4 Replies
View Related
Apr 3, 2008
Hi all,
i am using sql server 2005.
let me explain the scenario.
i am writing a function that return no of call a sales execituve done.
an executive can present n no of product to one customer but that call will be only one
my table record look like
empid custid product date and other field
em111 iimran aaaa 01-04-2008 ........
em111 iimran bbbb 01-04-2008 ........
em111 iimran cccc 01-04-2008 ........
em111 xxxxxx aaaa 01-04-2008 ........
em111 xxxxxx 2222 01-04-2008 ........
em111 iimran aaaa 02-04-2008 ........
em111 iimran bbbb 02-04-2008 ........
em111 iimran cccc 02-04-2008 ........
em111 xxxxxx aaaa 02-04-2008 ........
em111 xxxxxx 2222 02-04-2008 ........
Now if i select total no of call it should give me four call
also i am getting this through this query
select dcr_date , cust_id from vwdcrdtl where emp_id = @empcode and dcr_date between @stdate and @enddate group by dcr_date , dcr_cust_id
this query correctly return 4 rowcount(which i am gerring though @@rowcount) but i want this rowcount to return from function
i assinged this to rowcount to int veriable but this giving me error .
i would like to know how can i get count of these records
Please Help
View 1 Replies
View Related
Jun 12, 2007
wondering if someone can share their wisdom about getting record count from Source Oracle DB using SSIS?
View 5 Replies
View Related
Jul 20, 2005
Hi I have a Table with Automatic ID numbers...In access I delete All records by handWhen I add new record with delphi (SQL)the number ID of record count begin with last record+1and not with 0Someone know the statement to reset that?thanx for any help
View 2 Replies
View Related
Nov 9, 2007
I have the following data,
CustomerID EngID EngCount
1 A11 2
2 B12 1
3 C10 3
I need to display it as,
CustomerID EngID EngCount
1 A11 1
1 A11 1
2 B12 1
3 C10 1
3 C10 1
3 C10 1
Create a record based on the EngCount. So CustomerID of 3 has 3 records as shown above.
How to do?
View 5 Replies
View Related
Aug 31, 2006
I am currently using this SQL code to capture some records over the last 2 months and it has been working great. I am now being asked if I can change this code with specifications:
1) Scan the records in the system until the count (*) as Volume reaches 30 because they prefer that as a denominator when figuring an average
2) Only run the scan for a maximum of 6 months.
So, there will most likely be some records that do not reach a volume number of 30 in this date range. In this instance we will just take the maximum volume number reached at 6 months.
So, how can I write this so it will build the file each time a record has reached the maximum of 30 and keep scanning back until we reach 6 months? If someone could lead me in the right direction on the proper order of the methodology in my code to accomplish these results it would be greatly appreciated. Desperate!
declare
@startdate smalldatetime,
@enddate smalldatetime ,
@month int,
@year int
select
@startdate = dateadd (mm, -2, getdate())
SELECT
@month = datepart (month, @startdate),
@year = datepart (year, @startdate)
SELECT
@startdate = convert (smalldatetime, convert(varchar(2), @month) + "/1/" + convert (varchar(4), @year))
select
@enddate = dateadd (mm, 2 , @startdate)
select distinct
pe1.patev_loc_id as LocID,
pp_cproc_id_r as ProcID,
count (*) as Volume,
sum (datediff (mi, pe1.patev_event_time, pe2.patev_event_time)) as Minutes,
sum (datediff (mi, pe1.patev_event_time, pe2.patev_event_time))/count(*) as AvgMin
from
risdb_rch08_stag..performed_procedure (index pp_serv_time_r_ndx),
risdb_rch04_stag..patient_event pe1,
risdb_rch04_stag..patient_event pe2
where
pp_service_time_r between @Startdate and @Enddate
and pp_asn_req_no = pe1.patev_asn_req_no
and pp_asn_seq_no = pe1.patev_asn_seq_no
and pp_status_v = 'CP'
and pp_rep_id > 0
and pe1.patev_event_code = 'PB'
and (pp_asn_req_no = pe2.patev_asn_req_no
and pp_asn_seq_no = pe2.patev_asn_seq_no
and pe2.patev_event_code = 'PL')
and datediff (mi, pe1.patev_event_time, pe2.patev_event_time) > 0
group by
pe1.patev_loc_id , pp_cproc_id_r
View 1 Replies
View Related
Jan 18, 2007
Here's a little sql 2005 script I wrote:
1. Start by running this script....
declare @x int
select @x = 1
while ( @x < 75000)
begin
insert into myTesttable values (@x)
Select @x = @x + 1
end
2. While the script is still running, I want to know how many records are in the table. From the same query window as the script, I have run both of the following statements.
select count(*) from mytesttable
witn (nolock)
select count(*) from mytesttable
witn (tablock)
Instead of getting the answer immediately, they run only after the original script has completed. They seem to be "blocked". How can I get a near realtime count of the number of records in this table while the script populates the table?
Thanks,
Barkingdog
View 5 Replies
View Related
Oct 18, 2004
I am trying to write a stored procedure that updates a value in a table based on the sort order. For example, my table has a field "OfferAmount". When this field is updated, I need to resort the records and update the "CurrRank" field with values 1 through whatever. As per my question marks below, I am not sure how to do this.
Update CurrRank = ??? from tblAppKitOffers
where appkitid = 3 AND (OfferStatusCode = 'O' OR OfferStatusCODE = 'D')
ORDER BY tblAppKitOffers.OfferAmount Desc
All help is greatly appreciated.
View 2 Replies
View Related
Jul 19, 2005
Hi I want to know what are the three different ways by which we can get the record cound for a particular table using SQL Query analyserThanks in advance
View 3 Replies
View Related
Dec 10, 2014
This my table named myData
CREATE TABLE [dbo].[myData](
[idx] [int] NULL,
[paymentMethod] [nvarchar](200) NULL,
[daerahKutipan] [int] NULL,
[payer] [int] NULL,
[code]....
I want to calculate the number of payer Group By paymentMethod. The number of payer must be divided by daerahKutipan. So far, my query as follow
select paymentMethod,
COUNT(CASE WHEN daerahKutipan = 1 THEN payer ELSE 0 END) figure_Seremban,
COUNT(CASE WHEN daerahKutipan = 3 THEN payer ELSE 0 END) figure_KualaPilah,
COUNT(CASE WHEN daerahKutipan = 4 THEN payer ELSE 0 END) figure_PortDickson,
COUNT(CASE WHEN daerahKutipan = 5 THEN payer ELSE 0 END) figure_Jelebu,
COUNT(CASE WHEN daerahKutipan = 6 THEN payer ELSE 0 END) figure_Tampin,
COUNT(CASE WHEN daerahKutipan = 7 THEN payer ELSE 0 END) figure_Rembau,
[code]....
View 1 Replies
View Related
Jul 16, 2014
I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable. I need to check whether count > 0 or not in stored procedure.I have used below query.
SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0
IF (@_Stat_Count >0)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END
View 5 Replies
View Related
Feb 26, 2007
I'm trying to return the total records with my query, but I'm getting the following error:
"Item cannot be found in the collection corresponding to the requested name or ordinal."
Here's my query:
set rsFind = conn.Execute ("Select Count(Incident_ID) as TotalCount, Incident_ID, ProblemDescriptionTrunc, Action_Summary, RootCause, Problem_Solution002, " _
& " AssignedTechnician, DATEADD(s, dbo.TTS_Main.DateClosed, '1/1/1970') AS DateClosed, DATEADD(s, dbo.TTS_Main.Date_Opened, '1/1/1970') AS DateOpened, AssignedGroup From tts_main Where ProblemDescriptionTrunc LIKE '%" & prob & "%' And Last_Name LIKE '%" & l_name & "%' " _
& " AND AssignedTechnician LIKE '%" & assigned_tech & "%' And Incident_ID LIKE '%" & ticketnum & "%' AND assignedgroup LIKE '%" & assigned_group & "%' " _
& " Order By DateClosed DESC ")
<%response.write rsfind("TotalCount")%>
Thanks for any help!
Dale
View 10 Replies
View Related
Oct 10, 2006
We have an inventory table (Items) that contains item_no and qty_on_handfields.Another table (Item_Serial) contains serial numbers for any item that hasserial numbers.If an item has 10 qty_on_hand, it should have 10 records in Item_Serial, oneunique serial number for each item.I am trying to find items where the number of serial numbers does not equalthe qty_on_hand.Here is a query I'm trying to use and, of course, it does not work:select Items.item_no, Items.qty_on_handfrom Items inner join Item_Serial on Item_Serial.item_no = Items.item_nowhere Items.qty_on_hand <count(Item_Serial.item_no)Anybody know how to do this?Thanks.
View 4 Replies
View Related
Jan 15, 2007
Hello
I'm reading a XML file and the next operation need a column with the row count.
I don't want to know how many rows there is in the recordset, but, the row count of each record.
How can I implement this?
tkx in advance
Paulo Aboim Pinto
Odivelas - Portugal
View 1 Replies
View Related
Nov 17, 2006
Hi, I want to delete from table when the record count is over 5000. That is, the table's record cannot over 5000 count. How? row_number() seem to be used...
View 11 Replies
View Related
Jan 18, 2006
Hellois there a variable that is available to me that contains the numberof rows contained in a dataset return from a database call?
have a class that runs a stored proc and returns a dataset/resultsetlooking to simply assign an integer this value if it is possible
i'm using (learning) vb.net and sql server
thanks in advance
View 4 Replies
View Related