(Gast)
n/a Beiträge
|
AW: SQL: Jahresanteile des Vertags berechnen?
11. Jun 2013, 08:51
Ich glaube, es geht kürzer:
SQL-Code:
---
--- Testumgebung ----------------------------------------------------------
---
USE [Test]
GO
CREATE TABLE [dbo].[Contracts](
[ctID] [int] IDENTITY(1,1) NOT NULL,
[ctStart] [smalldatetime] NOT NULL,
[ctDurationMonths] [int] NOT NULL,
[ctPrice] [money] NOT NULL,
[soll] [int] NULL
) ON [PRIMARY]
declare @startPeriod dateTime
declare @EndPeriod DateTime
set @startPeriod='20120101'
set @EndPeriod = '20121231'
--- Abfrage ---------------------------------------------------------------------------
select *, ctPrice*DateDiff(Month, dmin,dmax)/ctDurationMonths as PriceInPeriod
from
(
select *,
(select max (X) from (values (@StartPeriod), (ctStart)) as T(X)) dMin,
(select min (X) from (values (@EndPeriod), (DateAdd(Month, ctDurationMonths, ctStart))) as T(X)) dMax
----------*****---- Hier war ein Tippfehler. Danke tgvoelker!
from contracts
) x
where DateDiff(Month, dmin,dmax)>0
--------------------------------------------------------------------------------------
Geändert von Furtbichler (12. Jun 2013 um 08:20 Uhr)
Grund: Berichtigung eines Tippfehlers
|
|
Zitat
|