quote:

---

Originally posted by velnias2010

Thanks that worked somewhat finally I also have a column startTime and StartMinute so I have BookingDate i.e 12/02/2009 Start Time ie 5 and Start Minute 15...

Is it possible also that at the end it sorts by BookingDate, then Start Hours, Then Start Minute

so i.e I would 12/02/2009 3 15
12/02/2009 3 30
12/02/2009 4 15

---

SELECT COALESCE(T1.form_data_id, T2.form_data_id) AS form_data_id,
       COALESCE(T1.user_id, T2.user_id) AS user_id,
       CONVERT(VARCHAR(50), T1.form_field_value) AS BookingDate,
       CONVERT(VARCHAR(50), T2.form_field_value) AS RoomID
FROM form_data_tbl AS T1
    FULL OUTER JOIN form_data_tbl AS T2
        ON T2.user_id = 30 AND T2.form_field_name = 'roomId'
WHERE T1.user_id = 30 AND T1.form_field_name = 'bookingDate'

SELECT *
FROM
(
    ... query here ...
) AS X
WHERE bookingDate > GetDate()

SELECT
    form_data_id,
    user_id,
    [BookingDate] = CONVERT(varchar(24), BookingDate, 113), -- Choose your Date Format here
    RoomID
FROM
(
    SELECT COALESCE(T1.form_data_id, T2.form_data_id) AS form_data_id,
           COALESCE(T1.user_id, T2.user_id) AS user_id,
           -- NOTE: The date format needs to be recognised here!! 103 = dd/mm/yyyy
           CONVERT(DATETIME, T1.form_field_value, 103) AS BookingDate,
           CONVERT(INT, T2.form_field_value) AS RoomID    -- RoomID is INT, right?
    FROM form_data_tbl AS T1
        FULL OUTER JOIN form_data_tbl AS T2
            ON T2.user_id = 30 AND T2.form_field_name = 'roomId'
    WHERE T1.user_id = 30 AND T1.form_field_name = 'bookingDate'
) AS X
WHERE BookingDate > GetDate()

quote:

---

Originally posted by velnias2010

Ah Cool...Another Thing Bothering me...

I would like to pass a 2 paramaters to the above query which are startDate and EndDate and I would like to compare them with all the other Start and EndDates in that table and see if they dont overlap with the ones in there. I.e can I Make a booking for then or is there dates overlapping with it. StartDate and EndDate are 2 other columns like BookingDate above

---

SELECT ... 
FROM ...
(
    SELECT ...
           MAX(CASE WHEN form_field_name = 'startDate' 
               THEN CONVERT(VARCHAR(50), form_field_value) 
               ELSE NULL END
               ) AS startDate, 
           ...
)
...
WHERE (startDate > GETDATE()) ...

CONVERT(DATETIME, CONVERT(VARCHAR(50), form_field_value, 103), 103) 

CONVERT(DATETIME, CONVERT(VARCHAR(50), form_field_value), 103) 

(startDate > CONVERT(datetime, '13/12/2009', 103))

quote:

---

Originally posted by velnias2010

The Data type must remain NTEXT im afraid

I have this now

SELECT user_id, endDate, startDate, CONVERT(DATETIME, startDate, 113) AS BookingDate
FROM (SELECT TOP (100) PERCENT form_data_id, MAX(CASE WHEN form_field_name = 'bookingDate' THEN CONVERT(VARCHAR(50), form_field_value) ELSE NULL END)
AS BookingDate, MAX(CASE WHEN form_field_name = 'roomId' THEN CONVERT(VARCHAR(50), form_field_value) ELSE NULL END) AS roomId,
MAX(CASE WHEN form_field_name = 'roomName' THEN CONVERT(VARCHAR(50), form_field_value) ELSE NULL END) AS RoomName,
MAX(CASE WHEN form_field_name = 'startDate' THEN CONVERT(DATETIME, CONVERT(VARCHAR(50), form_field_value, 103), 103) ELSE NULL END)
AS startDate, MAX(CASE WHEN form_field_name = 'endDate' THEN CONVERT(DATETIME, CONVERT(VARCHAR(50), form_field_value, 101), 103) ELSE NULL
END) AS endDate, user_id
FROM form_data_tbl
GROUP BY form_data_id, user_id
ORDER BY startDate) AS X
WHERE (startDate > GETDATE())
ORDER BY startDate

which sorts the dates correctly...what I cannot yet do is compare the date i.e start Date with a manual date

i.e AND (startDate > '13/12/2009') Then i get that out of range error message again

---