Introduction

In the past week my TL give me some work. In the work I have three tables and every table is linked with a primary key and foreign key.

table

Ay first look this table looks very simple but if we add one more condition then that can make this query a bit messy. The preceding records get more than three tables. The table details are shown below.

  1. select CustomerID,CompanyName from tbl_Customer
  1. CustomerID CompanyName
  2. 1 Love Lights
  3. 4 Edmundson Electrical (Guernsey)
  4. 5 PJH Group Ltd
  5. 6 O Neills Kitchen Supplies Ltd
  6. 7 Edmundson Electrical (kingslynn)
  7. 8 Dream Home T/AS Urban Myth
  8. 9 Onefit Ltd
  9. 10 Mereway Kitchens Ltd
  10. 11 Edmunson Electrical (Tonbridge)
  11. 12 Express Indian Cuisine **
  1. select Fk_customerID,Login_Time from tbl_loginHistory
  1. Fk_customerID Login_Time
  2. 30365 2014-12-16 12:07:56.160
  3. 30365 2014-12-16 12:13:07.727
  4. 30365 2014-12-16 15:23:55.143
  5. 30365 2014-12-16 16:58:00.090
  6. 30365 2014-12-16 17:08:26.430
  7. 30365 2014-12-16 17:10:37.100
  8. 30365 2014-12-16 17:11:17.453
  9. 30365 2014-12-16 17:24:20.257
  10. 30365 2014-12-16 17:58:22.473
  1. select CustomerID,OrderDate from tbl_Orderheader
  1. CustomerID Order Date
  2. 30365 2014-12-16 12:07:56.160
  3. 30365 2014-12-16 12:13:07.727
  4. 30365 2014-12-16 1:13:07.727
  5. 30365 2014-12-16 2:13:07.727
  6. 30365 2014-12-16 12:1:07.727
  7. 30365 2014-12-16 12:3:07.727
  8. 30365 2014-12-16 12:30:07.727
  9. 30365 2014-12-16 12:34:07.727
  10. 30365 2014-12-16 12:20:07.727
  11. 30365 2014-12-16 12:17:07.727
Try One:
  1. select C.CompanyName,COUNT(lh.Fk_customerID) from tbl_LoginHistory LH
  2. inner join tbl_Customer C on LH.Fk_customerID=c.CustomerID
  3. inner join tbl_OrderHeader OH on LH.Fk_customerID=oh.CustomerID
  4. where MONTH(LH.login_time)= 12 and year(LH.login_time)=2014 group by LH.Fk_customerID
If we run this query then compiler throws the error:

Column 'tbl_Customer.CompanyName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Note: The receding query can't be solved because we need a count of the until date.

Finally, I found one simple and best way to solve this type of problem.

  1. ALTER proc [dbo].[Proc_CustomerLoginHistory] 2014,12
  2. (
  3. @years varchar(max),
  4. @month varchar(max)
  5. )
  6. as
  7. begin
  8. ;with customerlist(csid,companyname)
  9. as
  10. (
  11. select distinct CustomerID ,CompanyName from tbl_Customer
  13. ),
  14. monthtotallogin(csid,monthlycount)
  15. as
  16. (
  17. select fk_customerid as csid,count(*) monthlycount from tbl_LoginHistory
  18. where MONTH(login_time)= @month and year(login_time)=@years group by Fk_customerID having Fk_customerID>0
  19. ),
  20. totalcount(csid,totallogincount)
  21. as
  22. (
  23. select fk_customerid as csid,count(*) as totallogincount from tbl_LoginHistory group by Fk_customerID having Fk_customerID>0
  24. ),
  25. Totalorderofmonth(csid,monthlyordercount)
  26. as
  27. (
  28. select CustomerID as csid,COUNT(*) as monthlyordercount from tbl_OrderHeader
  29. where MONTH(OrderDate)= @month and year(OrderDate)=@years
  30. group by CustomerID having CustomerID>0
  31. ),
  32. Totalordero(csid,ordercount)
  33. as
  34. (
  35. select CustomerID as csid,COUNT(*) as monthlyordercount from tbl_OrderHeader group by CustomerID having CustomerID>0
  36. )
  39. select CS.companyname as [Company Name],isnull(ML.monthlycount,0) as [This Month Total Login],
  40. isnull(TL.totallogincount,0) as [Total Login Till Date],isnull(TM.monthlyordercount,0) as [Total Order this Month], isnull(TOR.ordercount,0)as [Total Order Till Date]
  41. from customerlist cs
  42. left join monthtotallogin ML on cs.csid=ML.csid
  43. left join totalcount TL on cs.csid=TL.csid
  44. left join Totalorderofmonth TM on cs.csid=TM.csid
  45. left join Totalordero TOR on cs.csid=TOR.csid
  46. where monthlycount>0
  48. end
Output of above query
  1. Company Name This Month Total Login Total Login Till Date Total Order this Month Total Order Till Date
  3. Website Testing 37 37 1 1
Final word

If you have a question or information about this problem then drop your comments below in the comment box.