July 3, 2012 at 3:39 am
Hi all,
I have two tables
UserAccount
CustomerOrders
I need to select all the admin users that can verifiy an order from the user account table which iv done,
I now need to connect to the CustomerOrders table and get a count of how many orders the user has verified but im unsure how i can return a count?
The CustomerOrder table has a column called "VerifiedBy" and this is a varchar(3) which is the users initials,
The User Account table has a column called "ShortAbbr" and this is also a varchar(3) which is there initials
so when a user verifys an order there initials are placed within the column "VerifiedBy" located in the CustomerOrder table so i would be joining on that field......
But like i said im baffled on how i can return a count whilst selecting over fields.....
this sql so far
DECLARE @DateFrom datetime
DECLARE @DateTo datetime
DECLARE @Count int
Select distinct u.FirstName + ' ' + u.Surname as Verifier
From UserAccount as u
join CustomerOrders as co on co.VerifiedBy = u.ShortAbbr
where u.IsAdmin = 1 and u.CanVerifyOrder = 1
Where in the above can i get the count on how many orders have been verified by the user initials.....
July 3, 2012 at 4:10 am
This was removed by the editor as SPAM
July 3, 2012 at 4:11 am
Add in the COUNT clause in the select which references say the ID column in the orders table
Select distinct u.FirstName + ' ' + u.Surname as Verifier, COUNT(co.ID) AS NumberVerified
From UserAccount as u
join CustomerOrders as co on co.VerifiedBy = u.ShortAbbr
where u.IsAdmin = 1 and u.CanVerifyOrder = 1
group by u.FirstName + ' ' + u.Surname
Edit, Stewart, beat me to it
July 3, 2012 at 4:24 am
Brilliant,
Thanks for the information, do you know how i could work out the percentage of orders verified? as i have the total now just curious if that was possible????
July 3, 2012 at 4:32 am
something like this should do it.
declare @sum int
select @sum = COUNT(co.orderno)From UserAccount as u
join CustomerOrders as co on co.VerifiedBy = u.ShortAbbr
where u.IsAdmin = 1 and u.CanVerifyOrder = 1
Select u.FirstName + ' ' + u.Surname as Verifier,
COUNT(co.OrderNo) AS OrdersVerified,
(COUNT(co.OrderNo)/@sum) * 100) AS PercentageVerified
From UserAccount as u
join CustomerOrders as co on co.VerifiedBy = u.ShortAbbr
where u.IsAdmin = 1 and u.CanVerifyOrder = 1
GROUP BY u.FirstName + ' ' + u.Surname
July 3, 2012 at 4:46 am
SELECT
Verified = 100.00*SUM(CASE WHEN Verified = 'Yes' THEN [Count] ELSE 0 END)/SUM([Count]),
Unverified = 100.00*SUM(CASE WHEN Verified = 'No' THEN [Count] ELSE 0 END)/SUM([Count])
FROM (
SELECT
Verified,
[Count] = COUNT(*)
FROM #CustomerOrders
CROSS APPLY (SELECT Verified = CASE WHEN VerifiedBy IS NOT NULL THEN 'Yes' ELSE 'No' END) x
GROUP BY Verified
) d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 3, 2012 at 5:16 am
Hi Thanks for the snippet,
Iv been playing around with the informations iv been trying to get and this is my SQL
DECLARE @DateFrom datetime
DECLARE @DateTo datetime
DECLARE @Count int
Select u.FirstName + ' ' + u.Surname as Verifier,
COUNT(co.OrderID) AS OrdersVerified,
(COUNT(co.OrderID)* 100 / (Select COUNT(*) From CustomerOrders)) as PVerifications,
(COUNT(co.OrderID)* 100 / (Select COUNT(*) From CustomerOrders as co join Customer as c on c.CustomerID = co.CustomerID where (c.AccountNumber is not null and c.SortCode is not null))) as PVerificationsWithBD,
COUNT(oh.OrderID) as CoverSold,
COUNT(oh.ConnectedDate) as Connected
From UserAccount as u
join CustomerOrders as co on co.VerifiedBy = u.ShortAbbr
join OrderHandsets as oh on co.OrderID = oh.OrderID
where u.IsAdmin = 1 and u.CanVerifyOrder = 1
and oh.ConnectedDate is not null
and co.VerifiedBy = u.ShortAbbr
and oh.PhoneCover is not null
and oh.CreatedDate >= '2012-06-01'
GROUP BY u.FirstName + ' ' + u.Surname
But the output is sooooo incorrect ill keep playing around with it.
July 3, 2012 at 5:20 am
Please follow the second link in my signature on posting code for the best help.
With that we will be able to create a mock environment and help you out
July 3, 2012 at 5:30 am
-- use a variable for the date
DECLARE @MinCreatedDate DATETIME
SET @MinCreatedDate = '2012-06-01'
SELECT @MinCreatedDate -- check it's correct
-- start simple, look at the whole picture
-- don't aggregate until you're sure the join types are correct
-- keep predicates in the ON clause unless you're sure join type is INNER
SELECT
Verifier = u.FirstName + ' ' + u.Surname,
co.OrderID,
co.CustomerID,
oh.OrderID,
oh.ConnectedDate
FROM CustomerOrders co
LEFT JOIN UserAccount u
ON u.ShortAbbr = co.VerifiedBy
LEFT JOIN OrderHandsets oh
ON oh.OrderID = co.OrderID
AND oh.ConnectedDate IS NOT NULL
AND oh.PhoneCover IS NOT NULL
AND oh.CreatedDate >= @MinCreatedDate
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply