Parameter Start Date, End Date and the result on the next row

816 views c#
4

Good day! I want to ask a question. I'm using sql server 2012, and creating storeprocedure. here's my table.

ID    StartDate        EndDate
1     2013-10-01       2013-10-20
2     2013-10-21       2013-10-30
3     2017-11-02       2017-11-05
4     2018-11-10       2018-11-25
5     2018-11-10       2018-11-25
6     2019-11-10       2019-11-25

and I want to create an alias column beside enddate, lets say period. the result value is from filter parameter @StartDate and @EndDate.

my simple query

select StartDate, '' as Result from vessel a where YEAR(A.StartDate) >= 2018  and YEAR(a.StartDate) <= 2019 order by a.StartDate desc

so if I declare

I want to expect like this

ID    StartDate        EndDate        Result
4     2018-11-10       2018-11-25
5     2018-11-10       2018-11-25
6     2019-11-10       2019-11-25

and I don't know what are the value of Result column? because the user wants 'Result' column = Data from Filter Start Date – End Date

any idea?

thank you for helping me.

answered question

For row 4, what exactly do you expect Result to be, and why?

You're aliasing an empty string as "Result". Only you can tell us the value you want the Result column to contain.

I think there must be a mistakes from the document specification, maybe the the Result column shouldn't exists, right?

Is there a result column in your database table? If there is, your query isn't using that column.

1 Answer

6

As you are aliasing an empty string, you are getting result as expected. Are you trying to do this:

select 'Id', 'StartDate', 'EndDate', 'Result'
union all
select Id, StartDate, EndDate, datediff(day, StartDate, EndDate) as Result from vessel a where YEAR(A.StartDate) >= 2018  and YEAR(a.StartDate) <= 2019 order by a.StartDate desc

posted this

Have an answer?

JD

Please login first before posting an answer.