Specific SQL For Total Billing

Apr 10, 2008

My DB has the following description

Table & Columns: ID field is identity. Currency is either CDN or USD.
Order: ID, DateOrder, Currency, AmountOrder
Invoice: ID, NumOrder, DateInvoice, AmountInvoice
Credit: ID, NumOrder, NumInvoice, DateCredit, AmountCredit
Rate: YYYY, MM, US


Relationship among tables:
Order & Invoice : on Order.ID = Invoice.NumOrder, one to many
Order & Credit: on Order.ID = Credit.NumOrder, one to many
Invoice & Credit: on Invoice.ID = Credit.NumInvoice, one to many

Other conditions:
For orders of currency USD, the rate of the DateOrder is used to convert the Order.AmountOrder to CDN.
The rate of DateInvoice is used to convert the Invoice.AmountInvoice to CDN.
A credit always uses the rate of DateInvoice of its associated invoice.

Report: Total billings of USD sales in a period of time (startDate, endDate), converted to CDN

Total billing of USD sales converted to CDN = sum [AmountInvoice * rate of DateInvoice – sum(AmountCredit * rate of DateInvoice)]
WHERE the DateInvoice and DateCredit must be in the specified period.
AND associated Order.Devise = USD


SQL:
1 SELECT SUM (AmountInvoice*dbo.GetRate(DateInvoice) – SUM (AmountCredit*dbo.GetRate(DateInvoice)) as TotalBilling
2 FROM Order IINNER JOIN Invoice ON Order.ID = Invoice.NumOrder
3 LEFT JOIN Credit ON Invoice.ID = Credit.NumInvoice
4 WHERE Order.Devise = ‘USD’
5 AND DateInvoice >= startDate AND DateInvoice <= endDate
6 AND DateCredit >= startDate AND DateCredit <= endDate

Actual Results: Only orders which have both invoices & credits. The orders which invoices but no credit are not included in the results.


How can I correct this, please?

View 5 Replies


ADVERTISEMENT

Matrix - Hiding A Specific Row In Column Total

Feb 1, 2007

Hi,

I have created a matrix with multiple rows in the main data cell and a subtotal at the end of the row. The first row in my matrix main cell is just a count of records, whereas the 2nd row is a % of the value in the 1st row compared to the total of that row. I have 5 columns in the matrix as below (only first row is shown plus the total row. Ignore any rounding issues):










Status
A
B
C
D
E
Total

02/01/2007
No.
9
32
3
13
0
57

% Total
15.00%
56.00%
5.00%
24.00%
0.00%
100.00%

Total
No.
77
143
25
72
2
319

% Total
135.00%
350.00%
48.00%
250.00%
2.00%
556.00%

I have a total column at the end. I want to hide the %Total row within the total row as it does not make sense in this context. Anyone know how to achieve this?

Thanks

View 4 Replies View Related

Show Total Amount Of Rows In Specific Table

May 20, 2012

I need to show the total amount of rows in a specific table?

The query is as follows:

As part of the planning process to expand the database that supports Northwind operations, the IT manager would like to know how many rows are currently in specific tables so that he can conduct capacity planning.

The results needed include two columns, TableName( containing all the tables in the database and Rows, which contain the total amount of all the rows per table).

View 4 Replies View Related

Analysis :: Order Total For Orders Containing A Specific Product Group?

May 27, 2015

I have a request for being able to show ordertotals for those orders that contains a specific productgroup.

Case

Order 1   ProductGroup   Value
A 20
B 40
C 40
Total 100

Order 2   ProductGroup   Value
A 20
D 40
Total 60

So if i slice OrderTotal by ProductGroup the result will be

A 160
B 100
C 100
D 60
ALL            160

how to solve this.

View 9 Replies View Related

Power Pivot :: Total For Specific Date Range As Well As JTD Totals

Jul 8, 2015

In the typical Job Cost Reporting world you can easily create SQL Views / Stored Procedures (or create a report) that can return both Job To Date values along with period values for another date range.  Something like this:

Job,
Phase of Work,
Cost Type,
UM,
Budgeted Cost,
Budgeted Quantity,
Period Cost,
Period Quantity,
Period Unit Cost,
JTD Cost,
JTD Quanity,
JTD Unit Cost,
Variance between JTD and Period Cost,
Variance between JTD and Period Quantity,
Variance between JTD and Period Unit Cost

But in the tabular BI world I am not certain how to have these values show up in a pivot table or BI visualization.  My current Fact table is essentially every job cost transaction so it is at the most granular level holding Company, Job, Phase of Work, UM, Cost Type, Date, Budget Cost, Budget Quantity, Actual Cost, Actual Quantity.  The Budget values only have a value if the entry was updating the budget and the Actual values hold actual cost for each transaction. If you don't restrict by date you have your Budgeted Quantity / Cost and Job To date Cost / Quantity.

I can generate a Pivot table or chart and not restrict by date and I can get all the Budget Totals and the Job To Date Totals when I have the Company, Job, Phase Of Work, Cost Type.

But what if I want to look at a range like last quarter or yesterday or last week.  I can indeed filter that data in Pivot Table and get the period values, but now I don't know how to have the Job To Date values show up for comparison purposes.

I am guessing maybe a DAX formula (if using Excel Powerpivot or SQL Tabular Model), but I am at a loss.

So in the BI world how do you get Job To Date totals while also viewing the data for a period of time?

Here are some example graphs we would want to show for a single Job.  Let's say we want to show the 10 worst performing cost distributions. 

I would want a graph to show the cost distribution and for each cost distribution it would show Budgeted Cost, Period Cost, Job To Date Cost.I would want a graph to show the cost distribution and for each cost distribution it would show Budgeted Unit / Cost, Period Unit / Cost, Job To Date Unit / Cost.I would want a graph to show the cost distribution and the variance between JTD and Budget and Period and Budget.

As you can see it all stems around seeing JTD and Budget values vs Period Values. Here is a visualization that may work. I am using PivotTable filtered on one Company, Job, Phase of Work. I can see the sum of actual cost which is the totals for the date range I provided. But I still want to see the JTD totals for that Phase of Work and Cost Types and actually the Budget Totals as that is sort of a JTD, but for budget... especially if I want to compare Budget vs JTD vs Actual.

View 8 Replies View Related

Reporting Services :: SSRS Group Total To Contain Values Of Specific Groups Only

May 25, 2015

I have a requirement to display the total of a Group after subtracting a specific value from the same Group.

Example: Say the below data is grouped on a particular column 

Group Values Month

Jan-15 Feb-15 Mar-15

A 10 20 30
B 5 10 25
C 1 2 3
D 5 10 15

Total 11 22 33

Formula is :  Sum(A+C+D)- Sum(B)

What is the best way to Group the above scenario from SSRS level and display the result as shown above. I am able to display all the values except the last total row where am displaying the complete total i.e. 21  42  73.

How do I dynamically subtract the values for row B which is one of the group values.

View 4 Replies View Related

Transact SQL :: Adding Count Before And After A Specific Time Doesn't Match Total Number Of Records

Nov 19, 2015

If I just use a simple select statement, I find that I have 8286 records within a specified date range.

If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277

Is there something I am doing incorrectly in the following sql?

DECLARE @startdate date = '03-06-2015'
DECLARE @enddate date = '10-31-2015'
DECLARE @afterTime time = '17:00'
SELECT
General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct

[Code] ....

View 20 Replies View Related

# Of Billing Cycles

Jul 20, 2005

helloi need to write an sql which calculates # of billing cycles for anorder for which ive order start and end date. the first invoice willbe cut on the order start date. Also if i apply a coupon for lets sayone month (30 days) to that order i need to calculate how many billingcycles that coupon will apply to.ive start and end date of coupon ina diff table for that orderfor eg: if an order was started on 1/15 and ended on 4/14 i have 3billing cycles (the invoice was suppose to cut on. 1/15 , 2/15 and3/15)lets say on 3/15 if a users applied one month coupon which means hedidnt have to pay for billing cycle 3/15.can someone help me with this logicthank uml

View 1 Replies View Related

Monthly CD Billing Import

Aug 4, 2006

Greetings;
I recieve a monthly phone bill on cd. I am trying to autoimport these CD's into a SQL server database with a DTS in MSSQL 2000. I have noticed on the bills every so often they change the structure of one of the ACCESS DB tables by one row or something small.
I am trying to figure out how I can test the structure of the database before trying to do the actual imports, I want to do this so someone else can actually do the imports as we get the bills.
Is this a possibility? Or am I going about this all wrong?
 
Thanks
nhas

View 2 Replies View Related

Transact SQL :: Get Start And End Date Of Billing Cycle

Oct 9, 2015

I have weekly billing cycle. And the billing cycle can start on any day of the week.

Assumption: Start day of week is MONDAY

Hence, the following

DayNumber Day
1 Monday
if start day is 1, then billing cycle is Monday - Sunday
2 Tuesday
if start day is 2, then billing cycle is Tuesday - Monday
3 Wednesday
if start day is 3, then billing cycle is Wednesday - Tuesday
4 Thursday
if start day is 4, then billing cycle is Thursday - Wednesday
5 Friday
if start day is 5, then billing cycle is Friday - Thursday
6 Saturday
if start day is 6, then billing cycle is Saturday - Friday
7 Sunday
if start day is 7, then billing cycle is Sunday - Saturday

For a given date, i need find start date and end date of current billing cycle.

For example, if my billing cycle starts on 3rd day of week (wednesday), then for the input 09-Oct-2015, i need to get two output 07-Oct-2015 and 13-Oct-2015

View 6 Replies View Related

Power Pivot :: Compute Billing In Previous Period

Jun 14, 2015

Imagine a 5 column dataset with the following fields - Organiser, Date of Invoice, Total Invoice Value, Reimbursements and Service tax charged.  Using the PowerPivot, I want to determine the "Growth in Professional fee billed over the previous period" - please note that previous period need not be previous year because a client may be billed after a gap of 1-2 years as well.To compute growth, I first need to determine the absolute value of Professional fee billed over the previous period.  This is where I am getting stuck.  Since the billing periods for different clients need not be consecutive, I cannot use the SAMEPERIODLASTYEAR function.

In trying to solve the problem, I tried to frame a calculated field formula but could not do so.  Therefore, I tried solving it via a calculated column formula in the PowerPivot window.  My idea here was to determine the client wise previous financial year for each row and then use this column in a calculated field formula to get my desired result.  I am getting an error when I write this formula (see Billing data tab of PowerPivot window).

View 11 Replies View Related

How To Calculat Outstanding Billing Between Two Dates In Ssrs Report

Dec 12, 2007


hai, iam new to ssrs pease help me below calculations.iam taking the parameters as
begin date,Enddate,region,year,site,

My report having outstanding bills information, i need to calculate, here resters are nothing but students, you take any relavent input data.

1)Rosters Outstanding 6-10 Days (Rosters expected, but not received for up to 10 days in the rears),
2)Rosters Outstanding > 30 Days
3)Rosters Current (Rosters expected as of the report end date)
4)Total # Rosters Outstanding (Total rosters expected, but not received)
5)Rosters Queued For Entry Into 3rd Party Systems (Total rosters entered into the SIS System and expected to be entered into, but not already in 3rd party systems)
6)Rosters Entered Into 3rd Party Systems (Total rosters entered into 3rd party systems)
7)Display Total Billable Dollar Amounts Associated With All Outstanding Rosters For All Aging Columns At Bottom Of Report By Daily, Weekly, Monthly, SYTD





thanks

View 1 Replies View Related

Total Page Writes/total Amount Of Data Change In A Set Period Of Time

Jun 9, 2004

Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.

TIA

View 5 Replies View Related

Aggregated Subquery - Sum Total Trips And Total Values As Separate Columns By Day

Feb 26, 2014

Very new to SQL and trying to get this query to run. I need to sum the total trips and total values as separate columns by day to insert them into another table.....

My code is as follows;

Insert Into [dbo].[CombinedTripTotalsDaily]
(
Year,
Month,
Week,
DayNo,
Day,
Trip_Date,

[Code] .....

View 3 Replies View Related

Query By Year Group By Total Students Sort By Total For Each County

Jul 20, 2005

I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob

View 4 Replies View Related

Reporting Services :: SSRS Group Total Expression - Add Total Disabled

Oct 26, 2015

For some reason my Add Total is grey out, when i tried to add grand total using some expression.

I have two row & two column groups?

Is there any alternative or how can i enable add total? using expression..as you can see in my Attached Image

I'm using iff condition in my expression.. 

View 15 Replies View Related

SQL Server 2008 :: Pulling Daily Total From Cumulative Total

Jun 28, 2015

I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month. I need to pull the difference of todays cumulative total less yesterdays. So when my total for today is 30,000 and yesterday's is 28,800, my sales for today would be 1,200. I want to write this to a new field but I just can't seen to get the net sales for the day. Here is some sample data. For daily sales for 6-24 I want to see 2,000, for 6-25 3,000, 6-26 3,500, and 6-27 3,500. I'm thinking a case when but can't seem to get it right.

CREATE TABLE sales
(date_created date,
sales decimal (19,2))
INSERT INTO sales (date_created, sales)
VALUES ('6-23-15', '20000.00'),
('6-24-15', '22000.00'),
('6-25-15', '25000.00'),
('6-26-15', '28500.00'),
('6-27-15', '32000.00')

View 9 Replies View Related

Total Record Count - Pagination With Total Rows

Jul 26, 2013

My table contains 1000 records,

I need to know the total record count with the below paging query

SELECT EmpName,Place
FROM EmplyeeDetails ORDER BY Place
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

How to get?

View 2 Replies View Related

Adding Subreport Total To Main Report Total

Apr 28, 2006

Hi, can anyone help?

I have created a Report using Visual studio-the report displays a subreport within it.

On the Subjective Report I have 12 values for each month of the year.

For the first month the value is =sum(Fields! Month_1.Value), and I
have named this text box €™SubRepM1€™
The name of the subreport is €˜subreport1'.

On my Main Report, again I have 12 values for each month of the year.
For the first month the value is =sum(Fields! Month_1.Value)*-1, and I
have named this text box 'MainRepM1'
The name of the main report is 'GMSHA Budget Adjustment Differentials'

The report displays both of the subreport and main report values
but I now need to total these values together for each month in order to
produce a grand total.

I have tried using the following to add the totals for Month 1 together,
=subreport1.Report.SubRepM1 + MainRepM1
but this does not work and I get the following error message €˜The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.

I feel that it should be a simple matter of adding the two sets of values together but I€™m having major problems trying to get these totals to work.

Can anyone help, thanks

View 7 Replies View Related

Transact SQL :: Select Specific Values From All Rows Where Value Of A Specific Column Is (Active)

May 23, 2015

I need to select specific values from all rows where the value of a specific column is "Active"

This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client

But I want to add: WHERE StatusType = (Active) and how to do this.

View 4 Replies View Related

SQL Server 2008 :: Replicate Only Specific Data To Specific Subscriber?

Jun 30, 2015

We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.

Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?

View 2 Replies View Related

Can Logging Be Turned Off On Inserts To A Specific Temp Table From A Specific Sp?

Oct 10, 2007

I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.

I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.

For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?

View 4 Replies View Related

Command Line Printing To Specific Printers, And Specific Trays

Jan 23, 2007

Hi All,

Could you guys please help me with printing reports invoked thru command line/ URL access to print automatically to specific printers and specific trays and also is it possible to set the specific printer and tray as parameters.

Any suggestions is appreciated

Thanks A lot in advance

e,g :

http://localhost/reportserver?/testreports/employee sales&UserID='ABC'&LName=Lastname='victor'&rs:Command=Render

View 1 Replies View Related

How To Select A Specific Value From Dataset To Fill A Specific Cell ?

Mar 27, 2007

Hi there !

Thanks for taking the time to read this thread.

I don't know whether anyone has this problem, but I am definitely not using the right keywords to search for a thread.

My situation is this...

I have a dataset that has values to fill cells to multiple tables in a report.
However, I only want to select specific data from the dataset to fill textboxes and others.
I cannot change the stored procedure, but the sample of the data is shown below:-


Row Stat Val
0 dtRpt1 02/01/2005
1 Value1 1
2 Value2 2000
3 dtMailSent 02/28/2005
4 Value3 0
5 Value4 5
6 Value5 658

I know it looks weird, but the row really represents which "row" or textbox is it to fill with the Val. The Stat Column is just a way to make sure that I am filling the right values.

so my new report would have multiple tables to denote different categories.
In my first table, I tried putting the cells as follows:-
(expressions are highlighted in italics and bold)

TextBox1 =IIF(Fields!Row.Value =0, Fields!Val.Value,"")

Table1
Column1
DetailRow1 =IIF(Fields!Row.Value =1, Fields!Val.Value,"")
DetailRow2 =IIF(Fields!Row.Value =2, Fields!Val.Value,"")


Table2
Column1
DetailRow1 =IIF(Fields!Row.Value =3, Fields!Val.Value,"")
DetailRow2 =IIF(Fields!Row.Value =4, Fields!Val.Value,"")
DetailRow3 =IIF(Fields!Row.Value =5, Fields!Val.Value,"")
DetailRow4 =IIF(Fields!Row.Value =6, Fields!Val.Value,"")


I only expect this report to print out one page holding the previous values.

However, it ended up printing like this

----------------------------------------------------------

Table1
Column1
DetailRow1 1
DetailRow2

Column1
DetailRow1
DetailRow2 2000


Table2
Column1
DetailRow1 02/28/2005
DetailRow2
DetailRow3
DetailRow4

Table2
Column1
DetailRow1
DetailRow2 0
DetailRow3
DetailRow4

Table2
Column1
DetailRow1
DetailRow2
DetailRow3 5
DetailRow4

Table2
Column1
DetailRow1
DetailRow2
DetailRow3
DetailRow4 658

------------------------------------------------------

I tried putting it into the headerrows instead of DetailRows, and it ended up printing the last value.
Is there anyway to do this ? print all the values out in one table ? I tried using textboxes, but I think I got my expression wrong.

Is this the correct expression ?

=IIF((Fields!Row.Value,"Dataset") =1, (Fields!Val.value, "Dataset"), "")

and it give me an error
The value expression for the textbox €˜textbox5€™ contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.

Appreciate any advice or suggestion for this scenario !

Thanks!

Bernard

View 3 Replies View Related

Assign Specific Data To Specific Users

Mar 21, 2008

I am very early on in developing a website to track issues with projects which is tied to a SQL database.  I have my Projects Table, my Users Table, and am creating a third table to track issues.  I'm wondering what is the best way to assign specific users to specific data/projects.  The user should only be able to view & update the projects assigned to him.  He should not be able to see other projects.  What is the best way to assign projects/data to the users to make sure they are only viewing their data?

View 1 Replies View Related

Total Spaceused And Total Allocated

Jul 20, 2005

Anyone has a "one sql statement" to get the total spaceused and totalspace allocated of an instance ? ie same as sum of relevance fieldsfrom sp_spaceused for each database in an instance, that works accrossversion of mssql from 6 onward.ThanksKD

View 1 Replies View Related

Calculating The Total Amount Of Drugs Prescribed, Total Amount

Aug 10, 2006

Hi all,



I have a table named Prescription that consists of attributes like PatientId, MedicineCode, MedicineName, Prices of different drugs, quantity of different drugs(e.g 1,2,3,10), date .

I would like to get a summary of the total number and amount of different drugs in a specific period, the total amount of each type of drug.



I kindly request for help.

Thanx in advance.

Ronnie

View 4 Replies View Related

Please Help =IIf(IsError([total]),,[total])

Feb 29, 2008

Hi,

I am trying to create a report on some data. I have about 8 tables and 30+ queries attached to those 15 reports. In one of those reports I want to get the percentage based on the data in the tables and queries. Say I have the minimum hours for an employee as 176 hours and the employee works for 227 hours in a month. I want to see the result in percentage.

My report looks something like this :

ID Name Oct Nov Dec Jan Feb March Total
001 alex 87.6% 104.1% 65.1% 50.2% 85.6%
002 Linda 87.4% 109.1% 68.1% 35.2% 90.8%
003 Jon 87.6% 104.1%
004 alex 87.6% 104.1% 65.1% 50.2% 85.6%
005 Linda 87.4% 109.1% 68.1% 35.2% 90.8%

For the 002 ID, though he has worked for Nov and Dec the total % is blank.

The formula that I used for all of these entries is :

=IIf(IsError([total]),"",[total])

and for the month it is : =IIf(IsError([Oct]),"",[oct]), nov and so on.

It works fine for all, but where ever there is blank in one field it doesn;t calculates for the others too..

Please help, how can I get the total for all.

Thanks,


Farn

View 1 Replies View Related

Total Sales By Customer Vs Total Sales

Nov 26, 2007



I have a report which totals sales by customer. Then table footer has a grand total of all customer sales. I would like to get a percent of each customer's sales against the total sales. How do I get the sum from the table footer to use in an individual customer row?

Thanks.

View 3 Replies View Related

Getting Total From Row

Mar 16, 2006

I have built a query in Access that calculates the total to be charged to clients based on 3 cost columns.

I have tried to run the query in MSSQL but it will not run, so could someone help me out with the syntax in my query.

I have done about half of it myself but I am stuck on the calculation and I havent a clue of the syntax to be used.

This is my Access Query;
SELECT DISTINCT Holiday_Bookings.ClientID,
Holiday_Bookings.Booking_Cost,
Room_Facilities.FacilityCost,
Rooms.CostPerNight, Rooms!CostPerNight*Nights_Stayed+Holiday_Bookings! Booking_Cost+Room_Facilities!FacilityCost AS TotalCost,
[TotalCost]*17.5/100+[TotalCost] AS [Total+VAT]
FROM Room_Facilities
INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID = Rooms.ClientID)
ON Hotels.HotelID = Rooms.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID;

and this is what I have managed to salvage in MSSQL format:
SELECT
Holiday_Bookings.ClientID,
Holiday_Bookings.Booking_Cost,
Rooms.CostPerNight,
Room_Facilities.FacilityCost
FROM
Rooms
INNER JOIN Holiday_Bookings ON (Rooms.Clients_ID = Holiday_Bookings.ClientID)
INNER JOIN Room_Facilities ON (Rooms.FacilityID = Room_Facilities.FacilityID)


Any help would be great as I am completely lost.

View 9 Replies View Related

Get A Total

Oct 19, 2006

Hello Everyone,

How can I get the total of the loan numbers:


USE CHEC
SELECT DISTINCT
DAT01.[_@051] AS Branch,
DAT01.[_@550] AS LoanType,
convert(varchar(10), DAT01.[_@040], 110) AS Date,
DAT01.[_@LOAN#] AS LoanNum
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW]
ON DAT01.[_@040] = [_@040]
INNER JOIN [SMT_BRANCHES]
ON DAT01.[_@051] = SMT_BRANCHES.[BranchNbr]
WHERE
DAT01.[_@040] Between '06/01/2006' And '06/30/2006'
AND DAT01.[_@051] = '540'
And DAT01.[_@LOAN#] Like '2%'
And DAT01.[_@550] = '3'
GROUP BY
DAT01.[_@051],
DAT01.[_@550],
DAT01.[_@TP],
DAT01.[_@040],
DAT01.[_@LOAN#]
ORDER BY
DAT01.[_@051]


Where obviously DAT01.[_@LOAN#] is the LoanNum column.

TIA



Kurt

View 6 Replies View Related

Getting SUM Total

Jan 24, 2007

I have the following query which gives me a list of names, logins, and group name along with a count of how many training modules each individual has sat. This returns 70 rows with each individuals details and totals.

However I want to be able to report instead just the total modules sat for all 70 rows instead of displaying each row individually. This is the query I am using:

SELECTpps_principals.name AS pname, EXT_USER_GROUPS.LOGIN, EXT_USER_GROUPS.NAME,
COUNT(PPS_SCOS.SCO_ID) AS coursecount
FROMPPS_PRINCIPALS
LEFT JOINPPS_TRANSCRIPTS ON PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID
AND PPS_TRANSCRIPTS.STATUS like '[PCF]'
AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
AND pps_transcripts.date_created between '2006-10-01' and '2007-09-30'
LEFT JOINPPS_SCOS ON PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID
AND pps_scos.name like 'MT%'
LEFT JOIN EXT_USER_GROUPS ON EXT_USER_GROUPS.LOGIN = PPS_PRINCIPALS.LOGIN
WHERE pps_principals.login like '%score%' and ext_user_groups.name like 'ALL SCORE PTY'
AND dbo.PPS_PRINCIPALS.DISABLED IS NULL
GROUP BY pps_principals.name, EXT_USER_GROUPS.LOGIN, EXT_USER_GROUPS.NAME
HAVING COUNT(PPS_SCOS.SCO_ID) > 0
ORDER BY pps_principals.name, EXT_USER_GROUPS.LOGIN, EXT_USER_GROUPS.NAME

I am trying to use SUM to get the overall total but without success so far.

Any help gratefully received.

View 2 Replies View Related

Sum (Total)

May 22, 2007

I need to sum the totals for each of the ratings, then group them by owneridname. the problem i am having is that the "Count(*) as total" returns a total that includes ratings that are not listed below as a criteria.

Eg. the total for all ratings for a user john would be 789.
that figure would inlcude amounts for a rating eg. dead. that is not in the list below. please help.

SELECT owneridname, SUM(CASE WHEN new_ratingname = 'Hot' THEN 1 ELSE 0 END) AS Hot,
SUM(CASE WHEN new_ratingname = 'warm' THEN 1 ELSE 0 END) AS Warm, SUM(CASE WHEN new_ratingname = 'cold' THEN 1 ELSE 0 END) AS Cold,
SUM(CASE WHEN new_ratingname = 'cool' THEN 1 ELSE 0 END) AS Cool, SUM(CASE WHEN new_ratingname = 'new' THEN 1 ELSE 0 END) AS New,
SUM(CASE WHEN new_ratingname = 'Reservation Holder' THEN 1 ELSE 0 END) AS [Reservation Holder],
SUM(CASE WHEN new_ratingname = 'site visit' THEN 1 ELSE 0 END) AS [Site Visit], SUM(CASE WHEN new_ratingname IS NULL THEN 1 ELSE 0 END)
AS [Not Rated], SUM(CASE WHEN new_ratingname = 'Continous Updates' THEN 1 ELSE 0 END) AS [Cont Updates],
SUM(CASE WHEN new_ratingname = 'worked tasks' THEN 1 ELSE 0 END) AS [Wkd Tasks], COUNT(*) AS Total
FROM FilteredContact AS filteredcontact
WHERE (statuscodename = 'active')
GROUP BY owneridname

Melvin Felicien
IT Manager
DCG Properties Limited

View 4 Replies View Related







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