Advertisement
Guest User

Untitled

a guest
Mar 20th, 2019
59
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.57 KB | None | 0 0
  1. Tbl_User (UserId, Username)
  2. Tbl_Customer (CustomeriD, CustomerName)
  3. Tbl_DocA (DocId, CustomerID, DateCreate, DateAdd, UseriD)
  4. Tbl_DocB (DocId, CustomerID, DateCreate, DateAdd, UseriD)
  5. Tbl_DocC (DocId, CustomerID, DateCreate, DateAdd, UseriD)
  6.  
  7. DECLARE @UserId int = 1; -- or whatever the id of the user you need
  8. WITH CTEDocA AS
  9. (
  10. SELECT CustomerID
  11. , COUNT(DocId) As NumberOfReports
  12. , COUNT(CASE WHEN UserId = @UserId THEN 1 END) As NumberOfReportsByUserAzerty
  13. FROM Tbl_DocA
  14. GROUP BY CustomerID
  15. ), CTEDocB AS
  16. (
  17. SELECT CustomerID
  18. , COUNT(DocId) As NumberOfReports
  19. , COUNT(CASE WHEN UserId = @UserId THEN 1 END) As NumberOfReportsByUserAzerty
  20. FROM Tbl_DocB
  21. GROUP BY CustomerID
  22. ), CTEDocC AS
  23. (
  24. SELECT CustomerID
  25. , COUNT(DocId) As NumberOfReports
  26. , COUNT(CASE WHEN UserId = @UserId THEN 1 END) As NumberOfReportsByUserAzerty
  27. FROM Tbl_DocC
  28. GROUP BY CustomerID
  29. )
  30.  
  31. SELECT cust.CustomeriD
  32. ,cust.CustomerName
  33. ,ISNULL(a.NumberOfReports, 0) As NumberOfDocA
  34. ,ISNULL(a.NumberOfReportsByUserAzerty, 0) As NumberOfDocAByAzerty
  35. ,ISNULL(b.NumberOfReports, 0) As NumberOfDocB
  36. ,ISNULL(b.NumberOfReportsByUserAzerty, 0) As NumberOfDocBByAzerty
  37. ,ISNULL(c.NumberOfReports, 0) As NumberOfDocC
  38. ,ISNULL(c.NumberOfReportsByUserAzerty, 0) As NumberOfDocCByAzerty
  39. FROM Tbl_Customer cust
  40. LEFT JOIN CTEDocA As a
  41. ON cust.CustomeriD = a.CustomerID
  42. LEFT JOIN CTEDocA As b
  43. ON cust.CustomeriD = b.CustomerID
  44. LEFT JOIN CTEDocA As c
  45. ON cust.CustomeriD = c.CustomerID
  46.  
  47. select
  48. customerid,
  49. count(*),
  50. count(case when userid = <particular user ID here> then 1 end)
  51. from tbl_doca
  52. group by customerid;
  53.  
  54. select
  55. c.customerid,
  56. c.customername,
  57. doca.total as doc_a_total,
  58. doca.az as doc_a_by_azerty,
  59. docb.total as doc_b_total,
  60. docb.az as doc_b_by_azerty,
  61. docc.total as doc_c_total,
  62. docc.az as doc_c_by_azerty
  63. from tbl_customer c
  64. cross join
  65. (
  66. select userid from tbl_user where username = 'Azerty'
  67. ) azerty
  68. cross apply
  69. (
  70. select
  71. count(*) as total,
  72. count(case when da.userid = azerty.userid then 1 end)n as az
  73. from tbl_doca da
  74. where da.customerid = c.customerid
  75. ) doca
  76. cross apply
  77. (
  78. select
  79. count(*) as total,
  80. count(case when db.userid = azerty.userid then 1 end)n as az
  81. from tbl_docb db
  82. where db.customerid = c.customerid
  83. ) docb
  84. cross apply
  85. (
  86. select
  87. count(*) as total,
  88. count(case when dc.userid = azerty.userid then 1 end)n as az
  89. from tbl_docc dc
  90. where dc.customerid = c.customerid
  91. ) docc
  92. order by c.customerid;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement