June 29, 2015 at 8:28 am
SELECT
SUM(((CASE WHEN
o.date>= a.activity_date, other filter condition, other filter condition
THEN
(select coalesce(d.balance,d2.balance) from drawtable d where coalesce(d.date, d2.date) < a.activity_date order by d.date desc limit 1) - ( select coalesce(d.balance, d2.balance) from drawtable d where coalesce(d.date, d2.date) = interval 'current date'
else end ))
from
emailtable a
LEFT JOIN opportunity o
left join drawtable d
left join drawtable d2
etc
The tricky part is I'm joining that same table twice.....would this be better in a max/min case when statement? I'm totally lost here.
June 29, 2015 at 9:22 am
If you're lost, imagine us that only have a piece of code.
Why are you joining the table twice and then using it in subqueries within the CASE?
Can you post DDL with sample data and expected results? Here's an article on how to properly get help http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
June 29, 2015 at 10:36 am
Hi , the reason I'm joining twice is because I need to account that opportunities can come from two different joins.
I just don't know how to write the syntax properly...the select statements below would ultimately give me waht I want, but I'm not sure how to adapt it to syntax that I can use without errors. There isn't output because I can't get it to run.
SELECT
select s.balance from reportingstatus_history S where s.date < a.activity_date order by s.date desc limit 1) - ( select s.balance from reportingstatus_history S where s.date = interval 'current date'
FROM
reporting.os_marketo_activities a
LEFT JOIN reporting.os_marketo_leads z ON z.id = a.lead_id
LEFT JOIN reporting.os_marketo_activity_mapping b ON b.activity_type_id = a.activity_type_id
LEFT JOIN reporting.os_marketo_activity_attributes d ON d.id = a.id
LEFT JOIN sf_contact c ON c.id = z.sfdc_contact_id
LEFT JOIN sf_lead l ON l.id = z.sfdc_lead_id
LEFT JOIN sf_opportunity o ON o.accountid = c.accountid
LEFT JOIN sf_opportunity o2 ON o2.id = l.convertedopportunityid
LEFT JOIN reportingstatus_history S ON s.loan_id = o.loan_id__c
LEFT JOIN reportingstatus_history s2 ON S2.loan_id = O2.LOAN_ID__C
SO IN THE END, I JUST WANT THE DIFFERENCE BETWEEN THE S AND S2.BALANCE FIELDS with the date field constraints.
I'll also have more case whens in the same query and other metrics..but I can't figure this portion out.
June 29, 2015 at 10:48 am
Please post what I asked you to in the previous post. I can't figure out what you want. I'm sure that you have it very clear, but you might be leading us in a wrong way and a better solution is available.
June 29, 2015 at 11:06 am
The action this whole query revolves around is the activity date (a.activity_date). a.activity_date is the date of an email send.
I'm trying to determine the dollar amount of activity triggered by this email send.
The s.date field has corresponding values (dollar amounts for each customer within that table) and will give the dollar amount drawn out of an account. So basically, I want the closest coalesce(s.date, s2.date) that comes before the activity date and then it's corresponding dollar value.
So to illustrate that further I could write
case when MAX(coalesce(s.date, s2.date) < a.activity date then s.balance)
Then I want to subtract the above amount the date AFTER the a.activity_date that is the most recent. In a perfect world, the most recent date would cut off after 2 weeks elapsed and take that date (interval function possibly?)
Now I would love to pull results but since I can't figure out the syntax let's just say
case when MAX(coalesce(s.date, s2.date) < a.activity date then s.balance else end) --let's assume this results in 30
30 - (case when coalesce(s.date, s2.date) - a.activity date > interval 'one day' then s.balance else end) --let's assume this results in 35
30 - 35 (now that i write this i realize we'll need to switch the order but i hope this helps)
June 29, 2015 at 11:42 am
dandenise316 (6/29/2015)
The action this whole query revolves around is the activity date (a.activity_date). a.activity_date is the date of an email send.I'm trying to determine the dollar amount of activity triggered by this email send.
The s.date field has corresponding values (dollar amounts for each customer within that table) and will give the dollar amount drawn out of an account. So basically, I want the closest coalesce(s.date, s2.date) that comes before the activity date and then it's corresponding dollar value.
So to illustrate that further I could write
case when MAX(coalesce(s.date, s2.date) < a.activity date then s.balance)
Then I want to subtract the above amount the date AFTER the a.activity_date that is the most recent. In a perfect world, the most recent date would cut off after 2 weeks elapsed and take that date (interval function possibly?)
Now I would love to pull results but since I can't figure out the syntax let's just say
case when MAX(coalesce(s.date, s2.date) < a.activity date then s.balance else end) --let's assume this results in 30
30 - (case when coalesce(s.date, s2.date) - a.activity date > interval 'one day' then s.balance else end) --let's assume this results in 35
30 - 35 (now that i write this i realize we'll need to switch the order but i hope this helps)
If you post the DDL (CREATE TABLE statement) for the table(s) involved, some sample data (as INSERT INTO statements) for the tables involved that is representative of your problem domain, and the expected results based on the sample data you provide I am sure we can help you with your problem.
Our problem is that we can't see what you see. You need to help us help you with the information above.
June 29, 2015 at 12:10 pm
I converted to CSV and I threw in a mock example since I'm still unclear how to write the syntax.
DROP TABLE mytable;
CREATE TABLE mytable(
FIELD1 VARCHAR(33) NOT NULL PRIMARY KEY
, FIELD2 VARCHAR(27) NOT NULL
, FIELD3 VARCHAR(6) NOT NULL
, FIELD4 VARCHAR(16) NOT NULL
, FIELD5 VARCHAR(5) NOT NULL
, FIELD6 VARCHAR(6) NOT NULL
, FIELD7 VARCHAR(7) NOT NULL
, FIELD8 VARCHAR(2) NOT NULL
, FIELD9 VARCHAR(12) NOT NULL
, FIELD10 VARCHAR(10) NOT NULL
);
INSERT INTO mytable(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6,FIELD7,FIELD8,FIELD9,FIELD10) VALUES ('primary_attribute_value','date','emails','emails_delivered','opens','iphone','android','pc','unsubscribes','sum');
INSERT INTO mytable(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6,FIELD7,FIELD8,FIELD9,FIELD10) VALUES ('6/23 CLI - Yodlee.Email','2015-06-22T00:00:00.0000000','147','144','67','19','9','55','0','3294519.00');
INSERT INTO mytable(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6,FIELD7,FIELD8,FIELD9,FIELD10) VALUES ('LOC Rate Decrease June 2015.Email','2015-06-22T00:00:00.0000000','112','109','52','27','7','10','0','2973675.00');
June 29, 2015 at 12:24 pm
That makes absolutely no sense.
How does that reflect your data? Your problem is with the relation between 2 tables and you post only one without significant data types.
June 29, 2015 at 12:41 pm
DROP TABLE mytable;
CREATE TABLE mytable(
primary_attribute_value VARCHAR(33) NOT NULL PRIMARY KEY
, date VARCHAR(27) NOT NULL
, emails INTEGER(3) NOT NULL
, emails_delivered INTEGER(3) NOT NULL
, delivery_rate NUMERIC(6,4) NOT NULL
, opens INTEGER(2) NOT NULL
, open_rate NUMERIC(6,4) NOT NULL
, iphone INTEGER(2) NOT NULL
, android INTEGER(1) NOT NULL
, pc INTEGER(2) NOT NULL
, unsubscribes BIT NOT NULL
, clicks INTEGER(2) NOT NULL
, responses BIT NOT NULL
, opps INTEGER(1) NOT NULL
, approvals_delivered BIT NOT NULL
, approvals_opened BIT NOT NULL
, loans_delivered BIT NOT NULL
, draw_amount NUMERIC(14,6)
);
INSERT INTO mytable(primary_attribute_value,date,emails,emails_delivered,delivery_rate,opens,open_rate,iphone,android,pc,unsubscribes,clicks,responses,opps,approvals_delivered,approvals_opened,loans_delivered,draw_amount) VALUES ('6/23 CLI - Yodlee.Email','2015-06-22T00:00:00.0000000',147,144,0.9795,67,0.4652,19,9,55,0,28,0,5,0,0,0,2997433.117148);
INSERT INTO mytable(primary_attribute_value,date,emails,emails_delivered,delivery_rate,opens,open_rate,iphone,android,pc,unsubscribes,clicks,responses,opps,approvals_delivered,approvals_opened,loans_delivered,draw_amount) VALUES ('LOC Rate Decrease June 2015.Email','2015-06-22T00:00:00.0000000',112,109,0.9732,52,0.4770,27,7,10,0,4,0,0,0,0,0,NULL);
June 30, 2015 at 9:48 am
dandenise316 (6/29/2015)
DROP TABLE mytable;CREATE TABLE mytable(
primary_attribute_value VARCHAR(33) NOT NULL PRIMARY KEY
, date VARCHAR(27) NOT NULL
, emails INTEGER(3) NOT NULL
, emails_delivered INTEGER(3) NOT NULL
, delivery_rate NUMERIC(6,4) NOT NULL
, opens INTEGER(2) NOT NULL
, open_rate NUMERIC(6,4) NOT NULL
, iphone INTEGER(2) NOT NULL
, android INTEGER(1) NOT NULL
, pc INTEGER(2) NOT NULL
, unsubscribes BIT NOT NULL
, clicks INTEGER(2) NOT NULL
, responses BIT NOT NULL
, opps INTEGER(1) NOT NULL
, approvals_delivered BIT NOT NULL
, approvals_opened BIT NOT NULL
, loans_delivered BIT NOT NULL
, draw_amount NUMERIC(14,6)
);
INSERT INTO mytable(primary_attribute_value,date,emails,emails_delivered,delivery_rate,opens,open_rate,iphone,android,pc,unsubscribes,clicks,responses,opps,approvals_delivered,approvals_opened,loans_delivered,draw_amount) VALUES ('6/23 CLI - Yodlee.Email','2015-06-22T00:00:00.0000000',147,144,0.9795,67,0.4652,19,9,55,0,28,0,5,0,0,0,2997433.117148);
INSERT INTO mytable(primary_attribute_value,date,emails,emails_delivered,delivery_rate,opens,open_rate,iphone,android,pc,unsubscribes,clicks,responses,opps,approvals_delivered,approvals_opened,loans_delivered,draw_amount) VALUES ('LOC Rate Decrease June 2015.Email','2015-06-22T00:00:00.0000000',112,109,0.9732,52,0.4770,27,7,10,0,4,0,0,0,0,0,NULL);
Are you sure you're using SQL Server ? The integer data type does not use any kind of length parameter. Also, why are you using VARCHAR(27) for a datetime value? Any chance you're data is coming from Oracle? If that's true, there might not be much we can help with here. This is a SQL Server forum.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply