Help for separation periods. - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Help for separation periods.

Author

Topic

Black_Trouble
Starting Member

21 Posts

Posted - 2013-12-18 : 08:02:25

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;

[CODE]
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

[/CODE]

With the above code you can reach the following table.

[code]

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
[/CODE]

return :
5 01.01.2013 00:00:00 31.12.2013 00:00:00 4 31.12.2017 00:00:00 20 250,00
10 08.05.2013 00:00:00 31.12.2013 00:00:00 3 07.05.2023 00:00:00 41 120,00

I want you out of my table that way now.

[url=http://bit.ly/c25MCx]

[/url]

Black_Trouble
Starting Member

21 Posts

Posted - 2013-12-18 : 08:03:18

return

[url=http://bit.ly/c25MCx]

[/url]

Black_Trouble
Starting Member

21 Posts

Posted - 2013-12-18 : 08:06:01

[url=http://bit.ly/c25MCx]

[/url]

Black_Trouble
Starting Member

21 Posts

Posted - 2013-12-19 : 02:31:28

Help Me please

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-19 : 04:55:40

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

Black_Trouble
Starting Member

21 Posts

Posted - 2013-12-19 : 05:40:42

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-19 : 10:52:56

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

Black_Trouble
Starting Member

21 Posts

Posted - 2013-12-20 : 03:04:37

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.

Black_Trouble
Starting Member

21 Posts

Posted - 2013-12-24 : 02:41:25

help me please

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-24 : 12:32:54

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

Black_Trouble
Starting Member

21 Posts

Posted - 2013-12-25 : 03:11:19

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)

[CODE]
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

[/CODE]
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.

Black_Trouble
Starting Member

21 Posts

Posted - 2013-12-27 : 03:04:32

Help me plaese. still have not found the solution.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-28 : 01:55:43

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources