Stumbling on a SQL query - Help!!

  • I need to create a report looking at data from 2007. Basically I need to get the total number of months, outcome, staff , and priority by counties. I wrote a function and then tried to do a Select Case statement for each. But I don't think I am getting the results I need. How would I get a count of total records and be able to say if it's 1 then, etc etc? Also how do I calculate the frequency? Meaning if I want to do a sum (month) divide that by calls, etc?

    ALTER FUNCTION [dbo].[GetTotalOf1]

    (

    --@runspermon

    --@Outcome INT,

    --@County INT,

    --@Year INT

    --@Priority INT

    )

    RETURNS @YearTotalTable Table

    (

    Outcome INT,

    StaffLevel INT,

    Disp INT,

    County INT,

    Month INT,

    Year INT,

    FirstDue INT,

    Priority INT,

    NCR INT,

    Transport INT

    )

    AS

    BEGIN

    INSERT @YearTotalTable(Outcome,Level,Disp,County,Month,Year,

    FirstDue,Priority,NCR,Transport)

    SELECT outcome,staff,call_disp_level,

    sticky_county,sticky_month,sticky_month/disp_level as Frequency,sticky_year,first,priority,

    no_care,transport_by

    FROM dbo.grapes,dbo.apples

    WHERE sticky_year = '2007'--@Year AND sticky_county =@County

    GROUP BY outcome,staff,dispatch_level,

    sticky_county,sticky_month,sticky_year,first,priority,

    no_care,transport_by

    RETURN

    END

    ----------------------------

    Select Disp,

    SUM(CASE Disp When '1' THEN Disp ELSE 0 END) AS 'SLA',

    SUM(CASE Disp WHEN '2' THEN Disp ELSE 0 END) AS 'SLB',

    --SUM(CASE Disp WHEN '8' THEN Disp ELSE 0 END) AS 'Multiple .',

    SUM(CASE Disp WHEN '9' THEN Disp ELSE 0 END) AS 'Unknown'

    FROM dbo.GetTotalOf1()

    Group by Disp

    -----------------------------------

  • You have only provided part of the information we really need to help you with your problem. Please read the first article below in my signature block regarding asking for assistance. if you follow the guidelines for posting information in that article you will get much better responses to your requests for help.

  • Thanks Lynn - the last article was pretty good.

    Actually I didn't create a table,etc because I was used the function to create sort of a "temp table"

    So I was using my select statement against a table that is called YearTotalTable which includes the following columns: Outcome, Level, Disp, County, Month, Year, First Due, Priority,NCR, Transport.

    When I do a select * from the function GetTotalOf1() I get those columns - everything in the column is numeric 1, 2, 3, etc etc. I figured that is why I should use a CASE statement - to be able to make sense of the data. But I am stuck!!

  • Your function selects data from a table. How do I help you when I don't know anything about that underlying table or have any sample data to put into that table to run your function against?

  • You also realize that your select statement in your function is returning a cartesian product as well, right? You have no join criteria between the two tables dbo.grapes and dbo.apples.

  • Lynn,

    Sample data looks like the following. I think thats what makes it more complicated. It's all numeric. As for the join - I thought I was joining it by putting both both DB's there. Since it was pulling out the column I needed from the other DB I didn't think I need to write out a Join. Am I wrong? -- Thanks again.

    OutcomeStaff DispCountyMonthYearFirstPrior

    1212720071171

    1212720071271

    1212720071371

    1212720071471

    1212720071571

    1212720071671

    1212720071871

    1212720071971

    1213120071171

    1213120071271

  • Looks like my output got messed up when sending. But the Year is obviously 2007.

  • airborn (1/21/2009)


    Lynn,

    Sample data looks like the following. I think thats what makes it more complicated. It's all numeric. As for the join - I thought I was joining it by putting both both DB's there. Since it was pulling out the column I needed from the other DB I didn't think I need to write out a Join. Am I wrong? -- Thanks again.

    OutcomeStaff DispCountyMonthYearFirstPrior

    1212720071171

    1212720071271

    1212720071371

    1212720071471

    1212720071571

    1212720071671

    1212720071871

    1212720071971

    1213120071171

    1213120071271

    Yes. We really need to know what the underlying tables are and how they are related to each other. The way your query is currently written in the function you are basically linking each row from the first table to ALL the rows in the second table. If each table has, for instance, 10 rows your result set will have 100 rows.

  • I just used a Left Outer Join. For example:

    FROM dbo.apple LEFT OUTER JOIN dbo.grapes

    ON dbo.apple.win_id = dbo.assess.win_id

    With the JOIN my query runs in 1:32. Without it, it was taking forever. Wow! Actually does it matter if I use a Left or Right since I am using the primary key from both tables? What if I just used JOIN?

    Does the data I provided help?

  • I only need the Priority column from the second table. Everything else comes from the first - going back to whether I have to be specific as far as a LEFT, RIGHT, etc Join.

    Thanks again.

  • The type of join is going to be based on what end result you want. Do you just want results that are in both tables? Then use "Inner Join". Do you want all results from one table as well as matching results from the other table? Then it'll be an left or right outer join (depending on which table you want everything from and which table you want matching results from). Do you want all results from both tables, regardless of whether there are matching results in the other table? Then it's Full Outer Join.

    The way it's written in the sample provided is a Cross Join, which means match all rows in each table to all rows in the other table.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Yes, I think it's fine that I match row for row since I have a Where clause that states just for the year '2007'

    If that makes any sense. I guess Join can be a bit confusing.

    Thanks!

  • No, the data you provided doesn't really help. I have no idea if you need to use an outer join or inner join as this is your data and I have no idea how the tables are related as you haven't provided that information either.

    You really need to follow the guidelines in that article regarding posting code and data if you want good, tested answers to your questions.

    At this time, I still don't know what you are trying to accomplish or if you even need to use a table-valued function to accomplish your task.

    You may just need a properly formatted select query joining both tables.

  • Hi Lynn,

    I wish I could provide you better data - but that's how the output looks. The relationship between the apples and grape table is the win_id. Not sure if that is what you are asking. The data is all numeric - For example for the month column 1 is equal to Jan 2 is equal to Feb. The numbers are equivalent to a value. With the exception of some columns such as the Year column which you get what you see: 2007, 2008, etc etc. Another example would be the numeric value of '99' is = to 'unknown'. Is it making sense now?

    What I need to do is capture the total number of outcome, staff, disp, priority by counties. How many outcome (total) was there in for example Jessup County. So on and so forth. For the month - I need a total of the runs that took place in that month and then get a frequency. Frequency to me is dividing the total (Jan to Dec) by each individual sum. But I am not sure where to go with it.

  • I am trying to get my report to look something like the following.

    A total for each month, outcome, staff. Out of the total how many was cancelled, unknown or for staff out of the total how many were interns, exec..etc etc...then how many were from Jessup Co.

    Total Frequency(%)Jessup Co.

    1136864

    MonthsJan '07

    Feb32304

    Mar

    Apr

    May74120

    Jun

    Jul

    Aug

    Sep

    Oct153280

    Nov

    Dec

    Outcome

    Cancelled

    False

    Unknown

    Staff

    Intern4

    Exec192

    Admin

    Other320

    Unknown

Viewing 15 posts - 1 through 15 (of 50 total)

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