Exclude Row If Calculated Column Is Zero?
Oct 3, 2013
I am attempting to create an SQL statement that will query a file and give me amount totals by company number/customer number. The totals have to be combined into 4 groups (1/2/3/4) for each amount total in company number/customer number combination. In effect it will look something like this:
COMPANY | CUSTOMER | SORT | AMOUNT
==================================
00001 | 11111 | 1 | $55
00001 | 11111 | 2 | $12
00001 | 11111 | 3 | $19
00001 | 11111 | 4 | $ 0
00001 | 22222 | 1 | $99
00001 | 22222 | 2 | $53
...and so on.
I HAVE THIS PART WORKING ALREADY. The problem is that I am trying to exclude the rows that have 0 (zero) in the amount column from showing up in the output. The amount is a calculated field of all the invoice for that company number/customer number combination for that sort (eg: Company 00001/Customer 11111/Sort 1 has $55 associated to it). I cannot use the calculated field in my where clause.
I will include a simplified version of my select statement so you can see how I got as far as I have and where to go so I pretty much say "WHERE SUM(SubTBL.Amount) <> 0".
----SELECT STATEMENT-----
SELECT
MainTBL.Cust#,
SUM(SubTBL.Amount) As TotAmt,
CASE
WHEN (days (currdate) - days (MainTBL.DateFLD)) <= 30 THEN '1'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 31 AND 60 THEN '2'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 61 AND 90 THEN '3'
WHEN (days (currdate) - days (MainTBL.DateFLD))> 90 THEN '4'
[code]....
View 1 Replies
ADVERTISEMENT
Nov 12, 2012
I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)
ID Mail_code
111111 XNT
111111 N11
111111 XNC
222222 XNC
222222 XNL
333333 XNC
So, if there is any ID that has a value of XNC, I want to exclude the ID all together from my output regardless of the other values.
View 3 Replies
View Related
May 26, 2014
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
View 1 Replies
View Related
Dec 26, 2007
I am creating a query which will show patients that are enrolled in more than one program, but I need to exclude those patients that enrolled more than once in the same program. Here's part of the code:
SELECT member_id, service_id
FROM pat_prg_info ppi
This query produces results like the following:
member_id service_id
1001 1
1001 2
1003 9
1003 9
1004 2
1004 9
I would like to exclude 1003, since this member_id is enrolled twice in service_id 9. How can I accomplish this?
View 1 Replies
View Related
Oct 12, 2015
I am trying to create a whole number DAX calculated column that is derived from a date column. Basically it gets the date from the source data column and outputs it as an integer in the YYYYMMDD format.So 01/OCT/2015 would become --> 20151001...I've been fidgeting with DAX but my problem is that I keep missing the leading zeroes for months and days. So 01/March/2015 becomes 201531 which is not what I want (I need 20150301 in this case).
View 2 Replies
View Related
Mar 3, 2008
Hi. I'm creating a new publication for replication. There are a few columns on our main database I do not want published as they contain some confidential information. When I go to create a new publication, I go through the wizard and I select the option to filter rows vertically. I uncheck the columns I don't want published and the wizard completes without any errors.
When I go to start the Snapshot agent to create the database on new server, it gets error saying it cannot find certain columns--the columns I do not want published to begin with.
Am i doing something wrong? I appreciate any help you may provide!
View 7 Replies
View Related
Jun 4, 2015
I want to populate data from Production to UAT(except Identity column).For that I created query,its generating script what i have required.But in last column getting with comma.I should eliminate the last comma.
script to my requirement:
declare @TABLE_SCHEMA varchar(10)
set @TABLE_SCHEMA='dbo'
declare @TABLE_NAME varchar(100)
set @TABLE_NAME='Demo_Table'
SELECT ORDINAL_POSITION AS COLUMN_POSTION ,TABLE_SCHEMA,TABLE_NAME
[Code] ....
View 12 Replies
View Related
Jul 16, 2014
This is a followup to a previous question to a previous but in reverse of Find rows where value in column not found in another row
Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...
I would like to exclude any two rows where Type is equal to 'TypeA' and Key2 is Null that have a corresponding row in the table where Type is equal to 'TypeB' and Key2 is equal to Key1 from another row.
So, given the data
**KEY1** **Key2** **Type**
1 NULL TypeA
2 5 TypeA
3 1 TypeB
4 NULL TypeA
5 NULL TypeB
6 26 TypeC
7 NULL TypeD
8 NULL TypeD
I would like to return all the rows except where Key=1 and Key=3 because those rows together meet the criteria of Type='TypeA'/Key2=NULL and does have a corresponding row with Type='TypeB'/Key1=Key2.
View 2 Replies
View Related
Sep 11, 2014
I want to fetch max of Field2 if duplicate records in Field1 and rest of the values of field1 , below is the sample format.
Field1 Field2 Field3 Field4
32 375 abc-xyz A
32 379 xyz-efg A
55 405 abc-xyz B
55 407 xyz-efg B
132 908 abc-xyz C
132 999 xyz-efg C
152 800 abc-xyz D
152 850 xyz-efg D
155 900 abc-xyz E
156 925 abc-xyz F
157 935 abc-xyz G
View 2 Replies
View Related
Jan 1, 2008
I have a location table with columns for [state tax rate], [local tax rate] and a bit column for each to indicate if it is active. From this I need to calculate a total tax percentage; i.e.(state tax rate * bit) +(local tax rate * bit). If I want to stay at the database with this I can identify three alternatives (I'm sure there are more):
1. Calculated column
2. Table Variable (or Temp. Table)
3. View
I'm interested in feedback as to which of these methods is more appropriate or if there is a better way I haven't identified.
Thanks
View 3 Replies
View Related
Sep 12, 2007
Hello Everyone,
I'm a newbie to SSIS. While experimenting with it I've encountered an issue which I'm hoping someone of you could help me out with. I have need to make a specific transformation which as output would have to produce rows with a new calculated column that replaces single column from input. New column has different data type than input column it is replacing. I've used Derived Column Transformation (DER) to do the first part of the work - appending new column and calculating its value (based solely on value from single original column that has to be replaced). Question is how should I do second part, task of removing no longer needed column from the pipeline? I've tried in DER instead of Derived Column being added as new column, selecting Replace 'column' but as it seems it is meant to replace only column data and not column data type (what I've expected). I've also tried using Copy Column Transformation (CPYC) but as it turns out CPYC transformation just (logically) duplicates data in the pipeline with optional different allias.
View 1 Replies
View Related
Jan 29, 2007
Ok I have three columns in my database that deal with ratings of individual ads. One is called totalrating, one is totalvotes, and one is averagerating. TotalRating gets incremented with the rating and totalvotes is incremented by one when someone votes. Then averagerating is a calculated column which divides the totalrating by the totalvotes. The problem is unless I manually set totalrating and totalvotes to 0, the stored procedure does not work. They both remain null. I tried to set the default value for each column to 0, which visual studio changed to ((0)). Maybe I am doing this wrong. If someone could help me I would really appreciate it. Thanks so much. Dave Roda
View 4 Replies
View Related
Sep 19, 2005
Hi,I'm struggling to get a calculated column to work in sql, the fields to be calculated are:[AdRevenue_a] money[Admissions_a] int[DoorPrice_a] smallmoney[DoorSplit_a] moneyAnd the calculation I require is:(AdRevenue_a / ( (Admissions_a * DoorPrice_a) - DoorSplit_a )) * 100This is what I think it should be but it doesn't work...convert(decimal(6,2), ((AdRevenue_a / ((Admissions_a * DoorPrice_a) - DoorSplit_a))*100) ))Any suggestions??
View 6 Replies
View Related
Apr 21, 2008
*first issue
how to add a calculated column in a view such that this calculated column will be calculated from the oraginal columns
create view vw1
as
select tab.col1,tab.col2 from
from tab
and i want to add a column that contains the result of a comparison between col1 and col2 (col1<col2) such that the values of the column will be true,false
thanx
View 2 Replies
View Related
Mar 29, 2007
I have calculated column which is referenced by function and I need to alter the function. How can I disable the calculated column so I am able to modify the function?
..................................................................
I have orders table and I need to disable the calculated column OrderDate so I may able to modify the function dbo.udfTicksToDateTime.
CREATE TABLE Orders (
OrderDateAsTicks BIGINT,
OrderDate AS dbo.udfTicksToDateTime(OrderDateAsTicks)
)
Thanks
Sanjeev
Cleveland
View 2 Replies
View Related
Mar 28, 2008
Hi,
I'm writing a query that calculates values for each id, then I need to sum up all the values for each id and put them in another field, here is what I wrote but SQL cannot understand the column that I calculeted
select id,term_cd,
case when RIGHT(term_cd, 1) IN ('6') and substring(term_cd, 2,4) <= 2004) then '1'
when substring(term_cd, 2,4) <= 2004 and RIGHT(term_cd, 1) = 5 then '0.5'
else '1' end as term_count,
SUM(term_count) AS ttd_enrolled
group by id,term_cd
SQL gives me an error "invalid column name tern_count" in the line where I calculate the sum.
what's wrong with the query? or should I calculate the term_count in an inner query?
Appretiate your help
View 8 Replies
View Related
Jul 6, 2006
Table: Names
Columns: Name_RID char(10)
Name_Type smallint
Name_Last char(50)
Name_First char(25)
Name_MI char
I have search for and see how to put the columns for the last, first mi together (Name_Last + ', ' + Name_First + ' ' + Name_MI) as Name
But how can I test the value of the Name_Type field to determine how the Name column looks
if Name_Type = 1 then
Name = (Name_Last + ', ' + Name_First + ' ' + Name_MI)
else
Name = Name_Last
The Name_Type represents Individual versus an Entity
0,ABC Pipeline,,
1,Williams, John,A
View 3 Replies
View Related
Mar 12, 2015
I need to add a calculated column item in the same column. Please see SQL Codes for both existing data and desired outcome.
Product O is added according to:
for 201501 Product O= sum of en_count for product Y,W,N when yrmnth=201501
for 201502 Product O= sum of sum of en_count for product Y,W,N when yrmnth=20150
SQL:
--Existing Data
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Table1') IS NOT NULL DROP TABLE #Table1
--===== Create the test table with
CREATE TABLE #Table1
(
product char(100),
yrmnth varchar(6),
en_count int,
[Code] ....
--Desired Outcome
IF OBJECT_ID('TempDB..#Table2') IS NOT NULL DROP TABLE #Table2
--===== Create the test table with
CREATE TABLE #Table2
(
product char(100),
yrmnth varchar(6),
[Code] ....
View 9 Replies
View Related
Dec 10, 2013
I'm new to SQL Server and would like to add a calculated column to this query from the report writer in our ERP system based on the NextFreq case statement result.
Basically, I want to create a column called service with result as follows:
If IV.meter > NextFreq then the result should be 'OVERDUE'
If (NextFreq - IV.meter) <50 then the result should be 'DUE SOON'
Otherwise the result should be 'NOT DUE'
This is the code from the current report writer query:
Select IV.item, IV.meter, isnull(wt.name,0)as name, case when whh.meterstop is null then 0 end meterstop, whh.rejected, Case when cast(meterstop as int) > 0 then cast(meterstop as int) when meterstop is null then isnull(IV.meter,0) else isnull(IV.meter,0) end EndMeter, ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) as LastFreq,
case when whh.rejected = 1 then ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) when ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) = 0 then 100 when ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) = 100
[Code] ....
View 4 Replies
View Related
Aug 21, 2015
Where we have increase the size of the calculated column in cube..whether in attribute or some where else...
View 4 Replies
View Related
Oct 30, 2015
I have written a simple DAX calculated column formula using SEARCH and got an error: It tells the text "PWP" cannot be found but as you can see the screen shows this text exsist within "Promotional Claim"The same happens when I try FIND function.
View 2 Replies
View Related
Oct 13, 2015
My simplistic data model looks like this:
Table.1
Spread Product Sales Channel ..
2 x CB ..
2 y CB …
4 x GFR ..
4 y GFR ..
I need a column with Spread Mapping, which should look like this:
->There are more columns in data model.
-> Spread for GFR is wrong so it must be mapped to be able to compare with CB
I guess there should be calculated column formula to calculate Spread Mapping. How to do it?
View 6 Replies
View Related
Sep 25, 2007
Hi All,
I need to show the Cumulative calculated value only in Total by year/Group. I could not use Visibility expression using
InScope, as it creates *Blank column. Please go thru details below.
Year
Month01 02 03 Total
Salary Salary Salary Salary Cumulative (Calc)
Employee01 20 5 25 25
Employee02 10 10 20 45
.....
Total
How can i achieve this?. Any suggestion on this would be appreciated.
Thanks,
View 1 Replies
View Related
Jul 14, 2015
How to include a time difference column using 2 other date columns during creation of a table ?
The requirement is to create a table and include the following columns:
1.Downtime start date & time
2.Downtime end date & time
3.Downtime Duration in hh:mm (calculated date difference between column 1 and 2)
View 3 Replies
View Related
Mar 21, 2007
I am trying to add a calculated field / column in Report Builder when working with a Report Model built from anAnalysis Services Cube. I can create the calculated Field/Columns, but I get an error whenever I try to use it in a report.
Is there a way to create a report builder calculated column on report models built from a SSAS cube? Is this supported?
Thanks,
View 8 Replies
View Related
Jun 23, 2015
In a calculated column I am trying to get a scalar text value from a lookup to another table. This works quite well when getting numerical values with the following formula:
=MinX(Filter(LookUpTable;LookupTable[from]<=MySourceTable[Day] && LookupTable[to]>=MySourceTable[Day]);LookUpTable[numericalColumn])
But as soon as I substitute the numerical column by a string column, #error results.
I also want to mention that the above query yields only one row as a result. It should be simple to return the value of one of the columns but after searching for quite some time, I could not find any function for that.
View 5 Replies
View Related
Jun 11, 2015
I want a report that displays selected year quantity sales and previous year sales quantity and their quantity difference.
i also want to display a chart like I added year to the series group but i don't know how to add difference to the series group.
View 5 Replies
View Related
Jul 13, 2015
I am looking to add a column to one of my tables that displays a running rank of how many times a customer has ordered in a given period.
I currently have such a column however this column ranks against ALL of the orders that a customer has placed and ignores filters, whereas I need one that ranks based on the filters that are active at any given time.
The current formula is:
CustOrderCountPersistant=RANKX(FILTER('Q1 Data Set',[k1_customer_id]=EARLIER([k1_customer_id])),[order_id],[order_id],1,DENSE)
For example, if I am looking at a full years worth of data and a customer has placed 10 orders in that period this formula will add a 1 in the column for first order, a 2 for the second and so forth all the way to 10, the last order.
However it will give me exactly the same results if I filter the data to just one month of that year where they may have order only 2 orders.
In this scenario I want to have another column with a table that is filter sensitive and would show 1 for the first order and 2 for the second order.
Now, I do understand that the issue here is probably the FILTER() I have on as, if I understand correctly, that means all other filters are ignored. My attempts at reworking the formula to remove this have been unsuccessful (such as using a CALCUALTE and trying to use filter properties within that forumula).
To explain the context - I want to create a measure that counts how many customers have placed x amount orders in y number of days e.g. how many customers have placed 2 orders in 30 days.
View 3 Replies
View Related
Nov 18, 2014
I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?
this query returns the values i want in the table.
select a.quantity * b.price
from tblCustomerPurchases as a
join tblProduct as b
on a.ID=b.ID
View 17 Replies
View Related
Apr 20, 2014
I have 4 tables involved here. The priority table is TABLE1:
NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
[code]...
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
View 0 Replies
View Related
Feb 16, 2004
Table 1 is just a reference table. Users add values to table 2.
I need to select/exclude records from table1 where the id2 in table2 = 1.
How get the following results:
table 1
----------
id
----------
a
b
c
d
e
f
g
table 2
----------
id / id2
----------
b / 1
c / 1
d / 1
f / 1
c / 2
d / 2
a / 4
b / 4
need results
----------
id
----------
a
e
g
any suggestions?
thanks
View 1 Replies
View Related
Jul 26, 2005
i have two tables: "Person" and "Year". "Person" can have many "Year"
(one to many relation). i want a query which returns all the records
from "Person" where "Year" is 2005 but exclude if there is any "Year"
with 2004. how can i write that query? any help will be appreciated.
i did try
<code>
SELECT * FROM Person JOIN Year ON Person.Id = Year.PersonID WHERE Year.Year = 2005 AND Year.Year <> 2004
</code>
but it doesn't seem to work. i want this query to return records from
Person where there is no any year with 2004 but only 2005. If a person
has both 2004 and 2005 exclude that person.
View 1 Replies
View Related
Dec 15, 2005
I have to built a query to get the % for all the Region (Americas, Asia and Europe) from a cube.
But in these regions some countries are excluded and treated seperate.
Like Asia does not include India and Japan.
How do I get the ASIA query using an EXCLUDE condition.
Please help.
View 1 Replies
View Related