CODEWITHSUNDEEP
sqlsql-servert-sql
user23495
user23495

Reputation: 1481

SQL Server: Attempting to output a count with a date

I am trying to write a statement and just a bit puzzled what is the best way to put it together. So I am doing a UNION on a number of tables and then from there I want to produce as the output a count for the UserID within that day.

So I will have numerous tables union such as:

Order ID, USERID, DATE, Task Completed.
UNION
Order ID, USERID, DATE, Task Completed
etc

Above is layout of the table which will have 4 tables union together with same names.

Then statement output I want is for a count of USERID that occurred within the last 24 hours.

So output should be:

USERID--- COUNT OUTPUT-- DATE

I was attempting a WHERE statement but think the output is not what I am after exactly, just thinking if anyone can point me in the right direction and if there is alternative way compared to the union? Maybe a joint could be a better alternative, any help be appreciated.

I will eventually then put this into a SSRS report, so it gets updated daily.

Upvotes: 2

Views: 91

Answers (4)

Dannyg9090
Dannyg9090

Reputation: 196

I think this is what you are trying to achieve... 

Select
    UserID,
    Date,
    Count(1)
from 
    (Select *
     from table1
    Union All
     Select *
     from table2
    Union All
     Select *
     from table3
    Union All
     Select *
     from table4
    ) a
Group by 
    Userid,
    Date

Upvotes: 0

chixco
chixco

Reputation: 53

For even more readability, you could use a CTE:

;WITH cte_CTEName AS(
SELECT UserID, Date, [Task Completed] FROM Table1
UNION
SELECT UserID, Date, [Task Completed] FROM Table2
etc
)

SELECT COUNT(UserID) AS [Count] FROM cte_CTEName
WHERE Date <= GETDATE() AND Date >= DATEADD(hh, -24, GETDATE())

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269663

First, you should use union all rather than union. Second, you need to aggregate and use count distinct to get what you want:

So, the query you want is something like:

select count(distinct userid)
from ((select date, userid
       from table1
       where date >= '2015-05-26'
      ) union all
      (select date, userid
       from table2
       where date >= '2015-05-26'
      ) union all
      (select date, userid
       from table3
       where date >= '2015-05-26'
      )
     ) du

Note that this hardcodes the date. In SQL Server, you would do something like:

date >= cast(getdate() - 1 as date)

And in MySQL

date >= date_sub(curdate(), interval 1 day)

EDIT:

I read the question as wanting a single day. It is easy enough to extend to all days:

select cast(date as date) as dte, count(distinct userid)
from ((select date, userid
       from table1
      ) union all
      (select date, userid
       from table2
      ) union all
      (select date, userid
       from table3
      )
     ) du
group by cast(date as date) 
order by dte;

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You can try this:

select USERID, count(*) as [COUNT], cast(DATE as date) as [DATE]
from 
(select USERID, DATE From SomeTable1
 union all
 select USERID, DATE From SomeTable2
 ....
) t
where DATE <= GETDATE() AND DATE >= DATEADD(hh, -24, GETDATE())
group by USERID, cast(DATE as date)

Upvotes: 4

Related Questions