January 14, 2013 at 5:54 am
Hello,
I am new to this board so thanks in advance for any help that can be provided here...:-)
I would like some advice and best practice on the following as i am working on something that my rusty TSQL skills need help on..
Table 1 (Client) consists of say 5 clients and a column with an expected value populated for each (clientid, expectedvalue)
Table 2 (JobHistory) consists of the actual job data pulled back from each client for each run (clientid, jobinfo)
I want to know how to compare the value populated in table 1 (expectedvalue) against the actual row count for each client (jobinfo) in table 2 ?
i can get the value when specifying a where clause in the sub query but this fails when removed due to the subquery returning more than 1 value - i need this query to loop through all of my 5 clients until complete so i have a client \ expected \ actual value being output for all..
Would a CTE be more applicable here or a while loop to loop through all of the clients in table 1?
This will be pretty simple to someone who uses such processes so would appreciate someone directing me down the right path please? 😀
SELECT clientid, expectedvalue,
(select COUNT(jobinfo) FROM JobHistory
where clientid = 'xxx'
group by clientid) as received
FROM client
January 14, 2013 at 6:04 am
Hi
Something like this?
DECLARE @Client TABLE (ClientID INT, Expected INT)
DECLARE @JobHis TABLE (ClientID INT, JobInfo INT)
INSERT INTO @Client VALUES (1,1),(2,2),(3,3),(4,4),(5,5)
INSERT INTO @JobHis VALUES (1,100),(2,100),(2,200),(3,100),(3,200),(4,100),(4,200),(4,300),(4,400),(5,100),(5,200),(5,300),(5,400),(5,500)
SELECT
t1.ClientID,
t1.Expected,
COUNT(t2.ClientID) AS Actual,
CASE WHEN t1.Expected > COUNT(t2.ClientID) THEN 'Error, Actual less than Expected' ELSE 'OK, Expected and Actual match' END AS Status
FROM
@Client t1
INNER JOIN
@JobHis t2
ON
t1.ClientID = t2.ClientID
GROUP BY
t1.ClientID,
t1.Expected
If not, could you please follow the second link in my signature on posting code and data for the best help.
From that and with the expected outcome you provide we will be able to hopefully get a solution which solves your problem.
January 14, 2013 at 7:29 am
Hi Anthony,
many thanks for such a prompt reply, is greatly appreciated..:-)
I have tested your code and works as expected, however i didn't mention the fact that there could be possibly be upto 100 clients using this process and the expected values could change - i have read the best practices for posts as per advised and will ensure all information required is posted in future..
so, my client table has clientid and expected populated and will be pretty much static (future additions \ changes may or may not be made) and the jobhis table contains information about the job against each client
i first wanted some code to look through the client table and join with the jobhis table, then build on a calculation on the variance between the 2..
i have included some code to build the tables and test data - for example, client id 3,5 and 8 all should have variances, all of the other clients should have a count in jobhis that matches with their expected value..
-- create client table
CREATE TABLE [dbo].[Client](
[clientID] [int] NOT NULL,
[expected] [int] NOT NULL,
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[clientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- create jobhis table
CREATE TABLE [dbo].[JobHis](
[JobHistID] [int] IDENTITY(1,1) NOT NULL,
[clientid] [int] NOT NULL,
[jobinfo] [varchar](50) NOT NULL,
CONSTRAINT [PK_JobHis] PRIMARY KEY CLUSTERED
(
[JobHistID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- insert test client into client table
INSERT INTO [dbo].[Client]
([clientID]
,[expected])
values
(1,1),(2,2),(3,4),(4,2),(5,5),(6,2),(7,2),(8,5),(9,0),(10,3)
GO
--insert job results into jobhist table to compare against expected value
INSERT INTO [dbo].[JobHis]
([clientID]
,[jobinfo])
values
(1,'test job successful'),
(2,'test job successful'),
(2,'test job successful'),
(4,'test job successful'),
(4,'test job successful'),
(6,'test job successful'),
(6,'test job successful'),
(7,'test job successful'),
(7,'test job successful'),
(10,'test job successful'),
(10,'test job successful'),
(10,'test job successful')
GO
January 14, 2013 at 7:43 am
Something like this would work.
CREATE TABLE [dbo].[Client](
[clientID] [int] NOT NULL,
[expected] [int] NOT NULL,
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[clientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- create jobhis table
CREATE TABLE [dbo].[JobHis](
[JobHistID] [int] IDENTITY(1,1) NOT NULL,
[clientid] [int] NOT NULL,
[jobinfo] [varchar](50) NOT NULL,
CONSTRAINT [PK_JobHis] PRIMARY KEY CLUSTERED
(
[JobHistID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- insert test client into client table
INSERT INTO [dbo].[Client]
([clientID]
,[expected])
values
(1,1),(2,2),(3,4),(4,2),(5,5),(6,2),(7,2),(8,5),(9,0),(10,3)
GO
--insert job results into jobhist table to compare against expected value
INSERT INTO [dbo].[JobHis]
([clientID]
,[jobinfo])
values
(1,'test job successful'),
(2,'test job successful'),
(2,'test job successful'),
(4,'test job successful'),
(4,'test job successful'),
(6,'test job successful'),
(6,'test job successful'),
(7,'test job successful'),
(7,'test job successful'),
(10,'test job successful'),
(10,'test job successful'),
(10,'test job successful')
GO
select
*
from
dbo.Client c
left outer join dbo.JobHis j
on (c.clientID = j.clientid)
;
go
select
c.clientID,
c.expected,
ca.JobCount,
case when c.expected = ca.JobCount then 'No ' else '' end + 'Variance' as Results
from
dbo.Client c
cross apply (select count(*) from dbo.JobHis jh where jh.clientid = c.clientID) ca(JobCount)
;
go
drop table dbo.Client;
drop table dbo.JobHis;
go
January 14, 2013 at 7:52 am
hi Lynn
this is great, thanks for the outer join used also as making more sense to me now 🙂
would it be difficult if a value was to be output rather than a yes or no to themnot matching using the case statement? this would give me the actual variance between actual and expected also and kill 2 birds with one stone for me?
January 14, 2013 at 9:47 pm
finno (1/14/2013)
hi Lynnthis is great, thanks for the outer join used also as making more sense to me now 🙂
would it be difficult if a value was to be output rather than a yes or no to themnot matching using the case statement? this would give me the actual variance between actual and expected also and kill 2 birds with one stone for me?
Everything you need is already there. This is an exercise for you. If you have a problem figuring it out, let us know.
January 15, 2013 at 12:39 am
Thanks Lynn,yes i'll build on what i have now and let you know of any problems,again many thanks for your help:-D
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply