Help to get SQL output

Posted by Santosh4u under Sql Server on 10/12/2015 | Points: 10 | Views : 405 | Status : [Member] | Replies : 2
Hi
Any one help to get below output
SrNo ShiftStartDate ShiftEndDate
1 01-01-2013 25-01-2014
2 26-01-2014 25-09-2015
3 26-09-2015 Getdate()

i am passing fromdate(01-09-2014) and fromdate(20-10-2015)
based on this passing date if any date either fromdate or Todate falling between any Shiftfromdate and Shifttodate then that record should display.

Schenario 1 :
I/P
fromdate(01-09-2014) and fromdate(20-06-2015)
o/t 2 26-01-2014 25-09-2015

Schenario 2 :
I/P
fromdate(01-09-2014) and fromdate(26-09-2015)
o/T
2 26-01-2014 25-09-2015
3 26-09-2015 Getdate()

Schenario 3 :
I/P
fromdate(01-09-2012) and fromdate(01-01-2016)
o/T
1 01-01-2013 25-01-2014
2 26-01-2014 25-09-2015
3 26-09-2015 Getdate()

Thanks
Santosh




Responses

Posted by: Rajnilari2015 on: 10/12/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
Based on your input,the answer will be to Perform an inner join between the Calendar table and your input table. Hope this helps

--
Thanks & Regards,
RNA Team

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rojalin on: 10/13/2015 [Member] Starter | Points: 25

Up
0
Down
if any date either fromdate or Todate falling between any Shiftfromdate and Shifttodate then that record should display.

for this you can write query like:
select * from date_com where ('2013-09-01' between startdate  and enddate) 

or ( '2015-01-01' between startdate and enddate)

but as you mention your scenario 3, It will be something like
select * from date_com where (startdate between '2012-09-01' and '2016-01-01')

or (enddate between '2012-09-01' and '2016-01-01')



Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response