Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Total Page Writes/total Amount Of Data Change In A Set Period Of Time


Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.

TIA


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Calculating The Total Amount Of Drugs Prescribed, Total Amount
Hi all,

 

I have a table named Prescription that consists of attributes like PatientId, MedicineCode, MedicineName, Prices of different drugs, quantity of different drugs(e.g 1,2,3,10), date .

I would like to get a summary of the total number and amount of different drugs in a specific period, the total amount of each type of drug.

 

I  kindly request for help.

Thanx in advance.

Ronnie

View Replies !   View Related
Get Total Amount By Date
this is my sample records in my table


Sdate Amount
January 2007 250000.00
March 2007 300000.00
January 2008 350000.00



how can i get the amount from February to
May 2007 and sum it up like this


Details
February 2007 250000.00
March 2007 300000.00
April 2007 300000.00
May 2007 300000.00



this is all that i want to get
Total Amount: 1,150,000.00

i also posted this one in new to sql server section, but im hoping to get others opinion here. thnx

View Replies !   View Related
Total Amount Of Triggers Per Table
Hi

Just a quick question taht I hope someone can answer, What it the total amount of trigger per table?

I understand that it is better to have as least as possible for performance

Thanks Rich

View Replies !   View Related
Running Total: Summing On Distinct Amount
I am trying to do a sum on a goal amount that is repeated for each record.  But what is the the forumla to only sum on the distinct goal amount.

 

Example:

Month             Year             Goal           Other Value

March             2007           500             5568

March             2007           500             5568

March             2007           500             5569

April                2007           600             5568

April                2007           700             5569

 

Total (I am receive)

March-April    2007           1600           5568

March-April    2007           1200           5569

 

Total (I excpect)

March-April    2007           1100           5568

March-April    2007           1200           5569

 

I haven't found anything online to help. 

 

Thanks,

Sam

 

View Replies !   View Related
Query By Year Group By Total Students Sort By Total For Each County
I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob

View Replies !   View Related
Adding Subreport Total To Main Report Total
Hi, can anyone help?
 
I have created a Report using Visual studio-the report displays a subreport within it.
 
On the Subjective Report I have 12 values for each month of the year.
 
For the first month the value is =sum(Fields! Month_1.Value), and I
have named this text box €™SubRepM1€™
The name of the subreport is €˜subreport1'.
 
On my Main Report, again I have 12 values for each month of the year.
For the first month the value is =sum(Fields! Month_1.Value)*-1, and I
have named this text box 'MainRepM1'
The name of the main report is 'GMSHA Budget Adjustment Differentials' 
 
The report displays both of the subreport and main report values
but I now need to total these values together for each month in order to
produce a grand total.
 
I have tried using the following to add the totals for Month 1 together,
=subreport1.Report.SubRepM1 + MainRepM1
but this does not work and I get the following error message  €˜The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.
 
I feel that it should be a simple matter of adding the two sets of values together but I€™m having major problems trying to get these totals to work.
 
Can anyone help, thanks

View Replies !   View Related
Total On Each Page
hi,

      in my report i have amount field. i want to display its total (sum) on each page.....  i have tried runningvalue in tablefooter but it gives final total.......means grand total... i have also tried sum function in tablefooter & set repeateoneachpage = true but it gives same result.............. and i want pagewise totals...... anybody can help me...................

View Replies !   View Related
Total Spaceused And Total Allocated
Anyone has a "one sql statement" to get the total spaceused and totalspace allocated of an instance ? ie same as sum of relevance fieldsfrom sp_spaceused for each database in an instance, that works accrossversion of mssql from 6 onward.ThanksKD

View Replies !   View Related
How To Get Total Page Count For Individual Groups
Hello,

 

I have report in which I have created groups base on the customer name. Can anybody please tell me how to get the total page counts for the individual group? I have page break after every new group and I am able to reset page count to 1 when new group start but I am getting the total number of pages for a particular group.

 

For ex, let say I have 4 groups, 1 group has 3 page, 2 group has 2 pages and 3 group has 6 page and 4th group has 7 pages I need something like,

 

For 1st group

'Page 1 of 3€™ when user click next page it should be 'Page 2 of 3' etc

 

Similarly for other groups as wll.

 

Thanks!
 

View Replies !   View Related
Reset Total Page Number In A Group
I know how to reset the page numbers with each group, but how do you reset the total page number within each group.

EX.  Code for page of total pages

="Page " & Globals.PageNumber & " of  " & Globals.TotalPages

 

EX. Code to reset within a group
Custom Code:
Shared offset as Integer
Shared currentgroup as object
 
Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) as Object
If not (group = currentgroup)
offset = pagenumber - 1
currentgroup= group
end if
return pagenumber - offset
end function
 
=Code.GetGroupPageNumber(ReportItems!Category.Value(grouping),Globals!PageNumber)
 
What I need is code for a combination of the two...to display code for page of total pages that resets within a group.
 
Any help is greatly appreciated.
Thanks!

 

View Replies !   View Related
Counting Total Number Of Queries Executed Within The Page
Hi everyone,Does exist an easy way to count the actually number of queries executed within a page?I've searched here and in google but found anything...Thanks in advance! 

View Replies !   View Related
How To Create Total Time, Count It
i hv a problem in sql,

i) if in data hv time_start, time_end, date , id, how i can calculate the total time for very sesion user login

ii) how to make it if the total come out hv same id(may be two id hv two diffrent total), i want make it just one id then the total time...

post me at jeffliew2001@yahoo.com

View Replies !   View Related
Run-Time Running Total *(SOLVED)
I am re-writing a old query that update's it's value based on values at run time. Is there anyway to accomplish the following query where column s1.AI referances the values that were updated during run-time.

I want to avoid a Loop (i.e. cursor or a CTE).

Please help!



Declare @Stage Table(StartDate datetime,BenefitInterestID INT PRIMARY KEY, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4))
Insert Into @Stage
Select
convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,1 as BenefitInterestID,1701.00 as amount,79.605 as InterestAmount ,0.1000 as Interest,0.0000 as ai
Union all
select '2007-12-01 00:00:00.000',2,172.80,7.92,0.0500,0
Union all
select '2008-12-01 00:00:00.000',4,0.00,0.00,0.0700,0




UPDATEs1
SETs1.ai = s1.Interest * coalesce((SELECT SUM(coalesce(s2.Amount,0) + coalesce(s2.InterestAmount,0)+coalesce(s2.ai,0)) FROM @Stage AS s2 WHERE s2.StartDate < s1.StartDate ),0)

FROM@Stage AS s1

select * from @Stage

My desired Results are


2006-12-01 00:00:00.00011701.0079.6050.10000.0000
2007-12-01 00:00:00.0002172.807.920.050089.0303
2008-12-01 00:00:00.00040.000.000.0700143.5300


But I am geting

2006-12-01 00:00:00.00011701.0079.6050.10000.0000
2007-12-01 00:00:00.0002172.807.920.050089.0303
2008-12-01 00:00:00.00040.000.000.0700137.2928


Due to as you can see on row 3 that the amount does not factor in the previous Years ai column when I sum, due to the update takes place at runtime.

Please help!

View Replies !   View Related
Displaying Total Number Of Rows In A Report In Page Header.
Hi,

 

I have requirement to display Total number of Rows in a Report in Page Header.

 

I have written the following code in Page header it shows RowCount for the Page only.

               =Count(ReportItems!textboxInTableCell.Value)

 

Can anyone please help on this?

 

Regards

Raghav

 

 

 

View Replies !   View Related
Groupwise Page Numbering And Also Display The Total No. Of Pages In That Particular Group.
 

Friends,
 

I am using SSRS (Sql server reporting services) for one of my report related to EIS-MIS.
i want group wise page numbering and also display the no. of pages for that particular group.

e.g suppose group 1 has 5 pages then it should display
  page no. 1 /5, 2/5,3/5,4/5, 5/5. now suppose group change then it shold display 1/4 like this

I have already implemented groupwise page numbering using then custom code and i have call that function from the header portion of the report.
now the question is how can i display the total no. of pages in particular group?

For that i have add one count column in query itself but as we know we can't use field value in header and footer portion and also we can't use global variables in data portion.

 
So kindly guide me
Thanks,

Manoj Patel

View Replies !   View Related
Problem With Printing Execution Time And Total Pages
I have a strange problem.  All my report footer has 3 text boxes with the following formulea in them.

="Printed by " + User!UserID + " on " + DateTime.Now.ToString()
 

="Execution Time: " +

IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds",

(

IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") +

IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") +

IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", ""))

)

 
="Page " + Globals!PageNumber.ToString() + " of " + Globals!TotalPages.ToString()
 
When I run the report on the report manager, (through the site), the execution time, number of pages everything show properly.  But when I print the report, the execution time and total pages is printing huge numbers, which are not in any way related the actual page numbers.
 
For example: 
When I run the report it shows the footer like below:
Printed by xxxxxxxxx on 3/28/2008 1:59:01 PM     Execution Time: 6 Seconds   Page 1 of 213
When I print
Printed by xxxxxxxxx on 3/28/2008 1:59:01 PM     Execution Time: 47 Seconds   Page 1 of 483
 
Does anyone know what is the solution to this problem is?
I really appreciate your help,
 
Thank you,
 
 
 

View Replies !   View Related
How To Separate Period Amount From YTD Amount
I'm creating a temporary table in a Sql 2005 stored procedure that contains the transaction amount entered in a period <= the period the user enters.
I can return that amount in my result set. But I also need to separate out by account the amounts just in the period = the period the user enters. There can be many entries or no entries in any period. I populate the temporary table this way:

SELECT
t.gl7accountsid,
a.accountnumber,
a.description,
a.category,
t.POSTDATE,
t.poststatus,
t.TRANSACTIONTYPE,
t.AMOUNT,
case
when t.transactiontype=2 then amount * (-1)
else amount
end as transamount,
t.ENCUMBRANCESTATUS,
t.gl7fiscalperiodsid

FROM
UrsinusCollege.dbo.gl7accounts a

join
ursinuscollege.dbo.gl7transactions t on
a.gl7accountsid=t.gl7accountsid

where
(t.gl7fiscalperiodsid >= 97
And
t.gl7fiscalperiodsid<=@FiscalPeriod_identifier)
And poststatus in (2,3)
and left(a.accountnumber,5) between '2-110' and '2-999'
And right(a.accountnumber,4) > 7149
And not(right(a.accountnumber,4)) in ('7171','7897')

order by a.accountnumber

Later I create a temporary table that contains budget information. I join these 2 temporary tables to produce my result set. But I don't know how to get the information for just one period. For example, if the user enters 99 as the FiscalPeriod_identifier, I need a separate field that contains only those amounts(if any) that were entered for each account in Period 99.

Can anyone help? It may be that I am not seeing the forest for the trees, but I can't figure it out.

Thanks very much.

Sue

View Replies !   View Related
Page Numbes Per Group And Total Page Per Group.
Hi!
 
  I need to be able to reset the page numbering of a report for each top-level group in a table, and I need to be able to show the total number of pages for each group.  For example, if I'm printing invoices and each invoice is a table group, I need to be able to show "Page 1 of 5", Page 2 of 5" and then show "Page 1 of 3", "Page 2 of 3" for the next invoice.  I've read the so-called workarounds.  They don't work well.  They really don't, not when you have more than one user running a report at the same time and not if you display the report and print at the same time.  So, please don't refer me to http://blogs.msdn.com/ChrisHays/.  What I'm really asking is whether Microsoft is currently working on a resolution or not, or if perhaps this has already been resolved in a new Service Pack or HotFix that I may have missed.  People have been asking for this since Reporting Services came out.  I'm just wondering what Microsoft is doing about it. 
 
Thanks!
Karen

View Replies !   View Related
What's Microsoft Doing About Providing Page Numbering Per Group And Total Pages Per Group?
Hi!
 
    I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem!   I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is!  Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.
 

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311679
 
Provide Individual Page Numbering per Group and Total Pages per Group
 
Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.
 
 
I've read a few articles on this topic, but no one has come up with a decent work around.  My theory is that Microsoft should be addressing this immediately.  This is major functionality that's just plain missing from SSRS and should have been there from the start.  If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.

 
Thanks!
Karen

View Replies !   View Related
Adding Data From Fields To Get A Total
EX:  I have a table for products, and each product has a quantity.  How can I add up the QTY field in all the rows to find out the total QTY of all the products.Any help would be greatly appreciated.gkc

View Replies !   View Related
Total Length Of Data Type In A Table
Folks:

I would like to know the total length of data type in a table. I ran the following query. Will this give me the correct information? I also ran sp_columns <table name> and it too give the length. But There is a difference in the numbers. Am I doing something wrong and which is the correct the query or sp_column.

select sum(length) from syscolumns
where id in (select id from sysobjects
where name = 'XYZABC')


sp_columns XYZABC



Thanks !

View Replies !   View Related
Change To &"Total Hours&" Difference
How can I change the output into the total hours between 2 smalldatetime data? TIF


DECLARE @iDAY AS SmallDatetime
SET @iDAY = '2004-08-12 10:05:00'


PRINT @iDAY
PRINT GETDATE()

PRINT GETDATE()-@iDAY
+++++++++++++++
Aug 12 2004 10:05AM
Aug 13 2004 10:05AM
Jan 2 1900 12:00AM <--- I want to get the total hours (i.e. 24)

View Replies !   View Related
Create Query To Average Data & Total/sum Data
Hello,
I am very new to SQL and just getting to learn this stuff. To make this question easier I will scale down the fields dramatically.

I have about 8000 records close to 2000 records for the last 4 years
and I would like to create a query that will create a table on my SQL server. I need to bind the data based on two items the Year and the Name and average several records. However, one record needs it's own calculation.

Here are my field names:
[year] ***4 choices 2007, 2006, 2005, 2004***
[name]
[rush_no] ***integer***
[rush_net] ***integer***
[YPC] *** This field needs to be calculated by [rush_net] divided by [rush_no]***decimal***

I also need to create the same table that will "total/sum" the same records.

View Replies !   View Related
Please Help =IIf(IsError([total]),&&"&&",[total])
Hi,
 
I am trying to create a report on some data. I have about 8 tables and 30+ queries attached to those 15 reports. In one of those reports I want to get the percentage based on the data in the tables and queries. Say I have the minimum hours for an employee as 176 hours and the employee works for 227 hours in a month. I want to see the result in percentage.
 
My report looks something like this :
 
ID      Name       Oct          Nov           Dec             Jan      Feb      March                    Total
001    alex         87.6%      104.1%      65.1%          50.2%                                         85.6%
002    Linda       87.4%      109.1%      68.1%         35.2%                                          90.8%
003    Jon         87.6%      104.1%                                                                            
004    alex         87.6%      104.1%      65.1%          50.2%                                         85.6%
005    Linda       87.4%      109.1%      68.1%         35.2%                                          90.8%
 
For the 002 ID, though he has worked for Nov and Dec the total % is blank.
 
The formula that I used for all of these entries is :
 
=IIf(IsError([total]),"",[total])
 
and for the month it is : =IIf(IsError([Oct]),"",[oct]),               nov and so on.
 
It works fine for all, but where ever there is blank in one field it doesn;t calculates for the others too..
 
Please help, how can I get the total for all.
 
Thanks,
 
 
Farn
 
 

View Replies !   View Related
Total Sales By Customer Vs Total Sales
 

I have a report which totals sales by customer.  Then table footer has a grand total of all customer sales.  I would like to get a percent of each customer's sales against the total sales.  How do I get the sum from the table footer to use in an individual customer row?
 
Thanks.

View Replies !   View Related
Using Membership Db To Lookup Data In Another Db And Bring Back Into A Grid View (total Newbie To .net)
I have the membership stuff up and running.  I've added a field to the membership table called custnmbr.  Once a user logs in, I want store his custnbmr in the session and use that to lookup data in another db.
ie: Joe logs in and his custnumbr is 001, he goes to the login success page and sees his list of service calls which is:
select top 10 * from svc00200 where custnmbr = 001 (the membership.custnmbr for the logged in user)
I know how to do this in old ASP using session variables....but I have no idea where to even start with .Net.
Many thanks

View Replies !   View Related
Select Data Based On Certain Time Period
Hello all,

I am using SQL Server in a project where I want to fetch the records that were inserted after a time specified in my query.

Suppose 10 records were inserted at 10:00 AM(morning) and 5 were inserted at 10:15 AM( 15 minutes later). I am running a query at 10:20 AM( 5 minutes after the second transaction). I need this query to be such that it selects the records inserted 10 minutes before. So it will show only the records inserted at and after 10:10 AM and willl not show those inserted at 10:00 AM.

Please help me in making such a query.

I am trying and I think that some Date & Time functions will help but still not able to achieve it.

Thanks in advance

View Replies !   View Related
Reducing Time On Retrieving Large Amount Of Data
Hi,
My application needs to retrieve data from a table which has more than 15 lakh records. The records keep increasing in thousands every 15 days.
Is there anyway i can reduce the time to retrieve? basically i have a select statement with a few conditions and a clause for the id's of these records.

View Replies !   View Related
Selecting Data From SQL Table Based On A Time Period
I am trying to write a stored procedure that will select information from a SQL table based on a specific time.
For example I have a name field and a time field, I want to return just the names that were created between a specific time frame. ex between 3pm and 4pm.
Any thoughts?

View Replies !   View Related
Getting Total From Row
I have built a query in Access that calculates the total to be charged to clients based on 3 cost columns.

I have tried to run the query in MSSQL but it will not run, so could someone help me out with the syntax in my query.

I have done about half of it myself but I am stuck on the calculation and I havent a clue of the syntax to be used.

This is my Access Query;
SELECT DISTINCT Holiday_Bookings.ClientID,
Holiday_Bookings.Booking_Cost,
Room_Facilities.FacilityCost,
Rooms.CostPerNight, Rooms!CostPerNight*Nights_Stayed+Holiday_Bookings! Booking_Cost+Room_Facilities!FacilityCost AS TotalCost,
[TotalCost]*17.5/100+[TotalCost] AS [Total+VAT]
FROM Room_Facilities
INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID = Rooms.ClientID)
ON Hotels.HotelID = Rooms.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID;

and this is what I have managed to salvage in MSSQL format:
SELECT
Holiday_Bookings.ClientID,
Holiday_Bookings.Booking_Cost,
Rooms.CostPerNight,
Room_Facilities.FacilityCost
FROM
Rooms
INNER JOIN Holiday_Bookings ON (Rooms.Clients_ID = Holiday_Bookings.ClientID)
INNER JOIN Room_Facilities ON (Rooms.FacilityID = Room_Facilities.FacilityID)


Any help would be great as I am completely lost.

View Replies !   View Related
How To Get YTM Total
what I want to display as blow£º
Jan Feb March ... Month to Year
*** *** **** ****

now I use
PeriodsToDate(year,[Sales_Time].[All Sales_Time].[2005].[Quarter 2].[April])
to get:
Jan Feb March April
*** *** *** ***

but how to get £¨Month to Year£©total £¿
tks!!

View Replies !   View Related
Help With Total
HI everyone, I been looking for the answer for sometime now, thought i would post and see if anyone could help me it. This is the output of my query.

F_DivDiv_4Div_5Div_35
424600
504000
3510114

After the row 35 i want to add a row for total. I want to add the values of column Div_4 so it would be 246+0+1. The problem i'm having is that the columns are populated from using this logic.

Select
rr.F_BVT_DIV_NO,
(Case When rr3.F_DIVISION_NO = 4 then Max(rr3.Total_Pol_Count) else 0 end ) + (MAX(isnull(rr2.Div4,0))) Div_4

from RNEWL_RTENTN_DETL RR
--join for division 4 transfers

left join(

select
rr2.F_BVT_DIV_NO,
count(rr2.X_POLICY_NO) As Div4
from RNEWL_RTENTN_DETL RR2
where
RR2.F_BVT_DIV_NO<>RR2.F_DIVISION_NO
and month(RR2.X_POLICY_XPRTN_DT)=2 and
year(RR2.X_POLICY_XPRTN_DT)=2007 and
rr2.F_DIVISION_NO = 4
Group By
rr2.F_BVT_DIV_NO ) rr2 on
rr2.F_BVT_DIV_NO = rr.F_BVT_DIV_NO

from TRNEWL_RTENTN_DETL rr3
where
month(rr3.X_POLICY_XPRTN_DT)=2 and
year(rr3.X_POLICY_XPRTN_DT)=2007
Group by
rr3.F_DIVISION_NO) rr3 on
rr3.F_DIVISION_NO = rr.F_BVT_DIV_NO
group by
rr.F_BVT_DIV_NO,
rr3.F_DIVISION_NO
order by
rr.F_BVT_DIV_NO

That code is just a little piece of it, but the logic is the same throughout. Thanks for the input!

View Replies !   View Related
Sum (Total)
I need to sum the totals for each of the ratings, then group them by owneridname. the problem i am having is that the "Count(*) as total" returns a total that includes ratings that are not listed below as a criteria.

Eg. the total for all ratings for a user john would be 789.
that figure would inlcude amounts for a rating eg. dead. that is not in the list below. please help.

SELECT owneridname, SUM(CASE WHEN new_ratingname = 'Hot' THEN 1 ELSE 0 END) AS Hot,
SUM(CASE WHEN new_ratingname = 'warm' THEN 1 ELSE 0 END) AS Warm, SUM(CASE WHEN new_ratingname = 'cold' THEN 1 ELSE 0 END) AS Cold,
SUM(CASE WHEN new_ratingname = 'cool' THEN 1 ELSE 0 END) AS Cool, SUM(CASE WHEN new_ratingname = 'new' THEN 1 ELSE 0 END) AS New,
SUM(CASE WHEN new_ratingname = 'Reservation Holder' THEN 1 ELSE 0 END) AS [Reservation Holder],
SUM(CASE WHEN new_ratingname = 'site visit' THEN 1 ELSE 0 END) AS [Site Visit], SUM(CASE WHEN new_ratingname IS NULL THEN 1 ELSE 0 END)
AS [Not Rated], SUM(CASE WHEN new_ratingname = 'Continous Updates' THEN 1 ELSE 0 END) AS [Cont Updates],
SUM(CASE WHEN new_ratingname = 'worked tasks' THEN 1 ELSE 0 END) AS [Wkd Tasks], COUNT(*) AS Total
FROM FilteredContact AS filteredcontact
WHERE (statuscodename = 'active')
GROUP BY owneridname

Melvin Felicien
IT Manager
DCG Properties Limited

View Replies !   View Related
Getting SUM Total
I have the following query which gives me a list of names, logins, and group name along with a count of how many training modules each individual has sat. This returns 70 rows with each individuals details and totals.

However I want to be able to report instead just the total modules sat for all 70 rows instead of displaying each row individually. This is the query I am using:

SELECTpps_principals.name AS pname, EXT_USER_GROUPS.LOGIN, EXT_USER_GROUPS.NAME,
COUNT(PPS_SCOS.SCO_ID) AS coursecount
FROMPPS_PRINCIPALS
LEFT JOINPPS_TRANSCRIPTS ON PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID
AND PPS_TRANSCRIPTS.STATUS like '[PCF]'
AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
AND pps_transcripts.date_created between '2006-10-01' and '2007-09-30'
LEFT JOINPPS_SCOS ON PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID
AND pps_scos.name like 'MT%'
LEFT JOIN EXT_USER_GROUPS ON EXT_USER_GROUPS.LOGIN = PPS_PRINCIPALS.LOGIN
WHERE pps_principals.login like '%score%' and ext_user_groups.name like 'ALL SCORE PTY'
AND dbo.PPS_PRINCIPALS.DISABLED IS NULL
GROUP BY pps_principals.name, EXT_USER_GROUPS.LOGIN, EXT_USER_GROUPS.NAME
HAVING COUNT(PPS_SCOS.SCO_ID) > 0
ORDER BY pps_principals.name, EXT_USER_GROUPS.LOGIN, EXT_USER_GROUPS.NAME

I am trying to use SUM to get the overall total but without success so far.

Any help gratefully received.

View Replies !   View Related
Get A Total
Hello Everyone,

How can I get the total of the loan numbers:


USE CHEC
SELECT DISTINCT
DAT01.[_@051] AS Branch,
DAT01.[_@550] AS LoanType,
convert(varchar(10), DAT01.[_@040], 110) AS Date,
DAT01.[_@LOAN#] AS LoanNum
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW]
ON DAT01.[_@040] = [_@040]
INNER JOIN [SMT_BRANCHES]
ON DAT01.[_@051] = SMT_BRANCHES.[BranchNbr]
WHERE
DAT01.[_@040] Between '06/01/2006' And '06/30/2006'
AND DAT01.[_@051] = '540'
And DAT01.[_@LOAN#] Like '2%'
And DAT01.[_@550] = '3'
GROUP BY
DAT01.[_@051],
DAT01.[_@550],
DAT01.[_@TP],
DAT01.[_@040],
DAT01.[_@LOAN#]
ORDER BY
DAT01.[_@051]


Where obviously DAT01.[_@LOAN#] is the LoanNum column.

TIA



Kurt

View Replies !   View Related
% Of Total
I have a matrix report built on a database not on the cube.
 
                                                                   2006
                                                  Q1                        





  Volume           MS %
Sub Channel    Product           
   X                 X1                 10                

                      X2                 20
                     Subtotal           30 
  Y                  Y1                 20
                      Y2                 20

                          Subtotal           40 
 
 

I need to calculate the Market Share values which are Volume of the product / Subtotal for example
 
MS% of Product X1 = Volume of X1 / Subtotal i.e  10/ 30 * 100 = 33.33 %
 
How can i achieve this within the report..Can somebody help/advise...
 

View Replies !   View Related
Sub Total
Hi All 
 
I have a situation where I need your help.

 

I have a main group (say group 1) on Company and then I have another group on system number. Now System number can have 5 different options like Email, FAX, Voice, and Mobile. And data look like as below

 

Company A

     System X

                             Email                        10

                             FAX                         20

                             Voice                       25

                             Mobile                      0

     System Y

                             Email                        25

                             FAX                         30

                             Voice                       35

                             Mobile                      10

 

                    Email Total     35

                       FAX Total     50

                   Voice Total      60

                      Mobile Total 10

 

Company B

     System K

                             Email                        1

                             FAX                         2

                             Voice                       2

                             Mobile                      0

     System M

                             Email                        2

                             FAX                         3

                             Voice                       3

                             Mobile                      1

 

                    Email Total     3

                       FAX Total     5

                   Voice Total      5

                      Mobile Total 1

 

How I can do Sub total for sub Group on conditions. I tried

 

=(iif(Fields!delivery_type.Value=" Email",SUM(Fields!total.Value),0)) & VBCRLF &

(iif(Fields!delivery_type.Value=" FAX",SUM(Fields!total.Value),0)) & VBCRLF &

 (iif(Fields!delivery_type.Value=" Voice",SUM(Fields!total.Value),0)) & VBCRLF &

(iif(Fields!delivery_type.Value="Mobile",SUM(Fields!total.Value),0))

 

But this do complete total. 155 etc€¦ I am doing above calculation on Group One Footer€¦.

 

Please help and if you need more info please please let me know. Thanks in advance....

View Replies !   View Related
Getting A Total From A Total
basically I need to sum the value of one group from another group.

 

Is this possible yet?

 

View Replies !   View Related
Sub Sub Total
OK basically what I'm looking for is how to have two subtotals in a report along with a Grand Total.

So it would be something like this:

Revenue

Subtotal

Expense

Subtotal

Grand Total.

 

The grand total works fine, but I'm not sure how to add the subtotals.

View Replies !   View Related
Too Many Writes At Once Causing Time Outs?
Hi everyone! I'm new to this forum and I suspect I'll be using this forum frequently. Good stuff.

Allow this question may appear to be Web-related, I think the problem is with what I'm doing with the database. Please read.

I'm trying to implement a page tracking solution using ASP and SQL 2000. It basically writes a new record to a table every time a user visits a page on the site. It appeared to work fine at first, then I've increasingly been getting time out errors on my pages -- all pointing to the include file that fires the database write.

Here's the code that's referenced on every page:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "dsn=x;uid=y;pwd=z;"

Set objRecordset1= Server.CreateObject("ADODB.Recordset")
objRecordset1.Open "SELECT * FROM table",Conn,1,2
objRecordset1.AddNew
objRecordset1.Fi elds("PAGE") = Left(request.servervariables("SCRIPT_NAME"),100)
objReco rdset1.Fields("QUERY_STRING") = Left(request.servervariables("QUERY_STRING"),100)
objRec ordset1.Fields("DATE") = Date()
objRecordset1.Fields("TIME") = Time()
objRecordset1.Fields("PLATFORM") = Left(request.servervariables("HTTP_USER_AGENT"),100)
obj Recordset1.Fields("REFERRER") = Left(request.servervariables("HTTP_REFERER"),100)
objRec ordset1.Fields("USER_IP") = Left(request.servervariables("REMOTE_ADDR"),20)
If Request.Cookies("TEST")("ID")<>"" Then
objRecordset1.Fields("VISITOR_ID") = Request.Cookies("TEST")("ID")
End If
objRecordset1.Update

Conn.Close
Set Conn=Nothing
%>

After taking out the reference to the above code everything speeds back up. So, I know the performance hit and time out issues have to do with the code above.

Is it the simultaneous write to the table, the constant opening and closing of the recordset, the cursor type, the lock type – or combination of things?

HELP!! Thanks!

David

View Replies !   View Related
Getting Total Of Different Records
I have a SQL data source and i would like to present the total number of different records based on a "status" field.
I have done total records in the past by doing this:
     protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)    {        int RecordCount = e.AffectedRows;            if (RecordCount == 1)            { litRecordCount.Text = "1 record found"; }            else            { litRecordCount.Text = RecordCount.ToString() + " records found"; }        }
This would work, howerver, my SQLdatasource choose * records, and not based on a WHERE condition. Is there any way to total just those with a status of "Initialized" ?
I tried to do it on the Gridview, but then I realized if I have paging on, it will only be on that front page.
thoughts?

View Replies !   View Related
Query To Get Total
Hey all,
I have following query


select Date,

View Replies !   View Related
SQL With A Cummlative Total
Hello all SQL guru's
 
Is it possible to write an sql statement that will calculate a cummlative total of another field in the sql like below. The BOS column is being calculated an the sql and then the *** column is adding them up.
 
Thanks, Gary











VBS_NO
DIV_NO
ITM_NO
 SLL_DLR 
BOS
***%

609
9
53910
 $          36,270,045
1.24%
1.24%

609
9
53985
 $          31,542,973
1.08%
2.33%

609
9
16870
 $          29,583,159
1.01%
3.34%

609
9
24883
 $          29,532,282
1.01%
4.35%

609
9
39674
 $          27,856,172
0.96%
5.31%

609
9
11485
 $          27,598,356
0.95%
6.26%

609
9
39676
 $          26,933,126
0.92%
7.18%

609
9
33354
 $          23,056,438
0.79%
7.97%

View Replies !   View Related
How To Keep A Running Total
Hi there. As you will see from my questions, I am an SQL newb. Idabble but never get to spend enough time to get proficient so base anyfeeedback on that basis please. This is all theoretical information atthis point so I am also going to post this in a MySQL related group. Iwill create some designs and post back to the group if I get anyfeedback I can use.Problem:I would like to be able to keep a running percentage total in a fieldassociated with my users. In order to calculate the totals, I willparsing a text file with entries from my users in it. The parser (AWKetc) will search the file for specific text, compare it to informationin another file and output some entries into a csv file which cansubsequently be imported into the database.The users make posts that are considered good and bad and the ratingpercentage must be based on that. For example, if a user makes 10posts in a day, and 4 of them are considered 'bad' by my criterion, therating should reflect a score of 60% for that day.However, the rating is an ongoing value that will be adjusted daily andI must maintain a running total against all previous posts. So, letssay on day two the same user posts 10 more times and 3 are 'bad', Imust adjust his score to reflect a total percentage rating which wouldthen be 20 posts with 7 being bad for an overall rating of 65% etc.My question is, how should I go about recording and calculating allthis information?Here are my thoughts. I have a users table with a field calledsomething like 'Rating' which stores the overall value (65% etc). Thisvalue would have to be calculated from fields in another table like'Posts' which records each post in 'Good' and "Bad' fields thatincrement. The Good and Bad fields would be incremented (populated)from the text that gets imported etc.Looking for thoughts from experienced db designers please. Thanks alot in advance for any responses.

View Replies !   View Related
Total Replacing
Hi,Is it possible to do from one script? We have a set of user's tables like"tbl%". We can get this list very easy using this script:SELECT name FROM sysobjects WHERE xtype = 'U' AND name LIKE 'tbl%' ORDER BYname;We need to change some column names if these names are in a special listthat we have. What can we do? Use FOR EACH ROW? Or what?So, I need to get a column list for each table and check if every columnname is equal to one of the names from the list and then if YES replace itby something or add some symbol to this name. Terrible or possible?Regards,Dmitri

View Replies !   View Related
Total SQL Users?
I am writing a paper for a computer programming college class and Ineed to know how many companies worldwide use SQL server? How manyservers does this constitute? At what rate is SQL use growing? If youcan't provide close to exact numbers, could you point me to a sourcewhere I could find them?

View Replies !   View Related
Help Finding The Max Total
Hi,

I have the following code

SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2



It prints out:
Wbs2 BillExt
0141 0
0143 0
1217 20580

I want the code to return the wbs2 code '1217' because it has the highest amount in BillExt '20580'.

Can someone help me with this?

Thanks.
laura

View Replies !   View Related
Running Total
Help can anyone see anything wrong with this I am trying to get a running total to show on my form for the users to see, as a team member makes a payment they should be able to see the remaining amount

SELECT SUM(DISTINCT TotalPayments) AS Payment, SUM(AmountDue) AS Due, SUM(FinePayment) AS Fine, [TM #]
FROM dbo.FinePayments_Tbl
GROUP BY [TM #]
HAVING ([TM #] = 9282)

View Replies !   View Related
Running Total In SQL
Good day,

I have been trying to figure out how to get a running total using my sql statement and just can't.
My current table has 2 fields:
Value
Name

Then there are a whole bunch of entries:
31 Jon
23 George
3 Bob
4 Jon
9 Jon
98 Bob

What I would like is from my outputed SQL is something like:
23 Geogre 23
43 Jon 66
101 Bob 167

Can anyone help me, this is really confusing for me and need it to work. I am not able to change the table layout in anyway what so ever and only have the 2 fields, Value Name to use.

Please help me

View Replies !   View Related
Cummulative Total
I'm trying to show a running sum on a set of records ordered by
Date and Order Number to prioritize shipments by first-in-first-out.


CREATE TABLE #TMP (
DUE_DATE DATETIME,
ORD_NUM CHAR(10),
PRODUCT CHAR(3),
TONS REAL)

INSERT INTO #TMP
SELECT '2/23/07', '07026.0030', 'ABC', 3.375
UNION ALL
SELECT '2/23/07', '07047.0059', 'ABC', 3.375
UNION ALL
SELECT '2/23/07', '07053.0080', 'ABC', 3.375
UNION ALL
SELECT '2/24/07', '07045.0030', 'ABC', 2.25
UNION ALL
SELECT '2/25/07','07045.0027','ABC',1.125
UNION ALL
SELECT '2/25/07','07046.0070','ABC',6.75

SELECT T1.PRODUCT, T1.DUE_DATE, T1.ORD_NUM, T1.TONS,
'TTL_TONS'=(SELECT SUM(TONS) AS CUMM
FROM #TMP T2
WHERE T1.DUE_DATE>=T2.DUE_DATE AND T1.ORD_NUM>=T2.ORD_NUM )
FROM #TMP T1

DROP TABLE #TMP


The first 3 records returned show the right numbers in the ttl_tons column,
but then it falls apart after that?

Any hints?

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved