SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Interesting SELECT


Interesting SELECT

Author
Message
zajo1969
zajo1969
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 167
Hello,
I have this situation. I have a table #T1

CREATE TABLE #T1 (
cislo int,
datod smalldatetime,
datdo smalldatetime,
utvar varchar(24)
)


and here are these data

cislo datod datdo utvar
515 2002-04-01 00:00:00 2003-01-31 00:00:00 4004
515 2003-02-01 00:00:00 2006-07-17 00:00:00 7003
515 2006-07-18 00:00:00 2007-12-31 00:00:00 0000
515 2008-01-01 00:00:00 2008-10-27 12:15:00 7003


I have a storedProcedure with Input parameters:
@DatumOd as SmallDateTime
@DatumDo as SmallDateTime

and They have values
@DatumOd='20021201'
@DatumDo='20021231'

I have to select records , which are in this range. For this example, it have to select this record
515 2002-04-01 00:00:00 2003-01-31 00:00:00 4004


I try this:
select * from #T1r
where cislo=515 and
((datod between '20021201' AND '20021231') OR (datdo between '20021201' AND '20021231'))

or

select * from #T1
where cislo=515 and
((datod>='20021201' AND datdo<='20021231')) AND
(datdo '20021231'))

Could you help me with correct select command or some udf function?

Thank you
Out For Justice
Out For Justice
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1381 Visits: 24
I think your sql is correct - the reason no rows are being returned is because there is no data that falls within the date range of your parameters.
If you change the sql to

((datod between '20020101' AND '20021231') OR (datdo between '20021201' AND '20021231')) you will see you get 1 row returned.

In your data set there are no rows that have datod or datdo between 01-Dec-2002 and 31-Dec-2002 which is what you have your variables set to.
AnzioBake
AnzioBake
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1262 Visits: 700
It looks like you are swaping the Variable and the columns
Based on your parameter and expected result.....



Declare
@DatumOd SmallDateTime
,@DatumDo SmallDateTime

Select
@DatumOd='20021201'
,@DatumDo='20021231'

Select *
From #T1
Where
( @DatumOD >= Datod And @DatumOD <= Datdo )
And
( @DatumDo >= Datod And @DatumDo <= Datdo )







Sergiy
Sergiy
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40169 Visits: 12598
Martin,

Your question has nothing to do with T-SQL.
It's a task from school math.

To find the correct answer you need:
- stop browsing Internet for it;
- take a piece of paper;
- draw a time line on it;
- mark all time intervals from the table on the time line;
- mark the search interval on the same line.

Draw several different variations of the picture: with overlapping intervals, with one search interval covering several table intervals, etc.

Allocate 5 minutes of your time to analyze the pictures and figure out what are the criteria for selecting the right intervals.

I'm pretty sure you can find the answer.
zajo1969
zajo1969
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 167
Hello,

To Sergiy:
You are right. The first what I did was an analyze and developed diagram. Then I decided that I have to check every date from input range if it is between datod AND datdo.

May be I described my problem wrong. What I needed was join "utvar" from #T1 with every date from #T2. Table #T2 is created by input date range.

I have source data #T1:
cislo datod datdo utvar
3779 2002-04-01 00:00:00 2002-11-30 00:00:00 7003
3779 2002-12-01 00:00:00 2003-04-30 00:00:00 8007
3779 2003-05-01 00:00:00 2008-10-28 08:17:00 7003

Input date range:
@DatumOd='20030420'
@DatumDo='20030512'

Source data #T2:
cislo datum kodds hodin
3779 2003-04-23 00:00:00 100 495
3779 2003-04-24 00:00:00 100 450
3779 2003-04-25 00:00:00 100 585
3779 2003-04-28 00:00:00 100 495
3779 2003-04-29 00:00:00 100 495
3779 2003-04-30 00:00:00 100 450
3779 2003-05-02 00:00:00 100 450
3779 2003-05-05 00:00:00 100 435
3779 2003-05-06 00:00:00 100 495
3779 2003-05-07 00:00:00 100 495
3779 2003-05-09 00:00:00 100 435
3779 2003-05-12 00:00:00 100 495

This is result what I need:
cislo datum kodds hodin utvar
3779 2003-04-23 00:00:00 100 495 8007
3779 2003-04-24 00:00:00 100 450 8007
3779 2003-04-25 00:00:00 100 585 8007
3779 2003-04-28 00:00:00 100 495 8007
3779 2003-04-29 00:00:00 100 495 8007
3779 2003-04-30 00:00:00 100 450 8007
3779 2003-05-02 00:00:00 100 450 7003
3779 2003-05-05 00:00:00 100 435 7003
3779 2003-05-06 00:00:00 100 495 7003
3779 2003-05-07 00:00:00 100 495 7003
3779 2003-05-09 00:00:00 100 435 7003
3779 2003-05-12 00:00:00 100 495 7003

I used storedprocedure and "utvar" I got from:
SET @utvar2=(select utvar from #T1 where cislo=@cislo1 AND (@datum between datod and datdo))

On finally it looks very simply Smile .

Thank you for your help and advice
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search