Help to get SQL output

Posted by Santosh4u under Sql Server on 10/12/2015 | Points: 10 | Views : 1099 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

Latest Posts