[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

What SQL statement can I use to only get results after 10/10/2007? Where would I place it in my query?

What SQL statement can I use to only get results after 10/10/2007?  Where would I place it in my query?  I am a newbie and would appreciate the help.  When I ran this yesterday it came back with millions of rows.  Please help ASAP!


create table #promo (ctMarketingSKU varchar (50));

Insert into #promo  (ctMarketingSKU) values ('MXPAFLXDTSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPAFLXPGSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPALPSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPAOPNSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPASAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPEFLXDTSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPEFLXPGSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPELPSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPEOPNSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPESAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPSFLXDTSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPSFLXPGSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPSLPSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPSOPNSAREC');
Insert into #promo  (ctMarketingSKU) values ('MXPSSAREC');
Insert into #promo  (ctMarketingSKU) values ('SUBMXPAFLXDTSAREC')
Insert into #promo  (ctMarketingSKU) values ('SUBMXPAFLXPGSAREC')
Insert into #promo  (ctMarketingSKU) values ('SUBMXPALPSAREC')
Insert into #promo  (ctMarketingSKU) values ('SUBMXPAOPNSAREC')
Insert into #promo  (ctMarketingSKU) values ('SUBMXPASAREC')
Insert into #promo  (ctMarketingSKU) values ('SUBMXPEFLXDTSAREC')
Insert into #promo  (ctMarketingSKU) values ('SUBMXPEFLXPGSAREC')
Insert into #promo  (ctMarketingSKU) values ('SUBMXPELPSAREC')
Insert into #promo  (ctMarketingSKU) values ('SUBMXPEOPNSAREC')
Insert into #promo  (ctMarketingSKU) values ('SUBMXPESAREC')
Insert into #promo  (ctMarketingSKU) values ('SUBMXPSFLXDTSAREC')
Insert into #promo  (ctMarketingSKU) values ('SUBMXPSFLXPGSAREC')
Insert into #promo  (ctMarketingSKU) values ('SUBMXPSLPSAREC')
Insert into #promo  (ctMarketingSKU) values ('SUBMXPSOPNSAREC')
Insert into #promo  (ctMarketingSKU) values ('SUBMXPSSAREC')

select
      case
            when p.swdatecreated is null then 'Not Activated'
            else 'Activated'
      end as status
      , #promo.ctMarketingSKU
      , p.swdatecreated as ActivationDate
      , c.ctorgid as ORGid
      , c.swname as Company
      , pe.swfirstname as ContactFirst
      , pe.swlastname as ContactLast
      , pe.swjobtitle as Title
      , pe.swextemailaddress
      , c.swmainphonearea as AreaCode
      , c.swmainphone as Phone#
      , a.swcity as City
      , a.swstate as State
      -- aditional fields
      , price.ctMarketingSKU
      , price.ctModel
      , price.ctSKUType
      , price.ctOpenPrice
      , price.ctChannelPrice
      , price.ctActive
      , price.ctUserCount
      , price.ctProductFamily
      , price.ctRevenueGroup
      , price.ctLicenseType
      , price.ctSubsAdvantage
      , price.swDateCreated
from sw_inst_product p (nolock)
     -- additional tables start
left join sw_prod_release r (nolock)
      on r.swprodreleaseid = p.swprodreleaseid
left join ct_Prod_release_skus sku (nolock)
      on sku.swprodreleaseid = r.swprodreleaseid
left join ct_sap_price price (nolock)
      on price.ctMarketingSKU = sku.ctMarketingSKU
left join #promo (nolock)
 on #promo.ctMarketingSKU = sku.ctMarketingSKU
-- additional tables end
inner join sw_customer c (nolock)
      on p.swcustomerid = c.swcustomerid
left join sw_address a (nolock)
      on c.swcustomerid = a.swobjectid
left join ct_inst_prod_subscription s (nolock)
      on p.swinstprodid = s.swinstprodid
left join sw_person pe (nolock)
      on s.swpersonid = pe.swpersonid
where
      a.swtype is null or a.swtype = 'Physical Location'
order by #promo.ctMarketingSKU

--drop table #promo
0
iquagmire
Asked:
iquagmire
1 Solution
 
Sham HaqueSenior SAP CRM ConsultantCommented:
change the WHERE clause to this:

where
      a.swtype is null or a.swtype = 'Physical Location'
AND p.swdatecreated > '10/10/2007'
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now