Passing Variables down the Sub-subqueries

Question

Post reply

Passing Variables down the Sub-subqueries

Suresh Alluri

SSC Veteran

Points: 246
More actions
July 31, 2006 at 2:31 pm

#169435

Hi,
I have two SQLs
Select convert(varchar,DateAdd(mm, DateDiff(mm, 0, dateadd(mm, -2, Getdate())),0),112) as begindate
Select convert(varchar,DateAdd(mm, DateDiff(mm, 0, dateadd(mm, -1, Getdate())),0),112) as enddate
The output of these queries are 20060501 and 20060601. I am storing that values in begindate and enddate. I want to use these values down the stream (in sub-subqueries). I have to use these values for a very complecated query. Every month these value changes. Right now we are manually changing the dates and running the query. I got this query from this website but trying to implement in our query. I tried and it is giving me
"Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@Begindate'.
[OLE/DB provider returned message: Deferred prepare could not be completed.]"
I even tried to declare those values at the top, but it is sill giving me the same error.
Could somebody please provide me some solution in passing these values down the subquries.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Julie Hargraves SSCarpal Tunnel Points: 4457 More actions · Answer 1

Try like this:

Declare @BeginDate varchar(8)

Declare @EndDate varchar(8)

Select @BeginDate = convert(varchar,DateAdd(mm, DateDiff(mm, 0, dateadd(mm, -2, Getdate())),0),112)

Select @EndDate = convert(varchar,DateAdd(mm, DateDiff(mm, 0, dateadd(mm, -1, Getdate())),0),112)

Then you can use @BeginDate and @EndDate wherever you want.

Suresh Alluri SSC Veteran Points: 246 More actions · Answer 2

I tried but still same error.

"Server: Msg 8180, Level 16, State 1, Line 1

Statement(s) could not be prepared.

Server: Msg 137, Level 15, State 1, Line 1

Must declare the variable '@Begindate'.

[OLE/DB provider returned message: Deferred prepare could not be completed.]"

Bhushan Default port Points: 1427 More actions · Answer 3

try like this...

use adventureworks

SELECT h.* FROM sales.SalesOrderHeader h join

(Select

convert(varchar,DateAdd(mm, DateDiff(mm, 0, dateadd(mm, -2, Getdate())),0),112) begindate,

convert(varchar,DateAdd(mm, DateDiff(mm, 0, dateadd(mm, -1, Getdate())),0),112) enddate

) as Dt

on

h.Orderdate between dt.begindate and dt.enddate

John Rowan SSC Guru Points: 56440 More actions · Answer 4

You will get more effective help if you submit your SQL code. It is hard to determine exactly what is causing your error if we do not know what your code looks like.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Suresh Alluri SSC Veteran Points: 246 More actions · Answer 5

Here is the actual query. The variables we pass are used in openquery select statement where he it will be executed in another server.

Drop table My_main_table;

CREATE Table My_Main_Table

( Request_Type VARCHAR(20),

Issue_ID INT,

Submit_Date char(25),

Close_Date char (25),

Priority INT,

Days INT,

Weekend_holiday_count INT,

Act_num_of_working_days INT

)

go

Insert into My_Main_Table (Request_Type,

Issue_ID,

Submit_Date,

Close_Date,

Priority,

Days,

Weekend_holiday_count,

Act_num_of_working_days)

(select Result.Requet_Type

,Result.Issue_ID

,Result.Submit_Date

,Result.Close_Date

,Result.Priority

,Result.Days

,Result.weekend_holiday_count

,Result.Days - Result.weekend_holiday_count as Act_Num_of_working_days

from

(select Requet_Type

,init_result.Issue_ID

,Submit_Date

,Close_Date

,Priority

,CASE

WHEN DATEDIFF(d, Submit_Date,Close_Date)< 0 THEN 0

ELSE DATEDIFF(d, Submit_Date,Close_Date)

END AS DAYS

,Case count_result.weekend_holiday_count

when count_result.weekend_holiday_count then count_result.weekend_holiday_count

else 0

end as weekend_holiday_count

from openquery (team_track_server,'

SELECT CASE COD.TS_TECHARCH_REQUEST_TYPE

WHEN 3777 THEN ''Service Request''

WHEN 3778 THEN ''Issue''

WHEN 5153 THEN ''DCR Request''

END AS Requet_Type

, COD.TS_ISSUEID AS Issue_ID

, CASE

WHEN DATEPART(hour,DATEADD(s, COD.TS_SUBMITDATE, ''19700101'')) >= 15

THEN DATEADD(s, COD.TS_SUBMITDATE, ''19700102'')

ELSE DATEADD(s, COD.TS_SUBMITDATE, ''19700101'')

END AS Submit_Date

, DATEADD(s, COD.TS_SUBMITDATE, ''19700102'') AS Submit_Date

, CASE COD.TS_CLOSEDATE

WHEN COD.TS_CLOSEDATE THEN DATEADD(s, COD.TS_CLOSEDATE, ''19700101'')

ELSE getdate()

END AS Close_Date

, CASE

WHEN COD.TS_SEVERITY = 1996 AND COD.TS_TECHARCH_REQUEST_TYPE = 3778 THEN 1

WHEN COD.TS_SEVERITY = 1997 AND COD.TS_TECHARCH_REQUEST_TYPE = 3778 THEN 2

WHEN COD.TS_SEVERITY = 1998 AND COD.TS_TECHARCH_REQUEST_TYPE = 3778 THEN 2

WHEN COD.TS_SEVERITY = 1999 AND COD.TS_TECHARCH_REQUEST_TYPE = 3778 THEN 3

WHEN COD.TS_SEVERITY = 1996 AND COD.TS_TECHARCH_REQUEST_TYPE = 3777 THEN 1

WHEN COD.TS_SEVERITY = 1997 AND COD.TS_TECHARCH_REQUEST_TYPE = 3777 THEN 2

WHEN COD.TS_SEVERITY = 1998 AND COD.TS_TECHARCH_REQUEST_TYPE = 3777 THEN 3

WHEN COD.TS_SEVERITY = 1999 AND COD.TS_TECHARCH_REQUEST_TYPE = 3777 THEN 4

WHEN COD.TS_SEVERITY = 1996 AND COD.TS_TECHARCH_REQUEST_TYPE = 5153 THEN 1

WHEN COD.TS_SEVERITY = 1997 AND COD.TS_TECHARCH_REQUEST_TYPE = 5153 THEN 2

WHEN COD.TS_SEVERITY = 1998 AND COD.TS_TECHARCH_REQUEST_TYPE = 5153 THEN 3

WHEN COD.TS_SEVERITY = 1999 AND COD.TS_TECHARCH_REQUEST_TYPE = 5153 THEN 4

END as Priority

FROM teamtrack.UCO_COD_SRM COD

INNER JOIN

(SELECT DATEDIFF(s, ''19700101'', @Begindate) AS Min_Date

, DATEDIFF(s, ''19700101'', @Enddate) AS Max_Date) Date_Range

ON

COD.TS_SUBMITDATE >= Date_Range.Min_Date AND COD.TS_SUBMITDATE < Date_Range.Max_Date OR

COD.TS_CLOSEDATE >= Date_Range.Min_Date AND COD.TS_CLOSEDATE < Date_Range.Max_Date

WHERE (COD.TS_ISSUEID LIKE ''50%'')

and COD.TS_PROJECTID = 36

ORDER BY COD.TS_TECHARCH_REQUEST_TYPE , COD.TS_SEVERITY') as init_result left join

(

select count(Date_Col) as weekend_holiday_count

,Issue_ID

from openquery (team_track_server,'