Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Tbl_User (UserId, Username)
- Tbl_Customer (CustomeriD, CustomerName)
- Tbl_DocA (DocId, CustomerID, DateCreate, DateAdd, UseriD)
- Tbl_DocB (DocId, CustomerID, DateCreate, DateAdd, UseriD)
- Tbl_DocC (DocId, CustomerID, DateCreate, DateAdd, UseriD)
- DECLARE @UserId int = 1; -- or whatever the id of the user you need
- WITH CTEDocA AS
- (
- SELECT CustomerID
- , COUNT(DocId) As NumberOfReports
- , COUNT(CASE WHEN UserId = @UserId THEN 1 END) As NumberOfReportsByUserAzerty
- FROM Tbl_DocA
- GROUP BY CustomerID
- ), CTEDocB AS
- (
- SELECT CustomerID
- , COUNT(DocId) As NumberOfReports
- , COUNT(CASE WHEN UserId = @UserId THEN 1 END) As NumberOfReportsByUserAzerty
- FROM Tbl_DocB
- GROUP BY CustomerID
- ), CTEDocC AS
- (
- SELECT CustomerID
- , COUNT(DocId) As NumberOfReports
- , COUNT(CASE WHEN UserId = @UserId THEN 1 END) As NumberOfReportsByUserAzerty
- FROM Tbl_DocC
- GROUP BY CustomerID
- )
- SELECT cust.CustomeriD
- ,cust.CustomerName
- ,ISNULL(a.NumberOfReports, 0) As NumberOfDocA
- ,ISNULL(a.NumberOfReportsByUserAzerty, 0) As NumberOfDocAByAzerty
- ,ISNULL(b.NumberOfReports, 0) As NumberOfDocB
- ,ISNULL(b.NumberOfReportsByUserAzerty, 0) As NumberOfDocBByAzerty
- ,ISNULL(c.NumberOfReports, 0) As NumberOfDocC
- ,ISNULL(c.NumberOfReportsByUserAzerty, 0) As NumberOfDocCByAzerty
- FROM Tbl_Customer cust
- LEFT JOIN CTEDocA As a
- ON cust.CustomeriD = a.CustomerID
- LEFT JOIN CTEDocA As b
- ON cust.CustomeriD = b.CustomerID
- LEFT JOIN CTEDocA As c
- ON cust.CustomeriD = c.CustomerID
- select
- customerid,
- count(*),
- count(case when userid = <particular user ID here> then 1 end)
- from tbl_doca
- group by customerid;
- select
- c.customerid,
- c.customername,
- doca.total as doc_a_total,
- doca.az as doc_a_by_azerty,
- docb.total as doc_b_total,
- docb.az as doc_b_by_azerty,
- docc.total as doc_c_total,
- docc.az as doc_c_by_azerty
- from tbl_customer c
- cross join
- (
- select userid from tbl_user where username = 'Azerty'
- ) azerty
- cross apply
- (
- select
- count(*) as total,
- count(case when da.userid = azerty.userid then 1 end)n as az
- from tbl_doca da
- where da.customerid = c.customerid
- ) doca
- cross apply
- (
- select
- count(*) as total,
- count(case when db.userid = azerty.userid then 1 end)n as az
- from tbl_docb db
- where db.customerid = c.customerid
- ) docb
- cross apply
- (
- select
- count(*) as total,
- count(case when dc.userid = azerty.userid then 1 end)n as az
- from tbl_docc dc
- where dc.customerid = c.customerid
- ) docc
- order by c.customerid;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement