assume i have 2 tables

CREATE TABLE #Setup ([PPHProfileNo] INT, [%DPP] MONEY, RangeFrom MONEY, RangeThru MONEY, [%PPH] MONEY)
INSERT INTO #Setup VALUES (1,50,0,60000000,5)
INSERT INTO #Setup VALUES (2,50,60000001,190000000,15)
INSERT INTO #Setup...

• This reply was modified 2 years, 7 months ago by  gentong.bocor.

i mean if i put 55 millions, then you should give the output of 3 rows where sum all used columns is 55 millions. used column is based on the...

okay..

i will take this query for my program, without consider this query is not recursive. this just using normal loop. if can made recursive it will be better.

Thanks..

I have...

I mean, the query...

• This reply was modified 4 years, 2 months ago by  gentong.bocor.

Here's my partly done solution.  It needs CROSS APPLY the projections

drop table if exists #Consumption;
go
CREATE TABLE #Consumption(
  MeterNo VARCHAR(10),
  PERIOD SMALLDATETIME,
  Amount MONEY);
go

INSERT...

• This reply was modified 4 years, 2 months ago by  gentong.bocor.

yes, this query can't be run on 2008..

Anyone?

yessss... !!

that's what i want.. but unfortunately that code only can be run using SQL2012. is there any code using cte which simplified loop for each rows but running in...

anyone ???

@Eirikur Eiriksson

i see, your code may get wrong result if called with week no. 1

calling SELECT * FROM dbo.ITVFN_CALENDAR_YEAR(2016) AS INC

WHERE INC.WEEK_NO = 1 may get 2 days, 2016-01-01 &...

Eirikur Eiriksson (9/22/2016)

---

Everything needed was already in the code sample;-), here is a function that returns one year

😎

USE TEEST;

GO

SET NOCOUNT ON;

GO

CREATE FUNCTION dbo.ITVFN_CALENDAR_YEAR

(

@YEAR INT...

Eirikur Eiriksson (9/22/2016)

---

Quick inline calendar table example using the sample data posted previously on this thread.

😎

USE TEEST;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'tempdb..#tamp') IS NOT NULL DROP TABLE #tamp;

CREATE TABLE #tamp

( WeekNo INT,...

the #tamp table is...

J Livingston SQL (9/22/2016)

---

gentong.bocor (9/22/2016)

---

ok..

i give u temporary table for u try..

CREATE TABLE #tamp

( WeekNo INT, FromDate SMALLDATETIME, ThruDate SMALLDATETIME, Period VARCHAR(6) )

INSERT INTO #tamp VALUES (36,'2016-08-29','2016-09-04','201609')

INSERT INTO #tamp VALUES...

actually i can loop datetime variable using while, and increment with 1 days through loop.

but i wanna hear any idea which simplify the code..

if u said using cte will...