May 4, 2014 at 3:15 am
I've following query which display the result as required, but I need to get the missing Primary Key Values which are not available in the result:
SELECTA.SignedByUserID, B.FullName, COUNT(A.OutletID) AS TotalSignups, DATENAME(Month, A.SignupDate) AS Month
FROMdbo.tblMer_Outlet AS A LEFT OUTER JOIN dbo.tblGen_Users AS B ON A.SignedByUserID = B.UserID
WHERE(A.SignupDate >= '2014-04-01 00:00:00' AND A.SignupDate <= '2014-04-30 23:59:59')
GROUP BY A.SignedByUserID, B.FullName, DATENAME(Month, A.SignupDate)
This Query returns the following result:
SignedByUserID FullName TotalSignups Month
--------------------------------------------------------
9 Babu Raj 16 April
11 Faheem 19 April
39 Fasil Abbas 16 April
29 Hafiz Suleman 10 April
12 Hussain Abbas 16 April
15 Khawaja Aashan 33 April
33 M. Danyal 16 April
41 M. Qasim 01 April
32 M. Yousuf 16 April
37 Noman Yousaf 14 April
40 Sajid Saleem 16 April
5 Sales 10 April
20 Tauseef Anees 23 April
35 Umar Akbar 11 April
22 Willie 09 April
but my User table contains more value:
SELECTUserID, FullName
FROMtblGen_Users WHERE GroupID = 4 AND UserID NOT IN (
SELECTSignedByUserID
FROMtblMer_Outlet
WHERE(SignupDate >= '2014-04-01 00:00:00' AND SignupDate <= '2014-04-30 23:59:59')
)
SignedByUserID FullName
---------------------------
38 Bilal Mateen
which does not have a signup for the month of April, but I need it to be available for this or any upcoming month. I need this orr all users, which does not exists in the context needs to be displayed in the result.
May 4, 2014 at 4:55 am
Quite few ways of doing this, in essence create a set of all customers/months and then do the count.
The server normally translates queries such as "select * from A left outer join B where B.xx = yy" to an inner join.
😎
/* For lack of other data */
USE AdventureWorks2012;
GO
;WITH ORDER_MONTH AS
(
/* All Order Months */
SELECT DISTINCT
(YEAR(SOH.OrderDate) * 100) + MONTH(SOH.OrderDate) AS ISO_MONTH
FROM Sales.SalesOrderHeader SOH
)
,CUST_MONTH AS
(
/* All Customer Month combinations */
SELECT
SACU.CustomerID
,OM.ISO_MONTH
FROM Sales.Customer SACU
CROSS APPLY ORDER_MONTH OM
)
,CUST_MONTH_COUNT AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY
CM.CustomerID
,CM.ISO_MONTH
ORDER BY
(SELECT NULL)
) AS CUMO_RID
,CM.CustomerID
,CM.ISO_MONTH
,ISNULL(COUNT(SOH.SalesOrderID) OVER
(
PARTITION BY
CM.CustomerID
,YEAR(SOH.OrderDate), MONTH(SOH.OrderDate)
),0) AS ORDER_COUNT
FROM CUST_MONTH CM
LEFT OUTER JOIN Sales.SalesOrderHeader SOH
ON CM.CustomerID = SOH.CustomerID
AND CM.ISO_MONTH = (YEAR(SOH.OrderDate) * 100) + MONTH(SOH.OrderDate)
)
SELECT
CMC.CustomerID
,CMC.ISO_MONTH
,CMC.ORDER_COUNT
FROM CUST_MONTH_COUNT CMC
WHERE CMC.CUMO_RID = 1
AND CMC.ISO_MONTH = 200803;
May 4, 2014 at 8:41 am
eligiable (5/4/2014)
I've following query which display the result as required, but I need to get the missing Primary Key Values which are not available in the result:
SELECTA.SignedByUserID, B.FullName, COUNT(A.OutletID) AS TotalSignups, DATENAME(Month, A.SignupDate) AS Month
FROMdbo.tblMer_Outlet AS A LEFT OUTER JOIN dbo.tblGen_Users AS B ON A.SignedByUserID = B.UserID
WHERE(A.SignupDate >= '2014-04-01 00:00:00' AND A.SignupDate <= '2014-04-30 23:59:59')
GROUP BY A.SignedByUserID, B.FullName, DATENAME(Month, A.SignupDate)
This Query returns the following result:
SignedByUserID FullName TotalSignups Month
--------------------------------------------------------
9 Babu Raj 16 April
11 Faheem 19 April
39 Fasil Abbas 16 April
29 Hafiz Suleman 10 April
12 Hussain Abbas 16 April
15 Khawaja Aashan 33 April
33 M. Danyal 16 April
41 M. Qasim 01 April
32 M. Yousuf 16 April
37 Noman Yousaf 14 April
40 Sajid Saleem 16 April
5 Sales 10 April
20 Tauseef Anees 23 April
35 Umar Akbar 11 April
22 Willie 09 April
but my User table contains more value:
SELECTUserID, FullName
FROMtblGen_Users WHERE GroupID = 4 AND UserID NOT IN (
SELECTSignedByUserID
FROMtblMer_Outlet
WHERE(SignupDate >= '2014-04-01 00:00:00' AND SignupDate <= '2014-04-30 23:59:59')
)
SignedByUserID FullName
---------------------------
38 Bilal Mateen
which does not have a signup for the month of April, but I need it to be available for this or any upcoming month. I need this orr all users, which does not exists in the context needs to be displayed in the result.
Careful, the two queries above do not say same thing. Please read the comments I put in the code below.
SELECT
A.SignedByUserID,
B.FullName,
COUNT(A.OutletID) AS TotalSignups,
DATENAME(Month, A.SignupDate) AS Month
FROM
/*
select all rows from dbo.tblMer_Outlet whether there is
a matching row in dbo.tblGen_Users or not.
*/
dbo.tblMer_Outlet AS A
LEFT OUTER JOIN dbo.tblGen_Users AS B
ON A.SignedByUserID = B.UserID
WHERE
-- A.SignupDate >= '2014-04-01 00:00:00' AND
-- A.SignupDate <= '2014-04-30 23:59:59')
A.SignupDate >= '2014-04-01 00:00:00' AND
A.SignupDate < '2014-05-01 00:00:00') -- Insures that any time > 2014-04-30 23:59:59
-- and < 2014-05-01 00:00:00 are caught
GROUP BY
A.SignedByUserID,
B.FullName,
DATENAME(Month, A.SignupDate);
/*********************/
SELECT
UserID,
FullName
FROM
tblGen_Users
WHERE
GroupID = 4 AND
/*
Select all rows where the UserID is not in dbo.tblMer_Outlet
*/
UserID NOT IN (
SELECT
SignedByUserID
FROM
tblMer_Outlet
WHERE
SignupDate >= '2014-04-01 00:00:00' AND
SignupDate < '2014-05-01 00:00:00' -- same change as above
);
May 4, 2014 at 11:43 am
Lynn Pettis! The 2nd Query is just for reference, that what I'm trying to get into the results. My main Table is tblGen_Users and UserID is PK, I've tried every logic, I can think; but no luck.
SELECT
A.SignedByUserID,
B.FullName,
COUNT(A.OutletID) AS TotalSignups,
DATENAME(Month, A.SignupDate) AS Month
FROM
dbo.tblGen_Users AS B LEFT OUTER JOIN
--The tblGen_Users contains all the values
dbo.tblMer_Outlet AS A ON B.UserID = A.SignedByUserID
--The tblMer_Outlet contains only the selected values
/* If the values from tblGen_Users are not available in tblGen_Merchant, the missing values must be displayed in the result mentioning 0 under COUNT */
WHERE
(A.SignupDate >= '2014-04-01 00:00:00') AND
(A.SignupDate <= '2014-04-30 23:59:59')
GROUP BY
A.SignedByUserID,
B.FullName,
DATENAME(Month, A.SignupDate)
ORDER BY
B.FullName
May 4, 2014 at 12:29 pm
I've make this Fiddle and is running fine, I don't get it where is the problem:
May 4, 2014 at 12:39 pm
eligiable (5/4/2014)
I've make this Fiddle and is running fine, I don't get it where is the problem:
What ever happened to Baby Jane, oops, sorry, Bilal Mateen's ID?
😎
May 4, 2014 at 1:03 pm
It's just the SignedByUserID, that for Bilal Mateen does not exists in tblMer_Outlet, so we can change it to B.UserID from tblGen_Users, both are the same, but that not concern to me.
The main thing I'm not getting the desired result in My Query.
May 4, 2014 at 1:11 pm
eligiable (5/4/2014)
It's just the SignedByUserID, that for Bilal Mateen does not exists in tblMer_Outlet, so we can change it to B.UserID from tblGen_Users, both are the same, but that not concern to me.The main thing I'm not getting the desired result in My Query.
Good stuff, just wandered if you'd noticed. And then of course, you could also coalesce the count to a 0 and the month name.
😎
May 4, 2014 at 1:32 pm
COALESCE will work if the query returns a NULL value, but the thing is; in my actual case it's not returning.
May 4, 2014 at 1:41 pm
Finally got something Working ...
SELECT A.UserID, A.FullName, ISNULL(B.TotalSignups,0) AS TotalSignups, B.Month FROM
(
SELECT UserID, FullName
FROM tblGen_Users
WHERE GroupID = 4
) AS A LEFT OUTER JOIN
(
SELECT SignedByUserID, COUNT(1) AS TotalSignups, DATENAME(Month, SignupDate) AS Month
FROM tblMer_Outlet
WHERE SignupDate BETWEEN '2014-04-01' AND '2014-05-01'
GROUP BY SignedByUserID, DATENAME(Month, SignupDate)
) AS B ON A.UserID = B.SignedByUserID
ORDER BY A.FullName
May 4, 2014 at 2:49 pm
eligiable (5/4/2014)
Lynn Pettis! The 2nd Query is just for reference, that what I'm trying to get into the results. My main Table is tblGen_Users and UserID is PK, I've tried every logic, I can think; but no luck.
SELECT
A.SignedByUserID,
B.FullName,
COUNT(A.OutletID) AS TotalSignups,
DATENAME(Month, A.SignupDate) AS Month
FROM
dbo.tblGen_Users AS B LEFT OUTER JOIN
--The tblGen_Users contains all the values
dbo.tblMer_Outlet AS A ON B.UserID = A.SignedByUserID
--The tblMer_Outlet contains only the selected values
/* If the values from tblGen_Users are not available in tblGen_Merchant, the missing values must be displayed in the result mentioning 0 under COUNT */
WHERE
(A.SignupDate >= '2014-04-01 00:00:00') AND
(A.SignupDate <= '2014-04-30 23:59:59')
GROUP BY
A.SignedByUserID,
B.FullName,
DATENAME(Month, A.SignupDate)
ORDER BY
B.FullName
I do know that the second query was for reference. My point was that your first query would not return any values from the second table that did not exist in the first table.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply