Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 average

Author  Topic 

fan2005
Yak Posting Veteran

85 Posts
Posted - 2011-01-27 : 13:44:03
hi
There is a table like one here which has information about all accounts for each date they had transaction,
balance is calculated by running total from amount.
The dates are in Persian.

i want to calculate average of balance for 10 months from 1389/01/01 to 1389/10/01
for each account

for example
(DATEDIFF(dy,'1389/01/01','1389/06/18')*48000+DATEDIFF(dy,'1389/06/18','1389/10/01')*49000)/ DATEDIFF(dy,'1389/01/01','1389/10/01') for 1-11-1

but finding the dates is one problem
and not supporting datediff() function for persian date is another

thanks for reply



transactionID	branchcode	customerno	PersianDate	account	AccountID	accounttype	amount	balance
1	1         	11        	1388/10/15	1-11-1    	73	1         	51000.00	51000
2	1         	11        	1388/11/16	1-11-1    	73	1         	-3000.00	48000
3	1         	11        	1389/06/18	1-11-1    	73	1         	1000.00	49000
4	1         	11        	1389/02/11	1-11-2    	74	2         	65000.00	65000
5	1         	11        	1389/06/17	1-12-2    	75	2         	6000.00	6000
6	2         	12        	1389/01/23	2-12-1    	76	1         	100000.00	100000
7	2         	12        	1389/06/30	2-12-1    	76	1         	-1000.00	99000

fan2005
Yak Posting Veteran

85 Posts
Posted - 2011-01-27 : 14:35:07
first I want to find balance of max(date) where date<'1389/01/01) for each account.


SELECT
distinct C.account, C.balance ,S.maxdate 
FROM
TransactionDetail  C
inner join 
(
select distinct MAX(persiandate) as maxdate ,account
from TransactionDetail
group by persiandate,account
having MAX(persiandate)<'1389/01/01'
)S
on S.account =C.account
and S.maxdate=C.persiandate

this wont give the desired result

-----out put-----
account balance maxdate
1-11-1 48000 1388/11/16
1-11-1 51000 1388/10/15


the result i want is
account balance maxdate
1-11-1 48000 1388/11/16
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2011-01-27 : 14:36:45
What datatype is column "PersianDate"?

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

fan2005
Yak Posting Veteran

85 Posts
Posted - 2011-01-27 : 14:37:40
nchar(12)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2011-01-27 : 14:45:47
Works fine for me. Why are you using NCHAR(12)? Alter your column to DATE now, please.
DECLARE	@Sample TABLE
	(
		TransactionID INT,
		BranchCode INT,
		CustomerNo INT,
		PersianDate DATE,
		Account	VARCHAR(6),
		AccountID INT,
		AccountType INT,
		Amount MONEY,
		Balance MONEY
	)

INSERT	@Sample
VALUES	(1, 1, 11, '1388/10/15', '1-11-1', 73, 1,  51000.00,  51000),
	(2, 1, 11, '1388/11/16', '1-11-1', 73, 1,  -3000.00,  48000),
	(3, 1, 11, '1389/06/18', '1-11-1', 73, 1,   1000.00,  49000),
	(4, 1, 11, '1389/02/11', '1-11-2', 74, 2,  65000.00,  65000),
	(5, 1, 11, '1389/06/17', '1-12-2', 75, 2,   6000.00,   6000),
	(6, 2, 12, '1389/01/23', '2-12-1', 76, 1, 100000.00, 100000),
	(7, 2, 12, '1389/06/30', '2-12-1', 76, 1,  -1000.00,  99000)

SELECT	*,
	DATEDIFF(DAY, '1389/01/01', '1389/06/18') AS Peso
FROM	@Sample


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

fan2005
Yak Posting Veteran

85 Posts
Posted - 2011-01-27 : 14:50:57
quote:
Originally posted by Peso

Works fine for me. Why are you using NCHAR(12)? Alter your column to DATE now, please.
DECLARE	@Sample TABLE
	(
		TransactionID INT,
		BranchCode INT,
		CustomerNo INT,
		PersianDate DATE,
		Account	VARCHAR(6),
		AccountID INT,
		AccountType INT,
		Amount MONEY,
		Balance MONEY
	)

INSERT	@Sample
VALUES	(1, 1, 11, '1388/10/15', '1-11-1', 73, 1,  51000.00,  51000),
	(2, 1, 11, '1388/11/16', '1-11-1', 73, 1,  -3000.00,  48000),
	(3, 1, 11, '1389/06/18', '1-11-1', 73, 1,   1000.00,  49000),
	(4, 1, 11, '1389/02/11', '1-11-2', 74, 2,  65000.00,  65000),
	(5, 1, 11, '1389/06/17', '1-12-2', 75, 2,   6000.00,   6000),
	(6, 2, 12, '1389/01/23', '2-12-1', 76, 1, 100000.00, 100000),
	(7, 2, 12, '1389/06/30', '2-12-1', 76, 1,  -1000.00,  99000)

SELECT	*,
	DATEDIFF(DAY, '1389/01/01', '1389/06/18') AS Peso
FROM	@Sample


N 56°04'39.26"
E 12°55'05.63"


First how did you find '1389/06/18'.It should calculate like this
(DATEDIFF(dy,'1389/01/01','1389/06/18')*48000+DATEDIFF(dy,'1389/06/18','1389/10/01')*49000)/ DATEDIFF(dy,'1389/01/01','1389/10/01') for 1-11-1
which needs you first find 1-11-1 48000 1388/11/16

second persian date is not like christan it would give false result
for datediff.

could you help me in first part
for date diff problem i think i should write a function
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2011-01-27 : 15:01:19
So what are the rules for getting the difference in dates for persian dates?


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2011-01-27 : 15:02:49
I found this, http://www.irantour.org/Iran/iranianmonths.html
so it seems you will have to write your own DATEDIFF function for your Persian dates.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

fan2005
Yak Posting Veteran

85 Posts
Posted - 2011-01-27 : 15:05:31
the persain calender is like this

12 months
from 1st to 6th months of the 1st half of year it is always 31 days
other 5 month of the 2end half is 30 days
and the last month (12th ) is 29 days
except that : every 4 years it is 30 days
which this year is called kabiseh in persian language
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2011-01-27 : 15:45:44
Yes, exactly as described in the link I posted.
Now, which is the 4-year cycle?

//Peter

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

fan2005
Yak Posting Veteran

85 Posts
Posted - 2011-01-27 : 18:12:00
quote:
Originally posted by Peso

Yes, exactly as described in the link I posted.
Now, which is the 4-year cycle?

//Peter

N 56°04'39.26"
E 12°55'05.63"


 declare @IsLeap bit
   select @IsLeap=
     
            case 
            when       ( cast((substring (@PDate,1,4))as integer)) % 33 in(1, 5 , 9 , 13 , 17 , 22 , 26 ,30)
            then 1

            ELSE 0
            end
Go to Top of Page

fan2005
Yak Posting Veteran

85 Posts
Posted - 2011-01-28 : 03:49:15
quote:
Originally posted by fan2005

hi
There is a table like one here which has information about all accounts for each date they had transaction,
balance is calculated by running total from amount.
The dates are in Persian.

i want to calculate average of balance for 10 months from 1389/01/01 to 1389/10/01
for each account

for example
(DATEDIFF(dy,'1389/01/01','1389/06/18')*48000+DATEDIFF(dy,'1389/06/18','1389/10/01')*49000)/ DATEDIFF(dy,'1389/01/01','1389/10/01') for 1-11-1

but finding the dates is one problem
and not supporting datediff() function for persian date is another

thanks for reply



transactionID	branchcode	customerno	PersianDate	account	AccountID	accounttype	amount	balance
1	1         	11        	1388/10/15	1-11-1    	73	1         	51000.00	51000
2	1         	11        	1388/11/16	1-11-1    	73	1         	-3000.00	48000
3	1         	11        	1389/06/18	1-11-1    	73	1         	1000.00	49000
4	1         	11        	1389/02/11	1-11-2    	74	2         	65000.00	65000
5	1         	11        	1389/06/17	1-12-2    	75	2         	6000.00	6000
6	2         	12        	1389/01/23	2-12-1    	76	1         	100000.00	100000
7	2         	12        	1389/06/30	2-12-1    	76	1         	-1000.00	99000





--********SOLUTION**********---
I solved it
any better and faster solution ?
--------STEP1

alter table TransactionDetail add preBalance decimal(20,0)



SET NOCOUNT ON

--===== Declare the cursor storage variables
DECLARE @Amount decimal(20,0)
DECLARE @CurAccountID bigINT

--===== Declare the working variables
DECLARE @PrevAccountID bigINT
DECLARE @AccountRunningTotal decimal(20,0)
DECLARE @PreAccountRunningTotal decimal(20,0)


--===== Create the cursor with rows sorted in the correct
-- order to do the running balance by account
DECLARE curRunningTotal CURSOR LOCAL FORWARD_ONLY
FOR
SELECT AccountID, gardesh
FROM dbo.TransactionDetail

ORDER BY AccountID, tarikh, TransactionID
OPEN curRunningTotal

--===== Read the information from the first row of the cursor
FETCH NEXT FROM curRunningTotal
INTO @CurAccountID, @Amount

--===== For each account, update the account running total
-- column until we run out of rows. Notice that the
-- CASE statement resets the running total at the
-- start of each account.
WHILE @@FETCH_STATUS = 0
BEGIN
--===== Calculate the running total for this row
-- and remember this AccountID for the next row


SELECT
@AccountRunningTotal = CASE
WHEN @CurAccountID = @PrevAccountID
THEN @AccountRunningTotal + @Amount
ELSE @Amount
END,

@PreAccountRunningTotal = CASE
WHEN @CurAccountID = @PrevAccountID
THEN @AccountRunningTotal - @Amount

ELSE 0
END,
@PrevAccountID = @CurAccountID
--===== Update the running total for this row
UPDATE dbo.TransactionDetail
SET balance = @AccountRunningTotal,
Prebalance = @PreAccountRunningTotal
WHERE CURRENT OF curRunningTotal

--===== Read the information from the next row of the cursor
FETCH NEXT FROM curRunningTotal
INTO @CurAccountID, @Amount

END --End of the cursor

--======== Housekeeping
CLOSE curRunningTotal
DEALLOCATE curRunningTotal
GO
select * from TransactionDetail

------------out put Step1--------

transactionID	abrnchcod	Cfcifno	tarikh	account	AccountID	Tbdptype	gardesh	balance	preBalance
1	1         	11        	1388/10/15	1-11-1                                            	97	1         	51000.00	51000	0
2	1         	11        	1388/11/16	1-11-1                                            	97	1         	-3000.00	48000	51000
3	1         	11        	1389/06/18	1-11-1                                            	97	1         	1000.00	49000	48000
4	1         	11        	1389/09/15	1-11-1                                            	97	1         	2000.00	51000	49000
5	1         	11        	1389/10/02	1-11-1                                            	97	1         	1000.00	52000	51000
6	1         	11        	1389/02/11	1-11-2                                            	98	2         	65000.00	65000	0
7	1         	11        	1389/06/17	1-12-2                                            	99	2         	6000.00	6000	0
8	2         	12        	1389/01/23	2-12-1                                            	100	1         	100000.00	100000	0
9	2         	12        	1389/06/30	2-12-1                                            	100	1         	-1000.00	99000	100000


----Step2----------
drop table a10

select * into a10 from TransactionDetail where tarikh between '1389/01/01' and '1389/11/01'
order by account , tarikh
alter table a10 add sum10 decimal(20,0)



 CREATE CLUSTERED INDEX IXC_Transaction_AccountID_balance_preBalance_tarikh
     ON dbo.a10 (AccountID,balance, preBalance,tarikh)
    
 SET NOCOUNT ON

--===== Declare the cursor storage variables
DECLARE @CurAccountID        bigINT
DECLARE @preBalance        decimal(20,0)
Declare @total decimal(20,0) set @total=0
DECLARE @Balance        decimal(20,0)

--===== Declare the working variables
DECLARE @PrevAccountID       bigINT
DECLARE @Pretarikh       nvarchar(12) set @Pretarikh= '1389/01/01'
DECLARE @tarikh       nvarchar(12)
DECLARE @AccountRunningTotal decimal(20,0)
DECLARE @PreAccountRunningTotal   decimal(20,0)


--===== Create the cursor with rows sorted in the correct
     -- order to do the running balance by account
DECLARE curRunningTotal CURSOR LOCAL FORWARD_ONLY
    FOR
 SELECT AccountID, balance ,preBalance,tarikh

   FROM dbo.a10
--  WHERE AccountID <= 10 --Uncomment for "short" testing
  ORDER BY AccountID, tarikh, TransactionID
   OPEN curRunningTotal

--===== Read the information from the first row of the cursor
  FETCH NEXT FROM curRunningTotal
   INTO @CurAccountID,@balance, @prebalance,@tarikh

--===== For each account, update the account running total 
     -- column until we run out of rows.  Notice that the
     -- CASE statement resets the running total at the 
     -- start of each account.
  WHILE @@FETCH_STATUS = 0
  BEGIN
  --===== Calculate the running total for this row
     -- and remember this AccountID for the next row
     print(@total)print('+')print(dbo.ShamsiDateDiff(@Pretarikh,@tarikh))print (@Pretarikh)print( '-') print(@tarikh) print ('*') print (@preBalance) print ('=')
     print(@total+(dbo.ShamsiDateDiff(@Pretarikh,@tarikh))*@preBalance)
          
 SELECT    
  @total = CASE 
                               WHEN @CurAccountID = @PrevAccountID 
                               THEN ((dbo.ShamsiDateDiff(@Pretarikh,@tarikh))*@preBalance)

                               --@AccountRunningTotal + @Amount 
                               ELSE case when --@CurAccountID <> @PrevAccountID and
                                @Pretarikh='1389/01/01' then
                              ((dbo.ShamsiDateDiff(@Pretarikh,@tarikh))*@preBalance)
                               else case when @CurAccountID <> @PrevAccountID and @Pretarikh<>'1389/01,01'
                               then 0
                               END
                               END

                               END,

 @PrevAccountID = @CurAccountID
 ,
 @Pretarikh  = @tarikh
 print (@total)
 
--===== Update the running total for this row
 UPDATE dbo.a10
    SET sum10 = @Total
   
  WHERE CURRENT OF curRunningTotal

--===== Read the information from the next row of the cursor
  FETCH NEXT FROM curRunningTotal
   INTO @CurAccountID,@balance, @prebalance,@tarikh

    END --End of the cursor

--======== Housekeeping
     CLOSE curRunningTotal
DEALLOCATE curRunningTotal
GO

select * from a10 order by account , tarikh

--------output step2
transactionID	abrnchcod	Cfcifno	tarikh	account	AccountID	Tbdptype	gardesh	balance	preBalance	sum10
3	1         	11        	1389/06/18	1-11-1                                            	97	1         	1000.00	49000	48000	8256000
4	1         	11        	1389/09/15	1-11-1                                            	97	1         	2000.00	51000	49000	4312000
5	1         	11        	1389/10/02	1-11-1                                            	97	1         	1000.00	52000	51000	867000
6	1         	11        	1389/02/11	1-11-2                                            	98	2         	65000.00	65000	0	0
7	1         	11        	1389/06/17	1-12-2                                            	99	2         	6000.00	6000	0	0
8	2         	12        	1389/01/23	2-12-1                                            	100	1         	100000.00	100000	0	0
9	2         	12        	1389/06/30	2-12-1                                            	100	1         	-1000.00	99000	100000	16200000

-----------step3


declare @totalday int 
set @totalday= dbo.ShamsiDateDiff('1389/01/01','1389/11/01') 

select distinct--(SUM(C.sum10)+dbo.ShamsiDateDiff('1398/11/01',S.tarikh1))/11 ,  C.cfcifno
C.cfcifno,round((diff*C.balance+S.sum1)/@totalday,0) as avg,S.tarikh1
,C.balance,S.sum1 
from a10 as C
inner join 
(
--select * from a10
select distinct  C.cfcifno, sum (c.sum10) as sum1 ,C.Tbdptype,
dbo.ShamsiDateDiff(max(C.tarikh),'1389/11/01') as diff,max(C.tarikh)as tarikh1
--max(C.tarikh) as tarikh1
--
from
 dbo.a10 C 
 where C.Tbdptype=1         
GROUP BY C.cfcifno,C.Tbdptype
)S
on S.cfcifno=C.cfcifno
AND C.tarikh=S.tarikh1

------------output Step3
cfcifno	avg	tarikh1	balance	sum1
11        	48833.000000	1389/10/02	52000	13435000
12        	92412.000000	1389/06/30	99000	16200000
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts
Posted - 2011-01-28 : 06:20:21
This site allows you to download types for PersianDate and PersianDateTime which may allow a better approach to the problem.

http://persiandate.codeplex.com/

Edit:

Here is another:

http://www.codeproject.com/KB/database/PersianDateInSQLServer.aspx?msg=2655605
Go to Top of Page

fan2005
Yak Posting Veteran

85 Posts
Posted - 2011-01-28 : 13:42:26
quote:
Originally posted by Ifor

This site allows you to download types for PersianDate and PersianDateTime which may allow a better approach to the problem.

http://persiandate.codeplex.com/

Edit:

Here is another:

http://www.codeproject.com/KB/database/PersianDateInSQLServer.aspx?msg=2655605


Thanj you It was Interesting
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site