Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CTE or while loop or an other? Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 5:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 3, 2014 4:42 AM
Points: 13, Visits: 304
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
Post #1406675
Posted Monday, January 14, 2013 6:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:02 AM
Points: 5,143, Visits: 4,947
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
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

Post #1406681
Posted Monday, January 14, 2013 7:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 3, 2014 4:42 AM
Points: 13, Visits: 304
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

Post #1406721
Posted Monday, January 14, 2013 7:43 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:11 PM
Points: 23,070, Visits: 31,598
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





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)
Post #1406735
Posted Monday, January 14, 2013 7:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 3, 2014 4:42 AM
Points: 13, Visits: 304
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?
Post #1406741
Posted Monday, January 14, 2013 9:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:11 PM
Points: 23,070, Visits: 31,598
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.



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)
Post #1407018
Posted Tuesday, January 15, 2013 12:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 3, 2014 4:42 AM
Points: 13, Visits: 304
Thanks Lynn,yes i'll build on what i have now and let you know of any problems,again many thanks for your help
Post #1407055
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse