Skip to content

Instantly share code, notes, and snippets.

@zapkub

zapkub/plant_search.sql

Created October 15, 2019 05:03
Show Gist options
  • Save zapkub/4ef488a858835078aab9b1a5a88df574 to your computer and use it in GitHub Desktop.
Save zapkub/4ef488a858835078aab9b1a5a88df574 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
plant_search.sql
SELECT [PlantFieldModel].[Plant_ID] AS [id], [PlantFieldModel].[Plant_CreateSeasonID] AS [createdSeasonId], [PlantFieldModel].[Plant_Name] AS [name], [PlantFieldModel].[Plant_No] AS [no], [PlantFieldModel].[Plant_Moo] AS [villageNo], [PlantFieldModel].[Plant_SubDistrict] AS [subdistrict], [PlantFieldModel].[Plant_District] AS [district], [PlantFieldModel].[Plant_Province] AS [province], [PlantFieldModel].[Zone_ID] AS [zoneId], [PlantFieldModel].[Plant_Latitude] AS [lat], [PlantFieldModel].[Plant_Longitude] AS [long], [PlantFieldModel].[Plant_AreaRai] AS [areaAsRai], [PlantFieldModel].[Plant_AreaShape] AS [areaFromShape], [PlantFieldModel].[Plant_GeoLocation] AS [geoLocation], [PlantFieldModel].[Quota_ID] AS [quotaId], [PlantFieldModel].[Plant_Created] AS [createdAt], [PlantFieldModel].[Plant_Changed] AS [updatedAt], [PlantFieldModel].[Plant_Creator] AS [createdBy], [PlantFieldModel].[Plant_CreateBy] AS [creatorFullName], CONVERT(varchar(max), [Plant_GeoLocation]) AS [geoLocationParsed], CAST(Plant_CreateDate as [datetime]) as Plant_CreateDate, CAST(Substring(Plant_No, 0, Charindex('-', Plant_No)) as INT) AS [empNo], CAST(Substring(Plant_No, Charindex('-', Plant_No) + 1, Len(Plant_No) + 1) as INT) AS [seqNo], [plantFieldSeason].[Season_ID] AS [plantFieldSeason.seasonId], [plantFieldSeason].[PlantSeason_ID] AS [plantFieldSeason.id], [plantFieldSeason].[PlantSeason_SurveyBy] AS [plantFieldSeason.surveyedBy], [plantFieldSeason].[PlantSeason_Created] AS [plantFieldSeason.createdAt], [plantFieldSeason].[PlantStatus_ID] AS [plantFieldSeason.plantStatusId], [plantFieldSeason].[PlantSeason_Active] AS [plantFieldSeason.active], [plantFieldSeason->assessments].[Assess_Supervisor_By] AS [plantFieldSeason.assessments.supervisedBy], [plantFieldSeason->assessments].[Assess_ID] AS [plantFieldSeason.assessments.id], [quota].[Quota_ID] AS [quota.id], [quota].[Quota_No] AS [quota.no], [quota].[Quota_FirstName] AS [quota.firstName], [quota].[Quota_LastName] AS [quota.lastName], [quota].[QuotaType_ID] AS [quota.type], [quota].[Quota_Created] AS [quota.createdAt], [quota].[Quota_Creator] AS [quota.createdBy], [quota].[Quota_Changed] AS [quota.updatedAt], [quota].[Zone_ID] AS [quota.zoneId], [quota].[Quota_Active] AS [quota.active]
FROM [tb_opr_Plant] AS [PlantFieldModel] LEFT OUTER JOIN [tb_opr_PlantSeason] AS [plantFieldSeason]
ON [PlantFieldModel].[Plant_ID] = [plantFieldSeason].[Plant_ID] AND [plantFieldSeason].[Season_ID] = 4
LEFT OUTER JOIN [tb_opr_Assess] AS [plantFieldSeason->assessments]
ON [plantFieldSeason].[PlantSeason_ID] = [plantFieldSeason->assessments].[PlantSeason_ID]
INNER JOIN [tb_opr_Quota] AS [quota] ON [PlantFieldModel].[Quota_ID] = [quota].[Quota_ID]
AND [quota].[Zone_ID] = 1 AND [quota].[Quota_Active] = 1
WHERE (([plantFieldSeason].[PlantSeason_Active] != 0 AND [plantFieldSeason].[PlantStatus_ID] != 1)
OR ([plantFieldSeason].[PlantSeason_Active] IS NULL AND [plantFieldSeason].[PlantStatus_ID] IS NULL))
AND (NOT ([PlantFieldModel].[Plant_No] = N'9999')) AND [PlantFieldModel].[Quota_ID] IN (592)
ORDER BY [PlantFieldModel].[Plant_Created] DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
@MaxInnoTech
Copy link

SELECT PlantFieldModel.Plant_ID AS id, PlantFieldModel.Plant_CreateSeasonID AS createdSeasonId, PlantFieldModel.Plant_Name AS name, PlantFieldModel.Plant_No AS no, PlantFieldModel.Plant_Moo AS villageNo,
PlantFieldModel.Plant_SubDistrict AS subdistrict, PlantFieldModel.Plant_District AS district, PlantFieldModel.Plant_Province AS province, PlantFieldModel.Zone_ID AS zoneId, PlantFieldModel.Plant_Latitude AS lat,
PlantFieldModel.Plant_Longitude AS long, PlantFieldModel.Plant_AreaRai AS areaAsRai, PlantFieldModel.Plant_AreaShape AS areaFromShape, PlantFieldModel.Plant_GeoLocation AS geoLocation,
PlantFieldModel.Quota_ID AS quotaId, PlantFieldModel.Plant_Created AS createdAt, PlantFieldModel.Plant_Changed AS updatedAt, PlantFieldModel.Plant_Creator AS createdBy,
PlantFieldModel.Plant_CreateBy AS creatorFullName, CONVERT(varchar(MAX), PlantFieldModel.Plant_GeoLocation) AS geoLocationParsed, CAST(PlantFieldModel.Plant_CreateDate AS [datetime]) AS Plant_CreateDate,
CAST(SUBSTRING(PlantFieldModel.Plant_No, 0, CHARINDEX('-', PlantFieldModel.Plant_No)) AS INT) AS empNo, CAST(SUBSTRING(PlantFieldModel.Plant_No, CHARINDEX('-', PlantFieldModel.Plant_No) + 1,
LEN(PlantFieldModel.Plant_No) + 1) AS INT) AS seqNo, plantFieldSeason.Season_ID AS [plantFieldSeason.seasonId], plantFieldSeason.PlantSeason_ID AS [plantFieldSeason.id],
plantFieldSeason.PlantSeason_SurveyBy AS [plantFieldSeason.surveyedBy], plantFieldSeason.PlantSeason_Created AS [plantFieldSeason.createdAt], plantFieldSeason.PlantStatus_ID AS [plantFieldSeason.plantStatusId],
plantFieldSeason.PlantSeason_Active AS [plantFieldSeason.active], [plantFieldSeason->assessments].Assess_Supervisor_By AS [plantFieldSeason.assessments.supervisedBy],
[plantFieldSeason->assessments].Assess_ID AS [plantFieldSeason.assessments.id], quota.Quota_ID AS [quota.id], quota.Quota_No AS [quota.no], quota.Quota_FirstName AS [quota.firstName],
quota.Quota_LastName AS [quota.lastName], quota.QuotaType_ID AS [quota.type], quota.Quota_Created AS [quota.createdAt], quota.Quota_Creator AS [quota.createdBy], quota.Quota_Changed AS [quota.updatedAt],
quota.Zone_ID AS [quota.zoneId], quota.Quota_Active AS [quota.active]
FROM dbo.tb_opr_Plant AS PlantFieldModel LEFT OUTER JOIN
dbo.tb_opr_PlantSeason AS plantFieldSeason ON PlantFieldModel.Plant_ID = plantFieldSeason.Plant_ID LEFT OUTER JOIN
dbo.tb_opr_Assess AS [plantFieldSeason->assessments] ON plantFieldSeason.PlantSeason_ID = [plantFieldSeason->assessments].PlantSeason_ID INNER JOIN
dbo.tb_opr_Quota AS quota ON PlantFieldModel.Quota_ID = quota.Quota_ID AND quota.Zone_ID = 1 AND quota.Quota_Active = 1
WHERE (plantFieldSeason.PlantSeason_Active <> 0) AND (plantFieldSeason.PlantStatus_ID <> 1) AND (NOT (PlantFieldModel.Plant_No = N'9999')) AND (PlantFieldModel.Quota_ID IN (592)) OR
(plantFieldSeason.PlantSeason_Active IS NULL) AND (plantFieldSeason.PlantStatus_ID IS NULL) AND (NOT (PlantFieldModel.Plant_No = N'9999')) AND (PlantFieldModel.Quota_ID IN (592))
ORDER BY [PlantFieldModel].[Plant_Created] DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment