IPS SQL queries

gistfile1.txt

--/api/business_license/application_data/complaint_id
SELECT *
  FROM [Building].[dbo].[Complaint] c
  left outer JOIN [Building].[dbo].[User_Defined_Action] uda
  ON c.complaint_id = uda.parent_id
  left outer JOIN [Building].[dbo].[User_Defined_Action_Type] udat
  ON uda.user_defined_action_type_id = udat.user_defined_action_type_id
  WHERE c.complaint_number = 'CU2014-0050'
 
--/api/business_license/inspection_data/complaint_id
SELECT *
  FROM [Building].[dbo].[Complaint] c
  join [Building].[dbo].[Inspection] i
  ON c.transaction_to_property_id = i.transaction_to_property_id
  WHERE c.complaint_number = 'CU2014-0050'
 
--/api/business_license/payment_data/complaint_id
SELECT *
from Building.dbo.Complaint c
join Building.dbo.Payment p
on c.transaction_to_property_id = p.transaction_to_property_id
Where complaint_number = 'CU2014-0050'


--api/permit_record/inspection/permit_number

SELECT issue_date, expire_date, complete_date, inspect_date, permit_type_name, permit_completion_type_name, inspection_type_name, result_status_type_name
  FROM [Building].[dbo].[Permit] p
  join [Building].[dbo].[Inspection] i
  ON p.transaction_to_property_id = i.transaction_to_property_id
  JOIN [Building].[dbo].[Permit_Type] pt
  on p.permit_type_id = pt.permit_type_id
  JOIN [Building].[dbo].[Permit_Completion_Type] pct
  on pct.permit_completion_type_id = p.permit_completion_type_id
  JOIN [Building].[dbo].[Inspection_Type] it
  on it.inspection_type_id = i.inspection_type_id
  JOIN [Building].[dbo].[Result_Status_Type] rst
  on rst.result_status_type_id = i.result_status_type_id
  WHERE p.permit_number = '22666'
  
--/api/permit_application/application_number
  SELECT [application_number]
      ,[application_date]
      ,[description_of_work]
      ,[user_note]
      ,[is_denied]
      ,[is_void]
      ,[is_permit_issued]
      ,pa.[date_last_changed]
	  ,[department_name]
	  ,[permit_type_description]
	  ,[status_type_name]
  FROM [Building].[dbo].[Permit_Application] pa
  JOIN [Building].[dbo].[Department] d
  on pa.department_id = d.department_id
  JOIN [Building].[dbo].[Permit_Type] pt
  on pt.permit_type_id = pa.permit_application_type_id
  JOIN [Building].[dbo].[Status_Type] st
  on st.status_type_id = pa.status_enum
  WHERE application_number = '06375'
  
--/api/permit_record/permit_number
SELECT permit_number, issue_date, expire_date, complete_date, p.date_last_changed, department_name, permit_completion_type_name, permit_type_name
  FROM [Building].[dbo].[Permit] p
  JOIN [Building].[dbo].[Department] d
  on p.department_id = d.department_id
  JOIN [Building].[dbo].[Permit_Completion_Type] pct
  on pct.permit_completion_type_id = p.permit_completion_type_id
  JOIN [Building].[dbo].[Permit_Type] pt
  on pt.permit_type_id = p.permit_type_id
  WHERE permit_number = '06375'

--/api/business_license/approvals
SELECT groupusers_name,complaint_number,status_date, c.date_last_changed, expiration_date,  open_date, approval_status_types_name, comments
  FROM [Building].[dbo].[Approval_Approvals] aa
  JOIN [Building].[dbo].[Complaint] c
  on c.complaint_id = aa.record_id
  JOIN [Building].[dbo].[Approval_Status_Types] ast
  on aa.approval_status_types_id = ast.approval_status_types_id
  JOIN [Building].[dbo].[Approval_GroupUsers] agu 
  on agu.approval_groupusers_id = aa.approval_groupusers_id
  WHERE complaint_number = 'CU2014-0050'
  
--/api/permits/application_number
SELECT status_type_name, approval_approvals_id, record_id, approval_groupusers_id, status_date, aa.approval_members_id, comments, aa.date_last_changed, permit_application_type_id, application_number, application_date, description_of_work, approval_status_types_name, login_name, first_name, last_name, status_type_name
FROM [Building].[dbo].[Approval_Approvals] aa
JOIN [Building].[dbo].[Permit_Application] pa
ON aa.record_id = pa.permit_application_id
JOIN [Building].[dbo].[Approval_Status_Types] ast
ON aa.approval_status_types_id = ast.approval_status_types_id
JOIN [Building].[dbo].[Approval_Members] am
ON am.login_name = aa.user_last_changed
JOIN [Building].[dbo].[Status_Type] st
ON st.status_type_id = pa.status_enum
WHERE aa.is_active = 1 AND application_number = '23347';