?
Solved

Need help adding one field to this SQL Server query ASAP!  I also need the field to equal to "1".

Posted on 2007-10-12
6
Medium Priority
?
216 Views
Last Modified: 2012-06-21
I finally got my query down right.  But I do need one more help.  I need to add swRegionID (SWMID, Null) to my query.  The table is SW_CUSTOMER which is already in the query below.

I also need it to equal to "1".

Can you insert it in here ASAP?  Thank you!  I am still a novice but need to turn in this data today!

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
inner join sw_prod_release r (nolock)
      on r.swprodreleaseid = p.swprodreleaseid
inner join ct_Prod_release_skus sku (nolock)
      on sku.swprodreleaseid = r.swprodreleaseid
left join ct_sap_price price (nolock)
      on price.ctMarketingSKU = sku.ctMarketingSKU
inner 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
inner join sw_person pe (nolock)
      on s.swpersonid = pe.swpersonid
where
      a.swtype is null or a.swtype = 'Physical Location'
AND p.swdatecreated > '9/30/2007'
order by #promo.ctMarketingSKU

--drop table #promo
0
Comment
Question by:iquagmire
  • 3
  • 3
6 Comments
 
LVL 5

Expert Comment

by:Crag
ID: 20066079
I'm a little confused. By add you mean as a where clause?
0
 

Author Comment

by:iquagmire
ID: 20066250
Yes.  So I can limit the amount of records coming in to just that particular region.
0
 
LVL 5

Accepted Solution

by:
Crag earned 2000 total points
ID: 20066292
Change the

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

to

where
      a.swtype is null or a.swtype = 'Physical Location'
and c.swRegionID = 1
AND p.swdatecreated > '9/30/2007'
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:iquagmire
ID: 20066378
Do I need to add swRegionID in the code itself, other than in the Where clause?  Or will this run just like this?
0
 
LVL 5

Expert Comment

by:Crag
ID: 20066392
It'll run like this.
0
 

Author Comment

by:iquagmire
ID: 20086758
Thanks.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question