Facebook
From Hot Agouti, 7 Years ago, written in Plain Text.
Embed
Download Paste or View Raw
Hits: 281
  1. WITH rows AS
  2.  (SELECT RowId FROM
  3. (
  4. SELECT
  5.   dbo.UserDefinedFields.FieldTitle AS Title,
  6.   dbo.UserDefinedData.UserDefinedRowId AS RowId,
  7.   dbo.UserDefinedData.FieldValue AS Value
  8. FROM dbo.UserDefinedData
  9. INNER JOIN dbo.UserDefinedRows
  10.   ON dbo.UserDefinedData.UserDefinedRowId =
  11.   dbo.UserDefinedRows.UserDefinedRowId
  12. INNER JOIN dbo.UserDefinedFields
  13.   ON dbo.UserDefinedFields.UserDefinedFieldId =
  14.   dbo.UserDefinedData.UserDefinedFieldId
  15. WHERE dbo.UserDefinedRows.ModuleId = 671
  16. AND NOT (FieldTitle = 'Changed at'
  17. OR FieldTitle = 'Created at'
  18. OR FieldTitle = 'Changed by'
  19. OR FieldTitle = 'Created by')
  20. ) AS temptable1
  21.  WHERE Title='cat' AND Value = 'enf')
  22.  
  23. SELECT
  24.    Title,
  25.    SUM(CASE WHEN Value = 'n' THEN 1 ELSE 0 END)
  26. FROM
  27. (
  28. SELECT
  29.   dbo.UserDefinedFields.FieldTitle AS Title,
  30.   dbo.UserDefinedData.UserDefinedRowId AS RowId,
  31.   dbo.UserDefinedData.FieldValue AS Value
  32. FROM dbo.UserDefinedData
  33. INNER JOIN dbo.UserDefinedRows
  34.   ON dbo.UserDefinedData.UserDefinedRowId =
  35.   dbo.UserDefinedRows.UserDefinedRowId
  36. INNER JOIN dbo.UserDefinedFields
  37.   ON dbo.UserDefinedFields.UserDefinedFieldId =
  38.   dbo.UserDefinedData.UserDefinedFieldId
  39. WHERE dbo.UserDefinedRows.ModuleId = 671
  40. AND NOT (FieldTitle = 'Changed at'
  41. OR FieldTitle = 'Created at'
  42. OR FieldTitle = 'Changed by'
  43. OR FieldTitle = 'Created by')
  44. ) AS t1
  45.   JOIN rows AS r
  46.      ON r.RowId = t1.RowId AND Title LIKE 'UL%'
  47.  
  48. GROUP BY t1.Title