Excluding Records

Apr 24, 2008

Hello,

I'm trying to figure out the best way to approach this seemingly simple task in Reporting Services 2005, but have come up with nothing but frustration. Any help would be appreciated!

Here's the basic idea:

SELECT Server.Database1.Employee, Server.Database1.HomeDept, Server.Database2.WorkedDept
FROM Server.Database1, Server.Database2

I need to exclude certain combinations of HomeDept/WorkedDept (about 11)...such as 1015/2223, 1015/2226, 2002/4422, 2002/8742, 2002/2342, etc. I want any other combinations of HomeDept 1015 & 2002 to be selected (which would be hundreds).

I've tried:

WHERE (Server.Database1.HomeDept <> '1015' AND Server.Database2.WorkedDept NOT IN ('2223','2226')) AND Server.Database1.HomeDept <> '2002' AND Server.Database2.WorkedDept NOT IN ('4422','8742','2342'))

(and all combinations of NOT IN, <>, and parenthesis placement...I even tried to do each exception separately)

What happens is that all instances of HomeDept 1015 and 2002 and all instances of WorkedDept 2223, 2226, 4422, 8742, and 2342 are excluded.

The SQL Query Designer will remove parenthesis so that the HomeDept exclusion is processed separately from the WorkedDept.

I've tried to do the SQL in the Generic Query Designer, so the SQL Query Designer doesn't reformat it, and it still returns the same results.

This behavior only seems to happen when using <> for both, NOT IN for both, or a combo of NOT and <>. If I used = for the HomeDept, the exclusion of the WorkedDept worked fine.

Any suggestions?

View 1 Replies


ADVERTISEMENT

Excluding Duplicate Records

Apr 18, 2008

and generating a report from an SQL table, and need to know how to exclude records that are "duplicates". Not duplicates in a sense that every field is identical, but duplicates in a sense where everything except the unique identifier is identical. Is there a quick and easy way to do this?

View 5 Replies View Related

XL Source Editor Excluding Numeric Records

Dec 11, 2007

Hi All,

I have a XL source file which contains the 1st column value is some of Numeric and alpha numeric (ex. 1,1A,1B,2,2A,2B),
i have fired the select statement (select * from [sch 1 a$A9155] where f1 is not null ) in XL source editor, its not showing the numeric records means 1 and 2 were excluding from the above select statement i think it was due to combining numeric and alpha numeric.

I need both records should select , please anyone can help me to sort out the issue.

Thanks in advance

Thanks,
syed

View 6 Replies View Related

SQL Server 2012 :: Excluding Records Whose Values From 2 Different Fields Match

Aug 31, 2015

Using MSSQL 2012

I have a simple select query and I need to eliminate records whose values from 2 different fields match. I thought I had this working, but if one of those fields in my data IS NULL it filters out those records. If I comment out my last line then my number record shows, if I include that statement that record drops. The only thing I see in my data is the Name and PName are both NULL in the data for that particular number. Just need to filter out any records where it finds those 3 Names that also have "Default" as the PName, then include everything else even if Name or Pname is NULL.

Below is my where clause.

WHERE [DETERMINATION] <> 'Denied'
AND [Number] ='A150731000039'

---- Removes incorrect records where these names match----
AND ([Name] NOT IN ('GLASSMAN','NANCY','LUDEMANN') AND [PName] = 'DEFAULT')

View 4 Replies View Related

Excluding Value From Insert

Jun 17, 2014

The script basically maps ALL products to customer levels. The Delete statement at the start is needed to make sure that any deleted products or temporary mapped products are removed once a week. However I want to map some products to CustomerID's 9,10 and 14 only. But when I run this statement it maps all products to all customer levels. What I want is thisAll products mapped to customer levels 9,10 and 14Then every other product mapped to customer levels 0,5,7, and 8 but not those extra ones in 9,10 and 14.

DELETE FROM ProductCustomerLevel
WHERE CustomerLevelID IN (0, 5, 7, 8, 9, 10, 14)
INSERT ProductCustomerLevel
(
ProductID,
CustomerLevelID

[code]....

View 5 Replies View Related

Excluding Duplicates

Nov 20, 2007

I have to exclude some records from the data flow.

If there is more than one record with equal col1 and col2, I need to exclude both records.

I can't do this operation in the OLE DB Source because I have to run some transformations before I run the col1-col2-equality check.

ANy ideas? Is aggregation transformation way to go? How do I tell to the aggr. transf to do a having count() > 1?

View 3 Replies View Related

Excluding Empty Parameters

Apr 17, 2005

I would like to exclude any parameter that is empty from the SELECT
command?  How do I do this?  This is part of a stored
procedure.

SELECT PersonID FROM Persons WHERE
(FirstName = @firstname) AND
(LastName = @lastname) AND
(SSN = @ssn) AND
(AddressID = @addressid) AND
(DOB = @dob) AND
(Middle = @middle)

THanks

View 2 Replies View Related

# Of Weekdays Between Dates Excluding Sat/Sun

May 14, 2002

Does anyone know how to write code to tell the amount of days between 2 dates excluding Saturday and Sunday.
Datediff will tell me the total amount of days and I guess I need some logic to look at the startdate and manipulate the total after the fact.

Any thoughts or solutions out there?

View 4 Replies View Related

Excluding Data For Certain Criteria Only

Feb 6, 2012

I'm having a problem writing a SQL query that excludes certain data. This is for a pay stub application to display current and previous paycheck stubs. To calculate certain data such as YTD figures and time off, we SUM on other tables. However, to display correctly, I can't SUM bonus checks for the current payperiod ONLY - but for previous pay periods, I must SUM bonus checks.

Here's an example of my data:

No code has to be inserted here.
No code has to be inserted here.

No code has to be inserted here.
No code has to be inserted here.

Right now my SQL is this:

Code:
SELECT PR04PTF.PayCheckNo, SUM(PR11ERF_History.PayCheckAmt) AS [TotalSum]
FROM PR04PTF

INNER JOIN PR11ERF_History ON
PR11ERF_History.EmployeeID = PR04PTF.EmployeeID
AND PR11ERF_History.PayPeriodEnd <= PR04PTF.PayPeriodEnd

WHERE PR04PTF.EmployeeID=441

View 3 Replies View Related

Order By Excluding First X Characters

Jul 6, 2012

I have a column where records are either 6 or 7 characters long.

for example:

111.111

OR

111.01

I am trying to order by the characters after the period. In other words I would like to exclude the first 4 characters (the 111.)

View 1 Replies View Related

Duplicate Row Excluding Primary Key

Jul 6, 2007

I am trying to duplicate a row in a table and this is the solution I came up with:

CREATE PROC duplicate_row(@my_primary_key)
AS

DECLARE @sql_stmt varchar(MAX)
SET @sql_stmt = 'INSERT INTO My_Table('

SELECT@sql_stmt = @sql_stmt + COLUMN_NAME + ','
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_NAME = 'My_Table'
ANDCOLUMN_NAME NOT IN ('my_primary_key','title')

SET @sql_stmt = @sql_stmt + 'title) SELECT '

SELECT@sql_stmt = @sql_stmt + COLUMN_NAME + ','
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_NAME = 'My_Table'
ANDCOLUMN_NAME NOT IN ('my_primary_key','title')

SET @sql_stmt = @sql_stmt +' title+''_copy'' AS title FROM My_Table WHERE my_primary_key = '+LTRIM(STR(@my_primary_key))

EXEC(@sql_stmt)

However this stored proc is horrifically slow. I was wondering if anyone had any suggestions on a better way to accomplish this.
-thanks

View 5 Replies View Related

Group By By Excluding Prefix

Apr 12, 2006

Suppose there is a table containing these recodes.country-------CON_CHNCON_JAPJAPCON_CHNWhen I use the following sql:select country, count(*) as num from table group by countrythe normal result will be:country num---------------CON_CHN2CON_JAP 1JAP1However, my desired result is as follows:country num-----------------CON_CHN2CON_JAP 2How can I re-write my SQL? Or any other methods to do that?

View 2 Replies View Related

Excluding Repeated Values In A Sum

May 21, 2007

The scenario is as follows:

I have rows coming from the db including:
Contract Number, Contract Name, owner and Actions associated with each contract.

SQL statement brings back:
Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
1234 123453 $50 Bob y
534232 5464634211 $30 Harry z

The problem is that each contract can have multiple actions associated with it...
There ideal output would be:

Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
Bob y
534232 5464634211 $30 Harry z

Total: $80

Basically I need to hide and not include repeated items based on a contract number... one idea I had was creating a group based on contract number and then display info in the header and then only owner and actions in the detail section.. The problem is Totals... how can I can it to avoid count the duplicated values..

Any help would be greatly appreciated.

Thanks,
Neil

View 4 Replies View Related

Excluding Duplicates After Multicast

Sep 25, 2007

I have to do various controls on a dataset - I created a multicast. After performing controls (one control per copy), I merge my (7) multicasted datasets using a Union All transformation. The problem I'm having are the duplicate rows created by merging the multicast copies.

How do I get rid of the duplicates? Is the Sort Transformation the solution by setting the option Remove rows with duplicate sort values to True? I have a unique key by which I'm able to discard the duplicates correctly. Are there any other ways (at a Union All level)? Is there sth like Union and Union All like in SQL?

I'm working on my 1st integration serv. project and it seems that more I work more questions I have. Shoudn't be the opposite? Thank you for the help.

View 3 Replies View Related

Date Comparison Excluding Time

Jan 6, 2005

I have a stored procedure that is suppose to get all records that have an expiration date of today. My where clause is as follows:


Round2ExpDate <= getdate()


This is not working because of the time factore. What is the best way to truncate the time for each date? All recommendations are greatly appreciated.

View 1 Replies View Related

Simple Excluding Of Character Query

Feb 25, 2005

i have a field with 2 characters(many of them ) and spaced inbetween them
which are codes for something


ie XA OX YY BY CY DY XC XD OE In any or all combination

i want to make a count of rows which contains BOTH XA
BUT DOES NOT CONTAIN any one or all of XC,XD & OE

SO I write the query
select count(colname)
from tablename
where colname LIKE ('%XA%')
AND colname NOT LIKE ('%XC%')
OR colname NOT LIKE ('%XD%')
OR colname NOT LIKE ('%OE%')
')

iS this correct
why then it does not exclude the XC,XD,OE

is there a better way ?

View 14 Replies View Related

Best Way To Handle Excluding Holidays From Report

May 18, 2015

I inherited a report that counts patient visits per month. Holidays are hard coded into the query. What is the best way to handle holidays without hardcoding?

View 4 Replies View Related

Data From Previous Day Excluding Weekends

Jan 23, 2014

One of our departments once to automate a query that they have to pull data from the previous day. We are going to set this up as a job. How can we do this without using the Saturday and Sunday dates? So what we want to do is Pull the data from Friday on Monday. Is this possible? This is what they have. I know this pull the data from the day before.

select distinct

clm_id1, clm_rcvd, clm_6a, clm_6b, clm_dout, clm_cc1, clm_clir, clm_65a, clm_5, clm_1a, clm_1a1, clm_1a2, clm_1b, clm_1d, clm_1e, clm_1f, clm_tchg, clm_nego, clm_sppo, clm_att1, clm_att2, clm_att3, clm_att4, clm_att5, clm_chast, clme_fild

from

impact.dbo.clm
left join impact.dbo.clme on clm_id1 = clme_id
where
clm_dout = getdate()-1

View 5 Replies View Related

Excluding Time Period From Results

Jul 12, 2006

Hello, I would like to exclude the time period after 5.30pm and before 8.30am in my results. The time is in a 13 digit timestamp format which is the same as a standard unix timestamp with 3 digits which are microseconds.

I used:

dataadd(ss, TTIME/1000, '1970-01-01')AS time

to create a column with a readable time in it.

Here is a screenshot: http://www.abtecnet.com/timescreenshot.jpg

Can anyone help me with this. Thanks very much.
Andrew

View 5 Replies View Related

Excluding Based On Specific Criteria

Jan 2, 2007

I am trying to accomplish the following.

There are two tables. the second one is a table that contains fields based on which the exclusion logic has to be written. It has 5 fields and there may be data in all of them or only in some of them.

The first and second table have one field in common - the Account number.(ACCT_NUM)


the exclusion is based in steps..

1) if it has all the fields in the second table for that account number, then compare all of them..
2) if only 4 of them are present,compare and check for null for the rest
3) if only 3 of them are present,compare and check for null for the rest
4) if only 2 of them are present,compare and check for null for the rest.

how do i do this... pls suggest

Thanks

View 1 Replies View Related

Excluding Fields In SELECT Statement

Oct 23, 2005

Hi,Is there a way to exclude fields in a query other than just includingthe ones you want. If there are 20 fields and you want to see all but3, it would be a lot easier to exclude the 3.Thanks

View 8 Replies View Related

Excluding Weekends When Finding The (dd/hh/mm/ss) Between Two Dates

Mar 27, 2008

I have two datetime fields that I would like to find out how much time has passed between them. First field is "start date" and the second is "end date" the dates in both fields are in this format (03/27/2008 4:00PM). The problem I am having is I need to exclude the time passed from Friday, 6:00PM to Monday, 7:00AM if the dates happen to go over a weekend.



Any help would be greatly appreciated.



Thanks

JP

View 6 Replies View Related

Excluding Rows On A Table While Importing

Jul 11, 2007

I'm using DTSWizard to import a table from my main database to Temp.



This is the SQL statement...



CREATE TABLE [tempdb].[dbo].[xlaANLsubscribers] (
[subscriberid] int NOT NULL,
[pwd] varchar(255),
[name] varchar(255),
[deliveryformat] int,
[email] varchar(255),
[gender] varchar(255),
[phone] varchar(255),
[country] varchar(255),
[city] varchar(255),
[state] varchar(255),
[zip] varchar(255),
[address] varchar(1000),
[dateregistered] varchar(50),
[bounces] int
)



What I'd like to do for example, is exclude the first 5,000 rows, and import the rest.

Should I be using something other than DTSWizard, and it there something that can be added to the statement above telling it to start at a specified row?



This is probably fairly simple, but I'm new at this and I'd sure appreciate the help.



Thanks,

Bill

View 16 Replies View Related

DateAdd Function - Excluding Weekends

Jan 4, 2008

Im using the DateAdd Function to establish a future date base on the required time for a series of events to transpire. I'd like to exclude weekends, does anyone know a way to do this?

Thanks in advance

Alex

View 1 Replies View Related

Aggregate Excluding Filtered Rows

May 14, 2008

I am trying to show aggregate information in a grouping and report footer. The details section has a filter applied successfully. For example, if there are three records and one should be filtered out, then only two display. However, the count function returns 3 instead of the desired 2. I have tried to set the scope parameter to body, the table name and every group name on the report. Either this has no effect or returns an error message stating that the appropriate scope isn't applied.

Does anyone know how to perform aggregate functions and exclude the filtered rows?

Thanks,
Dennis

View 3 Replies View Related

Excluding A Package From The Build Process

Jan 17, 2007

There is some way to exclude a package from the build process of a SSIS project ?

Cosimo

View 1 Replies View Related

Excluding Character From A Numeric String X34820

Sep 13, 2001

hi I have a table which contain an id field as a char(20)
The content of this field is combination of string and numbers as follow

id flag
--------- -----
38383
88585
18834
x4820
z4892
t9494

I need to update the flag field where first character in the id field is not numeric. HOw can I do that.
thanks for your hlep

Thanks

AL

View 2 Replies View Related

Calculate Date Difference Excluding Dates

Aug 27, 2012

I have already seen stored procedures that can calculate a difference in dates, excluding the weekends. Any extension of such a SQL query to exclude not only weekends, but other dates as well. We have a table of "holidays" (not necessarily standard holidays), and I am wondering if there is a way to exclude them from the calculation.

View 7 Replies View Related

T-SQL (SS2K8) :: Consecutive Streak Excluding Weekends

Aug 8, 2014

I'm trying to write an algorithm that returns the most recent and longest consecutive streak of positive or negative price changes in a given stock. The streak can extend over null weekends, but not over null weekdays (presumably trading days).

For example, lets say Google had end of day positive returns on (any given) Tuesday, Monday, and previous Friday, but then Thursday, it had negative returns. That would be a 3 day streak between Friday and Tuesday. Also, if a date has a null value on a date that is NOT a weekend, the streak ends.

In the following code sample, you can get a simplified idea of what the raw data will look like and what the output should look like.

set nocount on
set datefirst 7
go
if object_id('tempdb.dbo.#raw') is not null drop table #raw
create table #raw

[Code] .....

I should also mention that this has to be done over about half a million symbols so something RBAR is especially unappealing.

View 5 Replies View Related

How To Get Details Of Employee Excluding Junc Data

Mar 11, 2015

I have table like :

emp

eno ename deptnno sal
1 a 10 50000
2 b 20 100000
3 c 20 150000
4 d 30 200000
5 e 30 2500000

Here how can I get all employee details excluding junc datas?

View 1 Replies View Related

Analysis :: MDX - Custom SET Excluding Unknown Member

Nov 21, 2008

Uknown Member is set to "Uknown" in the cube . This cannot be changed because it needs to be visible in other applications. However, in my particular MDX I want to create a custom set that excludes this member.

WITHSET setNoUnknowns as EXCLUDE( [Dim].[Hierarchy].CHILDREN, { [Dim].[Hierarchy].UNKNOWNMEMBER } )SELECT{[Measure].[MeasureName]} on COLUMNS,setNoUnknowns on ROWSFROM [Cube]

With the above MDX, I still get a return for member "Unknown". I've confirmed that there isn't an explicit member name of "Unknown".Excluding [Dim].[Hierarchy].[Unknown] does not work either.

View 5 Replies View Related

Reporting Services :: Excluding NaN From SSRS Expression

Apr 20, 2015

Below is my query, it output's a percentage based on some calculations that i've had to make to fit in with requirements.The trouble is that it is also coming with NaN values which i need set to 0%,modify the below query to accomplish this?

=Sum(IIF(IIF(ISNOTHING(Fields!TotalHours.Value) = true,25,Fields!TotalHours.Value) <= 24,1,0)) / Sum(Fields!SubTotal.Value)

View 7 Replies View Related

Selecting Based On A Date Excluding The Time

May 7, 2008

Hi,

I was wondering how you perform a select statement based on a specific date that will show all the records no matter which times belong to the specific date.

I have been having trouble with this one when using a single date, I think this is because of the time property as no records are displayed.

Thanks for any help.

View 1 Replies View Related







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