Advanced Select Query

  • ricky.chauvin

    SSC Enthusiast

    Points: 132

    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

  • Ian Scarlett

    SSC-Insane

    Points: 23197

    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

  • ricky.chauvin

    SSC Enthusiast

    Points: 132

    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

  • Allister Reid

    SSCrazy

    Points: 2646

    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/

  • ricky.chauvin

    SSC Enthusiast

    Points: 132

    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!

  • Ian Scarlett

    SSC-Insane

    Points: 23197

    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;-)

  • ricky.chauvin

    SSC Enthusiast

    Points: 132

    If only it was that easy.

    Can't wait for my boss to get back then its his blood pressure on the line again

  • diamondgm

    SSCrazy

    Points: 2824

    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.

  • ricky.chauvin

    SSC Enthusiast

    Points: 132

    That worked great thanks. Now just one pesky date column left and it's finally done.

    Ta Muchly for the help 😀

  • ricky.chauvin

    SSC Enthusiast

    Points: 132

    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)

  • ricky.chauvin

    SSC Enthusiast

    Points: 132

    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 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply