SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help for separation periods.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Black_Trouble
Starting Member

Turkey
21 Posts

Posted - 12/18/2013 :  08:02:25  Show Profile  Reply with Quote
Hello,

I have a table. In this table the 4 year period, taking in the months to calculate an amount for each period.

period will be as follows

example;
1,2,3 months of the first period,
4,5,6 months the second period,
7,8,9 third period of months,
10,11,12 fourth period of months. corresponding amount will be calculated for each month in the table.

table data.


SELECT     id, dmkodu, dmadi, dmgrup, dmdevir, dmoran, dmalistarih, dmaktiftarih, dmalismiktar, dmalisbirim, dmfirmakodu, dmfirmaun, dmalistutari, dmkdvorani, 
                      dmtoplam, dmsatistarih, dmsatisfirma, dmsatisfirmaunvan, dmsatistutar, dmsatiskdv, dmsatistoplam, dmsube, dmdepart, dmmuhasebekod, 
                      dmyontem, dmoltarih, dmdegistirmetarih
FROM         tblsabirkiymet


example;


	dmkodu	dmadi	dmoran	dmtarih	dmaktif	dmtutar
17	DM001	BENMARI	20	05.01.2013 00:00	01.01.2013 00:00	5000
18	DM002	SALATBAR	10	08.05.2013 00:00	08.05.2013 00:00	1000



With the above code you can reach the following table.




SELECT DemYili AS [DEM YILI], CONVERT(datetime, dmaktiftarih, 104) AS [Aktif Tarihi], CONVERT(datetime, [Son Gün], 104) AS [Bu Dönem Ayrilacak Son Tarih],
[Bu Yil Ayrilacak], [Ayrilacak Son Tarih], [Ayrilacak Dönem], CONVERT(decimal(18, 2), [Aylik Ayrilacak Tutar]) AS [Aylik Ayrilacak Tutar]
FROM (SELECT 100 / dmoran AS DemYili, dmoran, dmaktiftarih, CASE WHEN dmsatistarih < DATEADD(dd, - 1, DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy,
0, dmaktiftarih) + 1, 0))) THEN dmsatistarih WHEN dmsatistarih > DATEADD(dd, - 1, DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0,
dmaktiftarih) + 1, 0))) THEN DATEADD(dd, - 1, DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, dmaktiftarih) + 1, 0))) ELSE DATEADD(dd, - 1,
DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, dmaktiftarih) + 1, 0))) END AS [Son Gün], DATEDIFF(qq, dmaktiftarih, DATEADD(dd, - 1,
DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, dmaktiftarih) + 1, 0)))) + 1 AS [Bu Yil Ayrilacak], DATEDIFF(qq, dmaktiftarih, DATEADD(yy,
100 / dmoran, dmaktiftarih) - 1) + 1 AS [Ayrilacak Dönem], CONVERT(decimal(18, 2), dmalistutari) / CONVERT(decimal(18, 0), dmoran)
AS [Aylik Ayrilacak Tutar], CASE WHEN dmsatistarih < DATEADD(yy, 100 / dmoran, dmaktiftarih) - 1 THEN dmsatistarih WHEN DATEADD(yy,
100 / dmoran, dmaktiftarih) - 1 < dmsatistarih THEN DATEADD(yy, 100 / dmoran, dmaktiftarih) - 1 ELSE DATEADD(yy, 100 / dmoran,
dmaktiftarih) - 1 END AS [Ayrilacak Son Tarih]
FROM tblsabirkiymet) AS a

Black_Trouble
Starting Member

Turkey
21 Posts

Posted - 12/18/2013 :  08:03:18  Show Profile  Reply with Quote
return

[url=http://bit.ly/c25MCx][/url]
Go to Top of Page

Black_Trouble
Starting Member

Turkey
21 Posts

Posted - 12/18/2013 :  08:06:01  Show Profile  Reply with Quote
[url=http://bit.ly/c25MCx][/url]

Edited by - Black_Trouble on 12/18/2013 08:06:24
Go to Top of Page

Black_Trouble
Starting Member

Turkey
21 Posts

Posted - 12/19/2013 :  02:31:28  Show Profile  Reply with Quote
Help Me please
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/19/2013 :  04:55:40  Show Profile  Reply with Quote
sorry your explanation is not clear. can you elaborate on how you got those values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Black_Trouble
Starting Member

Turkey
21 Posts

Posted - 12/19/2013 :  05:40:42  Show Profile  Reply with Quote
I do not know english. with the translation may not be understood for what I did.

There are four periods in the first year accounting.
Starting at dmaktiftarih I want to do now until the end of the period is the month to devote to

Example

dmaktiftarih: 04.04.2013
1.period = null month
2.period =4,5,6 months
3.period =7,8,9 months
4.period =10,11,12 months
show as

example 2

dmaktiftarih=01.02.2013

1.period =2,3 (1 months null)
2.period =4,5,6 months
3.period =7,8,9 months
4.period =10,11,12 months


I think I take care of other parts.The important part for me at first
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/19/2013 :  10:52:56  Show Profile  Reply with Quote
Ok..thats fine. so how do you divide the value for those 4 periods? like 250,750 etc? the date starts at 1/1/2013 so it includes the whole year right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Black_Trouble
Starting Member

Turkey
21 Posts

Posted - 12/20/2013 :  03:04:37  Show Profile  Reply with Quote
Ok. 31.12.2013 up to 01.01.2013 for the portion beginning.
I need to automatically separating period. first date may not be obvious, but the deadline will be 31.12.2013. can also begin from the date 01.04.2013.
Go to Top of Page

Black_Trouble
Starting Member

Turkey
21 Posts

Posted - 12/24/2013 :  02:41:25  Show Profile  Reply with Quote
help me please
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/24/2013 :  12:32:54  Show Profile  Reply with Quote
quote:
Originally posted by Black_Trouble

help me please


The reason why you didnt get proper help is because ppl are having difficulty understanding your real requirement from your explanation.
Please try to post required information in below format
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 12/24/2013 12:37:49
Go to Top of Page

Black_Trouble
Starting Member

Turkey
21 Posts

Posted - 12/25/2013 :  03:11:19  Show Profile  Reply with Quote
CREATE TABLE tblsabirkiymet
(
id int Not null identity(1,1),
dmkodu nvarchar(max),
dmadi nvarchar(MAX),
dmoran decimal (18,2),
dmaktiftarih Datetime,
dmalistutari decimal(18,2),
dmsatistarih Datetime
)
insert into tblsabirkiymet (dmkodu, dmadi, dmoran, dmaktiftarih, dmalistutari,dmsatistarih) values ('DM001','BENMARI','20','05.01.2013','5000',null)
insert into tblsabirkiymet (dmkodu, dmadi, dmoran, dmaktiftarih, dmalistutari,dmsatistarih) values ('DM002','SALATBAR','10','08.05.2013','1000',null)


SELECT DemYili AS [DEM YILI], CONVERT(datetime, dmaktiftarih, 104) AS [Aktif Tarihi], CONVERT(datetime, [Son Gün], 104) AS [Bu Dönem Ayrilacak Son Tarih],
[Bu Yil Ayrilacak], [Ayrilacak Son Tarih], [Ayrilacak Dönem], CONVERT(decimal(18, 2), [Aylik Ayrilacak Tutar]) AS [Aylik Ayrilacak Tutar]
FROM (SELECT 100 / dmoran AS DemYili, dmoran, dmaktiftarih, CASE WHEN dmsatistarih < DATEADD(dd, - 1, DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy,
0, dmaktiftarih) + 1, 0))) THEN dmsatistarih WHEN dmsatistarih > DATEADD(dd, - 1, DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0,
dmaktiftarih) + 1, 0))) THEN DATEADD(dd, - 1, DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, dmaktiftarih) + 1, 0))) ELSE DATEADD(dd, - 1,
DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, dmaktiftarih) + 1, 0))) END AS [Son Gün], DATEDIFF(qq, dmaktiftarih, DATEADD(dd, - 1,
DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, dmaktiftarih) + 1, 0)))) + 1 AS [Bu Yil Ayrilacak], DATEDIFF(qq, dmaktiftarih, DATEADD(yy,
100 / dmoran, dmaktiftarih) - 1) + 1 AS [Ayrilacak Dönem], CONVERT(decimal(18, 2), dmalistutari) / CONVERT(decimal(18, 0), dmoran)
AS [Aylik Ayrilacak Tutar], CASE WHEN dmsatistarih < DATEADD(yy, 100 / dmoran, dmaktiftarih) - 1 THEN dmsatistarih WHEN DATEADD(yy,
100 / dmoran, dmaktiftarih) - 1 < dmsatistarih THEN DATEADD(yy, 100 / dmoran, dmaktiftarih) - 1 ELSE DATEADD(yy, 100 / dmoran,
dmaktiftarih) - 1 END AS [Ayrilacak Son Tarih]
FROM tblsabirkiymet) AS a


dmaktiftarih to [Bu Dönem Ayrilacak Son Tarih]
1.period = 1,2,3 months
2.period =4,5,6 months
3.period =7,8,9 months
4.period =10,11,12 months


I hope it was understandable way.



Go to Top of Page

Black_Trouble
Starting Member

Turkey
21 Posts

Posted - 12/27/2013 :  03:04:32  Show Profile  Reply with Quote
Help me plaese. still have not found the solution.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/28/2013 :  01:55:43  Show Profile  Reply with Quote
quote:
Originally posted by Black_Trouble

Help me plaese. still have not found the solution.


I'm still not able to understand your rules. And I think same is the case with others too seeing the nil response

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000