2 CTE with if function

You can't. The SQL utilizing the CTE needs to follow immediately. You need to put the select into each IF branch hence:

If ...
begin
     with CTE
      ...
      select ...
end
else
begin
    with CTE
    ...
    select ...
end

Maybe I am misreading, but it seems like the only thing the IF statement is in there for is to replace NULL @InputDate with GetDate.  Can you try one CTE with COALESCE(@InputDate, GetDate()) appropriately put in OR use (@InputDate IS NULL OR YourDateColumn < @InputDate) for example.

Try this:

#1, you don't need to put the cte in an if, just set the @input variable if it's null
#2, it looks like the entire first dozen lines are just trimming  off the time portion of @inputdate, much easier / faster to convert and round.

ALTER procedure [dbo].[CustomerWebServiceGetByDate4]
	@InputGUID as varchar(50),
	@InputDate as datetime = '1753-01-01 00:00:000'
as
                                            
if(@InputDate is null) set @InputDate = GETDATE()	--this will handle the if statement

set @InputDate = convert(datetime, floor(convert(float, @InputDate)))	-- and this will truncate the time portion of the datetime


;with CTE 
as
(
	SELECT CustomerNo, ProductionDate, DiscontinuedDate, StopProductionDate, StartDate, StopDate, SupplierID,
	ROW_NUMBER() over (partition by Park_GSRN order by StopDate asc) RN 
	FROM Customer 
	LEFT JOIN tmtB ON Customer.CustomerNo = tmtB.Park_GSRN
	WHERE 
	(
		(ProductionDate <= @InputDate and ProductionDate IS NOT NULL)
		and (StopProductionDate IS NULL or StopProductionDate >= @InputDate)
		and (DiscontinuedDate >= @InputDate or DiscontinuedDate IS NULL)
		and Startdate <= @InputDate 
		and (StopDate >= @InputDate or StopDate IS NULL) 
		and SupplierID = @InputGUID
	)
	or   
	(
		(ProductionDate <= @InputDate and ProductionDate IS NOT NULL)
		and  (StopProductionDate IS NULL or StopProductionDate >= @InputDate)
		and (DiscontinuedDate >= @InputDate or DiscontinuedDate IS NULL)
		and
		(
			Startdate <= @InputDate and @InputDate >=  (SELECT DISTINCT MIN(StopDate) FROM tmtB  WHERE StopDate <= @InputDate) 
			and SupplierID = @InputGUID
		)
	)
)
select * from CTE
where A.RN = 1
order by A.CustomerNo

Select allOpen in new window

Hi,
You cannot put define a CTE as an alternative determined by an IF statement. You can either define both CTEs, and then use the IF statement to choose between them in the final SELECT; or rewrite things so that you have one single CTE that implements the functionality you want from your IF.
Looking at your code, the following things spring to mind to fix the errors;
- You seem to be writing to your argument @InputDate. Avoid that by creating a local variable
- Your arguments are defined incorrectly with "as" - that is not necessary. Just write @InputDate datetime = 'yourdefault'. BTW, the "zero" date in SQL server is 1/1/1900.
- You're forcing @InputDate to a format which you can achieve by simply using CONVERT and expression style 20. You also don't need to convert if you're comparing dates, which are really floats with special meaning assigned to the integer and the fractional parts.
- Your IF statement is based on whether @InputDate is null. It can never be null, you make sure of that by providing a default value.
- You seem to be wanting to implement the logic: "If I have an input date, use that as my argument. Otherwise, use the current date". It is much easier to create a variable like this:

declare @myArgDate datetime

IF @InputDate = '1753-01-01 00:00:000'
BEGIN
  SET @myArgDate = getDate()
END
ELSE BEGIN
  SET #myArgDate = @InputDate
END

and then use the variable in your CTE expression.
I notice that you use @InputDate only when it is null (which never happens). Is that intentional or a mistake? Did you mean to use the current date (getDate()) only when @inputDate is NOT null?

HTH

Philippe

mwvisa1 you are not misreading!
Could you try to embed the COALESCE  in my code?

Code, for illustration:

USE [SQL2008_640987_merevind]
GO
/****** Object:  StoredProcedure [dbo].[CustomerWebServiceGetByDate4]    Script Date: 05/05/2011 23:35:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[CustomerWebServiceGetByDate4]
		
@InputGUID varchar(50),
@InputDate datetime
        
as

DECLARE @myArgDate datetime

If (@InputDate IS NULL) SET @myArgDate = CONVERT(varchar, getDate(), 20)
ELSE @myArgDate = CONVERT(varchar, @InputDate, 20)


;with CTE as
  (
	SELECT
	CustomerNo,
	ProductionDate,
	DiscontinuedDate,
	StopProductionDate,
	StartDate,
	StopDate,
	SupplierID,
	ROW_NUMBER() over (partition by Park_GSRN order by StopDate asc) RN 
	FROM Customer LEFT JOIN tmtB ON Customer.CustomerNo = tmtB.Park_GSRN
	WHERE ((ProductionDate <= @InputDate and ProductionDate IS NOT NULL)
						and 
						(StopProductionDate IS NULL or StopProductionDate >= @myArgDate)
						and
						(DiscontinuedDate >= @myArgDate or DiscontinuedDate IS NULL)
						and
						Startdate <= @myArgDate 
						and
						(StopDate >= @myArgDate or StopDate IS NULL) 
						and SupplierID = @InputGUID)
	or   
						((ProductionDate <= @myArgDate and ProductionDate IS NOT NULL)
						and 
						(StopProductionDate IS NULL or StopProductionDate >= @myArgDate)
						and
						(DiscontinuedDate >= @myArgDate or DiscontinuedDate IS NULL)
						and
						(Startdate <= @myArgDate and @myArgDate >= 
						(SELECT DISTINCT MIN(StopDate) 
						FROM tmtB 
						WHERE StopDate<= @myArgDate) 
						and SupplierID = @InputGUID))
  )



select * from CTE
where A.RN = 1
order by A.CustomerNo

Select allOpen in new window

using coalesce it would be:

@myArgDate = COALESCE(@InputDate, getDate())

Incidentally, your where clause is 90% the same in both as well, that can also be trimmed for better performance.

And converting a date to a number and back will outperform converting it to a varchar.
Try this:

Incidentally, I dont' this line:
@InputDate >=  (SELECT DISTINCT MIN(StopDate) FROM tmtB  WHERE StopDate <= @InputDate)

Is going to work as you see fit, this is going to get the min stopdate from the entire table, not just the joined results.

ALTER procedure [dbo].[CustomerWebServiceGetByDate4]
	@InputGUID as varchar(50),
	@InputDate as datetime = '1753-01-01 00:00:000'
as
                                            
if(@InputDate is null) set @InputDate = GETDATE()	--this will handle the if statement
set @InputDate = convert(datetime, floor(convert(float, @InputDate)))	-- and this will truncate the time portion of the datetime

;with CTE 
as
(
	SELECT CustomerNo, ProductionDate, DiscontinuedDate, StopProductionDate, StartDate, StopDate, SupplierID,
	ROW_NUMBER() over (partition by Park_GSRN order by StopDate asc) RN 
	FROM Customer 
	LEFT JOIN tmtB ON Customer.CustomerNo = tmtB.Park_GSRN
	where
		(ProductionDate <= @InputDate) --and ProductionDate IS NOT NULL)	..dont' need this line, if it's null the ProductionDate <= @input will never return true
		and (StopProductionDate IS NULL or StopProductionDate >= @InputDate)
		and (DiscontinuedDate >= @InputDate or DiscontinuedDate IS NULL)
		and Startdate <= @InputDate 
		and SupplierID = @InputGUID
		and
		(
			(StopDate >= @InputDate or StopDate IS NULL) 
			or
			@InputDate >=  (SELECT DISTINCT MIN(StopDate) FROM tmtB  WHERE StopDate <= @InputDate) 
		)
)
select * from CTE
where A.RN = 1
order by A.CustomerNo

Select allOpen in new window

Thank you guys for you input. It is much better than using the if statement in concern to the @inputdate is null.

My query now looks like this:

 

GO
/****** Object:  StoredProcedure [dbo].[CustomerWebServiceGetByDate5]    Script Date: 05/06/2011 01:03:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[CustomerWebServiceGetByDate5]
	@InputGUID as varchar(50),
	@InputDate as datetime = '1753-01-01 00:00:000'
as
                                            
if(@InputDate is null) set @InputDate = GETDATE()	--this will handle the if statement
set @InputDate = convert(datetime, floor(convert(float, @InputDate)))	-- and this will truncate the time portion of the datetime

;with CTE
as
(
	SELECT
	Park_GSRN,
	StartDate,
	StopDate,
	SupplierID,
	ROW_NUMBER() over (partition by Park_GSRN order by StopDate desc) RN 
	FROM tmtB
	WHERE (Startdate <= @InputDate and (StopDate >= @InputDate or StopDate IS NULL) and SupplierID = @InputGUID)
    or   (Startdate <= @InputDate and @InputDate >= 
              (SELECT DISTINCT MAX(StopDate) 
               FROM tmtB 
               WHERE StopDate<= @InputDate) and SupplierID = @InputGUID)
)

select * from CTE A
where A.RN = 1
order by A.Park_GSRN

Select allOpen in new window

When I execute my query are a bit slow though. Can it bee speed-ed up  a bit??

Looks like you have some good examples.

Note you can do this all in one step:

set @InputDate = datediff(dd, 0, coalesce(@InputDate, GetDate()))

That will replace null @inputdate with getdate() results and regardless of whether @InputDate was null or not, the resulting datetime will be stripped back to that day at midnight.

In 2008, you can just convert to date to get rid of time portion.

set @InputDate = convert(date, coalesce(@InputDate, GetDate()))

True, Sharath.  Thanks for the reminder.  Love tighter code. :)
Now the trick is to see what is going on in the WHERE clause to help speed things up.  

@Snarf0001:

Your latest suggestion get me this error:

Msg 137, Level 15, State 2, Procedure CustomerWebServiceGetByDate6, Line 23
Must declare the scalar variable "@StopDate".

Are Startdate, stopdate and SupplierID indexed? How big is the table in # of rows? If not, they definitely should be.

There is just a small error in @Snarf0001's code, in the WHERE clause - @StopDate (var) instead of Stopdate(field)

Also try replacing this:

select @maxStop = MAX(StopDate) from tmtB WHERE StopDate <= @InputDate

with this:

select @maxStop = MAX(StopDate) from tmtB WHERE StopDate <= @InputDate and SupplierdID = @InputGUID

How do I correct: There is just a small error in @Snarf0001's code, in the WHERE clause - @StopDate (var) instead of Stopdate(field)

Replace:

and (StopDate >= @InputDate or @StopDate is null or

with:

and (StopDate >= @InputDate or StopDate is null or

Great!
I have also exe. the followering:

CREATE INDEX TimeIndex
ON tmtB (StartDate, Stopdate, SupplierID)

Do I need to do anything else to take effect of the indexing?
There is about 9000 record in the tmtB table.

That'll work. 9000 rows is probably too few rows to make a real difference though. Are the date fields really dates or are they varchars?

Alright, thanks.

All the date columns are datetime data type.

 

Your index should take care of it then. Did it make a difference?
If not, try looking at the execution plan to see if anything looks illogical. Press Ctrl-M in the query window in SQL management studio. Then, after you run the query, you'll get an extra tab with the execution plan, which you can upload here as an image.
I can also try running it locally if you upload a copy of the table with any identifiable data changed. It sounds like it's all just dates and codes anyway. An excel spreadsheet will do the job nicely. 9000 rows is nothing in SQL server terms. I can't see anything on the face of it that would lead to problems.

One thing: Looking at your latest code, I see your statement:

if(@InputDate is null) set @InputDate = GETDATE()

This will never result in @InputDate being set to the current date because it will never be null (you supplied a default). remove the default in the argument list in order for this to work.

Yes it did. I have looked at the execution plan, but will have to read some theory first. But thanks anyway. The speed is much better now.

by the "the default in the argument" you refer to: = '1753-01-01 00:00:000' correct ?

While I have been working on the complex part of the query I have just used a simplified "hotfix" for the supplierID.
Now I need to expand the query so it joins my Supplier table.

Supplier _table
- SupplierID int
- SupplierGUID uniqueidentifier

Records:

SupplierID                              Supplier GUID
     2               CC3372A0-781F-11E0-B0A2-CC144924019B
     4               E27F6E9C-781F-11E0-BA99-D6144924019B

So the real @InputGUID is going to one of  GUID's from the Supplier GUID colum.  

You parse @InputDate and @InputGUID and the @InputGUID should then be used to select all record with tmtB.supplierID = Supplier.SupplierID where Supplier.SupplierID = Supplier.SupplierGUID where Supplier.SupplierGUID = @InputGUID.


I have changed the code to the following but get this error:

Msg 206, Level 16, State 2, Procedure CustomerWebServiceGetByDate6, Line 10
Operand type clash: uniqueidentifier is incompatible with int

Here is the code:

 

GO
/****** Object:  StoredProcedure [dbo].[CustomerWebServiceGetByDate6]    Script Date: 05/06/2011 21:52:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[CustomerWebServiceGetByDate6]
	@InputGUID as uniqueidentifier,
	@InputDate as datetime
as
                                            
if(@InputDate is null) set @InputDate = GETDATE()	--this will handle the if statement
set @InputDate = convert(datetime, floor(convert(float, @InputDate)))	-- and this will truncate the time portion of the datetime

declare @maxStop datetime
select @maxStop = MAX(StopDate) from tmtB WHERE StopDate <= @InputDate and SupplierID = @InputGUID



CREATE TABLE #tmtBalance
(Park_GSRN varchar(50),
StartDate datetime,
StopDate datetime,
SupplierID int,
RN int)

;with CTE 
as
(
	
	SELECT
	Park_GSRN,
	StartDate,
	StopDate,
	SupplierID,
	ROW_NUMBER() over (partition by Park_GSRN order by StopDate desc) RN 
	FROM (Customer LEFT JOIN tmtB ON Customer.CustomerNo = tmtB.Park_GSRN) LEFT JOIN Vendor ON tmtB.SupplierID=Vendor.id
	where (Startdate <= @InputDate and SupplierGUID = @InputGUID
	and (StopDate >= @InputDate or StopDate is null or 
	@InputDate >= @maxStop --entire subquery replaced here
))
)
INSERT INTO #tmtBalance
select * from CTE A
where A.RN = 1
order by A.Park_GSRN

DROP TABLE #tmtBalance

Select allOpen in new window

Correct, the default is set by the "= '1753-01-01 00:00:000' part of the argument definition."

I suspect the error comes either from the join expression CustomerNo=tmtB.Park_GSRN or tmtB.SupplierID=Vendor.id. Make sure these join expressoins compare fields of the same GUID type.

Also, I notice a mistake in the line :

      where (Startdate <= @InputDate and SupplierGUID = @InputGUID

I believe it should be:

      where (Startdate <= @InputDate and SupplierID = @InputGUID

Let me know. Sending the structures of the tables concerned would be helpful. thanks!

I have delete the default value for @InputDate.

Here are the structures of the tables:

Customer_table:
- CustomerNo varchar(50)
- ProductionDate datetime
- DiscontinuedDate datetime
- StopProductionDate datetime

tmtB_table:
Park_GSRN varchar(50)
SupplierID int
StartDate datetime
StopDate datetime

Vendor_table
- ID int
- vendorGUID uniqueidentifier


The code:


 

GO
/****** Object:  StoredProcedure [dbo].[CustomerWebServiceGetByDate6]    Script Date: 05/06/2011 23:48:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[CustomerWebServiceGetByDate6]
	@InputGUID as uniqueidentifier,
	@InputDate as datetime = '1753-01-01 00:00:000'
as
                                            
if(@InputDate is null) set @InputDate = GETDATE()	--this will handle the if statement
set @InputDate = convert(datetime, floor(convert(float, @InputDate)))	-- and this will truncate the time portion of the datetime

declare @maxStop datetime
select @maxStop = MAX(StopDate) from tmtB WHERE StopDate <= @InputDate and SupplierID = @InputGUID



--CREATE TABLE #tmtBalance
--(Park_GSRN varchar(50),
--StartDate datetime,
--StopDate datetime,
--SupplierID int,
--RN int)

;with CTE 
as
(
	
	SELECT
	Park_GSRN,
	StartDate,
	StopDate,
	SupplierID,
	ROW_NUMBER() over (partition by Park_GSRN order by StopDate desc) RN 
	FROM (Customer LEFT JOIN tmtB ON Customer.CustomerNo = tmtB.Park_GSRN) LEFT JOIN Vendor ON tmtB.SupplierID=Vendor.id
	where (Startdate <= @InputDate and vendorGUID = @InputGUID
	and (StopDate >= @InputDate or StopDate is null or 
	@InputDate >= @maxStop --entire subquery replaced here
))
)
--INSERT INTO #tmtBalance
select * from CTE A
where A.RN = 1
order by A.Park_GSRN

--DROP TABLE #tmtBalance

Select allOpen in new window

I now get this error:

Msg 206, Level 16, State 2, Procedure CustomerWebServiceGetByDate6, Line 10
Operand type clash: uniqueidentifier is incompatible with int

The error could come form the fact that you define InputGUID as a uniqueIdenfier:
@InputGUID as uniqueidentifier

but then use it to compare with SupplierID, which according to your post is an int:
select @maxStop = MAX(StopDate) from tmtB WHERE StopDate <= @InputDate and SupplierID = @InputGUID

Either change the required argument to int, or compare the InputGUID with the appropriate field (not SupplierID).

HTH

Philippe

You were still referencing the int -> Guid in the first max(..) select statement.
Since you need to use the value twice, I'd just split it out into a separate statement, rather than joining twice:

GO
/****** Object:  StoredProcedure [dbo].[CustomerWebServiceGetByDate6]    Script Date: 05/06/2011 23:48:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[CustomerWebServiceGetByDate6]
	@InputGUID as uniqueidentifier,
	@InputDate as datetime = '1753-01-01 00:00:000'
as
                                            
if(@InputDate is null) set @InputDate = GETDATE()	--this will handle the if statement
set @InputDate = convert(datetime, floor(convert(float, @InputDate)))	-- and this will truncate the time portion of the datetime

declare @maxStop datetime, @vendorID int
--grab the integer value here
select @vendorID = ID from Vendor_table where vendorGUID = @InputGUID
--now user the new int id here
select @maxStop = MAX(StopDate) from tmtB WHERE StopDate <= @InputDate and SupplierID = @vendorID


--CREATE TABLE #tmtBalance
--(Park_GSRN varchar(50),
--StartDate datetime,
--StopDate datetime,
--SupplierID int,
--RN int)

;with CTE 
as
(
	
	SELECT
	Park_GSRN,
	StartDate,
	StopDate,
	SupplierID,
	ROW_NUMBER() over (partition by Park_GSRN order by StopDate desc) RN 
	FROM (Customer LEFT JOIN tmtB ON Customer.CustomerNo = tmtB.Park_GSRN)-- LEFT JOIN Vendor ON tmtB.SupplierID=Vendor.id
	where (Startdate <= @InputDate and tmtB.SupplierID = @vendorID --vendorGUID = @InputGUID
	and (StopDate >= @InputDate or StopDate is null or 
	@InputDate >= @maxStop --entire subquery replaced here
))
)
--INSERT INTO #tmtBalance
select * from CTE A
where A.RN = 1
order by A.Park_GSRN

--DROP TABLE #tmtBalance

Select allOpen in new window

I actually got it to work with the following:  

 

GO
/****** Object:  StoredProcedure [dbo].[CustomerWebServiceGetByDate6]    Script Date: 05/07/2011 22:09:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[CustomerWebServiceGetByDate6]
	@InputGUID as uniqueidentifier,
	@InputDate as datetime = '1753-01-01 00:00:000'
as
                                            
if(@InputDate is null) set @InputDate = GETDATE()	--this will handle the if statement
set @InputDate = convert(datetime, floor(convert(float, @InputDate)))	-- and this will truncate the time portion of the datetime


declare @maxStop datetime
select @maxStop = MAX(StopDate) from tmtB WHERE StopDate <= @InputDate



CREATE TABLE #tmtBalance
(Park_GSRN varchar(50))


;with CTE 
as
(
	
	SELECT
	Park_GSRN,
	ProductionDate,
	DiscontinuedDate,
	StopProductionDate,
	StartDate,
	StopDate,
	SupplierID,
	ROW_NUMBER() over (partition by Park_GSRN order by StopDate desc) RN 
	FROM (Customer LEFT JOIN tmtB ON Customer.CustomerNo = tmtB.Park_GSRN) LEFT JOIN Vendor ON tmtB.SupplierID=Vendor.id
	where ((ProductionDate <= @InputDate and ProductionDate IS NOT NULL)
						and 
                        (StopProductionDate IS NULL or StopProductionDate >= @InputDate)
                        and
                        (DiscontinuedDate >= @InputDate or DiscontinuedDate IS NULL)
                        and
	Startdate <= @InputDate and vendorGUID = @InputGUID
	and (StopDate >= @InputDate or StopDate is null or 
	@InputDate >= @maxStop --entire subquery replaced here
))
)
INSERT INTO #tmtBalance
select Park_GSRN from CTE A
where A.RN = 1
order by A.Park_GSRN

DROP TABLE #tmtBalance

Select allOpen in new window

What do you think of that?

Yes, I can parse @InputDate as datetime and @InputGUID as uniqueidentifier. That delivers the needed security element when the SP is going to be publish as a web services exposed to all my suppliers.

I have one last challenge though.
In the developing phase I have been using some pseudo tables.  The "real" tables is actually on a remote server so I need to use OPENROWSET.  

I am not sure how to do that...

THat would be a separate question, Madsing. Please assign points and I can help you with the next one too. What is the RDBMS of the remote server?

Sure, I'll assign point and thank you very much all of you!
I really appreciate your time and effort.


 

Here is the link to the other question:
https://www.experts-exchange.com/questions/27024888/Embed-OPENROWSET-in-CTE-expression.html