How To Calculate The Least Anmongst A List Of Quarters
Oct 25, 2006
Hi,
i get a list of Quarters that look like following as an input to my proc.
'Q306/Q106/Q406/Q405'
I need to find out whihc one of the above is the least Quarter value.
In the above example it is 'Q405'
The Quarter values that come into the proc keep changing...It could be even 1 or 2 or 3 Quarters etc...
Is there any datatype called Quarter in SQL Server.It shows Quarter as a key word...
Can some one please help on this....
View 9 Replies
ADVERTISEMENT
Jun 25, 2007
Hi agian. Another probs. I need help again....
DECLARE @quarter varchar(10), @year int
SET @quarter = 'First' --First, Second, Third, Fourth
SET @year = 2007
table1 - hrEmpPaySumm
empno period1 sss_ee sss_er
1 1/1/2007 500 100
1 3/1/2007 600 200
1 3/16/2007 700 300
2 4/1/2007 800 400
3 4/1/2007 900 500
expected result...
if @quarter = 'First' AND @year = 2007 - returns all data of jan,feb,mar of 2007
empno month1 sss_ee1 sss_er1 month2 sss_ee2 sss_er2 month3 sss_ee3 sss_er3
1 January 500 100 Feb 0 0 March 1100 500
2 January 0 0 Feb 0 0 March 0 0
3 January 0 0 Feb 0 0 March 0 0
if @quarter = 'Second' AND @year = 2007 - returns all data of apr,may,jun of 2007
empno month1 sss_ee1 sss_er1 month2 sss_ee2 sss_er2 month3 sss_ee3 sss_er3
1 April 0 0 May 0 0 June 0 0
2 April 800 400 May 0 0 June 0 0
3 April 900 500 May 0 0 June 0 0
and so on....
-Thanks
-Ron-
"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."
View 4 Replies
View Related
Nov 20, 2015
I want past two and next two quarters from the present quarterLike now its 4th quarter i should get 3rd and 2nd quarter and next two quarters that is 1st and 2nd also this is my table and when the year changes data is not fetched correctly
View 10 Replies
View Related
Aug 14, 2012
I have the following query, which is producing the results, which i need.
SELECT
*
FROM
(
SELECT
o.FDMSAccountNo,
DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0) AS FirstDayOfMonth,
SUM(hst_sales_amt) AS 'Total'
[Code] ....
However I want to create an additional four columns, which gives me the quarterly sales amount
E.g.
FDMSAccount no 20120101 20120201 20120301
878743915885 NULL10000 35000
So the quarter sales amount for fdmsaccount no 878743915885 is 45000...
View 1 Replies
View Related
Aug 26, 2015
I would like to add a calculation to provide an annualized Gross Amount value. Most solutions I have seen are for annualizing down to the day. For instance, if today is the 123rd day of the year, the calculation would be Amount / 123 * 365. What I want to do is annualize the amount based only on completed periods. Today is 8/26.
The last completed quarter ended on 6/30. Therefore I want to basically take the sum of Q1 and Q2, divide by two, and multiply by four to get an annualized amount based on completed quarters. Eventually I want to do something similar with months.
So here's what I have so far. In my DSV my DimDate is actually based on a view. The view has a Date column with the actual date, all of the normal fields you would expect in a DimDate table, and two columns that look like this:
CASE
WHEN [Date] < DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0)
THEN 'Y'
ELSE 'N'
END AS
CompletedQuarter,
[Code] ....
Note that for dates between 1/1 and 3/31 the CompletedQuarterCount will be 0. I want any annualized amount to be 0 in that case because I only want to use completed quarters in my calculation.
I added both to my Pay Date dimension (which uses vwDimDate from the DSV) in the cube. I have tried the calculation below and in my Excel pivot table I'm getting blanks for the calculated field.
CREATE MEMBER CURRENTCUBE.[Measures].[Gross Amount Annualized by Pay Date Quarter]
AS SUM(
IIF([Pay Date].[Pay Date Completed Quarter Count] > 0,
[Gross Amount] / [Pay Date].[Pay Date Completed Quarter Count] * 4,
0)
),
FORMAT_STRING = "Currency",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Claim' ;
So, is my calculation correct?
View 2 Replies
View Related
Jun 15, 2015
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
So the output would look like :
CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]
SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0
GROUP BY
preop.Case_ID
View 8 Replies
View Related
Apr 9, 2008
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
View 21 Replies
View Related
Apr 18, 2007
HI...
I need to do a simple task but it's difficult to a newbie on ssis..
i have two tables...
first one has an identity column and the second has fk to the first...
to each dataset row i need to do an insert on the first table, get the @@Identity and insert it on the second table !!
i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"
here is the script
INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@@Identity,
?,
?,
?
)
what's the problem ??
View 7 Replies
View Related
Jan 20, 2005
Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
Can anyone help?
View 3 Replies
View Related
Mar 21, 2008
I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
How can I do this?
Insert Query:
insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE)
SELECT DISTINCT
tri_IDENT.IDA AS PPID,
tri_Ldg_Tran.CLM_ID AS PTicketNum,
tri_ClaimChg.Line_No AS PLineNum,
tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT'
THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier
ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate,
tri_Ldg_Tran.Tran_Date AS PEntryDate,
tri_ClaimChg.Hsp_Code AS PHCPCCode,
tri_Ldg_Tran.Adj_Type,
tri_Ldg_Tran.PRS_ID,
tri_Ldg_Tran.Create_Time,
tri_Ldg_Tran.Adj_Group,
tri_Ldg_Tran.Payer_ID,
tri_Ldg_Tran.TRN_ID,
tri_ClaimChg.Primary_Claim,
tri_IDENT.Version
FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN
qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc =
qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN
tri_Ldg_Tran RIGHT OUTER JOIN
tri_IDENT LEFT OUTER JOIN
tri_ClaimChg ON tri_IDENT.Pat_Id1 =
tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID =
tri_ClaimChg.PRS_ID AND
tri_Ldg_Tran.Chg_TRN_ID =
tri_ClaimChg.Chg_TRN_ID
AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN
tri_Payer ON tri_Ldg_Tran.Payer_ID
= tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type
= tri_Ldg_Tran.Pay_Type AND
qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type
WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg')
AND (tln_PaymentTypeMappings.PTMClientCode = 'SR')
AND (tri_ClaimChg.Primary_Claim = 1)
AND (tri_IDENT.Version = 0)
View 2 Replies
View Related
Feb 12, 2008
Hi,
I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words. But I wish to return from my proc the list of words with ID, whether it is new or old.
Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'
My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.
performance is a not that great so I'm thinking that my code can be improved.
SQL Express 2005 SP2
Windows Server 2003
1GB Ram....(I know, I know)
TIA
Code Snippet
GO
CREATE TABLE [dbo].[Tokens](
[TokenID] [int] IDENTITY(1,1) NOT NULL,
[Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Tokens] PRIMARY KEY CLUSTERED
(
[TokenID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tokens] ON [dbo].[Tokens]
(
[Token] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE FUNCTION [dbo].[SplitTokenList]
(
@TokenList varchar(max)
)
RETURNS
@ParsedList table
(
Token varchar(255)
)
AS
BEGIN
DECLARE @Token varchar(50), @Pos int
SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ','
SET @Pos = CHARINDEX(',', @TokenList , 1)
IF REPLACE(@TokenList , ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1)))
IF @Token <> ''
BEGIN
INSERT INTO @ParsedList (Token)
VALUES (@Token) --Use Appropriate conversion
END
SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos)
SET @Pos = CHARINDEX(',', @TokenList, 1)
END
END
RETURN
END
GO
CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList]
@TokenList varchar(max)
AS
BEGIN
SET NOCOUNT ON;
create table #Tokens (TokenID int default 0, Token varchar(50))
create clustered index Tind on #T (Token)
DECLARE @NewTokens table
(
TokenID int default 0,
Token varchar(50)
)
--Split ID's into a table
INSERT INTO #Tokens(Token)
SELECT Token FROM SplitTokenList(@TokenList)
BEGIN TRY
BEGIN TRANSACTION
--get ID's for any existing tokens
UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0)
FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token
INSERT INTO Tokens(Token)
OUTPUT INSERTED.TokenID, INSERTED.Token INTO @NewTokens
SELECT DISTINCT Token FROM #Tokens WHERE TokenID = 0
return the list with id for new and old
SELECT TokenID, Token FROM #Tokens
WHERE TokenID <> 0
UNION
SELECT TokenID, Token FROM @Tokens
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @er nvarchar(max)
SET @er = ERROR_MESSAGE();
RAISERROR(@er, 14,1);
ROLLBACK TRAN
END CATCH;
END
GO
View 5 Replies
View Related
May 22, 2006
Hi,
I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.
From the parent package-> Execute Process Task->
dtsexec /F etc... /<pkg variable> = "servername"
Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)
I "sometimes" get the following error:
OnError,I4,TESTDOMAdministrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
Help would be appreciated!
I have seen other posts on this but, not able to relate the solution to my scenario.
View 9 Replies
View Related
May 10, 2006
Hi All,
I have seen a few other people have this error.
Package works fine when run from BIDS, DTExec, dtexecui. When I schedule it, It get these random errors. (See below)
The main culprit is a variable called "RecordsetFileDIR" which is set using an expression. (@[User::_ROOT] + "RecordSets\")
A number of other variables use this as part of their expression and as they all fail, pretty much everything dies.
I have installed SP1 (Not Beta) on server. Package uses config files to set the value of _ROOT.
The error does not always seem to be with this particular variable though. Always a variable that uses an expression but errors are random. Also, It will run 3 out of 10 times without a problem. I am the only person on the server at the time.
Any ideas?
Cheers,
Crispin
Error log:
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073659822,0x,The variable "User::RecordsetFileDIR" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073639420,0x,The expression for variable "rsHeaderFile" failed evaluation. There was an error in the expression.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
View 1 Replies
View Related
Feb 13, 2007
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.
View 3 Replies
View Related
Mar 30, 2007
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
Feb 13, 2008
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!
View 2 Replies
View Related
Mar 7, 2001
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
View 3 Replies
View Related
Nov 10, 2006
Can anyone help me with how to calculate the exact age.
Thanks in advance.
View 1 Replies
View Related
Jan 25, 2004
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 Related
Apr 15, 2008
I 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?
View 3 Replies
View Related
May 30, 2008
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?
View 9 Replies
View Related
Jan 4, 2008
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.
View 8 Replies
View Related
May 6, 2008
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.
View 5 Replies
View Related
Mar 7, 2004
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
View 2 Replies
View Related
Nov 12, 1999
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.
View 3 Replies
View Related
Aug 26, 2004
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).
View 14 Replies
View Related
Dec 30, 2003
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
View 7 Replies
View Related
Apr 28, 2004
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
View 8 Replies
View Related
Aug 29, 2013
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]....
View 5 Replies
View Related
Apr 20, 2015
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.
View 3 Replies
View Related
Dec 13, 2005
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
View 3 Replies
View Related
Dec 13, 2005
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
View 1 Replies
View Related
Apr 12, 2006
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.
View 1 Replies
View Related
