Stumbling on a SQL query - Help!!

  • 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.

  • 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.......

  • 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.

  • 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.

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

  • 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.)

  • 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'

  • 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.

  • 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

  • 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.

  • 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.....???

  • 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.

  • Thanks for all your help Lynn. I will figure it out from here.

  • When a resolution you have, be sure to post back with it.

    There may be others with similiar issues as you.

  • 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