def gross_margin_data(dict_db, date_end):
sql_obj = SQLExchange(driver=dict_db.get('driver'),
server=dict_db.get('server_2'),
db=dict_db.get('db_playground'))
sql = """
declare @dateend date = '{d1}'
declare @datestart date = dateadd("d", -datepart("d", @dateend) + 1, @dateend)
set @datestart = dateadd("m", -11, @datestart)
select
glb.period,
glr.level_1,
sum(glb.amount * glr.report_multiplier) as Amount
from Playground.[myop\jason.walker].gl_balance glb
inner join Playground.[myop\jason.walker].gl_account_reporting glr
on glb.company = glr.company
and glb.gl_account = glr.gl_account
where
glr.level_1 in ('Sales', 'Cost')
and glb.period between @datestart and @dateend
and glb.company in ('HT', 'MYOP')
group by
glb.period,
glr.level_1
""".format(d1=date_end)
sql_result = sql_obj.sql_retrieve(sql)
return sql_result
from class_repo import SQLExchange
import setup1
dict_db = setup1.db_dictionary()
sql_obj = SQLExchange(driver=dict_db.get('driver'),
server=dict_db.get('server_2'),
db=dict_db.get('db_playground'))
connection_string = sql_obj.connection_string()
sql = """
drop table playground.[myop\jason.walker].grid1
"""
sql_obj.sql_no_retrieve(sql)
print('Complete')