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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.