Code To Exclude Records In A Crosstab

Jun 20, 2014

Using MS ACCESS I have:

TRANSFORM Avg(SAP_CALD.[CASE_DAYS]) AS AvgOfCASE_DAYS
SELECT SAP_CALD.[OPN_YEAR], SAP_CALD.[CLD_MONTH], Avg(SAP_CALD.[CASE_DAYS]) AS [Total Of CASE_DAYS]
FROM SAP_CALD
GROUP BY SAP_CALD.[OPN_YEAR], SAP_CALD.[CLD_MONTH]
PIVOT SAP_CALD.[Model];

which works fine. I want to look at the query SAP_CALD and disclude any record in the field 'department' that has a record of any {DPSG, System, Dealer, DealerIT, Inbound}..I can do this by piggybacking a another query that removes these but wondered if it would be easier to exclude them in the crosstab code.

View 1 Replies


ADVERTISEMENT

Exclude Records From A Table Where ID Column Is Same But Mail Code Column Is Multi-valued

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

Exclude All ID Which Doesn't Purchase Any Code

Aug 30, 2013

how can i exclude all id which doesn't purchase any code.

codeA,codeB,codeC,codeD,.....
ID 1 purchase codeA, this will not be pulled out.
ID 2 didnt purchase any code, this will be pulled out.

I tried select id,code from tableA where code not in ('codeA','codeB','codeC','codeD',...)

but it still pull out ID 1.

View 1 Replies View Related

Exclude Part Of The Code In A Transaction

Oct 25, 2007

Hello,

I have a TSQL (SQL SERVER 2005) code inside a Transaction,
I would like to exclude a piece of that code from the transaction (I do need to recovere it in the trasaction log).

For instance

BEGIN TRAN

SQL Statement 1
SQL Statement 2 -- out of the transaction
SQL Statement 3

COMMIT TRAN

I would like to exlude SQL Statement 2 (SQL statement1 and 3 must be inside the same transaction)

Is it possible? How to do it?

Thank

View 8 Replies View Related

Exclude Piece Of Code From Transaction

Mar 20, 2008



Hello,

I have created a procedure with several SQL statement, all are included in a transaction.
Some of the code are "insert" in tables that are in remote server.
for example:


procedure sp1 is on SERVER B.
From SERVERA by linked server we have:

exec serverB.db_B.dbo.sp1 (from serverA the procedure sp1 is called)


CREATE procedures sp1
AS
BEGIN TRANSACTION
insert into T1 (col1) values (1)
insert into serverA.db_A.dbo.T2 (col1) vales ('8vhrfvrf')
insert into T3 (col1) values (3)
COMMIT TRANSACTION
GO


I would like to make that these piece of code (
insert into serverA.db_A.dbo.T2 (col1) vales ('8vhrfvrf')
) is out of transaction.
My question is: Is it possible to exclude piece of code from a transction?


You ask me why this: because SQL server do not allow loopback transaction


Thank

View 7 Replies View Related

Exclude Duplicate Records

Jan 10, 2007

Hello

I'm developing my fist Integration Service and I have this operations:

Reading from a XML
Check for duplicate records and discard them
Insert the result into the database

The XML I don't control and could came with duplicate records that I have to discard. How can I find them?

I want to find the duplicates in the XML and not in database.



tkx for the help
Paulo Aboim Pinto
Odivelas - Portugal

View 14 Replies View Related

Returning Records That Exclude Top 10% Of Values

Mar 6, 2008

I have data that looks like below (columns are Timestamp, Offered, Answered and Delay). I'm looking to exclude returning records that have a value for Delay that are within the top 10% of values of that column. Are there any 2005 tricks where this can be accomplished in a simple statement?

2008-02-18 08:30:002322173
2008-02-18 08:45:002120174
2008-02-18 09:00:002425230
2008-02-18 09:15:002828277
2008-02-18 09:30:002522159

View 3 Replies View Related

How To Exclude Records That Have A Common Column

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

Transact SQL :: Exclude A Distinct Records From Select When One Condition Is True?

May 28, 2015

I have customers named Alex (Cid=1), Bob (Cid=2), and Carrie (Cid=3) in a table customer.

Cid
First_Name
1
Alex
2
Bob
3
Carrie

I have products name Gin (Pid=1), Scotch (Pid=2) and Vodka (Pid=3) in a table products.

Pid
Product_Name
1
Gin
2
Scotch
3
Vodka

And I have a table that holds purchase called Customer_Purchases that contain the following records:

Cid
Pid
1
1
1
2
2
1
2
3
3
2

I would like to make a marketing list for all customers that purchased Gin or Scotch but exclude customers that purchased Vodka. The result I am looking for would return only 2 records: Cid’s 1 (Alex) and 3 (Carrie) but not 2 (because Bob bought Vodka).

I know how to make a SELECT DISTINCT statement but as soon as I include Pid=2 This clearly doesn’t work :

SELECT DISTINCT Pid, Cid
FROM           
Customer_Purchases
WHERE        (Cid = 1) OR
(Cid = 3) OR
(Cid <> 2)

View 3 Replies View Related

Tables Joined On Multiple Columns, Exclude Records Found In Table A

Nov 7, 2006

I'm using SQL server 200

Table A has columns CompressedProduct, Tool, Operation

Table B in a differnt database has columns ID, Product, Tool Operation

I cannot edit table A. I can select records from A and insert into B. And I can select only the records that are in both tables.

But I want to be able to select any records that are in table A but not in Table B.

ie. I want to select records from A where the combination of Product, Tool and Operaton does not appear in Table B, even if all 3 on their own do appear.

This code return all the records from A. I need to filter out the records found in Table B.

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

SELECT ID, CompressedProduct, oq.Tool, oq.Operation FROM OPENQUERY (Lisa_Link,
'SELECT DISTINCT CompressedProduct, Tool, Operation FROM tblToolStatus ts
JOIN tblProduct p ON ts.ProductID = p.ProductID
JOIN tblTool t ON ts.ToolID = t.ToolID
JOIN tblOperation o ON ts.OperationID = o.OperationID
WHERE ts.ToolID=66
') oq
LEFT JOIN Family f on oq.CompressedProduct = f.Product and oq.Tool = f.Tool and oq.Operation = f.Operation

View 1 Replies View Related

Access Crosstab -&> SQL Crosstab

Jan 9, 2006

Hi all
I'm transferring some Access queries to SQL server and the crosstabs don't want to work, can anyone shed any light on the query below.

Thanks.

TRANSFORM Min(tCompany.cCompanyName) AS CompanyName
SELECT tProjContacts.ProjectID
FROM tCompany INNER JOIN (tProjContacts INNER JOIN tCompanyType ON tProjContacts.CoTypeId = tCompanyType.CoTypeId) ON tCompany.CompanyID = tProjContacts.CompanyID
WHERE (((tCompanyType.CoType) Like "*topo*" Or (tCompanyType.CoType) Like "*ground*"))
GROUP BY tProjContacts.ProjectID
PIVOT tCompanyType.CoType;

View 1 Replies View Related

Exporting Records To A File Through Code

Aug 13, 2007

Hi.

I am writing a program where I import data into a database with a System.Web.UI.WebControls.SqlDataSource object, using its Insert method.



Like this...
Dim DataBaseConnectionString As New Web.UI.WebControls.SqlDataSource()
DataBaseConnectionString.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True"
DataBaseConnectionString.InsertCommandType = Web.UI.WebControls.SqlDataSourceCommandType.Text
DataBaseConnectionString.InsertCommand = "BULK INSERT TBLMain FROM '/file.csv' WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '')"
DataBaseConnectionString.InsertParameters.Add("csvdatapath", OFDCSV.FileName.ToString())
Try
DataBaseConnectionString.Insert()
Catch ex As Exception



I have been able to get this working fine.

I need to export to (ideally) a .CSV or a .XLS. I have tried to use the:




Code Snippet (SQL)COPY TO '
esults.csv' CSV


Is it possible to use a simmilar method of interacting with the database through code to perform this?

Thanks

View 6 Replies View Related

DTS & Crosstab

Apr 19, 2000

I am a neewbie to SQL 7 and having previously used Access for some time.. I have a table with a date field in a want to create a table with the values filling columns in another table by month ie April,may ,Jun and so on. Ia m using the Month function to get the month number but I dont know how to crosstab this into the relevant columns in the new table...I thinks DTS is the way and do a transformation on the month field but am struglling at the mo...Any help greatfully appreciated...

Regards

Andrew Wall

View 2 Replies View Related

Please Help Me In Crosstab

Jun 18, 2007

Hi i am new to crosstab feature.i want a crosstab dynamically with this below table please help me .Thanks in advance.

Table :-

idnamecosttypemonth&yearamount

1a1Bcost9/2005300
2a1Ecost9/2005200
3a1Acost9/2005100
4a2Bcost10/2005150
5a2ECost10/2005100
6a2Acost10/2005150
7a3Bcost11/2005150
8a3ECost11/2005100
9a3Acost11/2005150



I want result table like this :-

ID Name Cost_Type 9/2005 10/2005 11/2005

1 a1Bcost 300 -
2 a1Ecost 200 -
3 a1Acost 100 -
4 a2Bcost -150
5 a2ECost - 100
6 a2Acost - 150
7 a3Bcost - -150
8 a3ECost - -100
9 a3Acost - -150


I want above result Please Give me your help.

Thanks in advance,
Rajeev

View 1 Replies View Related

CrossTab

Aug 23, 2005

Hi Guys!Is there anything like cross tab of access in sql server?Thanks.

View 1 Replies View Related

Crosstab Help

Jul 13, 2007

Hello,



I need to accomplish the turning column data into row data via SQL. I can sorta get what I want with creating the report as a matrix report. However, I always seem to need one little thing to happen to shape my data as I need it. Anyway, here goes...



I have a table with 4 columns



UNIQUE | Code | FieldID | CustomField



The Code column is the customer code. FieldID numbers 1-100 and CustomField has string data.



My problem. I need to be able to choose ALL Customers [Code] where FieldID/s ="6", "7" & "8" and the CustomField rowdata corresponding to the FieldID data. BUT I then need to be able to use a daterange parameter on any row with a FieldID of "6" but that is not a datetime format it is in a string format.



Currently my SQL is:



SELECT
[Unique ID], Code, [Field ID] AS IUdate, [Field ID] AS IUNote, [Field ID] AS IUReq, [Custom Field]

FROM
dbo.[Customer Custom Field]

WHERE
(Code = '07-8111')AND ([Field ID] = 6) OR (Code = '07-8111')AND ([Field ID] = 7) OR (Code =

'07-8111')AND ([Field ID] = 8)



However, everytime I try to run a daterange against, I get all kinds of data I don't need because the parameter is running against all the fields and NOT just the stringdate.



My idea is to convert the column data into row data (like a crosstab query) to sharpen it up for parameter ranges.



Is there something i am missing here? Can someone point me in the right direction?



Thanks in advance!



phorest

View 6 Replies View Related

Exclude Rows

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

Exclude Condition

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

Crosstab Query

Mar 14, 2002

Hello everyone,

I have a tabel which looks like the follows:

CODE Type Number
1 account 20
1 empl 3
2 account 15
2 empl 6
3 account 32
3 empl 7

I need to show the results like

CODE Account Empl
1 20 3
2 15 6
3 32 7

I've tried different attempts but so far I always get 2 rows for each code,where one column shows a NULL value.
How do I have to do this ?

View 1 Replies View Related

Crosstab Query

Apr 5, 2001

Is there a way to write Crosstab query in SQL 7.0.

I have a Table which has partner, usernames and the city they are from, apart from other fields. I have another table which tracks the user activity on a day by number of visits to the web site. What i want is like this :-

Date Total Visits Atlanta Connecticut .... .... ....

03/01/2001 5025 567 324 .... .... ....
03/02/2001 6789 423 146 .... .... ....
... ... ... ... .... .... ....

I.E. I wan't the city names to appear as columns. The main catch here is that this data is for a given partner. So the city names could be different for different users from different partners. i.e. city names have to be generated on the fly by looking at the partner table and then by getting all its users and seeing what all are the cities involved.

Any help would be appreciated.

Thanks
Sumit.

View 1 Replies View Related

Crosstab Reports

Jun 4, 2001

I am trying to run a report off a crosstab query. The report calls for columns A - F, which are returned from the crosstab query. The only problem is that at times some of the columns are not returned by the query because there is no data associated with them. The report asks for column X, and no such columns exists in the query result; thus, an error is generated. My question is: how do I deal with this problem? I would like to avoid having to build the report from scratch. Is there some way that at runtime I could programatically tell the report to ignore these fields?

View 2 Replies View Related

MDX - Exclude Condition

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

Crosstab Problem

Nov 10, 2005

I have a table called Ideas and a table called Users_Ideas. In Users_Ideas, there can be up to four rows referencing Ideas with a foriegn key. I need to select all rows from Ideas and for each Idea display the users associated with it.

View 8 Replies View Related

Yet Another Crosstab Problem

Nov 14, 2005

Hi all,
i see there are many posts on crosstab queries in this forum but i can't seem to find a solution to my problem. hope you can help me.

I have a view of server crashes:

[dirty shutdown] [previous clean] [server name]
2005-10-01 2005-09-01 srv1
2005-11-01 2005-10-10 srv2

which displays the date of a dirty shutdown of a server, and the date of the previous clean shutdown of this server plus the server name

Also, i have a view of alerts per server:

[alert id] [server name] [alert date] [alert name] [repeat count]
123 srv1 2005-09-05 an alert 0
124 srv1 2005-09-10 another alert 1
125 srv1 2005-09-20 an alert 0
126 srv2 2005-10-20 something else 0

If [repeat count] is 0, the alert was given once, if it is 1, there were 2 alerts etc.

Now, what i want (well not me but the guy i work for) is a view that displays all alerts between the clean and the dirty shutdown, per server:

[dirty] [clean] server total "an alert" "another alert" "something else"

2005-10-01 2005-09-01 srv1 4 2 2 0
2005-11-01 2005-10-10 srv2 1 0 0 1

Total is the total number of alerts for the server between the two dates. Of course, there are many servers, and the number of alert names varies over time which means i can't use "case when...".

Is this possible??? Would be extremely thankful for any help!

Regards,
Elisabet

View 1 Replies View Related

CrossTab Query

Mar 8, 2006

Is there a SQL version of a Crosstab Query??

View 14 Replies View Related

No Crosstab Like Access?

Mar 30, 2006

I'm trying to port a dymanically renderred form from a .mdb to a .adp. In the Access .mdb. The form is a representation of a crosstab query with unknown column headings (hence, the need for the form to be dynamically rendered at runtime).

I tried to port the crosstab from the .mdb to a stored procedure, but SQL Server doesn't like TRANSFORM, and perhaps the PIVOT as well. How do you do this in sql server?:

TRANSFORM Count(d.CAR_INIT) AS CountOfCAR_INIT
SELECT b.WKLD_SEQ, c.TRN_ID, c.TRK_NBR
FROM ((TSA_HS_MPCT_CNT a INNER JOIN TSA_HS_COMB2 b ON a.RECC_COMB_ID = b.COMB_ID)
INNER JOIN TSA_HS_WKLD c ON b.WKLD_ID = c.WKLD_ID) INNER JOIN TSA_HS_OBJ_TRN d ON c.WKLD_ID = d.WKLD_ID
GROUP BY b.WKLD_SEQ, c.TRN_ID, c.TRK_NBR
PIVOT d.LST_HMP_DTM + d.OBJ_DEP_TRN

Thanks,
Carl

View 1 Replies View Related

Crosstab Problem Help

Sep 20, 2006

Hi,
I have tried with the solutions available in the links provided by Pootle,but still I think I have to go a long way.My main problem is with the second aggregate column of previous year.
Let me put it again....


I have a table policy :

CREATE TABLE Policy(
Policyno VARCHAR(20)
,Inceptiondate DATETIME
,agentid VARCHAR(20))



I want a report like this (dates in DD/MM/YYYY)

date 32001 32002 32003 32004 2006 2005
01/08/2006 3 1 11 1 16 12
02/08/2006 1 1 1 2 5 22
03/08/2006 1 1 1 1 4 3



Now the problem is I can get a report using crosstab like this in crystal report

| agentid
-----------------
date | sum(policyno)
-----------------

which is giving a result like this

date 32001 32002 32003 32004 2006/ToTal
01/08/2006 3 1 11 1 16
02/08/2006 1 1 1 2 5
03/08/2006 1 1 1 1 4
Total 4 3 13 4 25


But I can't bring the previous year aggregate column ,i.e 2005 in the above case.

So can you suggest me a way or direction to acheive that?

Any help would be really appreciated.
BTW,Pootle gave these links in my previous post...
link1 (http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21820764.html#16500817)
Link2 (http://www.sqlteam.com/item.asp?ItemID=2955)

View 3 Replies View Related

Exclude If Returned Value Is 0

Jan 27, 2012

I am running a query that works just fine however, I would like it to exclude value that are equal to zero.

Basically my query looks at the commission that clients pay over a number of periods.

So it goes like this

T.Client_Code as Client
,SUM(CASE t.Transaction_Date WHEN DATEADD(day, DATEDIFF(day, 1, GETDATE()),0)THEN (ABS (t.transaction_commission) /((fx.Exchange_Bid + fx.Exchange_Ask)/2 )) ELSE 0 END)as Commission_Day

FROM TABLE T
JOINING FX TABLE

WHERE
fx.Currency = 'USD'

And T.Salesman_Name in ('X''Y'Z)

Group BY
T.Client_Code

It works perfectly fine however, we dont transact with our clients everyday so therefore this list will return all of our clients in the database and many will have generate zero commission. I want to keep the query along those lines I just need to insert something that says "ONLY SHOW WHEN Commission is not ZERO.

View 2 Replies View Related

Crosstab Select!!

Apr 3, 2008

Hi
I have table called MyTable that has 3 columns (City , Brand, Price)
I want to select from this table in Cross Tab format
i.e.
My table has
CityBrandPrice
LondonDELL1227
LondonToshiba1100
LondonAcer1007
LondonHP1467
LondonIBM1193
SydneyToshiba2100
SydneyAcer2219
SydneyApple2589
SydneyVAIO2122
SydneyHP1929
SydneyIBM2877
TokyoToshiba7200
TokyoAcer5299
TokyoCOMPAQ9200
TokyoIBM8779
TokyoHP6286
ParisDELL1670
ParisApple1825
ParisVAIO1267
ParisHP1882
ParisCOMPAQ1636
ParisIBM1332
NewYorkDELL2000
NewYorkToshiba1288
NewYorkAcer2333
NewYorkApple2299
NewYorkVAIO2327

__________________________________

i want the select statment result to be like this
DELLToshibaAcerHPIBMAppleVAIOCOMPAQ
London12271100100714671193N/AN/AN/A
SydneyN/A2100221919292877N/AN/AN/A
TokyoN/A7200529962868779N/AN/A9200
Paris1670N/AN/A18821332182512671636
NewYork200012882333N/AN/A22992327N/A


City & Brand can be anything so i will not be able to hard code them in my Select.

who can help with that?!

View 2 Replies View Related

Exclude Status Appear More Than Once In Same ID

Jul 16, 2013

How can i exclude those with more than one status? i tried using rows but it seems doesn't work.

ID status
1 new
1 new
1 close
2 new
2 close
3 close
3 close
4 new
4 close

the result should appear as:

ID status
2 new
2 close
4 new
4 close

View 4 Replies View Related

Exclude Columns

May 13, 2006

Hi

i wanna exclude some columns from my select statement. i do NOT wanna columns starting with 1,2,3,4,5 and I. How can i do it?

thanks

View 5 Replies View Related

Crosstab Query

Sep 2, 2006

Crosstab concepts

I m using Sql server 2000 version
I want to create a simple crosstab… as like the follows

My table is as like

Date warehouse (WH) Delivery No
7/7/2006 10 10-1
7/7/2006 20 20-1
7/7/2006 20 20-2
7/7/2006 30 30-1
8/7/2006 10 10-2
8/7/2006 10 10-3
11/7/2006 30 30-2
11/7/2006 20 20-3


I want to make a report using crosstab in iReport…..

I want report will be as like


Date WH10 WH20 WH30 total delivery
7/7/2006 1 2 1 4
8/7/2006 2 0 0 2
11/7/2006 0 1 1 2


my questions are:

i create a crosstab procedure and it is running but

01. I want to know the query.
02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table )

I want step by step instructions
I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem

Please help……

Thanks


shohan

View 1 Replies View Related

Crosstab Query

Sep 2, 2006

Crosstab concepts

I m using Sql server 2000 version
I want to create a simple crosstab… as like the follows

My table is as like

Date warehouse (WH) Delivery No
7/7/2006 10 10-1
7/7/2006 20 20-1
7/7/2006 20 20-2
7/7/2006 30 30-1
8/7/2006 10 10-2
8/7/2006 10 10-3
11/7/2006 30 30-2
11/7/2006 20 20-3


I want to make a report using crosstab in iReport…..

I want report will be as like


Date WH10 WH20 WH30 total delivery
7/7/2006 1 2 1 4
8/7/2006 2 0 0 2
11/7/2006 0 1 1 2


my questions are:

i create a crosstab procedure and it is running but

01. I want to know the query.
02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table )

I want step by step instructions
I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem

Please help……

Thanks


shohan

View 1 Replies View Related







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