August 28, 2018 at 12:15 pm
Hello,
Looking to list out end of month dates between a start and an end point in specific data set.
For instance,
Id start_date end_date
1 07/01/2017 08/01/2018
2 06/01/2015 08/01/2017
result
1 07/01/2017
1 08/01/2017
1 09/01/2017
1 10/01/2017
1 11/01/2017
.
1 08/01/2018
2 06/01/2015
2 07/01/2015
2 08/01/2015
2 09/01/2015
2 10/01/2015
2 11/01/2015
.
2 07/01/2017
I have a large data set to loop through. An efficient solution is greatly appreciated.
Thank you in advance.
August 28, 2018 at 1:07 pm
Try this:CREATE TABLE #temp (
Id int NOT NULL,
[start_date] date NOT NULL,
end_date date NOT NULL
);
INSERT INTO #temp (Id, [start_date], end_date)
VALUES (1, '07/01/2017', '08/01/2018'),
(2, '06/01/2015', '08/01/2017');
WITH Nums AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
Tally AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS N
FROM Nums AS A, Nums AS B
)
SELECT
T.Id,
DATEADD(month, T2.N, T.[start_date]) AS THE_START_DATE,
DATEADD(day, -1, DATEADD(month, T2.N + 1, T.[start_date])) AS THE_END_DATE
FROM #temp AS T
CROSS APPLY (
SELECT TN.N
FROM Tally AS TN
WHERE TN.N < DATEDIFF(month, T.[start_date], T.[end_date])
) AS T2
ORDER BY T.Id, THE_START_DATE;
DROP TABLE #temp;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 28, 2018 at 1:11 pm
If you ever need more than 100 months (8 and 1/3 years) for a given input row, just update the query to add another instance of the Nums table in the Tally CTE, and use C as the next table alias. That will get you out to 1,000 months, which is 83 and 1/3 years.
FYI...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 28, 2018 at 1:24 pm
Removed!!!
Steve beat me to it, and surprisingly similar code
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 28, 2018 at 1:27 pm
sgmunson - Tuesday, August 28, 2018 1:07 PMTry this:CREATE TABLE #temp (
Id int NOT NULL,
[start_date] date NOT NULL,
end_date date NOT NULL
);
INSERT INTO #temp (Id, [start_date], end_date)
VALUES (1, '07/01/2017', '08/01/2018'),
(2, '06/01/2015', '08/01/2017');WITH Nums AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
Tally AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS N
FROM Nums AS A, Nums AS B
)
SELECT
T.Id,
DATEADD(month, T2.N, T.[start_date]) AS THE_START_DATE,
DATEADD(day, -1, DATEADD(month, T2.N + 1, T.[start_date])) AS THE_END_DATE
FROM #temp AS T
CROSS APPLY (
SELECT TN.N
FROM Tally AS TN
WHERE TN.N < DATEDIFF(month, T.[start_date], T.[end_date])
) AS T2
ORDER BY T.Id, THE_START_DATE;DROP TABLE #temp;
Thank you in advance.
August 28, 2018 at 1:43 pm
alexander.lummer - Tuesday, August 28, 2018 1:27 PMsgmunson - Tuesday, August 28, 2018 1:07 PMTry this:CREATE TABLE #temp (
Id int NOT NULL,
[start_date] date NOT NULL,
end_date date NOT NULL
);
INSERT INTO #temp (Id, [start_date], end_date)
VALUES (1, '07/01/2017', '08/01/2018'),
(2, '06/01/2015', '08/01/2017');WITH Nums AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
Tally AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS N
FROM Nums AS A, Nums AS B
)
SELECT
T.Id,
DATEADD(month, T2.N, T.[start_date]) AS THE_START_DATE,
DATEADD(day, -1, DATEADD(month, T2.N + 1, T.[start_date])) AS THE_END_DATE
FROM #temp AS T
CROSS APPLY (
SELECT TN.N
FROM Tally AS TN
WHERE TN.N < DATEDIFF(month, T.[start_date], T.[end_date])
) AS T2
ORDER BY T.Id, THE_START_DATE;DROP TABLE #temp;
I have a table with a distinct list of id and its respective start and end dates. Could you please help me incorporate the logic into your solution in place of the free form values you entered.Thank you in advance.
Just realized i do not need the top section since i already have a table with the values. it ran in less the 30 seconds.
thank you very much!!!
August 28, 2018 at 3:49 pm
alexander.lummer - Tuesday, August 28, 2018 1:43 PMalexander.lummer - Tuesday, August 28, 2018 1:27 PMsgmunson - Tuesday, August 28, 2018 1:07 PMTry this:CREATE TABLE #temp (
Id int NOT NULL,
[start_date] date NOT NULL,
end_date date NOT NULL
);
INSERT INTO #temp (Id, [start_date], end_date)
VALUES (1, '07/01/2017', '08/01/2018'),
(2, '06/01/2015', '08/01/2017');WITH Nums AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
Tally AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS N
FROM Nums AS A, Nums AS B
)
SELECT
T.Id,
DATEADD(month, T2.N, T.[start_date]) AS THE_START_DATE,
DATEADD(day, -1, DATEADD(month, T2.N + 1, T.[start_date])) AS THE_END_DATE
FROM #temp AS T
CROSS APPLY (
SELECT TN.N
FROM Tally AS TN
WHERE TN.N < DATEDIFF(month, T.[start_date], T.[end_date])
) AS T2
ORDER BY T.Id, THE_START_DATE;DROP TABLE #temp;
I have a table with a distinct list of id and its respective start and end dates. Could you please help me incorporate the logic into your solution in place of the free form values you entered.Thank you in advance.
Just realized i do not need the top section since i already have a table with the values. it ran in less the 30 seconds.
thank you very much!!!
30 Seconds is an eternity for a lot of queries. Mind posting what your final query looks like?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 28, 2018 at 5:39 pm
Jeff Moden - Tuesday, August 28, 2018 3:49 PMalexander.lummer - Tuesday, August 28, 2018 1:43 PMalexander.lummer - Tuesday, August 28, 2018 1:27 PMsgmunson - Tuesday, August 28, 2018 1:07 PMTry this:CREATE TABLE #temp (
Id int NOT NULL,
[start_date] date NOT NULL,
end_date date NOT NULL
);
INSERT INTO #temp (Id, [start_date], end_date)
VALUES (1, '07/01/2017', '08/01/2018'),
(2, '06/01/2015', '08/01/2017');WITH Nums AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
Tally AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS N
FROM Nums AS A, Nums AS B
)
SELECT
T.Id,
DATEADD(month, T2.N, T.[start_date]) AS THE_START_DATE,
DATEADD(day, -1, DATEADD(month, T2.N + 1, T.[start_date])) AS THE_END_DATE
FROM #temp AS T
CROSS APPLY (
SELECT TN.N
FROM Tally AS TN
WHERE TN.N < DATEDIFF(month, T.[start_date], T.[end_date])
) AS T2
ORDER BY T.Id, THE_START_DATE;DROP TABLE #temp;
I have a table with a distinct list of id and its respective start and end dates. Could you please help me incorporate the logic into your solution in place of the free form values you entered.Thank you in advance.
Just realized i do not need the top section since i already have a table with the values. it ran in less the 30 seconds.
thank you very much!!!
30 Seconds is an eternity for a lot of queries. Mind posting what your final query looks like?
Just curious, but in your results you are showing just the first of each month including the end month. Does the following code meet your requirements?
WITH e1(n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n))
,e2(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b)
,eTally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM e2 a CROSS JOIN e2 b)
SELECT
[t].[Id]
, DATEADD(MONTH,[ca1].[n],[t].[start_date]) AS [StartDate]
, DATEADD(DAY,-1,DATEADD(MONTH,[ca1].[n] + 1,[t].[start_date])) AS [EndDate]
FROM
[#temp] AS [t]
CROSS APPLY (SELECT TOP (DATEDIFF(MONTH,[t].[start_date],[t].[end_date]) + 1) [e].[n] - 1 FROM [eTally] AS [e] ORDER BY [e].[n])ca1(n)
ORDER BY
[t].[Id]
, [t].[start_date];
Edit: Sorry but my cut/paste missed the ORDER BY.
August 29, 2018 at 7:20 am
drop view vw_active_program_product_p1
go
--drop table tbl_active_program_products
--create index indx_active_program_products on (account_id, product_id, product_start_date, product_end_date)
create view vw_active_program_product_p1 as
select
account_id, account_name, product_id, selling_office, region, business_industry, market_segment, product_type, product_subtype, product_name, product_start_date, product_end_date, prod_cat_1, prod_cat_2, prod_cat_3, isnull(product_total_revenue_local, 0) as product_total_revenue_local,
isnull(product_total_gp_local, 0) as product_total_gp_local, isnull(annualized_rev, 0) as annualized_rev, isnull(annualized_gp, 0) as annualized_gp, isnull(adj_annualized_rev, 0) as adj_annualized_rev, isnull(adj_annualized_gp, 0) as adj_annualized_gp, is_insurer, is_broker,
is_tmc, doh_percent, is_prorated,
case when current_status > 0 then 1
else 0 end as current_status,
case when prev_yr_status > 0 then 1
else 0 end as prev_yr_status
from
(select
account_id, account_name, product_id, selling_office, region, business_industry, market_segment, product_type, product_subtype, product_name, product_start_date, product_end_date, prod_cat_1, prod_cat_2, prod_cat_3, sum(product_total_revenue_local) as product_total_revenue_local,
sum(product_total_gp_local) as product_total_gp_local, sum(annualized_rev) as annualized_rev, sum(annualized_gp) as annualized_gp, sum(adj_annualized_rev) as adj_annualized_rev, sum(adj_annualized_gp) as adj_annualized_gp, sum(current_status) as current_status, sum(prev_yr_status) as prev_yr_status,
is_insurer, is_broker, is_tmc, doh_percent, is_prorated
from
(select
account_id, account_name, product_id, selling_office, region, business_industry, market_segment, product_type, product_subtype, product_name, product_start_date /*dateadd(day, 1, eomonth(product_start_date, -1)) as product_start_month, dateadd(day, 1, eomonth(product_end_date, -1)) as product_end_month*/,
product_end_date, product_total_revenue, bi_business_line, prod_cat_1, prod_cat_2, prod_cat_3, current_status, prev_yr_status, package_deal, is_insurer, is_broker, is_tmc, doh_percent, is_prorated, product_total_revenue as product_total_revenue_local, product_total_gp_local,
case when is_prorated is null and 15 > ((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2)) then isnull(product_total_revenue/ nullif(fx_rate, 0), 0)
when is_prorated is null then isnull((product_total_revenue / nullif((((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2))/365), 0))/nullif(fx_rate, 0), 0)
when is_prorated like 'Yes' and 15 > ((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2)) then isnull(product_total_revenue/ nullif(fx_rate, 0), 0)
when is_prorated like 'Yes' then isnull((product_total_revenue /nullif((((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2))/365), 0))/nullif(fx_rate, 0), 0)
when is_prorated like 'Only Greater than 1 year' and ((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2)) >= 347 then isnull(nullif((((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2))/365), 0)/nullif(fx_rate, 0), 0)
when is_prorated like 'Only Greater than 1 year' and ((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2)) < 347 then isnull(product_total_revenue/ nullif(fx_rate, 0), 0)
when is_prorated like 'No' then isnull(product_total_revenue/ nullif(fx_rate, 0), 0)
else 0 end as annualized_rev,
case when is_prorated is null and 15 > ((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2)) then isnull(product_total_gp_local/ nullif(fx_rate, 0), 0)
when is_prorated is null then isnull((product_total_gp_local /nullif((((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2))/365), 0))/nullif(fx_rate, 0), 0)
when is_prorated like 'Yes' and 15 > ((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2)) then isnull(product_total_gp_local/ nullif(fx_rate, 0), 0)
when is_prorated like 'Yes' then isnull((product_total_gp_local /nullif((((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2))/365), 0))/nullif(fx_rate, 0), 0)
when is_prorated like 'Only Greater than 1 year' and ((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2)) >= 347 then isnull(nullif((((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2))/365), 0)/nullif(fx_rate, 0), 0)
when is_prorated like 'Only Greater than 1 year' and ((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2)) < 347 then isnull(product_total_gp_local/ nullif(fx_rate, 0), 0)
when is_prorated like 'No' then isnull(product_total_gp_local/ nullif(fx_rate, 0), 0)
else 0 end as annualized_gp,
case when is_prorated is null and 15 > ((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2)) then isnull(product_total_revenue/ nullif(adj_fx_rate, 0), 0)
when is_prorated is null then isnull((product_total_revenue / nullif((((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2))/365), 0))/nullif(adj_fx_rate, 0), 0)
when is_prorated like 'Yes' and 15 > ((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2)) then isnull(product_total_revenue/ nullif(adj_fx_rate, 0), 0)
when is_prorated like 'Yes' then isnull((product_total_revenue /nullif((((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2))/365), 0))/nullif(adj_fx_rate, 0), 0)
when is_prorated like 'Only Greater than 1 year' and ((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2)) >= 347 then isnull(nullif((((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2))/365), 0)/nullif(adj_fx_rate, 0), 0)
when is_prorated like 'Only Greater than 1 year' and ((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2)) < 347 then isnull(product_total_revenue/ nullif(adj_fx_rate, 0), 0)
when is_prorated like 'No' then isnull(product_total_revenue/ nullif(adj_fx_rate, 0), 0)
else 0 end as adj_annualized_rev,
case when is_prorated is null and 15 > ((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2)) then isnull(product_total_gp_local/ nullif(adj_fx_rate, 0), 0)
when is_prorated is null then isnull((product_total_gp_local /nullif((((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2))/365), 0))/nullif(adj_fx_rate, 0), 0)
when is_prorated like 'Yes' and 15 > ((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2)) then isnull(product_total_gp_local/ nullif(adj_fx_rate, 0), 0)
when is_prorated like 'Yes' then isnull((product_total_gp_local /nullif((((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2))/365), 0))/nullif(adj_fx_rate, 0), 0)
when is_prorated like 'Only Greater than 1 year' and ((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2)) >= 347 then isnull(nullif((((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2))/365), 0)/nullif(adj_fx_rate, 0), 0)
when is_prorated like 'Only Greater than 1 year' and ((cast(product_end_date as decimal) + 2) - (cast(product_start_date as decimal) + 2)) < 347 then isnull(product_total_gp_local/ nullif(adj_fx_rate, 0), 0)
when is_prorated like 'No' then isnull(product_total_gp_local/ nullif(adj_fx_rate, 0), 0)
else 0 end as adj_annualized_gp
from
(select
account_id, account_name, account_type, account_status, account_role, account_owner, market_segment, program_market_segment, business_industry, account_category, program_owner_role_name, program_owner, program_status,
program_name, program_number, program_effective_date, program_expiration_date, type_prd, product_category, product_name, product_status, product_id, product_type, product_subtype, product_start_date, product_end_date, implementation_date,
implementation_status, product_designation, quantity, currency, product_total_revenue, product_total_gp_local, gp_percentage, program_duration, total_trav, total_expats, total_expat_dependents, total_lives, TT_indicator, selling_office, region, bi_business_line,
prod_cat_1, prod_cat_2, prod_cat_3, current_status, prev_yr_status, FY1617_status, package_deal, is_insurer, is_broker, is_tmc, doh_percent, is_prorated, adj_fx_rate,
case when fx_rate is null then m.adj_conversionrate
else fx_rate end as fx_rate
from
(select
a.winis_id__c as account_id, a.name as account_name, a.type as account_type, a.status__c as account_status, account_owner_s_role__c as account_role, a.ownername__c as account_owner, a.market_segment__c as market_segment, b.market_segment__c as program_market_segment,
a.business_industry__c as business_industry, a.createddate as account_inception_date, a.category__c as account_category, b.program_owner_s_role__c as program_owner_role_name, b.owner_atlas__c as program_owner, b.status__c as program_status,
b.program_description__c as program_name, b.name as program_number, b.effective_date__c as program_effective_date, b.expiration_date__c as program_expiration_date, c.type__c as type_prd, c.product_category__c as product_category,
g.product_name as product_name, c.status__c as product_status, c.productcode11__c as product_id, c.producttype__c as product_type, c.productsubtype__c as product_subtype, c.startdate__c as product_start_date, c.enddate__c as product_end_date, c.implementation_date__c as implementation_date,
c.implementation_status__c as implementation_status, isnull(d.product_designation__c, 'N/A') as product_designation, d.quantity_required__c as quantity, c.currencyisocode as currency, isnull(c.product_total_revenue__C, 0) as product_total_revenue, isnull(c.product_total_gross_profit__c, 0) as product_total_gp_local,
d.grossprofitpercentage__c as gp_percentage, b.program_duration_days__c as program_duration, isnull(b.totaltraveller__c, 0) as total_trav, isnull(b.rollexpatriatescount__c, 0) as total_expats, isinsurer__C as is_insurer, isbroker__C as is_broker,
is_tmc__c as is_tmc, isnull(b.rollexpatriatedependentscount__c, 0) as total_expat_dependents, b.total_of_lives__c as total_lives, b.travel_tracker__c as TT_indicator, L.name as selling_office, L.region__c as region, g.bi_business_line, g.prod_cat_1, g.prod_cat_2, g.prod_cat_3,
case when getdate()-1 between c.startdate__c and c.enddate__c then 1 else 0 end as current_status, case when getdate()-366 between c.startdate__c and c.enddate__c then 1 else 0 end as prev_yr_status, g.doh_percent, g.is_prorated,
case when CAST('2017-06-30 00:00:00' as datetime) between c.startdate__c and c.enddate__c then 1 else 0 end as FY1617_status, isnull(i.grmp, 0) as package_deal, j.curr_conversion as fx_rate, k.adj_conversionrate as adj_fx_rate
from salesforcelink.salesforce.dbo.account a
left hash join salesforcelink.salesforce.dbo.program__c b on a.id = b.account__c
left hash join (select *
from salesforcelink.salesforce.dbo.programproduct__c a
where not exists
(select *
from tbl_progprod_dup b
where a.id = b.id)) c on b.id = c.programid__c
left hash join salesforcelink.salesforce.dbo.product2 d on d.id = c.productid__c
left hash join (select *
from vw_date
where fiscal_year = 'FY1718') e on e.full_date = c.startdate__c --CURRENT FISCAL YEAR CALCULATION--
left hash join (select *
from vw_date
where fiscal_year = 'FY1617') f on f.full_date = c.startdate__C --PREVIOUS FISCAL YEAR CALCULATION--
left hash join vw_product_master g on g.productcode = c.productcode11__c
left hash join salesforcelink.salesforce.dbo."user" h on h.id = b.ownerid
left hash join tbl_grmp_accnts i on i.account_id = a.winis_id__C
left join tbl_finance_fxrates j on j.curr_code = c.currencyisocode and year(c.startdate__c) = year(j.date) and month(c.startdate__c) = month(j.date)
left hash join (select latest_date, a.curr_code, b.curr_conversion as adj_conversionrate
from
(select distinct DATEADD(month, DATEDIFF(month, 0,getdate()), 0) as latest_date, curr_code
from tbl_finance_fxrates) a
left join tbl_finance_fxrates b on a.latest_date = b.date and a.curr_code = b.curr_code) k on k.curr_code = c.currencyisocode
left hash join salesforcelink.salesforce.dbo.sellingoffice__c L on b.sellingofficeservicecenter__c = L.id
where 1=1
and a.isdeleted = 0 --ACCOUNT TABLE--
and a.ownername__c not like 'Raj Pat%' --ACCOUNT TABLE--
and a.name not like '%test account for apps team%' --ACCOUNT TABLE--
and a.name not like '%test account%' --ACCOUNT TABLE--
and a.name not like '%(test)%' --ACCOUNT TABLE--
and a.name not like '% (test) %' --ACCOUNT TABLE--
and a.name not like '% test %' --ACCOUNT TABLE--
and a.name not like '% - test %' --ACCOUNT TABLE--
and a.name not like '%_test' --ACCOUNT TABLE--
and a.name not like '%ABC Company - test %' --ACCOUNT TABLE--
and a.name not like '%account test%' --ACCOUNT TABLE--
and a.name not like '%Test - Qantas CS Training%' --ACCOUNT TABLE--
and a.name not like '%test eLearning%' --ACCOUNT TABLE--
and a.type in ('client', 'partners', 'prospect') --ACCOUNT TABLE--
and b.market_segment__c <> 'individual' --PROGRAM TABLE--
and b.isdeleted = 0 --PROGRAM TABLE--
and c.isdeleted = 0 --PROGRAM/PRODUCT TABLE--
--and c.startdate__c = '2018-05-28 00:00:00.000'
and d.isdeleted = 0 --PRODUCT TABLE--
and g.bi_business_line = 'Assistance'
--and b.name = '005030A'
--and h.region__c <> 'Americas'
--and a.winis_id__c = '199753' --QUERY ENTRIES--
--and c.productcode11__c = '4011601-A'
--order by c.startdate__c
) a
left hash join (select first_date, a.curr_code, b.curr_conversion as adj_conversionrate
from
(select distinct min(date) as first_date, curr_code
from tbl_finance_fxrates
group by curr_code) a
left join tbl_finance_fxrates b on a.first_date = b.date and a.curr_code = b.curr_code) m on m.curr_code = a.currency
group by
account_id, account_name, account_type, account_status, account_role, account_owner, market_segment, program_market_segment, business_industry, account_category, program_owner_role_name, program_owner, program_status,
program_name, program_number, program_effective_date, program_expiration_date, type_prd, product_category, product_name, product_status, product_id, product_type, product_subtype, product_start_date, product_end_date, implementation_date,
implementation_status, product_designation, quantity, currency, product_total_revenue, product_total_gp_local, gp_percentage, program_duration, total_trav, total_expats, total_expat_dependents, total_lives, TT_indicator, selling_office, region, bi_business_line,
prod_cat_1, prod_cat_2, prod_cat_3, current_status, prev_yr_status, FY1617_status, package_deal, adj_fx_rate, fx_rate, is_insurer, is_broker, is_tmc, doh_percent, is_prorated, adj_conversionrate) a) a
group by account_id, account_name, product_id, selling_office, region, business_industry, market_segment, product_type, product_subtype, product_name, product_start_date, product_end_date, prod_cat_1, prod_cat_2, prod_cat_3, current_status, prev_yr_status,
is_insurer, is_broker, is_tmc, doh_percent, is_prorated) a
go
drop view vw_active_program_product
go
create view vw_active_program_product as
WITH Nums AS (
SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
Tally AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS N
FROM Nums AS A, Nums AS B
)
SELECT
T.*, DATEADD(month, T2.N, eomonth(T.[product_start_date])) AS list_dates --, DATEADD(day, -1, DATEADD(month, T2.N + 1, T.[startdate__C])) AS THE_END_DATE
FROM
vw_active_program_product_p1 AS T
CROSS APPLY (
SELECT TN.N
FROM Tally AS TN
WHERE TN.N <= DATEDIFF(month, eomonth(T.[product_start_date]), eomonth(T.[product_end_date]))
) AS T2
--ORDER BY T.account_Id, list_dates;;
August 29, 2018 at 7:23 am
Thank you for the follow up. Please see code.
The view creation is taking just under 3 min. It's not terrible but if there is a better way to write this im all ears.
I then use the view in the code you provided me with to augment which takes just under 40 seconds. Also, if there is a better way im all ears.
Thank you again for all your help. You rock!
-Alex
August 29, 2018 at 9:03 am
First, this is a huge view. I would highly recommend commenting the view so that people new to it can understand what is going on in the view, including the all the joins. Not sure why you are using HASH joins and I am not going to say you should or shouldn't at this time. I can also determine that you are using a linked server, which I assume is to a SalesForce system. That portion of the view really needs commenting as from a brief read I am not sure what is being pulled back from there. I am sure if I had more time to review the code I could probably figure out the jest of it.
At this point with out more details regarding the view, I don't have the time to really look at it. Most of us here are volunteers and do this as a means of giving back to the community.
For anyone else looking at the view I have reformatted it so it should be easier to read.
CREATE VIEW [vw_active_program_product_p1]
AS
SELECT
[account_id]
, [account_name]
, [product_id]
, [selling_office]
, [region]
, [business_industry]
, [market_segment]
, [product_type]
, [product_subtype]
, [product_name]
, [product_start_date]
, [product_end_date]
, [prod_cat_1]
, [prod_cat_2]
, [prod_cat_3]
, ISNULL([product_total_revenue_local], 0) AS [product_total_revenue_local]
, ISNULL([product_total_gp_local], 0) AS [product_total_gp_local]
, ISNULL([annualized_rev], 0) AS [annualized_rev]
, ISNULL([annualized_gp], 0) AS [annualized_gp]
, ISNULL([adj_annualized_rev], 0) AS [adj_annualized_rev]
, ISNULL([adj_annualized_gp], 0) AS [adj_annualized_gp]
, [is_insurer]
, [is_broker]
, [is_tmc]
, [doh_percent]
, [is_prorated]
, CASE
WHEN [current_status] > 0
THEN 1
ELSE 0
END AS [current_status]
, CASE
WHEN [prev_yr_status] > 0
THEN 1
ELSE 0
END AS [prev_yr_status]
FROM
(
SELECT
[account_id]
, [account_name]
, [product_id]
, [selling_office]
, [region]
, [business_industry]
, [market_segment]
, [product_type]
, [product_subtype]
, [product_name]
, [product_start_date]
, [product_end_date]
, [prod_cat_1]
, [prod_cat_2]
, [prod_cat_3]
, SUM([product_total_revenue_local]) AS [product_total_revenue_local]
, SUM([product_total_gp_local]) AS [product_total_gp_local]
, SUM([annualized_rev]) AS [annualized_rev]
, SUM([annualized_gp]) AS [annualized_gp]
, SUM([adj_annualized_rev]) AS [adj_annualized_rev]
, SUM([adj_annualized_gp]) AS [adj_annualized_gp]
, SUM([current_status]) AS [current_status]
, SUM([prev_yr_status]) AS [prev_yr_status]
, [is_insurer]
, [is_broker]
, [is_tmc]
, [doh_percent]
, [is_prorated]
FROM
(
SELECT
[account_id]
, [account_name]
, [product_id]
, [selling_office]
, [region]
, [business_industry]
, [market_segment]
, [product_type]
, [product_subtype]
, [product_name]
, [product_start_date] /*dateadd(day, 1, eomonth(product_start_date, -1)) as product_start_month, dateadd(day, 1, eomonth(product_end_date, -1)) as product_end_month*/
, [product_end_date]
, [product_total_revenue]
, [bi_business_line]
, [prod_cat_1]
, [prod_cat_2]
, [prod_cat_3]
, [current_status]
, [prev_yr_status]
, [package_deal]
, [is_insurer]
, [is_broker]
, [is_tmc]
, [doh_percent]
, [is_prorated]
, [product_total_revenue] AS [product_total_revenue_local]
, [product_total_gp_local]
, CASE
WHEN [is_prorated] IS NULL
AND 15 > ((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2))
THEN ISNULL([product_total_revenue] / NULLIF([fx_rate], 0), 0)
WHEN [is_prorated] IS NULL
THEN ISNULL(([product_total_revenue] / NULLIF((((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) / 365), 0)) / NULLIF([fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'Yes'
AND 15 > ((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2))
THEN ISNULL([product_total_revenue] / NULLIF([fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'Yes'
THEN ISNULL(([product_total_revenue] / NULLIF((((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) / 365), 0)) / NULLIF([fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'Only Greater than 1 year'
AND ((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) >= 347
THEN ISNULL(NULLIF((((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) / 365), 0) / NULLIF([fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'Only Greater than 1 year'
AND ((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) < 347
THEN ISNULL([product_total_revenue] / NULLIF([fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'No'
THEN ISNULL([product_total_revenue] / NULLIF([fx_rate], 0), 0)
ELSE 0
END AS [annualized_rev]
, CASE
WHEN [is_prorated] IS NULL
AND 15 > ((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2))
THEN ISNULL([product_total_gp_local] / NULLIF([fx_rate], 0), 0)
WHEN [is_prorated] IS NULL
THEN ISNULL(([product_total_gp_local] / NULLIF((((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) / 365), 0)) / NULLIF([fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'Yes'
AND 15 > ((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2))
THEN ISNULL([product_total_gp_local] / NULLIF([fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'Yes'
THEN ISNULL(([product_total_gp_local] / NULLIF((((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) / 365), 0)) / NULLIF([fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'Only Greater than 1 year'
AND ((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) >= 347
THEN ISNULL(NULLIF((((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) / 365), 0) / NULLIF([fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'Only Greater than 1 year'
AND ((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) < 347
THEN ISNULL([product_total_gp_local] / NULLIF([fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'No'
THEN ISNULL([product_total_gp_local] / NULLIF([fx_rate], 0), 0)
ELSE 0
END AS [annualized_gp]
, CASE
WHEN [is_prorated] IS NULL
AND 15 > ((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2))
THEN ISNULL([product_total_revenue] / NULLIF([adj_fx_rate], 0), 0)
WHEN [is_prorated] IS NULL
THEN ISNULL(([product_total_revenue] / NULLIF((((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) / 365), 0)) / NULLIF([adj_fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'Yes'
AND 15 > ((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2))
THEN ISNULL([product_total_revenue] / NULLIF([adj_fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'Yes'
THEN ISNULL(([product_total_revenue] / NULLIF((((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) / 365), 0)) / NULLIF([adj_fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'Only Greater than 1 year'
AND ((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) >= 347
THEN ISNULL(NULLIF((((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) / 365), 0) / NULLIF([adj_fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'Only Greater than 1 year'
AND ((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) < 347
THEN ISNULL([product_total_revenue] / NULLIF([adj_fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'No'
THEN ISNULL([product_total_revenue] / NULLIF([adj_fx_rate], 0), 0)
ELSE 0
END AS [adj_annualized_rev]
, CASE
WHEN [is_prorated] IS NULL
AND 15 > ((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2))
THEN ISNULL([product_total_gp_local] / NULLIF([adj_fx_rate], 0), 0)
WHEN [is_prorated] IS NULL
THEN ISNULL(([product_total_gp_local] / NULLIF((((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) / 365), 0)) / NULLIF([adj_fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'Yes'
AND 15 > ((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2))
THEN ISNULL([product_total_gp_local] / NULLIF([adj_fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'Yes'
THEN ISNULL(([product_total_gp_local] / NULLIF((((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) / 365), 0)) / NULLIF([adj_fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'Only Greater than 1 year'
AND ((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) >= 347
THEN ISNULL(NULLIF((((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) / 365), 0) / NULLIF([adj_fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'Only Greater than 1 year'
AND ((CAST([product_end_date] AS DECIMAL) + 2) - (CAST([product_start_date] AS DECIMAL) + 2)) < 347
THEN ISNULL([product_total_gp_local] / NULLIF([adj_fx_rate], 0), 0)
WHEN [is_prorated] LIKE 'No'
THEN ISNULL([product_total_gp_local] / NULLIF([adj_fx_rate], 0), 0)
ELSE 0
END AS [adj_annualized_gp]
FROM
(
SELECT
[account_id]
, [account_name]
, [account_type]
, [account_status]
, [account_role]
, [account_owner]
, [market_segment]
, [program_market_segment]
, [business_industry]
, [account_category]
, [program_owner_role_name]
, [program_owner]
, [program_status]
, [program_name]
, [program_number]
, [program_effective_date]
, [program_expiration_date]
, [type_prd]
, [product_category]
, [product_name]
, [product_status]
, [product_id]
, [product_type]
, [product_subtype]
, [product_start_date]
, [product_end_date]
, [implementation_date]
, [implementation_status]
, [product_designation]
, [quantity]
, [currency]
, [product_total_revenue]
, [product_total_gp_local]
, [gp_percentage]
, [program_duration]
, [total_trav]
, [total_expats]
, [total_expat_dependents]
, [total_lives]
, [TT_indicator]
, [selling_office]
, [region]
, [bi_business_line]
, [prod_cat_1]
, [prod_cat_2]
, [prod_cat_3]
, [current_status]
, [prev_yr_status]
, [FY1617_status]
, [package_deal]
, [is_insurer]
, [is_broker]
, [is_tmc]
, [doh_percent]
, [is_prorated]
, [adj_fx_rate]
, CASE
WHEN [fx_rate] IS NULL
THEN [m].[adj_conversionrate]
ELSE [fx_rate]
END AS [fx_rate]
FROM
(
SELECT
[a].[winis_id__c] AS [account_id]
, [a].[name] AS [account_name]
, [a].[type] AS [account_type]
, [a].[status__c] AS [account_status]
, [account_owner_s_role__c] AS [account_role]
, [a].[ownername__c] AS [account_owner]
, [a].[market_segment__c] AS [market_segment]
, .[market_segment__c] AS [program_market_segment]
, [a].[business_industry__c] AS [business_industry]
, [a].[createddate] AS [account_inception_date]
, [a].[category__c] AS [account_category]
, .[program_owner_s_role__c] AS [program_owner_role_name]
, .[owner_atlas__c] AS [program_owner]
, .[status__c] AS [program_status]
, .[program_description__c] AS [program_name]
, .[name] AS [program_number]
, .[effective_date__c] AS [program_effective_date]
, .[expiration_date__c] AS [program_expiration_date]
, [c].[type__c] AS [type_prd]
, [c].[product_category__c] AS [product_category]
, [g].[product_name] AS [product_name]
, [c].[status__c] AS [product_status]
, [c].[productcode11__c] AS [product_id]
, [c].[producttype__c] AS [product_type]
, [c].[productsubtype__c] AS [product_subtype]
, [c].[startdate__c] AS [product_start_date]
, [c].[enddate__c] AS [product_end_date]
, [c].[implementation_date__c] AS [implementation_date]
, [c].[implementation_status__c] AS [implementation_status]
, ISNULL([d].[product_designation__c], 'N/A') AS [product_designation]
, [d].[quantity_required__c] AS [quantity]
, [c].[currencyisocode] AS [currency]
, ISNULL([c].[product_total_revenue__C], 0) AS [product_total_revenue]
, ISNULL([c].[product_total_gross_profit__c], 0) AS [product_total_gp_local]
, [d].[grossprofitpercentage__c] AS [gp_percentage]
, .[program_duration_days__c] AS [program_duration]
, ISNULL(.[totaltraveller__c], 0) AS [total_trav]
, ISNULL(.[rollexpatriatescount__c], 0) AS [total_expats]
, [isinsurer__C] AS [is_insurer]
, [isbroker__C] AS [is_broker]
, [is_tmc__c] AS [is_tmc]
, ISNULL(.[rollexpatriatedependentscount__c], 0) AS [total_expat_dependents]
, .[total_of_lives__c] AS [total_lives]
, .[travel_tracker__c] AS [TT_indicator]
, [L].[name] AS [selling_office]
, [L].[region__c] AS [region]
, [g].[bi_business_line]
, [g].[prod_cat_1]
, [g].[prod_cat_2]
, [g].[prod_cat_3]
, CASE
WHEN GETDATE() - 1 BETWEEN [c].[startdate__c]
AND [c].[enddate__c]
THEN 1
ELSE 0
END AS [current_status]
, CASE
WHEN GETDATE() - 366 BETWEEN [c].[startdate__c]
AND [c].[enddate__c]
THEN 1
ELSE 0
END AS [prev_yr_status]
, [g].[doh_percent]
, [g].[is_prorated]
, CASE
WHEN CAST('2017-06-30 00:00:00' AS DATETIME) BETWEEN [c].[startdate__c]
AND [c].[enddate__c]
THEN 1
ELSE 0
END AS [FY1617_status]
, ISNULL(.[grmp], 0) AS [package_deal]
, [j].[curr_conversion] AS [fx_rate]
, [k].[adj_conversionrate] AS [adj_fx_rate]
FROM
[salesforcelink].[salesforce].[dbo].[account] [a]
LEFT HASH JOIN [salesforcelink].[salesforce].[dbo].[program__c]
ON [a].[id] = .[account__c]
LEFT HASH JOIN
(
SELECT
*
FROM
[salesforcelink].[salesforce].[dbo].[programproduct__c] [a]
WHERE
NOT EXISTS
(
SELECT
*
FROM
[tbl_progprod_dup]
WHERE
[a].[id] = .[id]
)
) [c]
ON .[id] = [c].[programid__c]
LEFT HASH JOIN [salesforcelink].[salesforce].[dbo].[product2] [d]
ON [d].[id] = [c].[productid__c]
LEFT HASH JOIN
(
SELECT
*
FROM
[vw_date]
WHERE
[fiscal_year] = 'FY1718'
) [e]
ON [e].[full_date] = [c].[startdate__c] --CURRENT FISCAL YEAR CALCULATION--
LEFT HASH JOIN
(
SELECT
*
FROM
[vw_date]
WHERE
[fiscal_year] = 'FY1617'
) [f]
ON [f].[full_date] = [c].[startdate__C] --PREVIOUS FISCAL YEAR CALCULATION--
LEFT HASH JOIN [vw_product_master] [g]
ON [g].[productcode] = [c].[productcode11__c]
LEFT HASH JOIN [salesforcelink].[salesforce].[dbo]."user" [h]
ON [h].[id] = .[ownerid]
LEFT HASH JOIN [tbl_grmp_accnts]
ON .[account_id] = [a].[winis_id__C]
LEFT JOIN [tbl_finance_fxrates] [j]
ON [j].[curr_code] = [c].[currencyisocode]
AND YEAR([c].[startdate__c]) = YEAR([j].[date])
AND MONTH([c].[startdate__c]) = MONTH([j].[date])
LEFT HASH JOIN
(
SELECT
[latest_date]
, [a].[curr_code]
, .[curr_conversion] AS [adj_conversionrate]
FROM
(
SELECT DISTINCT
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS [latest_date]
, [curr_code]
FROM
[tbl_finance_fxrates]
) [a]
LEFT JOIN [tbl_finance_fxrates]
ON [a].[latest_date] = .[date]
AND [a].[curr_code] = .[curr_code]
) [k]
ON [k].[curr_code] = [c].[currencyisocode]
LEFT HASH JOIN [salesforcelink].[salesforce].[dbo].[sellingoffice__c] [L]
ON .[sellingofficeservicecenter__c] = [L].[id]
WHERE
1 = 1
AND [a].[isdeleted] = 0 --ACCOUNT TABLE--
AND [a].[ownername__c] NOT LIKE 'Raj Pat%' --ACCOUNT TABLE--
AND [a].[name] NOT LIKE '%test account for apps team%' --ACCOUNT TABLE--
AND [a].[name] NOT LIKE '%test account%' --ACCOUNT TABLE--
AND [a].[name] NOT LIKE '%(test)%' --ACCOUNT TABLE--
AND [a].[name] NOT LIKE '% (test) %' --ACCOUNT TABLE--
AND [a].[name] NOT LIKE '% test %' --ACCOUNT TABLE--
AND [a].[name] NOT LIKE '% - test %' --ACCOUNT TABLE--
AND [a].[name] NOT LIKE '%_test' --ACCOUNT TABLE--
AND [a].[name] NOT LIKE '%ABC Company - test %' --ACCOUNT TABLE--
AND [a].[name] NOT LIKE '%account test%' --ACCOUNT TABLE--
AND [a].[name] NOT LIKE '%Test - Qantas CS Training%' --ACCOUNT TABLE--
AND [a].[name] NOT LIKE '%test eLearning%' --ACCOUNT TABLE--
AND [a].[type] IN (
'client'
, 'partners'
, 'prospect'
) --ACCOUNT TABLE--
AND .[market_segment__c] <> 'individual' --PROGRAM TABLE--
AND .[isdeleted] = 0 --PROGRAM TABLE--
AND [c].[isdeleted] = 0 --PROGRAM/PRODUCT TABLE--
--and c.startdate__c = '2018-05-28 00:00:00.000'
AND [d].[isdeleted] = 0 --PRODUCT TABLE--
AND [g].[bi_business_line] = 'Assistance'
--and b.name = '005030A'
--and h.region__c <> 'Americas'
--and a.winis_id__c = '199753' --QUERY ENTRIES--
--and c.productcode11__c = '4011601-A'
--order by c.startdate__c
) [a]
LEFT HASH JOIN
(
SELECT
[first_date]
, [a].[curr_code]
, .[curr_conversion] AS [adj_conversionrate]
FROM
(
SELECT DISTINCT
MIN([date]) AS [first_date]
, [curr_code]
FROM
[tbl_finance_fxrates]
GROUP BY
[curr_code]
) [a]
LEFT JOIN [tbl_finance_fxrates]
ON [a].[first_date] = .[date]
AND [a].[curr_code] = .[curr_code]
) [m]
ON [m].[curr_code] = [a].[currency]
GROUP BY
[account_id]
, [account_name]
, [account_type]
, [account_status]
, [account_role]
, [account_owner]
, [market_segment]
, [program_market_segment]
, [business_industry]
, [account_category]
, [program_owner_role_name]
, [program_owner]
, [program_status]
, [program_name]
, [program_number]
, [program_effective_date]
, [program_expiration_date]
, [type_prd]
, [product_category]
, [product_name]
, [product_status]
, [product_id]
, [product_type]
, [product_subtype]
, [product_start_date]
, [product_end_date]
, [implementation_date]
, [implementation_status]
, [product_designation]
, [quantity]
, [currency]
, [product_total_revenue]
, [product_total_gp_local]
, [gp_percentage]
, [program_duration]
, [total_trav]
, [total_expats]
, [total_expat_dependents]
, [total_lives]
, [TT_indicator]
, [selling_office]
, [region]
, [bi_business_line]
, [prod_cat_1]
, [prod_cat_2]
, [prod_cat_3]
, [current_status]
, [prev_yr_status]
, [FY1617_status]
, [package_deal]
, [adj_fx_rate]
, [fx_rate]
, [is_insurer]
, [is_broker]
, [is_tmc]
, [doh_percent]
, [is_prorated]
, [adj_conversionrate]
) [a]
) [a]
GROUP BY
[account_id]
, [account_name]
, [product_id]
, [selling_office]
, [region]
, [business_industry]
, [market_segment]
, [product_type]
, [product_subtype]
, [product_name]
, [product_start_date]
, [product_end_date]
, [prod_cat_1]
, [prod_cat_2]
, [prod_cat_3]
, [current_status]
, [prev_yr_status]
, [is_insurer]
, [is_broker]
, [is_tmc]
, [doh_percent]
, [is_prorated]
) [a];
GO
August 29, 2018 at 9:17 am
I appreciate the feedback. Def have to comment it.
The final view is powering a BI tool which is scheduled for an hourly refresh. The view allows the data to flow through ready for consumption without my intervention. Originally built the tool inside powerbi (bi tool used) but the size and calculations slowed down the tool to a point where it crashed routinely. Took a different approach and decide to rebuild in SQL. In essence, pulling information from salesforce repository and standardizing using dimension tables (short explanation)
I thank you again, you really helped me out on this one. I was stuck for a while.
August 30, 2018 at 11:34 am
hi,
I was hoping you'd be able to help me to augment the code so that the date is always the last day of the month for each individual month.
current output - 5/30/2018
desired output - 5/31/2018
Thank you in advance.
Regards,
-Alex
August 30, 2018 at 11:54 am
The EOMONTH function can give you the last day of the month from any given date.
August 30, 2018 at 12:07 pm
alexander.lummer - Thursday, August 30, 2018 11:34 AMhi,
I was hoping you'd be able to help me to augment the code so that the date is always the last day of the month for each individual month.
current output - 5/30/2018
desired output - 5/31/2018Thank you in advance.
Regards,
-Alex
Not sure what you are talking about, at least with my code. Doing a visual check everything looks good based on the sample data in the #temp table.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply