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


ADVERTISEMENT

SQL 2012 :: Output Clause With Update

Sep 26, 2014

I need to update the account_status and show the audit in the ssrs report.

I wrote the following query in the ssrs report but when I click on refresh fields it is throwing error incorrect syntax near output

Declare @username nvarchar(50)
Declare @UserStatus Table
(id bigint,
Username nvarchar(50),
Oldaccount_status varchar(255),
Newaccount_status varchar(255))

[Code] .....

View 2 Replies View Related

Transact SQL :: Usage Of OUTPUT Clause Depending On Temporary Table

Jul 14, 2015

Suppose we have the following table in our database;

CREATE TABLE [dbo].[PERMISSION](
[ID] [int] IDENTITY(1,1) NOT NULL,
[USERID] [int] NOT NULL,
[STARTTIME] [smalldatetime] NOT NULL,
[ENDTIME] [smalldatetime] NOT NULL,
[REASON] [nvarchar](250) NULL,
[PERMISSIONTYPEID] [int] NOT NULL,

[code]....

This code works pretty well. But I don't want to do this with "select" since there is OUTPUT clause in T-SQL. So the CommandText property will be changed into this;

command.CommandText = @"insert PERMISSION
output INSERTED.ID, INSERTED.CREATETIME into @outID, @outCREATETIME
values(2, getdate(), getdate(), 'sdfg', 1, DEFAULT);";

well, not only this statement gives an error while executing; but also, no such usage defined in the

documentation of OUTPUT Clause. Actually the documentation tell us to use Temporary Tables for that. So I have to change CommandText into this;
command.CommandText = @"DECLARE @MyTableVar table(ID int, CREATETIME smalldatetime);
insert PERMISSION
output INSERTED.ID, INSERTED.CREATETIME into @MyTableVar

code]....

No temporary tables required; thus, no "type spesific" things required. This way is easier to create dynamic queries though. Only,the RETURNING INTO clause.So, I was wondering; why MS-SQL (T-SQL) forces users to "declare a temporary table (type specific)" and "execute select on the temporary table in order to assign values to output parameters" while using "the OUTPUT Clause". Comparing to the Oracle's sample I'm just using "the RETURNING INTO Clause in order to assign values to output parameters" and that's it; very easy and dynamic. To Summarize, Oracle's RETURNING INTO Clause is better than MS-SQL's OUTPUT Clause, but in fact they're doing the same job.

View 7 Replies View Related

Recovery :: UPDATE Table And OUTPUT Clause To Capture Some Of Columns

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 5 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 :: 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 View Related

Transact SQL :: Generic Store Procedure Call Without Output Parameters But Catching Output

Sep 21, 2015

Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])

EX1 : @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 : @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : @SPToCall : [sp_GetSomeResult]

I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working

DECLARE @myFinalVarFilledWithCachedOutput 
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'

[code]...

View 3 Replies View Related

OUTPUT Clause

Nov 7, 2007

Hi,

I'm copying records from table A to Table B. On "insert" being successful I want to delete the copied records from table A. I want to use one single transaction. Could I use OUTPUT to delete all records in table A which key matches the ones on "inserted" ?. I believe it could be possible but I have tried several ways without any luck.

INSERT INTO Table A (Field 1, Field 2)
OUTPUT (DELETE Table B WHERE Key = (SELECT Key FROM Inserted))
SELECT (Field1, Field 2) FROM TABLE B

Or something like that,

Thank you

View 1 Replies View Related

Question On Output Clause !

Feb 29, 2008

I would like to add more columns on output clause while insert....

Following are the table formats used here ...

Table1 (col1, col2)
Table2 (col1, col2, col3, col4)
MyTable (C1,C2,C3,C4,C5,C6)

Insert into Table1 (col1, col2)
Output Inserted.col1, inserted.col2,MyTable.C3, MyTable.C4 into Table2
select C1,C2 from MyTable

Now the Output statement retrives only the Inserted.col1, inserted.col2 values, But I want to add MyTable.C3, MyTable.C4 values from the table <MyTable>

Is there any way to achieve this....

Thanks...

View 1 Replies View Related

Question About OUTPUT Clause

Nov 14, 2006

In the past when inserting a record into a table with an identity column, to return the new identity value I used one of SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY.

Question: will this sql 2005 approach also provide the newly added identity value?

insert into TestTable -- ID column of testtable is an Identity field

output inserted.id, inserted.col1 into @insertedRecords

values('row 20')



select ID from @insertedRecords





TIA,



Barkingdog



.

View 3 Replies View Related

Question On Output Clause !

Feb 29, 2008

I would like to add more columns on output clause while insert....

Following are the table formats used here ...

Table1 (col1, col2)
Table2 (col1, col2, col3, col4)
MyTable (C1,C2,C3,C4,C5,C6)

Insert into Table1 (col1, col2)
Output Inserted.col1, inserted.col2,MyTable.C3, MyTable.C4 into Table2

select C1,C2 from MyTable

Now the Output statement retrives only the Inserted.col1, inserted.col2 values, But I want to add MyTable.C3, MyTable.C4 values from the table <MyTable>


Is there any way to achieve this....

Thanks...

View 3 Replies View Related

Can A Value Be Assigned To An Output Parameter And Be Used In A Where Clause?

Dec 18, 2006

I need to update a table using a stored procedure that "UPSERTS". In this case, @OfficeId is passed with ParameterDirection.OutputSo when I update I assign the value to OfficeId like this (the office id value is being correctly assigned): SqlParameter OfficeIdParam = new SqlParameter("@OfficeId", SqlDbType.Int, 4);            if (_officeId > 0) { OfficeIdParam.Value = _officeId; }            OfficeIdParam.Direction = ParameterDirection.Output;   The following segment of the stored procedure is throwing an error that the OfficeId does not exist: Update --- @OfficeId int  output if not exists (select 1 from ORG_Office where OfficeId=@OfficeId)    begin        RAISERROR ('OfficeId does not exist in ORG_Office: E002', 16, 1) -- OfficeId does not exist.        return -1    end May this approach work, and if the operation turns out to be an Insert I may do this:select @error_code = @@ERROR, @OfficeId= scope_identity() Thank you,jspurlin 

View 1 Replies View Related

T-SQL (SS2K8) :: Get Output Of Procedure And Assign It To A Variable Used In WHERE Clause?

Mar 25, 2014

Get output of SQL Procedure and assign it to a variable used in WHERE Clause

Later I want to use this variable in my code in the WHERE Clause

Declare @ProjectNo nvarchar(10)

--Now I want to assign it to output of a storedprocedure which returns only 1 value and use it in the below SELECT query.

SELECT ID from TABLEA where Project in (@ProjectNo)

How to do it. How to assign @ProjectNo to output of storedProcedure called 'GetProjNumber'

View 1 Replies View Related

SQL Server 2012 :: OUTPUT Clause Returning Wrong Row?

Nov 6, 2014

I'm looking at various methods for deleting duplicate rows. Among the alternatives, one works just fine but gives me results that make me go?.

Consider this script:

declare @t table (a int, b int, c int, d int, e int)
insert into @t (a, b, c, d, e) values
(1, 2, 3, 4, 5),
(3, 4, 2, 3, 4),
(1, 2, 3, 4, 5)

select a,b,c,d,e, rn = row_number() over (
partition by a,b,c,d,e

[Code] ....

The code works -- that is, the duplicate row is deleted. However the output clause returns:

abcdern
123451

So....why? Why does the output clause show that the row with rn=1 was deleted, when the where clause stipulates rn > 1?

View 9 Replies View Related

OUTPUT @count =no Of Records Returnd By Dynamic Where Clause

Aug 18, 2005

Arvind writes "i want to create a stored procedure returns an OUTPUT variable containing the no. of records given by a query, the query being dynamic.
Preferrably the query should also be passed as a parameter to the stored procedure...If not,it should be constructed in the SP and a Part of the where clause is dependant on the value of another variable passed to the SP.

How should the query be constructed, executed, and then the Count(*) value returned?

"WHERE <condition1> AND <condition 2> ;


"AND <condition 2> " may exist or may not exist in the query; it is dependant."

View 1 Replies View Related

Error While Using OUTPUT Clause - The Multi-part Identifier Could Not Be Bound

Jun 2, 2006

I was trying to copy child records of one parent record into another, and wanted to report back new child record id and corresponding child record id that was used to create it. I ran into run-time error with OUTPUT clause. Following is a script that will duplicate the situation I ran into:
 
CREATE TABLE Parent(
      ParentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
      ParentName VARCHAR(50) NOT NULL)
GO
 
CREATE TABLE Child(
      ChildID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
      ParentID INT NOT NULL REFERENCES Parent(ParentID),
      ChildName VARCHAR(50) NOT NULL)
GO
 
INSERT INTO Parent(ParentName) VALUES('Parent 1')
INSERT INTO Parent(ParentName) VALUES('Parent 2')
GO
 
INSERT INTO Child(ParentID, ChildName) VALUES(1, 'Child 1')
INSERT INTO Child(ParentID, ChildName) VALUES(1, 'Child 2')
GO
 
At this stage, there Child table looks like:
 




ChildID

ParentID

ChildName


1

1

Child 1


2

1

Child 2
 
What I want to do is copy Parent 1€™s children to Parent 2, and report back which source ChildID that was used to create the new child records. So I wrote the query:
 
DECLARE @LinkTable TABLE (FromChildID INT, ToChildID INT)
 
INSERT INTO Child(ParentID, ChildName)
OUTPUT c.ChildID, inserted.ChildID INTO @LinkTable
      SELECT 2, c.ChildName
      FROM Child c
      WHERE c.ParentID = 1
 
SELECT * FROM @LinkTable
 
In the end I was expecting Child table to look like:
 




ChildID

ParentID

ChildName


1

1

Child 1


2

1

Child 2


3

2

Child 1


4

2

Child 2
 
and OUTPUT clause to return me:
 




FromChildID

ToChildID

 


1

3

Child record with ID 3 was created using ID of 1.


2

4

Child record with ID 4 was created using ID of 2.
 
 
But infact I€™m getting following error:
 
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "c.ChildID" could not be bound.
 
Any ideas on how to fix the OUTPUT clause in the query to return me the expected output?
 
Thanks
Yogesh

View 7 Replies View Related

Mapping A String Field To Boolean Output In SELECT Clause

Oct 18, 2006

Hello,
I am facing a problem in a SELECT clause which i cannot solve.
In my SQL table ("myTable") i have a few columns ("Column1", "Column2", "TypeColumn"). When I select different columns of the table, instead of getting the value of TypeColumn, i would like to get a boolean indicating whether its value is a certain string or not.
For example, the TypeColumn accepts only a number of selected strings: "AAA", "BBB", "CCC".
when i do a select query on the table, instead of asking for TypeColumn i would like to ask a boolean value of 1 if TypeColumn is "AAA" and 0 if TypeColumn is "BBB" or "CCC". Also, i would like to make this query while I am also fetching the other columns. And i would like to use one query to get all that. I thought something like thsi would work:

SELECT Column1 AS Col1, Column2 AS Col2, IF(TypeColumn = "AAA", 1, 0) AS Col3
FROM myTable

but this doesn't work in SQL 2005!
Is it possible to do something similar in SQL 2005 using one query only? i am trying to avoid multiple queries for this.

thanks a lot for your help!

View 3 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 :: 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

Transact SQL :: Firing After Update Trigger - On Table Row Update

Jul 8, 2015

I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
 
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),

I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.

I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?

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

Transact SQL :: FOR XML Output To A File

Feb 19, 2013

I am using SELECT FOR XML which is working great.My problem is writing the results out to the filesystem.I am using spWriteStringToFile procedure that uses Scripting.FileSystemObject to write the file.The file gets written and all of the "xml" is there, but there are no CR/LFs and parsers, browsers and validators don't like it.What can I do to get a more usable output file?

View 11 Replies View Related

Transact SQL :: How To Get Output Through Email

Jul 2, 2015

I have one PS script; Basically it reads all application logs and give a report. Here is the PS script:- 

$logs='C:AppLog*.log'
$ufo=Get-Date -ufo '%Y-%m-%d' # Unidentified Flying Object
$match="^$ufo.+error.+$"
sls $match $logs|
    group filename -noe|%{
        [pscustomobject]@{Name=$_.name;Date=$ufo;'#Errors'=$_.count}

[code]..

Now I would like to setup a SQL job and want to get the result in email.

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







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