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: 1004340 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)

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

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: 1004340 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)

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