Hi there,
I hope this helps you,
This is how to split 1 row into 7....
SELECT TOP (100) PERCENT Table_1.Driver_Id,'Sunday' as TheDay, Table_1.Sun_StartTime as StartTime,Table_1.Sun_EndTime as EndTime
FROM Table_1
WHERE (Table_1.Sun_StartTime IS NOT NULL) AND (Table_1.Sun_EndTime IS NOT NULL)
UNION ALL
SELECT TOP (100) PERCENT Table_1.Driver_Id, 'Monday' as TheDay,Table_1.Mon_StartTime,Table_1.Mon_EndTime
FROM Table_1
WHERE (Table_1.Mon_StartTime IS NOT NULL) AND (Table_1.Mon_EndTime IS NOT NULL)
UNION ALL
SELECT TOP (100) PERCENT Table_1.Driver_Id,'Tuesday' as TheDay, Table_1.Tue_StartTime,Table_1.Tue_EndTime
FROM Table_1
WHERE (Table_1.Tue_StartTime IS NOT NULL) AND (Table_1.Tue_EndTime IS NOT NULL)
UNION ALL
SELECT TOP (100) PERCENT Table_1.Driver_Id,'Wednesday' as TheDay, Table_1.Wed_StartTime,Table_1.Wed_EndTime
FROM Table_1
WHERE (Table_1.Wed_StartTime IS NOT NULL) AND (Table_1.Wed_EndTime IS NOT NULL)
UNION ALL
SELECT TOP (100) PERCENT Table_1.Driver_Id,'Thursday' as TheDay, Table_1.Thu_StartTime,Table_1.Thu_EndTime
FROM Table_1
WHERE (Table_1.Thu_StartTime IS NOT NULL) AND (Table_1.Thu_EndTime IS NOT NULL)
UNION ALL
SELECT TOP (100) PERCENT Table_1.Driver_Id,'Friday' as TheDay, Table_1.Fri_StartTime,Table_1.Fri_EndTime
FROM Table_1
WHERE (Table_1.Fri_StartTime IS NOT NULL) AND (Table_1.Fri_EndTime IS NOT NULL)
UNION ALL
SELECT TOP (100) PERCENT Table_1.Driver_Id,'Saturday' as TheDay, Table_1.Sat_StartTime,Table_1.Sat_EndTime
FROM Table_1
WHERE (Table_1.Sat_StartTime IS NOT NULL) AND (Table_1.Sat_EndTime IS NOT NULL)
Because I'm feeling kind / bored.... Here is the Stored Proc
CREATE PROCEDURE MyProc
@SINO int = 0,
@TheDay varchar(10) = ''
AS
BEGIN
SET NOCOUNT ON;
with TempIdea (SINO, Driver_Id, TheDay,StartTime,EndTime) as (
SELECT TOP (100) PERCENT Table_1.SINO, Table_1.Driver_Id,'Sunday' as TheDay, Table_1.Sun_StartTime as StartTime,Table_1.Sun_EndTime as EndTime
FROM Table_1
WHERE (Table_1.Sun_StartTime IS NOT NULL) AND (Table_1.Sun_EndTime IS NOT NULL)
UNION ALL
SELECT TOP (100) PERCENT Table_1.SINO,Table_1.Driver_Id, 'Monday' as TheDay,Table_1.Mon_StartTime,Table_1.Mon_EndTime
FROM Table_1
WHERE (Table_1.Mon_StartTime IS NOT NULL) AND (Table_1.Mon_EndTime IS NOT NULL)
UNION ALL
SELECT TOP (100) PERCENT Table_1.SINO,Table_1.Driver_Id,'Tuesday' as TheDay, Table_1.Tue_StartTime,Table_1.Tue_EndTime
FROM Table_1
WHERE (Table_1.Tue_StartTime IS NOT NULL) AND (Table_1.Tue_EndTime IS NOT NULL)
UNION ALL
SELECT TOP (100) PERCENT Table_1.SINO,Table_1.Driver_Id,'Wednesday' as TheDay, Table_1.Wed_StartTime,Table_1.Wed_EndTime
FROM Table_1
WHERE (Table_1.Wed_StartTime IS NOT NULL) AND (Table_1.Wed_EndTime IS NOT NULL)
UNION ALL
SELECT TOP (100) PERCENT Table_1.SINO,Table_1.Driver_Id,'Thursday' as TheDay, Table_1.Thu_StartTime,Table_1.Thu_EndTime
FROM Table_1
WHERE (Table_1.Thu_StartTime IS NOT NULL) AND (Table_1.Thu_EndTime IS NOT NULL)
UNION ALL
SELECT TOP (100) PERCENT Table_1.SINO,Table_1.Driver_Id,'Friday' as TheDay, Table_1.Fri_StartTime,Table_1.Fri_EndTime
FROM Table_1
WHERE (Table_1.Fri_StartTime IS NOT NULL) AND (Table_1.Fri_EndTime IS NOT NULL)
UNION ALL
SELECT TOP (100) PERCENT Table_1.SINO,Table_1.Driver_Id,'Saturday' as TheDay, Table_1.Sat_StartTime,Table_1.Sat_EndTime
FROM Table_1
WHERE (Table_1.Sat_StartTime IS NOT NULL) AND (Table_1.Sat_EndTime IS NOT NULL)
)
Select * from TempIdea
Where SINO = @SINO AND TheDay = @TheDay
END
If you want to return 7 records and not the specific day, just remove the "TheDay" parameter from the Stored Procedure.
That will then return to you the 7 days based on the SlNo you give the Stored Procedure.
One note...
The way I have written the SQL means the Start and End time must not be null, otherwise they wont return a record. So if there is a start time and the end time is null, then it wont retrieve that record.
Anyways,
Good luck.