October 28, 2009 at 4:46 am
Hi all,
Im fairly new to SQL having got into a new job through being fairly competant at VBA and Data Analysis. Within my new job I have been learning to use SQL (bigger data sets = more useful data).
I have been asked to generate some reports using SSRS (easy enough) however I am hitting a bit of a brick wall when it actually comes to getting the data into a usable format as the database is VERY large.
The problem I am hitting at the moment is I have to pull back when a product has passed through a process AND when it has failed (easy enough this data is already there). This is measured by the "Error Code" Column. In this column 0 = a pass <>0 (multiple error codes) = a fail.
I need to seperate these results into two columns and count the number of results. There are date parameters and operations to seperate by aswell but I think I can manage that easily enough myself using params.
To sum up:
I need to go from
|Error Code|
|----0-----|
|----0-----|
|---123----|
|---329----|
|----0-----|
to
|--Pass--|--Fail--|
|---3----|---2---|
Any help is appreciated. I am doing my best to teach myself all this but it isn't easy with a database this large.
Rik
October 28, 2009 at 5:05 am
Try something along these lines:-
create table test (err int)
go
insert test select 0
insert test select 0
insert test select 333
insert test select 212
insert test select 22
select sum(case err when 0 then 1 else 0 end) as Pass, sum(case err when 0 then 0 else 1 end) as fail from test
October 28, 2009 at 5:28 am
Will give that a shot in a moment. Already had some of the code around those sort of lines but gave up as I thought I was going in the wrong direction.
Ta Muchly
October 28, 2009 at 6:03 am
A cross-tab query as Ian has provided is the way to tackle this problem, you'll just have to get your group by correct so that the rows of you results are correct.
Check out the following for more information on cross-tab queries: http://www.sqlservercentral.com/articles/T-SQL/63681/
October 28, 2009 at 6:30 am
Cheers I'll look at that correctly once I've finished fixing the data that everyone has entered wrong...
Thank god im young and healthy otherwise my blood pressure might be a worry right now!
October 28, 2009 at 7:24 am
ricky.chauvin (10/28/2009)
Cheers I'll look at that correctly once I've finished fixing the data that everyone has entered wrong...Thank god im young and healthy otherwise my blood pressure might be a worry right now!
Constrain your database so the ba****ds, sorry, I mean users, can't enter wrong data... then you might still have low blood pressure where you're old and unhealthy;-)
October 28, 2009 at 7:30 am
If only it was that easy.
Can't wait for my boss to get back then its his blood pressure on the line again
October 28, 2009 at 1:55 pm
Ian Scarlett (10/28/2009)
Try something along these lines:-
create table test (err int)go
insert test select 0
insert test select 0
insert test select 333
insert test select 212
insert test select 22
select sum(case err when 0 then 1 else 0 end) as Pass, sum(case err when 0 then 0 else 1 end) as fail from test
I've learned a new approach, thank you :w00t: I compared your query with what I would have suggested:
SELECT (SELECT COUNT(*) FROM #test WHERE err = 0) [Pass], (SELECT COUNT(*) FROM #test WHERE err != 0) [Fail]
and found yours to be twice as efficient (the 2 table scans I'm guessing?)
Thank you Ian.
October 30, 2009 at 6:12 am
That worked great thanks. Now just one pesky date column left and it's finally done.
Ta Muchly for the help 😀
October 30, 2009 at 6:19 am
Thought I would include a bit of code to show anyone who's interested exactly what I am working with 😀 (edited for to make sure it didn't relate to my ACTUAL tables)
SELECT Operations.meo_OperationName, HistoryControlEvents.hce_EventDate,
SUM(CASE dbo.HistoryControlEvents.hce_ErrorCodeID WHEN 0 THEN 1 ELSE 0 END) AS Pass,
SUM(CASE dbo.HistoryControlEvents.hce_ErrorCodeID WHEN 0 THEN 0 ELSE 1 END) AS fail
FROM Products INNER JOIN
ProductType ON Products.prd_ProductTypeID = ProductType.prf_ProductTypeID INNER JOIN
HistoryControlEvents INNER JOIN
ProductItems ON HistoryControlEvents.hce_ProductItemID = ProductItems.pdi_ProductItemID INNER JOIN
Operations ON HistoryControlEvents.hce_OperationID = Operations.meo_OperationID ON
Products.prd_ProductID = ProductItems.pdi_ProductID
GROUP BY dbo.HistoryControlEvents.hce_EventDate, dbo. Operations.meo_OperationName
HAVING (HistoryControlEvents.hce_EventDate BETWEEN @startdate AND @enddate)
October 30, 2009 at 7:29 am
Ok chaps (and chapettes 🙂 )
One last question. My current query is being a bit of a pain in the fact that it is bringing results back for each minute individually. What I really need is for it to have each day individual but not each minute.
Any advice is appreciated.
Once I become an expert I promise to help as you have 😀
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply