SQL Syntax Grouping question

I am trying to get a count per day of individual users that have entered something onto our system and am struggling with the syntax. What I really want to do is dump the results into an empty table that already has the fields in it.

I am trying this:
(
      SELECT  (
                        COUNT(DISTINCT[UserID])
                        ,[Siteid]
                         ,[Year]
                         ,[Month]
                         ,[Day]
                  )
      FROM [MyDB].[dbo].[vw_WkOnly]
      WHERE cast(convert(varchar,[Entrydate],101) as datetime) >= cast(convert(varchar,'01 January 2017',101) as datetime) AND
            cast(convert(varchar,[Entrydate],101) as datetime) <= cast(convert(varchar,'31 Dec 2017',101) as datetime)
            GROUP BY [SiteID], [Year], [Month],[Day]
            order by [SiteID], [year], [Month], [Day]    (I guess the order is not important here as I am putting into a table that I can query)
)

So, eg
For Site 1 I will have 25 users for 2017, month 1, Day 1
For Site 2 I will have 48 users for 2017, month 1, Day 1

For Site 1 I will have 16 users for 2017, month 3, Day 6
For Site 2 I will have 75 users for 2017, month 3, Day 6  etc etc

And I want to put these results into a table that contains:

SiteNo
UserCount
Day
Month
Year

Thank you!
alexkingAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
I noted that you already have the dateparts [year], [month], [day] so it becomes a straight query
select SiteID, count(*) as UserCount, [YEAR],[MONTH],[DAY] 
  FROM [vw_WkOnly]
  WHERE Entrydate >= '2017-01-01'
  AND   entrydate < '2018-01-01'
  GROUP BY [SiteID], [Year], [Month],[Day]
  order by [SiteID], [year], [Month], [Day] 

Open in new window


And to insert into your table....
INSERT yourtable(siteID,userCount,[Year],[month],[day])
select SiteID, count(*) as UserCount, [YEAR],[MONTH],[DAY] 
  FROM [vw_WkOnly]
  WHERE Entrydate >= '2017-01-01'
  AND   entrydate < '2018-01-01'
  GROUP BY [SiteID], [Year], [Month],[Day]
  order by [SiteID], [year], [Month], [Day] 

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Is it possible to get some sample data ?

Data definitions ?

What version of SQL Server ?
0
 
alexkingAuthor Commented:
SQL server 2014

In the View I am using I will have:

SiteID (Int)      Entrydate (datetime)                          UserID      (GUID)                      Day(tinyint)      Month(tinyint)      Year (smallint)
1              2017-08-03 09:07:12.553        01A5D762-F799-45F7-98C7-022EBC6AC89F           3                          8                                     2017
1              2017-08-04 13:07:42.343        92W5E662-E799-95T7-6807-156EBC6A8888           4                          8                                     2017
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ste5anSenior DeveloperCommented:
First of all: Change your date handling. The string cast are unnecessary and error prone. Use unambiguous date literals instead.
 
Second: Why has the Entrydate a time portion?

Third: String the date split over three columns violates 1NF.

E.g.
SELECT   COUNT(DISTINCT [UserID]) ,
         [Siteid] ,
         [Year] ,
         [Month] ,
         [Day]
FROM     [MyDB].[dbo].[vw_WkOnly]
WHERE    Entrydate >= '20170101'
         AND Entrydate < '20180101'
GROUP BY [SiteID] ,
         [Year] ,
         [Month] ,
         [Day]
ORDER BY [SiteID] ,
         [year] ,
         [Month] ,
         [Day];

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Will run up the test data, but initial query would be :


;with cte_Site as
( SELECT  [UserID] ,[Siteid], datepart(year,entrydate) as [year], datepart(month,entrydate) as [month], datepart(day,entrydate) as [day]
  FROM [vw_WkOnly]
  WHERE Entrydate >= '2017-01-01'
  AND   entrydate < '2018-01-01'
 
) select SiteID, count(*) as UserCount, [YEAR],[MONTH],[DAY] 
  from cte_Site
  GROUP BY [SiteID], [Year], [Month],[Day]
  order by [SiteID], [year], [Month], [Day] 

Open in new window

0
 
ste5anSenior DeveloperCommented:
Or:

SELECT   [Siteid] ,
         YEAR(CAST(EntryDate AS DATE)) AS [Year] ,
         MONTH(CAST(EntryDate AS DATE)) AS [Month] ,
         DAY(CAST(EntryDate AS DATE)) AS [Day] ,
         COUNT(DISTINCT [UserID])
FROM     [MyDB].[dbo].[vw_WkOnly]
WHERE    Entrydate >= '20170101'
         AND Entrydate < '20180101'
GROUP BY [SiteID] ,
         CAST(EntryDate AS DATE)
ORDER BY [SiteID] ,
         CAST(EntryDate AS DATE);

Open in new window

0
 
alexkingAuthor Commented:
Perfect thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.