How do I write the SQL query for the below question please? Do longer, or shorter campaigns
Question:
How do I write the SQL query for the below question please?
- Do longer, or shorter campaigns tend to raise more money? Why?
Headers of my data on each tabs are as follows
Table: campaign (main data) - ID, NAME, SUB_CATERGORY_ID, COUNTRY_ID, CURRENCY_ID, LAUNCHED, DEADLINE, GOAL, PLEDGED, BACKERS, OUTCOME
Table: country- ID, NAME
Table: currency- ID, NAME
Table: category- ID, NAME
Table: sub_category- ID, NAME, CATEGORY_ID
A Tutor was kind enough to answer this question for me, but for some reason my SQL server is not accepting the DATEDIFF function. Its throwing out an error saying Error no such function: DATEDIFF
I don't have a day in my data, I only have a launched and deadline column and I am not sure how to derive to the duration of a campaign. The below query was provided to me by a tutor here, which is not working in my server. Pls help!!
SELECT
CASE
WHEN DATEDIFF(cam.DEADLINE, cam.LAUNCHED) <= AVG_DURATIONTHEN'Shorter Campaigns'
ELSE'Longer Campaigns'
ENDas Campaign_Length,
AVG(cam.PLEDGED)as Average_Pledged
FROM campaign cam,
(SELECT AVG(DATEDIFF(DEADLINE, LAUNCHED))as AVG_DURATIONFROM campaign)as avg_table
GROUPBY Campaign_Length;
For your information, I tried the TRUNC function as well which does not seem to work, not sure if i am using that function correctly though, I am pretty new to SQL.
Smith and Roberson Business Law
ISBN: 978-0538473637
15th Edition
Authors: Richard A. Mann, Barry S. Roberts