Forum Replies Created
Viewing 15 posts - 1,006 through 1,020 (of 1,439 total)
-
RE: remove leading 0's from a character string
There's lots of ways, this should work
declare @s varchar(20)set @s='008EE'
--set @s='05TT'
--set @s='000RTG'
--set @s='0GG'
select substring(@s,patindex('%[^0]%',@s),len(@s))
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
RE: Maximum ID with other columns
Try this
with cte as (select id,loannumber, sequence,rank() over(partition by loannumber order by id desc) as rk
from paymenttransaction
where loannumber = '0025187956')
select * from cte
where rk=1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Get Total and Avg Values
SELECT COALESCE(a.Customer,b.Customer) AS Customer,
a.CurrentYear,
b.LastYear
FROM (
SELECT Customer, SUM(Total) AS CurrentYear
FROM #CurrentYr
GROUP BY Customer) a
FULL OUTER JOIN (
SELECT...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Splitting a date range
Using a numbers table
SELECT Field1,Number AS IndividualYear
FROM mytable
INNER JOIN Numbers ON Number BETWEEN StartYear AND EndYear
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Get Total and Avg Values
David-155102 (11/2/2009)
I've had a bit of a play and have simplified what I am asking for help on, first the sample code and data:use tempdbgo
--Create table to hold this years...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: To find the count of a particular character inside a string
Ian Scarlett (10/30/2009)
Paul White (10/30/2009)
Atif Sheikh (10/30/2009)
Glad it helped you.You need to be careful with this method:
Declare @v-2 varchar(100)Declare @i int
Set @v-2 = '1234 ~'
Select @i = Len(@v) -...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Help with select query please
You can do the first with something like this. The others should be similar.
SELECT Customer
FROM mytable
GROUP BY Customer
HAVING SUM(CASE WHEN ProductID='AAA123' THEN 1 ELSE 0 END)>0
AND SUM(CASE...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Finding difference of cumulative values in a table
You can use the "quirky update" method for this kind of problem
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: CAST DATE i.e 1=Jan, 2=Feb, 3=March etc....
Look up DATENAME in BOL
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Finding difference of cumulative values in a table
rekha_sara (10/21/2009)
There is no particular order which has to be followed if both the amounts are made on the same date.It can be taken in any orderTo shed some light...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Finding difference of cumulative values in a table
rekha_sara (10/21/2009)
Mark,SanojThank you for the response..
Tried out both your queries.But the issue i'm facing with the above queries is that if the date is same, the value calculated is not...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Finding difference of cumulative values in a table
Ineffecient, but should work
UPDATE mytable
SET RemAmt = RemAmt - (SELECT SUM(a.Amt) FROM mytable a WHERE a.GrpID=mytable.GrpID AND a.Date<=mytable.Date)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Slow Query
Tara-1044200 (10/16/2009)
select distinct a.empid
from ViewEmpployee a inner join #Empdates b on a.empid=b.empid,#researchtab
where EmmJobCode in( '435','767','974') or
(Job1 = '5' or Job2 = '5'or Job3 ='5' or Job4 = '5' or Job5...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Max date from multiple accounts
krypto69 (10/16/2009)
Thanks Mark..where do i put in the account numbers?
Here
with cte as (select *,row_number() over(partition by loan order by thedate desc) as rn
from loandata
where loan in (....)
)
select * from cte...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
RE: Max date from multiple accounts
with cte as (select *,row_number() over(partition by loan order by thedate desc) as rn
from loandata)
select * from cte where rn=1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 15 posts - 1,006 through 1,020 (of 1,439 total)