Is it possible to write a query do achieve this...?

Transact-SQL
1

Afternoon all,

I have a query that out puts all the data I want, unfortunately I don't like the way its displayed and I'd like to combine data from multiple results into a single result. Problem is I have no idea how to do it... Let me try and explain....

SELECT  S_Name, S_Address1, S_Postcode,
        B_StartDate, B_EndDate, 
        DATEPART(Week, B_StartDate) AS WeekNum,
        DATEDIFF(Day, B_StartDate, B_EndDate) +1 As NoOfDays,
        DATENAME(dw, B_StartDate) As DayWeek,
        B_StartTime, B_EndTime,
        DATEDIFF(Hour, B_StartTime, B_EndTime) As NoOfHours,
        T_Name, S_Email, S_FaxNo
        FROM Bookings
        INNER JOIN Employees
        ON T_TID=B_TID
        INNER JOIN Clients
        ON S_SID=B_SID
        WHERE B_TimeSheetCreated = 0
        ORDER BY S_Name, B_StartDate

Output with some example data:

What I would like to try and achieve is something like this :

Hope you understand what I mean.... any idea if its possible...?

Thanks

Dave

Edit: looks like he formatting has all been messed up, I've uploaded images instead.......

2

It is possible. Would you please post some sample data as:

CREATE TABLE ...
INSERT INTO ...

For each table involved? It makes our lives a little simpler.

3

Sure thing, here goes...

CREATE TABLE Bookings
(
B_BID Int NOT NULL Primary Key,
B_TID Int,
B_SID Int,
B_StartDate Date,
B_EndDate Date,
B_Start_Time Time(0),
B_End_Time Time(0), 
B_JobGroup Nvarchar(20),
B_TimeSheetCreated Bit,
)

CREATE TABLE Clients
(
S_SID Int NOT NULL Primary Key,
S_Name Nvarchar(40) NOT NULL,
S_Address1 Nvarchar(30),
S_Postcode Nvarchar(10),
S_Email Nvarchar(30),
S_Fax Nvarchar(15),
)

CREATE TABLE Employees
(
T_TID Int NOT NULL Primary Key,
T_Name Varchar(30),
T_Address1 Varchar(30),
T_Postcode Varchar(10),
T_DOB Date,
)

And the data :

INSERT INTO Bookings (B_BID, B_TID, B_SID, B_StartDate, B_EndDate, B_Start_Time, B_End_Time, B_TimeSheetCreated)
VALUES ('9001', '5001', '1001', '2015-05-25', '2015-05-25', '09:00:00', '17:00:00', '0')

INSERT INTO Bookings (B_SID, B_StartDate, B_EndDate, B_Start_Time, B_End_Time, B_TimeSheetCreated)
VALUES ('9002', '5002', '1002', '2015-05-26', '2015-05-26', '08:00:00', '16:00:00', '0')

INSERT INTO Bookings (B_SID, B_StartDate, B_EndDate, B_Start_Time, B_End_Time, B_TimeSheetCreated)
VALUES ('9003', '5002', '1002', '2015-05-27', '2015-05-27', '08:00:00', '16:00:00', '0')


INSERT INTO Clients (S_SID, S_Name, S_Address1, S_Postcode, S_Email, S_Fax)
VALUES ('1001', 'Client 1', '1 High St', 'AA1 1AA', 'info@a.com', '0123456789')

INSERT INTO Clients (S_SID, S_Name, S_Address1, S_Postcode, S_Email, S_Fax)
VALUES ('1002', 'Client 2', '25 Long Ave', 'BB2 2BB', 'info@b.com', '0987654321')


INSERT INTO Employees (T_TID, T_Name, T_Address1, T_Postcode, T_DOB)
VALUES ('5001', 'John Doe', '1 John St', 'JD1 1ME', '1981-01-01')

INSERT INTO Employees (T_TID, T_Name, T_Address1, T_Postcode, T_DOB)
VALUES ('5002', 'Jane Doe', '1 Jane St', 'JD1 1FE', '1982-02-02')

Hope that helps, I think its all right... It should reproduce the data in the images above....

Many thanks

Dave

4

Here is an approach using PIVOT:

WITH src
AS (
     SELECT S_Name
          , S_Address1
          , S_Postcode
          , B_StartDate
          , B_EndDate
          , DATEPART(Week, B_StartDate) AS WeekNum
          , DATEDIFF(Day, B_StartDate, B_EndDate) + 1 AS NoOfDays
          , DATENAME(dw, B_StartDate) AS DayWeek
          , B_Start_Time
          , B_End_Time
          , DATEDIFF(Hour, B_Start_Time, B_End_Time) AS NoOfHours
          , T_Name
          , S_Email
          , S_Fax
     FROM Bookings
     INNER JOIN Employees ON T_TID = B_TID
     INNER JOIN Clients ON S_SID = B_SID
     WHERE B_TimeSheetCreated = 0
          --ORDER BY S_Name, B_StartDate
     )
SELECT S_Name
     , S_Address1
     , S_Postcode
     , isnull(max(Monday), 0)
     , isnull(max(Tuesday), 0)
     , isnull(max(Wednesday), 0)
     , T_Name
     , S_Email
     , S_Fax
FROM src
pivot(sum(NoOfHours) FOR DayWeek IN (Monday, Tuesday, Wednesday)) p
GROUP BY S_Name
     , S_Address1
     , S_Postcode
     , T_Name
     , S_Email
     , S_Fax

I only did the first three days (call me lazy!) but hopefully you can see the pattern

5

Sir, that is genius...!

It looks like its working perfectly, but I'll run some more tests in the morning as I'm exhausted now and liable to make mistakes... One thing, the new columns created for the days of the week, how would I give them the correct title?

Many thanks

Dave

6

change

isnull(max(Monday), 0)

to

isnull(max(Monday), 0) AS Monday
...etc
7

Perfect, thank you!

8

Simpler (don't need the group by):

WITH src
AS (
     SELECT S_Name
          , S_Address1
          , S_Postcode
          --, B_StartDate
          --, B_EndDate
          , DATEPART(Week, B_StartDate) AS WeekNum
          , DATEDIFF(Day, B_StartDate, B_EndDate) + 1 AS NoOfDays
          , DATENAME(dw, B_StartDate) AS DayWeek
          --, B_Start_Time
          --, B_End_Time
          , DATEDIFF(Hour, B_Start_Time, B_End_Time) AS NoOfHours
          , T_Name
          , S_Email
          , S_Fax
     FROM Bookings
     INNER JOIN Employees ON T_TID = B_TID
     INNER JOIN Clients ON S_SID = B_SID
     WHERE B_TimeSheetCreated = 0
     --ORDER BY S_Name, B_StartDate
     )
SELECT S_Name
     , S_Address1
     , S_Postcode
     , isnull(Monday, 0) AS Monday
     , isnull(Tuesday, 0) AS Tuesday
     , isnull(Wednesday, 0) AS Wednesday
     , isnull(Thursday, 0) AS Thursday
     , isnull(Friday, 0) AS Friday
     , isnull(Saturday, 0) AS Saturday
     , isnull(Sunday, 0) AS Sunday
     , T_Name
     , S_Email
     , S_Fax
FROM src
PIVOT(sum(NoOfHours) FOR DayWeek IN (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday)) p
9

I did wonder about that, nice one thank you.