Code to list dates end of month dates between specific start and end date ranges

• alexander.lummer

  Old Hand

  Points: 387

  More actions

  August 28, 2018 at 12:15 pm

  #362222

  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.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  August 28, 2018 at 1:07 pm

  #2003703

  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)

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  August 28, 2018 at 1:11 pm

  #2003704

  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)

• Mike01

  SSChampion

  Points: 11393

  More actions

  August 28, 2018 at 1:24 pm

  #2003710

  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/

• alexander.lummer

  Old Hand

  Points: 387

  More actions

  August 28, 2018 at 1:27 pm

  #2003712

  sgmunson - Tuesday, August 28, 2018 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;

  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.

• alexander.lummer

  Old Hand

  Points: 387

  More actions

  August 28, 2018 at 1:43 pm

  #2003719

  alexander.lummer - Tuesday, August 28, 2018 1:27 PM

  sgmunson - Tuesday, August 28, 2018 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;

  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!!!

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  August 28, 2018 at 3:49 pm

  #2003738

  alexander.lummer - Tuesday, August 28, 2018 1:43 PM

  alexander.lummer - Tuesday, August 28, 2018 1:27 PM

  sgmunson - Tuesday, August 28, 2018 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;

  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

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 28, 2018 at 5:39 pm

  #2003747

  Jeff Moden - Tuesday, August 28, 2018 3:49 PM

  alexander.lummer - Tuesday, August 28, 2018 1:43 PM

  alexander.lummer - Tuesday, August 28, 2018 1:27 PM

  sgmunson - Tuesday, August 28, 2018 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;

  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.

• alexander.lummer

  Old Hand

  Points: 387

  More actions

  August 29, 2018 at 7:20 am

  #2003796

  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;;

• alexander.lummer

  Old Hand

  Points: 387

  More actions

  August 29, 2018 at 7:23 am

  #2003797

  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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 29, 2018 at 9:03 am

  #2003818

  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


• alexander.lummer

  Old Hand

  Points: 387

  More actions

  August 29, 2018 at 9:17 am

  #2003822

  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.

• alexander.lummer

  Old Hand

  Points: 387

  More actions

  August 30, 2018 at 11:34 am

  #2004057

  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

• andycadley

  SSCertifiable

  Points: 5296

  More actions

  August 30, 2018 at 11:54 am

  #2004064

  The EOMONTH function can give you the last day of the month from any given date.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 30, 2018 at 12:07 pm

  #2004067

  alexander.lummer - Thursday, August 30, 2018 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

  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.