Referring to calculated column in select query

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 2005 Forums

Transact-SQL (2005)

Referring to calculated column in select query

Author

Topic

tbaker818
Starting Member

3 Posts

Posted - 2008-09-25 : 21:13:23

I haven't been able to find anything that tells me there's a way to do this, but I haven't found anything that says I can't either. My problem is that I'm doing a lot of logic and math which gets very repetitive and long if I have to calculate each column independently without having one building off another. I'm converting from Access where this is possible.

I tried creating scalar functions which helps a little but still gets ugly and eventually fails. I think it's at the point where I'm using functions within functions that it runs into a problem. It tells me that the column is invalid because it is not contained in either an aggregate function or a group by clause. However, if I break up the arguments of the outer function into individual columns, I have no problem, so I don't see why I'd have an aggregation/group by problem. Plus, I'm doing the same thing essentially in Access but referencing other columns instead of functions.

Is there an easier way to do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 01:14:19

I'm still not clearly what you're trying to do and what problem you're facing. Can you elaborate on your problem by giving necessary info. Refer to link below to understand what all info you need to provide

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

tbaker818
Starting Member

3 Posts

Posted - 2008-09-26 : 19:59:13

Here's what it looks like:


CREATE TABLE [dbo].[tbl875Fixed](
	[Event Date] [datetime] NOT NULL,
	[AD_ID] [float] NOT NULL,
	[Advertiser] [nvarchar](100) NULL,
	[Description] [nvarchar](100) NULL,
	[Start Date] [datetime] NULL,
	[End Date] [datetime] NULL,
	[Ad Size] [nvarchar](10) NULL,
	[Rate] [money] NULL,
	[Booked] [float] NULL,
	[Total Daily Delivered] [float] NULL,
	[Total Campaign Delivered] [float] NULL,
	[Total Daily Clicks Delivered] [float] NULL,
	[Total Campaign Clicks Delivered] [float] NULL,
	[Content Category] [nvarchar](100) NULL,
	[PO Num] [nvarchar](25) NULL,
	[SalesPerson] [nvarchar](40) NULL,

 CONSTRAINT [aaaaatbl875Fixed_PK] PRIMARY KEY NONCLUSTERED 
(
	[Event Date] ASC,
	[AD_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[tbl875Fixed]  WITH CHECK ADD  CONSTRAINT [FK_tbl875Fixed_tblAdvertisers] FOREIGN KEY([Advertiser])
REFERENCES [dbo].[tblAdvertisers] ([Advertiser])
GO
ALTER TABLE [dbo].[tbl875Fixed] CHECK CONSTRAINT [FK_tbl875Fixed_tblAdvertisers]
GO
ALTER TABLE [dbo].[tbl875Fixed]  WITH CHECK ADD  CONSTRAINT [FK_tbl875Fixed_tblSalespeople] FOREIGN KEY([SalesPerson])
REFERENCES [dbo].[tblSalespeople] ([Sales Person])
GO
ALTER TABLE [dbo].[tbl875Fixed] CHECK CONSTRAINT [FK_tbl875Fixed_tblSalespeople]




CREATE TABLE [dbo].[tblImpsLandedByCC](
	[Event Date] [datetime] NOT NULL,
	[AD_ID] [float] NOT NULL,
	[Content Category] [nvarchar](255) NOT NULL,
	[SumOfImpressions Delivered] [float] NULL,
	[SumOfClicks Delivered] [float] NULL,
 CONSTRAINT [aaaaatblImpsLandedByCC_PK] PRIMARY KEY NONCLUSTERED 
(
	[Event Date] ASC,
	[AD_ID] ASC,
	[Content Category] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


ALTER TABLE [dbo].[tblImpsLandedByCC]  WITH CHECK ADD  CONSTRAINT [tblImpsLandedByCC_FK00] FOREIGN KEY([Content Category])
REFERENCES [dbo].[tblContentCategories] ([Content Category])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[tblImpsLandedByCC] CHECK CONSTRAINT [tblImpsLandedByCC_FK00]
GO
ALTER TABLE [dbo].[tblImpsLandedByCC]  WITH CHECK ADD  CONSTRAINT [tblImpsLandedByCC_FK01] FOREIGN KEY([Event Date], [AD_ID])
REFERENCES [dbo].[tbl875Fixed] ([Event Date], [AD_ID])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[tblImpsLandedByCC] CHECK CONSTRAINT [tblImpsLandedByCC_FK01]
GO



ALTER FUNCTION dbo.PacingMinimized
(@EndDate varchar(10))
RETURNS @output TABLE([Advertiser Name] varchar(100),[Content Category] varchar(50), 
                               [Targeted Category] varchar(100), 
                               [Start Date] datetime, [Ad End Date] datetime, [Ad Rate] money, 
                               [Creative Size] varchar(7), [Booked Total] bigint, [Daily Dlvry] bigint,
                               [Imps Dlvrd MTD] bigint, [Imps Dlvrd Total] bigint, [Mnth Forecast] bigint, 
                               [Ideal Mnthly Imps] bigint, [Booked Imps] bigint, [Rev MTD] money, [Rev Forecast] money,
                               [Ideal Mnthly Rev] money, [Booked Rev] money, [Revenue Delta] money, SalesPerson varchar(60),
                               [Ad ID] bigint
					)
BEGIN

Declare @BegMonth datetime
Declare @EndMonth datetime

Set @BegMonth = dbo.BegMonth(@EndDate)
Set @EndMonth = dbo.EndMonth(@EndDate)

Insert @Output 
SELECT     tbl875Fixed.Advertiser, tblImpsLandedByCC.[Content Category], tbl875Fixed.[Content Category] AS [Targeted Category], 
                      tbl875Fixed.[Start Date], tbl875Fixed.[End Date], tbl875Fixed.Rate, 
                      tbl875Fixed.[Ad Size], SUM(tbl875Fixed.Booked) * dbo.PercentOfTotal(SUM(tblImpsLandedByCC.[SumOfImpressions Delivered]), 
                      SUM([Imps Targeted].[Targeted Imps])) AS [Booked Total], CASE WHEN dbo.LiveDaysMTD([Start Date], [End Date], @EndDate) 
                      = 0 THEN 0 ELSE SUM([SumOfImpressions Delivered]) / dbo.LiveDaysMTD([Start Date], [End Date], @EndDate) END AS [Daily Dlvry], 
                      SUM(tblImpsLandedByCC.[SumOfImpressions Delivered]) AS [Imps Dlvrd MTD], SUM(tbl875Fixed.[Total Campaign Delivered]) 
                      * dbo.PercentOfTotal(SUM(tblImpsLandedByCC.[SumOfImpressions Delivered]), SUM([Imps Targeted].[Targeted Imps])) AS [Imps Dlvrd Total], 
                      50000 AS [Mnth Forecast], 50000 AS [Ideal Mnthly Imps], 50000 AS [Booked Imps], 1000 AS [Rev MTD], 2000 AS [Rev Forecast], 
                      2500 AS [Ideal Mnthly Rev], 2500 AS [Booked Rev], - 150 AS [Revenue Delta], tbl875Fixed.SalesPerson, tbl875Fixed.AD_ID AS [Ad ID] 
FROM	tbl875Fixed Inner Join tblContentCategories ON tbl875Fixed.[Content Category] = tblContentCategories.[Content Category] INNER JOIN
                          (SELECT     AD_ID, MAX([Event Date]) AS MAX_Date
                            FROM          tbl875Fixed AS tbl875Fixed_1
                            GROUP BY AD_ID) AS Latest ON Latest.AD_ID = tbl875Fixed.AD_ID AND Latest.MAX_Date = tbl875Fixed.[Event Date] INNER JOIN
        tblImpsLandedByCC ON tbl875Fixed.AD_ID = tblImpsLandedByCC.AD_ID INNER JOIN
                          (SELECT     AD_ID, SUM([Total Daily Delivered]) AS [Targeted Imps]
                            FROM          tbl875Fixed AS tbl875Fixed_2
                            WHERE      ([Event Date] <= CONVERT(datetime, @EndDate)) AND ([Event Date] >= dbo.BegMonth(@EndDate))
                            GROUP BY AD_ID) AS [Imps Targeted] ON tbl875Fixed.AD_ID = [Imps Targeted].AD_ID INNER JOIN
        (SELECT     tbl875Fixed_3.AD_ID, tblImpsLandedByCC_1.[Content Category], 
                            SUM(tblImpsLandedByCC_1.[SumOfImpressions Delivered] / 1000 * tbl875Fixed_3.Rate) AS [Revenue MTD]
                            FROM          tbl875Fixed AS tbl875Fixed_3 INNER JOIN
                                                   tblImpsLandedByCC AS tblImpsLandedByCC_1 ON tbl875Fixed_3.[Event Date] = tblImpsLandedByCC_1.[Event Date] AND 
                                                   tbl875Fixed_3.AD_ID = tblImpsLandedByCC_1.AD_ID
                            WHERE      (tblImpsLandedByCC_1.[Event Date] <= CONVERT(datetime, @EndDate)) AND 
                                                   (tblImpsLandedByCC_1.[Event Date] >= dbo.BegMonth(@EndDate))
                            GROUP BY tbl875Fixed_3.AD_ID, tblImpsLandedByCC_1.[Content Category]) AS Rev_MTD ON tbl875Fixed.AD_ID = Rev_MTD.AD_ID AND 
                      tblContentCategories.[Content Category] = Rev_MTD.[Content Category]
WHERE     (tblImpsLandedByCC.[Event Date] <= CONVERT(datetime, @EndDate)) AND (tblImpsLandedByCC.[Event Date] >= dbo.BegMonth(@EndDate))
GROUP BY tbl875Fixed.Advertiser, tblImpsLandedByCC.[Content Category], tbl875Fixed.[Content Category], tbl875Fixed.[Ad Size], 
                      tbl875Fixed.AD_ID, tbl875Fixed.[Start Date], tbl875Fixed.[End Date], tbl875Fixed.Rate,  
                      tbl875Fixed.SalesPerson
                      
RETURN
END


ALTER FUNCTION [dbo].[BegMonth] 
(
	@Date datetime
)
RETURNS datetime
AS
BEGIN
	DECLARE @Result datetime

	-- Add the T-SQL statements to compute the return value here
	SELECT @Result = Convert(datetime,
								cast(datepart(month,@Date)as varchar) + '/' + cast('1' as varchar) + '/' + cast(datepart(year,@Date)as varchar)
							)

	-- Return the result of the function
	RETURN @Result
END



ALTER FUNCTION [dbo].[EndMonth] 
(
	@Date datetime
)
RETURNS datetime
AS
BEGIN
	DECLARE @Result datetime

	SELECT @Result = Convert(datetime,
		convert(varchar,datepart(mm,@Date)+1) 
	+ '/' + convert(varchar,'1') 
	+ '/' + convert(varchar,datepart(yy,@Date))
	)-1

	RETURN @Result

END




ALTER FUNCTION dbo.DTEBookedImps 
	(
	@AdEndDate datetime,
	@AdStartDate datetime,
	@EndDate datetime,
	@BookedTotal bigint,
	@ImpsDlvrdTotal bigint,
	@DailyDlvry bigint,
	@ImpsDlvrdMTD bigint,
	@AdSize varchar(8)
	)
RETURNS bigint
AS
	BEGIN
	Declare @Result bigint
	Declare @DTEImps bigint
	Declare @LiveDaysFromStart int
	Declare @LiveDaysMTD int
	Declare @LiveDaysRemainingMTD int
	Set @LiveDaysRemainingMTD = dbo.LiveDaysRemainingMTD(@EndDate,@AdEndDate)
	Set @LiveDaysMTD = dbo.LiveDaysMTD(@AdStartDate,@AdEndDate,@EndDate)
	Set @LiveDaysFromStart = dbo.LiveDaysFromStart(@AdStartDate,@AdEndDate,@EndDate)
	Set @DTEImps = dbo.DTEImps(@AdEndDate,@AdStartDate,@EndDate,@BookedTotal,@ImpsDlvrdTotal,@DailyDlvry)
	
	Select @Result =
	Convert(bigint,
				Case 
				When @AdSize='720x300' Then 
					@DTEImps 
				Else 
					Case When @BookedTotal=0 Then 
						@DTEImps
					Else 
						(@BookedTotal-(@ImpsDlvrdTotal-@ImpsDlvrdMTD/@LiveDaysFromStart*(@LiveDaysMTD+@LiveDaysRemainingMTD)-@ImpsDlvrdMTD)) 
					End
				End
			)
	RETURN @Result
	
	END




ALTER FUNCTION dbo.DTEImps 
	(
	@AdEndDate datetime,
	@AdStartDate datetime,
	@EndDate datetime,
	@BookedTotal bigint,
	@ImpsDlvrdTotal bigint,
	@DailyDlvry bigint
	)
RETURNS bigint
AS
	BEGIN
	Declare @Result int
	Select @Result = Convert(bigint,
						Case When dbo.LiveDaysRemainingMTD(@EndDate,@AdEndDate)=0 Then 
							0 
						Else 
							Case When @ImpsDlvrdTotal>@BookedTotal And @BookedTotal>0 Then 
								0 
							Else 
								Case When @DailyDlvry*dbo.LiveDaysRemainingMTD(@EndDate,@AdEndDate)<@BookedTotal-@ImpsDlvrdTotal Or @BookedTotal=0 Then 
									@DailyDlvry*dbo.LiveDaysRemainingMTD(@EndDate,@AdEndDate) 
								Else 
									(@BookedTotal-@ImpsDlvrdTotal)/dbo.LiveDaysRemainingTotal(@EndDate,@AdEndDate)*dbo.LiveDaysRemainingMTD(@EndDate,@AdEndDate)
								End
							End 
						End
							)
	RETURN @Result
	
	END




ALTER FUNCTION dbo.LiveDaysFromStart 
	(
	@AdEndDate datetime,
	@AdStartDate datetime,
	@EndDate datetime
	)
RETURNS int
AS
	BEGIN
	Declare @Result int
	Select @Result = Convert(int, @AdEndDate-
							Case
							When @AdEndDate-@AdStartDate>dbo.BegMonth(@EndDate) Then
								@AdStartDate
							Else
								dbo.BegMonth(@EndDate)+1
							End
							)
	RETURN @Result
	
	END




ALTER FUNCTION dbo.LiveDaysMTD 
	(
	@AdStartDate datetime,
	@AdEndDate datetime,
	@EndDate datetime
	)
RETURNS int
AS
BEGIN

Declare @Result int
Select @Result = 
	Convert(int,
		Case	
		When @AdStartDate<dbo.BegMonth(@EndDate) Then	
			Case
			When @AdEndDate<dbo.BegMonth(@EndDate) Then
				0
			Else 
				Case
				When @AdEndDate<@EndDate Then
					@AdEndDate-dbo.BegMonth(@EndDate)+1
				Else 
					@EndDate-dbo.BegMonth(@EndDate)+1
				End
			End
		Else
			Case
			When @AdEndDate<@EndDate Then
				@AdEndDate-@AdStartDate+1
			Else 
				@EndDate-@AdStartDate+1
			End
		End
	)
Return @Result
END




ALTER FUNCTION [dbo].[LiveDaysRemainingMTD] 
(
	@EndDate datetime,
	@AdEndDate datetime
)
RETURNS int
AS
BEGIN
	DECLARE @Result int
	
	SELECT @Result = Convert(int,
						Case When @AdEndDate<=@EndDate Then 
							0
						When @AdEndDate>@EndDate And @AdEndDate<dbo.EndMonth(@EndDate) Then 
							@AdEndDate-@EndDate 
						When @AdEndDate>=dbo.EndMonth(@EndDate) Then 
							dbo.EndMonth(@EndDate)-@EndDate 
						End
					)
	RETURN @Result

END



ALTER FUNCTION LiveDaysRemainingTotal 
(
	-- Add the parameters for the function here
	@EndDate datetime,
	@AdEndDate datetime
)
RETURNS int
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result int

	-- Add the T-SQL statements to compute the return value here
	SELECT @Result = Cast(Case When @AdEndDate-@EndDate<=0 Then 0 Else @AdEndDate-@EndDate End as int)

	-- Return the result of the function
	RETURN @Result



ALTER FUNCTION dbo.LiveDaysTotal 
	(
	@AdStartDate datetime,
	@AdEndDate datetime
	)
RETURNS int
AS
	BEGIN
	Declare @Result int
	
	Select @Result= Convert(int,
							Case 
								When @AdEndDate-@AdStartDate<=0 Then
									0
								Else
									@AdEndDate-@AdStartDate
								End
							)
	Return @Result
	END



ALTER FUNCTION dbo.PercentOfTotal
	(
	@ImpsDlvrdMTD bigint,
	@TotalTargetedDelivery bigint
	)
RETURNS real
AS
	BEGIN
	Declare @Result real
	Select @Result = 
				Case When @ImpsDlvrdMTD=0 Then 
					0 
				Else 
					@ImpsDlvrdMTD/(@TotalTargetedDelivery *1.0)
				End 
	RETURN @Result
	
	END
END

This query as it is works. You'll notice I have hard numbers plugged into some of the fields in the select statement. These are where it gets more complicated because I'm using functions within functions. The one I'm trying to do right now to replace what's currently there for [Mnth Forecast] which looks like this:


SumOfImpressions Delivered + dbo.DTEImps(End Date,Start Date,@EndDate,

SUM(tbl875Fixed.Booked) * dbo.PercentOfTotal(SUM(tblImpsLandedByCC.[SumOfImpressions Delivered]), SUM([Imps Targeted].[Targeted Imps])),

SUM(tbl875Fixed.[Total Campaign Delivered]) * dbo.PercentOfTotal(SUM(tblImpsLandedByCC.[SumOfImpressions Delivered]), 
SUM([Imps Targeted].[Targeted Imps])),

CASE WHEN dbo.LiveDaysMTD([Start Date] , [End Date] , @EndDate) = 0 THEN 0 ELSE SUM([SumOfImpressions Delivered]) / dbo.LiveDaysMTD([Start Date] , [End Date] , @EndDate) END)

As [Mnth Forecast]

Unfortunately, because all of these numbers are inter-related, there's no way to simplify this whole thing. Can anyone get their head around this?

Ted Baker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-27 : 02:01:05

why are you using functions to perform all these small operations. i think many of the operations can be done inline rather than calling functions. Wrap them in functions only if calculation is really complex. Wrapping each of the small caclutaions in functions will create overhead as for each caclculation it has call the function and get returned resultset.

tbaker818
Starting Member

3 Posts

Posted - 2008-09-28 : 03:28:15

Here's the problem - a formula might reference 3 or 4 of these functions several times. So to accomplish what you see in the last section of my previous message without using functions or simply referring to another calculated column like I do in Access, here's what would be required:


Case When 
						Convert(int,
						Case When [End Date] <= @EndDate Then 
							0
						When [End Date] > @EndDate And [End Date] < dbo.EndMonth(@EndDate) Then 
							[End Date] - @EndDate 
						When [End Date] >= dbo.EndMonth(@EndDate) Then 
							dbo.EndMonth(@EndDate) - @EndDate 
						End 
						) --Live Days Remaining MTD
					= 0 
					Then
						0
					Else
						Case When SUM(tbl875Fixed.[Total Campaign Delivered]) * 
						tblImpsLandedByCC.[SumOfImpressions Delivered] / 
						(SUM([Imps Targeted].[Targeted Imps]) * 1.0) --Imps Dlvrd Total
						> 
						SUM(tbl875Fixed.Booked) * tblImpsLandedByCC.[SumOfImpressions Delivered] / 
						(SUM([Imps Targeted].[Targeted Imps]) * 1.0) --Booked Total
						And [Booked] > 0
						Then 
							0 
						Else 
							Case When 
								SUM(tblImpsLandedByCC.[SumOfImpressions Delivered]) / 
								CASE WHEN [Start Date] < dbo.BegMonth(@EndDate) THEN 
									CASE WHEN [End Date] < dbo.BegMonth(@EndDate) THEN 
										0 
									ELSE 
										CASE WHEN [End Date] < @EndDate THEN 
											[End Date] - dbo.BegMonth(@EndDate) + 1 
										ELSE 
											@EndDate - dbo.BegMonth(@EndDate) + 1 
										END 
									END 
								ELSE 
									CASE WHEN [End Date] < @EndDate THEN 
										[End Date] - [Start Date] + 1 
									ELSE 
										@EndDate - [Start Date] + 1 
									END 
								END --Daily Dlvry
								* 
								Convert(int,
								Case When [End Date]<=@EndDate Then 
									0
								When [End Date]>@EndDate And [End Date]<dbo.EndMonth(@EndDate) Then 
									[End Date]-@EndDate 
								When [End Date]>=dbo.EndMonth(@EndDate) Then 
									dbo.EndMonth(@EndDate)-@EndDate 
								End
								)
								--Live Days Remaining MTD
								
								< 
								
								SUM(tbl875Fixed.Booked) * 
								CASE WHEN tblImpsLandedByCC.[SumOfImpressions Delivered] = 0 THEN 
									0 
								ELSE 
									tblImpsLandedByCC.[SumOfImpressions Delivered] / (SUM([Imps Targeted].[Targeted 

Imps]) * 1.0) 
								END --Booked Total
								- 
								SUM(tbl875Fixed.Booked) * tblImpsLandedByCC.[SumOfImpressions Delivered] / 
								(SUM([Imps Targeted].[Targeted Imps]) * 1.0) --Imps Dlvrd Total
								
								Or
								
								SUM(tbl875Fixed.Booked) * tblImpsLandedByCC.[SumOfImpressions Delivered] / 
								(SUM([Imps Targeted].[Targeted Imps]) * 1.0) --Booked Total
								= 0 Then 
									SUM(tblImpsLandedByCC.[SumOfImpressions Delivered]) / 
									CASE WHEN [Start Date] < dbo.BegMonth(@EndDate) THEN 
										CASE WHEN [End Date] < dbo.BegMonth(@EndDate) THEN 
											0 
										ELSE 
											CASE WHEN [End Date] < @EndDate THEN 
												[End Date] - dbo.BegMonth(@EndDate) + 1 
											ELSE @EndDate - dbo.BegMonth(@EndDate) + 1 
											END 
										END 
									ELSE 
										CASE WHEN [End Date] < @EndDate THEN 
											[End Date] - [Start Date] + 1 
										ELSE @EndDate - [Start Date] + 1 
										END 
									END --Daily Delivery
									* 
									Convert(int,
									Case When [End Date]<=@EndDate Then 
										0
									When [End Date]>@EndDate And [End Date]<dbo.EndMonth(@EndDate) Then 
										[End Date]-@EndDate 
									When [End Date]>=dbo.EndMonth(@EndDate) Then 
										dbo.EndMonth(@EndDate)-@EndDate 
									End 
									) --Live Days Remaining MTD
							Else 
								(SUM(tbl875Fixed.Booked) * 
								CASE WHEN tblImpsLandedByCC.[SumOfImpressions Delivered] = 0 THEN 
									0 
								ELSE 
									tblImpsLandedByCC.[SumOfImpressions Delivered] / (SUM([Imps Targeted].[Targeted 

Imps]) * 1.0) 
								END --Booked Total
								- 
								(SUM(tbl875Fixed.[Total Campaign Delivered]) * tblImpsLandedByCC.[SumOfImpressions 

Delivered]) / 
								(SUM([Imps Targeted].[Targeted Imps]) * 1.0)) --Imps Dlvrd Total
								/
								Convert(int,
								Case When [End Date]-@EndDate<=0 Then 
									0 
								Else 
									[End Date]-@EndDate 
								End 
								) --Live Days Remaining Total								*
								Convert(int,
								Case When [End Date]<=@EndDate Then 
									0
								When [End Date] > @EndDate And [End Date] <dbo.EndMonth(@EndDate) Then 
									[End Date] - @EndDate 
								When [End Date] >= dbo.EndMonth(@EndDate) Then 
									dbo.EndMonth(@EndDate) - @EndDate 
								End 
								) --Live Days Remaining MTD
							End
						End 
					End

Where in Access, it looks like this:

tblImpsLandedByCC.[SumOfImpressions Delivered] 
* 
IIf([LiveDaysRemainingMTD]=0,
 0,
 IIf([Imps Dlvrd Total]>[Booked Total] And [Booked Total]>0,
  0,
  IIf([Daily Dlvry]*[LiveDaysRemainingMTD]<[Booked Total]-
  [Imps Dlvrd Total] Or [Booked Total]=0,
   [Daily Dlvry]*[LiveDaysRemainingMTD],
   ([Booked Total]-[Imps Dlvrd Total])/[LiveDaysRemainingTotal]*[LiveDaysRemainingMTD])))

Ted Baker

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources