Best Way to do IT

Question

Post reply

Best Way to do IT

abhi-307868

SSC-Addicted

Points: 415
More actions
February 16, 2007 at 10:45 am

#172132

Hello All,

I have a table say tableA. In this table i have a field called order_type which can have one of the following 3 values 'B','R',NULL. The other fields that will be used from this table are order_number and date_entered.

My requirement is to create a report based on the backorders and reship pecentage on a weekly basis for the entire year. The formula that will go on the report is as following-

(no. of order_type='B'/no. of order_type=NULL)*100

(no. of order_type='R'/no. of order_type=NULL)*100

For this requirement i have to create a dataset to have the fields say B(for backorder),R(for reships) and N(for total orders) based on weeks for each month.

I am not sure how i can create a dataset with all the 3 values for B,R and N available based on the dates.

Once this dataset is available it's very easy to create the report.

Any help will be appreciated.
February 19, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Sounds a lot like homework... What have you tried?

--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)
Intro to Tally Tables and Functions

abhi-307868 SSC-Addicted Points: 415 More actions · Answer 2

abhi-307868

SSC-Addicted

Points: 415

February 19, 2007 at 6:08 pm

#690568

I created views and then did joins on the combination of views and was able to achieve what i wanted .....but was wondering if there is any quicker,efficient way to achieve the same.

Thanks

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Sure there is... is Table "A" the only table you need to draw from? Also, can you type up an example of what you'd like the output to look like?

--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)
Intro to Tally Tables and Functions

John Deupree SSCommitted Points: 1705 More actions · Answer 4

One approach is to create a date table (or table variable) and do a join against it. I have used a table valued function to create a table variable with the begin and end dates of every month for a 5 year span and then joined a table against it using BETWEEN

select * from table A

inner join dateTable d on A.enteredDate between d.begDate and d.endDate

Not the most efficient query, but it worked for me against a table with 2m rows.

HTH

John Deupree