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

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: 1004356 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://www.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)