SQL Query - Group by name and view visits by day of week

Question

I'm relatively inexperienced in SQL and I have a query I'd like to run in SQL-Server.

So I have two tables:

Sites:

Id (PK) | SiteName

and Visits:

Id (PK) | Duration | EntryTime | site_Id (FK > Sites)

I want to create a table as output from this query that looks as follows:

SiteName | MondayVisitCount | TuesdayVisitCount | etc...

I want a totalled visit count on each day of the week using the EntryTime field from the Visits table accompanied with the Site name associated with the visits. The final query should implement a date range, so it is the cumulative visits on each day of the week over that date range. It's the process of creating this new table I don't understand, any tips welcome.

I guess this will answer your query. http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server Thank you — BlueSky, Apr 18 '16 at 11:26
@BlueSky your link points to what seems to be a random question using pivot. I don't see that having any relation to the question except using pivot — t-clausen.dk, Apr 18 '16 at 11:30

score 0 · Answer 1 · answered Apr 18 '16 at 11:48

0

@jonny

i dont have proper data to test, but you can achieve your goal by little bit changing on my query.

SELECT S.SITENAME AS SITENAME,
CASE WHEN DATENAME(DW, V.EntryTime) = 'Monday' THEN 0 ELSE 1 END AS 'MondayVisitCount ',
CASE WHEN DATENAME(DW, V.EntryTime ) = 'Tuesday' THEN 0 ELSE 1 END AS 'TuesdayVisitCount ',
FROM SITES AS S 
INNER JOIN VISITS AS V ON S.SITE_ID = V.SITE_ID

answered Apr 18 '16 at 11:48

Bharat

5,869
4
38
58

Thanks for the comment. so how would I make the value of the "MondayVisitCount" into the count(*) of visits on that day over the period? Currently it's either 1 or 0 obviously. – JonnyKnottsvill Apr 18 '16 at 12:15
yes you are right, i just check that whether entry time is at which day of week.but to get total count you have to put count(v.EntryTime) at 0 for each case. i dont know it's proper way or not. but it surely helps you.. – Bharat Apr 18 '16 at 12:19

BlueSky · Answer 2 · 2016-04-19T07:33:32.093

DECLARE @T TABLE
(
    SiteID  INT,
    SiteName VARCHAR(100),
    MondayVisitCount Int,
    TuesdayVisitCount Int,
    WednesdayVisitCount Int,
    ThursdayVisitCount Int,
    FridayVisitCount Int,
    SaturdayVisitCount INT,
    SundayVisitCount Int
)


INSERT INTO @T

        ( 
          SiteID,
          SiteName ,
          MondayVisitCount ,
          TuesdayVisitCount ,
          WednesdayVisitCount ,
          ThursdayVisitCount ,
          FridayVisitCount ,
          SaturdayVisitCount ,
          SundayVisitCount
        )
SELECT ID, SiteName
FROM Sites


SELECT COUNT(*) AS N, Site_Id, DATEPART(weekday, EntryTime) AS [DayOfWeek]
INTO #tmpVisits
FROM [dbo].Visits WITH (NOLOCK)
--DATE FILTER GOES HERE
GROUP BY DATEPART(weekday, EntryTime), Site_Id

UPDATE @T
SET SundayVisitCount = tVisits.N
FROM @T AS tResult
INNER JOIN #tmpVisits AS tVisits ON tVisits.Site_Id = tResult.Id AND tVisits.[DayOfWeek] = 1

UPDATE @T
SET MondayVisitCount = tVisits.N
FROM @T AS tResult
INNER JOIN #tmpVisits AS tVisits ON tVisits.Site_Id = tResult.Id AND tVisits.[DayOfWeek] = 2
--.
--.
--.
--.
UPDATE @T
SET SaturdayVisitCount = tVisits.N
FROM @T AS tResult
INNER JOIN #tmpVisits AS tVisits ON tVisits.Site_Id = tResult.Id AND tVisits.[DayOfWeek] = 7


SELECT *
FROM @T

DROP TABLE #tmpVisits

score 0 · Answer 3 · answered Apr 19 '16 at 12:55

declare @startdate datetime = cast('2008/01/01' as datetime)
declare @enddate   datetime = cast('2008/07/31' as datetime)

select p.* 
        into sitevisits         --create a permanent table in database
from
(
select 1 as rectype,s.sitename , datename(DW,v.entrytime) as [Dayofweek],
        1 as [dayofweekcount]   --give pivot something to sum
from @site s
join  @visits v on v.siteid = s.id
where   v.entrytime between @startdate and @enddate

) s
pivot (sum(s.dayofweekcount) for s.[dayofweek] in ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]))  p
union
select p.* from
(
select 2 as rectype,'Grand Total' sitename, datename(DW,v.entrytime) as [Dayofweek],
        1 as [dayofweekcount]
from  @visits v 
where   v.entrytime between @startdate and @enddate
) s
pivot (sum(s.dayofweekcount) for s.[dayofweek] in ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]))  p

SQL Query - Group by name and view visits by day of week

3 Answers3