June 28, 2011 at 11:20 am
Hello all, stuggling to figure out how to do a Distinct first instance, if thats the correct way to state this.
Tryng to add a (1 or o) column that will add 1 to the frist instance of the type and 0 for any other within my frist instance column as way to create a unique count for another report.
TYPESTATEDATE
APPLEAZ5/1/2020
APPLEMN5/20/2010
APPLENY5/20/2010
GRAPENY5/1/2020
GRAPECA5/20/2010
ORANGECA5/1/2020
ORANGENY5/20/2010
ORANGEAZ5/20/2010
So the end result would be
TYPESTATE DATE first count
APPLEAZ5/1/2020 1
APPLEMN5/20/20100
APPLENY5/20/20100
GRAPENY5/1/2020 1
GRAPECA5/20/20100
ORANGECA5/1/2020 1
ORANGENY5/20/20100
ORANGEAZ5/20/20100
Thanks Joe
June 28, 2011 at 11:30 am
You could use row_number to achieve this.
The question is whether you actually need to do this to get the unique count for the report. If you could explain a bit more about the tables involved and what the final report needs there might be a way that doesnt require preprocessing.
June 28, 2011 at 11:45 am
Hi Nevyn
not sure about row number but the frist count is used as part of another formula to get a percentage.
so in effect it would be like adding the number states by the first type to get a percentage. So i need a unique count that is part of a larger query...
Hence the term Newbie, that still struggling with nested statments, such as IFF or in
June 28, 2011 at 11:58 am
If you tell us a bit more about the table you are querying we can figure something out.
Its tough to demonstrate when we are guessing at information.
For example, is the table you are querying made up of only those 3 rows (type, state, and date)? Are those the real row names (hopefully not as they are reserved words). If not, what is the primary key for the table?
The easiest thing would be for you to script the create table statement and post it. Then we should be able to give you your "first unique query".
If you give more detail about the final report you want and the tables it uses we might have a better answer for that, too.
June 28, 2011 at 12:11 pm
Nevyn is correct, it's hard to understand what you need. The description you've given isn't that clear as to what is needed.
You can mock up the table, but please give us:
create table MyTable
( type varchar(10)
, state varchar(2)
, saledate datetime
)
insert mytable select 'Apple', AZ, '5/1/2011'
insert mytable select 'Apple', CA, '5/3/2011'
...
And then explain how you are calculating the results. "First instance" doesn't mean anything. What is first? By date? By state then date?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply