Hi,

I have problem with forming a select statement and I was hoping someone
could help...

In my application the user can enter the date that the project started
and the date when it ended.
However, because some parts of the date can be null the dates are stored
in seperate columns:

dateStartedYear, dateStartedMonth, dateStartedDay
dateEndedYear, dateEndedMonth, dateEndedDay

In the application I have to make a filter wher the user selected
two dates and if the projects "lies" between those two selected
dates then the row is selected.

How to do this?

Thanks,
sa¹o

Please post sample data, DDL would also be nice, and expected results.


ML

---
http://milambda.blogspot.com/

Hi,

Sample data:

daySt  monSt    yearSt  dayEnd  monEnd  yearEnd
15    8    1907    NULL    NULL    1907     (1)
15    8    1907    NULL    NULL    1907     (2)
9    2    1907    NULL    NULL    NULL     (3)
27    1    1907    NULL    NULL    NULL     (4)
19    2    1908    19    2    1908     (5)

Now I would pass in two dates:
e.g. 3.3.1907 and 2.2.1907
and I would like to get all the dates where the project duration (period
of time from project start and end) is between those two dates:

15    8    1907    NULL    NULL    1907     (1)
15    8    1907    NULL    NULL    1907     (2)

Is this enough information?

thanks,
sašo


Show quoteHide quote

Sa¨o Zagoranski (s***@skz.si) writes:
Show quoteHide quoteNo. At least for me it isn't.

I can understand if all End columns are NULL, then this means that the
project is still running. Then again, I would expect (3) to be included
in the output in such case. 

But what does it mean if only yearEnd is non-NULL? That the project
is due to end during 1907, but we don't know then? And what would it
mean if only, say, dayEnd or daySt are non-NULL?`

IF I don't understand the business rules, it's pointless to try to write
a query for it.

I'm sorry... I now see that I made a typo :(
And I also see that I didn't clarify enough:

First the mistake was here:
The second date is meant to be 2.2.1908! (and not 1907)

If some value is NULL that means that the value isn't known.

Let's say that ALL values are null then the start date shoudl be asumed
something very low: e.g. 1.1.1800
and the end date something very high: e.g. 31.12.2100

So in the select (3) would be translated into:
15.8.1907 - 31.12.2100 and that's why it isn't included

Number (1) would be translated into:
15.8.1907 - 31.12.1907 and that's why it should be in there

I'm sorry for the mistake and thanks for the reply

sa¨o

Show quoteHide quote

Sa¨o Zagoranski (s***@skz.si) writes:
Show quoteHide quoteSo rhe requirement is then that the project is running for the entire
period?

   SELECT ...
   FROM   (SELECT datestart = ltrim(str(coalesce(yearST, 1800))) +
                              CASE WHEN monthST IS NULL
                                   THEN '01'
                                   WHEN dayST BETWEEEN 1 AND 9
                                   THEN '0' + ltrim(str(monthST))
                                   ELSE ltrim(str(monthST))
                              END +
                              CASE WHEN dayST IS NULL
                                   THEN '01'
                                   WHEN dayST BETWEEEN 1 AND 9
                                   THEN '0' + ltrim(str(dayST))
                                   ELSE ltrim(str(dayST))
                              END,
                 dateend   = ltrim(str(coalesce(yearEND, 9999))) +
                              CASE WHEN monthEND IS NULL
                                   THEN '01'
                                   WHEN dayST BETWEEEN 1 AND 9
                                   THEN '0' + ltrim(str(monthEND))
                                   ELSE ltrim(str(monthEND))
                              END +
                              CASE WHEN dayEND IS NULL
                                   THEN CASE WHEN monthEND IS NULL
                                             THEN '31'
                                             WHEN monthEND IN (4, 6, 9, 11)
                                             THEN '30'
                                             WHEN monthEND = 2
                                             THEN '28'
                                             ELSE '31'
                                   END
                                   WHEN dayEND BETWEEEN 1 AND 9
                                   THEN '0' + ltrim(str(monthEND))
                                   ELSE ltrim(str(monthEND))
                              END,
                ...
           FROM tbl) AS x
     WHERE datestart >= @filterstart
       AND dateend   <= @filterend

I have ignored leap years.

Show quote Hide quote Thanks for your reply...
Although I still have some questions regarding your solution...

Let's say that all requiered data is inside a table called Projects...
And I want to get two columns ProjectInfo and ProjectNumber...

How would I modify the statement?

Sa¨o Zagoranski (s***@skz.si) writes:
That's what the ... in the query stands for.

The thing in parentheses is a so-called derived table. A derived table is
always a standalone query, and you can think of it as temp table within
the query. However, it is not materialised, and for a query like the
one I posted, the optimizer will recast the computation order as if the
derived table hadn't been there. I used it, so that I did not have to
repeat the complex expressions for datestart and dateend.

There are two ... in your query.

I understand the derived table concept I don't know how to position the
other columns.

The query now is like this:
SELECT ... FROM ( SELECT [derivedTableStuff], ... FROM Projects ) AS x
WHERE datestart >= @filterstart
        AND dateend   <= @filterend

Even if I leave the query as it is and replace the first ...
wtih * and remove the ... in the derived table query I get an error:
Msg 241, Level 16, State 1, Line 9
Syntax error converting datetime from character string.





Show quoteHide quote

Sa¨o Zagoranski (s***@skz.si) writes:
Yes. You would need to fill in the desired output columns in two places.

Which could be because there is some error in my construction of the
dates. Or simple incorrect data in your database, like and entry
with DaySt = 31 and MonthSt = 4.

Use the derived query and filter with

    isdate(datestart) = 0
    isdate(dateend)   = 0

to find the bad data - or my bug.

Combining 2 select statements
column dataType
Applying a function to a subquery
Case statement question
Tuning