SQL Statement to get contract renewal amount by time range

I have two views. One view shows me the expiration dates of contracts of a customer. The other one is returning all the start dates of the customer's contracts. I wan't to find out the renewal amount of an expired contract. The rule is, that the customer has to start a contract in 90 days after a contract was expired.

http://d.pr/i/bITI

Pretend that the analysis period is Jan 2013. I have an Expiration Amount of 2835,15. Now I have two (2835, 1596) contract starts in the next 90 days after this expiration.

I need a query to get the result:

http://d.pr/i/3ztY

Tried a lot of statements but I found no way to get this result. Any ides?

Thanks

Answers


Here's the answer that I've tried

Table One :

USE [NMIIFDB_DEV]
GO

/****** Object:  Table [dbo].[tes1]    Script Date: 5/3/2013 4:20:35 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tes1](
    [ContractEndDate] [date] NULL,
    [CustomerNo] [nvarchar](50) NULL,
    [ContractAmount] [numeric](18, 2) NULL
) ON [PRIMARY]

GO

Table 2

USE [NMIIFDB_DEV]
GO

/****** Object:  Table [dbo].[tes2]    Script Date: 5/3/2013 4:20:46 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tes2](
    [ContractStartDate] [date] NULL,
    [CustomerNo] [nvarchar](50) NULL,
    [ContractAmount] [numeric](18, 2) NULL
) ON [PRIMARY]

GO

SELECT STATEMENT

select YEAR(T1.ContractEndDate) AS ExpirationYear, MONTH(T1.ContractEndDate) as ExpirationMonth,

T1.ContractAmount AS ExpirationAmount, SUM(T2.ContractAmount) AS RenewalAmount
from tes1 T1, tes2 T2

where MONTH(T1.ContractEndDate) = 01 AND YEAR(T1.ContractEndDate) = 2013 AND MONTH(T2.ContractStartDate) = 01 AND YEAR(T2.ContractStartDate) = 2013 

GROUP BY YEAR(T1.ContractEndDate), MONTH(T1.ContractEndDate),
T1.ContractAmount

You can declare the parameter you want to compare in parentheses


Need Your Help

dynamically storing runtime variables in android

java android

I am trying to port my PC-app to android. I am not sure how I should handle the variables:

javascript get the value of checkbox if checked without button clicked

javascript jquery html

javascript get the value of checkbox if checked without button clicked

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.