Stored Procedure Help

Transact-SQL
1

Hi,
I created a SP ages ago that gives me these columns based on a @Param executed which is a company name, i.e. XYZ Ltd:

Visitor Type
Date
Weekday
Visitor Name
Location
Company
Time In
Time Out
Duration (hh:mm)

Here is the SP:
SELECT
format(cast([Visitor].[ArrivalDateTime] as date),'dd MMM yyyy') AS 'Date',
FORMAT([Visitor].[ArrivalDateTime],'dddd') AS 'Weekday',
[Location].[VisitorTypeName] AS 'Visitor Type',
[Visitor].[VisitorName] AS 'Visitor Name',
[Location].[Name] AS 'Location',
[Client].[Name] AS 'Company',
--[Visitor].[ArrivalDateTime] AS ORDER_BY_TC0,
Min(CONVERT(VARCHAR(30), ([Visitor].[ArrivalDateTime]), 108) ) AS 'Time In',
Max(CONVERT(VARCHAR(30), ([Visitor].[DepartureDateTime]), 108) ) AS 'Time Out',
CONVERT(char(5),DATEADD(second, (DATEDIFF(Second, Min(CONVERT(VARCHAR(30), ([Visitor].[ArrivalDateTime]), 108) ), Max(CONVERT(VARCHAR(30), ([Visitor].[DepartureDateTime]), 108) ))),'19000101'), 108) AS 'Duration (hh:mm)'
--DATEDIFF(Second, Min(CONVERT(VARCHAR(30), ([Visitor].[ArrivalDateTime]), 108) ), Max(CONVERT(VARCHAR(30), ([Visitor].[DepartureDateTime]), 108) )) AS TC9

FROM
[dbo].[Client] [Client]
JOIN [dbo].[Visitor] [Visitor] ON [Client].[Id] = [Visitor].[ClientId]
JOIN [dbo].[Location] [Location] ON [Visitor].[LocationId] = [Location].[Id]
WHERE
( [Client].[Name] = @Param )
AND
( cast([Visitor].[Arrivaldatetime] as date) between cast(getdate()-8 as date) and cast(getdate()-1 as date) )
GROUP BY
[Visitor].[ArrivalDateTime],
format(cast([Visitor].[ArrivalDateTime] as date),'dd MMM yyyy'),
FORMAT([Visitor].[ArrivalDateTime],'dddd'),
[Location].[VisitorTypeName],
[Visitor].[VisitorName],
[Location].[Name],
[Client].[Name]
ORDER BY
[Visitor].[ArrivalDateTime] ASC,
Min(CONVERT(VARCHAR(30), ([Visitor].[ArrivalDateTime]), 108) ) ASC

What I need now is a fortnightly report giving me a count, total duration (hh:mm) by Location Name and then by Visitor Name.

Thanks!

2

can you provide DDL, sample data and expected output

4

Expected output below:

Date From Date to Visitor Name Visitor Type Location Company Duration (hh:mm)
12-Nov-21 26-Nov-21 Sharon Stone Visitor Smithfields ACME Ltd 10
12-Nov-21 26-Nov-21 Sharon Stone Visitor Oxford ACME Ltd 10

I hope this helps?

5

That's the output. Do you have DDL and Sample data?
Create table.....
insert into table....

6

Sorry I don't. If you look at my other Stored Procedure, I would like the new one to Group by LocationName and VisitorName. Thanks!

7

Umm.... Why should anyone help you if you cannot be bothered to provide some consumable test data and the expected output for that test data?

1 Like