Stumbling on a SQL query - Help!!

Question

Post reply

Stumbling on a SQL query - Help!!

Viewing 15 posts - 16 through 30 (of 50 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You don't seem to get "it"

We can't help you unless we see what you see EXACTLY.

That means we need a copy of the tables WITH data AND the queries and functions you are using.

Since you don't provide those, we CAN NOT HELP.

You're the one who needs the help, so help us help you!

airborn SSC Enthusiast Points: 183 More actions · Answer 2

If you read through the thread you would see that I have posted the function and the sQL query I used in order to try and get an output. Not to mention I send the output (to show how it looks). I am not sure what else I need to send.

Here are the two tables with the columns - the PK is win_id on both.

dbo.apples

( outcome int, not null

staff int, not null

disp int,not null

county int,not null

month int,not null

year int,not null

)

dbo.grapes

(

priority int

)

Maybe I am not understanding what else to send. But this is all I got.

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

The script that create those objects. Reread the article again to see how to do it exactly.

The output should look something like this :

CREATE Table dbo.TableName (Col 1 INt, Col2 Int)

repeat for each table in the query

Create function dbo.FunctionName()....

INSERT INTO TABLEONE (Col1, col2) VALUES (1,2)

repeat for all tables.

Then send the failing query, why it's failing. Then send the correct result set you need.

Anything less than that, we CAN NOT HELP.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

airborn (1/21/2009)
If you read through the thread you would see that I have posted the function and the sQL query I used in order to try and get an output. Not to mention I send the output (to show how it looks). I am not sure what else I need to send.
Here are the two tables with the columns - the PK is win_id on both.
dbo.apples
( outcome int, not null
staff int, not null
disp int,not null
county int,not null
month int,not null
year int,not null
)
dbo.grapes
(
priority int
)
Maybe I am not understanding what else to send. But this is all I got.
Thanks

Please tell me, based on the above tables, how are apples and grapes related to one another?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Earlier you indicated that you read the article I asked you to read. Follow the guidelines in that article to post your DDL for your tables, to create and post sample data for those tables. Based on that sample data, what should the expected results be from any query we may write.

Without that, we can't help you. All we can do at the moment is guess at what you need.

I still haven't grasped what it is you are trying to accomplish.

GSquared SSC Guru Points: 260824 More actions · Answer 6

One statement is that win_id is the relationship between the two, but that column does not appear in either of those table definitions. Is it supposed to be in there and was just missed, or something else?

- 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

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 7

Lynn, you have the patience of a saint. Yoda be da man!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

airborn SSC Enthusiast Points: 183 More actions · Answer 8

Lynn - thanks for having patience! I appreciate that.

As you can see I am very much new to SQL - and thus far I have been used to DB's with a "what you see is what you get" format (as far as output). However, these databases are a bunch of numbers!!!!

I don't know what else to provide. So if none of what I have provided thus far is not making sense and it is impossible for any of you to see what I am trying to do. Then I thank you all for your help and I will move on.

Thanks again!!

--===== 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 '19820865478765','1'UNION ALL

SELECT '19820835478765','5'UNION ALL

SELECT '19820895478765','4' UNION ALL

SELECT '19820865438765','2',UNION ALL

SELECT '19820865478765','1',UNION ALL

SELECT '19820865478765','2',UNION ALL

===== My function up-to-date

--@runspermon

--@OutcomeStatus INT,

--@County INT,

--@Year INT

--@Priority INT

)

RETURNS @YearTotalTable Table

(

Outcome INT,

Staff INT,

Disp INT,

County INT,

Month INT,

Year INT,

Priority INT,

)

AS

BEGIN

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

Priority)

SELECT outcome_sta,staff_high,disp_level,

r_county,r_month,r_year,priority,

FROM dbo.apples LEFT OUTER JOIN dbo.grapes

ON dbo.apples.win_id = dbo.grapes.win_id

WHERE r_year = '2007'

GROUP BY outcome_sta,staff_high,disp_level,

r_county,r_month,r_year,priority,

RETURN

END

===== The SQL Query I tried for just Disp

Select Disp,

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

You are missing one last thing from your last post. Go back and look at your sample data. Based on that data, what is the expected output you are looking for. Need something to test against or still guessing at what you need.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

You also have a problem with the following data.

INSERT INTO #grapes

(win_id, priority)

SELECT '19820865478765','1' UNION ALL

SELECT '19820835478765','5' UNION ALL

SELECT '19820895478765','4' UNION ALL

SELECT '19820865438765','2' UNION ALL

SELECT '19820865478765','1' UNION ALL

SELECT '19820865478765','2'

The column win_id is declared as a primary key, yet each record as the same key value. This won't work.

airborn SSC Enthusiast Points: 183 More actions · Answer 11

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

airborn SSC Enthusiast Points: 183 More actions · Answer 12

Actually the the values are not the same. They look the same -but they are different.

GSquared SSC Guru Points: 260824 More actions · Answer 13

Lynn Pettis (1/21/2009)
You also have a problem with the following data.
INSERT INTO #grapes
(win_id, priority)
SELECT '19820865478765','1' UNION ALL
SELECT '19820835478765','5' UNION ALL
SELECT '19820895478765','4' UNION ALL
SELECT '19820865438765','2' UNION ALL
SELECT '19820865478765','1' UNION ALL
SELECT '19820865478765','2'
The column win_id is declared as a primary key, yet each record as the same key value. This won't work.

They're not the same, except the last 2. They all start the same and end the same, but the middles are slightly different (except the last 2, where I don't see any differences).

- 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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

GSquared (1/21/2009)
Lynn Pettis (1/21/2009)
You also have a problem with the following data.
INSERT INTO #grapes
(win_id, priority)
SELECT '19820865478765','1' UNION ALL
SELECT '19820835478765','5' UNION ALL
SELECT '19820895478765','4' UNION ALL
SELECT '19820865438765','2' UNION ALL
SELECT '19820865478765','1' UNION ALL
SELECT '19820865478765','2'
The column win_id is declared as a primary key, yet each record as the same key value. This won't work.
They're not the same, except the last 2. They all start the same and end the same, but the middles are slightly different (except the last 2, where I don't see any differences).

All I know is the insert failed with an attempt to enter a duplicate key. My eyes are getting old, and glancing at the values, yes they initially appeared the same to me.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 15

Tell me if I am wrong, but aren't the last 2 the same as the first?