January 21, 2009 at 7:55 am
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
-----------------------------------
January 21, 2009 at 8:05 am
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.
January 21, 2009 at 8:19 am
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!!
January 21, 2009 at 8:39 am
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?
January 21, 2009 at 8:44 am
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.
January 21, 2009 at 8:56 am
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
January 21, 2009 at 8:57 am
Looks like my output got messed up when sending. But the Year is obviously 2007.
January 21, 2009 at 9:11 am
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.
January 21, 2009 at 9:20 am
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?
January 21, 2009 at 9:22 am
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.
January 21, 2009 at 9:25 am
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
January 21, 2009 at 9:35 am
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!
January 21, 2009 at 9:42 am
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.
January 21, 2009 at 9:50 am
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.
January 21, 2009 at 9:57 am
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