RE: Query

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Mal .mullerjannie_at_hotmail.com> (.mullerjannie_at_hotmail.com)
Date: 10/15/04 

Date: Fri, 15 Oct 2004 09:03:02 -0700

I've read this so many times , but I can't get what your asking =]

You want the first day (startperioddate)
where the startperioddate = N + 1 ?

"Peter Newman" wrote:

> Im trying to create a query to return when the latest crediting period
> started and the credit value to dat from the start of the last period.
>
> The sample data below is only a small represation only as entries for all
> customers go gack to Jan 04.
>
> I need to find the first entry in the table for in this case licence
> '217514', and then move in blocks through the table in sections of N days .
> in this case N = 14 . Each N + 1 days is the start of a new period and the
> credit limit is reset to 0. What i need to return is the startdate of the
> current period, and the credit recived within this period
>
> ( ie if the first entry is 01/01/2004 that is the start of the first
> period, which makes the next period start 15/01/2004 etc )
>
> when N = 14
> Licence StarPeriodDate CreditThisPeriod
> 217514 2004-11-13 1000
>
> if N = 28
> when N = 14
> Licence StarPeriodDate CreditThisPeriod
> 217514 2004-10-10 3000
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Table1]
> GO
>
> CREATE TABLE [dbo].[Table1]
> [Licence] [varchar] (6)- COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MoveDate] [datetime] NOT NULL ,
> [FileValue] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> SET NOCOUNT ON
> INSERT INTO [Table1] ([Licence],[MoveDate],[FileValue])VALUES('217514','Sep
> 1 2004 12:00:00:000AM','1000')
> INSERT INTO [Table1] ([Licence],[MoveDate],[FileValue])VALUES('217514','Sep
> 10 2004 12:00:00:000AM','2000')
> INSERT INTO [Table1] ([Licence],[MoveDate],[FileValue])VALUES('217514','Sep
> 11 2004 12:00:00:000AM','1000')
> INSERT INTO [Table1] ([Licence],[MoveDate],[FileValue])VALUES('217514','Oct
> 10 2004 12:00:00:000AM','1000')
> INSERT INTO [Table1] ([Licence],[MoveDate],[FileValue])VALUES('217514','Nov
> 11 2004 12:00:00:000AM','1000')
> SET NOCOUNT OFF
>