Yee-Haa!

declare @asd varchar(20)

set @asd ='asdaaaadffa'

select len(@asd) - len(replace(@asd,'a',''))

Try this

WITH data (Client_id, id, value) AS (

SELECT 10, 1, 1 UNION ALL

SELECT 20, 1, 1 UNION ALL

SELECT 30, 1, 1 UNION ALL

SELECT 40, 1, 2 UNION ALL

SELECT 10, 2,...

SELECT a.ITEM,b.DESCRIPTION AS ITEM_DESC,a.CNT,a.DESCRIPTION

FROM @ITEM a

INNER JOIN @ITEM b ON b.CNT='' AND b.ITEM=a.ITEM

Something like this?

WITH RankedORDERS AS (

SELECT USER_ID,ORDER_DATE,

ROW_NUMBER() OVER(PARTITION BY USER_ID ORDER BY ORDER_DATE) AS rn

FROM ORDERS)

SELECT USER_ID,

...

Then you can do this

WITH CTE AS (

SELECT EID, AbsenceType, AbsenceStartDate ,AbsenceEndDate,

...

This is a just couple of queries rolled into one.

This first query finds the start points of the intervals by finding all start points that don't lie inside an earlier...

Not very efficient, but should give the correct results

SELECT s1.well_id,

s1.Top1,

MIN(t1.BASE1) AS BASE1,

...

There's lots of ways of doing this, here's another

SELECT CustID

FROM product_details

WHERE ProdId = 1

INTERSECT

SELECT CustID

FROM product_details

WHERE ProdId = 4

EXCEPT

SELECT CustID

FROM product_details

WHERE ProdId = 0;

Also this

WITH Summary...

May not be what you're after, but you can return an additional column with the total row count

Alter proc [dbo].[InvoiceDisplayTest] (@InvoiceNumber nvarchar(50),@InvoiceDate datetime ,@InvoiceDate1 datetime, @AccountNumber nvarchar(50),@TradingPartnerID nvarchar(50),@Page nvarchar(50),@rownum int...

Try adding

ORDER BY SN,NodeName,NodeValue

to the end of the query

Use sql:variable

set @starttag ='XML'

set @root ='Provider'

SELECT

dense_rank() OVER (ORDER BY C.value('local-name(.)', 'varchar(50)')) AS 'SN',

NodeName = C.value('local-name(.)', 'varchar(50)')

, NodeValue = C.value('(.)[1]', 'varchar(50)')

FROM @xml.nodes('/*[local-name(.)=sql:variable("@starttag")]/*[local-name(.)=sql:variable("@root")]/*') AS T(C)

UPDATE t1

SET Id = COALESCE(t2a.Id,t2b.Id,t2c.Id)

FROM Table1 t1

LEFT OUTER JOIN Table2 t2a ON t2a.Acc = t1.AccType1

LEFT OUTER JOIN Table2 t2b ON t2b.Acc = t1.AccType2

LEFT OUTER JOIN Table2 t2c ON t2c.Acc...

I think this is a running totals problem, have a look here

http://www.sqlservercentral.com/articles/T-SQL/68467/

If you are using SQL Server 2012, you can use the built-in windowing functions

DECLARE @ToAllocate INT = 21;

WITH CTE...

Yet another to try...

DECLARE @t TABLE(ID INT IDENTITY, Formula VARCHAR(100))

DECLARE @Old CHAR(1)

DECLARE @new CHAR(1)

INSERT @t(Formula)

SELECT 'L@*K@*H@/324'

UNION ALL SELECT '(AY#-AR#)*(Y#+BB#)'

UNION ALL SELECT 'TD@*(L#+D@)*C@'

UNION ALL SELECT '((AE#-AR#)*(Y#+A#))/Y#'

UNION ALL SELECT 'A#+AB#+BA#+B#'

SET @Old =...