January 21, 2009 at 12:56 pm
Sorry Lynn you were right - the last two are the same.
It's okay to switch the numbers around to make them non-dupe.
The whole point is the win_id is about 14 digits long.
Thanks for catching that for me GSquared.
January 21, 2009 at 12:57 pm
Your eyes are better then mine. Yes, the last two are the same as the first. Again, it can be switched around in the middle to be different. the first three numbers remain the same.......
January 21, 2009 at 12:59 pm
airborn (1/21/2009)
Based on my sample data - I need my output to show me a total record count for the Outcome,Staff,Disp,Month,Priority and that just for the year '2007'.Thanks
Here is the data as I modified it to get it to insert:
INSERT INTO #grapes
(win_id, priority)
SELECT '19820835478765','5' UNION ALL
SELECT '19820865478765','1' UNION ALL
SELECT '19820895478765','4' UNION ALL
SELECT '19820835438765','2' UNION ALL
SELECT '19820865438765','1' UNION ALL
SELECT '19820895438765','2'
Now, I need you to put together what the expected out should lok like. NOT a description, WHAT it should look like. I need something to compare what I come up with against. I can't compare my output to your description.
January 21, 2009 at 1:04 pm
Okay, is this what you are trying to accomplish?
--===== Create the test table
CREATE TABLE #apples
(
win_id numeric(18,0) PRIMARY KEY,
[Month] INT Not Null,
Outcome INT Not Null,
Staff INT Not Null,
Disp INT Not Null,
County INT Not Null,
[Year] INT Not Null
)
CREATE TABLE #grapes
( win_id numeric (18,0)PRIMARY KEY,
priority INT Not Null -- this is a Foreign Key as well
)
--===== Insert the test data into the test table #apples
INSERT INTO #apples
(win_id, [Month], Outcome, Staff, Disp,County,[Year])
SELECT '19820865478765','1','13','1','1','24','2007' UNION ALL
SELECT '19820865438765','2','12','2','3','33','2008' UNION ALL
SELECT '19820860438765','3','14','22','5','26','2006' UNION ALL
SELECT '19823860438763','5','20','3','1','32','2005' UNION ALL
SELECT '19823160438765','6','10','6','7','25','2006' UNION ALL
SELECT '19823960438765','7','13','12','9','3' ,'2004'
INSERT INTO #grapes
(win_id, priority)
SELECT '19820835478765','5' UNION ALL
SELECT '19820865478765','1' UNION ALL
SELECT '19820895478765','4' UNION ALL
SELECT '19820835438765','2' UNION ALL
SELECT '19820865438765','1' UNION ALL
SELECT '19820895438765','2'
declare @SelectYear int;
set @SelectYear = 2007
select
a.Outcome,
a.Staff,
a.Disp,
a.[Month],
g.priority
from
#apples a
inner join #grapes g
on (a.win_id = g.win_id)
where
[Year] = @SelectYear;
drop table #apples;
drop table #grapes;
If so, please ask any questions that you may have regarding the code above. If not me, someone will be happy to answer your questions.
January 21, 2009 at 1:19 pm
Lynn - from what I understand this code will just pull the outcome, staff, etc for 2007. But how would you add all the records for outcome then get a total? Likewise for Staff, Disp, etc. The main goal is to get a count of how many outcomes there were in 2007 and how many disp...etc etc etc...
declare @SelectYear int;
set @SelectYear = 2007
select
a.Outcome,
a.Staff,
a.Disp,
a.[Month],
g.priority
from
#apples a
inner join #grapes g
on (a.win_id = g.win_id)
where
[Year] = @SelectYear;
drop table #apples;
drop table #grapes;
January 21, 2009 at 1:25 pm
airborn (1/21/2009)
Lynn - from what I understand this code will just pull the outcome, staff, etc for 2007. But how would you add all the records for outcome then get a total? Likewise for Staff, Disp, etc. The main goal is to get a count of how many outcomes there were in 2007 and how many disp...etc etc etc...declare @SelectYear int;
set @SelectYear = 2007
select
a.Outcome,
a.Staff,
a.Disp,
a.[Month],
g.priority
from
#apples a
inner join #grapes g
on (a.win_id = g.win_id)
where
[Year] = @SelectYear;
drop table #apples;
drop table #grapes;
That is why I asked you for the expected output. You gave me a description of what you wanted, not what you'd expect as output based on the provided sample data.
Give me that, and i can make the appropriate changes to my code.
I will be honest with you, I am a visually inspired type. Show me what you are looking for, don't describe it. Put another way (I am a born and raised Coloradoan), pretend I'm from Missouri and Show Me.
(For those of you outside the US, Missouri is also known as the Show Me State.)
January 21, 2009 at 1:43 pm
Based on the sample data - lets start with outcome has the number '13'
So in my table for Outcome there will be numbers from 1 to 13. So say we have 13, 1, 3, 5, 20. Then I want in my report for outcome to be the sum - in this case 42 for outcome as a total. But then out of 42 how many were unknown (total), missing(total), etc etc. Does that make sense?
INSERT INTO #apples
(win_id, [Month], Outcome, Staff, Disp,County,[Year])
SELECT '19820865478765','1','13','1','1','24','2007' UNION ALL
SELECT '19820865438765','2','12','2','3','33','2008' UNION ALL
SELECT '19820860438765','3','14','22','5','26','2006' UNION ALL
SELECT '19823860438763','5','20','3','1','32','2005' UNION ALL
SELECT '19823160438765','6','10','6','7','25','2006' UNION ALL
SELECT '19823960438765','7','13','12','9','3' ,'2004'
INSERT INTO #grapes
(win_id, priority)
SELECT '19820835478765','5' UNION ALL
SELECT '19820865478765','1' UNION ALL
SELECT '19820895478765','4' UNION ALL
SELECT '19820835438765','2' UNION ALL
SELECT '19820865438765','1' UNION ALL
SELECT '19820895438765','2'
January 21, 2009 at 1:58 pm
Unfortunately, no. Using your sample data, what would you like to see as output.
Input --> Process --> Output.
You have given me the Input, give me the Output, and I'll figure out the Process.
I need concrete, visual of the expected output base on the sample input. I.E. 2, 3 --> 8. From that I can figure out the process.
January 21, 2009 at 2:09 pm
See example below:
I have totals for each individual months and then under Frequency you see the Sum for Jan to Dec. I couldn't figure out how to get the frequency so that is left empty. I have to do it by county - Yet haven't figured out how to split (for example) the amount in Jan 15584 to see how many of those calls took place in Jessup Co. If you look at outcome I have the total for how many was cancelled, False or unknown..then as with month I need to figure out the frequency. I know this much..Frequency is calculated by dividing for example 15584/1136864...you get a decimal answer...is this good enough to figure out the process???
TotalFrequency(%)Jessup Co.
1136864
MonthsJan '0715584
Feb32304
Mar51072
Apr60800
May74120
Jun99456
Jul118944
Aug149504
Sep157536
Oct153280
Nov109736
Dec114528
Outcome Status
Cancelled 99456
False 60800
Unknown 32304
January 21, 2009 at 2:19 pm
Okay, I am having a disconnect. I can't figure out how you are getting from here:
INSERT INTO #apples
(win_id, [Month], Outcome, Staff, Disp,County,[Year])
SELECT '19820865478765','1','13','1','1','24','2007' UNION ALL
SELECT '19820865438765','2','12','2','3','33','2008' UNION ALL
SELECT '19820860438765','3','14','22','5','26','2006' UNION ALL
SELECT '19823860438763','5','20','3','1','32','2005' UNION ALL
SELECT '19823160438765','6','10','6','7','25','2006' UNION ALL
SELECT '19823960438765','7','13','12','9','3' ,'2004'
INSERT INTO #grapes
(win_id, priority)
SELECT '19820835478765','5' UNION ALL
SELECT '19820865478765','1' UNION ALL
SELECT '19820895478765','4' UNION ALL
SELECT '19820835438765','2' UNION ALL
SELECT '19820865438765','1' UNION ALL
SELECT '19820895438765','2'
to here:
Total Frequency (%) Jessup Co.
1136864
Months Jan '07 15584
Feb 32304
Mar 51072
Apr 60800
May 74120
Jun 99456
Jul 118944
Aug 149504
Sep 157536
Oct 153280
Nov 109736
Dec 114528
Outcome Status
Cancelled 99456
False 60800
Unknown 32304
I need the expected output based on the input provided, or you need to do some explaining regarding your data, or you need to provide more sample data, or all of the above.
January 21, 2009 at 2:22 pm
Because what I have in the sample data is only 6 lines....the DB I am working with has thousands. So the output I showed you is a total based off a thousand rows of data. I am sure it makes sense to you that with the sample data I require the same thing.....???
January 21, 2009 at 2:28 pm
But I need to be able to get from point A to point B, and point B in this case has no resemblance to point A. Show me what the output would be based solely on the sample data. If the sample isn't enough, then add more data, not all, but enough. Then base the expected output on the increased sample data.
January 21, 2009 at 2:32 pm
Thanks for all your help Lynn. I will figure it out from here.
January 21, 2009 at 2:41 pm
When a resolution you have, be sure to post back with it.
There may be others with similiar issues as you.
January 26, 2009 at 4:17 pm
I just thought I'd stop by and see how you are doing with your query and see if need any help.
Viewing 15 posts - 31 through 45 (of 50 total)
You must be logged in to reply to this topic. Login to reply