Dividing Counts

Sep 19, 2007

I'm sorry if someone has already posted this but I've looked through a few pages to see if someone had already posted and I couldn't find anything. Anyways, I have two counts and I would like to divide them to get a percentage. Basically I would like to see a percentage of how many tickets are overdue. Here's my SQL:

select count(*)[No. of Tickets Overdue],
case
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
end [Ticket Status]
from whd.priority_type pt
inner join whd.job_ticket j on pt.priority_type_id = j.priority_type_id
where j.status_type_id = '1' and j.deleted = '0' and not j.priority_type_id = '5' and not j.priority_type_id = '6'
group by pt.due_hours
order by pt.due_hours desc
COMPUTE SUM(count(*))

select count(*)[Count2]
from whd.job_ticket jt
where jt.status_type_id = '1' and jt.deleted = '0'-- and not jt.priority_type_id = '5' and not jt.priority_type_id = '6'
--COMPUTE [No. of Tickets Overdue]/[Count2]

I know this isn't correct but basically the commented line at the bottom is what I want to do. I've only been doing SQL statements for a few months now, so I know its novice but any help is appreciated.
Thanks in advance.

View 7 Replies


ADVERTISEMENT

Dividing

Jun 20, 2007

I have four sum statements in a procedure and I want to add two more fields that divide thismonthdeclines / thismonthsales, and prevmonthdeclines / premonthsales. I have tried a few ways and I just cant get the syntax right. Can someone help..


ALTER PROCEDURE dbo.sp_MTD_Declines

(@prevMonthStart datetime, @prevMonthEnd datetime, @thisMonthStart datetime, @thisMonthEnd datetime)

as

select

sum(case when O.orderdate between @prevMonthStart and @prevMonthEnd then D.orderlinetotal else 0 end ) as PrevMonthSales,

sum(case when O.orderdate between @thisMonthStart and @thisMonthEnd then D.orderlinetotal else 0 end ) as ThisMonthSales,

sum(case when O.orderdate between @prevMonthStart and @prevMonthEnd then case when O.orderstatus = 'CC Declined' then O.ordertotal end else 0 end) as PrevMonthDeclines,

sum(case when O.orderdate between @thisMonthStart and @thisMonthEnd then case when O.orderstatus = 'CC Declined' then O.ordertotal end else 0 end) as ThisMonthDeclines



from exigo_data_sync.Orders O INNER JOIN
exigo_data_sync.OrderDetail D ON O.OrderID = D.OrderID

where O.orderdate >= @prevMonthStart

View 11 Replies View Related

Dividing Up Result Set Into 4 Groups

Feb 1, 2007

Hello,

I am trying to take my result set and dividing up the results into 4 equal
groups

I have included my code and a partial result set.
All of your input and help is appreciated as I am new at this


select accounts.id, sum(trans.amount) as 'income'
from jom.dbo.accounts
join jom.dbo.trans
on trans.accounts_id = accounts.id
where trans.amount > 0
group by accounts.id


id income
471098.00
471108.00
4711112.00
4711216.00
4712516.00
4712610.00
471278.00
4712816.00
4712942.99
47142139.00
471438.00
4714452.00
471455.00
47159625.94
4716075.00
471618.00
471628.00
4717625.00
4717720.00
4717816.00
4717928.00
4719274.94
47193248.00
47194176.00
4719539.99
4719616.00
4720916.00
4721046.00
47211177.00
472128.00
4722644.00
47227178.99
4722824.97
4722927.00
4724216.00
4724324.00
472448.00
4724549.00
4724670.00
4725919.95
4726010.00
4726122.00
4726240.00
472768.00
4727768.00
472788.00
472798.00
4729316.00
4729456.00
4729510.00
4729663.00
4730934.00
4731126.00
4731218.00
4732624.00
4732716.00
47328215.00
473298.00
4734319.95
4734419.95
4734519.95
4734642.00
4735962.00
4736016.00
4736175.00
4736240.00
473638.00
4737619.95
4737719.95
4737824.00
4737913.00
473938.00
4739498.95
4739563.95
4739625.95
4741021.00
4741147.00
47412229.00
474138.00
4742618.00
474278.00
4742860.00
4742944.95
4744393.00
4744420.00
4744510.00
4744618.00
4746028.00
4746116.00
4746232.00
4746348.00
4747622.00
47477110.00
4747816.00
4747936.00
474808.00
4749398.95
4749460.00
4749524.00
4749698.00

View 2 Replies View Related

Dividing Two CAse Statements

Mar 9, 2007

How do I divide these two case statements:

1. sum(CASE WHEN o.sap_apc_indic is null THEN wip.wip_oth_exp_amt
ELSE 0
END) Markup,

2. sum(CASE WHEN o.sap_apc_indic is not null THEN wip.wip_oth_exp_amt
ELSE 0
END) AP,

View 1 Replies View Related

Help Dividing Two Values From Different Tables.

Jul 14, 2007

Hi, I am completly new in ASP , but, due the necessity of the production in the company I work, I need
urgently to learn do deal with scripts and SQL.

I did a script that shows on the production screen the consume tax of the gas instantly on time and it

worked. Now I have to do a table formed by the DIVISION of a valor V from one table by the valor V in

another table, mas the values must be taken at the same time do give me the gas consumed by ton produced.

Explaining in details:

I have two tabels:

1 - PDE#HD#Consumption#RP_Consumption_Gas
2 - PDE#HD#SpeedCurves#Productivity

Inside each of these tables, there are two columms with the same name for both -> V, T. The V columm of the

table PDE#HD#Consumption#RP_Consumption_Gas gives me the cas consumed and the V table of

PDE#HD#SpeedCurves#Productivity the productivity. The V1/V2 graph must be ploted in a 24 hours period. What

happens is that if I do it for 24 houres, I mean, 1 day, the graph is ploted completly wrong in time! If I

do the same thing, but using only one columm, not the division by another, it works! Why this is happening ?

What is wrong with the following script ?? I need to know the answer for this as fast as possible! If you

need more details, I will give.

The script:

DayForm = Request.Form("D1")

Dim hoje
Dim dia

If DayForm = "" OR DayForm = 0 Then hoje = true

If hoje Then _
Set rs = conn.Execute("SELECT

convert(varchar,PDE#HD#Consumption#RP_Consumption_Gas.T,108),(PDE#HD#Consumption
#RP_Consumption_Gas.V/PDE#HD#SpeedCurves#Productivity.V) As Consumo FROM

PDE#HD#Consumption#RP_Consumption_Gas INNER JOIN PDE#HD#SpeedCurves#Productivity ON

PDE#HD#Consumption#RP_Consumption_Gas.T = PDE#HD#SpeedCurves#Productivity.T WHERE

PDE#HD#Consumption#RP_Consumption_Gas.T > DATEADD(dd, -1, GetDate()) ORDER BY

PDE#HD#Consumption#RP_Consumption_Gas.T") _
Else _
Set rs = conn.Execute("SELECT

convert(varchar,PDE#HD#Consumption#RP_Consumption_Gas.T,108),(PDE#HD#Consumption
#RP_Consumption_Gas.V/PDE#HD#SpeedCurves#Productivity.V) As Consumo FROM

PDE#HD#Consumption#RP_Consumption_Gas INNER JOIN PDE#HD#SpeedCurves#Productivity ON

PDE#HD#Consumption#RP_Consumption_Gas.T = PDE#HD#SpeedCurves#Productivity.T WHERE

PDE#HD#Consumption#RP_Consumption_Gas.T > convert(date, DATEADD(dd, " & DayForm & ", GetDate())) AND

PDE#HD#Consumption#RP_Consumption_Gas.T < convert(date, DATEADD(dd, " & DayForm+1 & ", GetDate())) ORDER BY

PDE#HD#Consumption#RP_Consumption_Gas.T ")

View 2 Replies View Related

Dividing Two Count() Results

Jul 20, 2005

Just a quick question, how do I divide the results of these two queriestogether ?(select count (*) as ontime from schedule where actualarrivaldate <=estimatearrivaldate)(select count (*) as total from schedule)Thanks for any helpRobert

View 2 Replies View Related

Dividing Subtotal In A Matrix

Jan 12, 2007

I want to divide the subtotal of a matrix. Can I do ?

View 1 Replies View Related

Percentage, Dividing By Zero … #Error … ???

May 6, 2008

Hi
1: I€™m trying to get a percentage value (Left to Target) but I€™m getting €œ#Error€? in the Preview.
This is what I have:
=IIF(Fields!SalesTarget.Value Is Nothing , 0, ((Fields!Rev.Value)-(Fields!SalesTarget.Value))/(Fields!SalesTarget.Value))
How can this not work?
I looked at a previous thread in here but I couldn€™t get anything from there L
Kind Regards

View 12 Replies View Related

Dividing Integers To Produce A Percentage

Aug 27, 2003

I'm embarassed I haven't figured this out already but here goes.

Lets say you need a percentage from dividing two integers for example

"select 2000/4000"

This will produce a zero and I'm assuming that is because of the datatypes involved (the values are coming from columns where the datatype is int)

I've tried converting the values to decimal types but I keep getting overflow erros unless I use very small values.

As always, thanks VERY MUCH for the kind advice.

View 2 Replies View Related

T-SQL (SS2K8) :: Dividing A Number With Remainders

Jan 29, 2015

SQL Server (TSQL) how to divide a number by either 2,3,4, or 6 and spreading it across that number of rows depending on what we are dividing the number by where it comes back to the original number...That is confusing I know so let me break it down...

Bill for 143.23 that will be paid out through 2 months...When you divide that by 2, you come back with 71.62, but if you multiply that number by 2, you come back with 143.24, not the amount for the bill...the end result has to be 71.62 for month1 and 71.61 for month2...Basically when there is a remainder, that has to be applied to the first month...

143.23:
Month1 = 71.62
Month2 = 71.61

Another example...Same amount but have to divide by 6

143.23
Month1 = 23.88
Month2 = 23.87
Month3 = 23.87
Month4 = 23.87
Month5 = 23.87
Month6 = 23.87

View 9 Replies View Related

Dividing The Sale Amount By Month

Jul 3, 2006

Hello,

I want to process a row from a source table by dividing the sales amount in that row over the period of the sale by month.

For instance if an item is sold for 500$ and it's duration is 5 months from 1/15/2004 till 6/15/2004, I want to divide the sale amount by month as follows:

Month 1: 50$

Month 2: 100$

Month 3: 100$

Month 4: 100$

Month 5: 100$

Month 6: 50$

I know I can create a script component and do the calculation for each month and insert 6 records in the fact table for each row in the source table, where each record holds the amount for the corresponding month. However I was wondering if there is another technique that utilizes the components of SSIS to do it more efficiently.

Thanks,

Grace

View 4 Replies View Related

Dividing Bytes By 1 Million To Get Megabytes In Select

Feb 13, 2005

Hello!

I have a table that contains a field containing the total bytes for a file. I am displaying the information in a datagrid but need to display the information in MB. If I divide by 1,000,000 in my select statement as such:SELECT cs_fileSize / 1000000 AS MB, cs_fileSize
FROM t_client_spotsI get the following results:

MB | cs_filesize
1 | 1899602
1 | 1782281

I would like the results to be:

MB | cs_filesize
1.89 | 1899602
1.78 | 1782281

Any suggestions?

Thanks in advance!!

View 2 Replies View Related

Dividing Two Numbers Truncates After 4 Decimal Places

Dec 19, 2000

I have two fields - both defined as money.
When I divide them, SQL Server truncates the result after the 4th decimal point.
So SQL Server says: 370.45 / 3,391,517.85 = 0.0001
I want to achieve: 370.45 / 3,391,517.85 = 0.00010922837... etc.
The field the result is going into is defined as decimal(20,18)

I've tried using "cast(1stmoneyfield as decimal(20,18)) / cast(2ndmoneyfield as decimal(20,18)) as dividednumber", but SQL Server reports back errors about null values and Arithmetic overflow and terminates.

I'm at a loss as to how to solve the problem. Any suggestions please?

View 1 Replies View Related

T-SQL (SS2K8) :: Dividing Two Rows Within CASE Statement

Jul 28, 2014

I was given the task to come up with a result set based on certain criteria:

Please add one row for each offer code

1) Opt-in rate by offer code: This can be calculated by dividing XXX Inventory with lead / XXX Inventory (A)

The report should read something like below:

Example result set:

Log Date: OfferLetter OfferCode DailyCount

2014-07-20 A XXX Inventory (A) 108
2014-07-20 A XXX Inventory with lead 54
2014-07-20 A XXX Inventory Opt-in Rate: 50%

There are 12 different groupings and OfferLetter A is just one of them.

Below is the code that is written to date:

DECLARE @Start datetime = DATEADD(day, DATEDIFF(day, 0, GETDATE() - 8), 0)
DECLARE @End datetime = DATEADD(day, DATEDIFF(day, 0, GETDATE() - 1) + 1, 0)
DECLARE @Today datetime = DATEADD(day, DATEDIFF(day, 0, GETDATE() - 1), 0);

SELECTDT.OfferCode, DT.OfferCodeDesc
INTO#tempOfferCodes

[Code] ....

The issue I'm having is that the values I need to divide by are in fact, a result set from the CASE statement. It's been a long time since I've done anything like this.

View 2 Replies View Related

T-SQL (SS2K8) :: Dividing Two Values Of Same Column / Grouping

Aug 4, 2014

I'm trying to divide two values from separate rows. Each row is a separate UNION statement.

2014-08-03 00:00:00.000NKBB (N) - Total Offers 1218 UNION (A)
2014-08-03 00:00:00.000NKBB (N) - With Lead 301 UNION (B)
2014-08-03 00:00:00.000NKBB (N) - Without Leads 917 UNION (C)

In the below example, I would like to divide KBB (N) - With Lead (UNION (B)/KBB (N) - Total Offers UNION (A)

What would be the best way to accomplish this?

View 1 Replies View Related

Dividing A Large Flat File Into Small Files

Jul 16, 2007

Hi ,

Is there any method by which I can divide the large flat file into certain number of small files keeping the header in each of the sub files?

Regards,

Prash

View 4 Replies View Related

SQL Server 2012 :: Create A New Column By Dividing 2 Columns In The Query?

Mar 12, 2015

I have the following query that displays 2 values. I want to add a column with the percentage ([Providers With Security]

/ProviderTotal) * 100
SELECT (SELECT COUNT(DISTINCT NPI) FROM HS140_Rpt_Tmp_ForSummary WHERE Market = s.Market) AS ProviderTotal,COUNT(DISTINCT NPI) AS [Providers With Security]
FROM HS140_Rpt_Tmp_ForSummary s
WHERE s.[Security] = 'Yes'
GROUP BY Market

How can I do this?

View 1 Replies View Related

Row Counts

Feb 5, 2007

If I right click and browse the properties for the table I can get the value of rows. But for the same table if I do select count(*) from table the value does not match the table properties rows. Please can some one tell me why this is so?

SQL Newbie

View 2 Replies View Related

Counts By Groups

Jan 8, 2007

I expect to get a record below with a count of 0 (and I do), but when I take the comments out (--) of lines 1 & 6 I don't understand why I get no records at all. I need to be able to see all teams in EvalAnswers even if none of the records satisfies the where clause.1 select Count(*) as cnt--, TeamID
2 from EvalAnswers
3 where CoID=@CoID
4 and EvaluatorID=@EvaluatorID
5 and (Scr0=0 and Sugg0 is NULL)
6 --group by TeamID
7

View 4 Replies View Related

Need Help Bracketing Counts

Sep 6, 2007

I need to create a view that shows the number of times that clients made payments, how many clients, and how much they paid over a period of time. I'm not sure if I can use a case for this. How can I put something like this together?
I have a tblClients with a clientid field
I have a tblPayments with the clientid, pmtdate, and pmtamount
For example:
1 Payment ----- 23 Clients ----- $16000
2 Payments ----- 12 Clients ----- $32000
3 Payments ----- 4 Clients ----- $13000
etc...

View 3 Replies View Related

GETTING ROW COUNTS FROM DATABASE

Nov 15, 2000

I need a procedure that will go to a database and give me all the row counts for the user tables.

Does anyone know how I can get this?

Thanks,
Dianne

View 3 Replies View Related

SQL Is Giving Different Row Counts

Apr 20, 2004

Hi,

...giving a very 'summarized' scenario of the problem I have trying to
solve all day (make it 2 days now).

Below are the relevant DDLs... I am not listing the DDLs of my other tables:

CREATE TABLE [SalesFACT] (
[varchar] (10),
[TransDate] [varchar] (10),
[SaleAmt] [float],
[CustCode] [varchar] (10)
. . .
)

I populate the above table via a DTS and have checked and have verified that correct data is coming in... I also have a product master table; for business reasons we can have the same product created with different ProductCodes though the rest of the Product details are EXACTLY the same. We have covered this using a field named 'UniqueProdCode'.

CREATE TABLE ProdMaster(
[ProdCode] [varchar] (10),
[ProdName] [varchar] (35),

[UniqueProdCode] [varchar] (10),

... many other product fields e.g. unit price, category etc...
...
)

First a small Request:
Please note that I have NOT defined links between my tables (in the diagram editor) nor have I defined Primary keys (or any constraint) for any of the tables. When you kindly reply, please suggest I should define primary keys for the tables and also link them in the diagram editor.


[u]THE PROBLEM:
When I do a count(*) query on the table 'SalesFACT', I get the correct number of records.

If I create a view, add table 'SalesFACT' and table ProdMaster, link the
UniqueProdCode field of table 'SalesFACT' with the UniqueProdCode field of ProdMaster (so that I can also get the name, category, etc. for the products in the SalesFACT), and run a count(*) query I get a much higher and incorrect number of rows. The SQL for the view is:


SELECT dbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode,
dbo.SalesFACT.SaleAmt
FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.UniqueProdCode


Kindly note that I have checked and the contents of the table SalesFACT' UniqueProdCode field DOES contain the correct data i.e. it contains the UniqueProdCode and NOT the ProdCode.

But if i link the "wrong fields", I get the correct count count :confused: i.e. I create a very similar view (as mentioned above) but instead link the UniqueProdCode of table SalesFACT with the ProdCode field (not the UniqueProdCode field) of ProdMaster
table I get the correct count. This is really driving me nuts and I just can't understand what's going on and why the "REVERSE" logic. For your convenience here is the SQL for the 2nd view:


SELECTdbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode,
dbo.SalesFACT.SaleAmt
FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.ProdCode


Please guide... I have run out of all the things that I could check and thus this SOS and F1

Billions of thansk in advance.

View 2 Replies View Related

How To Do Multiple Counts

Dec 12, 2006

I just inherited an app, where I have two tables that look like this:
[Owners]
--------
Owner
...

[Cases]
-------
Owner
Status
Assigned
...

I need a query to get results that look like this:
[Results]
--------
Owner
# of cases records where Status='Open'
# of cases records Where Status='Pending'
# of cases records WHERE Status<>'Closed' AND Assigned=''

I have one query that works already, but it's using several nested selects. I know I ought to be able to do this using group by instead, and I like to know how.

View 4 Replies View Related

Multiple Counts

Jan 24, 2006

I am creating a database for a soccer league.

I would like to write a query that would give me results in a league table form .

How could I combine different count queries such as:

select hometeam, count(*) as homegames from matches where comp="en1pp" group by hometeam
order by hometeam

select hometeam, count(*) as homewins from matches where homescore>awayscore group by hometeam order by hometeam

into one query giving a three column result homteams, homegames, homewins.

Thanks

View 6 Replies View Related

Do Lots Of COUNTs

Sep 19, 2006

Hello :)

I seem to have somehow got myself into a situation where I'm having to run the following SELECTs, one after another, on a single ASP page. This is not tidy. How can I join them all together so I get a single recordset returned with all my stats in different columns?

SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years'
SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL
SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy'
SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULL

View 8 Replies View Related

Compare Row Counts

Oct 6, 2006

Hi all,

I have a need to compare the number of rows returned from table A when it is joined to table B to the number of rows returned when there are no joins involved. If the number of rows returned are the same, then I need to proceed to execute my next step else end.

So, If RowCount A = RowCount A when A joined to B
THEN Goto Next Step
Else End

I need to put the above logic in a sp that I want to execute using a job.

Help is appreciated.

V

View 5 Replies View Related

Counts On Subquerys

Apr 22, 2004

OK I have a query that bring back a unique identifier and text value
for instance

Select Distinct global_id, Page_url from PageList

global_id page_url
---------- ---------------------------------
4306549 /true/attitudes.htm
1460753 /true/current/answerthis.htm
4303667 /true/current/answerthis.htm
4306549 /true/current/answerthis.htm

Now I want to do a count of the page_url.
Can this be done in one SQL statement using a sub query of some kind

View 6 Replies View Related

Mixing Counts, Min Or Max ?

Nov 28, 2007

Hi I have this

select emailid,
count(emailid) as 'No.of occurences',

FROM tableA
where start_moment between '2007-11-01' and '2007-11-02'
GROUP BY emailid
having (COUNT(emailid) > 1)



Fair enough this returns the emailid along with the amount of times it appears (all greater then 1 .. duplicated in other words)

My question is , there is also a start_moment field in tableA
so i need to get the max or min start_moment along with the above result?

View 3 Replies View Related

Retention Counts

Dec 4, 2007

I have members in a database who have paid thru dates. I am creating retention reports

I created a cross tab in Crystal (using SQL) that counts records that paid within a certain year. I need to create a script that will let me find when members skip payment for a year. Any ideas?

I was thinking of running a count of all paid (Activity) records, but still kind of stuck.

DZ

View 9 Replies View Related

Getting Table Counts

Jul 20, 2005

I want to get a resultset of every table in the database, with thecurrent record count of each. What is the easiest way to do this?I can get the list of tables with:Select s.name from sysobjects s where xtype = 'U'each s.name is a table name, but I'm not sure how to join a record countcolumn to the resultset.Thanks,RickN

View 4 Replies View Related

Record Counts

Oct 8, 2006

Hi,

I have 10 databases created. each data base has arround 100 tables . i need to keep track of the number of records in each table of 10 databases and the last modification date on that table. The solution should be programatically by running a T-sql program or any stored procedures or any other but mechinical.

the output should specify the following:

Table #number_of_recs Last_date

database_name.Table_name ###,###,### DD/MM/YY



I will appreciate any assistance in this regard.

thanks,

View 1 Replies View Related

Multiple Counts In A Query

May 20, 2008

I have a table that is linked to other tables in one to many relationship.I have a query using LEFT OUTER JOINs to join the tables together.There are multiple counts in the query and count numbers are messed up.(if there are 4 records from one table and 3 from the other  - it shows 12 as the count)  Your help is appreciated. 

View 2 Replies View Related

Multiple Counts In A Sql Statement

Jan 26, 2004

How would I create a sql statement with 3 or 4 counts which would represent 3 or 4 different columns in a datagrid?

For example

SQL = "SELECT Count(department_id) as "totals1" FROM nonconformance WHERE department_id = '1'"


How would I make additional counts in this SQL statement that looks for when department_id=2 and 3 etc....

Thanks

View 7 Replies View Related







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