November 13, 2006 at 8:05 am
I need to write a stored procedure that meets the following specification
Tables Involved:
TblCustomer - CustNo,CustName
TblInvoice - InvoiceNo,IssuedDate,CustNo
TblProduct - ProductCode,ProdDesc,ProductFamily,GroupKeyID,FRB,SourceID,MarketSegmtID,ConvFactor
TblInvoiceDetailProduct - InvoiceNo,ProductCode,Quantity,UnitPrice
TblInvoiceDetailGL - InvoiceNo,GLCode,Quantity,UnitPrice
TblInvoiceDetailNonProduct - InvoiceNo,
TblInvoiceDetailProductNonInventory - InvoiceNo,ProductCode,Quantity,UnitPrice
Fields to be selected - CustName,ProductDescription,ProductCode,SourceID,FRB,MktSegmtID,Quantity( Sum up for the Invoices' of the particular period),Value - Summation of all the Invoices' quantity * convfactor in ProductTable
Using the CustNo from TblInvoice and TblCustomer,CustName should be selected
If the ProductCode of ProductTable matches with the following tables
InVoiceDetailProduct
InVoiceDetailProductNonInventory
InVoiceDetailGL
InVoiceDetailNonProduct,
We have to retrieve the corresponding fields : ProductDesc,SourceId,FRB, MktSegmentID From ProductTable
Quantity - For the particular period,summing up of all the Invoices' Quantity
Can anyone let me know how I can write a stored procedure that meets the specification?That would be a great help!
Thank you!
Sincerely,
Deepa
November 13, 2006 at 8:36 am
Hello Deepa,
you have posted a very similar question recently
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=321867
and it was answered to your satisfaction. Could you please point out the differences, or with what you have problems in this procedure, how your procedure looks now and what it does not perform correctly?
You tried to write something before posting a question, didn't you?
Also, you didn't answer the question about table NonInventory in the other thread and how it is used. This question still stands and is relevant for this post as well.
We'll be glad to point you in the right direction, but if you tell us what you have so far, it will be much easier.
November 13, 2006 at 9:09 am
Hi,
Well,I do need changes that differ from previous stored procedure.Before the stord procedure was bit straight forward.
Now,I retrieve the CustName from Customer Table using the relation between TblCustomer and TblInvoice thro' CustNo field
From then on For each matching Productcode with the Table TblInvoiceDetailProduct,
I need to retrieve ProductDesc,SourceID,FRB,MtSegmtId,BillingQuantity Reporting Quantity and Value.
Billing Quantity - sum of Quantity in InvoiceDetailProduct for all the invoices of that particular (eg: Jan 1 to 10 2006)
Reporting Quantity - sum of Quantity in InvoiceDetailProduct for all the
invoices * Convfactor from TblProduct
Value - Summation of all the invoices' Quantity * unitprice in InvoiceDetailProduct
LikeWise I've retrieve the same set of fields for matching ProductCode From ProductTable with InvoicedDetailProductNonInventory,InvoiceDetailGL
and InvoiceDetailNonProduct
If there is no matching records,Those can be left blank.
Incase of InvoiceDetailGL,Under ProductCode column,GLCode of that table can be selected.
I hope I've given the detailed requirement.
Please let me know how to acheive the specification using store procedure.
Thank you!
Sincerely,
Deepa!
November 16, 2006 at 2:53 am
Hi,
Can I know the logic to build upon this stored procedure?
Thank you,
Sincerely,
Deepa
November 16, 2006 at 8:31 pm
Hi,
I've just gone with this follwing stored procedure for the above requirement specified.Might be the Query is not so efficient,its meeting the requirement.But I couldn't retrieve the records for the specified period.Can anyone help me in getting the data?
Create Procedure dbo.procMARRPTMonthlySalesByCustomer_Final
@FromDate smalldatetime='',
@ToDate smalldatetime=''
As
Set NoCount On
if @FromDate =''
Begin
Select @FromDate=cast(cast(year(getdate())as varchar)+ '-01-01' as smalldatetime)
End
Begin
Select @FromDate=cast(cast(year(getdate()) as varchar)+'-01-01' as smalldatetime)
End
if @ToDate =''
Begin
Select @ToDate=cast(cast(year(getdate())as varchar)+ '-01-01' as smalldatetime)
End
Begin
Select @ToDate=cast(cast(year(getdate()) as varchar)+'-01-01' as smalldatetime)
End
Select Cust.CustName,
P.ProductDesc,
P.ProductCode,
P.SourceID,
P.FRB,
P.MarketSegmtID,
Sum(InvDp.Quantity) As "Billing Quantity",
Sum(InvDp.Quantity) * P.ConvFactor As "Reporting Quantity",
Sum(InvDp.Quantity) * InvDp.UnitPrice As "Value"
From
dbo.MARTblInvoiceDetailProduct InvDp
inner join
dbo.MARTblInvoice Inv
on InvDp.InvoiceNo = Inv.InvoiceNo
inner join
dbo.MARTblCustomer Cust
on Inv.CustNo = Cust.CustNo
inner join
dbo.MARTblProduct P
on InvDp.ProductCode = P.ProductCode and InvDp.SourceID = P.SourceID and InvDp.MarketSegID = P.MarketSegmtID
Where Inv.IssuedDate between @FromDate and @ToDate
Group by
Cust.CustNo,
Cust.CustName,
P.ProductDesc,
P.ProductCode,
P.sourceID,
P.FRB,
P.MarketSegmtID,
P.ConvFactor,
InvDp.UnitPrice,
InvDp.Quantity
union
Select Cust.CustName,
P.ProductDesc,
P.ProductCode,
P.SourceID,
P.FRB,
P.MarketSegmtID,
Sum(InvDpNonInv.Quantity) As "Billing Quantity",
Sum(InvDpNonInv.Quantity) * P.ConvFactor As "Reporting Quantity",
Sum(InvDpNonInv.Quantity) * InvDpNonInv.UnitPrice As "Value"
From
dbo.MARTblInvoiceDetailProductNonInventory InvDpNonInv
inner join
dbo.MARTblInvoice Inv
on InvDpNonInv.InvoiceNo = Inv.InvoiceNo
inner join
dbo.MARTblCustomer Cust
on Inv.CustNo = Cust.CustNo
inner join
dbo.MARTblProduct P
on InvDpNonInv.ProductCode = P.ProductCode and InvDpNonInv.SourceID = P.SourceID and InvDpNonInv.MarketSegID = P.MarketSegmtID
Where Inv.IssuedDate between @FromDate and @ToDate
Group by
Cust.CustNo,
Cust.CustName,
P.ProductDesc,
P.ProductCode,
P.sourceID,
P.FRB,
P.MarketSegmtID,
P.ConvFactor,
InvDpNonInv.Quantity,
InvDpNonInv.UnitPrice
Select Cust.CustName,
GLC.GLDesc As "ProductDesc",
InvDetGL.GLCode As "ProductCode",
'' As SourceID,
'' As FRB,
'' As MarketSegmtID,
Sum(InvDetGL.Quantity) As "Billing Quantity",
0 As "Reporting Quantity",
Sum(InvDetGL.Quantity) * InvDetGL.UnitPrice As "Value"
From
dbo.MARTblInvoiceDetailGL InvDetGL
inner join
dbo.MARTblInvoice Inv
on InvDetGL.InvoiceNo = Inv.InvoiceNo
inner join
dbo.MARTblCustomer Cust
on Inv.CustNo = Cust.CustNo
inner join
dbo.MARTblGLCode GLC
on InvDetGL.GLCode = GLC.GLCode
Where Inv.IssuedDate between @FromDate and @ToDate
Group by
Cust.CustNo,
Cust.CustName,
InvDetGL.Quantity,
InvDetGL.UnitPrice,
GLC.GLDesc,
InvDetGL.GLCode
union
Select Cust.CustName,
InvDNonP.ProductName As "ProductDesc",
'' As "ProductCode",
'' As "SourceID",
'' As "FRB",
'' As "MarketSegmtID",
0 As "Billing Quantity",
0 As "Reporting Quantity",
Sum(InvDNonP.Amount) As "Value"
From
dbo.MARTblInvoiceDetailNonProduct InvDNonP
inner join
dbo.MARTblInvoice Inv
on InvDNonP.InvoiceNo = Inv.InvoiceNo
inner join
dbo.MARTblCustomer Cust
on Inv.CustNo = Cust.CustNo
Where Inv.IssuedDate between @FromDate and @ToDate
Group by
Cust.CustNo,
Cust.CustName,
InvDNonP.ProductName,
InvDNonP.Amount
Thank you,
Sincerely,
Deepa
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply