|
I'm sure this can be done without all these subqueries - I've never even tried to put a sub-query in the columns list of a SELECT before and I have no idea if it would work.
Anyway, I think that something like this would be best:
SELECT Products.ProductID,
SUM(Sales.Sold_Qty) AS T_Sold_Qty,
SUM(SalesReturn.Returned_Qty) AS T_Returned_Qty
FROM Products
INNER JOIN Sales ON Sales.ProductID = Products.ProductID
INNER JOIN SalesReturn ON SalesReturn.ProductID = Products.ProductID
GROUP BY Products.ProductID
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I used this query at actual senerio.
It is too time consuming. It took 5 min approximatly for 10 to 50 records in each table.
Table: Products, InvoicesDetails, PurchasesDetail, SalesReturnsDetails, PurchasesReturnsDetails
SELECT DISTINCT Products.ID, (SELECT SUM([InvoicesDetails.SoldQuantity]) As T_SoldQty FROM InvoicesDetails WHERE Products.ID=InvoicesDetails.ProductID) AS T_SoldQty_, (SELECT SUM([PurchasesDetail.PurchasedQty]) As T_PurchQty FROM PurchasesDetail WHERE Products.ID=PurchasesDetail.ProductID) AS T_PurchQty_, (SELECT SUM([SalesReturnsDetails.ReturnedQty]) As T_SRetQty FROM SalesReturnsDetails WHERE Products.ID=SalesReturnsDetails.ProductID) AS T_SRetQty, (SELECT SUM([PurchasesReturnsDetails.ReturnedQty]) As T_PRetQty FROM PurchasesReturnsDetails WHERE Products.ID=PurchasesReturnsDetails.ProductID) AS T_PRetQty
FROM Products, InvoicesDetails, PurchasesDetail, SalesReturnsDetails, PurchasesReturnsDetails;
Thanks for your reply, I will check your method after transforming into the actual senerio.
Thanks Again!
Shoaib Nawaz
|
|
|
|
|
SELECT Products.ProductID,
SUM(Sales.Sold_Qty) AS T_Sold_Qty,
SUM(SalesReturn.Returned_Qty) AS T_Returned_Qty
FROM Products
INNER JOIN Sales ON Sales.ProductID = Products.ProductID
INNER JOIN SalesReturn ON SalesReturn.ProductID = Products.ProductID
GROUP BY Products.ProductID
I have tried but it is problematic. (Sentax error)
Shoaib Nawaz
|
|
|
|
|
Sorry, I don't see it - Where did SQL Server say there was a syntax error?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I have a set of querys that pull data from a bunch of tables in a database and return totals for important datapoints for our company (ie Total Revenue for the day, for the month...quarter and so on). I have build a windows "dashboard" to display these values and auto refresh every 5 minutes. The problem with this is if 10 or so people have this installed on their system, the database is getting constantly hammered every 5 minutes and pulling from our most important tables. There has got to be a better way to do this and I figured the smart readers of codeproject would give me a suggestion or advice.
Thanks
JGA
|
|
|
|
|
When people learn about relational databases there is a lot of emphasis put on normalisation of the data model. However, like all optimisations, it really depends on the situation. Obviously you have a data model and it works reasonably well for most of the things that you do. However, a highly normalised data model is inefficient for reporting.
What I suggest is to create a report table (or tables) that contain duplicate information to the main database, however these tables are flattened so that hold data in the format that your SELECT statement is currently creating. For example. Say you have a SELECT statement being kicked off for your "dashboard" like this
SELECT A.C1, A.C2, B.C3, C.C4, C.C5, D.C6<br />
FROM A<br />
INNER JOIN B ON A.PKA = B.FKA<br />
INNER JOIN C ON A.PKA = C.FKA<br />
INNER JOIN D ON C.PKC = D.FKC
You create a single reporting table that contains C1..C6 in a flattened format. You have a process that once every 5 minutes updates the report table. All your "dashboard" users access the report table meaning that your main database is hit only once per 5 minutes for the data and the users access the fast tables that are pre-formatted for their needs.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
yeah, that does help. Thanks!
|
|
|
|
|
Hey,
I have sql script files that when loaded into sql query analizer work as expected.
How do I load and execute the entire contents of the file from with in a C# windows
app.
I tried
m_cConn = new SqlConnection();
m_cConn.ConnectionString = sConnStr;
m_cConn.Open();
m_cCmd = new SqlCommand(sSQL);
m_cCmd.Connection = m_cConn;
m_cCmd.ExecuteNonQuery();
m_cConn.Close();
etc..
The sSQL is a string with the contents of the sql file loaded into it.
The file contains DB create / drop, table create / drop, procedure create / drop ,
use, update and insert statements.
Thanks in advance.
For Example:
--create the database
use master
declare @DBName varchar(50)
set @DBName = '[NetPortalDB]'
if exists(select * from sysdatabases where name=@DBName)
drop database [NetPortalDB]
create database [NetPortalDB]
GO
use [NetPortalDB]
-- Adding downtime login
if not exists (select * from master.dbo.syslogins where loginname = N'downtime')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'us_english'
if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
select @logindb = N'master'
if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
select @loginlang = @@language
exec sp_addlogin N'downtime', N'downtime', @logindb, @loginlang
END
GO
-- Adding processmore login
if not exists (select * from master.dbo.syslogins where loginname = N'processmore')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'us_english'
if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
select @logindb = N'master'
if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
select @loginlang = @@language
exec sp_addlogin N'processmore', null, @logindb, @loginlang
END
GO
if not exists (select * from dbo.sysusers where name = N'downtime' and uid < 16382)
EXEC sp_grantdbaccess N'downtime', N'downtime'
GO
if not exists (select * from dbo.sysusers where name = N'processmore' and uid < 16382)
EXEC sp_grantdbaccess N'ProcessMORe', N'processmore'
GO
then goes on to create tables and populate them
|
|
|
|
|
The GO is not part of SQL. It is used within Query Analyser to delimit batches.
I put together a little application that will allow you to process files with GO delimiters in them within .NET. You can find it on my blog[^].
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
-- modified at 4:03 Thursday 6th October, 2005
|
|
|
|
|
Hello,
I have a stored procedure called P_DATE_TO_MS that takes 2 parameters DATE & MILLISECOND and I need to call this procedure from an update statement:
conConnection.Execute ("UPDATE table SET CURRENT_TIME_IN_MS = P_DATE_TO_MS(getDate()) WHERE id=123")
It looks like the stored procedure returns the information in the second variable (ByRef?) and not as a return value. I'm calling the statement through ADODB.Connection object
and not in the SQL directly.
Please correct me if my assumption is incorrect, I'm not an SQL guy and I'm just getting into SQL
Can anyone tell me if this is possible or how to do it.
here is the store procedure:
CREATE PROCEDURE P_DATE_TO_MS
@DATE DATETIME,
@MILLISECOND DECIMAL OUTPUT
SET @MILLISECOND = TIME_IN_MS
END
GO
Thank you in advance
ilya
|
|
|
|
|
Hi,
I think a user defined function would work better for what you are trying to do (a SCALAR FUNCTION in this case)
CREATE FUNCTION dbo.udfGetSeconds (@Days INT)
RETURNS INT
AS
BEGIN
DECLARE @Seconds INT
SET @Seconds = @Days * 24 * 60 * 60
RETURN @Seconds
END
Then use it as follows
SELECT dbo.udfGetSeconds(5)
(edit)
or in an UPDATE statement
UPDATE [Table] SET [column] = dbo.udfGetSeconds(5)
(edit)
The man who smiles when things go wrong has thought of someone he can blame it on.
If you tell a man there are 300 billion stars in the universe, he'll believe you. But if you tell him a bench has just been painted, he'll have to touch it to be sure.
-- modified at 8:03 Thursday 6th October, 2005
|
|
|
|
|
I need to write a script so that I will be able to import and export data from a MS Access database.
What I really need to do is export the exisiting data from the table, re create the table with a few added columns and export the data back.
Any help and suggestions would be greatyl appreciated.
Thanks,
Susmita
|
|
|
|
|
Hello everyone,
I've just written my first stored procedure and maybe gotten in a little over my head. It's not exactly working out the way I thought; essentially, what I want to do is select a few fields from one table set of rows that meets the SELECT criteria and also where the identifying number is not already in the second table, and then perform a few operations on some of the fields and subsequently insert everything into the second table. I've been told a cursor would assist in this, but I have yet to find a good and clear tutorial/example on T-SQL cursors. Just so you can get a better idea of what I'm talking about, here is my stored procedure thus far:
<br />
CREATE PROCEDURE sp_UpdateLeaders<br />
AS<br />
DECLARE<br />
-- prep all variables<br />
@YspCalc money, <br />
@FeeCalc money, <br />
@Fees money, <br />
@Amount money, <br />
@Ysp money,<br />
@Fee1 money, <br />
@Fee2 money, <br />
@Fee3 money, <br />
@Fee4 money, <br />
@Fee5 money, <br />
@Fee6 money,<br />
@Number varchar(50), <br />
@McName varchar(50), <br />
@Client varchar(50), <br />
@Officer varchar(50),<br />
@FName varchar(25), <br />
@LName varchar(25),<br />
@Date datetime,<br />
@Points integer, <br />
@LoanType integer, <br />
@Mortgage integer<br />
<br />
-- get info from bors<br />
SELECT <br />
TLH_1_68=@FName, TLH_1_69 = @LName, TLH_1_19 = @LoanType, <br />
TLH_1_748 = @Date, TLH_1_2 = @Amount, TLH_2_58 = @McName, <br />
TLH_1_420 = @Mortgage, TLH_1_869 = @Ysp, TLH_1_2 = @Amount, <br />
GRICA_1_2 = @Fee1, GRICA_1_4 = @Fee2, GRICA_1_6 = @Fee3, <br />
GRICA_1_10 = @Fee4, GRICA_1_12 = @Fee5, GRICA_1_8 = @Fee6,<br />
TLH_1_317 = @Officer, TLH_1_876 = @Number<br />
FROM MakeDaily53.DBO.bors<br />
WHERE TLH_1_749 Is NULL <br />
AND Filename NOT LIKE '%.2'<br />
-- only for the following branch codes<br />
AND TLH_1_1007 IN ('645','644','642','638','636','599','598','592','589','587','586','582','581','580','579','565','112')<br />
<br />
Print('Before')<br />
<br />
-- check for existing loan in leaderboard<br />
IF NOT EXISTS(SELECT * FROM LeaderBoard WHERE LoanNumber = @Number)<br />
BEGIN<br />
<br />
Print(@Number)<br />
<br />
-- check for nulls<br />
IF @Fee1 = Null<br />
SET @Fee1 = 0<br />
IF @Fee2 = Null<br />
SET @Fee2 = 0<br />
IF @Fee3 = Null<br />
SET @Fee3 = 0<br />
IF @Fee4 = Null<br />
SET @Fee4 = 0<br />
IF @Fee5 = Null<br />
SET @Fee5 = 0<br />
IF @Fee6 = Null<br />
SET @Fee6 = 0<br />
<br />
-- concatenate client name<br />
SET @Client = @FName + ' ' + @LName<br />
-- ysp+rsp caluclation<br />
SET @YspCalc = (((@Ysp - 100)/100)*@Amount)<br />
-- additional fees are calulated differently for 1st and 2nd mortgages<br />
IF (@Mortgage = 1)<br />
SET @FeeCalc = ((@Fee1 + @Fee2 + @Fee3 + @Fee4 + @Fee5 + @Fee6)-1425)<br />
IF (@Mortgage = 2)<br />
SET @FeeCalc = ((@Fee1 + @Fee2 + @Fee3 + @Fee4 + @Fee5 + @Fee6)-275)<br />
<br />
-- insert into leaderboard<br />
INSERT INTO LeaderBoard (ClientName, LoanType, LoanNumber, ClosedDate, LoanAmount, YspSrp, Points, AddFees, McName, MortgageType, LoanOfficer)<br />
VALUES (@Client, @LoanType, @Number, @Date, @Amount, @YspCalc, @Points, @FeeCalc, @McName, @Mortgage, @Officer)<br />
<br />
END<br />
<br />
-- go!<br />
GO
Any help is greatly appreciated. Thanks in advance!
-------------------
abort, retry, fail?
|
|
|
|
|
See the comments in you're modified source code to see what is going on.
CREATE PROCEDURE sp_UpdateLeaders
AS
DECLARE
-- prep all variables
@YspCalc money,
@FeeCalc money,
@Fees money,
@Amount money,
@Ysp money,
@Fee1 money,
@Fee2 money,
@Fee3 money,
@Fee4 money,
@Fee5 money,
@Fee6 money,
@Number varchar(50),
@McName varchar(50),
@Client varchar(50),
@Officer varchar(50),
@FName varchar(25),
@LName varchar(25),
@Date datetime,
@Points integer,
@LoanType integer,
@Mortgage integer
-- Declare the cursor. This sets up the select statement that contains
-- the rows of data we want to iterate over. (Note: I've taken out your
-- assignments to the local variables - you'll see later why)
DECLARE bors_cursor CURSOR FOR
SELECT
TLH_1_68, TLH_1_69, TLH_1_19,
TLH_1_748, TLH_1_2, TLH_2_58,
TLH_1_420, TLH_1_869, TLH_1_2,
GRICA_1_2, GRICA_1_4, GRICA_1_6,
GRICA_1_10, GRICA_1_12, GRICA_1_8,
TLH_1_317, TLH_1_876
FROM MakeDaily53.DBO.bors
WHERE TLH_1_749 Is NULL
AND Filename NOT LIKE '%.2'
AND TLH_1_1007 IN ('645','644','642','638','636','599','598','592',
'589','587','586','582','581','580','579','565','112')
-- Now open the cursor
OPEN bors_cursor
-- Collect the first row of data into the variables that will be used
-- Note: You had two different columns being assigned to @Amount, I guess one
-- of these is incorrect.
FETCH NEXT FROM bors_cursor
INTO @FName, @LName, @LoanType, @Date, @Amount, @McName,
@Mortgage, @Ysp, @Amount, @Fee1, @Fee2, @Fee3, @Fee4,
@Fee5, @Fee6, @Officer, @Number
-- @@FETCH_STATUS tells us if the previous fetch operation went okay
-- Note: This is regardless of the number of cursors you have. It always
-- corresponds to the most recent FETCH command that was executed so make
-- sure you check it after each fetch operation. A status of 0 means
-- it went okay. So here, while we keep getting rows then go into the loop.
WHILE @@FETCH_STATUS = 0
BEGIN
-- check for existing loan in leaderboard
IF NOT EXISTS(SELECT * FROM LeaderBoard WHERE LoanNumber = @Number)
BEGIN
-- You'll recognise this code - I copied it unchanged from your post.
Print(@Number)
-- check for nulls
IF @Fee1 = Null
SET @Fee1 = 0
IF @Fee2 = Null
SET @Fee2 = 0
IF @Fee3 = Null
SET @Fee3 = 0
IF @Fee4 = Null
SET @Fee4 = 0
IF @Fee5 = Null
SET @Fee5 = 0
IF @Fee6 = Null
SET @Fee6 = 0
-- concatenate client name
SET @Client = @FName + ' ' + @LName
-- ysp+rsp caluclation
SET @YspCalc = (((@Ysp - 100)/100)*@Amount)
-- additional fees are calulated differently for 1st and 2nd mortgages
IF (@Mortgage = 1)
SET @FeeCalc = ((@Fee1 + @Fee2 + @Fee3 + @Fee4 + @Fee5 + @Fee6)-1425)
IF (@Mortgage = 2)
SET @FeeCalc = ((@Fee1 + @Fee2 + @Fee3 + @Fee4 + @Fee5 + @Fee6)-275)
-- insert into leaderboard
INSERT INTO LeaderBoard (ClientName, LoanType, LoanNumber, ClosedDate, LoanAmount, YspSrp, Points, AddFees, McName, MortgageType, LoanOfficer)
VALUES (@Client, @LoanType, @Number, @Date, @Amount, @YspCalc, @Points, @FeeCalc, @McName, @Mortgage, @Officer)
END
-- Note: This fetch statement is exactly like the one just before the
-- loop starts.
FETCH NEXT FROM bors_cursor
INTO @FName, @LName, @LoanType, @Date, @Amount, @McName,
@Mortgage, @Ysp, @Amount, @Fee1, @Fee2, @Fee3, @Fee4,
@Fee5, @Fee6, @Officer, @Number
END
-- Clean up. Cursors are terribly messy
CLOSE bors_cursor
DEALLOCATE bors_cursor
-- We're done!
-- go!
GO
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Thanks! After ironing out a few type mismatches, that worked like a dream!
I was a hesitant to use a cursor because I've heard bad things about them, but this technique was ordained by those higher up the ladder than myself, so I figured now is as good of a time as any to learn.
Thanks again!
-------------------
abort, retry, fail?
|
|
|
|
|
jszpila wrote:
I was a hesitant to use a cursor because I've heard bad things about them
Yes, I would say that cursors are useful in a small number of situations. It is something that once learned is a difficult habit to get out of because people are used to procedural approaches that are used in more traditional languages such as C/C++/C#/VB/Pascal/Java and so on. SQL is a set-based language and so it operates on sets of data more efficiently and if you can keep it that way then all the better.
Now that you know how to use cursors, my advice is to put that knowledge away and not touch it unless you really must use it as there is no other option. Just about every resource I've read on the subject has said how awful and inefficient they are - and I don't mean gaining a few milliseconds here or there. I mean stored procedures going from taking hours with cursors to a few seconds to do the same thing without.
Like I said, I think your problem can be solved without using cursors - but it would probably take a little bit longer to think through. However, the benefits at the other end are so much better.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I'm searching for a good forum to ask questions, exchange ideas etc regarding the creation on data providers. Not using them, making/creating/developing them..
I'm responsible for creating the API-layers for my employers IMDBMS, so far we have written a JDBC-driver, OLEDB-driver and a .NET Data Provider for .NET 1.1.
Problem is that our .net driver that is working using VS2003 and .net 1.1, but will not work in VS2005 .net 2.0. Thanks Bill, appriciate it..not! 
So I'm desperatly looking for a nice place/forum to ask questions, getting ideas etc concerning this.
I've tried the ".NET Framework Data Access and Storage" forum at forums.microsoft.com/msdn, but they seem to think I'm trying to use the SQL Servers data provider, not my own.
Has anyone seen such a place?
TIA/
Michael Hulthin
|
|
|
|
|
Hi I have reports created in ms Access database. Do you know
how to use code in VB.NET so that I can launch the access database and then open up the reports?
Could you give me some sample codes please? thanks
dw1928
|
|
|
|
|
I have what I hope is a simple question. I'm trying a product called RapTier from Sharp Power that generates a data access layer. One of the options it has is to produce stored procedures from my database. I've looked through the generated stored procedures, and they seem pretty good.
My problem is this, I have the stored procedures as a .sql file, and I have to figure out how to actually attach/add them to the database that I have (I'm using MSDE 2000).
Can anyone give me a hand on how to do this?
Thanks.
The ends can never justify the means. It is the means that determine the ends.
|
|
|
|
|
You can actually hurl any SQL (including a code-generated script) through a regular SqlCommand object.
A few months ago I wrote a simple application to demonstrate that - It picks up a file and dumps the contents to a SQL Server via the SqlCommand object in .NET
You can find the download here[^]
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
OK, I don't really understand your response, to tell the truth. I'll go to your site and take a look at the utility, maybe it can help.
What I'm really looking for (I think) is just the command I need to feed OSQL to get it to go find my .sql file and add it to the database. I'm not sure if that is equivalent to "hurl[ing] any SQL through a regular SqlCommand object" or not.
Thanks for the reply though!
The ends can never justify the means. It is the means that determine the ends.
|
|
|
|
|
I think what he meant was "why don't you just go read the .sql file into the commandtext property of a sql command object and executenonquery". The only thing you need to be careful of is "go", but I also read in another post that colin wrote a utility to help with sql containing "go" in it.
|
|
|
|
|
If it's a local instance of MSDE, use the following command:
osql -E -i filename.sql The -E switch indicates to use Windows authentication, and the -i filename.sql indicates which file contains the statements to run. Run osql /? to see other options.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
I use ADO.NET to execute sql. But it takes a lot of time, so after 30 seconds program show error message "Timeout expired!" (althrough I declare "Connect Timeout=600" in connect string). How to use SqlConnection without Timeout or with a long time?
Please help me!!!
Thanks!
|
|
|
|
|
There are two timeouts. The one you are using is for the connection. In other words, how long should the .NET application wait to connect (not do any work, just connect) to the database.
The other timeout it on the command object, CommandTimeout. This timeout is the amount of time that the .NET application will wait for it to perform what ever command you sent it.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
