Skip to content

Instantly share code, notes, and snippets.

@zapkub
Created October 15, 2019 05:02
Show Gist options
  • Save zapkub/b27fad028c24e01d0599129c9de8e8dd to your computer and use it in GitHub Desktop.
Save zapkub/b27fad028c24e01d0599129c9de8e8dd to your computer and use it in GitHub Desktop.
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment