Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CTE or while loop or an other?


CTE or while loop or an other?

Author
Message
finno
finno
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 426
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? :-D

SELECT clientid, expectedvalue,

(select COUNT(jobinfo) FROM JobHistory
where clientid = 'xxx'
group by clientid) as received

FROM client
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6091 Visits: 6069
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


finno
finno
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 426
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
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




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
finno
finno
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 426
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?
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
finno (1/14/2013)
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?


Everything you need is already there. This is an exercise for you. If you have a problem figuring it out, let us know.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
finno
finno
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 426
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search