Help Requre in SQL Query SEPM Database

See more:

SQL

Hi,


I have created below query to get the count..
I need to add a count how many system communicate in last 7 days (0 to 7 Days)


I want to add last check-in count in my below query please suggest..
Below table (Last_Update_Time) is in UNIX Time format.

,dateadd(second, SEM_AGENT.LAST_UPDATE_TIME/1000, '01-01-1970 00:00:00') AS 'Last check-in'

=========================

SELECT
IDENTITY_MAP.NAME AS 'Group Name' , count(*) as 'Total Clients'
,count(case when STATUS = '1' then 1 end) as 'Online Count'
,count(case when STATUS = '0' then 1 end) as 'Offline Count'
,count(case when DATEDIFF(DD,P.PATTERNDATE,GETDATE()) < 7 then 1 end) 'Updated (0-7 Days)'
,count(case when DATEDIFF(DD,P.PATTERNDATE,GETDATE()) > 7 then 1 end) 'Out-Of-Date (0-7 Days)'
FROM   ((((SEM_AGENT SEM_AGENT 
 INNER JOIN SEM_CLIENT SEM_CLIENT ON ((SEM_AGENT.COMPUTER_ID=SEM_CLIENT.COMPUTER_ID) AND (SEM_AGENT.DOMAIN_ID=SEM_CLIENT.DOMAIN_ID)) AND (SEM_AGENT.GROUP_ID=SEM_CLIENT.GROUP_ID)) 
 INNER JOIN SEM_COMPUTER SEM_COMPUTER ON ((SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID) AND (SEM_AGENT.DOMAIN_ID=SEM_COMPUTER.DOMAIN_ID)) AND (SEM_AGENT.DELETED=SEM_COMPUTER.DELETED)) 
 INNER JOIN PATTERN PATTERN ON SEM_AGENT.PATTERN_IDX=PATTERN.PATTERN_IDX) 
 INNER JOIN IDENTITY_MAP IDENTITY_MAP ON SEM_CLIENT.GROUP_ID=IDENTITY_MAP.ID)
 INNER JOIN V_SEM_COMPUTER V_SEM_COMPUTER ON SEM_COMPUTER.COMPUTER_ID=V_SEM_COMPUTER.COMPUTER_ID and SEM_AGENT.DELETED=0
 INNER JOIN PATTERN P ON SEM_AGENT.PATTERN_IDX=P.PATTERN_IDX
group by NAME order by count(*) desc

Thanks in Advance