June 9, 2015 at 3:00 pm
Hi,
Sample data to play with
declare @client table(clientid int primary key,clientname varchar(30));
declare @company table(compid int primary key,clientid int,compname varchar(30));
declare @compoffer table(id int identity(1,1),compid int,startdate datetime, endadate datetime, compoffermessage varchar(500));
declare @clientoffer table(id int identity(1,1),clientid int,startdate datetime, endadate datetime, clientoffermessage varchar(500));
insert into @client(clientid,clientname)
select 1,'Microsoft' union all select 2,'Sun Microsystems' union all select 2,'Oracle' ;
insert into @company(compid,clientid,compname)
select 100,1,'Nokia' union all select 200,2,'igate' union all select 300,3,'Peoplesoft' ;
insert into @compoffer(compid,startdate,endadate,compoffermessage)
select 100,'2015-05-30','2015-07-30','Sample offer1' union all
select 200,'2015-03-30','2015-05-30','Sample offer2'
Basically i need to get the list of companies where the offer expired or company doesn't have offer at all. So if the offer doesn't available for the company level then i need to
to check on the client level where offer available for the client belongs to the company.
in the above sample data, i need the result as 200,300
200 - Offer expired
300 - doesn't have valid offer in company level or client level.
Any sample query to achieve this please. I tried with multiple separate queries with union but i believe they should some other way to avoid multiple queries.
June 9, 2015 at 3:05 pm
Just to clarify, do you just need the company information for companies meeting one of those criteria, or do you want the reason (expired offer, no offer) included in the output?
Cheers!
EDIT: The INSERT into @client will throw an error because the data to be inserted violates the primary key constraint (two clients with clientid=2).
Also, you have defined a fourth table,@clientoffer, not used at all in the example. @client will be populated when the primary key violation is fixed (by changing Oracle to clientid=3), but it doesn't seem to play a role in the desired results just based on what you've shown so far. How do those tables play into this?
June 9, 2015 at 3:17 pm
Try this:
declare @client table(clientid int primary key,clientname varchar(30));
declare @company table(compid int primary key,clientid int,compname varchar(30));
declare @compoffer table(id int identity(1,1),compid int,startdate datetime, endadate datetime, compoffermessage varchar(500));
declare @clientoffer table(id int identity(1,1),clientid int,startdate datetime, endadate datetime, clientoffermessage varchar(500));
declare @TD AS date = GETDATE();
insert into @client(clientid,clientname)
select 1,'Microsoft' union all select 2,'Sun Microsystems' union all select 3,'Oracle' ;
insert into @company(compid,clientid,compname)
select 100,1,'Nokia' union all select 200,2,'igate' union all select 300,3,'Peoplesoft' ;
insert into @compoffer(compid,startdate,endadate,compoffermessage)
select 100,'2015-05-30','2015-07-30','Sample offer1' union all
select 200,'2015-03-30','2015-05-30','Sample offer2'
SELECT CO.compid,
CASE
WHEN @TD NOT BETWEEN CF.startdate AND CF.endadate THEN 'Offer Expired'
ELSE 'doesn''t have valid offer in company level or client level.'
END AS Offer_Status
FROM @client AS CL
LEFT OUTER JOIN @company AS CO
ON CL.clientid = CO.clientid
LEFT OUTER JOIN @compoffer AS CF
ON CO.compid = CF.compid
WHERE CF.compid IS NULL
OR @TD NOT BETWEEN CF.startdate AND CF.endadate;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 9, 2015 at 3:53 pm
I added some functionality that you didn't ask for. I assumed that your table may have multiple offers for each company including future and multiple expired offers. I set it to ignore future offers (in the CTE) and picked the most recent offer of the remaining (based on the end date and then start date).
The test for the row number is in the ON clause rather than the WHERE clause, because it will retain any companies that don't have offers.
;
WITH ordered_offers AS (
SELECT *
, ROW_NUMBER() OVER(PARTITION BY compid ORDER BY endadate DESC, startdate DESC) AS rn
FROM @compoffer
WHERE startdate < GETDATE()
)
SELECT c.compid,
CASE WHEN o.endadate IS NULL THEN 'doesn''t have valid offer in company level or client level.'
WHEN o.endadate < GETDATE() THEN 'Offer expired'
ELSE o.compoffermessage
END AS compoffermessage
FROM @company AS c
LEFT OUTER JOIN ordered_offers AS o
ON c.compid = o.compid
AND o.rn = 1
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 9, 2015 at 7:07 pm
Hi Drew/Sumngson,
thanks for your response. once condition is missed on your query. If the offer is not available at company level then we need to check on client level based on client of company.
Table : @clientoffer
Also one more issue that on companyoffer table . ie if same company have valid and invalid offer then that company should not be listed on the output as it has valid offer.
sample record:
select 200,'2015-03-30','2015-07-30','Sample offer2'
in this case 200 should be listed on output. Wehn i check with my real data right now it's listing.
Any suggestion please
June 10, 2015 at 7:56 am
KGJ-Dev (6/9/2015)
Hi Drew/Sumngson,thanks for your response. once condition is missed on your query. If the offer is not available at company level then we need to check on client level based on client of company.
Table : @clientoffer
Also one more issue that on companyoffer table . ie if same company have valid and invalid offer then that company should not be listed on the output as it has valid offer.
sample record:
select 200,'2015-03-30','2015-07-30','Sample offer2'in this case 200 should be listed on output. Wehn i check with my real data right now it's listing.
Any suggestion please
I don't understand what you mean when you say an "offer" can be at the client level vs. the company level. What structure in your data indicates this difference? What data conditions would indicate this scenario is in place? Your data only ties companies to offers, so I fail to see how the client has any involvement.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 10, 2015 at 10:52 am
KGJ-Dev (6/9/2015)
Hi Drew/Sumngson,thanks for your response. once condition is missed on your query. If the offer is not available at company level then we need to check on client level based on client of company.
Table : @clientoffer
You didn't provide any data for this table. That's why I didn't include it in my code. You have to remember, we can only work with what you give us. If your data is incomplete, then any solution we provide is also likely to be incomplete. It also helps if you provide the intended results as well as sample data, because it gives something for people to compare their results to instead of trying to guess if they match the results that you want.
Also one more issue that on companyoffer table . ie if same company have valid and invalid offer then that company should not be listed on the output as it has valid offer.
sample record:
select 200,'2015-03-30','2015-07-30','Sample offer2'in this case 200 should be listed on output. Wehn i check with my real data right now it's listing.
Any suggestion please
You're contradicting yourself. First you say it shouldn't be listed then you say it should be listed. In any case, it should be easy enough for you to figure out how to add a WHERE clause to my query to return only companies with invalid offers.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 10, 2015 at 11:33 am
Thank you guys. I figured out and adding a condition to drew's code worked for me.
much appreciated for your time.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply