It will be better if you provide DDL and sample data in a ready to use format so that volunteers here can copy+paste it in their machine and start working...

The below code will help you handle the overlapping conditions

SELECTBookings.Booking_Skey,

SUM (

(

DATEDIFF(

DAY,

CASE

WHEN PitchValues.StartDate BETWEEN Bookings.ArrivalDate AND Bookings.DepartureDate THEN PitchValues.StartDate

ELSE Bookings.ArrivalDate

END,

CASE

WHEN PitchValues.EndDate BETWEEN Bookings.ArrivalDate AND Bookings.DepartureDate THEN PitchValues.EndDate

ELSE Bookings.DepartureDate

END

) +...

wafw1971 (2/21/2013)

---

Hi Kingston

I didn't think of that and I should have, each price is a daily price so if the price for a pitch is £9.00 on the 31/01/2010 and...

Great 🙂

Looking at your code, I have a question.

Which price should you...

Your query is basically joining the 1 row in Bookings table with 4 rows in PitchValues table

You will need one more condition on date to filter it down to one...

Can you give us the results of the below mentioned queries

SELECT * FROM Bookings WHERE Booking_Skey = 1

SELECT * FROM PitchValues WHERE PitchType_Skey IN ( SELECT PitchType_Skey FROM Bookings WHERE...

Sean Pearce (2/21/2013)

---

INSERT INTO PseudoTable VALUES ('Tear,Bike,Monkey')

SELECT * FROM PseudoTable WHERE Item LIKE '%tea%'

1 row returned.

INSERT INTO PseudoTable VALUES ('Tear')

INSERT INTO PseudoTable VALUES ('Bike')

INSERT INTO PseudoTable VALUES ('Monkey')

SELECT * FROM...

vchauhan_me (2/21/2013)

---

Thanks for the reply from all of you, but i need the specific reason why separate rows (70,000 rows) are more preferable to store in DB in place...

vchauhan_me (2/21/2013)

---

My requirement is like i have to search the word from this 70,000 words. so 1st i have to store that in the table then execute select statement with...

It depends on your business requirement( What are you going to do with this 70,000 words? )

If you intend to handle each word differently by joining them individually to some...

Even this would do the work for you and is probably much simpler

SELECTCONVERT(TIME, DATEADD(SECOND, DATEDIFF(SECOND, minibgtime, maxendtime), '' ) )

FROMtablename

This should help

WHERE a.connected = 0 AND ( dt.connected IS NOT NULL OR b.id IS NULL )

Even the below code will solve your requirement if you are using SQL Server...

change your WHERE Clause like below

WHERE A.connected = 0 AND dt.connected IS NOT NULL

This should help you out

; WITH cte_Delete_Dups AS

(

SELECTROW_NUMBER() OVER ( PARTITION BY row_id, ename, job, sal ORDER BY row_id ) AS RN, *

FROMempl

)

DELETE

FROMcte_Delete_Dups

WHERErn > 1

You can use Dynamic CROSS-TABS to achieve this

You can have a look at the below mentioned articles for more details on CROSS-TABS

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/Crosstab/65048/

Something like below

DECLARE@strColumns VARCHAR(MAX)

DECLARE@strSQL VARCHAR(MAX)

IF OBJECT_ID('tempdb..#tmp_table') IS NOT NULL

DROP...