Derived Query - GROUP BY

Here i am writing about derived query when we can use, and why
I am writing a query which gives me results as Total Emails went to one particular Person(Here we can consider FirstName,LastName)
And i am going to get these requirement by using GROUP BY Normally we have to use all columns whatever we mentioned in the SELECT statement.We have to mention all the columns in GROUP BY also.
But i want to get the result using FirstName,LastName in GROUP BY.
But it is not possible if you have many columns along with FirstName and LastName in Select Query.We have to give all the columnnames.

But by using DerivedQuery we can use this Find below Query.

SELECT Y.*,X.cnt
FROM
(
select count(1) cnt, ISnull(Table2.FirstName,'') as FirstName ,isnull(Table2.LastName,'') AS LastName FROM Table1
INNER JOIN Table2
ON Table1.emailID = Table2.EmailID
LEFT OUTER JOIN Table3 AS Table3
ON Table1.EmailID=Table3.EmaiID
WHERE 1=1
GROUP BY ISnull(Table2.FirstName,'') ,isnull(Table2.LastName,'')
)x
INNER JOIN
(
SELECT ISnull(Table2.FirstName,'') as FirstName ,isnull(Table2.LastName,'') AS LastName, Table1.DateSent as DateSent,Email.Subject as Subject,
case when RTRIM('') = 'SMS' then 'SMS' when RTRIM(' ') = 'Email' then 'Email' when Table3.EmaiID is null
then 'Email' Else 'SMS,EMAIL' end as MessageSent,
Table1.UserName as SentFrom
FROM Table1 INNER JOIN Table2
ON Table1.emailID = Table2.EmailID
LEFT OUTER JOIN Table3 AS Table3
ON Table1.EmailID=Table3.EmaiID
WHERE 1=1


) y on x.FirstName = y.FirstName and x.LastName=y.LastName order by 1,2

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server