|
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
|
|
|
|
|
Hi,
I dont think that asking others to write stored procedure for you will be good for you. You can search for syntax and understanding of stored procedures on google.com. Then if you have any specific problem, everybody here is sitting for solving each others problems. I am sure someone will help you.
As far as the syntax of stored procedures you can visit - http://www.microsoft.com/sql/techinfo/books.mspx[^]
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
|
Hi,
I came up with the following stored procedure.But I couldn't any data.Can anyone let me know the issue behind?
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
|
|
|
|
|
Hi there,
I am passing a LocationCode to a Stored Proc in my SQL report, and then the report is build around that parameter I passed.
The problem is, now I need to display the Location Name in the report footer for each page. I can currently display the LocationCode using the following code:
<br />
"Parameters!LocationCode.Value"
How can I get the LocationName for that LocationCode, without using "ReportingItems!txtLocationName.Value" - if I use this, I can only display the Name on the first page in a PDF doc.
|
|
|
|
|
Are you using Reporting Services ?
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Hello,
I'm working on a project which carrying a large scale of database (over 5 millions records).
I'm looking for a way to improve the communication speed with the SQL server in any way that i can find useful.
The main slowing reason that i have encounter is the INSERT and UPDATE command that can be processed for more then 2 minutes and this amount of time is unreasonable.
can anyone please advise how to do a different process method?
thanks,
Leo S
|
|
|
|
|
Hello,
Are you using Store Procedure for insert and update ?
Use set nocount on/off in store procedure.
Regards,
S Kumar
|
|
|
|
|
Lior S wrote: The main slowing reason that i have encounter is the INSERT and UPDATE command that can be processed for more then 2 minutes
What is being updated? What else is the SQL Server doing at the time of the update?
It may be a poor data model, or it may be that there are so many transactions taking place at the same time that the SQL Server cannot cope.
Tweaking the data model may help. So might the creation of staging tables. (Where you queue changes to the data and then have a process that implements the changes once in a while).
But, you really haven't provided enough information. The reason for the slow down may be due to a million and one possible things.
|
|
|
|
|
basically the main slowing reason is the UPDATE procedure since it is searching in more then 5 million records to fit to the conditioned row for the update.
i have used the c# SqlBulkCopy which provided me a good method to insert data quickly but still i don't have any solution for the update.
|
|
|
|
|
Lior S wrote: basically the main slowing reason is the UPDATE procedure since it is searching in more then 5 million records to fit to the conditioned row for the update.
Sounds like the search component isn't very efficient. Are you searching on the primary key, or an index? If not make an index from the columns on which you are searching.
|
|
|
|
|
HI,
I need to know what is the best way to create a database on SQLSERVER 2000 for a holding and its subsidiaries.
Same server will be used.
Same database structure will be applied for all subsidiaries which have common data(in tables like country, city, agency, client, product), but at the same time, transactional data specific to each one on transactional tables.
Is it better to create a database for all common tables and another one for the other tables (with creating triggers , so that we have same data on all databases) OR Creating one database for all subsidiaries? OR is there another way?
Advantages and disadvantages ??
Thanks in advance,
Regards,
|
|
|
|
|
Myra,
This depends a lot on security, and how much data you need to proccess and what schema your data will be organised in.
I suggest you model your data then make a choice from there.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Thanks Frank,
I'll try to give you more details, maybe you would clarify the situation to me.
I have until now 4 subsidiaries for this holding, each one having to book spots for different TV channels.Each subsidiary have one or more channels.A user should be related to one subsisiary and can only view and work with the booking orders of this subsisiary. However, he can have access to product , client, agency tables which are shared by all users in the holding. cause u know that, for instance, the agencies and the products are the same.
as an example, one subsidiary could have booked around 300 000 spots per year, for 5 channels.Another one , having one channel, 60 000 records for booking Order per year
I'm a little bit lost 
Thanks in advance
|
|
|
|
|
Basically I cannot connect (in certain circumstances to SQL Server 2005 (Express) on my laptop (from my laptop)).
Configuration:
Window XP SP2 (not part of domain)
SQL Server 2005 Express
Visual Studio .NET 2005 Standard
SQL Management Studio Express
The service is running as is the SQL Browser Service
I can connect to the server using SSME (Management Studio) but not using SqlConnection nor osql nor sqlcmd nor VS2005 Server Explorer.
Shared Memory, Named Pipes and TCP/IP connections are enabled and I've been trying to log in through Windows Authentication (work requires it although not essential for development).
Does anybody have any ideas on what might be causing this lack of connectivity, I've run through the articles in the MS KB on configuring the XP firewall, even tried disabling it with no luck, still same error message:
<span name="intelliTxt" id="intelliTxt" style="margin-top: -15px;"></span>An error has occurred while establishing a connection to the server. When connecting to SQL Server
2005, this failure may be caused by the fact that under the default settings SQL Server does not
allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection
to SQL Server). Which reminds me remote connections are enabled in SSME.
Don't know what's going on since it was running under Win2K just fine (BTW removing SP2 is unfortunately not an option).
|
|
|
|
|
What is the status of windows firewall? Is port 1433 open for SQL server TCPIP connections?
What does your connection string look like?
|
|
|
|
|
Firewall is enabled, added the default instance of sqlservr.exe (only one running) to the exceptions list and opened port 1433 as well but to no avail.
Note I have also tried disabling the firewall as well but that doesn't seem to do anything either, what's bugging me is that SSME can connect but nothing else, not even VS.
Connection strings tried:
Data Source=(local);Initial Catalog=MIDAS;Integrated Security=true;
Data Source=seafire;Initial Catalog=MIDAS;Integrated Security=true;
Data Source=<ipaddress>;Initial Catalog=MIDAS;Integrated Security=true; Also tried variations using an SQL authentication but that doesn't seem to work.
On a different note another possibility would be if I could get the following scenario to work.
SQL 2005 on Win2K with ZoneAlarm as firewall (at least this is easy to configure).
Accessing from WinXP SP2 from laptop.
Both can be (and are I think) part of the same workgroup but there is no domain involved. IP address of desktop with Win2K is fixed, laptop is assigned dynamically from the router through which we access our broadband.
Either is a possibility, but using SQL on laptop is preferable (but only slightly to accessing it from the desktop).
Many thanks
|
|
|
|
|
Fixed it (I think), changed the connection string to use Data Source=SEAFIRE\SQLEXPRESS. At least I got a different error which showed that the connection was thrown out by SQL because of permissions so I'm correcting these and hopefully it should work.
Looks like it's not a default instance as I thought it was.
|
|
|
|
|
I have created a database table with 5 columns in VS 2005 and I want the first column to autoincrement. I have set the Is Identity property of the column to yes and set the Identity Increment and Identity Seed properties to 1. In my codebehind, when I make the call this.TableAdapter.Insert(1, 1, DateTime.Now, 0, 0) and run it, I get this error message: Cannot insert explicit value for identity column in table 'Table' when IDENTITY_INSERT is set to OFF. What do I need to pass in as the first argument into the Insert function to get the auto increment to work? Any help would be appreciated, thanks.
|
|
|
|
|
Try passing a DbNull as the value for the identity column. Alternatively, change the commandtext for the adapters's insertCommand to simply omit the first column.
|
|
|
|
|
I have a C++ application that uses ADO to connect to a JET database. It uses MDAC available in most Windows operating systems to support this. The application checks for MDAC 2.71 and if it is an earlier version in the computer, stops the program and asks the user to update their MDAC ( Have customers who still use Win98). So far so good.
How do I proceed with Windows Vista - it does not have MDAC - has Windows DAC. Apparently does not support JET - does this mean Access/Jet programs don't work any more? MSDN says support to ADO is built in - does this mean, I just don't bother to check for MDAC if it is Vista & all my current calls to ADO will work (Even though the database is a JET database)?
I would appreciate any and all input.
Thanks in advance.
|
|
|
|
|
consider a payment table with the following fields: billno,invid,amount,date
Also consider tables invoices (contains invid,invno,pkgid) and packages (contain pkgid)...
I need to generate bill for a particular invoice number(invno). That invoice may contain more than 1 packages. I have to generate bill for each packages with a same bill no. If there are 3 packages in invoice number 'I31' then 3 bill must be generated.
How to insert this using package or someother method? I'll pass thes values (billno,invno, and date) from my application to SQL server. The insert command must generate no. of bills that is equal to the number of packages of that invoice. The no. of pkgid can be retrieved by invno, the amount can be retrived by using the pkgid.
Please help me to insert multiple records from one request.
Hope you understand my prob.
Thanks.
|
|
|
|
|
Is is possible to add variables to functions in SQL Server. My issue is I am buildint a dashboard and I am sendint the variables directly from the dropdown menu.
what I need to do is have the ability to modify the Datediff function. By changing the type.
i.e
datediff(@dateRange, getdate())=0
I need to have the @dateRange be a variable instead of (dd, ww, or yy).
Any Ideas?
thanks
|
|
|
|
|
oskardiazdeleon wrote: i.e
datediff(@dateRange, getdate())=0
That's not a legal call to DATEDIFF[^] See the linked documentation for what you actually need.
oskardiazdeleon wrote: Any Ideas?
CASE[^]
CASE @dateRange WHEN 'dd' THEN DATEDIFF(dd, GETDATE(), @someOtherDate)
WHEN 'ww' THEN DATEDIFF(ww, GETDATE(), @someOtherDate)
WHEN 'yy' THEN DATEDIFF(yy, GETDATE(), @someOtherDate)
ELSE 0 END
You could then potentially wrap this in a function[^], however, since GETDATE() is non-deterministic it isn't possible to do what you want, but you could always pass GETDATE() into the function.
e.g.
CREATE FUNCTION DateDifference(@dateRange CHAR(2), @startDate DATETIME, @endDate DATETIME)
RETURNS INT
AS
BEGIN
RETURN CASE @dateRange WHEN 'dd' THEN DATEDIFF(dd, @startDate, @endDate)
WHEN 'ww' THEN DATEDIFF(ww, @startDate, @endDate)
WHEN 'yy' THEN DATEDIFF(yy, @startDate, @endDate)
ELSE 0 END
END
It can be called like this:
SELECT dbo.DateDifference(@dateRange, @someDate, GETDATE())
Does this help?
|
|
|
|
|
Does anyone have any experience with creating data dictionaries for Sql Server?
Our corporation needs to setup a data dictionary to store all our db info however currently, we're just storing it in a spreadsheet. What's the global practice for creating a corporate Data Dictionary?
Or does anyone perhaps know of an online tutorial that explains this?
Thanks.
-Goalie35
|
|
|
|
|
Have you searched Microsoft's web site for database tutorials, if not, then please do, however ...
For non-microsoft produced tutorials about Databases, there are many web sites that can help, here is one you might find helpful ...
http://www.geekgirls.com/menu_databases.htm[^]
and here are some video tutorials
http://www.learnsqlserver.com/VideoTutorials/[^]
Also, make a search here at Code Project, there are many database tutorials at various levels
modified 1-Aug-19 21:02pm.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
