Breaking News: Grepper is joining You.com. Read the official announcement!

select account, pvt.* from ( select ur.account, field.name, field.intValue from google.access_token(default,default,default) as gat cross apply ( select * from core.day where date between datefromparts(2022,11,1) and datefromparts(2022,11,1) ) as cd cross

Paula Knowles (STAFF) Support answered on November 7, 2022 Popularity 1/10 Helpfulness 1/10

answer select account, pvt.* from ( select ur.account, field.name, field.intValue from google.access_token(default,default,default) as gat cross apply ( select * from core.day where date between datefromparts(2022,11,1) and datefromparts(2022,11,1) ) as cd cross

select account, pvt.* from ( select ur.account, field.name, field.intValue from google.access_token(default,default,default) as gat cross apply ( select * from core.day where date between datefromparts(2022,11,1) and datefromparts(2022,11,1) ) as cd cross

Comment

select	account, pvt.*
from	(
			select	ur.account, field.name, field.intValue
			from	google.access_token(default,default,default) as gat
					cross apply (
						select	*
						from	core.day
						where	date between datefromparts(2022,11,1) and datefromparts(2022,11,1)
					) as cd
					cross apply google.get(
						formatmessage(
							'https://admin.googleapis.com/admin/reports/v1/usage/users/all/dates/%s',
							convert(nvarchar(256),cd.date)
						),
						(
							select	parameters = concat_ws(',',
										'gmail:num_emails_sent',
										'gmail:num_emails_received'
									)
							for json path, without_array_wrapper
						),
						default,
						gat.access_token
					) as gg
					cross apply openjson(gg.content,'$.usageReports') with (
						account nvarchar(256) '$.entity.userEmail',
						parameters nvarchar(max) '$.parameters' as json
					) as ur
					cross apply openjson(ur.parameters) with (
						name nvarchar(256),
						intValue int
					) as field
			where	account in (
						select	id
						from	security.account
						where	domain = 'cliftoncollege.com'
								and active = 1
					)
		) as raw
		pivot ( sum(intValue) for name in ([gmail:num_emails_sent],[gmail:num_emails_received]) ) as pvt
order by pvt.account

Popularity 1/10 Helpfulness 1/10 Language sql

Source: Grepper

Tags: between cd cross-apply date select sql where

Link to this answer
Share Copy Link

Contributed on Nov 07 2022

Paula Knowles (STAFF) Support

0 Answers Avg Quality 2/10

select account, pvt.* from ( select ur.account, field.name, field.intValue from google.access_token(default,default,default) as gat cross apply ( select * from core.day where date between datefromparts(2022,11,1) and datefromparts(2022,11,1) ) as cd cross

Contents

More Related Answers

select account, pvt.* from ( select ur.account, field.name, field.intValue from google.access_token(default,default,default) as gat cross apply ( select * from core.day where date between datefromparts(2022,11,1) and datefromparts(2022,11,1) ) as cd cross

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.