Qira,

really it can be a beginning of rather long road. Next step is to take target column data type into account. Both at query generation and at user input....

ken.mulvihill (11/19/2015)

---

Lynn

In a nutshell this is what I was trying to do.

Case

When term = fall

Then run the query that uses reference date 9/15

Else run the query that uses reference date...

Jason-299789 (11/19/2015)

---

The LAST_VALUE function will probably work, but will need an sort on the columns

SELECT

tgt.*

,LAST_VALUE(Class4) OVER (PARTITION BY identifier ORDER BY identifier)

FROM #TestTable tgt

Order by Identifier,AsOfDate...

If you need to combine comparison terms using arbitrary OR , AND logical operators, consider

CREATE TABLE [dbo].[crpt_LembaranImbangan_2_Param_2](

[idx] [int] IDENTITY(-2147483648,1) NOT NULL,

[left_bracket]...

Jeff Moden (11/18/2015)

---

I believe that using something like "Lead/Lag" would do the same thing faster but I don't have 2012 on the machine that I'm currently working on and won't...

This definetly will work. But keep in mind sequences pose almost no locks (and no locks on user tables at all) as they are generated outside the scope of the...

I see....

Sure?

create table #test_period (periodid int, customerid int, closebalance decimal (18,2))

insert into #test_period values

(6,1,10.82),

(6,2,14.67),

(6,3,15.90),

(6,4,12345.90),

(6,5,545.86),

(6,6,8456.05),

(6,7,549851.00),

(6,8,564891.02),

(6,9,7845.09),

(6,10,12.23),

(6,11,74.15),

(6,12,98.45),

(6,13,98.99),

(6,14,931.25),

(7,1,54.44),

(7,2,518.54),

(7,3,8253.25),

(7,4,455.90),

(7,5,5575.86),

(7,6,556.05),

(7,7,851.00),

(7,8,891.02),

(7,9,75.09),

(7,10,1.23),

(7,11,7.15),

(7,12,9.45),

(7,13,9.99),

(7,14,91.25),

(3,1,118.82),

(3,2,154.67),

(3,3,155.90),

(3,4,1345.90),

(3,5,45.86),

(3,6,856.05),

(3,7,59851.00),

(3,8,56891.02),

(3,9,785.09),

(3,10,2.23),

(3,11,7.15),

(3,12,8.45),

(3,13,988.99),

(3,14,31.25),

(4,1,174.82),

(4,2,174.67),

(4,3,175.90),

(4,4,127345.90),

(4,5,5745.86),

(4,6,87456.05),

(4,7,5851.00),

(4,8,891.02),

(4,9,75.09),

(4,10,742.23),

(4,11,7444.15),

(4,12,9858.45),

(4,13,99698.99),

(4,14,95231.25),

(5,1,1052.82),

(5,2,12544.67),

(5,3,15245.90),

(5,4,1254345.90),

(5,5,52445.86),

(5,6,84422456.05),

(5,7,5449851.00),

(5,8,56424891.02),

(5,9,784245.09),

(5,10,4212.23),

(5,11,224274.15),

(5,12,922458.45),

(5,13,24532.99),

(5,14,2545.25)

;with cte as

(select

DENSE_RANK() over(order by periodid) AS DR,

row_number() over(partition by periodid order by...

Specifically the piece of code on fig.7 that you may want to adapt.

First it provides for max 10000 rows to be generated and then restricts the number of...

Antony,

and periodid >= (select max(dr)-3 from cte)

may lead to too much periods returned. Consider periods

(3,4,5,6,7). Then max(dr) -3 ==2.

I'd prefer

with cte as

(select

DENSE_RANK() over(order by periodid desc) AS...

Quite formally you may first refactor cursor part of code to

INSERT INTO @WFdetails

select RDL.execution_id as ParentWF_ExecutionID,

zzz.LastWF_Name,

...

from #RequestDetailedList RDL

cross apply (

SELECT TOP 1 SWE.Name as

, SWE.InternalStatus as LastWF_InternalStatus

, SWED.ExecutionID...

See DENSE_RANK() https://msdn.microsoft.com/en-us/library/ms189798(v=sql.120).aspx

SELECT top(SELECT top(1) maxl FROM leafs) level

FROM (...)

exactly selects rows according to maximum level.

Use tally function of choice instead of ... . See http://www.sqlservercentral.com/articles/Tally+Table/72993/ for great tally...

If i got it right try this

CREATE TABLE [t_dim_product_temp](

[product_cd] [varchar](100) NOT NULL PRIMARY KEY CLUSTERED,

[product_desc] [varchar](100) NOT NULL,

[product_parent_cd] [varchar](100) NOT NULL,

[product_id] [int] NOT NULL)

--------------------------------------------

INSERT INTO [t_dim_product_temp]

(product_cd,product_desc,product_parent_cd,product_id)

SELECT 'TOTPROD','Total Commercial Beverages','',1...

Should the script extend all "short" branches to the maximum number of levels? Including BS999 in your example?

Them the idea is get leafs only, get their levels, get...