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.
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.
- select CustomerID,CompanyName from tbl_Customer
- CustomerID CompanyName
- 1 Love Lights
- 4 Edmundson Electrical (Guernsey)
- 5 PJH Group Ltd
- 6 O Neills Kitchen Supplies Ltd
- 7 Edmundson Electrical (kingslynn)
- 8 Dream Home T/AS Urban Myth
- 9 Onefit Ltd
- 10 Mereway Kitchens Ltd
- 11 Edmunson Electrical (Tonbridge)
- 12 Express Indian Cuisine **
- select Fk_customerID,Login_Time from tbl_loginHistory
- Fk_customerID Login_Time
- 30365 2014-12-16 12:07:56.160
- 30365 2014-12-16 12:13:07.727
- 30365 2014-12-16 15:23:55.143
- 30365 2014-12-16 16:58:00.090
- 30365 2014-12-16 17:08:26.430
- 30365 2014-12-16 17:10:37.100
- 30365 2014-12-16 17:11:17.453
- 30365 2014-12-16 17:24:20.257
- 30365 2014-12-16 17:58:22.473
- select CustomerID,OrderDate from tbl_Orderheader
- CustomerID Order Date
- 30365 2014-12-16 12:07:56.160
- 30365 2014-12-16 12:13:07.727
- 30365 2014-12-16 1:13:07.727
- 30365 2014-12-16 2:13:07.727
- 30365 2014-12-16 12:1:07.727
- 30365 2014-12-16 12:3:07.727
- 30365 2014-12-16 12:30:07.727
- 30365 2014-12-16 12:34:07.727
- 30365 2014-12-16 12:20:07.727
- 30365 2014-12-16 12:17:07.727
Try One:
- select C.CompanyName,COUNT(lh.Fk_customerID) from tbl_LoginHistory LH
- inner join tbl_Customer C on LH.Fk_customerID=c.CustomerID
- inner join tbl_OrderHeader OH on LH.Fk_customerID=oh.CustomerID
- 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.
- ALTER proc [dbo].[Proc_CustomerLoginHistory] 2014,12
- (
- @years varchar(max),
- @month varchar(max)
- )
- as
- begin
- ;with customerlist(csid,companyname)
- as
- (
- select distinct CustomerID ,CompanyName from tbl_Customer
-
- ),
- monthtotallogin(csid,monthlycount)
- as
- (
- select fk_customerid as csid,count(*) monthlycount from tbl_LoginHistory
- where MONTH(login_time)= @month and year(login_time)=@years group by Fk_customerID having Fk_customerID>0
- ),
- totalcount(csid,totallogincount)
- as
- (
- select fk_customerid as csid,count(*) as totallogincount from tbl_LoginHistory group by Fk_customerID having Fk_customerID>0
- ),
- Totalorderofmonth(csid,monthlyordercount)
- as
- (
- select CustomerID as csid,COUNT(*) as monthlyordercount from tbl_OrderHeader
- where MONTH(OrderDate)= @month and year(OrderDate)=@years
- group by CustomerID having CustomerID>0
- ),
- Totalordero(csid,ordercount)
- as
- (
- select CustomerID as csid,COUNT(*) as monthlyordercount from tbl_OrderHeader group by CustomerID having CustomerID>0
- )
-
-
- select CS.companyname as [Company Name],isnull(ML.monthlycount,0) as [This Month Total Login],
- 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]
- from customerlist cs
- left join monthtotallogin ML on cs.csid=ML.csid
- left join totalcount TL on cs.csid=TL.csid
- left join Totalorderofmonth TM on cs.csid=TM.csid
- left join Totalordero TOR on cs.csid=TOR.csid
- where monthlycount>0
-
- end
Output of above query
- Company Name This Month Total Login Total Login Till Date Total Order this Month Total Order Till Date
-
- 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.