December 8, 2011 at 11:14 am
I have three columns TransID, PubstartDate and PubEndDate in a table.. I need SQL to determine TransID between @Startdate and @Enddate. For example in the table TransID=3, pubstartDate is 12/5/2011 , PubEndDate is 12/7/2011. Now in the parameters if select @Startdate = 12/6/2011 and @Enddate = 12/9/2011 i want the SQL to return TransID=3 because there are two days in common for (PubstartDate and PubEndDate) and ( @Startdate and @Enddate) i.e 6th and 7th of December. I need SQL for this. Please help me with some idea. Thanks.
December 8, 2011 at 11:26 am
Does the entire date range between PubStartDate and PubEndDate need to fall inside the range defined by your parameters? Or are you looking for overlap? If you're not including overlap, then using BETWEEN comparisons should work just fine. What have you got so far?
December 8, 2011 at 11:29 am
with the requirements you've laid out ....
SELECT .....
FROM .....
WHERE
WHERE
PubstartDate BETWEEN @Startdate AND @Enddate
OR
PubEndDate BETWEEN @Startdate AND @Enddate
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 8, 2011 at 11:30 am
Just overlap is fine. I got nothing so far. I have been thinking but couldn't get any thing. Thanks for the reply.
December 8, 2011 at 11:45 am
where
CASE WHEN @PrmDateDim= 'CreationDate' THEN (Case WHEN L.SALESSTATUS = 1
and H.SALESORIGINID IN ('DSM','DSM-Trf')
THEN ( L.STARTDATE between @InvoiceStart and @InvoiceEnd) or (L.ENDDATE between @InvoiceStart and @InvoiceEnd) )
ELSE L.CREATEDDATETIME
END)
END between @InvoiceStart and @InvoiceEnd
So in the where clause i put your code "( L.STARTDATE between @InvoiceStart and @InvoiceEnd) or (L.ENDDATE between @InvoiceStart and @InvoiceEnd) )" but that is wrong could you help me with right syntax..thanks.
December 8, 2011 at 12:00 pm
varunkum (12/8/2011)
where
CASE WHEN @PrmDateDim= 'CreationDate' THEN (Case WHEN L.SALESSTATUS = 1
and H.SALESORIGINID IN ('DSM','DSM-Trf')
THEN ( L.STARTDATE between @InvoiceStart and @InvoiceEnd) or (L.ENDDATE between @InvoiceStart and @InvoiceEnd) )
ELSE L.CREATEDDATETIME
END)
END between @InvoiceStart and @InvoiceEnd
So in the where clause i put your code "( L.STARTDATE between @InvoiceStart and @InvoiceEnd) or (L.ENDDATE between @InvoiceStart and @InvoiceEnd) )" but that is wrong could you help me with right syntax..thanks.
I think you may be trying to do something like this:
WHERE (H.SALESSTTUS = 1 AND
H.SALESORIGINID IN ('DSM','DSM-Trf') AND
L.STARTDATE BETWEEN @InvoiceStart AND @IndvoiceEnd AND
L.ENDDATE BETWEEN @InvoiceStart AND @InvoiceEnd)
OR (L.CREATEDDATETIME BETWEEN @InvoiceStart AND @InvoiceEnd)
December 9, 2011 at 8:34 am
JonFox (12/8/2011)
I think you may be trying to do something like this:
WHERE (H.SALESSTTUS = 1 AND
H.SALESORIGINID IN ('DSM','DSM-Trf') AND
L.STARTDATE BETWEEN @InvoiceStart AND @IndvoiceEnd AND
L.ENDDATE BETWEEN @InvoiceStart AND @InvoiceEnd)
OR (L.CREATEDDATETIME BETWEEN @InvoiceStart AND @InvoiceEnd)
This can be simplified even further.
WHERE H.SALESSTTUS = 1 AND
H.SALESORIGINID IN ('DSM','DSM-Trf') AND
L.STARTDATE < @IndvoiceEnd AND
@InvoiceStart > L.ENDDATE
This also emphasizes the fact that this relationship is commutative. That is, if A overlaps B then B overlaps A. That is not as clear in the other solutions.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 9, 2011 at 8:55 am
drew.allen (12/9/2011)
This can be simplified even further.
WHERE H.SALESSTTUS = 1 AND
H.SALESORIGINID IN ('DSM','DSM-Trf') AND
L.STARTDATE < @IndvoiceEnd AND
@InvoiceStart > L.ENDDATE
This also emphasizes the fact that this relationship is commutative. That is, if A overlaps B then B overlaps A. That is not as clear in the other solutions.
Drew
Good point, I accidentally wrote that to exclude overlap...my bad!
December 9, 2011 at 10:56 am
CELKO (12/9/2011)
SELECT DISTINCT T.trans_id,@in_start_date AAS report_start_date
@in_end_date AS report_end_date
FROM Calendar AS C, Something_Transactions AS T
WHERE C.cal_date BETWEEN @in_start_date AND @in_end_date
AND C.cal_date BETWEEN pub_start_date AND pub_end_date;
The implicit join syntax has been deprecated. Please follow the standards and use the explicit CROSS JOIN syntax.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 9, 2011 at 11:49 am
drew.allen (12/9/2011)
JonFox (12/8/2011)
I think you may be trying to do something like this:
WHERE (H.SALESSTTUS = 1 AND
H.SALESORIGINID IN ('DSM','DSM-Trf') AND
L.STARTDATE BETWEEN @InvoiceStart AND @IndvoiceEnd AND
L.ENDDATE BETWEEN @InvoiceStart AND @InvoiceEnd)
OR (L.CREATEDDATETIME BETWEEN @InvoiceStart AND @InvoiceEnd)
This can be simplified even further.
WHERE H.SALESSTTUS = 1 AND
H.SALESORIGINID IN ('DSM','DSM-Trf') AND
L.STARTDATE < @IndvoiceEnd AND
@InvoiceStart > L.ENDDATE
This also emphasizes the fact that this relationship is commutative. That is, if A overlaps B then B overlaps A. That is not as clear in the other solutions.
Drew
This is the answer. Thank you. And thanks all for giving me your suggestions.
December 9, 2011 at 11:55 am
Keep in mind if your date columns are of datatype datetime don't use BETWEEN but use
col >= @begin and col <= @end
SQLServer has an issue with datetime data typed columns with regards to BETWEEN.
( I cannot dig up the ref right now, but it was somewhere at SSC if I remember well )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 9, 2011 at 1:37 pm
ALZDBA (12/9/2011)
Keep in mind if your date columns are of datatype datetime don't use BETWEEN but usecol >= @begin and col <= @end
SQLServer has an issue with datetime data typed columns with regards to BETWEEN.
( I cannot dig up the ref right now, but it was somewhere at SSC if I remember well )
No, the problem is that people use BETWEEN when it's not appropriate. This is independent of datatype. It's similar to the fencpost error in that people don't correctly account for records that fall right at the endpoints. So for example if working with grades between 90 and 100; between 80 and 90; between 70 and 80; etc., they're not taking into account grades that are exactly 90, 80, 70, etc., and end up potentially counting them twice.
Instead of fixing their logic, people fudge the endpoints to get the results that they want. So they might use the ranges 90-100, 80-89.9, 70-79.9, etc. This will give you the correct results as long as your grades have a scale of 1. The reason that this error in logic is most prevalent with datetime data is that people often don't specify the correct scale necessary to catch all of the data.
Your "fix" has the exact same problem that BETWEEN has--it includes BOTH endpoints. The correct fix is to have one endpoint included and the other excluded. Typically the included endpoint is the smaller one, but that is not mandatory. So one way to correct this would be to use col >= @begin and col < @end
All that being said, I think that BETWEEN has other problems when applied to this particular problem. It is entirely possible that one event can be ending at the exact same time that another event is starting. People don't think of these events as overlapping, so for this particular problem we want to exclude both endpoints whereas BETWEEN includes both endpoints, so it is doubly wrong.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply