Transact SQL :: How To Create UNION Clause With Two Queries That BOTH Have WHERE Clause

Nov 4, 2015

I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?

I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....

I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.

Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?

View 13 Replies


ADVERTISEMENT

Union Clause

May 15, 2004

sql = "Select c1 from t1
sql = sql & " Union Select c1 from t2 Order By c1"

t1=c1 = 1,2,3
t2=c1 = 200,5,300

datatype=nvarchar

result is not ordered as 1,2,3,4,200,300 why

View 1 Replies View Related

UNION With WHERE Clause?

Jun 19, 2012

I have 2 tables(Customer,Staff) with the same column name 'email'. I would like to join both tables together where email="xxx@xx.com" to check if email exist already in the database. I have tried

Code:
Select email from (select email from staff union select email from customer) as 'CombinedEmail' where email = 'xxx@xx.com'

But it does not work at all. Is there anything wrong with my codes?

View 4 Replies View Related

Top Clause , Union And Order By

Dec 7, 2007

Hi,

I'm currently have a problem with a query using a top clause. When I run it by itself as a single query, I have no problems and the results are valid. However, if I try duplicate the query after a union clause, the order by ... desc doesn't order properly.

The following is the query I'm using along with the results. Then I'll have the query I was trying to unite and the results (date ranges selected were the same in both):

QUERY 1

select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'

from EventStrings ES

JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3

WHERE es.providerid in (0,1,4)
and s.ldate>=?
and s.ldate<=?
and v.mdtid=20411

order by s.ldate desc


RESULTS 1

DATE MDT IDPU Odometer DO Odometer Total Miles
12/6/2007 2041112810.6 12874.5 63.9

QUERY 2 (with Union)

select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'

from EventStrings ES

JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3

WHERE es.providerid in (0,1,4)
and s.ldate>=[From Date,Date]
and s.ldate<=[To Date,Date]
and v.mdtid=20411

Union

select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'

from EventStrings ES

JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3

WHERE es.providerid in (0,1,4)
and s.ldate>=?
and s.ldate<=?
and v.mdtid=2642

order by s.ldate desc

RESULTS 2

DATE MDT ID PU OdometerDO Odometer Total Miles
4/10/2007 20411 1207.21252.5 45.3
1/2/2007 2642 193652.6193817 164.4

As you can see, the results are sorted very differently. Is there any way to have the order by apply to both queries?

Thanks!
Craig

View 5 Replies View Related

Using Group By Clause In Union Query

Sep 19, 2007

Hi all,

I have two table having datas like

Table1
--------------------------------------------------------------------
A C1 C2 C3 C4
--------------------------------------------------------------------
x 0 0 3 2
x 0 1 0 2
x 0 0 2 1
y 1 5 2 0


Table2
--------------------------------------------------------------------
A C1 C2 C3 C4
--------------------------------------------------------------------
x 0 0 1 4
y 1 0 3 1
y 1 2 0 0
y 0 0 5 1

select * from(
select A,C1,C2,C3,C4 from Table1 group by A
union
select A,C1,C2,C3,C4 from Table2 group by A
)as t

Result:
--------------------------------------------------------------------
A C1 C2 C3 C4
--------------------------------------------------------------------
x 0 1 5 5
y 1 5 2 0
x 0 0 1 4
y 2 2 8 2

But i need the result like i.e grouped by column 'A'
--------------------------------------------------------------------
A C1 C2 C3 C4
--------------------------------------------------------------------
x 0 1 6 9
y 3 7 10 2

select * from(
select A,C1,C2,C3,C4 from Table1 group by A
union
select A,C1,C2,C3,C4 from Table2 group by A
)as t group by A

The above query gives the following error
[Error Code: 8120, SQL State: S1000] Column 't.C1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Please help me out.

-Anand

View 3 Replies View Related

How To Use Parameterized Queries With IN Clause

Feb 5, 2008

Hi,
I need to use parameters with the IN clause in a SQL statement like:
select * from tableX where field IN (1,2,3,4)
I don't know how to do that.
I'm using SQLServer and OleDB.
 
Thanks for your help.
 

View 1 Replies View Related

When Does Ssrs Use A DISTINCT Clause In It's Queries?

Jun 27, 2007

I am having a heck of a time figuring out what controls how/when the generated SQL for a report puts a DISTINCT clause in front of it.

For instance, not that this report makes any sense, but I have 58 rows in my fact table/entity €“ If I pull in a lookup field and execute, the distinct is put in the query and I basically get a list of the possible domain values. It runs the whole joined table query to get them, but it does list (in this case) just 4 records. Now I put in the primary ID of the fact entity and the distinct goes away and I get my 58 rows. If I put two lookup fields, the DISTINCT is back. If I pull in the description field (text string just a direct source field mapping not part of the identifying attributes), the distinct is there. If I pull in the Company Name field on a different entity (which is essentially the same as pulling in Description only it is part of the identifying attributes), there is no DISTINCT. I can pull in all my fields on this entity and none of them drive the distinct. And I swear (ok, I am probably lying but not on purpose) the field/attribute and roles properties are all the same on the attributes. But you get my general question/situation...

Any insight for me? Does it have to do with how I am building the report rather than the underlying model?

Thanks in advance,

View 1 Replies View Related

Sub Queries, Aggregate Functions && Group By Clause

Feb 17, 2008

Hi Guys,

I am having trouble with a particular query that is beyond my scope of understanding.

Basically I need to pull sales records based on the following criteria:

I have CustomerID, InvoiceNumber, ContractEndDate, MobileNumber, etc..

Customers recontract their mobile phone plans through us, and we have a new sales record for each time they recontract.

For example, CustomerNumber 123 has recontracted 3 times..

once on 2006-01-01, then on 2007-02-12, and finally on 2008-02-15..

So they have a 12 month contract each time.. then come in to recontract it.

So.. a customer has a single Customer Detail record, but may have many sales records attached. And a customer may have several sales for the SAME mobile phone number.

Currently to pull ALL sales records for all customers, my query is this:


Code:


SELECT xxx.CustomerID AS xxx_CustomerID,
xxx.Invoice AS xxx_Invoice,
yyy.PhoneType AS yyy_PhoneType,
yyy.PlanType AS yyy_PlanType,
yyy.ContractEnds AS yyy_ContractEnds,
yyy.MOB AS yyy_MobileNumber

FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice

WHERE yyy.ContractEnds IS NOT NULL
AND xxx.CustomerID IS NOT NULL



We want to get a list of customers that we can call to recontract, based on the ContractEnd field.

However, we want UNIQUE mobile phone numbers, with the LATEST ContrtactEnd date.

So, Customer 123 has 6 sales, for 2 unique Mobile numbers, the sql may be like:


Code:


SELECT MAX(yyy.ContractEnds) AS LatestCED, yyy.MOB
FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice
WHERE xxx.CustomerID='123'
GROUP BY yyy.MOB



Now, this works fine, and of course if i remove the WHERE clause, it collects all unique mobiles, with latest ContractEnd date for each, for all customers. (Customer 123 displays 2 mobile numbers, each with the LATEST ContractEnd date)

BUT i need this information ALONG WITH the other fields (xxx.CustomerID, xxx.Invoice, yyy.PhoneType, yyy.PlanType) and i have tried a few ways of doing it, but can't get my head around it..

Keep getting errors about Aggregate functions and Group By clause, and i understand why i am getting them, just cant think of any alternative query.

Can anyone please help me!

Thanks guys,

Mick

View 1 Replies View Related

Report Model - Where Clause In Named Queries

Mar 30, 2008



Hi,
Consider the following situtation.
ReportModel is created using named queries. It has multiple entities.
Report Model is deployed to the report server and datasource is set dynamically at runtime(programmatically). When user runs the report (which is created using report model above), I need to change the where clause of one of the entities in ReportModel. How can i acccomplish this programmatically?

View 4 Replies View Related

Transact SQL :: Use Where Clause

Aug 11, 2015

Declare @date date = '2015-02-12'
Column in table datetime format

when i  use  where clause WHERE @Date > ValidateDateTime ,  got no rows  but when i changed ValidateDateTime > @date, getting result as expected, this behavior because of data format or  something else.

View 3 Replies View Related

Transact SQL :: IS NULL In A Where Clause

Nov 10, 2015

When building stored procs, I typically have several nullable parameters. Then in the where clause I will either do this

WHERE (@MyParam IS NULL OR MyColumn = @MyParam)
or this:
WHERE MyColumn = COALESCE(@MyParam, MyColumn)
Lately, I've noticed that either of these imposes a performance penalty. In other words, if I change the first one to
WHERE MyColumn = @MyParam

without the IS NULL, it is noticeably faster. If I only have one or two nullable parameters, I can separate the logic using IF/ELSE, but it becomes huge if I have a bunch of nullable params.I've looked at the execution plans.

View 8 Replies View Related

Transact SQL :: UPDATE With OUTPUT Clause

May 28, 2015

I am trying to update a table and then also use OUTPUT clause to capture some of the columns. The code that I am using is something like the one below

UPDATE s
SET Exception_Ind = 1
OUTPUT s.Master_Id, s.TCK_NR
INTO #temp2
FROM Master_Summary s
INNER JOIN Exception d
ON d.Id = LEFT(s.Id, 8)
AND d.Barcode_Num = s.TCK_NR
WHERE s.Exception_Ind IS NULL

The above code is throwing an error as follows:

Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.Master_Id" could not be bound.
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113

The multi-part identifier "s.TCK_NR" could not be bound.

View 4 Replies View Related

Transact SQL :: Match Column Value In Where Clause

Jun 4, 2015

I have a table Customer with column name "SerNo" the value of SerNo column is like below.

Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3
Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3
Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3
Metz-2d9c957d-ca1c-4b27-adf8-39fef552f3f7
Metz-2d9c957d-ca1c-4b27-adf8-39fef552f3f7
Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3

[code]...

I want to join it with nother table "Order" which has a SerNo column but does not have first part of SerNo.

SerNo.
9ad88929-f32d-459e-96c4-8d6c3d61e9d9
0a1d8b8f-7f5f-4a01-8e63-64579213afef
9342f8d7-dfdd-4535-8f01-5301bde669aa

[code]...

So basically i want to join these two tables and ignore the first part before "-" from SerNo column in customer table.

View 5 Replies View Related

Transact SQL :: Value In Field Not Returned By WHERE Clause

Nov 18, 2015

I have a small table named Exclude_Cusno_Item that has a varchar(29)  field named

ITEM. When I query the table to show all of the records like this:
Select * from Exclude_Cusno_Item

The result set contains a record with 'AAA' in the ITEM field.  However, where I write a query with a WHERE clause to select this specific 'AAA' record like this:

Select * from Exclude_Cusno_Item where item = 'AAA'

I get no results. What could be happening that would prevent the 'AAA' record from appearing in the result set of this query? Note that the 'AAA' entry has no trailing or leading blanks.

View 12 Replies View Related

Transact SQL :: Using Literal In Openquery Where Clause

Aug 14, 2015

I'm struggling with the syntax for qualifying an openquery's results with a where clause. I copied this from examples on the web but get the error   incorrect syntax near '32810'.

declare @sysid varchar(50) = '32810C534D01C920E7CB07EBC0A80122'
declare @sql varchar(500) =
'select * from OPENQUERY(WAREHOUSE,''select * from xxx.yyyy where sys_id = ''' + @sysid + ''''')'
exec(@sql)
selecting @sql it looks like
select * from OPENQUERY(WAREHOUSE,'select * from xxx.yyyy where sys_id = '32810C534D01C920E7CB07EBC0A80122'')

View 6 Replies View Related

Transact SQL :: CASE Expression In WHERE Clause?

Nov 6, 2015

I have a scenario where the End User is going to select two parameters.  @AgentID and @Location If the End User selects @AgentID parameter, in my WHERE clause, I do not want to check the location condition. By default, the agent knows their location already.

If the End User select @Location parameter, in my WHERE clause, I still want to check the Agent condition.

@AgentID parameter would populate either with "All" or their actual agent# (76754 for example).

@Location parameter would populate either with "All" or their actual location (Birmingham or Bessemer for example)Sample of my current WHERE clause:

WHERE
 
 gl.ReceiptDate >= @BeginDate and gl.ReceiptDate < DATEADD(D,1,@EndDate)
 AND gl.AgentId = CASE WHEN @AgentID = 'All' THEN gl.AgentID ELSE @AgentID END
 AND gl.Location = @Location

View 9 Replies View Related

How To Create Dynamic T-SQL Clause

May 6, 2008

Hi,


I have a project which is need dynamically t-sql clause...I've created interface for the create dynamic t-sql clause.
But the clause should get data from relational database. It needs inner join...so it's very hard to do it..

for example


lkpProduct

id product
1 Mouse
2 Keyboard

3 Modem
4 Monitor


main

id productid

1 4
2 1
3 2
4 3
5 4

You can get this table with 2 ways like this..

1. Select main.id, lkpProduct.product from main,lkpProduct where main.productid = lkpProduct.id
2. Select id, case productid when 1 then 'Mouse'.....end as product from main

mytable

id product
1 Monitor
2 Mouse
3 Keyboard
4 Modem
5 Monitor


My question is; how can i use CASE function dynamically ?? Mouse should get from lkpProduct....I do not want to write long table manually..

I hope it is clear for you..

Regards.

View 4 Replies View Related

Transact SQL :: Getting Last Register From A Query That Use A Group By Clause

Aug 13, 2015

I've got this set of registers (just an example) after ordering by the first 3 columns:

value_A value_B value_C ID date
1 2 3 YVIR 29/08/2015
1 2 3 ANTE 27/04/2015
1 2 3 REGO 20/02/2015

I need to get as a final result:

value_A value_B value_C ID date
1 2 3 REGO 29/08/2015

In other words, I need to get, after ordering the result by the date field, the most recent date but at the same time the oldest ID in the list.

I've been trying to do this with the group by clause:

select
value_A, value_B, value_C, min(ID), max(date) -- or max(ID)
from table
group by value_A, value_B, value_C

But in the field ID I'm getting the wrong result because this value is been associated with the alphabetic order.

In access this query involves the function LAST, but in SQL I have not found a good way to perform this. And I am asking because I have seen some possible solution but almost all of them involving the UNION operation, but my problem is, this table can have more than 350.000 registers.

This table is update by some one else, I just can access the information and use it as a source.

View 3 Replies View Related

Transact SQL :: Writing Where Clause In Dynamic String?

Nov 2, 2015

I am trying to write where clause after pivot but I could not know how?

DECLARE @PivotQuery AS NVARCHAR(MAX)

View 8 Replies View Related

Transact SQL :: Case Statement In Update Clause

Jun 4, 2015

I have used the below update query. However, its updating only the first value. Like its updating AB with volume when c.Type  = ABC, similarly for CD. Its not updating based on the 2nd or the next case condition.
 
Update XYZ Set AB = a.Amt * (CASE WHEN c.Type = 'ABC'  THEN  (c.volume)
 WHEN c.TYPE = 'DEF'  THEN  (c.volume)
 WHEN c.Type = 'GHI'  THEN  (c.volume)
 Else 0
 END),
 CD = CASE WHEN c.Type = 'MARGIN' THEN '4105.31'
 WHEN c.Type = 'ABC' THEN '123.1'
 WHEN c.Type = 'DEF' THEN '234.2'
WHEN c.Type = 'GHI' THEN '567.1'
END
 from table1 a join table2 b
 on a.Cust = b.Customer
 join table3 c
 on b.account = c.account and b.channel =c.channel

Why its not working properly? But if i use Select statement instead of update query its working properly.

View 18 Replies View Related

Transact SQL :: Removing Duplicates Rows With OVER Clause

Jun 2, 2015

I have an existing stored table with duplicate rows that I want to delete.Using a cte gives me

WITH CTE AS
(
SELECT rn = ROW_NUMBER()
OVER(
PARTITION BY employeeid, dateofincident, typeid, description
ORDER BY Id ASC), *
FROM dbo.TableName
)
DELETE FROM cte
WHERE rn > 1

This is what I want to do basically. But this is only deleting in my CTE, is there anyway I can update my existing table "TableName" with this, without using temp tables?

View 4 Replies View Related

Transact SQL :: Dynamic Query And Formulating IN Clause

May 7, 2015

I have to build a dynamic query to go over to Oracle and get some data and bring it back via a SQL Server Stored Procedure. How I can create an IN clause passing values and ","s from a SQL Server Temp Table that I have already built.

SET @SQLQuery = @SQLQuery + 'WHERE MEMBER.MEMBER_ID IN (''''' + SELECT MEMBER.MEMBER_ID + ',' FROM #TempTable + ''''' '

View 6 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

GROUP By Clause Or DISTINCT Clause

Jul 23, 2005

Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1

View 3 Replies View Related

Filtering Results In The Where Clause Vs A Having Clause

Oct 25, 2007

I am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following.

When on SQL Server 2000 the following statement ran without issue:

UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN

(


SELECT TrackID

FROM dbo.Track_ID

GROUP BY TrackID

HAVING MAX(LegNum) = 1 AND


TrackID + 'x1' IN


(


SELECT

dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions


ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)

)
Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.

I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?


UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN
(



SELECT TrackID

FROM dbo.Track_ID

WHERE TrackID + 'x1' IN


(


SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions


ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)
GROUP BY TrackID

HAVING MAX(LegNum) = 1

)

View 3 Replies View Related

Transact SQL :: Where Clause Based On Select Statement From Another Table

Jul 9, 2015

I basically want to select all GRNID's from one table but they have to be between dates in another table.So I want all GRN's between two dates found in the ABSPeriodEndDate table. To find out the start date for the between clause I need to find the MAX Period then minus 1 and the max year. To find the end date of the between clause I want I need to find both the max period and year. But I want the DateStamp column to return the results for the between clause. My query is below:

SELECT tblGRNItem.GRNID
FROM tblGRNItem
INNER JOIN ABSPeriodEndDates ON tblGRNItem.DateCreated = ABSPeriodEndDates.DateStamp
WHERE tblGRNItem.DateCreated BETWEEN
(SELECT ABSPeriodEndDates.DateStamp FROM ABSPeriodEndDates WHERE ABSPeriodEndDates.DateStamp = (SELECT

[code]....

View 6 Replies View Related

Transact SQL :: Parent Child Data With Different Order By Clause?

Jun 24, 2015

I'm asking my previous question in this new thread with more specific data and condition.I have below sample data,

DECLARE @DATA TABLE (U_ID INT, U_NM NVARCHAR(20), U_DT DATE, U_ORD INT, P_U_NM NVARCHAR(20) NULL)
INSERT INTO @DATA VALUES (1, 'Design', '06/15/2015', 2, NULL), (1, 'Plan', '07/01/2015', 1, NULL), (1, 'Cust Plan 1', '06/10/2015', 0, 'Plan'), (1, 'Cust Plan 2', '06/05/2015', 0, 'Plan'),
(2, 'Design', '06/25/2015', 2, NULL), (2, 'Plan', '06/20/2015', 1, NULL)

We have 2 different U_ID (1, 2) and I want a SELECT query to display,

1. For U_ID = 1, we have 2 parent U_NM (Design & Plan) and Plan having 2 child (Cust Plan 1 & Cust Plan 2).
2. I want to display parent U_NM ORDER BY U_ORD
3. If any parent having child element, then need to show immediately under that parent and ORDER BY U_DT
4. For U_ID = 2, we don't have any child, hence display ORDER BY U_ORD

View 10 Replies View Related

Transact SQL :: Using A Variable Containing Comma Separated Values In IN Clause?

Jun 12, 2015

I am try to use a variable containing comma separated values in the IN clause.

View 10 Replies View Related

Transact SQL :: Finding Syntax To Combine IN And CASE In A WHERE Clause?

Oct 29, 2015

I cannot seem to find the syntax to combine IN + CASE in a WHERE clause

WHERE
ses.BK_MS_SESSION <= '2015-03'
AND vis.CAT_DRAW_STATUS =
(CASE ses.BK_MS_SESSION
WHEN '2015-03' THEN vis.CAT_DRAW_STATUS
ELSE
CASE stat.BK_MS_VISIT_STATUS
WHEN 'T' THEN 'X'
ELSE vis.CAT_DRAW_STATUS
END
END
) IN ('D','R')

View 7 Replies View Related

Transact SQL :: How To Make Roundtrip Records By Using Order By Clause

Oct 7, 2015

I have a table that has the records as shown below

Id         From             To
1 Delhi         Noida
2 Faridabad   Kanpur
3 Noida Delhi
4 Kanpur Faridabad
5 Banglore Mumbai
6 G H
7 I J
8 Mumbai Banglore

I want the results in the following order

5 Banglore Mumbai
8 Mumbai Banglore
1 Delhi Noida
3 Noida Delhi
2 Faridabad Kanpur
4 Kanpur Faridabad
6 G H
7 I J

Suppose as in above records There is a complete roundtrip from Banglore to Mumbai and Mumbai to Banglore.

View 11 Replies View Related

'Include Clause' In Create Index Syntax

Mar 26, 2008

What is the equivalent for INCLUDE clause (in Create index syntax) in SQL Server 2000.
SQL Server 2005 will support Include clause in Create index syntax . How to attain it in SQL Server 2000

Example : I have below query executable in SQL Server 2005

CREATE INDEX Index_Name ON mytable(col1 ASC) INCLUDE (name,id);

What is the equivalent for the above query in SQL server 2000

Thanks ,
Sushma

View 1 Replies View Related

Transact SQL :: How To Build Dynamic WHERE Clause In Openquery To Linked Database

Jul 17, 2015

I'd like to modify the dates within this where clause to be dynamic, building the date depending on the current year, but everything I try doesn't seem to be syntactically correct.

SELECT *
FROM Openquery(LS_CIS, 'select * from BI_WRKFLW_TASKS where (BI_EVENT_DT_TM>=''1/1/2011'' and (BI_NEEDED_DT_TM>=''1/1/2011''))OR (BI_EVENT_DT_TM>=''1/1/2011'' and BI_NEEDED_DT_TM is null)') AS derivedtbl_1
I'd like to replace ''1/1/2011''  in the where clause with something like:
CAST(CAST(YEAR (GETDATE())-4 AS varchar) + '-' + CAST(01 AS varchar) + '-' + CAST(01 AS varchar) AS DATETIME)

View 9 Replies View Related

Transact SQL :: Case In Where Clause With Column Comparison With Greater Than Or No Filter

Sep 24, 2015

For Below example when @x=1 to retrieve col>0 rows or all rows.

With out another if else blocks or Dynamic sql to solve only in where clause.

select 0 col into #x
union
select 1 col
union
select 2 col

declare @x INT =1
SELECT * FROM #x
where col>CASE WHEN @x=1 THEN 0 ELSE (col=col) END

--here in case i want to compare only when @x=1 then col>0 other wise select all rows with out filter

View 5 Replies View Related







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