delete from [BD].[dbo].[PruebasPicking_LE]

insert into [BD].[dbo].[PruebasPicking_LE]
Select 
	a.sku, a.skupickeado
from 
	[192.168.10.14].[MP_StockSupply].picking.PedidosSkuPickeados a, 
	[192.168.10.14].[MP_StockSupply].Picking.PedidosCuerpos b, 
	[192.168.10.14].[MP_StockSupply].Picking.PedidosEncabezados c 
where
	a.PedidoCuerpoId = b.PedidoCuerpoId and b.PedidoEncabezadoId = c.PedidoEncabezadoId 
	and c.numeropedido = '0019473909' 
order by b.sku

if exists(select 1 from sysobjects where name = 'Actualiza_Datos_Empleado_Planillas_Esp')
drop trigger [Actualiza_Datos_Empleado_Planillas_Esp]
GO
-- =============================================
-- Author:		Leonardo Mora
-- Create date: 13/May/2019
-- Description:	Se actualizan datos desde el FAS a Planillas especiales
-- =============================================
CREATE TRIGGER [dbo].[Actualiza_Datos_Empleado_Planillas_Esp]
   ON  [dbo].[PL_Empleados]
   AFTER UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	Update A set TipoBanco = B.TipoBanco,
				 Empleado_Nombre= B.Nombre 
	from LDFAS_KIELSA_PLANILLA.dbo.Tipo_Pago_Empleados A 
	INNER JOIN inserted  B on A.Empleado_Cedula = B.Cedula
	INNER JOIN LDFAS_KIELSA_PLANILLA.dbo.Tipo_Pago C On C.Estado = 'GU'  

	Update TP set 
	 TipoBanco = Inst.TipoBanco
	from LDFAS_KIELSA_PLANILLA.dbo.Pago_Generado_Empleados TP 
	INNER JOIN inserted Inst on TP.Empleado_Cedula = Inst.Cedula;


END
GO

declare @userId bigint;

DECLARE my_cursor CURSOR FOR
select userId from users where userdetailId is NULL;

OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @userId

WHILE @@FETCH_STATUS = 0
BEGIN
	insert into UserDetail DEFAULT VALUES;
	update Users set UserDetailId = (SELECT SCOPE_IDENTITY()) where UserId = @userId;
	FETCH NEXT FROM my_cursor INTO @userId
END

CLOSE my_cursor
DEALLOCATE my_cursor
GO

select top 1000 hq.QuoteID
				,hql.QuoteLoadId
				,hq.CustomerGuid
				,tl.loadguid
				,tcl.customerguid
				,hq.CreatedDate as HorizonCreatedDate
				,tl.CreatedDate as OptimizerCreatedDate
from EchoQuote.Horizon.Quoteload hql
inner join EchoQuote.Horizon.Quote hq on hql.QuoteID = hq.quoteid
inner join EchoOptimizer.dbo.tblLoads tl on hql.loadid = tl.loadid
inner join EchoOptimizer.dbo.tblCustomerLoads tcl on tl.LoadGuId = tcl.loadguid

SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
-- WHERE [Tables].name = N'name of the table'
GROUP BY SCHEMA_NAME(schema_id), [Tables].name;

/*
The purpose of this query is to do the following:
	-Identify attributes about EchoShip customers
	-Identify quotes generated on EchoShip so we can measure losses
		-Differentiate between quotes generated through the Book Shipment path & the Quote & Book Shipment path
			-In the Book Shipment (BS) path, users enter in all details about the items and route before viewing a list of quotes.
			-In the Quote & Book Shipment (QBS) path, users are able to view a list of quotes before adding the specific details about the route and items
	-Calculate conversion rate on an account level
	-Segment clients based on volume, gp, and industry for comparative analyses
*/


--BLOCK 1: CUSTOMER ATTRIBUTES


--The table below pulls summary information about every Echo customer

if object_id('tempdb.db.#cust_attributes1', 'U') is not null drop table #cust_attributes1
select		c.customerid
			,c.customername
			,c.CustomerGuid
			,pc.customerid parentaccountid
			,c.segment
			,count(*) vol		--loads in past year
			,sum(trm.reportrevenue - l.cogs) gp		--gp in past year
into		#cust_attributes1
from		EchoOptimizer.dbo.tblCustomer c
left join	echooptimizer.dbo.tblCustomerLoads cl on c.CustomerGuid = cl.CustomerGuiD 
left join   (select * from echooptimizer.dbo.tblloads where cast(echooptimizer.dbo.tblloads.createddate as date) between dateadd(d,-366,cast(getdate() as date)) 
															and dateadd(d,-1,cast(getdate() as date))) l
			on cl.loadguid = l.loadguid			--identifies all loads created in past 366 days
left join	echooptimizer.dbo.tblreportmoney trm on l.loadguid = trm.loadguid
left join	echooptimizer.dbo.tblcustomer pc on c.parentaccountid = pc.customerguid
group by	c.customerid
			,c.customername
			,pc.customerid
			,pc.customername
			,c.segment
			,c.CustomerGuid
;

--The table below identifies information about each company's industry. Colin Johnston is most knowledgable about the data in the NaicsCodeDesc table

if object_id('tempdb.db.#industry', 'U') is not null drop table #industry
select acct.E_Number__c
		,acct.NaicsCode as IndustryCodeFull		--industry code
		,left(acct.NaicsCode,2) as IndustryCodeTwoDigit			--twodigit industry code (for summarizing)
		,n.[Description]		--industry description
into	#industry
from SalesAnalytics.Salesforce.Account acct
left join (select NaicsCode, [Description] from SalesAnalytics.dbo.NaicsCodeDesc where digits = 2) n on left(acct.NaicsCode,2) = n.NaicsCode
where acct.E_Number__c is not null
;

--The table below appends industry information to our customer attributes table

if object_id('tempdb.db.#cust_attributes2', 'U') is not null drop table #cust_attributes2
select ca.CustomerGuid
		,ca.CustomerName
		,ca.CustomerId
		,ca.Parentaccountid
		,ca.vol
		,ca.gp
		,ca.Segment
		,i.[Description]
		,i.IndustryCodeTwoDigit
		,i.IndustryCodeFull
into	#cust_attributes2
from	#cust_attributes1 ca
left join #industry i on ca.customerid = i.E_Number__c
;


--BLOCK 2A: BOOK SHIPMENT PATH


--The table below identifies all pageviews where a quote was generated through the Book Shipment path

if object_id('tempdb.db.#bs_quotes_source', 'U') is not null drop table #bs_quotes_source
select firstpartycookies_accountguid		--This is the same as CustomerGuid. Tealium recognizes it as a first party cookie.
		,c.CustomerName
		,EventId		--Every pageview is an 'event'. Tealium generates a unique identifier for each event
		,pageurl_domain		--This is the page url. The Tealium source data collects pageviews from EchoTrak/EchoShip/EchoDrive.
		,udo_page_name
		,udo_page_type
		,DATEADD(hour, -6, DATEADD(ss, CAST (udo_tealium_timestamp_epoch AS int), '19700101')) as dt		--The Tealium source data is formatted as epoch time; this converts to standard datetime
into	#bs_quotes_source
from	InformaticaTest.web.TealiumStaging ts
left join EchoOptimizer.dbo.tblCustomer c on ts.firstpartycookies_accountguid = c.CustomerGuid
where udo_page_name in ('carrier')		--This is the page_name of the view quotes page along the book shipment path. EchoShip is a single-page application (all pages have the same url), so this is necessary to differentiate between page views.
		and udo_page_type in ('new-shipment')			--This is the page_type of the view quotes page along the book shipment path
		and pageurl_domain not in ('shipperportal-www-dev1.echo.com', 'shipperportal-www-qa1.echo.com', 'shipperportal-www-dev2.echo.com', 'dev1-eswww01.dev.echogl.net', 'dev2-eswww01.dev.echogl.net')		--This removes pageviews done by our devs
		and DATEADD(hour, -6, DATEADD(ss, CAST (udo_tealium_timestamp_epoch AS int), '19700101')) > '09-30-2018'		--I need to run all events prior to 9/30/2018 through my ETL. Once this is done I will remove this qualifier
;

/*
The table below does two important things.
1: It associates pageviews with quoteids. Under our current configuration (as of 3/25/19) Tealium does not capture the quoteid that is generated on the page. To account for this we do the following:
	a. Calculate the datediff(ss) between the quote createddate and the pageview

	b. Determine whether the quote createddate was before or after the pageview. 

		Most of the time, the pageview should be before the createddate. The Tealium tags fire asynchroniously, meaning that they fire in a manner that does not disrupt the page load. 
		In most (70-80%) cases, the Tealium tag fires before the API pings RateIQ and RateIQ is able to generate a quote.
		However, since this is not always the case, and since some pageviews may not be real pageviews, this data is not 100% reliable (i.e. someone hit back button too many times, thus triggering the tags to fire but the page doesn't load).
		Geoffrey set the time difference to be no more than an hour in either direction. I am currently experimenting with tighter timeframes.

 	c. As there may be many quotes generated within the timeframe referenced above, we order each row based on timediff between the pageview datetime and the createddate. The lowest timediff receives a value of 1, which we use for determining match.

2: It identifies the origin and destination zip for each quote. This is necessary for calculating cross-channel conversions.
	
*/
if object_id('tempdb.db.#bs_quotes_matched', 'U') is not null drop table #bs_quotes_matched
select bsqs.firstpartycookies_accountguid
		,bsqs.CustomerName
		,bsqs.eventid
		,bsqs.pageurl_domain
		,bsqs.udo_page_type
		,bsqs.udo_page_name
		,hq.QuoteID
		,ql.QuoteLoadID
		,hq.CreatedDate
		,bsqs.dt
		,GLO.Name as Origin_Zip		
		,GLO.CountryCode as Origin_Country
		,GLD.Name as Destination_Zip
		,GLD.CountryCode as Destination_Country
		,concat(GLO.Name, GLD.Name) as quote_org_dest		--This can be used for identifying cross-channel conversions
		,abs(datediff(s, bsqs.dt, hq.CreatedDate)) as TimeDiff		--Calculates timediff between pageview and when quote was created in RateIQ
		,sign(datediff(s, bsqs.dt, hq.CreatedDate)) as HorizonBeforeEchoShip		--Determines whether pageview happened before or after when quote was generated in RateIQ
		,row_number() over(partition by bsqs.EventId order by abs(datediff(s, bsqs.dt, hq.CreatedDate))) as r
into	#bs_quotes_matched
from	#bs_quotes_source as bsqs
left join EchoQuote.horizon.quote hq on try_convert(uniqueidentifier, bsqs.firstpartycookies_accountguid) = hq.CustomerGuid 
		and hq.CreatedDate between dateadd(hour, -1, bsqs.dt) and dateadd(hour, 1, bsqs.dt)		--Timeframe for determining whether the quote should be attributed to the pageview. This may want to be tightened to a 10 minute window.
		and hq.OriginApplicationName is not null
left join EchoQuote.Horizon.QuoteLoad ql on hq.QuoteID = ql.QuoteID 
left join EchoQuote.Horizon.Location LO ON HQ.QuoteID = LO.QuoteID and LO.SourceTypeID = 1
left join EchoQuote.Horizon.Location LD ON HQ.QuoteID = LD.QuoteID and LD.SourceTypeID = 2
left join SearchRepository.Geo.Location GLO ON LO.LocationSearchID = GLO.ID
left join SearchRepository.Geo.Location GLD ON LD.LocationSearchID = GLD.ID
;

--The table below gives a count of quotes and loads that were generated through the Book Shipment path.

if object_id('tempdb.db.#bs_quotes_final', 'U') is not null drop table #bs_quotes_final
select firstpartycookies_accountguid
		,count(quoteid) as bs_quotes
		,count(quoteloadid) as bs_loads
		,min(bsqm.dt) as first_bs_quote_dt		--Identifies first time a user generates a quote on the book shipment path. Useful for understanding when a user became active on EchoShip.
into	#bs_quotes_final
from	#bs_quotes_matched bsqm
where r = 1
group by firstpartycookies_accountguid
;


--BLOCK 2B: QUOTE & BOOK SHIPMENT PATH

--The tables in this block are the same as block 2a, except these look at quotes generated on the quote & book shipment path.

if object_id('tempdb.db.#qbs_quotes_source', 'U') is not null drop table #qbs_quotes_source
select firstpartycookies_accountguid
		,c.CustomerName
		,EventId
		,pageurl_domain
		,udo_page_name
		,udo_page_type
		,DATEADD(hour, -6, DATEADD(ss, CAST (udo_tealium_timestamp_epoch AS int), '19700101')) as dt
into	#qbs_quotes_source
from	InformaticaTest.web.TealiumStaging ts
left join EchoOptimizer.dbo.tblCustomer c on ts.firstpartycookies_accountguid = c.CustomerGuid
where	udo_page_name in ('carrier-quote')
		and udo_page_type in ('new-quote')
		and pageurl_domain not in ('shipperportal-www-dev1.echo.com', 'shipperportal-www-qa1.echo.com', 'shipperportal-www-dev2.echo.com', 'dev1-eswww01.dev.echogl.net', 'dev2-eswww01.dev.echogl.net')
		and DATEADD(hour, -6, DATEADD(ss, CAST (udo_tealium_timestamp_epoch AS int), '19700101')) > '09-30-2018'
;

if object_id('tempdb.db.#qbs_quotes_matched', 'U') is not null drop table #qbs_quotes_matched
select qbsqs.firstpartycookies_accountguid
		,qbsqs.CustomerName
		,qbsqs.eventid
		,qbsqs.pageurl_domain
		,qbsqs.udo_page_type
		,qbsqs.udo_page_name
		,hq.QuoteID
		,ql.QuoteLoadID
		,hq.CreatedDate
		,qbsqs.dt
		,GLO.Name as Origin_Zip
		,GLO.CountryCode as Origin_Country
		,GLD.Name as Destination_Zip
		,GLD.CountryCode as Destination_Country
		,concat(GLO.Name, GLD.Name) as quote_org_dest
		,abs(datediff(s, qbsqs.dt, hq.CreatedDate)) as TimeDiff
		,sign(datediff(s, qbsqs.dt, hq.CreatedDate)) as HorizonBeforeEchoShip
		,row_number() over(partition by qbsqs.EventId order by abs(datediff(s, qbsqs.dt, hq.CreatedDate))) as r
into	#qbs_quotes_matched
from	#qbs_quotes_source as qbsqs
left join EchoQuote.horizon.quote as hq on try_convert(uniqueidentifier, qbsqs.firstpartycookies_accountguid) = hq.CustomerGuid 
	and hq.CreatedDate between dateadd(hour, -1, qbsqs.dt) and dateadd(hour, 1, qbsqs.dt)
	and hq.OriginApplicationName is not null
left join EchoQuote.Horizon.QuoteLoad ql on hq.QuoteID = ql.QuoteID
inner join EchoQuote.Horizon.Location LO ON HQ.QuoteID = LO.QuoteID and LO.SourceTypeID = 1
inner join EchoQuote.Horizon.Location LD ON HQ.QuoteID = LD.QuoteID and LD.SourceTypeID = 2
inner join SearchRepository.Geo.Location GLO ON LO.LocationSearchID = GLO.ID
inner join SearchRepository.Geo.Location GLD ON LD.LocationSearchID = GLD.ID
;

if object_id('tempdb.db.#qbs_quotes_final', 'U') is not null drop table #qbs_quotes_final
select firstpartycookies_accountguid
		,count(quoteid) as qbs_quotes
		,count(QuoteLoadID) as qbs_loads
		,min(qbsqm.dt) as first_qbs_quote_dt
into	#qbs_quotes_final
from	#qbs_quotes_matched qbsqm
where r = 1
group by firstpartycookies_accountguid
;


--BLOCK 3: UNIONING GUIDS ACROSS BOOK SHIPMENT & QUOTE AND BOOK SHIPMENT PATHS


--The table below grabs all the CustomerGuids from the final tables for each path. Necessary because some clients may only use one of the two paths to book a load.

if object_id('tempdb.db.#guids', 'U') is not null drop table #guids
select firstpartycookies_accountguid.*
into   #guids
from   (select firstpartycookies_accountguid from #qbs_quotes_final
		union
		select firstpartycookies_accountguid from #bs_quotes_final) firstpartycookies_accountguid
where firstpartycookies_accountguid is not null
;


--BLOCK 4: CONVERSION RATES


--The table below calculates the conversion rate for each path, and also provides a percentile value for each customer's volume and GP

if object_id('tempdb.db.#conversion_source', 'U') is not null drop table #conversion_source
select g.firstpartycookies_accountguid
		,ca.CustomerName
		,ca.CustomerId
		,ca.Parentaccountid
		,ca.vol
		,case when ca.vol is not null then ntile(100) over (order by vol asc) else null end as vol_ntile
		,ca.gp
		,case when ca.gp is not null then ntile(100) over (order by gp asc) else null end as gp_ntile
		,ca.[description]
		,ca.IndustryCodeTwoDigit
		,ca.IndustryCodeFull
		,bs.bs_quotes
		,bs.bs_loads
		,cast(bs_loads as decimal)/nullif(bs_quotes,0) as bs_conv		--Conversion rate for book shipment path
		,bs.bs_quotes - bs.bs_loads as bs_quote_diff		--Count of how many more quotes than loads a client has on the Book Shipment path
		,qbs.qbs_quotes
		,qbs.qbs_loads
		,cast(qbs_loads as decimal)/nullif(qbs_quotes,0) as qbs_conv		--Conversion rate for quote and book shipment path
		,qbs.qbs_quotes - qbs.qbs_loads as qbs_quote_diff		--Count of how many more quotes than loads a client has on the Quote & Book Shipment path
		,isnull(isnull(bs_quotes,0) + isnull(qbs_quotes,0),0) as total_quotes		--total quotes across both paths
		,isnull(isnull(bs_loads,0) + isnull(qbs_loads,0),0) as total_loads		--total loads across both paths
		,case when first_bs_quote_dt <= first_qbs_quote_dt 
			  then first_bs_quote_dt else first_qbs_quote_dt end as first_quote_dt
		,case when (isnull(isnull(bs_quotes,0) + isnull(qbs_quotes,0),0)) = 0 then null
			  else ((isnull(cast(qbs_loads as decimal),0) + isnull(cast(bs_loads as decimal),0)) / (isnull(isnull(bs_quotes,0) + isnull(qbs_quotes,0),0)))
			  end as total_conv
into	#conversion_source
from	#guids g
left join #bs_quotes_final bs on g.firstpartycookies_accountguid = bs.firstpartycookies_accountguid
left join #qbs_quotes_final qbs on g.firstpartycookies_accountguid = qbs.firstpartycookies_accountguid
left join #cust_attributes2 ca on g.firstpartycookies_accountguid = ca.CustomerGuid
;

--The table below categorizes volume and gp percentiles into low/medium/high

if object_id('tempdb.db.#conversion_percentiles', 'U') is not null drop table #conversion_percentiles
select cs.firstpartycookies_accountguid
		,cs.CustomerName
		,cs.[description]
		,cs.industryCodeFull
		,cs.vol
		,cs.vol_ntile
		,case when vol_ntile is null then 'low'
				when vol_ntile between 0 and 32 then 'low'
				when vol_ntile between 33 and 65 then 'medium'
				when vol_ntile between 66 and 100 then 'high'
				else null end as vol_level
		,cs.gp
		,cs.gp_ntile
		,case when gp_ntile is null then 'low'
				when gp_ntile between 0 and 32 then 'low'
				when gp_ntile between 33 and 65 then 'medium'
				when gp_ntile between 66 and 100 then 'high'
				else null end as gp_level
		,total_quotes
		,total_loads
		,cast(total_conv as decimal(4,3)) as total_conv
		,cast(bs_conv as decimal(4,3)) as bs_conv
		,cast(qbs_conv as decimal(4,3)) as qbs_conv
into	#conversion_percentiles
from	#conversion_source cs
;

/*
The purpose of this query is to identify the following:
	-Time spent on a page
	-Which page was viewed before the current page
	-Which page was viewed after the current page
	-Flag pageviews where the user may have been idle
*/


if object_id('tempdb.db.#visits_source', 'U') is not null drop table #visits_source
select firstpartycookies_accountguid as CustomerGuid		--Tealium recognizes CustomerGuid as a first party cookie
		,c.CustomerName			--Customername
		,EventId				--Unique identifier created for each page visit
		,pageurl_domain			--Specifies whether EchoDrive, EchoShip or EchoTrak
		,udo_page_type			--For EchoShip views only. EchoShip is a single-page application, where each page has the same url but different page names and page types
		,udo_page_name			--For EchoShip views only. EchoShip is a single-page application, where each page has the same url but different page names and page types
		,concat(udo_page_type, ' : ', udo_page_name) as page_desc		--Combines page_type and page_name into a single identifier
		,DATEADD(hour, -6, DATEADD(ss, CAST (udo_tealium_timestamp_epoch AS int), '19700101')) as visittime						--Tealium uses epoch timestamps. This converts to standard datetime
		,convert(date,DATEADD(hour, -6, DATEADD(ss, CAST (udo_tealium_timestamp_epoch AS int), '19700101'))) as visitdate		--Converts epoch timestamp to standard date format
		,row_number() over(partition by firstpartycookies_accountguid, 
										convert(date,DATEADD(hour, -6, DATEADD(ss, CAST (udo_tealium_timestamp_epoch AS int), '19700101')))
										order by DATEADD(hour, -6, DATEADD(ss, CAST (udo_tealium_timestamp_epoch AS int), '19700101'))) as sessionorder			--Assigns a number for which page view in the session the event was. 
into	#visits_source
from	InformaticaTest.web.TealiumStaging ts
left join EchoOptimizer.dbo.tblCustomer c on ts.firstpartycookies_accountguid = c.CustomerGuid
where pageurl_domain like '%echoship%'		--Filter based on EchoShip/EchoDrive/EchoTrak
		and udo_page_name is not null
		and CustomerGuid is not null
order by CustomerGuid, visittime asc
;

--The table below assigns a row number to every row. This allows for analysis based on each session.

if object_id('tempdb.db.#visits_ordered', 'U') is not null drop table #visits_ordered
select *
	   ,row_number() over(order by CustomerGuid, visittime) as allrows		--Provides a row number for every row in the dataset; needed to calculate timediff between pageviews in following temp table
into	#visits_ordered
from	#visits_source
order by allrows
;

--The table below determines which page was viewed before/after the current page view. Also determines time spent on page. Also flags pageviews that lasted for >999 seconds as likely having idle time

if object_id('tempdb.db.#visits_timed', 'U') is not null drop table #visits_timed
select CustomerGuid
		,CustomerName
		,eventid
		,visitdate
		,visittime
		,pageurl_domain
		,page_desc
		,case when (lag(sessionorder,1,0) over (order by allrows asc)) < sessionorder
			  then (lag(page_desc,1,0) over (order by allrows asc)) else null end as prev_page		--This determines which page the user viewed previously
		,case when (lead(sessionorder,1,0) over (order by allrows asc)) > sessionorder
			  then (lead(page_desc,1,0) over (order by allrows asc)) else null end as next_page		--This determines which page the user views next
		,sessionorder
		,allrows
     	,case when (lead(sessionorder,1,0) over (order by allrows asc)) > sessionorder
		      then datediff(s,visittime,(lead(visittime,1,0) over (order by allrows asc))) else null end as view_duration	--This calculates the time difference between the current row and the following row to determine time spent on page.
		,case when (lead(sessionorder,1,0) over (order by allrows asc)) > sessionorder
			  then
			       case when datediff(s,visittime,(lead(visittime,1,0) over (order by allrows asc))) >999
				   then 1 else 0 end
			  else null end as idle_flag		--This flags pageviews exceeding 999 seconds as pageviews with idle time (i.e. the user walked away from the computer but left the window open).
into #visits_timed
from #visits_ordered
order by allrows
;

-----------------------------------------------------------------
    -- Provide the value for the following parameters
    DECLARE @prefix nvarchar(32) =''
    DECLARE @schema nvarchar(32) ='dbo'
    -----------------------------------------------------------------
    
    DECLARE @sql nvarchar(max) = '';
    
    SELECT @sql += 'DROP TABLE ' + QUOTENAME([TABLE_SCHEMA]) + '.' + QUOTENAME([TABLE_NAME]) + ';'
    FROM [INFORMATION_SCHEMA].[TABLES]
    WHERE [TABLE_TYPE] = 'BASE TABLE' AND [TABLE_NAME] like @prefix + '_%' AND [TABLE_SCHEMA]= @schema;
    
    PRINT @sql
    EXEC SP_EXECUTESQL @sql;
    
    PRINT 'Finished dropping all tables. Starting to drop all stored procedures now.'
    
    SELECT @sql='';
    SELECT @sql += 'DROP PROCEDURE ' + QUOTENAME([ROUTINE_SCHEMA]) + '.' + QUOTENAME([ROUTINE_NAME]) + ';'
    FROM [INFORMATION_SCHEMA].[ROUTINES]
    WHERE [ROUTINE_TYPE] = 'PROCEDURE' AND [ROUTINE_NAME] like @prefix + '_%' AND [ROUTINE_SCHEMA]= @schema;
    PRINT @sql
    EXEC SP_EXECUTESQL @sql;
    
    PRINT 'Finished dropping all stored procedures. Starting to drop all types now.'
    
    SELECT @sql=''; 
    SELECT @sql += 'DROP TYPE ' + QUOTENAME(SCHEMA_NAME([SCHEMA_ID])) + '.' +  QUOTENAME([NAME]) + ';'
    FROM SYS.TYPES
    WHERE is_user_defined = 1 AND [NAME] LIKE @prefix + '_%' AND [SCHEMA_ID]=SCHEMA_ID(@schema);
    
    PRINT @sql
    EXEC SP_EXECUTESQL @sql;

BACKUP DATABASE [ran_web] TO DISK = N'F:\sql_bak\ran_web.bak' WITH NAME = N'ran_web - Backup',NOFORMAT,NOINIT,SKIP,STATS = 5
BACKUP DATABASE [ran_info] TO DISK = N'F:\sql_bak\ran_info.bak' WITH NAME = N'ran_info - Backup',NOFORMAT,NOINIT,SKIP,STATS = 5
BACKUP DATABASE [ran_info_577] TO DISK = N'F:\sql_bak\ran_info_577.bak' WITH NAME = N'ran_info_577 - Backup',NOFORMAT,NOINIT,SKIP,STATS = 5
BACKUP DATABASE [rzxt] TO DISK = N'F:\sql_bak\rzxt.bak' WITH NAME = N'rzxt - Backup',NOFORMAT,NOINIT,SKIP,STATS = 5

select ts.Time_Sheet_RecID as id
,lower(m.member_id) as member_id
,cast(m.First_Name as varchar)+ ' ' + cast(m.Last_Name as varchar) as Member_Name
,m.First_Name
,m.Last_Name
,m.Role_ID as role
,m.Email_Address as email
,m.title
,lower(m.Reports_To) as Reports_To
,ol.Description as Location
,mt.Description as Member_Type
,bu.Description as Billing_Unit
,wr.Description as Default_Work_Role
,loc.Description as Default_Service_Location
,lower(ts.Updated_By) as time_sheet_last_updated_by
,tes.Description as time_sheet_status
,cast(ts.Last_Update_UTC	as datetime) as time_sheet_last_update
,cast(ts.Date_Entered_UTC as datetime) as time_sheet_entered
,CAST(tep.Date_Start AS DATETIME) as time_period_start
,CAST(tep.Date_End AS DATETIME) as time_period_end
,CAST(tep.Date_DeadLine AS DATETIME) as time_sheet_due_date
,CAST(tep.Date_DeadLine AS DATETIME) - 1 as adj_time_sheet_due_date
,te.ha as hours_actual
from time_sheet ts
left join member as m on ts.member_recid = m.member_recid
left join owner_level as ol on ol.Owner_Level_RecID = m.Owner_Level_RecID
left join member_Type as mt on mt.Member_Type_RecID = m.Member_Type_RecID
left join billing_unit as bu on m.SR_Billing_Unit_RecID = bu.Billing_Unit_RecID
left join activity_type as wr on m.Activity_Type_RecID = wr.Activity_Type_RecID
left join sr_location as loc on m.sr_location_Recid = loc.sr_location_Recid
left join te_Status as tes on tes.TE_Status_ID = ts.TE_Status_ID
left join TE_Period as tep on tep.TE_Period_RecID = ts.TE_Period_RecID
left join (select Time_Sheet_RecID as id
                    ,Member_RecID as mem
                    ,sum(hours_actual) as ha
                    /*,sum(coalesce(CASE WHEN Billable_Flag = 1 AND Invoice_Flag = 1 THEN Hours_Invoiced END,0)) AS hb
                    ,sum(coalesce(CASE WHEN Billable_Flag = 0 AND Invoice_Flag = 0 THEN Hours_Invoiced END,0)) AS hnb
                    ,sum(coalesce(CASE WHEN Billable_Flag = 0 AND Invoice_Flag = 1 THEN Hours_Invoiced END,0)) AS hnc
                    -- the above fields exist and can be added in, but CW shows hours actual in the time sheet */
                    from time_entry
                    group by Time_Sheet_RecID, Member_RecID) as te on te.id = ts.Time_Sheet_RecID and te.mem = ts.member_recid
where ts.Last_Update_UTC > dateadd(mm,-24,current_timestamp) and  m.Inactive_Flag = 'False'