Transact SQL :: Picking Up Average Last Two Payments

May 18, 2015

Got a table named Payments in the following form:

Report_DateCustomer_IDCustomer_NamePayment_DatePayment_Amount

The Customers Table have the fields:

Customer_IDCustomer_Name

Since the Customers Table contains the complete list of customers I seek to have a Join made between it and the Paymentstable to check the amount made by the customers.

However, the trouble arises when I seek to check the average amounts made by each customer during last two months in which such a customer had paid.

In further clarification, lets consider current month of May 2015 going on;

Customer_A had paid $10,000 in AprilCustomer_A had paid $5,000 in AprilCustomer_B had paid $10,000 in AprilCustomer_A had paid $18,000 in MarchCustomer_A had paid $2,000 in MarchCustomer_A had paid $10,000 in FebruaryCustomer_B had paid $8,000 in FebruaryCustomer_B had paid $4,000 in FebruaryCustomer_B had paid $3,000 in February...

View 4 Replies


ADVERTISEMENT

Transact SQL :: Getting The Average Of Time

Apr 30, 2015

I have the following code which returns day of week name, position and date and time of surgeries.

SELECT
DATENAME(WEEKDAY,OPE_START_TIME) AS DOW,
[OPE_ORDER_IN_SESS_ASC] AS POSITION,
OPE_START_TIME AS [TIME]

FROM table1The data looks like thisHow can i group this data so I get the average start time by day of week and position?

View 3 Replies View Related

Transact SQL :: Need To Get Average Duration Per Day For Every Month

Sep 23, 2015

I am storing duration of a lot of jobs in a column in a table per job. This duration is in seconds and an integer datatype.

Sample data:
Job        Duration    date
Job1       25          2015/9/23
Job2       30          2015/9/23
Job3       45          2015/9/23
Job4       1            2015/9/22

Now I need to get average duration per day for every month. Is this possible? I have a calendar table that has every single day month year microsecond millisecond  second minute and hour.

View 5 Replies View Related

Transact SQL :: Calculate Average Based On A Scenario

Oct 30, 2015

Col1                      Col2       Col3          Col4
54763.00              21           0              0             
--Row1
59574.00              23           0              0             
--Row2
64085.00              20           0              0             
--Row3
0.0               0.00      0           0              0             
--Row4

I'm trying to calculate Average of Col1 of above table based on below scenario:

CASE WHEN all the columns in the above table are “0” (as highlighted) THEN I want AVERAGE of Col1 as (Row1+Row2+Row3)/3

ELSE if at least one of the column of highlighted row has value other than “0”, THEN I want the AVERAGE of Col1 to be (Row1+Row2+Row3+Row4)/4

View 7 Replies View Related

How Can I Get A List Of The Last Payments

Apr 21, 2008

How can I get a list of the last payment for each client?
SELECT ClientID,PaymentDate,PaymentAmount FROM tblClients INNER JOIN tblPayments ON tblPayments.ClientID = tblClients.ClientID

View 3 Replies View Related

Many Year Payments

Jan 16, 2004

I need to create a stored procedure that will show all clients that have made payments every year for a number of years (3) or (5). My tables are simple but I have no clue how to ask the SQL question, mostly because it is each and every year.

tblClients ClientID (pk)
tblOrders OrderID (pk) ClientID (fk)
tblOrderPayments PaymentID (pk) OrderID (fk)

Thanks for any help,

View 8 Replies View Related

Analysis :: Calculation Of average Using DAX AVERAGE And AVERAGEX

Jun 21, 2015

Calculation of an average using DAX' AVERAGE and AVERAGEX.This is the manual calculation in DW, using SQL.In the tabular project (we're i've noticed that these 4 %'s are in itself strange), in a 1st moment i've noticed that i would have to divide by 100 to get the same values as in the DW, so i've used AVERAGEX:

Avg_AMP:=AVERAGEX('Fct Sales';'Fct Sales'[_AMP]/100)
Avg_AMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_AMPdollar]/100)
Avg_FMP:=AVERAGEX('Fct Sales';'Fct Sales'[_FMP]/100)
Avg_FMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_FMPdollar]/100)

The results were, respectively: 701,68; 2120,60...; -669,441; and  finally **-694,74** for Avg_FMPdollar.i can't understand the difference to SQL calculation, since calculations are similar to the other ones. After that i've tried:

test:=SUM([_FMPdollar])/countrows('Fct Sales') AND the value was EQUAL to SQL: -672,17
test2:=AVERAGE('Fct Sales'[_Frontend Margin Percent ACY]), and here, without dividing by 100 in the end, -696,74...

So, AVERAGE and AVERAGEX have a diferent behaviour from the SUM divided by COUNTROWS, and even more strange, test2 doesn't need the division by 100 to be similar to AVERAGEX result.

I even calculated the number of blanks and number of zeros on each column, could it be a difference on the denominator (so, a division by a diferente number of rows), but they are equal on each row.

View 2 Replies View Related

How Can I Get My Code To Look For Partial Payments

Apr 12, 2008

The following code accepts a couple of parameters and creates a temp table to hold unpaid schedule rows (like invoices). Then is takes the payment amount passed and starts paying the oldest ones first, until it runs out of money or pays a partial. When it pays it inserts a row in the applieds table with the schedule_ID, Receipt_ID (payment id), applied amount, and applied date.
What I need help with is: Say there is a partial payment from a previous payment, I need to pay that one, but only the unpaid part. Then continue to pay other schedule rows...
I appreciate any help,
@PaymentAmount money,@PledgeID Int,@Receipt_ID IntAS-- Get unpaid reminder rows for the passed in pledge id and put them into the temp tableDeclare @temp_oldest Table(PledgeSchedule_ID int,ReminderDueDate datetime,AmountDue money)INSERT INTO @temp_oldest SELECT PledgeSchedule_ID, PledgeDueDate, AmountDueFROM tblPledgeReminderScheduleWHERE (dbo.tblPledgeReminderSchedule.Pledge_ID=@PledgeID) AND (dbo.tblPledgeReminderSchedule.ReceivedDate IS NULL) -- AND (dbo.tblPledgeReminderSchedule.PledgeDueDate < GETDATE())WHILE((SELECT Count(*) FROM @temp_oldest)>0)BEGIN-- If the payment is greater or equal to the amount due for the current row, do thisIF(@PaymentAmount >= (SELECT Top 1 AmountDue FROM @temp_oldest))BEGIN-- Update the reminder row with todays dateUPDATE tblPledgeReminderSchedule SET ReceivedDate = GETDATE()WHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest)-- Insert a row to track applied payment and reminder row associatedINSERT INTO tblPledgeReminderSchedule_Applieds (PledgeSchedule_ID,Receipt_ID,Applied_Amount,Applied_Date)(SELECT Top 1 PledgeSchedule_ID,@Receipt_ID,AmountDue,GETDATE() FROM @temp_oldestWHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest))-- Subtract the amountdue from the paymentamount and reset itSET @PaymentAmount = (@PaymentAmount - (SELECT Top 1 AmountDue FROM @temp_oldest))ENDELSEIF(@PaymentAmount < (SELECT Top 1 AmountDue FROM @temp_oldest))BEGIN -- Insert a row to track applied PARTIAL payment and reminder row associatedINSERT INTO tblPledgeReminderSchedule_Applieds (PledgeSchedule_ID,Receipt_ID,Applied_Amount,Applied_Date)(SELECT Top 1 PledgeSchedule_ID,@Receipt_ID,@PaymentAmount,GETDATE() FROM @temp_oldestWHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest))BREAKENDELSEIF @PaymentAmount = 0-- Delete all rows from temp tableDELETE FROM @temp_oldestELSE-- Delete only the current row from the temo tableprint @PaymentAmountDELETE FROM @temp_oldest WHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest)END 

View 1 Replies View Related

Help With Udf Or Where Cluase To Get Quarterly/annual Due Payments

Jan 9, 2008

I'm trying to build a where clause that looks at two columns to determine if QUARTERLY and ANNUAL payments are due.
rec_day could be any day.
I got the monthly one pretty easily:
WHERE rec_start <= GETDATE() AND rec_frequency = 'Monthly' AND rec_day = DAY(GETDATE())
So if the rec_day was 9 and the rec_frequency was 'Quarterly' then I would be looking to see if todays date is 1/9 or 4/9 or 7/9 or 10/9 (not sure how I do this part)
I'm pretty much stuck on where to go with the quarterly where clause:
WHERE rec_start <= GETDATE() AND rec_frequency = 'Quarterly' AND rec_day = ????
 
I tried the following, but it did not work:
(CAST(DATEPART(Q, GETDATE()) * 3 - 2 AS VARCHAR(2)) + '/' + CAST(rec_day AS VARCHAR(2)) + '/' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) = GETDATE())

View 1 Replies View Related

Count Quantity Of Payments With 20000 Step

Jan 17, 2014

My need is to count the quantity of payments with 20000 step. This is my Oracle code. I'm in process of moving from Oracle to SQL Server. How to rewrite this code for SQL Server.

WITH got_grps AS
(
SELECT TRUNC (sys_creation_date) AS date_creation
, ( 1

+ FLOOR ( NVL (amount_original, 0)
/ 20000

[Code] ......

View 1 Replies View Related

Count Quantity Of Payments With 20000 Step

Jan 17, 2014

My need is to count the quantity of payments with 20000 step.

This is my Oracle code. I'm in process of moving from Oracle to SQL Server. How to rewrite this code for SQL Server.

WITH got_grps AS
(
SELECT TRUNC (sys_creation_date) AS date_creation
, ( 1
+ FLOOR ( NVL (amount_original, 0)

[Code] ....

View 1 Replies View Related

Transact SQL :: How To Get Moving Total Like Moving Average

Nov 10, 2015

I trying to get the moving total (juts as moving average). It always sum up the current record plus previous two records as well and grouped by EmpId.For example, attaching a image of excel calculation.

View 3 Replies View Related

Need An Average By Year Of An Average By Month

Feb 15, 2008

I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged...
I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25.
The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement.
?confused?

Here's the original query.
accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): '

SELECT name, country_cd from weather_station where platformId=&&platformId;

SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS
FROM daily
WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime
GROUP BY to_char(datetime,'MM')
ORDER BY to_char(datetime,'MM');

with a result of:

NAME_________________CO
-------------------- --
OFFUTT AFB___________US

MO______MAX_T _____AVG_T__MIN_TEMTP_TOTAL_YEARS
-- ---------- ---------- ---------- -----------
01_________21______-5.31________-30__________60
02_________26______-2.19______-28.3__________61
03_______31.1_______3.61______-26.1__________60
04_______35.6______11.07______-12.2__________60
05_______37.2_______17.2_______-3.3__________60
06_______41.1______22.44__________5__________60
07_______43.3______24.92________7.2__________60
08_______40.6______23.71________5.6__________60
09_________40______18.84_______-2.2__________59
10_______34.4_______12.5_______-8.9__________59
11_________29_______4.13______-23.9__________60
12_________21______-2.52______-28.3__________60

View 4 Replies View Related

Picking Out Duplicate Records

Mar 19, 2008

Hi, I have a student results table with the layout shown below (four records with the fields separated by dashes). Sorry its so messy. Anyway, you can see that there are duplicates. I want to write an SQL statement that will pick out only the 'supplemental' records if duplicates occur. Any ideas on how to do this?

ID - StudentNo - Subject - Term - Yearofstudy - YearTaken - Grade
1195- 11111111- MA1E2- Annual - 1- 2006- 34
1205- 11111111- MA1E2- Supplemental- 1- 2006- 40 (S)
1194- 11111111- MA1E1- Annual -1- 2006- 35
1204- 11111111- MA1E1- Supplemental- 1- 2006- 40 (S)

Here is the SQL I'm using to get all the records from the studentresults table, for first years only:

SELECT *
FROM studentresults
WHERE studentresults.StudentNo = 11111111 AND studentresults.YearOfStudy = 1

How do I change this to pick out only the supplemental exam results?

Regards,

sabatier

View 1 Replies View Related

Picking A Representative Subset?

Feb 5, 2013

The problem is to find a subset of rows such that each value in each of two columns (animals and food brands in this example) appears in at least one row. The purpose is to produce a set of samples from a large table. The table has a animal_name column and an food_brand column; I want a set of samples that contains at least one of each animal_name and at least one of each food_brand, but no more than necessary.

CREATE TABLE Feeding_Options
(license_nbr INTEGER NOT NULL PRIMARY KEY,
animal_name VARCHAR (10) NOT NULL,
food_brand VARCHAR(15) NOT NULL);
INSERT INTO Feeding_Options
VALUES
(6401715, 'rat', 'IAMS'),

[code]....

To frame the problem better, her are the values in each column:

animals = {'rat', 'dog', 'cat', 'fish', 'fox'}
food = {'IAMS', 'Sci Diet', 'Purina', 'Alpo'}

In this data, (6401715, 'rat', 'IAMS') and (1058337, 'rat', 'IAMS') are interchangeable, as are some of the other rows. There can be more than one minimal solution whcihmight be the whole set.

View 2 Replies View Related

Picking Records Having Both Of Two Criteria

May 14, 2008

Hi,

Slightly knotty and hard-to-articulate problem here, so please bear with me.

As part of a series of transactions, I have a table which contains data which looks like this:

AdBookingID adWeeks AdID clientId valueid
----------- ----------- ----------- ----------- -----------
97336 3 95127 248 1007
98220 1 94304 458 1007
98220 1 94304 458 7610
98386 1 88123 319 7604
98388 1 91484 319 7610
98390 1 91963 319 7610
98392 1 92468 319 7601
98392 1 92468 319 7608


The key to this problem is the "valueID". The first digit of the value is significant - the "1" and the "7" in the above mean the values are parts of different groups of search criteria.

What I need to do is to select from this list into a temp table all those bookings (identified by AdBookingID) which have valueIds of both types - i.e. an row in the table where the valueId starts with a 1 and a row in the table where the valueId starts with a 7 - in the above data sample the only AdBooking which qualifies is 98220.

I worked out a method of doing this going by the number of entries each item had in the table:

select count(distinct valueId)
as valueId, adWeeks, clientId, adID, AdBookingID
into #worktable
from #tmp
group by adWeeks, clientId, adID, AdBookingID
having count(distinct valueid) > 1

Which is largely accurate. But - and here's the killer - although each adBooking can only have one valueId starting with 1, it can have multiple entries starting with 7. These bookings are rare, but they do exist and are causing anomalies in the data returned which - if you recall - must only contain booking records for which there are valueId entries with both a 1 and a 7.

Can anyone suggest a way I can get just the data I need?
Cheers,
Matt

View 7 Replies View Related

Picking The Specific Data

May 16, 2007

I have in my table something like this
Col1 Col2
6 O
6 O
6 C
6 C
6 C
5 O
5 O

i want the value as 6 iff all the corresponding
records in col2 are C
similarly, since for 5 there are no C it should
not pick record 5

Please suggest me a query for this

View 10 Replies View Related

SQL Server Not Picking Up New NTLogin Name

Oct 11, 2007

Can anyone suggest an alternative to the following problem?

We have had 2 users change their NTLogin in AD. For example, from jsmith to jbloggs. NB this is a change, NOT a completely new login. When the users connect to sql server and call the function SYSTEM_USER, the login returned is the old login:

TheDomainjsmith

This is causing an audit trail problem. Exactly the same issue exists with sys.sysprocesses.

The only way I have found of correcting the problem is by restarting the SQL server service which is obviously not ideal for such a trivial issue.

You can recreate this problem via local users on a sql box as well:

1. Create a local user on the SQL box called FirstUserName and grant access to SQL
2. do a runas on management studio and runas the FirstUserName user
3. connect to SQL and run SELECT SYSTEM_USER, you will get [nameofsqlbox]FirstUserName which is correct
4. kill management studio
5. rename the user SecondUserName
6. do a runas on management studio and runas the SecondUserName user (note, same user, now has different login name)
7. connect to SQL and run SELECT SYSTEM_USER, you will get [nameofsqlbox]FirstUserName which is now incorrect
8. restart sql server service
9. connect to SQL and run SELECT SYSTEM_USER, you will get [nameofsqlbox]SecondUserName which is now correct

Any solutions other than just sucking up a restart everytime a user changes login name would be much appreciated!

View 15 Replies View Related

Problem With Picking The Right Algorithm

Feb 8, 2007

Hi

I'm using SQL Server 2005. The problem I have is as follows. I have several production lines and as with everything parts in the line tend to break. I have data from all the breaks that occurred in the last 2 years. What I want to do is predict the next break and the production line it's going to happen on. I would also like to go to a future date and check what possible breaks might occur on that date. I've run quite a few models but none of them helps me with future events. I think I might be using the wrong algorithm or Im just not doing it right. If somebody can please suggest an algorithm and maybe help me with a web site that has a tutorial similar to my problem

Thanks
Elmo

View 7 Replies View Related

Picking Up Data Meant For Many People

Nov 29, 2007

Hi,I have a gridview on a page, which should select data from a database. I want it to select the data row if it finds its UserName in the UserName column, but there will be many usernames in the username column, seperated by commas.Here is the select statement that I have at the moment, which doesnt return any data atall:SELECT [message], [senddate], [subject], [messageid], [sendername], [recievername] FROM [Messages] WHERE ([recievername] LIKE '% ' + @recievername + ' %')@recievername = Profile("UserName") which is the vwd wizards way of saying the current user. A Username column has data like this:bezzer , bezlan , beezer , beezler with a space between commas and a space at the start and the end.Thanks if someone can help!Jon  

View 2 Replies View Related

WHERE Clause Not Picking Up 2nd Field Criteria

Jun 20, 2001

Hello,

I have this SP that works, except I need to add another field value for the WHERE clause. As you can see I have "WM" but I need to add "PR", and those two are definitely in the table field. I've tried a variety of syntax arrangements using the AND operator, the OR operator, the & operator, just a comma between the two, nothing between the two. Can someone please show me what I'm doing wrong. It fileters for "WM" fine, but I also need it to filter in the WHERE clause for "PR". Here is the SP:

CREATE procedure spDemoSchedule (@beginDate varchar(10), @endDate varchar(10), @storeNum int)
AS

SELECT Progstats.[Program#], Progstats.KCKOFF, Progstats.ProgramName, Progstats.Parent, Store.[Str#], Store.Status, Progstats.Dept, Store.[Program#], Product.[Item#], Product.[Item]
FROM Progstats INNER JOIN Product ON Progstats.[Program#] = Product.[Program#] INNER JOIN Store ON Progstats.[Program#] = Store.[Program#]
WHERE Progstats.KCKOFF BETWEEN @beginDate AND @endDate AND Store.[Str#]=@storeNum AND Progstats.CLASS="WM"
GO

TIA,
Bruce Wexler

View 2 Replies View Related

2 Structural Questions For Picking Field Type

Jul 25, 2006

Hi all,

Hi am creating a new database not sure which way is the best way to go. Here are the questions

I have a history table which has its an identity field and an identification field which shows what type of history field it is. As in payment, printout, change and so on.... For this field should i use
Full text which has the payment, printout, change and so on and make it easy on the eye and for queries and coded to use the same text all the time
have varchar(2) type of field which holds an understandable field PR, PO, CH and a reference table for it as in PR= PAYMENT, PO = Printout, CH as in change and so on
or have a integer field as in 1, 2, 3 and ref. table 1=payment, 2 = printout, 3=change and so on
I have a address table which holds multiple types of addresses which are really limited count maybe upto 4 different types
have a integer field which links to a reference table as above
have bit fields which can be used to check if it gets in one of the group types
have a full text option like above
Im open for any type of suggestions and i would love to hear the reasons why its picked TY

View 1 Replies View Related

Picking A Data Source File From Configuration

Mar 7, 2008



Hi there,
I believe this is simple requirement but to me it seems to be complicated. My data source is excel. I would like to configure the path of my data source in SSIS Package and file name is not a constant value. SSIS package should pick the file named as Today's (current) date from the configured path. As well, package should catch the exception for example file not found and log into custom log. It would be gr8 if some body helps me out.

View 7 Replies View Related

Package Not Picking Up Latest Config File Changes

Nov 7, 2006

Hi,

I've created a solution with 5 packages in. I've scripted a config file for each package where I would be able to change the source file connection and the database servers (source and destination). On the first package, I am able to change the config attributes and the affects are picked up, but the rest of the packages do no pick up the changes.

For example, if the source file is "C:Filessource.txt" and this is changed to "C:Filessource123.txt" (Just to make sure it fails) it wont pick up the new changes and still uses "C:Filessource.txt".

Also, I tried changing the name of config file itself and the package still ran as if nothing had changed. It's as if it's not recognising the config file.

Any ideas ?

Thanks, Richie.

View 9 Replies View Related

Report Not Picking Up Custom Style Sheet OR HtmlViewer.css Changes

Feb 27, 2007

I cannot seem to get the report to pick up the custom style sheet I placed into Reporting servicesReportServerStyles subdirectory. Nor will it pick up any changes to the HTMLViewer.css either. The report obviously isn't looking there perhaps? I've used the http: etc. ReportServer/Pages/ReportViewer.aspx?%reportname%&rs:Format=HTML4.0&rs:Command=Render&rc:StyleSheet=MyStyleSheetname

I've even modified the HTMLViewer.css file as well. Nothing. Nada. Is there somewhere else this is cached? Or some way I can find where the report looks for this?



View 4 Replies View Related

Integration Services :: For Each Loop Container Is Not Picking Up CSV Source File

Jul 2, 2015

My package is having .csv file as a source and I kept OLEDB destination to load it.

Stored the .csv file in a shared folder and the exact path is given in Enumerator configuration of the foreach loop container. When I execute my package, it is giving the warning as below:

It is saying that file is not there in the specified path and directory is empty. I am running the SSIS package from TFS. I am sure that I have read and write access for the shared folder for my userID. Is there any access there to pick up this file from path.

View 4 Replies View Related

SQL Server Admin 2014 :: Picking Static Port Number For Named Instance

Apr 3, 2015

Basically the question is, which number should I pick?

View 4 Replies View Related

SQL 2012 :: Picking Number String Out Of Text String

Jul 14, 2015

I have a text field which has entries of variable length of the form:

"house:app.apx&resultid=1234,clientip"
or
"tost:app.apx&resultid=123,clientip"
or
"airplane:app.apx&resultid=123489,clientip"

I'm trying to pick out the numbers between resultid='...',clientip no matter what the rest of the string looks like. So in this example it would be the numbers:

1234
123
12389

the part of the string of the form resultid='...',clientip always stays the same except the length of the number can vary.

View 5 Replies View Related

SQL 2000: T-SQL @@Error Not Picking Up Error

Oct 23, 2007

Hi

I'm trying to finish a DTS Package by controlling the error flow with @@ERROR.
Yet, there's one error which I can't seem to capture
'Syntax error converting the varchar value to a column of data type int'

If you try the code below, I can't seem to pull an @@ERROR ID from it.

IF
OBJECT_ID('tempdb..##LogTemp') IS NOT NULL
BEGIN
DROP TABLE
##LogTemp
END

CREATE TABLE
##LogTemp
(
Error_ID INT
)
INSERT INTO ##LogTemp
(
Error_ID
)

VALUES
(
'Test'
)

PRINT @@ERROR

Query Analyzer prints this out:

Server: Msg 241, Level 16, State 1, Line 83
Syntax error converting datetime from character string.

View 7 Replies View Related

Average

Sep 26, 2007

Hello,I have two tables:[Posts] > PostId, ...[Ratings] > RatingId, PostId, RatingI want to select all posts and add a new column named AverageRating.This new column is the average of all ratings associated to that Post.If a post was not rated then its AverageRating would be NULL.How can I do this?Thanks,Miguel

View 2 Replies View Related

Average Help

Apr 18, 2006

if I have a bunch of times, and want an average of them, how do I do that?

Thanks.

For more details, see this thread:

http://www.dbforums.com/showthread.php?t=1215633

View 8 Replies View Related

Average?

Jun 19, 2007

I am getting the number of transactions for two different months based on dates entered from the user. I need to display the number of transactions along with the average sale. I have figured out how to get the dates and sum the transactions but I can't seem to add in the average....

(@prevMonthStart datetime, @prevMonthEnd datetime, @thisMonthStart datetime, @thisMonthEnd datetime)

as

select sum(case when orders.orderdate between @prevMonthStart and @prevMonthEnd then 1 else 0 end ) as PrevMonthCount,

sum(case when orders.orderdate between @thisMonthStart and @thisMonthEnd then 1 else 0 end ) as ThisMonthCount

(how do I add in the average for both months? The column is orders.ordertotal)

View 3 Replies View Related

Getting Average?

Jan 6, 2008

Using MS SQL 2005 server how can I get the average cost price of item 'shirt' from this table please?

code item description cost retail
---- ---- ----------- ---- ------
191 shirt shirt - white 2.30 9.99
170 pants pants - beige 6.34 15.00
196 shirt shirt - red 2.64 9.99
199 shirt shirt - blue 2.61 9.99

View 2 Replies View Related







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