Need suggestion on TSql Query with Joins

Hi All,

Please suggest me is there any performance/other differences between the below two queries.

-query1

select T1.name,T1.Number, T2.Dept, T2.Desig

From T1 Inner Join T2 on T1.EID = T2.EID

-query2

select T1.name,T1.Number, T2.Dept, T2.Desig

From T1 Inner Join (Select Dept, Desig From T2) As T2 on T1.EID = T2.EID

Thanks

Senthil

There is no performance difference for your quires.

Both are executed in same way..

Suppose, if your query2, subquery has any distinct or group by clause then the query1 may perform better than query2.

|||Thanks Sekaran!

need some TSQL help

I have some entries in a column as such
XYZ ( ABC ) ( 1)
XYZ ( ABC ) ( 11)
XYZ ( ABC ) ( 2)
XYZ ( ABC ) ( 3)
XYZ ( ABC ) ( 4)
UVW ( XYZ ) ( 10)
UVW ( XYZ ) ( 12)
UVW ( XYZ ) ( 41)
So i want to group entries like this by the first ')' seen
so I can get distinct values such as
XYZ ( ABC )
UVW ( XYZ )
How can i write the query ? Thankscreate table t (
s varchar(40)
)
go
insert into t values ('XYZ ( ABC ) ( 1)')
insert into t values ('XYZ ( ABC ) ( 11)')
insert into t values ('XYZ ( ABC ) ( 2)')
insert into t values ('XYZ ( ABC ) ( 3)')
insert into t values ('XYZ ( ABC ) ( 4)')
insert into t values ('UVW ( XYZ ) ( 10)')
insert into t values ('UVW ( XYZ ) ( 12)')
insert into t values ('UVW ( XYZ ) ( 41)')
go
select distinct
substring(s,1,charindex(')',s)) as s
from t
where charindex(')',s) > 0
go
drop table t
Steve Kass
Drew University
Hassan wrote:

>I have some entries in a column as such
>XYZ ( ABC ) ( 1)
>XYZ ( ABC ) ( 11)
>XYZ ( ABC ) ( 2)
>XYZ ( ABC ) ( 3)
>XYZ ( ABC ) ( 4)
>UVW ( XYZ ) ( 10)
>UVW ( XYZ ) ( 12)
>UVW ( XYZ ) ( 41)
>So i want to group entries like this by the first ')' seen
>so I can get distinct values such as
>XYZ ( ABC )
>UVW ( XYZ )
>How can i write the query ? Thanks
>
>|||or
select substring(s,1,charindex(')',s)) as s
from #t
where charindex(')',s) > 0
group by substring(s,1,charindex(')',s))
Madhivanan

Need script to loop through all non-system databases and drop all user schemas - Almost ther

Does anybody have any tsql code that will loop through all non-system databases on a SQL Server 2005 instance and drop all the user schemas in each database? Thanks.

you could get the DB's by using:

select * from master.dbo.sysdatabases where dbid > 4

You could then loop through the objects with:

SELECt CASE WHEN xtype = 'P' THEN 'DROP PROC ' + name

WHEN xtype = 'U' THEN 'DROP TABLE ' + name

END

from sysobjects

where xtype in( 'P','U')

Order by xtype, name

Depending on the objects you could also add functions..views..etc

|||Perhaps you might want to check the objects for not being system ones:

where xtype in( 'P','U')

AND OBJECTPROPERTY(OBJECT_ID(name),'IsMSShipped') = 0

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I use the below script to clean up the objects in a database. The script will fail if you have schemabound dependencies between objects.

For e.g. if there is a schemabound view that is dependent on a table, dropping the table before dropping the view will fail and will be report by an appropriate error message. You will need to manually drop such objects.

DECLARE @.sqlcommand VARCHAR(max), @.delimiter CHAR, @.ErrorMessage NVARCHAR(4000)
SET @.delimiter = ';'
begin try
begin transaction

SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP '+
CASE type
WHEN 'AF' THEN N'AGGREGATE'
WHEN 'P' THEN N'PROCEDURE'
WHEN 'PC' THEN N'PROCEDURE'
WHEN 'FN' THEN N'FUNCTION'
WHEN 'FS' THEN N'FUNCTION'
WHEN 'FT' THEN N'FUNCTION'
WHEN 'R' THEN N'RULE'
WHEN 'RF' THEN N'PROCEDURE'
WHEN 'SN' THEN N'SYNONYM'
WHEN 'IF' THEN N'FUNCTION'
WHEN 'TF' THEN N'FUNCTION'
WHEN 'U' THEN N'TABLE'
WHEN 'V' THEN N'VIEW'
ELSE N'INVALID'
END
+' '+SCHEMA_NAME(schema_id)+'.'+name
FROM sys.objects
WHERE type not in ('C','D','F','PK','UQ','X','S','IT','TR','TA','R','SQ') --Not dropping constraints,triggers,service queues

exec (@.sqlcommand);

commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch

set @.sqlcommand=null;

--Now lets drop the rules and defaults
begin try
begin transaction

SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP '+
CASE type
WHEN 'R' THEN N'RULE'
WHEN 'D' THEN N'DEFAULT'
ELSE N'INVALID'
END
+' '+SCHEMA_NAME(schema_id)+'.'+name
FROM sys.objects
WHERE type not in ('C','F','PK','UQ','X','S','IT','TR','TA','SQ')

exec (@.sqlcommand);

commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch
set @.sqlcommand=null;

--Now lets drop the types
begin try
begin transaction

SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP TYPE '+SCHEMA_NAME(schema_id)+'.'+name
FROM sys.types
WHERE system_type_id <> user_type_id and name <> 'sysname'--only drops user defined types

exec (@.sqlcommand);

commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch
set @.sqlcommand=null;

--Now lets drop the schemas
begin try
begin transaction
SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP SCHEMA '+name
FROM sys.schemas where schema_id > 4 and schema_id < 16384

exec (@.sqlcommand);
commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch

set @.sqlcommand=null;

|||

We are on track. Basically I am trying to do something like this:

create table #dbs (dbid int IDENTITY(1,1), dbname nvarchar(128))

insert into #dbs (dbname)

select name from master.dbo.sysdatabases where dbid > 4

select * from #dbs

DECLARE @.sqlcommand VARCHAR(max),

@.useCommand varchar(max),

@.delimiter CHAR,

@.dbname nvarchar(128),

@.idx int

SET @.delimiter = ';'

select @.idx = min(dbid) from #dbs

while @.idx is not null

begin

select @.dbname = dbname from #dbs where dbid = @.idx

select @.useCommand = 'USE ' + @.dbname + @.delimiter

select @.useCommand

SELECT @.sqlcommand = COALESCE(quotename(@.sqlcommand), quotename(@.delimiter), ' ') + 'DROP SCHEMA ' + name

FROM quotename(@.dbname)+'.sys.schemas where schema_id > 4 and schema_id < 16384'+''''

select @.sqlcommand

--exec(@.sqlcommand)

select @.idx = min(dbid) from #dbs where dbid > @.idx

select @.sqlcommand

end

My code is just not working because of syntax errors. I think I am very close though, maybe one you SQL experts out there can show me my mistake and help me out. Thanks for all your help thus far!

|||Maybe this isn't even possible, if its not please let me know so I stop spinning my wheels and trying to figure this one out.|||This can get really complicated because you cannot drop the schemas without first dropping the objects in that schema. Is it too much to run the script manually per database ?|||

The script is for a migration from a SQL Server 2000 databse to SQL Server 2005. All of the objects are owned by dbo. The schemas are automatically created for each user as part of the migration from SQL Server 2000 to 2005. We are just doing a back up and restore of the database to migrate it. I need to drop all the schemas in each database before I can drop and re add all the users. Everything needs to be scripted and automated so it can be tested before running on production.

I have come up with this thus far:

/* Drop schemas */

create table #dbs (dbid int IDENTITY(1,1), dbname nvarchar(128))

insert into #dbs (dbname)

select name from master.dbo.sysdatabases where dbid > 4

create table #schemas (schemaid int identity(1,1), dbname nvarchar(max), schemaname nvarchar(max))

/* Change the object owner for these objects temporarily so we can drop the schema */

select @.idx = min(dbid) from #dbs

while @.idx is not null

begin

select @.dbname = dbname from #dbs where dbid = @.idx

select @.sql = 'INSERT INTO #schemas (dbname,schemaname) '

select @.sql = @.sql + 'Select ' +''''+@.dbname+'''' + ', name From ' + quotename(@.dbname) + '.sys.schemas where schema_id > 4 and schema_id < 16384'

Exec(@.sql)

select @.idx = min(dbid) from #dbs where dbid > @.idx

end

select @.idx = min(schemaid) from #schemas

while @.idx is not null

begin

select @.sql = ' USE ' + quotename(dbname) + ' DROP SCHEMA ' + schemaname + ';' from #schemas where schemaid = @.idx

Exec(@.sql)

select @.idx = min(schemaid) from #schemas where schemaid > @.idx

end

Need script to loop through all non-system databases and drop all user schemas

Does anybody have any tsql code that will loop through all non-system databases on a SQL Server 2005 instance and drop all the user schemas in each database? Thanks.

you could get the DB's by using:

select * from master.dbo.sysdatabases where dbid > 4

You could then loop through the objects with:

SELECt CASE WHEN xtype = 'P' THEN 'DROP PROC ' + name

WHEN xtype = 'U' THEN 'DROP TABLE ' + name

END

from sysobjects

where xtype in( 'P','U')

Order by xtype, name

Depending on the objects you could also add functions..views..etc

|||Perhaps you might want to check the objects for not being system ones:

where xtype in( 'P','U')

AND OBJECTPROPERTY(OBJECT_ID(name),'IsMSShipped') = 0

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I use the below script to clean up the objects in a database. The script will fail if you have schemabound dependencies between objects.

For e.g. if there is a schemabound view that is dependent on a table, dropping the table before dropping the view will fail and will be report by an appropriate error message. You will need to manually drop such objects.

DECLARE @.sqlcommand VARCHAR(max), @.delimiter CHAR, @.ErrorMessage NVARCHAR(4000)
SET @.delimiter = ';'
begin try
begin transaction

SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP '+
CASE type
WHEN 'AF' THEN N'AGGREGATE'
WHEN 'P' THEN N'PROCEDURE'
WHEN 'PC' THEN N'PROCEDURE'
WHEN 'FN' THEN N'FUNCTION'
WHEN 'FS' THEN N'FUNCTION'
WHEN 'FT' THEN N'FUNCTION'
WHEN 'R' THEN N'RULE'
WHEN 'RF' THEN N'PROCEDURE'
WHEN 'SN' THEN N'SYNONYM'
WHEN 'IF' THEN N'FUNCTION'
WHEN 'TF' THEN N'FUNCTION'
WHEN 'U' THEN N'TABLE'
WHEN 'V' THEN N'VIEW'
ELSE N'INVALID'
END
+' '+SCHEMA_NAME(schema_id)+'.'+name
FROM sys.objects
WHERE type not in ('C','D','F','PK','UQ','X','S','IT','TR','TA','R','SQ') --Not dropping constraints,triggers,service queues

exec (@.sqlcommand);

commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch

set @.sqlcommand=null;

--Now lets drop the rules and defaults
begin try
begin transaction

SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP '+
CASE type
WHEN 'R' THEN N'RULE'
WHEN 'D' THEN N'DEFAULT'
ELSE N'INVALID'
END
+' '+SCHEMA_NAME(schema_id)+'.'+name
FROM sys.objects
WHERE type not in ('C','F','PK','UQ','X','S','IT','TR','TA','SQ')

exec (@.sqlcommand);

commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch
set @.sqlcommand=null;

--Now lets drop the types
begin try
begin transaction

SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP TYPE '+SCHEMA_NAME(schema_id)+'.'+name
FROM sys.types
WHERE system_type_id <> user_type_id and name <> 'sysname'--only drops user defined types

exec (@.sqlcommand);

commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch
set @.sqlcommand=null;

--Now lets drop the schemas
begin try
begin transaction
SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP SCHEMA '+name
FROM sys.schemas where schema_id > 4 and schema_id < 16384

exec (@.sqlcommand);
commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch

set @.sqlcommand=null;

|||

We are on track. Basically I am trying to do something like this:

create table #dbs (dbid int IDENTITY(1,1), dbname nvarchar(128))

insert into #dbs (dbname)

select name from master.dbo.sysdatabases where dbid > 4

select * from #dbs

DECLARE @.sqlcommand VARCHAR(max),

@.useCommand varchar(max),

@.delimiter CHAR,

@.dbname nvarchar(128),

@.idx int

SET @.delimiter = ';'

select @.idx = min(dbid) from #dbs

while @.idx is not null

begin

select @.dbname = dbname from #dbs where dbid = @.idx

select @.useCommand = 'USE ' + @.dbname + @.delimiter

select @.useCommand

SELECT @.sqlcommand = COALESCE(quotename(@.sqlcommand), quotename(@.delimiter), ' ') + 'DROP SCHEMA ' + name

FROM quotename(@.dbname)+'.sys.schemas where schema_id > 4 and schema_id < 16384'+''''

select @.sqlcommand

--exec(@.sqlcommand)

select @.idx = min(dbid) from #dbs where dbid > @.idx

select @.sqlcommand

end

My code is just not working because of syntax errors. I think I am very close though, maybe one you SQL experts out there can show me my mistake and help me out. Thanks for all your help thus far!

|||Maybe this isn't even possible, if its not please let me know so I stop spinning my wheels and trying to figure this one out.|||This can get really complicated because you cannot drop the schemas without first dropping the objects in that schema. Is it too much to run the script manually per database ?|||

The script is for a migration from a SQL Server 2000 databse to SQL Server 2005. All of the objects are owned by dbo. The schemas are automatically created for each user as part of the migration from SQL Server 2000 to 2005. We are just doing a back up and restore of the database to migrate it. I need to drop all the schemas in each database before I can drop and re add all the users. Everything needs to be scripted and automated so it can be tested before running on production.

I have come up with this thus far:

/* Drop schemas */

create table #dbs (dbid int IDENTITY(1,1), dbname nvarchar(128))

insert into #dbs (dbname)

select name from master.dbo.sysdatabases where dbid > 4

create table #schemas (schemaid int identity(1,1), dbname nvarchar(max), schemaname nvarchar(max))

/* Change the object owner for these objects temporarily so we can drop the schema */

select @.idx = min(dbid) from #dbs

while @.idx is not null

begin

select @.dbname = dbname from #dbs where dbid = @.idx

select @.sql = 'INSERT INTO #schemas (dbname,schemaname) '

select @.sql = @.sql + 'Select ' +''''+@.dbname+'''' + ', name From ' + quotename(@.dbname) + '.sys.schemas where schema_id > 4 and schema_id < 16384'

Exec(@.sql)

select @.idx = min(dbid) from #dbs where dbid > @.idx

end

select @.idx = min(schemaid) from #schemas

while @.idx is not null

begin

select @.sql = ' USE ' + quotename(dbname) + ' DROP SCHEMA ' + schemaname + ';' from #schemas where schemaid = @.idx

Exec(@.sql)

select @.idx = min(schemaid) from #schemas where schemaid > @.idx

end

Need help!!!! (SP or SSIS Task)

I have written the Dynamic TSQL S-Proc. Below is what i wanted to implement in SSIS using foreach loop container as a cursor. But i am little doubtful whether I can achieve the dynamics to this level. I know everything is possible but is it advisable to go for this simple Sproc or SSIS tasks.

I have some 15 tables being populated using this SPROC.

Here is some helpful description

ENTITYNAME gives me the table i need to work

FIELDNAME gives me the field i have to work on

CHANGEDVALUE gives me the value changed in that field

( This three i get from source table which is about 9000 rows and containing 15 possible ENTITY to be work on and 100's of their respective FIELD )

while in Cursors i need to get using these above variables other variables like

FLAG

KeyName

Thrugh SQL1 I get the KeyValue

then using this KeyValue check if the data exist update else insert new data.

QUESTION: IS THIS ADVISABLE to go for SSIS task or just carry with SPROC?

/*******************************************************************************************************/

DECLARE Table_Cursor CURSOR
FOR SELECT ENTITYNAME,FIELDNAME, KEYID, CHANGEDVALUE, UPDATEUSER, UPDATEDATE
FROM dbo.ChangedDimensionStage

OPEN Table_cursor

FETCH NEXT FROM Table_cursor INTO @.ENTITY, @.FIELD, @.KEYID, @.VALUE, @.USER, @.DATE

WHILE @.@.FETCH_STATUS = 0

BEGIN

DECLARE @.FLAG NVARCHAR(50);
SET @.FLAG = (SELECT LEFT(@.ENTITY, (SELECT CHARINDEX( 'DIM', @.ENTITY) -1)) )+ 'LastUpdateFlag';

DECLARE @.KeyName NVARCHAR(50);
SET @.KeyName = (SELECT LEFT(@.ENTITY, (SELECT CHARINDEX( 'DIM', @.ENTITY) -1)) )+ 'Key'

DECLARE @.KeyValue NVARCHAR(50)

DECLARE @.SQL1 NVARCHAR (1000)

SET @.SQL1 = N'Select @.KeyValueOUT = '+ @.KeyName + ' FROM DW_Integration.dbo.MangFact WHERE ClaKey = ' + @.KEYID + ' GROUP BY ' + @.KeyName + ' HAVING SUM(TotalClaCount) > 0 OR SUM(IncidentOnlyClaCount) > 0 '

EXECUTE sp_executesql @.SQL1, N'@.KeyValueOUT INT OUTPUT', @.KeyValue OUTPUT;

DECLARE @.WC_TABLE NVARCHAR(100)
SET @.WC_TABLE = 'WorkingCopy' + @.ENTITY

DECLARE @.SQL2 nvarchar (1000);
SET @.SQL2 = 'IF EXISTS (SELECT '+ @.KeyName +' FROM ' + @.WC_TABLE + ' WHERE ' + @.KeyName + ' = ' + @.KeyValue + ' )' +
' BEGIN UPDATE ' + @.WC_TABLE + ' SET '+ @.FIELD + ' = '''+ @.VALUE + ''' WHERE ' + @.KeyName + ' = ' + @.KeyValue +'; END' +
' ELSE BEGIN
INSERT INTO '+ @.WC_TABLE + ' SELECT * FROM DW_Integration.dbo.' + @.ENTITY + ' WHERE ' + @.Flag + ' = ' + '''Y''' + ' AND '+ @.KeyName + ' = ' + @.KeyValue + ';' +
'UPDATE ' + @.WC_TABLE + ' SET '+ @.FIELD + ' = '''+ @.VALUE + ''' WHERE ' + @.KeyName + ' = ' + @.KeyValue +'; END'

EXECUTE sp_executesql @.SQL2

FETCH NEXT FROM Table_cursor INTO @.ENTITY, @.FIELD, @.KEYID, @.VALUE, @.USER, @.DATE

END

CLOSE Table_cursor
DEALLOCATE Table_cursor

This is not a striaghtforward question, and there is no straightforward answer either.

The stored proc you have provided is dreadfully inefficient, and could be done much more efficiently. But that may not be an issue with the volumes of data you are working with.

An SSIS package is another approach, but do you need to re-develop this work? What is the justification for doing so? Do you need to be able to easily update the procedure? Perhaps using external configuration? In this case, an SSIS reimplementation may be justified. Do you need to hand it over to someone else to maintain? Then again, SSIS may be useful, especially if the maintainers have little experience with T-SQL.

Nobody here is going to say to you to do it one way or another way. I would ask the person who is paying for the job what it is they are after.

Need help with TSQL syntax and JOIN.

Hey all,
I'm having problems with a TSQL query, and I know someone out there can
help.
Note the following:
--
CREATE TABLE #temp
( ID int,
ParentID int,
Text varchar(50)
)
INSERT INTO #temp (ID, ParentID, Text) VALUES (1,null,'Top')
INSERT INTO #temp (ID, ParentID, Text) VALUES (2,1,'Middle')
INSERT INTO #temp (ID, ParentID, Text) VALUES (3,2,'Bottom')
SELECT t1.Text + ':' + t2.Text + ':' + t3.Text
FROM #temp t1
INNER JOIN #temp t2 ON t1.ID = t2.ParentID
INNER JOIN #temp t3 ON t2.ID = t3.ParentID
DROP TABLE #temp
--
This temp table allows me to define a series of Text values, and change them
together within a Parent-Child relationship. The select returns
"Top:Middle:Bottom", which is exactly what I want.
But, let's pretend that I have 5 more levels. to get the select statement
to work, I'd have to have 5 more inner joins. Let's say I had X more
levels -- how am I to know how many levels to continue to add to my SQL
statement?
Hopefully you can see the problem.
I want to write a SELECT statement that accomplishes the same thing (i.e.
concatenating the TEXT together for X number of parent-child relationships),
but without having to explicitly account for every possible level.
Does anyone know how I can accomplish this?
Thank you very much for your help!
WadeFigured out a way:
use tempdb
CREATE TABLE temptbl
( ID int,
ParentID int,
Text varchar(50)
)
GO
CREATE FUNCTION f_categoryPath
( @.iID int,
@.sText varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @.sRetval varchar(8000),
@.sSlash char(1),
@.t_id int,
@.t_parentid int,
@.t_text varchar(50)
SET @.sSlash = ''
SET @.sRetval = ''
SELECT @.t_id = ID,
@.t_parentid = ParentID,
@.t_text = Text
FROM temptbl
WHERE ID = @.iID
set @.sRetVal = @.sSlash + @.t_text + @.sText
if @.t_parentid is not null
begin
set @.sRetval = dbo.f_categoryPath(@.t_parentid,@.sRetVal)
end
return @.sRetval
END
GO
INSERT INTO temptbl (ID, ParentID, Text) VALUES (1,null,'Top')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (2,1,'Middle')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (3,2,'Bottom1')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (4,3,'Bottom2')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (5,4,'Bottom3')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (6,5,'Bottom4')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (7,6,'Bottom5')
SELECT dbo.f_categoryPath(7,'')
DROP TABLE temptbl
DROP FUNCTION f_categoryPath
--
thanks anyway!
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:epq8QZpIGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hey all,
> I'm having problems with a TSQL query, and I know someone out there can
> help.
> Note the following:
> --
> CREATE TABLE #temp
> ( ID int,
> ParentID int,
> Text varchar(50)
> )
> INSERT INTO #temp (ID, ParentID, Text) VALUES (1,null,'Top')
> INSERT INTO #temp (ID, ParentID, Text) VALUES (2,1,'Middle')
> INSERT INTO #temp (ID, ParentID, Text) VALUES (3,2,'Bottom')
> SELECT t1.Text + ':' + t2.Text + ':' + t3.Text
> FROM #temp t1
> INNER JOIN #temp t2 ON t1.ID = t2.ParentID
> INNER JOIN #temp t3 ON t2.ID = t3.ParentID
> DROP TABLE #temp
> --
> This temp table allows me to define a series of Text values, and change
> them together within a Parent-Child relationship. The select returns
> "Top:Middle:Bottom", which is exactly what I want.
> But, let's pretend that I have 5 more levels. to get the select statement
> to work, I'd have to have 5 more inner joins. Let's say I had X more
> levels -- how am I to know how many levels to continue to add to my SQL
> statement?
> Hopefully you can see the problem.
> I want to write a SELECT statement that accomplishes the same thing (i.e.
> concatenating the TEXT together for X number of parent-child
> relationships), but without having to explicitly account for every
> possible level.
> Does anyone know how I can accomplish this?
> Thank you very much for your help!
> Wade
>|||Have you gotten a copy of TREES & HIERARCHIES IN SQL yet? There are
several other ways to do this without procedural code at all.

Need help with tsql script

I have a t sql script that works but i need to modify it to show the prvious years info can someone show me how to do this below is the code I have and it show this between defined dates I need it to show the yearbefore also

SELECT 'Quarter All' as 'qtr',
COUNT(JOB.JOBID) as 'transcount',
COUNT(DISTINCT JOB.PATIENTID) as 'patient count',
SUM(JOB.LANGUAGE_TCOST) as 'lcost',
SUM(JOB.LANGUAGE_DISC_COST) as 'dlcost',
AVG(JOB.LANGUAGE_DISC) as 'avgLDisc',
SUM(JOB.LANGUAGE_TCOST) + SUM(JOB.LANGUAGE_DISC_COST) as 'LGrossAmtBilled',
SUM(JOB.LANGUAGE_TCOST) / COUNT(DISTINCT JOB.PATIENTID) as 'PatAvgL',
SUM(JOB.LANGUAGE_TCOST) / COUNT(JOB.JOBID) as 'RefAvgL',
SUM(JOB.LANGUAGE_DISC) as 'avgPercentDiscL',
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
JOB.LANGUAGE_ID,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE AS EXPR1,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
LANGUAGES.DESCRIPTION

FROM JOB
INNER JOIN INVOICE_AR
ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER
ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES
ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN LANGUAGES
ON JOB.LANGUAGE_ID = LANGUAGES.DESCRIPTION

WHERE
(INVOICE_AR.AMOUNT_DUE > 0)
AND
(INVOICE_AR.INVOICE_DATE BETWEEN @.startdate and @.enddate)
AND
(MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3,4,5,6,7,8,9,10,11,12))
AND
(PAYER.PAY_COMPANY like '%' + @.Company + '%')


Group By
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
JOB.LANGUAGE_ID,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
LANGUAGES.DESCRIPTION
Order By 'QTR' asc

How do want to diplay the "Year before also"?You just run it for the previous year by changing the input dates,You could alias the table so you could select twice and join the second selection change (INVOICE_AR.INVOICE_DATE BETWEEN @.startdate and @.enddate)to (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD(year, -1, @.startdate) and DATEADD(year, -1, @.enddate)and you can select two periods at once!|||How do want to diplay the "Year before also"?You just run it for the previous year by changing the input dates,You could alias the table so you could select twice and join the second selection change (INVOICE_AR.INVOICE_DATE BETWEEN @.startdate and @.enddate)to (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD(year, -1, @.startdate) and DATEADD(year, -1, @.enddate)and you can select two periods at once!|||How do want to diplay the "Year before also"?You just run it for the previous year by changing the input dates,You could alias the table so you could select twice and join the second selection change (INVOICE_AR.INVOICE_DATE BETWEEN @.startdate and @.enddate)to (INVOICE_AR.INVOICE_DATE BETWEEN DATEADD(year, -1, @.startdate) and DATEADD(year, -1, @.enddate)and you can select two periods at once!|||

Try

SELECT 'Quarter All' as 'qtr',
COUNT(JOB.JOBID) as 'transcount',
COUNT(DISTINCT JOB.PATIENTID) as 'patient count',
SUM(JOB.LANGUAGE_TCOST) as 'lcost',
SUM(JOB.LANGUAGE_DISC_COST) as 'dlcost',
AVG(JOB.LANGUAGE_DISC) as 'avgLDisc',
SUM(JOB.LANGUAGE_TCOST) + SUM(JOB.LANGUAGE_DISC_COST) as 'LGrossAmtBilled',
SUM(JOB.LANGUAGE_TCOST) / COUNT(DISTINCT JOB.PATIENTID) as 'PatAvgL',
SUM(JOB.LANGUAGE_TCOST) / COUNT(JOB.JOBID) as 'RefAvgL',
SUM(JOB.LANGUAGE_DISC) as 'avgPercentDiscL',
JOB.JURISDICTION,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
JOB.LANGUAGE_ID,
I1.INVOICE_NO,
I1.INVOICE_DATE AS EXPR1,
I1.AMOUNT_DUE,
I1.CLAIMNUMBER,
I2.INVOICE_NO,
I2.INVOICE_DATE AS EXPR1,
I2.AMOUNT_DUE,
I2.CLAIMNUMBER,
LANGUAGES.DESCRIPTION
FROM JOB
INNER JOIN INVOICE_AR I1 ON JOB.JOBID = INVOICE_AR.JOBID
INNER JOIN INVOICE_AR I2 ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN LANGUAGES ON JOB.LANGUAGE_ID = LANGUAGES.DESCRIPTION
WHERE I1.AMOUNT_DUE > 0
AND (I1.INVOICE_DATE BETWEEN @.startdate and @.enddate)
AND (DATEADD(year, 1, I2.INVOICE_DATE) BETWEEN @.startdate and @.enddate)
AND (MONTH(I1.INVOICE_DATE) IN (1,2,3,4,5,6,7,8,9,10,11,12))
AND (PAYER.PAY_COMPANY like '%' + @.Company + '%')
Group By JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, JOB.JOBOUTCOMEID, JOB.SERVICEOUTCOME,
JOB.LANGUAGE_ID, I1.INVOICE_NO, I1.INVOICE_DATE, I1.AMOUNT_DUE,I1.CLAIMNUMBER, LANGUAGES.DESCRIPTION
Order By 'QTR' asc