I have three fields date, low, high. I need to calculate the midpoint
of low and high and display it.
Date,Low,High
20071106,92.03,92.13
20071106,88.77,88.87
20071106,90.20,90.30
20071106,95.21,95.31
20071106,93.13,93.23
20071106,91.01,91.11
Hi,
I am trying to execute a query which calculate % on group by. here is the query
SELECT C.USR_HIGHEST_DEGREE,COUNT(DISTINCT C.MASTER_CUSTOMER_ID) AS [# MEM],(convert(numeric(5,2),COUNT(DISTINCT C.MASTER_CUSTOMER_ID))
/ (
SELECT convert(numeric(5,2),COUNT(CC.MASTER_CUSTOMER_ID)) FROM MBR_PRODUCT AS MPP WITH (NOLOCK) INNER JOIN PRODUCT AS PP WITH (NOLOCK) ON MPP.PRODUCT_ID = PP.PRODUCT_ID INNER JOIN ORDER_MASTER AS AA WITH (NOLOCK) INNER JOIN CUSTOMER AS CC WITH (NOLOCK) ON AA.SHIP_MASTER_CUSTOMER_ID = CC.MASTER_CUSTOMER_ID INNER JOIN ORDER_DETAIL AS BB WITH (NOLOCK) ON AA.ORDER_NO = BB.ORDER_NO ON MPP.PRODUCT_ID = BB.PRODUCT_ID WHERE (CC.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (BB.CYCLE_END_DATE >= GETDATE()) AND (AA.ORDER_STATUS_CODE = 'A') AND (BB.LINE_STATUS_CODE = 'A') AND (BB.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MPP.LEVEL1 IN ('NATIONAL'))
)*100 AS [%]
FROM MBR_PRODUCT AS MP WITH (NOLOCK) INNER JOIN PRODUCT AS P WITH (NOLOCK) ON MP.PRODUCT_ID = P.PRODUCT_ID INNER JOIN ORDER_MASTER AS A WITH (NOLOCK) INNER JOIN CUSTOMER AS C WITH (NOLOCK) ON A.SHIP_MASTER_CUSTOMER_ID = C.MASTER_CUSTOMER_ID INNER JOIN ORDER_DETAIL AS B WITH (NOLOCK) ON A.ORDER_NO = B.ORDER_NO ON MP.PRODUCT_ID = B.PRODUCT_IDWHERE (C.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (B.CYCLE_END_DATE >= GETDATE()) AND (A.ORDER_STATUS_CODE = 'A') AND (B.LINE_STATUS_CODE = 'A') AND (B.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MP.LEVEL1 IN ('NATIONAL')) GROUP BY C.USR_HIGHEST_DEGREE
My problem here it , query gives error. I would appreciate if someone can give me any idea how to go about this. I tried calculating % outside sql but still doesn't work.
Any help would be appreciate.
I have an SQL statement which returns the Top 10 states with the number of visitorsSELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors FROM [Customer] WHERE Customer.year = '2006' GROUP BY Customer.state ORDER BY COUNT(Customer.state) DESCSo far this is what I havestate| visitorsMD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9But what i need is to calculate the total for the Visitors column in my SQL so that is like soMD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9Total Top 10995279.3Total for All Years12555100I tried using the sum but I was only getting one value and not the rest...So how can i accomplish this?Thank you
View 8 Replies View Related
Hi all,
I'm having the table in the following structure,
UId - int
Pct - int
Amt - money
Example
UId Pct Amt
12 25 1500
12 30 2500
12 45 2000
i want to calculate to calculate the sum of amount for the UId 12 in the following manner,
sum of ( Amt * (Pct/100)) for UId = 12
how to write query in sql server 2000?
when i calculate if the value (Pct/100) < 0 that is 0.25 then it will take it as 0 so i'm getting all the values as 0.
Thanks!
I have a table that has a providername and totals field.
I want to be able to calculate the MTD and YTD totals for each provider.
EX. Provider MTD YTD
Bob 100.00 300.00
But I am not sure how. I can get both totals seperately, but not n the same line....
Thanks
Ron
Can anyone help me with how to calculate the exact age.
Thanks in advance.
I have a DOB field in my sql table, does anyone know how to display that field as an actual age in a view or a SP?
View 8 Replies View RelatedI want to calculate rate as percentage using code below:
SELECT OrderBy, NumShiped/Total AS Rate FROM Order
I always got Rate = 0 even thought NumShiped = 80 and Total = 100
What is problem?
Hello,
I have a date of birth column in my table and I want to calculate age based on this date. Here is the code I am trying to use in my select statement.
DateDiff ("yyyy",(a.date_of_birth),CurrentDate)
Apparently CurrentDate is not recognized by SQL. What is the best way to do this?
I have the table with list of 1500 employees with the following headings.
Surname, Ini,title,gender,Race,IdNo,FirstName
Then I need to calculate the age of each employee but I am stuk.
Please assist me.
Hello every one, I am trying to get the total percentage of a column
Regions
Workbooks Required
Workbooks Sent
Workbooks Returned
Workbooks Complete
A
20
21
20
18
B
33
33
33
30
C
19
29
18
16
D
9
18
8
8
Totals
Thanks in advance for any tips/solutions.
Need to calculate the Grade age based on the birthdate and Nov month and 30th Day of the current year.
I have a working datediff statement but I need to always but in the current year. I would like to have the statement get the current year. Then if the age is greater than xx and less than xx your age level is "xyz"
This works DateDiff("d" [Birthdate], 11/30/2004) /365.25 will return the age.
I want to replace the 2004 with a getdate yyyy so I do not need to maintain this statement.
Thanks in advance of a reply
Gary
Hi All!
I need a query to find all dates from today to one-year back.
If I start from today day I need find all dates until 11/12/98.
Thanks a lot.
Greg.
hi all;
I have seen posts on numerous websites to get the age from a dob column I am faced with the same problem but get this error:
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals divide, type equals datetime.
when I run the following query:
SELECT ROUND((currentdate - DOB)/365.24,0) FROM FL1_A_Backup
All I need is a simple statement that calcualtes the age from the dob column(smalldatetime) then creates a new row which it puts the data into.
I have already created a CurrentDate column which has a (getdate()) value attached to it.
This is nothing fancy or complicated and I just need to figure out from the year I'm not really concerned about if the person has a bday tomorrow or so on.
I know I am probably doing something wrong with this query and am not shy to say that I have only been using ms sql for a couple of months (basic select statements).
I am having trouble creating a sp for the following situation:
The database contains a record of the mileage of trucks in the fleet. At the end of every month, a snapshot is collected of the odometer. The data looks like this:
Truck Period Reading
1 1/31/03 55102
2 1/31/03 22852
1 2/28/03 62148
2 2/28/03 32108
1 3/31/03 69806
2 3/31/03 52763
How can I calculate the actually miles traveled during the month in a query?
TIA,
Rob
b/w two colums?
i have two numberic columns
i want percentage of column2/column1
They are on separate table.. l am intended to creat view with my results.
thanks
I have requirement in which i need to calculate percentage value based on billamount and concession amount..to calculate the percentage part and show the o/p.
SELECT PP.KID_ID_NO_V,(PP.FIRSTNAME_V + SPACE(1) + PP.LASTNAME_V)AS [PATIENT_NAME],BM.TOTAL_AMOUNT_M AS [BILL_AMOUNT],BM.CONCESSION_AMOUNT_M AS [CONCESSION_AMOUNT],BM.BILL_AMOUNT_M AS [TOTAL_AMOUNT],
FROM BILL_MASTER BM
INNER JOIN PATIENT_PROFILE PP ON BM.PATIENT_ID_N=PP.PATIENT_ID_N
WHERE BM.BILL_SETTLED_C='Y'
[code]....
What is the best way, or a good way, or even a way to calculate age using a DOB field when the DOB field is sometimes NULL so that the result does not return NULL? Would prefer to just return an empty string.
I have this, which works, but returns NULL when DOB field is NULL.
floor(datediff(day, DOB, CURRENT_TIMESTAMP) / 365.25) as Age
And this obviously returns an error
isnull(floor(datediff(day, c.DOB, CURRENT_TIMESTAMP) / 365.25),'') as Age.
I prefer not to write a function.
Hi, Please help.
I need to calculate percentage [COUNT(ALERT_RECEIVED_DATE) FRAUDCT,over SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK] I tried my best but I cant come up with the solution.
Please, help.
Thk
SELECT
CONVERT(nvarchar(10),dateadd(d,-day(ALERT_RECEIVED_DATE) + 1,ALERT_RECEIVED_DATE),101) PERIOD,
COUNT(ALERT_RECEIVED_DATE) FRAUDCT,
SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK,
SUM(CASE WHEN FRAUD_DECISION ='D' THEN 1 ELSE 0 END) DECLINED,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) PENDING_EXCEPTION,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION='D' THEN 1 ELSE 0 END) DECLINED_EXCEPTION
FROM
TBLFRAUDFINDER O
Hi, Please help.
I need to calculate percentage [COUNT(ALERT_RECEIVED_DATE) FRAUDCT,over SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK] I tried my best but I cant come up with the solution.
Please, help.
Thk
SELECT
CONVERT(nvarchar(10),dateadd(d,-day(ALERT_RECEIVED_DATE) + 1,ALERT_RECEIVED_DATE),101) PERIOD,
COUNT(ALERT_RECEIVED_DATE) FRAUDCT,
SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK,
SUM(CASE WHEN FRAUD_DECISION ='D' THEN 1 ELSE 0 END) DECLINED,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) PENDING_EXCEPTION,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION='D' THEN 1 ELSE 0 END) DECLINED_EXCEPTION
FROM
TBLFRAUDFINDER O
Hi, everybody,
I'm new to SQL, hope you could help me.
I have a table named salary in sql server side, it contains several fields such as housing,overtime, netwage,totalnet gross, tax, factor...etc, the totalnet=netwage+housing+overtime, and gross=totalnet+tax.etc... and tax is calculated from a funcion below:
function taxn(ran:double):double;
var
saSum: Double;
begin
saSum := ran - 1600;
if saSum <=0 then result := 0
else if saSum <= 475 then Result := (saSum-0)/(1 - 0.05)*0.05-0
else if saSum < 1825 then Result := (saSum-25)/(1 - 0.1)*0.1-25
else if saSum < 4375 then Result := (saSum-125)/(1 - 0.15) *0.15-125
else if saSum <= 16375 then Result := (saSum-375)/(1 - 0.2)*0.2-375
else if saSum <= 31375 then Result := (saSum-1375)/(1 - 0.25)*0.25-1375
else if saSum <= 45375 then Result := (saSum-3375)/(1 - 0.3)*0.3-3375
else if saSum <= 58375 then Result := (saSum-6375)/(1 - 0.35)*0.35-6375
else if saSum <= 70375 then Result := (saSum-10375)/(1 - 0.4)*0.4-10375
else if saSum > 70375 then Result := (saSum-15375)/(1 - 0.45)*0.45-15375;
end;
ran=totalnet.
when the table loaded into a DBGrid, it is filled with housing, overtime and netwage, how to calculate other fields and display all in the DBGrid?
I tried to use onCalcFields events as below:
procedure TForm1.ADODataSet1CalcFields(DataSet: TDataSet);
var
totalnet,tax,taxedsalary:extended;
begin
with DataSet do
begin
FieldByName('totalnet').Value :=fieldbyname('housing').Value+fieldbyname('overtime').Value+fieldbyname('netwage').Value;
totalnet := fieldbyname('totalnet').Value;
fieldByName('taxedsalary').Value := roundto(taxbase(totalnet)+0.0001,-2);
tax:=roundto((taxn(totalnet)+0.0001),-2);
fieldbyname('totalgross').Value := totalnet+tax;
fieldbyname('tax').Value := tax;
fieldbyname('taxrate').Value :=floattostr(100*taxraten(totalnet))+'%';
fieldbyname('factor').Value:=factorn(totalnet);
end;
end;
but nothing happened.
Could you shed some lights?
THank you in advance.
Hi, I'm having huge troubles calculating overtime work in my db.
I have a table containing working hours that looks like this:
Userid Login Logout
---------------------------------------------------
2 21.03.2007 12:00:0021.03.2007 23:00:00
2 22.03.2007 08:00:0021.03.2007 17:00:00
To complicate things I also have a table that stores overtime rates:
From To Percent
----------------------------
00:00:00 20:00:00 0
20:00:00 22:00:00 20
22:00:00 24:00:00 50
(That percentage part isn't that important in this case.)
Now here's the tricky part, I want to transform those tables into something like this:
Userid 00:00-20:00 20:00-22:00 22:00-24:00
------------------------------------------
2 8 2 1
2 9 0 0
I'm pretty sure this requires a complicated sql query, but I have no clue where to start,
can someone push me in the right direction?
Any help is appreciated.
I need to calculate Median on each calculated result from the query below. There is one Median function available in SQL2K but it is not working. Can anyone help me in this regard.
------------------------------------------------------------------------
SELECT INS.Code As [code],INS.FinYr as [YEAR], ' ' As [FIRE BUSINESS], (INSRev.FI_NetPremLessIns / INSRev.FI_GrPremium) * 100 As [Rention Ratio],
(INSRev.FI_NetClaimPaid/INSRev.FI_AdjNetPremium)*100 As [Claim Ratio], ((INSRev.FI_AgencyCommPaid+INSRev.FI_ReInsCommPaid+INSRev.FI_MgmtExpenses+INSRev.FI_OthExpenses)/INSRev.FI_AdjNetPremium)*100 As [Expense Ratio],
((INSRev.FI_NetClaimPaid/INSRev.FI_AdjNetPremium)*100)+(((INSRev.FI_AgencyCommPaid+INSRev.FI_ReInsCommPaid+INSRev.FI_MgmtExpe nses+INSRev.FI_OthExpenses)/INSRev.FI_AdjNetPremium)*100) As [Combine Ratio],
(INSRev.FI_ClosingBal/INSRev.FI_NetClaimPaid) As [Unexpired Risk Reserve to Net Claim(x)],(INSRev.FI_MgmtExpenses/INSRev.FI_AdjNetPremium)*100 As [Management Expenses to Adj. Net Premium],
(INSRev.FI_AgencyCommPaid/INSRev.FI_AdjNetPremium)*100 As [Agency Commissioned to Adj. Net Premium]
FROM
(InsuranceGen As INS LEFT JOIN INSURANCEGen As INS1 ON (INS1.FinYr=INS.FinYr-1 AND INS.Code=INS1.Code))
LEFT JOIN INSRevGen as INSRev ON (INS.Code=INSRev.Code AND INS.FinYr=INSRev.FinYr) WHERE INS.Code IN ('ABC1','ABC2','ABC3') AND INS.FinYr=2005 ORDER BY INS.Code, INS.FinYr
----------------------------------------------------------------------
Thanks
Hi,
In a table there is a column as Formulae varchar(50), When the data getting inserted column can have data as "(TP*12.00)+12". So in procedure user send in parn TP value.
First of all the TP has to be replaced with In parn value, then procedure need to value then return final value.
For Ex:
TP [In Parn] = 1.5
"(TP*12.00)+12" => (1.5*12.00)+12
return value as 30
pl. help how can I achieve this,
Thanks
Sreenu
Hi
I am using the below code in sql procedure.How do I add 7% of the price and then VAT for the total.
CAST(CAST(cast(Price * 1.175 as decimal(19, 2)) as INT) + 0.99 AS DECIMAL(19, 2)) as [item-price],'
Advance thanks
hello i`m rather new in sql and i have to do something simple for u i guess.
i have 3 columns a)date of birth b)current date c)final age.
i have the values of the first 2 columns and i want column c to be calculated automaticaly when i enter the values of the first 2 columns. example.
a)20/03/1972 b)07/02/2008 c)?? (should be 36).
if you could help me i would appriciate very much.
thanks in addvance
Hi,
I wish to create a matrix with multiple rows in the main data cell and a subtotal at the end of the row. The first row in my matrix main cell is just a count of records, whereas the 2nd row is a % of the value in the 1st row compared to the total of that row. I have 5 columns in the matrix as below (ignore rounding issues):
Status
A
B
C
D
E
Total
01/01/2007
Number
9
32
3
13
0
57
% of Total
15%
56%
5%
24%
0.00%
100.00%
Can someone advise the best way to calculate the % cells in this example?
Thanks
Hi,
I want to get the no of saturdays that are coming between given span of days,
ex:- if i give dates as 04/05/08 and 04/25/08 , i want to get the no of saturdays between these dates
can anyone help
Using SQL Server 2005, I have the following query to calculate the median sales of each quarter over the past 5 years:
WITH CompMedian AS
(
SELECT SoldDate, SoldPrice, ROW_NUMBER() OVER(PARTITION BY Convert(Varchar(5),Year(SoldDate)) + Convert(Varchar(5), RIGHT(CAST(100+DATEPART(QQ,SoldDate) AS CHAR(3)), 2)) ORDER BY SoldPrice) AS RowNum, COUNT(*) OVER(PARTITION BY Convert(Varchar(5),Year(SoldDate)) + Convert(Varchar(5), RIGHT(CAST(100+DATEPART(QQ,SoldDate) AS CHAR(3)), 2))) AS Cnt FROM tbl_Orders
WHERE Status = 'Sold'
AND SoldDate >= DATEADD(Year, -5, Convert(DateTime, Convert(Varchar(5),Month(GetDate())) + Convert(Varchar(5), '/1/') + Convert(Varchar(5), YEAR(GetDate()))))
AND SoldDate < DATEADD(Year, 0, Convert(DateTime, Convert(Varchar(5),Month(GetDate())) + Convert(Varchar(5), '/1/') + Convert(Varchar(5), YEAR(GetDate()))))
)
SELECT Convert(Varchar(5),Year(SoldDate)) + Convert(Varchar(5), RIGHT(CAST(100+DATEPART(QQ,SoldDate) AS CHAR(3)), 2)) AS CompDate, AVG(SoldPrice) AS CompMedian
FROM CompMedian
WHERE RowNum IN((Cnt + 1) / 2, (Cnt + 2) / 2)
GROUP BY Convert(Varchar(5),Year(SoldDate)) + Convert(Varchar(5), RIGHT(CAST(100+DATEPART(QQ,SoldDate) AS CHAR(3)), 2))
ORDER BY CompDate;
Now my client would like me to change the query so that each quarter would represent the median for the past 12 months ending with that quarter. I've been looking at this for hours and I'm at a loss. Anyone have any thoughts?
Thanks in advance,
Russ
Hi,
I need to retrieve both Mont and Quarter from an MS SQL server field containing a date as ''01/01/2007 12:10:00'.
Retrieving the Month is working properly, but getting some sql error with the Quarter function.
I will appreciate if someone can indicate me how should I do in order to retrieve the Quarter from a date.
To retrieve the month, I am using:
& "MONTH(STOCK.VALUEDATE) AS 'Month'"
To retrieve the Quarter, I am trying (but getting error):
& "QUARTER(STOCK.VALUEDATE) AS Quarter"
Thanks,
Aldo.
Hi guys
I have got a huge table which contains patients data.
I need to calculate the the no of patients for each clinical service.
Here is the sample data
Patient no Startdate EndDate ClinicalService cost
1qq 12/04/03 15/04/03 restHome $12
2qq 1/1/07 1/2/07 dementia $4234.00
3ww 30/01/04 12/01/06 hospital $1244
1qq 1/1/2004 12/1/2004 dementia $400
Assumption: If a person has been in two services at a different period of time, he will be treated as a different case each time in every different service.
I have got a table of dates called 'static_tab' which contains all the possible dates.
here is the script for the 'static_tab' table
USE [LOSDSS1]
GO
/****** Object: Table [dbo].[static_tab] Script Date: 11/13/2007 14:51:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[static_tab](
[cnt] [int] NULL,
[dates] [datetime] NULL,
[Quarter] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
I have been using a query for calculating total cost for each patient for each catagory . Its as follows
Select Year(x.dates) As [Year], Month (x.dates) As [Month],x.clinicalservice
Sum(dailyvol) As cost
From c.dates,f.clinicalService,
(f.cost / (DateDiff(dd, f.[Start date], f.[End date]) + 1)) As DailyVol
From tablePatients f
Inner Join static_tab c On c.dates Between f.[Start date]
And f.[End date]) as x
Group By Year(x.dates), Month (x.dates),x.clinicalservice
Order By Year(x.dates), Month (x.dates),x.clinicalservice
And the output table is as follows
clinical service Year Month cost
Dementia 2007 7 1016842.852
Dementia 2007 8 1005945.664
Dementia 2007 9 976899.9008
Hospital Care 2003 10 2714517.879
Hospital Care 2003 11 2724111.029
I want the similar output but insteasd of cost, I need the patient count.
I tried doing it but it gives me a conversion error
Please help guys
Thanks
I have to get the ratio like A:B with two calculated columns. A and B both are the counts of the rows. Counts are done on the Group level.
A B Ratio
| | Count(A) : Count(B)
| |
| |
--- ---
Count(A) Count(B)
Please help me with this, it's really urgent!
I am converting a report created using Crystal Reports 10 to Reporting Services. The report contains a list of items with dollar values. The original report displays both the Average and Median value. I can easily ( using avg(Field1.Value!) ) determine the average but cannot find a function to determine the median.
How can I add the Median to the report?
Thanks in advance,
Greg