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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 LEFT JOIN NULLs

Author  Topic 

patbe
Starting Member

9 Posts
Posted - 2007-06-16 : 03:30:44
I'm having a bit of a struggle on this one, I do a LEFT JOIN and when I get NULLs I would like to take the last value that is not NULL and do some calculations, if there's more NULLs then I need to take the last value that I have calculated the do the calculations on that one.

So when ml.MeasurementLogValue is NULL then I want to take the last value that is not NULL from ml.MeasurementLogValue and substract f.forecastValue and add d.deliveryValue, if more rows have NULL in ml.MeasurementLogValue then the same calculation should be made but instead of startinf with the last value that is not NULL in ml.MeasurementLogValue it should start with the calculated value in the row before.

Please let me know if it's not clear



CREATE TABLE [MeasurementLog] (
[MeasurementLogID] [int] IDENTITY (1, 1) NOT NULL ,
[MeasurementLogTimeStamp] [datetime] NOT NULL CONSTRAINT [DF_MeasurementLog_MeasurementLogTimeStamp] DEFAULT (getdate()),
[TankObjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MeasurementLogStatus] [int] NULL ,
[MeasurementLogValue] [int] NOT NULL ,
[MeasurementLogTemperature] [int] NULL ,
CONSTRAINT [PK_MeasurementLog] PRIMARY KEY CLUSTERED
(
[MeasurementLogID]
ON [PRIMARY] ,
CONSTRAINT [FK_MeasurementLog_TankObject] FOREIGN KEY
(
[TankObjectID]
REFERENCES [TankObject] (
[TankObjectID]
ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO


CREATE TABLE [Delivery] (
[DeliveryID] [int] IDENTITY (1, 1) NOT NULL ,
[TankObjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DeliveryValue] [int] NOT NULL ,
[DeliveryDate] [datetime] NULL ,
[Timestamp] [datetime] NULL CONSTRAINT [DF_Delivery_Timestamp] DEFAULT (getdate()),
[SupplierReference] [varchar] (50) COLLATE Finnish_Swedish_CI_AS NULL ,
CONSTRAINT [PK_Delivery] PRIMARY KEY CLUSTERED
(
[DeliveryID]
ON [PRIMARY] ,
CONSTRAINT [FK_Delivery_TankObject] FOREIGN KEY
(
[TankObjectID]
REFERENCES [TankObject] (
[TankObjectID]

) ON [PRIMARY]
GO


CREATE TABLE [Forecast] (
[ForecastID] [int] IDENTITY (1, 1) NOT NULL ,
[TankObjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ForecastValue] [int] NOT NULL ,
[ForecastDate] [datetime] NULL ,
[Timestamp] [datetime] NOT NULL CONSTRAINT [DF_Forecast_Timestamp] DEFAULT (getdate()),
[CustomerReference] [varchar] (50) COLLATE Finnish_Swedish_CI_AS NULL ,
CONSTRAINT [PK_Forecast] PRIMARY KEY CLUSTERED
(
[ForecastID]
ON [PRIMARY] ,
CONSTRAINT [FK_Forecast_TankObject] FOREIGN KEY
(
[TankObjectID]
REFERENCES [TankObject] (
[TankObjectID]

) ON [PRIMARY]
GO


Some sample date for the log table

--INSERTs generated by 'sp_generate_inserts' stored procedure written by Vyas
--Build number: 22
--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com
--http://vyaskn.tripod.com

SET NOCOUNT ON

SET IDENTITY_INSERT [dbo].[Measurementlog] ON
GO


PRINT 'Inserting values into [Measurementlog]'

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(875,'Mar 8 2003 1:30:00:000PM','SN05000620',NULL,4000,0)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(876,'Mar 9 2003 1:31:00:000PM','SN05000620',NULL,3800,0)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(877,'Mar 10 2003 1:28:00:000PM','SN05000620',NULL,3500,0)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(878,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,17)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(879,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,17)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(880,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(881,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(882,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(883,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(884,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(885,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(886,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(887,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(888,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(889,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(890,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(891,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(892,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(893,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(894,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(895,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(896,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(897,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(898,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(899,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,23)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(900,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,21)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(901,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,26)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(902,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,26)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(903,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,26)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(905,'Mar 27 2006 2:40:00:000PM','0000000006',1,14000,16)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(906,'Mar 27 2006 2:40:00:000PM','0000000007',1,13500,16)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(907,'Mar 23 2006 3:00:00:000PM','SN05000620',1,23311,21)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(908,'Mar 28 2006 12:00:00:000PM','0000000007',1,12000,12)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(909,'Mar 28 2006 12:01:00:000PM','0000000006',1,13000,24)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(910,'Mar 28 2006 5:20:36:520PM','0000000010',1,4027,0)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(911,'Mar 28 2006 5:21:16:940PM','0000000010',1,4027,0)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(912,'Mar 28 2006 5:22:00:000PM','0000000007',1,23500,21)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(913,'Mar 28 2006 5:25:22:230PM','0000000010',1,4027,21)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(914,'Mar 28 2006 5:27:03:870PM','0000000010',1,4027,21)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(915,'Mar 23 2006 3:00:00:000PM','0000000010',1,4027,21)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(916,'Mar 23 2006 3:00:00:000PM','0000000010',1,4027,21)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(917,'Mar 23 2006 3:00:00:000PM','0000000010',1,4027,21)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(918,'Mar 23 2006 3:00:00:000PM','0000000010',1,4027,21)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(919,'Mar 23 2006 3:00:00:000PM','0000000010',2,23311,21)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(920,'Mar 23 2006 3:00:00:000PM','0000000010',2,23311,21)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(921,'Mar 23 2006 3:00:00:000PM','0000000010',1,4027,0)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(922,'Mar 28 2006 5:59:27:977PM','0000000010',1,4027,0)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(923,'Mar 28 2006 6:04:32:873PM','0000000010',1,4027,21)
INSERT INTO [Measurementlog] ([MeasurementLogID],[MeasurementLogTimeStamp],[TankObjectID],[MeasurementLogStatus],[MeasurementLogValue],[MeasurementLogTemperature])VALUES(924,'Mar 28 2006 6:08:03:963PM','0000000010',1,4027,12)

PRINT 'Done'


SET IDENTITY_INSERT [dbo].[Measurementlog] OFF
GO
SET NOCOUNT OFF


Sample for Forecast

--INSERTs generated by 'sp_generate_inserts' stored procedure written by Vyas
--Build number: 22
--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com
--http://vyaskn.tripod.com

SET NOCOUNT ON

SET IDENTITY_INSERT [dbo].[forecast] ON
GO


PRINT 'Inserting values into [forecast]'

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(1,'0000000002',11,'Jan 5 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(2,'0000000002',11,'Jan 6 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(3,'0000000002',11,'Jan 7 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(4,'0000000002',11,'Jan 8 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(5,'0000000002',11,'Jan 9 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(6,'0000000002',11,'Jan 10 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(7,'0000000002',11,'Jan 11 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(8,'0000000002',11,'Jan 12 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(9,'0000000002',11,'Jan 13 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(10,'0000000002',11,'Jan 14 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(11,'0000000002',11,'Jan 15 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(12,'0000000002',11,'Jan 16 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(13,'0000000002',11,'Jan 17 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(14,'0000000002',11,'Jan 18 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(15,'0000000002',11,'Jan 19 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(16,'0000000002',11,'Jan 20 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(17,'0000000002',11,'Jan 21 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(18,'0000000002',11,'Jan 22 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(19,'0000000002',11,'Jan 23 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(20,'0000000002',11,'Jan 24 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(21,'0000000002',11,'Jan 25 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(22,'0000000002',11,'Jan 26 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(23,'0000000002',11,'Jan 27 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(24,'0000000002',11,'Jan 28 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(25,'0000000002',11,'Jan 29 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(26,'0000000002',11,'Jan 30 2006 12:00:00:000AM','Jun 3 2007 8:03:37:013PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(27,'0000000002',11,'Jan 31 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(28,'0000000002',11,'Feb 1 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(29,'0000000002',11,'Feb 2 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(30,'0000000002',11,'Feb 3 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(31,'0000000002',11,'Feb 4 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(32,'0000000002',11,'Feb 5 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(33,'0000000002',1024,'Feb 6 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(34,'0000000002',1024,'Feb 7 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(35,'0000000002',1024,'Feb 8 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(36,'0000000002',1024,'Feb 9 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(37,'0000000002',1024,'Feb 10 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(38,'0000000002',1024,'Feb 11 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(39,'0000000002',1024,'Feb 12 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(40,'0000000002',1024,'Feb 13 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(41,'0000000002',1024,'Feb 14 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(42,'0000000002',1024,'Feb 15 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(43,'0000000002',1024,'Feb 16 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(44,'0000000002',1024,'Feb 17 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(45,'0000000002',1024,'Feb 18 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(46,'0000000002',1024,'Feb 19 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(47,'0000000002',1024,'Feb 20 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(48,'0000000002',1024,'Feb 21 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)
INSERT INTO [forecast] ([ForecastID],[TankObjectID],[ForecastValue],[ForecastDate],[Timestamp],[CustomerReference])VALUES(49,'0000000002',1024,'Feb 22 2006 12:00:00:000AM','Jun 3 2007 8:03:37:030PM',NULL)

PRINT 'Done'


SET IDENTITY_INSERT [dbo].[forecast] OFF
GO
SET NOCOUNT OFF


Thers no data in the Delivery table

Jim77
Constraint Violating Yak Guru

440 Posts
Posted - 2007-06-18 : 05:19:42
I havent read all your email becuase it is huge, but it strikes me that you need to use the COALESCE function to cater for your not null calculatons.
Check it out on Books on Line.
Go to Top of Page

patbe
Starting Member

9 Posts
Posted - 2007-06-26 : 04:24:21
ok, thx I will look into it
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site