Two Sums From Different Tables
Jul 29, 2004
I have this query:
select Customers.CustomersID, Customers.name, sum(table1.amount), sum(table2.amount)
from Customers, table1, table2
where Customers.CustomerID = table1.CustomerID
and Customers.CustomersID = table2.CustomerID
group by Customers.CustomersID, Customers.name
I am trying to make a query to report the total amount of two different things from two different tables by customer. the problem is that the total amount from table1 doubles if there are two rows of that customer in table2. My guess is that I have to group the things in a different way, but I don't know how.
Any suggestions?
View 1 Replies
ADVERTISEMENT
Jun 17, 2015
I am using sql server and I have a table called accnt with the fields ven1 and amnt1 and a table called acc1167 with fields ven, job#, and amnt. for this example these tables look like this
    accnt              acc1167
  ven1   amnt1        ven   job#  amnt
  1167   100         1167   1    200   Â
  1152   50          1167   2    300
  1167   110         1167   3    100
  1167   300         1167   4    200
  1252   1050        1167   5    200
  1167   210         1167   6    150
  1167   1150Â
  1167   130Â
  2113   800Â
  1167   550
  1167   1200
I need to sum amnt1 for all the records in accnt with the ven1 of 1167, we will call this sumA. Then sum amnt in acc1167 for all records, we will call this sumB. next I need to divide sumB by sumA to get a ratio. finally I need to multiply each amnt value from acc1167 by the ratio and get a number that will then replace the acc1167 amnt value.
for example, sumA = 3750, sumB = 1150. taking these values, sumB/sumA = 0.307. I then replace every value in acc1167 amnt with 0.307*itself, so the final table should look like this:
     acc1167
  ven  job#   amnt
  1167  1    61.4
  1167  2    92.1
  1167  3    30.7
  1167  4    61.4
  1167  5    61.4
  1167  6    46.05
i have tried to use the sum function and and some insert, but i am very new to SQL and have never used sum before and don't know how to call from multiple tables, or how to store a ratio. Ive tried this:
  UPDATE   acc1167
  sum1 = sum amnt1 where ven1 = '1167'
  from accnt
  sum2 = sum amnt
  from accnt
  SET     amnt = sum2/sum1*amnt
  FROM    acc1167
View 2 Replies
View Related
Oct 19, 2005
Hello, any help is appreciated.Here is what I’m trying. I want a month to date total of bookingsbased on a fiscal month.I can isolate my records for the month and sum up the days so I windup with a list of days of the month with their bookings total forthat day. Like this10/1/05 - $100010/2/05 - $500010/3/05 - $2000And so on thru the month.But I want the sum of month 10.I have a line of code like “select sum(EXT_AMT) where month = “10””What I need is sum(sum(EXT_AMT).And that does not work.Any suggestions? I thought to throw the results into a table then sumthe table.Not sure how to do that either! I'm frustrated! Any help isappreciated.Thanks, Duane
View 2 Replies
View Related
Feb 21, 2008
Ok I have two tables
clients and expenses
enpense table columns: clientid, expensetype, expensevalue
clients.clientid and expenses.clientid relate the tow tables on an inner join
what i have is one client that will have multiple expense records in the expense table
cleint1, expensetype1, expensevalue1
cleint1, expensetype2, expensevalue2
cleint1, expensetype3, expensevalue3
what i need to output is client.*, sum(expensevalues), and also expensevalue1, expensevalue2, expensevalue3 as one output line
how do I get this?
View 3 Replies
View Related
Jul 23, 2005
Hi AllI'm really stuck on this one so would appreciate any help you can give.In essence, I have 1 SQL 2000 table with rows of data logging stockmovement. To differenciate between a stock sale and a stock receipt thetable has a TRANSACTIONTYPE field so that 8,7 equal invoices and 3 equals areceipt.I've been asked to report on this data by suming the total qty used oninvoices and the total qty recvd for each stock item, but I can't figure outhow I sum the same rows twice in the one query.For example, my query is as follows:select st.stockid as 'STYLE',s.picture as 'COLOUR','' as 'IN FIRST IN LAST WEEK','' as 'THIS WEEK IN','' as 'TOTAL IN','' as 'OUT FIRST OUT LAST WEEK',SUM(st.quantity) as 'THIS WEEK OUT','' as 'TOTAL OUT','' as 'REMAINING','' as 'TOTAL DIGESTION %'from stocktransactions st, stock swhere st.stockid = s.stockid andst.transactiontype in (8,7) andst.transactiondate >= '2005-07-12 00:00:00' andst.transactiondate <= '2005-07-12 23:59:59'group by st.stockid,s.pictureorder by st.stockidApart from the 'THIS WEEK OUT' column SUMing all of the stock sales bytransactiontype 7,8, I also want the 'THIS WEEK IN' column to SUM all of thetransactions by transactiontype 3, so that I get the following results:STYLE COLOUR .... THIS WEEK IN .... THIS WEEK OUT .......IVP Red 12 23STP Blue 4 15etc etcMy problem is that I don't want to exclude a stock item if it hasn't got arow/value for the THIS WEEK IN and/or the THIS WEEK OUT. Am I asking toomuch of SQL?My table schemas are as follows:create table STOCKTRANSACTIONS(STOCKTRANSACTIONID T_STOCKTRANSACTIONSDOMAIN not nullidentity(1,1),TRANSACTIONTYPE smallint not null,TRANSACTIONDATE datetime null ,REFERENCE varchar(40) null ,Comment varchar(255) null ,STOCKID T_STOCKDOMAIN null ,DESCRIPTION varchar(255) null ,UNITOFSALE varchar(20) null ,WAREHOUSEID T_WAREHOUSESDOMAIN null ,PEOPLEID T_PEOPLEDOMAIN null ,AccountID T_AccountsDomain null ,AgentID T_AgentsDomain null ,PLRate float null ,CONTACTID T_CONTACTDETAILSDOMAIN null ,JOBID T_JOBSDOMAIN null ,QUANTITY float null ,CURRENCYID T_CURRENCIESDOMAIN null ,SELLINGPRICE float null ,DISCOUNTPERCENT float null ,COSTPRICE float null ,MINIMUMPRICE float null ,TILLID T_TILLSDOMAIN null ,UserID T_UsersDomain null ,ClockDate DateTime null ,TimeStamp TimeStamp ,constraint pk_stocktransactions primary key (STOCKTRANSACTIONID))gocreate table STOCK(STOCKID T_STOCKDOMAIN not null,NAME varchar(40) not null,PICTURE varchar(40) null ,WEIGHT float null ,VOLUME float null ,BARCODE smallint null ,NumberOfPriceBreaks SmallInt not null default 1,STOCKCATEGORYID T_STOCKCATEGORIESDOMAIN null ,SALESNOMINALID T_NOMINALACCOUNTSDOMAIN null ,PURCHASENOMINALID T_NOMINALACCOUNTSDOMAIN null ,SELLINGCOMMENT varchar(255) null ,INCLUDESELLINGCOMMENT TinyInt null ,DISPLAYSELLINGCOMMENT TinyInt null ,COSTCOMMENT varchar(255) null ,DISPLAYCOSTCOMMENT TinyInt null ,PRODUCTTRACKING smallint null ,ITEMTYPE smallint null ,VALUATIONPRICE float not null default0.00 ,INCLUDEINCUSTOMERSTURNOVER TinyInt null ,INCLUDEINAGENTSTURNOVER TinyInt null ,SUPERCEDED TinyInt null ,SUPERCEDEDBY T_STOCKDOMAIN null ,SUPPLIERID T_PEOPLEDOMAIN null ,SUPPLIERSTOCKID varchar(40) null ,SUPPLIERCOMMENT varchar(255) null ,NEXTSERIALNUMBER int null ,SERIALNUMBERLENGTH smallint null ,SERIALNUMBERPREFIX varchar(10) null ,SERIALNUMBERSUFFIX varchar(10) null ,SERIALNUMBERPREFIXLENGTH smallint null ,SERIALNUMBERSUFFIXLENGTH smallint null ,TIMESTAMP timestamp not null,constraint pk_stock primary key (STOCKID))goThanksRobbie
View 3 Replies
View Related
May 13, 2008
Hello All,
I'm trying to do a query to produce multiple sums based on how many rows are in a table.
Here's a sample of the table and data, what I want is to have a query to sum each company's total and display it.
create table #invoices
(
InvoiceNumber varchar(5),
--other info
CompanyCode int,
InvoiceAmount real
)
Insert Into #invoices values('A1000', 1, 1000)
Insert Into #invoices values('A1000', 2, 100)
Insert Into #invoices values('A1000', 3, 300)
Insert Into #invoices values('A1000', 1, 600)
Insert Into #invoices values('A1001', 2, 2000)
Insert Into #invoices values('A1001', 3, 1000)
Insert Into #invoices values('A1001', 1, 300)
Insert Into #invoices values('A1002', 2, 2500)
Insert Into #invoices values('A1002', 3, 2000
I was thinking of doing it something like this:
Select
Sum(case when CompanyCode=1 Then CompanyCode End) as TOTAL1,
Sum(case when CompanyCode=2 Then CompanyCode End) as TOTAL2,
Sum(case when CompanyCode=3 Then CompanyCode End) as TOTAL3
from
#invoices
But I would rather not have to hard code the company numbers in the query as they can be added or removed from the list. Ideally it would take the CompanyCode from the COMPANY table and SUM each companies totals and display it.
Any help on this would be greatly appreciated!
Thanks,
View 3 Replies
View Related
May 12, 2006
Could someone explain to me, how I can get sum from row which I have values in 2 colums and I want the realtime sum to third column. Fourth colum is for item.
Also can someone tell me how to sum these third colums where the item is same so I have real time values for the item sum.
Thanks!
AD
View 1 Replies
View Related
May 5, 2006
How can I combine the 2 Sum amounts below. Basically teh 2 queries are exactly the same, just hitting 2 different tables (pdc and pdcdeleted) with the same structure:
SELECT SUM(PQuery.Amount) as PDCs_IL
FROM
(SELECT c.name,
c.customer,
(SELECT Top 1 fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code)
AS FeeSchedule,
m.branch,
pd.desk,
'PDC' AS Type,
pd.Active,
m.number,
pd.Amount,
CONVERT(money, 0) AS OverPaidAmt,
pd.OnHold
FROM Master m (NOLOCK)
LEFT JOIN pdc pd ON pd.number = m.number
INNER JOIN Customer c ON c.Customer = m.Customer
WHERE pd.Active = 1
AND m.Customer IN (SELECT Customer from Customer_DashboardGraphs where Illinois = 1)
AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @ProcessDate) + 1, @ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @ProcessDate), DATEADD(MONTH, 1, @ProcessDate)) AND pd.Entered <> '1900-01-01 00:00:00.000'
AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @ProcessDate) + 1, @ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @ProcessDate), DATEADD(MONTH, 1, @ProcessDate))
AND pd.Deposit IS NOT NULL
AND pd.OnHold IS NULL
AND c.customer <> '9999999'
) as PQuery
SELECT SUM(PQuery2.Amount) as PDCs_IL_deleted
FROM
(SELECT c.name,
c.customer,
(SELECT Top 1 fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code)
AS FeeSchedule,
m.branch,
pd.desk,
'PDC' AS Type,
pd.Active,
m.number,
pd.Amount,
CONVERT(money, 0) AS OverPaidAmt,
pd.OnHold
FROM Master m (NOLOCK)
LEFT JOIN pdcdeleted pd ON pd.number = m.number
INNER JOIN Customer c ON c.Customer = m.Customer
WHERE pd.Active = 1
AND m.Customer IN (SELECT Customer from Customer_DashboardGraphs where Illinois = 1)
AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @ProcessDate) + 1, @ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @ProcessDate), DATEADD(MONTH, 1, @ProcessDate)) AND pd.Entered <> '1900-01-01 00:00:00.000'
AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @ProcessDate) + 1, @ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @ProcessDate), DATEADD(MONTH, 1, @ProcessDate))
AND pd.Deposit IS NOT NULL
AND pd.OnHold IS NULL
AND c.customer <> '9999999'
) as PQuery2
View 1 Replies
View Related
Feb 27, 2008
I am creating a statistics page for our site. Using a very basic select statement, my query currently returns:
Select DateAdded, MTCreds, HICreds from tblStudentsAndCredits
DateAdded-MTCreds-HICreds-----------------------------------------1/1/2008 - 2 - 01/1/2008 - 0 - 41/2/2008 - 3 - 01/2/2008 - 2 - 41/3/2008 - 2 - 01/3/2008 - 0 - 3
Instead, I would like it sum up MTCreds and HICreds for each day and group them into something more usable like this:
DateAdded-MTCreds-HICreds-----------------------------------------1/1/2008 - 2 - 41/2/2008 - 5 - 41/3/2008 - 2 - 3
Thanks for any help - SQL is not my strong point.
View 3 Replies
View Related
May 1, 2001
here is the script im using.
SELECT dbo_ITEM.part_no, dbo_SALES_ORDER.entered_date_time, Sum(dbo_SALES_ORDER_ITEM.sales_price) AS SumOfsales_price
FROM dbo_ITEM INNER JOIN (dbo_SALES_ORDER INNER JOIN dbo_SALES_ORDER_ITEM ON dbo_SALES_ORDER.sales_order_id = dbo_SALES_ORDER_ITEM.sales_order_id) ON dbo_ITEM.item_id = dbo_SALES_ORDER_ITEM.item_id
GROUP BY dbo_ITEM.part_no, dbo_SALES_ORDER.entered_date_time
HAVING (((dbo_ITEM.part_no)="5030" Or (dbo_ITEM.part_no)="5040" Or (dbo_ITEM.part_no)="5050" Or (dbo_ITEM.part_no)="5060" Or (dbo_ITEM.part_no)="6014" Or (dbo_ITEM.part_no)="6016" Or (dbo_ITEM.part_no)="6017" Or (dbo_ITEM.part_no)="5071" Or (dbo_ITEM.part_no)="5081" Or (dbo_ITEM.part_no)="5091") AND ((dbo_SALES_ORDER.entered_date_time) Between [Enter Start Date] And [Enter End Date]))
ORDER BY dbo_ITEM.part_no;
If i delete the
((dbo_SALES_ORDER.entered_date_time) Between [Enter Start Date] And [Enter End Date]))
statement than the SUM function works, for some reason (no pun intended) when a date set is entered the SUM statement is inopperative. Any help would be much appriciated.
Thanks
Matthew
View 2 Replies
View Related
Aug 3, 2007
Hi,
I have a table with a userid and duration field. I need to contrast and individual against a group of users from the table. I can get the users cumulative hours by date in the select clause.
I am having a problem producing the group's sum of averages in the main query.I have tried it many ways and just cant get it to work. I need to be able to display something like this below:
Date GV IV
1/1/07 .45 .37
1/2/07 .56 .45
1/3/07 .68 .59
--
So Far I have this:
SELECT
D1.Date,
GV=ROUND(CAST(SUM(D2.Duration) AS FLOAT) / 3600,2),
IV=(SELECT ROUND(CAST(SUM(DurationAll)AS FLOAT) / 3600,2)
FROM IR_UserDaily WHERE UserID=@UserID AND Date<=D1.Date)
FROM
( SELECT Date,Duration=AVG(DurationAll)
FROM IR_UserDaily
WHERE
UserID IN(SELECT FilterID FROM IR_Filter WHERE ReportID=@RID)AND(Date BETWEEN @LowDate and @HighDate)
GROUP BY Date
) AS D1
INNER JOIN
( SELECT Date,Duration=AVG(DurationAll)
FROM IR_UserDaily
WHERE
UserID IN(SELECT FilterID FROM IR_Filter WHERE ReportID=@RID)AND(Date BETWEEN @LowDate and @HighDate)
GROUP BY Date
) AS D2
ON D2.Date < = D1.Date
WHERE (D1.Date BETWEEN @LowDate and @HighDate) AND (D2.Date BETWEEN @LowDate and @HighDate)
GROUP BY D1.Date,D1.Duration
ORDER BY D1.Date
The problem is that the avg function is returning the avg for all values up to the inner join condition, I think.
Can someone help me here I know it should be simple.
View 1 Replies
View Related
Feb 6, 2008
I currently have a pivot table query that is working great but I need to add to it. The below code is giving me the total ServiceTime per date. I need to split this service time out depending on the stage of this note. Basically I need to pivot table queries and join them together.
1) If the note has been signed
2) If the note has been signed and countersigned
SUM(CASE WHEN countersigned_id IS NULL AND signed_id IS NOT NULL THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) ELSE
0 END) as PendingServiceTime,
SUM(CASE WHEN countersigned_id IS NOT NULL THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) ELSE
0 END) as ApprovedServiceTime
How do I add this to my pivot table query and the dates are dynamic.
SELECT lastname + ', ' + firstname as FullName, [12/3/2007], [12/4/2007], [12/5/2007]
FROM (SELECT p.LastName, p.FirstName, t.ServiceDate,
ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) AS ServiceTime
FROM dbo.allNotes(8) AS t
LEFT JOIN dbo.note_Collateral_provider AS d ON d.note_Collateral_id = t.ID
LEFT JOIN dbo.Personnel as p ON d.personnel_id = p.ID
LEFT JOIN dbo.Clients as c on t.ClientID = c.ID
LEFT JOIN fPayor(8) fp on fp.noteId = t.id and fp.dbTable = 'collateral'
LEFT JOIN dbo.payor py ON py.ID = substring(fp.fPayorName, 41, 19)
LEFT JOIN dbo.payorinfo pyInfo ON pyInfo.ID = py.payorinfoid
WHERE t.AgencyID = 8 AND t.tableName = 'collateral'
AND t.not_billable_reason_id IS NULL AND VOID_ID IS NULL
AND ((t.signed_id IS NOT NULL AND t.countersigned_id IS NULL) OR (t.countersigned_id IS NOT NULL))
AND t.ServiceDate BETWEEN CONVERT(DATETIME, '12/03/2007') AND CONVERT(DATETIME, '12/05/2007')
) rs Pivot (SUM(rs.ServiceTime) FOR rs.ServiceDate IN ([12/3/2007], [12/4/2007], [12/5/2007]
View 1 Replies
View Related
Oct 18, 2007
I am trying to write a query that gets daily sales for each of our stores plus WTD and MTD totals
columns would be
Store date daily_total wtd_total mtd_total
the query need to produce a row per store per day and have the wtd sum reset for each week so that it
sums up sales day by day for that week
Is there a good way to do this?
View 3 Replies
View Related
Jun 10, 2006
Hi
Im new in this forum so don't if this is the right place to post these kind of questions..
Im using vb2005 and have made a program that deals with customers and payments.
I have a table called acount. Roughly it contains these columns
Number, amount, type..
type can be a payment or a charge
I would like to have a query that gives me this result:
Number, sum(payments), sum(charges) (grouped by number)
I think it is some type of merging these to querys
select number, sum(amount) where type = 0 group by number
and
select number, sum(amount) where type <> 0 group by number
Im using it to calculate a customers acount to see if they have paid to much or to little.
Any ideas?
View 6 Replies
View Related
Dec 24, 2003
i have this query and would like to have a sum for each column. how can i phrase the compute line please ?
select office as Office
, Sum(Case (role) when 'ebp' then 1 else 0 end) as 'EBP'
, Sum(Case (role) when 'support' then 1 else 0 end) as 'Support'
, Sum(Case (role) when 'Awaiting Disposal' then 1 else 0 end) as 'Awaiting Disposal'
, Sum(Case (role) when 'Interview Room' then 1 else 0 end) as 'Interview Room'
, Sum(Case (role) when 'Sch Drop In' then 1 else 0 end) as 'Sch Drop In'
, Sum(Case (role) when 'Sch CX Staff' then 1 else 0 end) as 'Sch CX Staff'
, Sum(Case (role) when 'Not in Use' then 1 else 0 end) as 'Not in Use'
, Sum(Case (role) when 'Public' then 1 else 0 end) as 'Public'
, Sum(Case (role) when 'IAG' then 1 else 0 end) as 'IAG'
, Sum(Case (role) when 'Delivery' then 1 else 0 end) as 'Delivery'
, Sum(Case (role) when 'NVQ Use' then 1 else 0 end) as 'NVQ Use'
, Sum(Case (role) when 'Hot Swap Spare' then 1 else 0 end) as 'Hot Swap Spare'
, Sum(Case (role) when 'Archived' then 1 else 0 end) as 'Archived'
, Sum(Case (role) when 'Network Infrastructure' then 1 else 0 end) as 'Network Infrastructure'
, Sum(Case (role) when 'Unknown' then 1 else 0 end) as 'Unknown'
, Sum(Case (role) when 'Drop in Centres' then 1 else 0 end) as 'Drop in Centres'
, Sum(Case (role) when 'Training' then 1 else 0 end) as 'Training'
from tempassets2
group by office, role
order by office
any help appreciated.
View 2 Replies
View Related
Dec 16, 1999
I am trying to build a query in SQL Server 7 that shows my customer ID, customer name, total sales. The query needs to be sorted from highest to Lowest sales. Once the query is sorted I want to put a ranking number beside my total sales field(i.e.1, 2, 3, 4 ....etc.) So far have a query(I did this in Access as a test) that sorts my sales by customer.
Anyone out there know how to get the ranking number beside my sorted list of customers?
Someone has mentioned cursors to me. I have no idea how to use these.
SELECT
Customer.`Customer Name`,
SUM (Customer.`Last Year's Sales`) as total
FROM
`Customer` Customer
GROUP BY
Customer.`Customer Name`
ORDER BY
total DESC
Thanks for the help
Mark
View 2 Replies
View Related
Nov 2, 2003
You are given say a pricelist of books. And you have to find out
all possible sets of books, each of them having total sum of book
prices equal to a given number.
set nocount on
if object_id('tempdb..#t')>0 drop table #t
if object_id('tempdb..#tt')>0 drop table #tt
create table #t (n int, price int)
insert into #t -- note asc order of book prices
select 1, 1 union all
select 2, 3 union all
select 3, 4 union all
select 4, 5 union all
select 5, 7 union all
select 6, 7 union all
select 7, 11 union all
select 8, 15 union all
select 9, 20 union all
select 10, 20 union all
select 11, 22 union all
select 12, 28 union all
select 13, 33 union all
select 14, 40 union all
select 15, 43 union all
select 16, 47 union all
select 17, 50 union all
select 18, 55 union all
select 19, 56 union all
select 20, 63
go
create table #tt (n int, price int)
go
declare @rows int, @p int, @sum int set @sum=16
delete from #t where price>@sum
set @p=(select sum(price) from #t)
if @p>=@sum
begin
set @rows=(select max(n) from #t)
declare @n int, @s int
set @n=@rows+1 set @s=0
while 0=0
begin
while @n>1
begin
set @n=@n-1
if @s+(select price from #t where n=@n)<=@sum
and @s+(select sum(price) from #t where n<=@n)>=@sum
begin
set @s=@s+(select price from #t where n=@n)
insert into #tt select n, price from #t where n=@n
if @s=@sum select * from #tt --- outputting
end
end
set @n=(select min(n) from #tt)
set @s=@s-(select price from #tt where n=@n)
delete from #tt where n=@n
if @s=0 and (select sum(price) from #t where n<@n)<@sum break
end
end
drop table #tt
drop table #t
Result for @sum=16 (for e.g. @sum=76 number of different sets = 196):
n price
----------- -----------
8 15
1 1
n price
----------- -----------
7 11
4 5
n price
----------- -----------
7 11
3 4
1 1
n price
----------- -----------
6 7
4 5
3 4
n price
----------- -----------
6 7
4 5
2 3
1 1
n price
----------- -----------
5 7
4 5
3 4
n price
----------- -----------
5 7
4 5
2 3
1 1
EDIT: added one more condition (in blue) into an IF statement.
Now it works incredibly fast.
View 4 Replies
View Related
Jul 20, 2005
I have just added a third table to a query and I am no longer gettingthe results I am expecting.Three Tables:CUSTINVOICEJOUR (Header Table)CUSTINVOICETRANS (Line Item Table)MARKUPTRANS (Additional Header Info)CUSTINVOICEJOUR has a one to many relationship to CUSTINVOICETRANS.CUSTINVOICEJOUR has a one to many relationship to MARKUPTRANS.I need to sum an integer column from MARKUPTRANS, in rows that arerelated to CUSTINVOICEJOUR, and include that output with my querybelow, which right now has a row for each CUSTINVOICETRANS record:SELECT CUSTINVOICEJOUR.INVOICEAMOUNT, CUSTINVOICETRANS.QTYFROM CUSTINVOICEJOUR INNER JOINCUSTINVOICETRANS ON CUSTINVOICEJOUR.INVOICEID =CUSTINVOICETRANS.INVOICEIDWHERE (CUSTINVOICEJOUR.DATAAREAID = 'acm') AND(CUSTINVOICETRANS.DATAAREAID = 'acm')The above works fine - a row for each record in CUSTINVOICETRANS withthe header info in there as well.I tried the query below to add a SUM() from MARKUPTRANS, but when I runit, I get one row with strange results in it - not what I expected.What am I doing wrong?SELECT CUSTINVOICEJOUR.INVOICEAMOUNT, CUSTINVOICETRANS.QTY,SUM(MARKUPTRANS.VALUE) AS FreightValueFROM CUSTINVOICEJOUR INNER JOINCUSTINVOICETRANS ON CUSTINVOICEJOUR.INVOICEID =CUSTINVOICETRANS.INVOICEID INNER JOINMARKUPTRANS ON CUSTINVOICEJOUR.RECID =MARKUPTRANS.TRANSRECIDWHERE (CUSTINVOICEJOUR.DATAAREAID = 'acm') AND(CUSTINVOICETRANS.DATAAREAID = 'acm') AND (MARKUPTRANS.DATAAREAID ='acm')GROUP BY CUSTINVOICEJOUR.INVOICEAMOUNT, CUSTINVOICETRANS.QTY,MARKUPTRANS.MARKUPCODEHAVING (MARKUPTRANS.MARKUPCODE = 'Freight')
View 1 Replies
View Related
Apr 12, 2007
Hello All
I am trying to figure out if what i am attempting to do is possible and whether or not my approach is wrong to begin with.
I am trying to build a custom report for our accounting system which is Traverse from Open systems. This is what i have done in the stored procedure thus far
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE rptArFLSalesByCustItemized_sp
@custId pCustID,
@dateFrom datetime,
@dateThru datetime,
@itemIdFrom pItemId,
@itemIdThru pItemId
as
set nocount on
-- define some variables for previous year
declare @LYqty int, @LyAmt money, @LYfrom datetime, @LYthru datetime
-- set defaults
SET @itemIdFrom=ISNULL(@itemIdFrom,(SELECT MIN(itemId) FROM tblInItem))
SET @itemIdThru=ISNULL(@itemIdThru,(SELECT MAX(itemId) FROM tblInItem))
SET @LYfrom=DATEADD(YEAR,-1,@dateFrom)
SET @LYthru=DATEADD(YEAR, -1, @dateThru)
-- create small temp table to hold customer info
Create Table #tmpArCustInfo
(
custId pCustID,
custName VARCHAR (30),
)
-- populate customer temp table with info
Insert into #tmpArCustInfo
select custId, custName
from tblArCust
WHERE custId = @custId
-- create a temp table to hold the Data for each Item
Create Table #tmpArSalesItemized
(
itemId pItemId,
productLine VARCHAR (12),
pLineDesc VARCHAR (35),
descr VARCHAR (35),
LYQtySold int,
LYTDQtySold int,
QtySold int,
LYTDsales money,
totalSales money,
LastInvDate datetime,
)
-- populate the temp table with all of the inventory items
insert into #tmpArSalesItemized
select ii.itemId, ii.productLine, ip.Descr, ii.Descr, 0,0,0,0,0, NULL
from tblInItem ii, tblInProductLine ip
where ip.productLine = ii.productLine
AND ii.itemId BETWEEN @itemIdFrom AND @itemIdThru
-- update table with this years quantities
update #tmpArSalesItemized
SET QtySold = (select SUM(QtyOrdSell) from tblArHistDetail hd
where TransId IN (select TransId from tblArHistHeader where custId = @custId)
AND orderDate IN (select OrderDate from tblArHistHeader where OrderDate BETWEEN @dateFrom AND @dateThru)
AND hd.partId BETWEEN @itemIdFrom AND @itemIdThru
GROUP BY hd.partId
)
-- Return the temp tables results
select * from #tmpArSalesItemized, #tmpArCustInfo
drop table #tmpArSalesItemized, #tmpArCustInfo
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
My problems begin where i want to start updating all of the Qty's of the QtySold field. I have managed to get it to write the same sum in every field but i cannot figure out how to update each row based on the sum of the qty found for that item in the tblArHistDetails table, trouble is too that there is no reference to the custId in that table either. The custId resides in tblArHistHeader and is linked to the details table via the TransId column. So really i need to update many rows based on criteria from 2 other tables.
Can anyone please help? I dont have a clue how to make this work, and most of what i have learned about sql thus far has been from opening other stored procs etc in the accounting system and just reading to see how the developers have done things.
Thanks
Jamie
View 1 Replies
View Related
Nov 23, 2015
I have a data table that contains budget and actual data by month. Â I use the data to create a pivot that shows actual results next to budgeted results. Â I need a column that shows that variance between those columns. Â I think my issue is that the "Type" field contains actual and Budget. Â I sum on "Type". Â I can't seem to create a sum since those items are in the same field or am I missing something?
Table design
Month|Division|Subdivision|Type|Dept|Rate|Units|Amount
October|DC|Day|Budget|125|10.00|100|1000
October|DC|Day|Actual|125|10.00|110|1100
Output Design
DC
DAY
Actual
Budget
125 AvgOfRate
AvgOfRate
SumOfUnits
SumOfUnits
SumOfAmt
SumOfAmt
View 4 Replies
View Related
Aug 14, 2006
Hi,
I need to update a table by summing the amount for a year and month with the month that comes before.
Table Ex:
Year-Month-Amount...
2006-01-40
2006-02-10
We're trying to optimize a series of existing code that calculates the cumalated stats for each month. (Once we've calculated the amount for February we then need to add the amount from January and so on.)
The query that we tried was something like this:
UPDATE table1
SET amount =
(SELECT SUM(amount) FROM table1 WHERE year="2006" AND month="01")
WHERE year="2006" AND month="02"
This didn't work. Any ideas???
View 2 Replies
View Related
Jan 24, 2008
I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
Thanks,
Keith
View 4 Replies
View Related
Dec 7, 2006
We have 20 -30 normalized tables in our dartabase . Also we have 4tables where we store the calculated data fron those normalised tables.The Reason we have these 4 denormalised tables is when we try to dothe calcultion on the fly, our site becomes very slow. So We haveprecalculated and stored it in 4 tables.The Process we use to do the precalcultion, will get do thecalculation and and store it in a temp table. It will compare the thetemp with denormalised tables and insert new rows , delte the old oneans update if any changes.This process take about 20 mins - 60mins. Ittakes long time because in this process we first do the calculationregardless of changes and then do a compare to see what are changed andremove if any rows are deleted, and insert new rowsand update thechanges.Now we like to capture the rows/columns changed in the normalisedtables and do only those chages to the denormalised table , which weare hoping will reduce the processing time by atleast 50%WE have upgraded to SQL SERVER 2005.So We like to use the newtechnology for this process.I have to design the a model to capture the changes and updated onlythose changes.I have the list of normalised tables and te columns which will affectthe end results.I thought of using Triggers or OUTPUT clause to capture the changes.Please help me with the any ideas how to design the new process
View 3 Replies
View Related
Oct 7, 2015
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
View 1 Replies
View Related
Feb 5, 2007
From Newbie to Newbie,
Add reference to:
'Microsoft ActiveX Data Objects 2.8 Library
'Microsoft ADO Ext.2.8 for DDL and Security
'Microsoft Jet and Replication Objects 2.6 Library
--------------------------------------------------------
Imports System.IO
Imports System.IO.File
Code Snippet
'BACKUP DATABASE
Public Shared Sub Restart()
End Sub
'You have to have a BackUps folder included into your release!
Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click
Dim addtimestamp As String
Dim f As String
Dim z As String
Dim g As String
Dim Dialogbox1 As New Backupinfo
addtimestamp = Format(Now(), "_MMddyy_HHmm")
z = "C:Program FilesVSoftAppMissNewAppDB.mdb"
g = addtimestamp + ".mdb"
'Add timestamp and .mdb endging to NewAppDB
f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""
Try
File.Copy(z, f)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
MsgBox("Backup completed succesfully.")
If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then
End If
End Sub
Code Snippet
'RESTORE DATABASE
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
RestoreDB.Click
Dim Filename As String
Dim Restart1 As New RestoreRestart
Dim overwrite As Boolean
overwrite = True
Dim xi As String
With OpenFileDialog1
.Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*"
If .ShowDialog() = Windows.Forms.DialogResult.OK Then
Filename = .FileName
'Strips restored database from the timestamp
xi = "C:Program FilesVSoftAppMissNewAppDB.mdb"
File.Copy(Filename, xi, overwrite)
End If
End With
'Notify user
MsgBox("Data restored successfully")
Restart()
If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub
Code Snippet
'CREATE NEW DATABASE
Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CreateNewDB.Click
Dim L As New DatabaseEraseWarning
Dim Cat As ADOX.Catalog
Cat = New ADOX.Catalog
Dim Restart2 As New NewDBRestart
If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then
If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then
Exit Sub
Else
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
End If
End If
Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;
Jet OLEDB:Engine Type=5")
Dim Cn As ADODB.Connection
'Dim Cat As ADOX.Catalog
Dim Tablename As ADOX.Table
'Taylor these according to your need - add so many column as you need.
Dim col As ADOX.Column = New ADOX.Column
Dim col1 As ADOX.Column = New ADOX.Column
Dim col2 As ADOX.Column = New ADOX.Column
Dim col3 As ADOX.Column = New ADOX.Column
Dim col4 As ADOX.Column = New ADOX.Column
Dim col5 As ADOX.Column = New ADOX.Column
Dim col6 As ADOX.Column = New ADOX.Column
Dim col7 As ADOX.Column = New ADOX.Column
Dim col8 As ADOX.Column = New ADOX.Column
Cn = New ADODB.Connection
Cat = New ADOX.Catalog
Tablename = New ADOX.Table
'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet
OLEDB:Engine Type=5")
'Open the Catalog
Cat.ActiveConnection = Cn
'Create the table (you can name it anyway you want)
Tablename.Name = "Table1"
'Taylor according to your need - add so many column as you need. Watch for the DataType!
col.Name = "ID"
col.Type = ADOX.DataTypeEnum.adInteger
col1.Name = "MA"
col1.Type = ADOX.DataTypeEnum.adInteger
col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col2.Name = "FName"
col2.Type = ADOX.DataTypeEnum.adVarWChar
col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col3.Name = "LName"
col3.Type = ADOX.DataTypeEnum.adVarWChar
col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col4.Name = "DOB"
col4.Type = ADOX.DataTypeEnum.adDate
col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col5.Name = "Gender"
col5.Type = ADOX.DataTypeEnum.adVarWChar
col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col6.Name = "Phone1"
col6.Type = ADOX.DataTypeEnum.adVarWChar
col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col7.Name = "Phone2"
col7.Type = ADOX.DataTypeEnum.adVarWChar
col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col8.Name = "Notes"
col8.Type = ADOX.DataTypeEnum.adVarWChar
col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable
Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")
'You have to append all your columns you have created above
Tablename.Columns.Append(col)
Tablename.Columns.Append(col1)
Tablename.Columns.Append(col2)
Tablename.Columns.Append(col3)
Tablename.Columns.Append(col4)
Tablename.Columns.Append(col5)
Tablename.Columns.Append(col6)
Tablename.Columns.Append(col7)
Tablename.Columns.Append(col8)
'Append the newly created table to the Tables Collection
Cat.Tables.Append(Tablename)
'User notification )
MsgBox("A new empty database was created successfully")
'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing
'Restart application
If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub
Code Snippet
'COMPACT DATABASE
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CompactDB.Click
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine
'The first source is the original, the second is the compacted database under an other name.
JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program
FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")
'Original (not compacted database is deleted)
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
'Compacted database is renamed to the original databas's neme.
Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")
'User notification
MsgBox("The database was compacted successfully")
End Sub
End Class
View 1 Replies
View Related
Jul 20, 2005
I'm working on an ASP.Net project where I want to test code on a localmachine using a local database as a back-end, and then export it tothe production machine where it uses the hosting provider's SQL Serverdatabase on the back-end. Is there a way to export tables from oneSQL Server database to another in such a way that if a table alreadyexists in the destination database, it will be updated to reflect thechanges to the local table, without existing data in the destinationtable being lost? e.g. suppose I change some tables in my localdatabase by adding new fields. Can I "export" these changes to thedestination database so that the new fields will be added to thedestination tables (and filled in with default values), without losingdata in the destination tables?If I run the DTS Import/Export Wizard that comes with SQL Server andchoose "Copy table(s) and view(s) from the source database" and choosethe tables I want to copy, there is apparently no option *not* to copythe data, and since I don't want to copy the data, that choice doesn'twork. If instead of "Copy table(s) and view(s) from the sourcedatabase", I choose "Copy objects and data between SQL Serverdatabases", then on the following options I can uncheck the "CopyData" box to prevent data being copied. But for the "CreateDestination Objects" choices, I have to uncheck "Drop destinationobjects first" since I don't want to lose the existing data. But whenI uncheck that and try to do the copy, I get collisions between theproperties of the local table and the existing destination table,e.g.:"Table 'wbuser' already has a primary key defined on it."Is there no way to do what I want using the DTS Import/Export Wizard?Can it be done some other way?-Bennett
View 3 Replies
View Related
Oct 17, 2006
I have a trade data tables (about 10) and I need to retrieve information based on input parameters. Each table has about 3-4 million rows.
The table has columns like Commodity, Unit, Quantity, Value, Month, Country
A typical query I use to select data is "Select top 10 commodity , sum(value), sum(quantity) , column4, column5, column6 from table where month=xx and country=xxxx"
The column4 = (column2)/(total sum of value) and column 5=(column3)/(total sum of quantity). Column6=column5/column4.
It takes about 3-4 minutes for the query to complete and its a lot of time specially since I need to pull this information from a webpage.
I wanted to know if there is an alternate way to pull the data from server ?
I mean can I write a script that creates tables for all the input combinations i.e month x country (12x228) and save them in table (subtable-table) with a naming convention so from the web I can just pull the table with input parameters mapped to name convention and not running any runtime queries on database ??
OR
Can I write a script that creates a html files for each table for all input combinations save them ?
OR
Is there exists any other solution ?
View 1 Replies
View Related
Dec 9, 2007
Hi all,
I have a large Excel file with one large table which contains data, i've built a SQL Server DataBase and i want to fill it with the data from the excel file.
How can it be done?
Thanks, Michael.
View 1 Replies
View Related
Apr 22, 2004
Hi,
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
#2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
#3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1
2nd choice: Change step 1 to use #3 instead of #1
3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
Thank you for any suggestion.
View 2 Replies
View Related
Feb 22, 2008
I have 3 Checkbox list panels that query the DB for the items. Panel nº 2 and 3 need to know selection on panel nº 1. Panels have multiple item selection. Multiple users may use this at the same time and I wanted to have a full separation between the application and the DB. The ASP.net application always uses Stored Procedures to access the DB. Whats the best course of action? Using a permanent 'temp' table on the SQL server? Accomplish everything on the client side?
[Web application being built on ASP.net 3.5 (IIS7) connected to SQL Server 2005)
View 1 Replies
View Related
Oct 5, 2007
Firstly I consider myself quite an experienced SQL Server user, andamnow using SQL Server 2005 Express for the main backend of mysoftware.My problem is thus: The boss needs to run reports; I have designedthese reports as SQL procedures, to be executed through an ASPapplication. Basic, and even medium sized (10,000+ records) reportingrun at an acceptable speed, but for anything larger, IIS timeouts andquery timeouts often cause problems.I subsequently came up with the idea that I could reduce processingtimes by up to two-thirds by writing information from eachcalculationstage to a number of tables as the reporting procedure runs..ie. stage 1, write to table xxx1,stage 2 reads table xxx1 and writes to table xxx2,stage 3 reads table xxx2 and writes to table xxx3,etc, etc, etcprocedure read final table, and outputs information.This works wonderfully, EXCEPT that two people can't run the samereport at the same time, because as one procedure creates and writesto table xxx2, the other procedure tries to drop the table, or read atable that has already been dropped....Does anyone have any suggestions about how to get around thisproblem?I have thought about generating the table names dynamically using'sp_execute', but the statement I need to run is far too long(apparently there is a maximum length you can pass to it), and evenbreaking it down into sub-procedures is soooooooooooooooo timeconsuming and inefficient having to format statements as strings(replacing quotes and so on)How can I use multiple tables, or indeed process HUGE procedures,withdynamic table names, or temporary tables?All answers/suggestions/questions gratefully received.Thanks
View 2 Replies
View Related
Mar 2, 2008
Does anyone have a script that can drop the Identity columns from all the tables in a database? Thanks
View 1 Replies
View Related
Aug 12, 2006
Hello all,
Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.
Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.
What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1
Please help :-D
Greetingz,
DJ Roelfsema
View 6 Replies
View Related
