where condition parameter

Question

Post reply

where condition parameter

codrutza_m

SSC Journeyman

Points: 90
More actions
July 1, 2011 at 6:59 am

#240218

Maybe you help me with this:
I have to write a procedure
Create proc with parameter @deparr
Select * from t
where FORMULA
and Year(@deparr)=year(result of FORMULA )
FORMULA
if t.type=import then t.arrdate
if t.type=export then t.depdate
How do I write this, please

Viewing 10 posts - 1 through 9 (of 9 total)

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

Veronka Default port Points: 1425 More actions · Answer 1

something like this.

CREATE PROCEDURE usp_GetT

@Type varchar(16) == import/export

AS

SELECT * from t

WHERE (@Type = 'import' AND Year(@deparr) = Year(t.arrdate))

OR (@Type = 'export' AND Year(@deparr) = Year(t.depdate))

codrutza_m SSC Journeyman Points: 90 More actions · Answer 2

I have to do a report which has to have parameter year(@deparr)

Then the procedure has to be something like that

CREATE PROCEDURE usp_GetT

year(@deparr) smallint

AS

....

And how/when to write @Type?

Veronka Default port Points: 1425 More actions · Answer 3

what kind of report? Crystal? SSRS?

you just create a paramenter, hardcote the values and pass them in?

please be more specific, I don't understand your Q?

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

This *may* be more performant

Create proc Search @deparr datetime

as

declare @deparr datetime

Select @deparr = '20110501'

Declare @YearStart datetime

Declare @NextYear datetime

Select @YearStart = DATEADD(yy, DATEDIFF(yy,0,@deparr), 0)

Select @NextYear = dateadd(yy,1,@YearStart)

Select *

from t

where t.type ='import'

and t.arrdate >=@YearStart

and t.arrdate < @NextYear

union all

Select *

from t

where t.type ='export'

and t.depdate >=@YearStart

and t.depdate < @NextYear

Clear Sky SQL
My Blog[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

-- simplest

SELECT *

FROM t

WHERE YEAR(CASE

WHEN t.[type] = 'import' THEN t.arrdate

WHEN t.[type] = 'export' THEN t.depdate

ELSE NULL END) = YEAR(@deparr)

-- possibly faster

SELECT *

FROM t

WHERE (t.[type] = 'import' AND YEAR(t.arrdate) = YEAR(@deparr))

OR (t.[type] = 'export' AND YEAR(t.depdate) = YEAR(@deparr))

-- fastest (SARGable)

SELECT *

FROM t

WHERE (t.[type] = 'import' AND t.arrdate BETWEEN [start of year](@deparr) AND [end of year](@deparr))

OR (t.[type] = 'export' AND t.depdate BETWEEN [start of year](@deparr) AND [end of year](@deparr))

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

codrutza_m SSC Journeyman Points: 90 More actions · Answer 6

Sorry, I try to explain better. Forgive my English.

I have developed a report with CrystalReports, based directly on tables, and I solved the problem. But now I have to do the same report in CrystalReports, but based on a stored procedure. The procedure has to retrieve data for a year. This year has to be year(arrdate) for type=import or year(depdate) for type=export.

Then when I run the report in CrystalReports, I have to input just 2010 or 2011 or wherever year and retrieves all the data sales, costs, .....for that year.

codrutza_m SSC Journeyman Points: 90 More actions · Answer 7

Thank you, Dave. Maybe I’ll use your solution in other report.

Thank you, Chris. Your solution made sense for me.

:satisfied:

Veronka Default port Points: 1425 More actions · Answer 8

Modify your sp to have a column for Type (dummy).

1) In CR, DB Expert you can reference the stored procedure

2) Add a report parameter.

3) in Selection Expert create a condition based on that parameter and the dummy column.

---------------------------------------------

OR

If you have your report based on the tables, you can create expressions to handle the same thing.

codrutza_m SSC Journeyman Points: 90 More actions · Answer 9

codrutza_m

SSC Journeyman

Points: 90

July 1, 2011 at 9:29 am

#1347108

Thanks, vcapone