In short, there exists a table say #limits(FISICAL_QUARTER ,[PROD_OFFRG_DESC], max_sum).

The query should calculate orders' running totals (excluding current order) within FISICAL_QUARTER ,[PROD_OFFRG_DESC] and select only orders, where runnig total is...

See the difference

DECLARE

@total INT = 5

,@free INT = 4

SELECT @free/@total*100 AS INTEGER_ARITHM

, (0.+@free)/@total*100 AS NUMERIC_ARITHM

You also may convert it explicitly using exactly precsion you need.

Try

SELECT TYPE, SEQ, b.SUB_TYPE

FROM #TEMP a

CROSS APPLY (SELECT TOP(1) SUB_TYPE

FROM #TEMP

WHERE TYPE = a.TYPE AND SEQ <= a.SEQ AND SUB_TYPE >''

ORDER BY SEQ DESC

) b

ORDER BY TYPE,...

FETCH NEXT

Discard this, yes, the script has FETCH in a loop.

Try PARTITION

SELECT A. Date, B.DatePlus3BusinessDays

FROM TableA A

LEFT JOIN (Select DateKey, LEAD(DateKey,3) OVER (PARTITION BY IsBusinessDay ORDER BY datekey) AS DatePlus3BusinessDays FROM Calendar WHERE IsBusinessDay = 1) B ON A.DateKey =...

simon_s (5/12/2015)

---

thank you both for your replies 🙂 to clarify I want is to return rows that only contain the letters/characters outside of A through G and outside of...

If procedure perfomance is a concern then any WHILE, any extra table impose some overhead you know.

As a simple example

x =1+2+3+4+5

is faster then

x=0

for i=1..5 do {x +=...

May be a future big number of problem types is a reason behind the requierment.

You may generalise 4 types of problems as a rows of a table variable.

DECLARE @problems TABLE (

id int -- problem type =1,2,3,4

,cnt int

,msg VARCHAR(MAX)

, priority VARCHAR (100)

-- more...

Using Eirikur Eiriksson's setup

WITH XMLNAMESPACES (DEFAULT 'urn:vim25')

SELECT

HSI.DATA.value('(HostSystemIdentificationInfo/identifierValue/text())[3]','varchar(50)')

,HSI.DATA.value('(HostSystemIdentificationInfo/identifierValue/text())[4]','varchar(50)')

FROM @SAMPLE_XML.nodes('obj') AS HSI(DATA);

Any chance OrderLineItemID is not unique in RoyaltyActuals?

Then depending on where predicate different set of RoyaltyActuals rows will be applyed to update given #TEMP1.OrderLineItemID producing different results.

Demo

DECLARE @temp1 TABLE (

OrderLineItemID...

It may depend heavily on data distribution but 'not exists' perfoms better in my tests. Here are the test scripts. QU version uses a copy of original data but...

Good job Dwain.

It seems TOP(1) date instead of min(date) does the trick. This runs nearly as fast as your query.

with dts as (

select date, b.ItemId, b.neededQuantity

from (select distinct date from...

To get an 'Conversion failed..' error raised is quite enough to have[testcolumn] - 0 at select . Why to put it as [testcolumn] %2 =1 at where ?...

Try this.

DECLARE @BOM TABLE

(

ItemIDINT

,neededQuantityfloat

)

INSERT INTO @BOM (ItemID, neededQuantity)

SELECT 1, 10

UNION ALL SELECT 2, 10

UNION ALL SELECT 3, 5;

DECLARE @WhareHouseMovement TABLE

(

ItemIDINT

,Quantityfloat

,DateDATETIME

)

INSERT INTO @WhareHouseMovement (ItemID, Quantity, Date)

SELECT 1, 10, '2015-03-01'

UNION ALL SELECT...