A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


Format SSAS Dates from '1900-01-01 00:00:00.000' to '01/01/1900'

 
 
Steps:
1. Add a Varchar Date to your Data Source View
go into your data source view
right click on your date tables header
create a new named calculation
 
 
 
make a new varchar date
 
 
 
 
 
2. In your date Dimenision, Add your new field as the NameColumn on your datetime field.
Find the date you want to change


View it's properties
 Change the namecolumn field , add your new field.
 
 
 
 
 

Create UPC Check Digit Function

 

 
 
The function returns a UPC with the check digit when provided with the prefix numbers.
 
 

 

create function f_get_upc_check_digit (@upc_prefix varchar(32))

returns varchar(32) -- returns the whole upc.

as

 

begin

       -- http://www.gs1.org/barcodes/support/check_digit_calculator

 

       declare @base_index int

       set @base_index = len(@upc_prefix)

 

       declare @index int, @multiplier_start int, @sum int

       set @index = 1

       set @sum = 0

       set @multiplier_start = case when @base_index = 12 then 1 else 3 end

 

       while @index <= @base_index

       begin

                     set @sum = @sum + cast(substring(@upc_prefix, @index, 1) as int) * @multiplier_start

                     set @index = @index + 1

                     set @multiplier_start = case when @multiplier_start = 1 then 3 else 1 end

 

       end

 

       declare @calc_mod int, @check_digit int

       set @calc_mod = @sum%10

       set @check_digit = case when @calc_mod = 0 then 0 else -1 end

 

       while @calc_mod <> 0

       begin

              set @check_digit = @check_digit + 1     

              set @calc_mod = @sum%10

              set @sum = @sum + 1

       end

 

       return @upc_prefix + cast(@check_digit as varchar(64))

 

end

 

go

 

 
 

Test: Actual GTIN-12 UPCs

--886792630591

print dbo.f_get_upc_check_digit ('88679263059')

--886792630522

print dbo.f_get_upc_check_digit ('88679263052')

--886792630553

print dbo.f_get_upc_check_digit ('88679263055')

--886792630584

print dbo.f_get_upc_check_digit ('88679263058')

--886792630515

print dbo.f_get_upc_check_digit ('88679263051')

--886792630546

print dbo.f_get_upc_check_digit ('88679263054')

--886792630577

print dbo.f_get_upc_check_digit ('88679263057')

--886792635268

print dbo.f_get_upc_check_digit ('88679263526')

--886792630539

print dbo.f_get_upc_check_digit ('88679263053')

--886792630560

print dbo.f_get_upc_check_digit ('88679263056')
 
 
Result : 
 
 
 
 
 
Manual Check Digit Check. 
 
 
 
 
 

sp_blocked - procedure for viewing locks and blocks

create procedure sp_blocked
as
begin
 
       SELECT *
       FROM sys.dm_exec_requests
       WHERE blocking_session_id <> 0;
 
       SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
       FROM sys.dm_os_waiting_tasks
       WHERE blocking_session_id <> 0
 
       SELECT resource_type, request_session_id, resource_database_id, resource_associated_entity_id
       , resource_subtype, resource_description, request_status, request_owner_type, request_mode
       FROM sys.dm_tran_locks
       WHERE resource_type IN ('PAGE', 'KEY', 'EXTENT', 'RID');
 
       SELECT tl.request_session_id, wt.blocking_session_id, DB_NAME(tl.resource_database_id) AS DatabaseName
       , tl.resource_type, tl.request_mode, tl.resource_associated_entity_id
       FROM sys.dm_tran_locks as tl
       INNER JOIN sys.dm_os_waiting_tasks as wt
       ON tl.lock_owner_address = wt.resource_address;
 
end