Making Two Sums In One Query

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


ADVERTISEMENT

Running Sums Query

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

Compute Sums In Select Query

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

Making A SQL Update Query Run Once

Aug 22, 2005

I have a datagrid in my file along with an Update Query.

My Update Query basically adds the numerical values in two columns
together when the page is loaded. This means whenever the page is
Refreshed the Update query is fired.

This is my Update Query (which is in an Stored Procedure):

UPDATE Rental
SET TotalFee = ExtraFee + TotalFee
WHERE DaysOverdue >= 0

I have declared my query in the 'Page_Load' part of the coding, because
I want the query to run automatically. Not manually by a button.

My main question is that how can I get the query to run only once a day, no matter how many times the page is loaded.

View 7 Replies View Related

Making A Table From A Query

Feb 10, 2004

I have a Query named qryQuery1 from which I would like to make a Table named tblTable1. I need to do this within a VBA Microsoft Access module.

Can anyone help me with the proper code?

Thank you for any help.

Jim

View 14 Replies View Related

Help Me Making FOR XML QUERY Plezz!!

Sep 20, 2007

OK Here goes my BIG question


I've following database structure (just junk values)

[ChannelID]---[ChannelName]---[ChannelCatID]---[RSSFeedURL]

1---YouTube---1---ret---
2---GoogleVideo---1---ert---
3---MSNVideo---1---rtertert---
4---ABC---2---retert---
5---YahooVideo---1---erter---
6---CBS---2---rtr---
7---NBC---2---trtrt---
8---ESPN---2---rt---
9---FOX---2---rtrtttr---
10---AOL---1---rt---

I wanna generate following XML structure out of it

<DOC>
-<Item Type="xyz">
--<Channel id="a">
---<Name>aaaaa<Name/>
---<URL>aaaaa<URL/>
--</Channel>

--<Channel id="b">
---<Name>bbbb<Name/>
---<URL>bbbb<URL/>
--</Channel>
---
---
-</Item>

-<Item Type="lmn">
--<Channel id="x">
---<Name>xxxx<Name/>
---<URL>xxxx<URL/>
--</Channel>

--<Channel id="y">
---<Name>yyyy<Name/>
---<URL>yyyy<URL/>
--</Channel>
---
---
-</Item>
------
------
------
</DOC>

I've written this Select For XML query

SELECT ChannelCatID as '@type',
ChannelID as 'Channel/@id',
ChannelName as 'Channel/Name',
RSSFeedURL as 'Channel/URL'
FROM ChannelMaster
FOR XML PATH('Item'), ROOT('DOC')


It gives me this result
<DOC>
-<Item Type="xyz">
--<Channel id="a">
---<Name>aaaaa<Name/>
---<URL>aaaaa<URL/>
--</Channel>
-</Item>
-<Item Type="xyz">
--<Channel id="b">
---<Name>bbbb<Name/>
---<URL>bbbb<URL/>
--</Channel>
-</Item>
-<Item Type="xyz">
---
-</Item>
-<Item Type="xyz">
---
-</Item>
------
------
------
</DOC>

I want it this way :

all channels of Type xyz should come under singe <Item Type="xyz"> tag instead of seperate tags

what changes should i make, plezz help meout wit dis ??

.(][)[](][).

View 1 Replies View Related

Slow Performance When Making A Query

Aug 1, 2005

I have a table with about 20,000 records.  So, to minimise performance issues, i try to only retrieve the top 100 records.  The databaset retrieved can be paged and sorted.  Below is my code, but it's taking quite a while even to only load the 100 records.  Any suggestions?Private Sub BindGrid()        Dim connectionString As String = "MyConnectionString"        Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
         Dim queryString As String = "SELECT TOP 100 [Person].* FROM [Person] ORDER BY " & viewstate("sortField").ToString() & " " & viewstate("sortDirection").ToString()
        Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand        dbCommand.CommandText = queryString        dbCommand.Connection = dbConnection
        Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter        dataAdapter.SelectCommand = dbCommand        Dim dataSet As System.Data.DataSet = New System.Data.DataSet        dataAdapter.Fill(dataSet)
        Try           DataGrid1.DataSource = dataSet           DataGrid1.Databind()        Catch e As Exception           DataGrid1.CurrentPageIndex = 0        End TryEnd Sub

View 1 Replies View Related

Making A Wide Table -- SQL Query

Sep 24, 2004

Hi Guys,
I have a requirement as follows:->

I have a table like below

EMPLOYEEID------- OWNS
********** ****
1-----------------car
1-----------------house
1-----------------dog
2-----------------house
3-----------------car
3-----------------bus
3-----------------shop
3-----------------hotel
3-----------------theater
3-----------------casino

Requirement:
I wanted to create another table based on the column values. For eg: I have to take the employee id and check for what value he has under owns column in the table. I take only 3 values and then these values should go to the newly created columns (owns1, owns2,owns3).
if there is no value for any of these columns it should have null values loaded in them.
The result of the modification should look like this:->

EMPLOYEEID-------OWNS1------OWNS2------OWNS3
*********** ***** ***** *
1-----------------car--------house------dog
2-----------------house------Null-------Null
3-----------------car--------Bus--------shop

Note: eventhough employeeid 3 owns more than 3 things we only take 3 of what he owns and populate to above coloumns.
In addition to it, the column OWNS will have more than 500 different values in them.

Its kind of urgent and if anyone knows how to , Can you please help me on this.
Thanks a lot.
-- Ragulan
;)

View 3 Replies View Related

Using Local Variable Making The Query Slow

May 28, 2008


Hi,

I am using a local variable to capture datetime and then select records from another table by making use of the above local variable result. But the query is running too slow when I use the local variable

declare @a smalldatetime
select @a=last_run_time from job_status
where job_des='sample'


SELECT * FROM History
WHERE CHANGE_DATE> @a

Instead of the above select statement if I use the below statement it is returning results quickly. Can someone help me in tuning the above query.
SELECT * FROM History
WHERE CHANGE_DATE> '5/23/2008 6:22:00.000 AM '

History table has columns ( case number, change_date, change_desc). I have two indexes defined on the above table one is on case_number and the other on change_date.I tried using force index but still the query is running slow.

will there be any difference if use dynamic sql ?

View 7 Replies View Related

Making A Query Have Flexible Index Utilization

Apr 29, 2008

I have one query that looks like this.

DECLARE @propname

SET @propname = NULL -- can be Null or have wild card values

SELECT col1, col2, col3, propertyname FROM testtable where col1 = 1 and col2 = 2 and col3 = 3 and (@propname IS NULL OR propertyname LIKE @propname)

col1, col2, col3 are part of a clustered index

propertyname is a nonclustered index

This is the predicament. If the "@propname is NULL" is first in the OR statement then the query will use the clustered index for finding the record. If I put "@propname is NULL" last then it uses the propertyname index no matter what. So I either get full index scans if NULL is ever used on property names or I get consistent two second long searches on the clustered index. Any way to have the best of both worlds? Or do I have to divide up my query into more stored procedures?

THANKS

View 11 Replies View Related

Problem With Making A Sqldatasource For Inserting Using The Query Builder

Aug 19, 2007

I want to make a sqldatasource to insert data ito a table with values from textfields page. I want to use the sqldatasource programically (not bind it to a formcontrol).
I drag a sqldatasource from the toolbox to the design surface and start configuring the datasource. I spesify a custom sql statement, select the "Insert" tab and insert the table I want to store into the builder. Then I select the fields and the values (parameters from tue textfields. I test it with the "Execute query" button and the results is stored in the table. (everything seems ok) I press the "OK" button but both the "Next" and "Finish" buttons are dissabled, o I can not store the query.
What is going wrong. Can someone please help me ?
Tom Knardahl

View 2 Replies View Related

Making Date Dynamic In Pivot Table Query

Apr 16, 2008

I have a pivot table query I am running and wanted to find out if there was a way to pull in the dates like getdate() - 12 months, getdate() - 11 months, etc. instead of hard coding the dates.

Here is my query

SELECT Client, [4/1/2007 12:00:00 AM] AS Month1, [5/1/2007 12:00:00 AM] AS Month2, [6/1/2007 12:00:00 AM] AS Month3, [7/1/2007 12:00:00 AM] AS Month4,
[8/1/2007 12:00:00 AM] AS Month5, [9/1/2007 12:00:00 AM] AS Month6, [10/1/2007 12:00:00 AM] AS Month7, [11/1/2007 12:00:00 AM] AS Month8,
[12/1/2007 12:00:00 AM] AS Month9, [1/1/2008 12:00:00 AM] AS Month10, [2/1/2008 12:00:00 AM] AS Month11, [3/1/2008 12:00:00 AM] AS Month12,
[4/1/2008 12:00:00 AM] AS Month13, Engineer
FROM (SELECT Client, DollarsBilled, SlipDates, Engineer
FROM dbo.MonthlyClientBillables) p PIVOT (SUM(DollarsBilled) FOR SlipDates IN ([4/1/2007 12:00:00 AM], [5/1/2007 12:00:00 AM],
[6/1/2007 12:00:00 AM], [7/1/2007 12:00:00 AM], [8/1/2007 12:00:00 AM], [9/1/2007 12:00:00 AM], [10/1/2007 12:00:00 AM], [11/1/2007 12:00:00 AM],
[12/1/2007 12:00:00 AM], [1/1/2008 12:00:00 AM], [2/1/2008 12:00:00 AM], [3/1/2008 12:00:00 AM], [4/1/2008 12:00:00 AM])) AS pvt

View 31 Replies View Related

SQL Server 2012 :: Making Query For Presenting Data In Different Format?

Jun 18, 2015

I have below table:

IF OBJECT_ID('tempdb..#complaints') IS NOt NULL
DROP TABLe #complaints

--===== Create the test table with

create table #cs([Year] float,
[Week] float,
[Month] float,
[C#] float,
[Dept] nvarchar(255)
,[Issue] nvarchar(255), [Type] nvarchar(255), [Dept age] nvarchar(255))

--===== All Inserts into the IDENTITY column

INSERT INTO #cs
([Year], [ Week], [ Month], [C#],[Dept],[Issue], [Type], [Dept age])
SELECT 2015, 14, 4, 188, D1,I1,T1, 5 UNION ALL
SELECT 2015, 14, 4, 452,d1, I1, T2, 5 UNION ALL
SELECT 2015, 14, 4, 63, d1, I1, T1, 6 UNION ALL
SELECT 2015, 14, 4, 9, d1,I2, T1, 7 UNION ALL
SELECT 2014, 14, 4, 187, D1,I1,T1, 5 UNION ALL
SELECT 2014, 14, 4, 451,d1, I1, T2, 5 UNION ALL
SELECT 2014, 14, 4, 62, d1, I1, T1, 6 UNION ALL
SELECT 2014, 14, 4, 10, d1,I2, T1, 7 UNION ALL)

and i want to have a table in below format: (query for it)

Week, Month, C1#,c2# Dept,Issue, Type, Dept age
14, 4 188 187 d1 i1 t1 5
14, 4 452 451 d1 i1 t2 5

I.E. I WANT two columns C1# and C2#, where C1# contains data from 2015 and C2# contains data from previous year (2014). If 2015 data is not present, then C1# will contain data of 2014 and C2# will contain data of 2013.

View 9 Replies View Related

Sum Of Sums

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

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 View Related

Gets Sums On Inner Joins

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

Getting 2 SUMs From The Same Table

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

How To Do Multiple Sums?

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

Different Sums From Table

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

Combine SUMs

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

SQL Group By With Multiple Sums?

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

Reformulated Question On Sums

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

Running Sums Of Averages

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

Need Two SUMS In Pivot Table

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

Ranking Sums Using Cursors And Stored Procs??

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

Selecting Rows With Sums Equal To A Given Number

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

JOINs And SUMs Not Giving Expected Results

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

Updating Multiple Rows Based On Sums From Another Table

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

DB Design :: Summing From Two Tables Then Performing Operation With 2 Sums

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

Power Pivot :: Difference Between Two Pivot Table Sums?

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

Update A Table With Sums From This Table

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

Making A League

Apr 14, 2007

Hello, I have a table of data with the following columns:ParticipantResult (win or lose)The table has other data in, but I want to make a league to sort by the amounts of wins. For example: Participant            Won            PlayedA                          5                  10B                           4                  7C                          3                   3 I want this to show up on web page. I was hoping to do this through the source code on the web page, but I am struggling to do it.Is there a better way to do this, or a place where I can see an example of the code. Many Thanks, Oliver 

View 1 Replies View Related

Ok I'm Making A Webpage But

Dec 7, 2003

with SQL desktop engine installed where do I even begin working with it?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved