Help in building query to generate a view

Question

Post reply

Help in building query to generate a view

raj.lath-932078

SSC Journeyman

Points: 98
More actions
July 13, 2010 at 1:34 am

#223763

I have this situation and is looking forward to a solution

Tables:

Med : MedicineCode,MedicineName

Medlot : MedicineCode,lotcode,OpeningStock,Salerate,MRP,input,output

medmast : tdate,refno

meddet : refno,medicinecode,qty,trantype

Get Closing stock on a particular date (tdate from medmast)

display

GroupBand MedicineName

DetaildBand Lotcode

MRP

Bought (sum of qty for trantype 'P' for this lotcode upto tdate)

sold (sum of qty for trantype 'S' for this lotcode upto tdate)

SR (sum of qty for trantype 'sr'for this lotcode upto tdate)

PR (sum of qty for trantype 'pr'for this lotcode upto tdate)

ClosingStock Calculated by Opening+Bought+SR-sold-pr

Value ClosingStock * salesrate

inputvalue sold-sr * input

outputvalue bought-pr *output

inputded (Opening+Bought-pr)*input

Hope somebody has a solution for this.
July 13, 2010 at 4:46 am

This was removed by the editor as SPAM

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

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

lloyd-236007 SSC Enthusiast Points: 197 More actions · Answer 1

lloyd-236007

SSC Enthusiast

Points: 197

July 13, 2010 at 6:19 am

#1191950

Can we get more details please

============================

blog: http://lloydom.blogspot.com/

raj.lath-932078 SSC Journeyman Points: 98 More actions · Answer 2

Tables are

1.Med Fields are MedicineCode primary varchar(10)

MedicineName varchar(30)

2.MedLot Fields are MedecineCode Primary varchar(10)

Lotcode Primary int

OpeningStock int

Salerate decimal(8,2)

MRP decimal(6,2)

input decimal(6,4)

output decimal(6,4)

3.MedMast Fields are refno primary int

tdate Datetime

4.Meddet Fields are id primary int auto

refno int

qty int

trantype varchar(5)

Med table is populated with medicinename having unique medcode

Medlot has medcode and lotcode (medcode can have multiple lotcode) so combine to be primary

Every Medcode+lotcode has a op.stock,salerate,mrp,input and output value

Medmast is the master table have a refno and date

Meddet is the detail table for the medmast.For every refno there are multiple records

having a qty and trantype.

For every record in the Medlot table we need to find out closing stock by first finding out the sum of

bought = for all the record having trantype = 'PU' in the meddet table

sold for all the record having trantype = 'SA' in the meddet table

sret for all the record having trantyep = 'SR' in the meddet table

pret for all the record having trantype = 'PR' in the meddet table

for a particular medcode + lotcode combination

Once these values are found

closing stock has to be calculated by Opstock+Bought+Sret-(Sold+pret)

after calculating the closing stock

we also need to calculate

NowValue ClosingStock * salesrate

inputvalue (sold-sret) * input

outputvalue (bought-pr) *output

inputded (Opening+Bought-pr)*input

I think this sums up the requirement and hope i have explained it properly.

If you need any more information please let me know.

For reason i can not upload database but i can mail it to you if i can have your email id.

Thanks once again to both of you.

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

raj.lath-932078 (7/13/2010)

Tables are

1.Med Fields are MedicineCode primary varchar(10)

MedicineName varchar(30)

2.MedLot Fields are MedecineCode Primary varchar(10)

Lotcode Primary int

OpeningStock int

Salerate decimal(8,2)

MRP decimal(6,2)

input decimal(6,4)

output decimal(6,4)

3.MedMast Fields are refno primary int

tdate Datetime

4.Meddet Fields are id primary int auto

refno int

qty int

trantype varchar(5)

Med table is populated with medicinename having unique medcode

Medlot has medcode and lotcode (medcode can have multiple lotcode) so combine to be primary

Every Medcode+lotcode has a op.stock,salerate,mrp,input and output value

Medmast is the master table have a refno and date

Meddet is the detail table for the medmast.For every refno there are multiple records

having a qty and trantype.

For every record in the Medlot table we need to find out closing stock by first finding out the sum of

bought = for all the record having trantype = 'PU' in the meddet table

sold for all the record having trantype = 'SA' in the meddet table

sret for all the record having trantyep = 'SR' in the meddet table

pret for all the record having trantype = 'PR' in the meddet table

for a particular medcode + lotcode combination

Once these values are found

closing stock has to be calculated by Opstock+Bought+Sret-(Sold+pret)

after calculating the closing stock

we also need to calculate

NowValue ClosingStock * salesrate

inputvalue (sold-sret) * input

outputvalue (bought-pr) *output

inputded (Opening+Bought-pr)*input

I think this sums up the requirement and hope i have explained it properly.

If you need any more information please let me know.

For reason i can not upload database but i can mail it to you if i can have your email id.

Thanks once again to both of you.

Hi Raj,

You're kind of new and the other folks haven't given you enough information to know what they mean. I know you might be in a hurry but people will jump through hoops for you if you submit table and data information like the following article shows you...

http://qa.sqlservercentral.com/articles/Best+Practices/61537/

Don't wait for next time... do it now. You'll probably be very pleasantly surprised.

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