Conditional Sum Based On Count

Oct 15, 2013

In the database, there is Date, Store#, Item#, and %Total Sales. In some cases, the same item# for the same date may be given more than one value for '% of Total Sales'. (For some reason this is a valid business scenario that happens rarely, but it happens.)

In that situation only, the requirement is to sum the two values together into one line. So if Item# 123 has a line with a value of .05%, and another line with a value of .08%, I need to sum those two values into one line for Item #123 that has a %Total of .13%. ONLY when an item has more than one percentage assigned, those percentages should be summed. Otherwise, if an item# has only one percentage value assigned, we just want to see that value.

Basically, I would like to implement logic that would work like this:

SELECT Date, Store#, Item#,
CASE WHEN Count(%Total Sales) >1 THEN Sum(%Total Sales)
ELSE %Total Sales
END

FROM (some tables and joins)
GROUP BY Date, Store#, Item#

However, I'm not sure how to craft it so that I don't get a syntax error (this query produces errors).

View 5 Replies

Conditional SQL Count

Feb 13, 2006

Is there a way to have a conditonal count within a stored procedure

ie

Code:

Select field, field2, Count(if Field3 > 0)

(i know this code will not work)

The idea is to only count records if they have a value greater than zero

View 7 Replies View Related

Conditional Row Count

Jan 8, 2007

Hi,

Is there a way to to use RowCount based on a condition?

I have AS400 logs in csv file which I want to append to a SQL table using filters. But during passing of each record, I also want to count row only if they matches to a specific criteria.

Thanks

View 1 Replies View Related

Conditional Count

Aug 29, 2006

Hello,

I have a report, for which I want to do a conditional count. The table is something like this:
ID Date Description
1 30-09-2000 10:36:06 Desc1
2 15-03-2000 11:45:11 Desc2
3 12-07-2005 16:21:10 Desc3
4 10-09-2006 11:12:18 Desc4
(...)

I want to count only the entries where the Date field has the value 2000.
The expressiona that I am using is the following one:
=Sum(IIF(Fields!Date.Value.ToString().Contains("2000"),1,0))
It is giving me the right value but my problem is that the value apears repeated. Something like:
CountResult
2
2
2
(...)
What I want to have is just one value of the conditional count.
Has anyone passed by something like this? Any solutions?

Thanks and best regards

vjn

View 6 Replies View Related

INSERT Based On Conditional

Jun 4, 2008

Good Afternoon,

I am new to MSSQL and am trying to write a complicated SQL statement that I'm having trouble with. Any help that anyone can offer is much appreciated!

Here is the problem I am tackling:

I have a list of about 5,000 members of our organization stored in the MemberList MSSQL table. I have a separate MSSQL table (CityList) that has approximately 500,000 resident of a city.

I am trying to find matches between MemberList and CityList for the purposes of figuring out which of our members are registered voters.

The tricky part of this problem, is that there is no unique ID (such as a social security number) that is present in each list. Accordingly, I have decided to created several types of matches:

1. NameDOBMatch: Where the FirstName, LastName & DOB fields in MemberList table match the same fields in CityList table.

2. NameAddressMatch: Where the LastName, FirstName & Address fields in the MemberList table match the same fields in the CityList table.

3. DoubleMatch: A combination of the first two matches (i.e. where the LastName, FirstName, DOB, & Address fields in the MemberList table match the same fields in the CityList table).

My goal is to "loop" through the MemberList and CityList tables and to add a new row to a third MSSQL table (MemberMatch) each time one of the aforementioned matches is found. The MemberMatch table has the following fields:
1. MatchID (key)
2. MemberID (Unique ID of member from MemberList table)
3. ResidentID (Unqiue ID of matching member from CityList table).
4. MatchType (value of NameDOBMatch, NameAddressMatch or DoubleMatch, depending on match type).

If anyone could help me create an SQL statement that would accomplish that, I would very much appreciate it!

Thanks,
Bryan

View 9 Replies View Related

Conditional Sum Based On Visibility

Mar 12, 2007

Hi,

I have a report that is conditionally showing a textbox based on the previous entry that is working correctly.

My issue is that the non visible entries are still being added to my Sum statement at the end of the report.

I need a way to exclude an entry based on its visibility.

Any help would be greatly appreciated.

View 9 Replies View Related

Inner Join Based On Conditional Column

Mar 5, 2014

I Have Table Called 'Sales' and 'Voucher',I Need To Show Each Customer ""Dueamount"" Details Based Upon Customer Paid in 'Voucher' Table But One thing I have Not Maintained Transaction History For Customer in 'Sales' Table Means I Have Column named "CreditAmount" in 'Sales' and Column Named "VoucherAmount" in 'Voucher' ,For every transaction I am updating Column named "CreditAmount" in 'Sales', So finally 'Dueamount' Must be calculated according to "VoucherAmount" of customer in 'Voucher' Table....

Sales Table:

BillMasterId BillDate CustomerId NetAmount CreditAmount

26 03/03/2014 101 1000 1000

My Query:
SELECT CONVERT(varchar,BillDate,103) as BillDate,isnull(NetAmount,0) as BillAmount, case when VoucherAmount != 0 then sum(VoucherAmount)else 0 end as'AmountReceived',case when CreditAmount !=0 then CreditAmount else 0 end as 'DueAmount' from Voucher INNER join Sales on CustomerId=CustomerID and BillMasterID=BillMasterID WHERE CONVERT(varchar,BillDate,103)='03/03/2014' AND CustomerId=101

My Output:

BillDate BillAmount AmountReceived DueAmount

03/03/2014 1000 0 0
03/03/2014 1000 500 0
03/03/2014 1000 300 0
03/03/2014 1000 200 0

Exact Output:

BillDate BillAmount AmountReceived DueAmount

03/03/2014 1000 0 1000
03/03/2014 1000 500 500
03/03/2014 1000 300 200
03/03/2014 1000 200 0

View 7 Replies View Related

Conditional Split Based On Conditions

Sep 23, 2007

Hi,

I have the following table in MsAccess

EmployeesA

empId integer,

empName varchar(60),

empAge integer,

empStatus char(1) - can be N,D or S - New, Deleted or Shifted

and the following in Sql2005

EmployeesB

Id smallint,

Name varchar(60),

Age int,

Status char(1) - Bydefault 'N'

I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now.

If empStatus = N in EmployeesA, then insert a new record in EmployeesB

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.

How do I do it for each table each row in EmployeesA using a foreach file loop?

Thanks,

lolsron

View 6 Replies View Related

Conditional Formatting Based On Second Dataset

Jun 15, 2007

I am trying to create a report which has conditional formatting.

The primary dataset is a view of objects with several values

eg

object1,0,4,0,1

object2,0,3,1,1

The secondary dataset is the comparison table and just contains the values

eg

0,3,1,1

I'd like to conditionally format the values based on the comparison table but when I create an expression comparing to the second dateset

eg

=iif(Fields!object1.Value <> Fields!comp_object1.Value , "Red", "SkyBlue")

i get

Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Not sure if there is a way to tell the expression to look for comp_object1 in dataset2, even tho it is uniquely named?

Ideas gratefully received !

View 7 Replies View Related

Conditional Formating Based On Second Dataset.

Mar 15, 2007

I have two databases located on seperate machines, using different platforms and credentials.

I have setup dataset1 source to the first database wich provides me the following colums

Incident_id, description, date, status.

And dataset2 datasource provides

incident_id, is_billable

My report is a tabular report showing the four fields from dataset1.

I am trying to apply some conditional formating wich will link the two datasets on the incident_id field.

something along the line of if the dataset1.incident_id is in dataset2.incident_id then turn it red.

I tried using the below expression on the color property but the IN part is not allowed.

=iif(Fields!INCIDENT_ID.Value,"Dataset1" in Fields!INCIDENT_ID.Value,"Dataset2","Black","Red")

View 2 Replies View Related

Conditional Insert Based On MAX Number Of Field

Dec 15, 2005

Hi!

I have a table called DB1 that contains this:

MID
IIN
NUM_EVENTS
DATE

MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?

View 6 Replies View Related

Conditional Insert Based On MAX Number Of Field

Dec 15, 2005

View 3 Replies View Related

Conditional Fields Based On Export Type

Aug 27, 2007

I need to be able to suppress the printing of a particular value when exporting, but not when displaying on a web viewer on-line. I can place an IIF() condition around the field to do this, but do not know how to obtain a parameter/value/function which would recognize that the viewer has selected an export (To .PDF for example).
I would prefer there be a direct parameter I can read from the RDL language, however recognizing the selection while setting up the viewer to be displayed in the code-behind and setting an external parameter is also an option.

Any help would be appreciated.

Jerry

View 2 Replies View Related

Transact SQL :: How To Get Results Based On Conditional Where Clause

Jul 14, 2015

My source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum              PolicyStartdate
123G                       01/01/2012
456D                       02/16/2012
789A                       01/21/2012
163J                       05/25/2012

Now my output should return based on 3 parameters..

First two parameters are date range... let say @fromdt and @todt

Third parameter is @policynum

Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output

Scenario-2: enter policy num in policynum param and don't select any dates
Ex: policynum ='456D'     It returns 2nd row in the output

Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'. it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)

I need t-sql code to get above results.

View 12 Replies View Related

Retrieving Data From A DB Based On Output Of A Conditional Split

Sep 5, 2007

This is probably an easy question, and I just can't find the solution. I've searched extensively, but I am probably just not searching for exactly what I need.

Basically, I have a Conditional Split. What I need to do is for each row coming out of my split, I need to SELECT some data from another database based on one of the fields and then place the data from the DB into a file for later processing.

Seems pretty simple, considering the power of SSIS. Using tools such as OLE DB Command didn't help - the data that comes out of the OLE DB Command is the input data, not the data returned by the command.

How can I do this?

Thank you!

Nolan

View 1 Replies View Related

Analysis :: Conditional Sum Based On Latest Survey Answer

Jul 24, 2015

Basically our cube contains two different Facts:

- Sales Volume: Information about sold articles to a customer incl. Selling date
- Survey: irregular answered survey questions about customers incl. date of answer
and three Dimensions:
- Customer
- Date
- Survey Answer: Information about possible Answer values (e.g. Yes / No)

Relations:
Fact: Sales Volume ------> Dim: Customer <------ Fact: Survey   ------> Dim: Survey Answer
                    '-------------->      Dim: Date       <----------------'

We would like to be able to determine the aggregated sales volume (sum) of a customer for a specific period depending on the latest survey answer within this period.

For example:
Selected Time period: Jan - Jul 2015
Sales Volume Customer X - Jan - Jul 2015: 1000 Litres
Sales Volume Customer Y - Jan - Jul 2015: 500 Litres

Surveys answered:

15th Jan 15: Customer X, Survey Question A: Yes
2nd Mar 15: Customer X, Survey Question A: No
20th Apr 15: Customer X, Survey Question A: Yes

10th Feb 15: Customer Y, Survey Question A: Yes
20th Jul 15: Customer Y, Survey Question A: No

Latest survey answer (Jan-Jul) Customer X, Question A: Yes
Latest survey answer (Jan-Jul) Customer Y, Question A: No

Excel Pivot should show something like this:

Question       |      Latest Answer:     Yes                |       No         |

------------------------------------------------------------------------------------
A                    |     1000 Litres     |    500 Litres    |

How this is possible and has to be implemented?

View 5 Replies View Related

Integration Services :: SSIS Execute Multiple Packages Based On Conditional Split

Sep 4, 2015

We are building a dataload application where parameters are store in a table. And there are multiple packages for each load.There is a column IsChecked column if it is 1 then only the child package should execute.Created a master package. In which i have taken execute SQL task in that storing a results in variable and based on the result the child package should execute. But In executesql task i selected result set as full result set. I am getting the below error.

[Execute SQL Task] Error: Executing the query "SELECT isnull(ID ,0) AS ID FROM DataLoadParameter..." failed with the following error: "The type of the value (DBNull) being assigned to variable "User::LoadValue" differs from the current variable type (Int32). Variables may not change type during execution. Variable types are strict, except for variables of type Object.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

View 3 Replies View Related

SQL Server 2012 :: Have Conditional Join / Union Based On Parameters Passed To Stored Procedure

Jul 15, 2014

I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.

I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.

View 6 Replies View Related

Matrix Report:Adding Average Column/Sorting Based On Last Month/Conditional Formating

Aug 2, 2007

I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like

Sale Profit
Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007
A 100 200 300 20 25 15
B 200 250 50 30 45 19

how can i do following 3 things

1)Add Total column for Sale and Average column for Profit
2)Sort report by lastMonth of Sale (here 7/1/2007) High to low
3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red

thanks

View 1 Replies View Related

Getting Top 5 Based On The Count?

Aug 19, 2013

I am using the following query

select top(5) COUNT(incident),name from company_info group by name
order by COUNT(incident) desc ;

to get most repeated incidents.

countincident
17ghi
15def
14abc
11z
9x

I want to get the same output with out using order by query.

View 4 Replies View Related

Alphabetical Value Based Of Count?

Mar 6, 2014

there is another way to get the alphabetical value for a number other than using a case statement?

example:

Count = 1 That's = A
Count = 5 That's = E
Count = 8 That's = H

and so on I know a case could do this but wondering if there is a nice function or simple statement that would do this on the fly?

View 2 Replies View Related

Questions About Memory Based Bulk Copy Operation(InsertRow Count,array Insert Directly,set Memory Based Bulk Copy Option)

Feb 15, 2007

Hi~, I have 3 questions about memory based bulk copy.

1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)?
For example, how much insert row at below sample?(the max value of nCount)
for(i=0 ; i<nCount ; i++)
{
pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData));
}

2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop)

3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ?
BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK);

-------------------------------------------------------
My solution is like this. Is it correct?

// CoCreateInstance(...);
// Data source
// Create session

m_TableID.uName.pwszName = m_wszTableName;
m_TableID.eKind = DBKIND_NAME;

DBPROP rgProps[1];
DBPROPSET PropSet[1];

rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[0].colid = DB_NULLID;
rgProps[0].vValue.vt = VT_BSTR;
rgProps[0].dwPropertyID = SSPROP_FASTLOADOPTIONS;
rgProps[0].vValue.bstrVal = L"ROWS_PER_BATCH = 10000,TABLOCK";

PropSet[0].rgProperties = rgProps;
PropSet[0].cProperties = 1;
PropSet[0].guidPropertySet = DBPROPSET_SQLSERVERROWSET;

if(m_pIOpenRowset)
{
if(FAILED(m_pIOpenRowset->OpenRowset(NULL,&m_TableID,NULL,IID_IRowsetFastLoad,1,PropSet,(LPUNKNOWN*)&m_pIRowsetFastLoad)))
{
return FALSE;
}
}
else
{
return FALSE;
}

View 6 Replies View Related

Acceptable Data Based On Count?

Jan 16, 2007

I have x amount of tests for a person on a particular day. There are criteria to invalidate a test. I can filter all the bad tests away, but now I need to only accept 2 valid tests per day, even if there are more tests. The tests are filtered according to person ID and then put in order by date. I need to accept the first 2 valid test based on the criteria and reject the others. Any suggestions

View 2 Replies View Related

Create Record Based On Count

Nov 9, 2007

I have the following data,

CustomerID EngID EngCount
1 A11 2
2 B12 1
3 C10 3

I need to display it as,

CustomerID EngID EngCount
1 A11 1
1 A11 1
2 B12 1
3 C10 1
3 C10 1
3 C10 1

Create a record based on the EngCount. So CustomerID of 3 has 3 records as shown above.

How to do?

View 5 Replies View Related

SQL Count Records Based On 2 Columns

Oct 15, 2007

Newbie alrt...

I am trying to create an asp page that will update an Access 2000 database. I need to update records if the user exists and create a new record if the user does not exist. Most of the variables are pulled from a separate "post" form.

I am using 2 pieces of info to find duplicates, as employee numbers can be assigned to multiple employees. Right now I have the page check for a duplicate employee id number then check for a duplicate last name. Unfortunately it is running each check separately, so if the last name is duplicated anywhere, it is sending a duplicated value.

here is the chunk of code in question... all RF_variables are request.form variables

cnt="SELECT COUNT(emp_id) AS Xnum FROM " & RF_course
cnt=cnt & " WHERE emp_id='" & RF_emp_id & "'"
set again=conn.Execute(cnt)
dup=again("Xnum")

if dup>=1 then
cnt="SELECT COUNT(lname) AS Xnum FROM " & RF_course
cnt=cnt & " WHERE lname='" & RF_lname & "'"
set again=conn.Execute(cnt)
dupl=again("Xnum")

if dupl=1 then
upd="UPDATE " & RF_course & " SET "
upd=upd & "section" & RF_section & "='" & RF_score & "'"
upd=upd & " WHERE emp_id='" & RF_emp_id & "'"
upd=upd & " AND lname='" & RF_lname & "'"
on error resume next
conn.Execute upd
else
ins="INSERT INTO " & RF_course
ins=ins & " (lname,fname,emp_id,cname,"
ins=ins & "section" & RF_section & ")"
ins=ins & " VALUES "
ins=ins & "('" & RF_lname & "',"
ins=ins & "'" & RF_fname & "',"
ins=ins & "'" & RF_emp_id & "',"
ins=ins & "'" & RF_cname & "',"
ins=ins & "'" & RF_score & "')"
on error resume next
conn.Execute ins
end if
else
ins="INSERT INTO " & RF_course
ins=ins & " (lname,fname,emp_id,cname,"
ins=ins & "section" & RF_section & ")"
ins=ins & " VALUES "
ins=ins & "('" & RF_lname & "',"
ins=ins & "'" & RF_fname & "',"
ins=ins & "'" & RF_emp_id & "',"
ins=ins & "'" & RF_cname & "',"
ins=ins & "'" & RF_score & "')"
on error resume next
conn.Execute ins
end if

Hopefully this is understandable.
If anyone can offer any help I would greatly appreciate it.

Thanks

View 1 Replies View Related

MDX - Select Count Based On Two Different Dimensions

May 19, 2008

I have a fact table with a create time dimension and an expiration time dimension. I'd like to have a calculated member that would compare the (count for create time) / (count with that expiration time). I already have these counts as measures.

I would be able to put the create time dimension in the "row fields" area, and see the ratio (calculated above)
over the different create time periods.

Can someone point me in the right direction on how I would create that kind of calculated member? What would the MDX look like?

Thanks for your help

View 1 Replies View Related

Query To Count # Records In Db Based On Foreign Key

Mar 14, 2008

My SQL is very basic. How do I create a query that will accept a parameter, an integer, and based on the integer, locate all the matches in a db?
SELECT COUNT(*) AS Expr1, tblArticle.ArticleIDFROM tblArticle INNER JOIN tblArticleCategory ON tblArticle.ArticleCatID = tblArticleCategory.ACategoryIDGROUP BY tblArticle.ArticleID
This isn't setting up the query to request a parameter.What am I doing wrong here? I"m trying to get the total number of articles for a particular category ID.

View 3 Replies View Related

Updating Field Based On Record Count

Oct 18, 2004

I am trying to write a stored procedure that updates a value in a table based on the sort order. For example, my table has a field "OfferAmount". When this field is updated, I need to resort the records and update the "CurrRank" field with values 1 through whatever. As per my question marks below, I am not sure how to do this.

Update CurrRank = ??? from tblAppKitOffers
where appkitid = 3 AND (OfferStatusCode = 'O' OR OfferStatusCODE = 'D')
ORDER BY tblAppKitOffers.OfferAmount Desc

All help is greatly appreciated.

View 2 Replies View Related

T-SQL (SS2K8) :: Count Record Based On Group

Dec 10, 2014

This my table named myData

CREATE TABLE [dbo].[myData](
[idx] [int] NULL,
[paymentMethod] [nvarchar](200) NULL,
[daerahKutipan] [int] NULL,
[payer] [int] NULL,

[code]....

I want to calculate the number of payer Group By paymentMethod. The number of payer must be divided by daerahKutipan. So far, my query as follow

select paymentMethod,
COUNT(CASE WHEN daerahKutipan = 1 THEN payer ELSE 0 END) figure_Seremban,
COUNT(CASE WHEN daerahKutipan = 3 THEN payer ELSE 0 END) figure_KualaPilah,
COUNT(CASE WHEN daerahKutipan = 4 THEN payer ELSE 0 END) figure_PortDickson,
COUNT(CASE WHEN daerahKutipan = 5 THEN payer ELSE 0 END) figure_Jelebu,
COUNT(CASE WHEN daerahKutipan = 6 THEN payer ELSE 0 END) figure_Tampin,
COUNT(CASE WHEN daerahKutipan = 7 THEN payer ELSE 0 END) figure_Rembau,

[code]....

View 1 Replies View Related

SQL Server 2012 :: How To Get Consecutive Count Based On First Value

Jan 13, 2015

We have customer accounts that we measure usage. We want to run a report for all customers whose current usage is 0 and a count of how many months it has been zero consecutively. Here is an example.

declare @YourTable table (
CustomerID int,
ReadDate datetime,
usage int
)

insert into @YourTable select 1,' 1 mar 2014',0
insert into @YourTable select 1,' 1 feb 2014',0

[Code] ....

This should return

1,3
2,1

This is what I am currently using but it isn't working right

WITH cte
AS
(
SELECT *,COUNT(1) OVER(PARTITION BY cnt,CustomerID) pt FROM
(
SELECT tt.*

[Code] .....

View 9 Replies View Related

SQL Server 2012 :: Filter Based On Count Of Value

Mar 10, 2015

I have the table below and want to show the prop_code if the rent_review_date count is less than 1 in 12 months. This means to show only propcode if there has not been any rent update since the first rent_review_date

DECLARE @table TABLE
( Prop_Code INT
,Current_Rent INT
,Revised_Rent INT
,Rent_Review_Date varchar(10)
,Rent_Review_Time DATEtime)

[Code] .....

View 6 Replies View Related

Transact SQL :: How To Count And Sum A Date Based On Conditions

Jun 18, 2015

I have conducted a thorough search in the forums and cannot quite find my answer. I have a date field called open_date. If the open_date is more than 30 days old, I need to count it. I have started with the following code:

SELECT 'Older_Than_30Days' =
CASE
WHEN open.date >= 30 THEN '1'
ELSE '0"
END

My problem is the WHEN.

View 6 Replies View Related

How To Count Cases For Different Groups Based On Different Criteria

Apr 25, 2008

Hello,

I need to create a query that will count new cases based on the create date(create_date) and criteria for the groups(The only way to distinguish between the 2 major groups mts and bnb is area!= 'bnb" because everything else is MTS). The sample report I need to create below shows how it needs to be counted weekly, for a 4 month period, for the groups under MTS and BNB. The totals and grand totals can be achieved in the report tool. I want to create variables for the new cases (mts_newcases_sales, mts_newcases_salesd, bnb_newcases_salesd etc)

Ex. MTS sales : (status = 'Calculated' OR status = 'REJECTED') and errorsource != 'marketing' and accountns is null and area != 'BNB'(everything else is MTS)

MTS salesd ; Credit >= '1001' and (status = 'REJECTEDV' or status = 'ACCEPTEDS') and errorsource != 'marketing' and accountnr is null

BNB creditr: Credit < 101 and (status = 'SUBMITTED' OR status = 'REJECTEDS' OR status = 'REJECTEDA' OR STATUS = 'ACCEPTEDC')

12-Jan

19-Jan

26-Jan

2-Feb

9-Feb

16-Feb

MTS

New Cases Received

85

84

79

98

79

95

Sales

30

32

27

40

42

38

SalesD

47

34

37

23

23

37

CreditR

44

29

26

35

55

54

CreditB

6

12

9

5

7

13

CreditS

-

-

-

-

3

-

CreditP

10

11

11

24

17

7

MTS Subtotal

140

125

110

144

151

150

BNB

New Cases Received

12

13

14

14

6

11

Sales

-

-

-

-

-

-

SalesD

-

-

-

-

-

-

CreditR

12

11

12

10

5

9

CreditB

8

13

9

17

16

6

CreditS

-

-

2

-

-

-

CreditP

1

1

1

1

4

3

BNB Subtotal

21

25

24

28

26

19

Total

New Cases Received

97

97

93

112

85

106

Sales

30

32

27

40

42

38

SalesD

47

34

37

23

23

37

CreditR

56

40

38

45

60

63

CreditB

14

25

18

22

23

19

CreditS

-

-

2

-

3

-

CreditP

11

12

12

25

21

10

Grand Total

161

150

134

172

177

169

This is just a very brief bit of code

SELECT MTS_new_cases_sales, mts_new_cases_salesd ��.

FROM vwCreditN
WHERE mts_sales_new_cases = ( )...
and (status = 'Calculated' OR status = 'REJECTED')...

Can you please show me how to accomplish this?

Thank you in advance for your effort,

Rhonda

View 2 Replies View Related