Skip to content

Instantly share code, notes, and snippets.

@Kristjan-Reinsberg

Kristjan-Reinsberg/gist:287f692bf6b5c8d382f9ff09d3edd098

Created May 21, 2020 12:31
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Kristjan-Reinsberg/287f692bf6b5c8d382f9ff09d3edd098 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Kristjan-Reinsberg/287f692bf6b5c8d382f9ff09d3edd098 to your computer and use it in GitHub Desktop.
Download ZIP
SQL same table join and find difference in days
Raw
gistfile1.txt
SELECT
renewals.start_date AS UUS_POLIIS_start_date
,policies.end_date AS EELMINE_POOLIIS_end_date
,renewals.id AS UUS_POLIIS_id
,policies.id AS EELMINE_POOLIIS
,DATEDIFF( renewals.start_date , policies.end_date ) AS DATE_DIFF
FROM
policies
LEFT JOIN offering_policy ON offering_policy.policy_id = policies.id
LEFT JOIN offerings ON offerings.id = offering_policy.offering_id
/*MAGIC TRICK - JOIN SAME TABLE*/
/*JOIN samatabel AS ALIAS*/
/*ALIAS ON ALIAS.id = samatatabel.tulp */
LEFT JOIN policies AS renewals ON renewals.id = policies.renew_id
WHERE
policies.insurance_type_id = 6
AND policies.start_date >= "2019-01-31"
AND policies.start_date <= "2019-12-31"
AND policies.renew_id != 0
AND SUBSTRING(offerings.metadata, LOCATE("vehicle_year", offerings.metadata) +21, 4) != 2019
AND DATEDIFF( renewals.start_date , policies.end_date ) > 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment