MS SQL Server :: I Need Help With This Tsql Statement

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys,
I have the query below (running okay):

Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01

The results are just as I need:

Field01           Field02

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

192473           8461760

192474           22810

Because other reasons. I need to modify that query to:

Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

Field02

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

22810
8461760

And what I need is (without showing any other field):

Field02

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

8461760
22810

Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View Replies !

TSQL - WITH Statement
Hi guys,
I need help with this one...
Iam Trying to understand how to use the statement WITH
I am running the code below, but getting error.

note: I have SQL SERVER 2005 in my PC, but retrieving data from the SQL SERVER 2000 (in the server)

Thanks in advance,
Aldo.

Code Snippet
WITH MyCTE (FILTER, SORTGROUP)
AS
(
SELECT ACCOUNTS.FILTER, ACCOUNTS.SORTGROUP FROM ACCOUNTS
)
SELECT * FROM MyCTE AS CTE_01;

Error Messages:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.

View Replies !

How Can I Use Variables In This TSQL Statement
Hi all,I would like to replace the default directory location (c: emp) and thefilename (emails.csv) with variables like @FileDir and @FileName in thestatement below.SELECT @cnt = COUNT(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft TextDriver (*.txt; *.csv)}; DefaultDir=c: emp;','select * from "emails.csv"')However, my attempts have not been successful.Any ideas appreciated, and TIA.Greg

View Replies !

TSQL From An Access SQL Statement
Good morning one and all,

I have some queries that were written in access that I need to port into SQL 7, the whole process is boring and mundane. Does any1 know of a translator (i.e. access sql to t-sql) or a reference to the differences between access SQL and t-Sql.

Any and all help appreciated,

Thanx Gurmi

View Replies !

What Is Wrong With This TSQL Statement

hello,

I have wriiten this query to select all records from those tables in a database that have "to_be_transffered" column where this "to_be_transferred_column" is not null

--code

EXEC sp_MSForEachTable 'IF EXISTS(SELECT c.table_name, c.column_name

                                                         FROM information_schema.columns c

                                                                   INNER JOIN information_schema.tables t

                                                                   ON c.table_name = t.table_name

                                                         WHERE c.table_name = SUBSTRING(''?'', (CHARINDEX(''['',''?'', 2) + 1),

                                                                                                                  ((LEN(''?'') - (CHARINDEX(''['',''?'', 2) + 1))))

                                                                      AND c.column_name = ''to_be_transferred''

                                                                      AND t.table_type = ''BASE TABLE''

                                                         )

                                                        BEGIN

                                                                  SELECT * FROM ?

                                                                  WHERE to_be_transferred IS NOT NULL

                                                          END'

But I am getting the following error

Msg 207, Level 16, State 3, Line 12

Invalid column name 'to_be_transferred'

I thought the if statement filters all those tables with the specified column and do select statement fot those tables only

what did I do wrong, any suggestionm?

Thanks

View Replies !

Tsql Case Statement
Hi,
Here is the scenario. I want to add last year sale dollars in accordance with current period in exsiting fact table.
And below is the syntax.

Syntax:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select
         a. store_key,
        a.fisc_date_key,
         sum(a.net_sale_Dollars) as sale_TY ,
         sum ( b.net_sale_dollars ) as sale_LY ,
         a.division_name,
         a.department_number

fromFact 1 as a

,Fact 1 as b

Whereb.fisc_date_key = (a.fisc_date_key -364)
and a.division_name=b.division_name
and a.department_number =b.department_number
and a.store_key = b.store_key

group by
             a.division_name,
             a.department_number,
            a.fisc_date_key,
              a.store_key

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

The current table from this query is showing like:

store_key date_key   sale_TY   sales_LY div         dept
------------------------------------------------------------------------------------------------
1                1                30           20           ABC      1
2                1                20           20           ABC       3

But, if we assume that in the current date, dept = 2 has a sale amount, and in parallel year if dept=2 does not have any sale then this information was excluded.

The structure of table that I want to create must look like:

store_key date_key   sale_TY   sales_LY div       dept
------------------------------------------------------------------------------------------------
1               1                30           20           ABC      1
2                1                20           20           ABC       3
2                 1                15           0             ABC       2

>>>> want to put 0 value where only one side ( current or parrallel period) has sales info.

So, I'm thinking the case statement like:

Case statement logic like:
------------------------------------------------------------
if a. dept not exist in b.dept
then Sale TY -> a.net_sale_dollars
       Sale LY -> 0

if b.dept not exist in a.dept
then sale TY -> 0
       sale LY -> b. net_sale_dollars
-------------------------------------------------------------

below is the syntax which doesn't work (it's wrong):

Syntax:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select
         a. store_key,
        a.fisc_date_key,
         sum(case when a.department_number = b.department_number then a.net_sale_dollars
                else case when a.department_number NOT IN (b.department_number)then a.net_sale_dollars else null end)
                as sale_TY ,
         sum ( case when b.department_number =a.department_number then b.net_sale_dollars

                  else case when a.department_number NOT IN (b.department_number) as sale_LY,
         a.division_name,
         a.department_number

fromFact 1 as a

Fact 1 as b

Whereb.fisc_date_key = (a.fisc_date_key -364)
and  a.division_name=b.division_name
and a.department_number =b.department_number
and a.store_key = b.store_key

group by
             a.division_name,
             a.department_number,
            a.fisc_date_key,
              a.store_key

,
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Is it possible to create these kind of structure?
Please give me some comments.
Thanks.

View Replies !

TSQL: Conditional Union Statement
Is it possible to have a conditional union statement in a stored proc?Here's an example on the northwind database. If says there's a syntaxerror near the UNION statement. Looks like it doesn't like having theBEGIN directly in front of it.Is the only solution to create a dynamic sql string then call exec onit?Any help appreciated.Tom.CREATE PROCEDURE usp_test(@both int = 1)ASSET NOCOUNT ONSELECT * FROM territories WHERE regionid = 1IF @both = 1BEGINUNIONSELECT * FROM territories WHERE regionid = 2ENDGO

View Replies !

TSQL: I Want To Use A SELECT Statement With COUNT(*) AS 'name' And ORDER BY 'name'
I am very new to Transact-SQL programming and don't have a programmingbackground and was hoping that someone could point me in the rightdirection. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIsand want to ORDER BY 'IOI's'. I have been combing through the BOL, butI don't even know what topic/heading this would fall under.USE INDIISELECT FIXID, COUNT(*) AS IOIsFROM[dbo].[IOI_2005_03_03]GROUP BY FIXIDORDER BY FIXIDI know that it is a simple question, but perhaps someone could assistme.Thanks,

View Replies !

Help With TSQL Statement To Check For Table Size
I have to deal with an environment where the developers create and modify tables at will, and the tool they use does not check if the table created is over the 8060 limit for a table. I have a sql statement below which checks the size for me, but I can't figure out how to put a where clause on it to check for the size being over 8060. I i have over 1000 tables to check and was going to write a cursor routine to check it and print it, but I need te where clause to work.Any help appreciated

select Name=left(c.name,20),
c.prec, scale = ISNULL(c.scale,0),
t.name from syscolumns c inner join systypes
t on c.xtype = t.xtype where id in
(select id from sysobjects where name = 'mh_demographic2_')
order by c.colid COMPUTE sum(c.prec)

View Replies !

TSQL Statement For Adding Employee Numbers To A Table
What is the easiest way to fill a column with sequencial numbers. The name of the column is empno an is 4 characters long. I would like to start with '0001' and continue to add 1 to the empno until the end of the table.

Thanks for your help in advance.

View Replies !

Is There A TSQL Statement To Delete File Like Xp_delete_file For SQL2000
I know there is a command xp_delete_file for SQL 2005 but if I want to delete a file from a 2000 database is there a command to do the same.

View Replies !

How To Free Memory Used By Prior Query Statement Within A Batch By TSQL?
Just Like these:

-- batch start
Select * from someTable --maybe a query which need much res(I/O,cpu,memory)

/*
can I do something here to free res used by prior statement?
*/

select * from someOtherTable
--batch end

The Sqls above are written in a procedure to automating test for some select querys.

View Replies !

Whqt Is The TSQL Statement To Make A Backup Of A Specific Databese??
i have a database names "students" in SQL server 2000. is there any TSQL statement to make a backup of the student databse in to a sspecific location ??????????

pls help

View Replies !

Is There A TSQL Statement For Importing A Singular Table Into Sql Server 2005 From Access?
Hello all.

I was wondering if there was a simple Import statement I could use in SQL to import an Access Table into SQL Server 2005.

I know how to use the SSIS Import/Export Wizard, but that seems excessive to import a single 204 record table

Any help on this would be greatly appreciated.

View Replies !

Undo A &"delete Tbl_test&" TSQL Statement
I accidentaly deleted data from the wrong table. Is there a way to un-delete what I had deleted.

need help ASAP.

Regards,
Steve

View Replies !

TSQL Statement Extracting Data From One Table Through Another Table
Hi,

I have 2 tables,
MembersTemp and Organisations

I'm trying to extract the organisation Name from the organisations table but am unsure of the sql statement to do this.

Initiallt I only have the ExecID for the MembersTemp table

MembersType table:
ExecID 3013
OrganisationID 4550

Organisation table:
ID 4550 (PK)
Name "Microboff"

Any ideas??

View Replies !

Equivalent Tsql For Sql Server 2000 Is Needed [from Sql Server 2005 Only Tsql]
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005

1
-- Full Table Structure

select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id

2
-- PK and Index
select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id

This sql is extracting some sort of the information about the structure of the sql server database[2005]
I need a sql whihc will return the same result for sql server 2000

View Replies !

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian

View Replies !

Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View Replies !

TSQL Or SQL CLR?
Hello Friends, I am not sure if this is the right place to post this question but if not please suggest me when it can be posted.
I have been thinking of writing Stored procs in SQL CLR/ changing all of my Stored Procs to SQL CLR.
Is there any thing that I need to keep in mind about the size of the sotred proc (like calculation intensive) before I do that? I mean can even change a TSQL stored proc which is relatively small Stored Proc, that simply said Select * from Customers to SQL CLR? or the SQL CLR does only useful/makes difference with calculation intensive stored procs and big stored procs?
When I talked to our Architects they said every small sized stored proc can be written using SQL CLR and more over forget about the classic TSQL stored procs and get used to write SQL CLR when ever writing any database related stuff.
And also there are so many articles that discussed about the advantages of SQL CLR over the TSQL but I would appreciate if some one could put few bulletted points why do you think SQL CLR is more powerful.
Please advise.Thanks in advance,-L

View Replies !

TSQL Please Help
Hi
I am creating some dynamic sql by passing variouse parametrs to my Stored Procedure. One of my Parameters is string has lots of values seperated by a comma to help build an 'IN' statement.
SET @SQL = 'SELECT * FROM areas'SET @p1 = '10,20'If @p1 IS NOT NULLBEGINSET @sSQL = @sSQL + ' WHERE (Areas IN (''' + Replace(@p1,',',''',''') + '''))'END
The above query runs perfecly well in Query Analyser, however when I put it into my ASP.NET application I get an error of "Error converting data type varchar to numeric."
So I think I need to do some sort of casting or Converting but im not sure how to do it. Or do I need to use a INSTRING?
I did manage to work out a method by using the follwoing
SELECT * FROM Areas WHERE PATINDEX('%,' + CAST(ArType AS VARCHAR) + ',%',',' + @p1 + ',') > 0
But I cant seem to convert the above line into coherent dynamic statement. My feeble attempt is below but I keep getting errors
SET @sql = @sql + ' WHERE PATINDEX(''%,'' + CAST(ArType AS VARCHAR) + '',%'','','' + @p1 + '','') > 0'
IM strugging to understand all the '''. My TSQL is pretty basic, any help would be much appreciated
Many thanks in advance

View Replies !

TSQL Help
A stored procedure was running slowly so I took the code, removed thesubselect and included a join, then took the max and included as partof a correlated subquery.The result is below, however, this is no improvement over the original.An advice would be greatly appreciated.SELECT FSALT.FUNDING_LINE_TYPE_ID,A.PAYMENT_PERIOD_ID,A.CASH AS CASH,A.VOLUME AS VOLUMEFROM ACTUALS AINNER JOIN (SELECT MAX(COLLECTION_PAYMENT_PERIOD_*ID) AS CPP FROMACTUALS ACT WHERE COLLECTION_PAYMENT_PERIOD_ID<=*456) AS O ON O.CPP =A.COLLECTION_PAYMENT_PERIOD_IDINNER JOIN FS_ACTUAL_LINE_TYPES FSALT ON FSALT.FS_ACTUAL_LINE_TYPE_ID =A.FS_ACTUAL_LINE_TYPE_IDINNER JOIN PAYMENT_PERIODS PP ON PP.PAYMENT_PERIOD_ID =A.PAYMENT_PERIOD_IDWHEREA.ORG_ID=24771AND A.LSC_ORG_ID=5816AND PP.FUNDING_STREAM_ID=5AND PP.FUNDING_PERIOD_ID=6GROUP BYFSALT.FUNDING_LINE_TYPE_ID,A.PAYMENT_PERIOD_ID, A.CASH, A.VOLUME

View Replies !

TSQL Help
I need help with a query that I want to incorporate into a scheduled DTS within SQL Server 2000. I have a process that brings in records from an Excel file, compares that day's records with any related information already stored in the database, then saves a table of joined records. This leaves me with any matches to the data. I need to filter out all but the most current record, which would be identified by a column named [DATE]. Ex:

Table tblMatchedRecords

M BLah Blah2 [DATE]
1 this that 20040101
2 this them 20040102
3 this that 20040630

In this example, I would like to have the record with [DATE] = 20040630 kept, and the others deleted. All columns do not always have to match exactly. I want to be able to specify which fields that need to match and pull only the latest date.

Make sense? Can anyone offer any suggestions?

View Replies !

Tsql Help
I have a few variables declared

@amount=100000 (any big int)
@pamount=100000
@a1,@a2 - thorugh @25 -- each a bigint
@p1,@p2,@p3
@id - int

if @id is an odd number I need to randomly change @amount-@a1 (randomly to @a1 - @a25 so it ends up being any of 25 values)
and then @pamount randomly= to either @p1,@p2 or @p3

Can someone help me do the sql for this?
it will be within my stored procedure

View Replies !

TSQL Help...please
I am trying to determine concurrency of a program running based on start and stop times. Right now I have 8863 records to compare, but here is a small sample.

Start Time Finish Time
1)2003-12-01 00:31:12.0002003-12-01 01:14:23.000
2)2003-12-01 06:55:43.0002003-12-01 15:50:47.000
3)2003-12-01 07:19:12.0002003-12-01 16:30:06.000
4)2003-12-01 07:22:10.0002003-12-01 15:56:44.000
5)2003-12-01 07:27:46.0002003-12-01 18:36:05.000
6)2003-12-01 07:34:53.0002003-12-01 09:57:15.000

I need to compare the times for overlap to determine concurrency. For instance:
Comparing record 1 to record 2:
If record1.starttime <= record2.finishtime AND record1.finishtime >= record2.starttime, then count = 1, esle 0
If record1.starttime <= record3.finishtime AND record1.finishtime >= record3.starttime, then count = 1, else 0
AND SO ON AND SO ON...
when you encounter a 0, SUM the count as Concurrent.

Then, I need it to move to record to and do the same thing...

Comparing record 2to record 1:
If record2.starttime <= record1.finishtime AND record2.finishtime >= record1.starttime, then count = 1, esle 0
Comparing record2 to record 3:
If record2.starttime <= record3.finishtime AND record2.finishtime >= record3.starttime, then count = 1, else 0
AND SO ON AND SO ON...
when you encounter a 0, SUM the count as Concurrent.

Finally, when the loop (or cursor??) is finished, select MAX(count) as maximum concurrency.

I tend to stay more on the system side and less on programming so I am really out of my realm. Any help polishing off the logic, and some pointers on how to write this in T-SQL would be much appreciated.

Thanks in advance.

Ryan Hunt

View Replies !

TSQL Help
Hi,

I am trying to break up a column "name" into 2 columns "first name" and "last name". The name colum is currently "lastname, firstname". Is there an easy way to do this in SQL 6.5?

I successfully extracted the first name using:

firstname = rtrim(substring(name, (charindex(',', name)), 25))

But I am having trouble doing the lastname. Please help.

Thanks so much!
Laura

View Replies !

TSQL ?
I am using the following statement to do an extract for a data security matrix. I can get the select to work,
but not the insert . Any help appreciated.

set nocount on
declare @dbname varchar(30)
declare @sqlperm varchar(50)
declare @objname varchar (50)
declare @secgroup varchar (50)
declare @printline varchar(80)
declare @sql varchar(255)
declare @sql1 varchar(255)

if @dbname is NULL
begin
declare dbcursor cursor
for select name from master..sysdatabases
where name not in ('master','msdb','pubs','model','northwinds')

open dbcursor
fetch next from dbcursor into @dbname
while (@@FETCH_STATUS <> -1)

begin
select @dbname, case spt.action
when 26 then "REFERENCES " when 193 then "SELECT " when 195 then "INSERT " when 196 then "DELETE " when 197 then "UPDATE " when 224 then "EXECUTE " end
, so.name
, (select su.name from ois..sysusers su where su.uid = spt.uid) from ois..sysobjects so join ois..sysprotects spt on so.id = spt.id where so.type in ("P","U","V")
select @sqlperm = case spt.action
Select @sqlobject = so.name
select @sqlgroup - su.name
insert into bsrdict..tbl_sql_sec
values
(@dbname @sqlperm, @sqlobject,@sqlgroup)

fetch next from dbcursor into @dbname
end
close dbcursor
deallocate dbcursor
end

View Replies !

TSQL
I'm new in T-SQL...

Is there way I can do loops in SQL?
I’m trying to do something like:
"for each a.description in account_codes a where clientId in(1,0)
select b.balance from account_balance b".....

Right now I'm trying to execute this:
"select a.description,b.balance
from
account_balance b, account_codes a
where
a.clientid in (1,0)"

the result that I get is:
description balance
-------------------- ---------------------
A income 123.1200
A income 235.1200
B income 123.1200
B income 235.1200

but what I want to see is:
description balance
-------------------- ---------------------
A income 123.1200
B income 235.1200

Any help will be greatly appreciated...
Thank you,
Nastya.

View Replies !

Tsql Help
Hi,

I want to write a query which selects ind and
clcode where
ind(individual) has taken all 3 classes(clcode) 15, 16 and 17.
for example: it should only return data for ind 1 from the following data as only ind 1 has taken all three classes.
How can I write it?

table: ind_history

ind clcode
----------- -----------
1 16
1 17
2 17
1 15
2 16
3 11
4 15

View Replies !

Tsql Help!
simple update, I want to update max_seq with max(0rdr_seq) from another table.
how do you?

update h
set max_seq = d.max(ordr_seq)
from h_drug_stage_dup h
join drug_ordr_stage d
on h.patkey = d.patkey and
h.ordr_dtm= d.ordr_dtm and
h.h_drug = d.h_Drug

View Replies !

TSQL Help !
How do I say example, adding three business days with TSQL ?

Help is greatly appreciated.

Thanks,
Jeff

View Replies !

TSQL Help !
Please see below ( in my sub-query I need to say settle_date = post_date +
3 business days )
How would this be done ? Pleas help !!!

declare @PD datetime, @MY_SD datetime
--SELECT @PD = SELECT POST_DATE FROM TRANSACTION_HISTORY
--select @MY_SD = @PD + 3 --T+3
--select @MY_SD = @MY_SD + case datepart(dw, @MY_SD) when 7 then 2 when 1 then 1 else 0 end*/
SELECT
WIRE_ORDER_NUMBER FROM TRANSACTION_HISTORY
WHERE POST_DATE BETWEEN '02/01/2001' AND '02/28/2001' AND
WIRE_ORDER_NUMBER IN
(
SELECT ORDER_NUMBER
FROM TRANSACTION_ARCHIVE WHERE TRANSACTION_ARCHIVE.ORDER_NUMBER = TRANSACTION_HISTORY.WIRE_ORDER_NUMBER
SETTLE_DATE = DATEADD(day, 3, POST_DATE ) case datepart(dw, POST_DATE) when 7 then 2 when 1 then 1 else 0 end))

View Replies !

Tsql
Sql Server 7.0
==============
Hi all!
To find out the duplicate entries in a particular column,
I used the following tsql

select pno ,count(pno) from table1 group by pno
having count(pno)>1

But now I have another case where i have to test duplicity as a combination of 3 columns.
ie, for Eg :I have 3 columns with the following values.

colA colB colC
1 2 3===============row 1
1 3 5===============row 2
1 2 3===============row 3
1 4 5===============row 4
8 9 0===============row 5

I want to pick up all the duplicate rows(combo of colA,colB,colC)
duplicate rows here would be row 1 and row 3.

Can somebody give me a clue as to how to achieve this via TSQL.
Any help greatly appreciated.

TIA
Kinnu

View Replies !

TSQL
HI,
I am creating a stored procedure where i need to update a table.
I have a field which has year as 2000-2001 i need to remove -2001
and just store it as 2000.I created a Procedure but i am getting an
error.

create PROCEDURE prdUpdate_Year
@year varchar(40)
As
@year = Mid(Newfiels,1,4)
UPDATE AddressBook SET AddressBook.Year = @year;
GO

Thanks

View Replies !

How Can This Be Done Using TSQL?
I have a table that has a datetime field that needs a calculated time difference created when querying the table. I'm new to TSQL and I've been banging my head on this one. There is a time window that must be dealt with as well, so that any time that falls outside if the time window gets assigned a zero value (not calculated). The last record inside the window and the first record inside the window get calculated based on a given start and end time. The time outside the window is 7PM to 7AM. Here's an example:

in/outside window    Time Value                   Calculation              Time Diff (in minutes)
inside window         time1 = '13:15:00'           null                     the first record is always null
inside window         time2 = '14:15:00'        time2-time1                         60
inside window          time3 = '18:50:00'        time3-time2                         275
outside window        time4 = '19:10:00'        18:59:59 - time3                  10
outside window        time5 = '21:00:00'          0                                      0
outside window       time6 = '06:30:00'           0                                      0
outsidw window       time7 = '06:45:00'            0                                     0
inside window         time8 = '07:45:00'        time8 - 07:00:00                   15

How can this be done using TSQL? Let me know if more info is needed. Thanks!

View Replies !

TSQL - Using WHERE
Hi guys,
The query below is running ok.

Code Block
USE test2006mdt

if object_id('AuxTable20071224132300') is not null exec('DROP TABLE AuxTable20071224132300')

SELECT DISTINCT
Stock.ACCOUNTKEY AS 'Stock.ACCOUNTKEY',
Stock.DOCNUMBER AS 'Stock.DOCNUMBER',
Stock.DOCUMENTID AS 'Stock.DOCUMENTID',
CASE WHEN Cast(Stock.DOCUMENTID as int) In ('1','35') THEN 'IN# ' + Cast(Stock.DOCNUMBER as varchar) WHEN Cast(Stock.DOCUMENTID as int) In ('3','120') THEN 'CM# ' + Cast(Stock.DOCNUMBER as varchar) ELSE '' END AS 'Invoice #',
Stock.VALUEDATE AS 'Stock.VALUEDATE',
Stock.TFTAL AS 'Stock.TFTAL',
Stock.CURRENCY AS 'Stock.CURRENCY',
CASE WHEN ReceiptJurnalMatch.SUF Is NULL THEN '0' ELSE -1 * ReceiptJurnalMatch.SUF END AS 'ReceiptJurnalMatch.SUF',
Stock.ID AS 'Stock.ID',
CASE WHEN ReceiptJurnalMatch.FULLMATCH Is NULL THEN '0' ELSE ReceiptJurnalMatch.FULLMATCH END AS 'ReceiptJurnalMatch.FULLMATCH'

INTO AuxTable20071224132300
FROM
RECEIPTJURNALMATCH
RIGHT OUTER JOIN JURNALTRANSMOVES ON RECEIPTJURNALMATCH.JURNALTRANSID = JURNALTRANSMOVES.ID
RIGHT OUTER JOIN STOCK
INNER JOIN ACCOUNTS ON STOCK.ACCOUNTKEY = ACCOUNTS.ACCOUNTKEY
INNER JOIN JURNALTRANS ON STOCK.ID = JURNALTRANS.STOCKID ON JURNALTRANSMOVES.TRANSID = JURNALTRANS.TRANSID
LEFT OUTER JOIN STOCKMOVES ON STOCK.ID = STOCKMOVES.STOCKID

WHERE
Stock.ACCOUNTKEY Between 'Account01' AND 'Account01'
AND Stock.Status Not In ('0','2')
AND Stock.DOCUMENTID In ('1','35','3','120')
ORDER BY Stock.ACCOUNTKEY

SELECT DISTINCT
[Stock.ACCOUNTKEY] AS 'Stock.ACCOUNTKEY',
[Stock.DOCNUMBER] AS 'Stock.DOCNUMBER',
[Stock.DOCUMENTID] AS 'Stock.DOCUMENTID',
[Invoice #] AS 'Invoice #',
[Stock.VALUEDATE] AS 'Stock.VALUEDATE',
[Stock.TFTAL] AS 'Stock.TFTAL',
[Stock.CURRENCY] AS 'Stock.CURRENCY',
Sum([ReceiptJurnalMatch.SUF]) AS 'Sum([ReceiptJurnalMatch.SUF])',
CASE WHEN CAST(DATEDIFF(dd,[Stock.VALUEDATE],GETDATE()) AS int) <= 30 THEN [Stock.TFTAL] - Sum([ReceiptJurnalMatch.SUF]) ELSE '' END AS '1-30',
CASE WHEN CAST(DATEDIFF(dd,[Stock.VALUEDATE],GETDATE()) AS int) BETWEEN 31 AND 60 THEN [Stock.TFTAL] - Sum([ReceiptJurnalMatch.SUF]) ELSE '' END AS '31-60',
CASE WHEN CAST(DATEDIFF(dd,[Stock.VALUEDATE],GETDATE()) AS int) BETWEEN 61 AND 90 THEN [Stock.TFTAL] - Sum([ReceiptJurnalMatch.SUF]) ELSE '' END AS '61-90',
CASE WHEN CAST(DATEDIFF(dd,[Stock.VALUEDATE],GETDATE()) AS int) > 90 THEN [Stock.TFTAL] - Sum([ReceiptJurnalMatch.SUF]) ELSE '' END AS '>> 90',
[Stock.TFTAL] - Sum([ReceiptJurnalMatch.SUF]) AS 'Balance'

FROM AuxTable20071224132300

WHERE Balance <> 0 ==> Getting ERROR!!!

GROUP BY [Stock.ACCOUNTKEY], [Stock.DOCNUMBER], [Stock.DOCUMENTID], [Invoice #], [Stock.VALUEDATE], [Stock.CURRENCY], [Stock.TFTAL]
The results are:

ACCOUNTKEY DOCNUMBER DOCUMENTID   Invoice #       VALUEDATE                  TFTAL CURRENCY SUF 1-30   31-60 61-90   >> 90   Balance

Account01        16917                 35                    IN# 16917   2007-12-25 00:00:00.000 15000 Euro            15000 0        0          0          0           0

Account01        16918                 35                    IN# 16918   2007-12-25 00:00:00.000 300     Euro             300     0        0          0          0           0

Account01        16919                 35                    IN# 16919   2007-12-25 00:00:00.000 110     Euro             110     0        0          0          0           0

Account01        16920                 35                    IN# 16920   2007-11-10 00:00:00.000 5         Euro             5         0        0          0          0           0

I inted to add a WHERE clause in order to Show Only Invoices with Balance <> 0, but getting Error.
Thanks in advance for any help.
Note: I am working under SQL SERVER 2000
Aldo.

View Replies !

Tsql
Hi,

Knows anybody solution for this problem:

http://blog.vyvojar.cz/dotnet/archive/2007/10/05/226475.aspx

Thx a lot

View Replies !

SP Or TSQL
Hi
Is there any sp or command to deactivate and activate all constraints in a database...

Thanks in Advance

RKNAIR

View Replies !

Tsql Help
I have 2 sql server that are registered and linked.

If I have a table in server 1 that I want to make a backup of to server 2, how would I do that in t-sqlv(if the table did not already exisit on the second server)

Thanks

"Impossible is Nothing"

View Replies !

How To Write Select Statement Inside CASE Statement ?
Hello friends,
      I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
    CASE E.EmployeeType
        WHEN 1 THEN
            select * from Tbl1
        WHEN 2 THEN
            select * from Tbl2
        WHEN 3 THEN
            select * from Tbl3
    END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View Replies !

[OT?] TSQL Function
Hi,
I need some pointers, I am trying to create a SQL function which will check to see if a column in a table will allow null values to be inserted.
I've tried searching the net but to no avail.
Kind Regards

View Replies !

Recursive Tsql
i have a table like this
parentid |    childid    |   description
1             2                     blah
1              3
1              4
2              23
2              24
5              8
3              10
and i want to give the parentid 1 and get all the children
i have a cursor now like this but i dont know how to make it recursive any help?

1
2 DECLARE @childid nvarchar(50)
3 DECLARE ItemStruc CURSOR FOR
4
5 SELECT cmponent_prt_no , parent_part_no
6 FROM oauser_prod_structure
7 WHERE parent_part_no = @parentid
8
9 OPEN ItemStruc
10 FETCH NEXT FROM ItemStruc
11 INTO @childNum , @parentid
12
13 WHILE @@FETCH_STATUS = 0
14 BEGIN
15
16 print @childNum +'is the a child to: ' +@parentid

View Replies !

TSQL Question
Hi. I have three table. Payments, Members, MemberPayments
Payments Columns: PaymentID, Year (Payments are annual)
Members: MemberID, MembershipDate
MemberPayments: MemberID, PaymentID
MemberPayments hold only payments that is paid like:
User1 PaymentID1User1 PaymentID2User2 PaymentID1
I want to find payments that is not paid by users. For example user1's and user2's membership date year is 2006. User2 hasn't paid 2007 payment (PaymentID2) yet. I want a query that will find it but i'm not good at tsql. Do you help? Thanks in advance.

View Replies !

Tsql Loop
I have 2 tables, i am trying to write a query that will loop through table1, select 3 fields and populate table 2. All fields are nvarchar, using Sql2000...

View Replies !

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.DESCRIPTIONOrder By 'QTR' asc

View Replies !

TSQL Problems
Hello,

This is my SP:

CREATE PROCEDURE GetRoles
@user_id int

AS

DECLARE @group_id int

SET @group_id = (SELECT user_group_id FROM users WHERE user_id = @user_id)

SELECT group_name
FROM groups
WHERE group_id = @group_id
GO

I'm expecting it to return the name of the group that the user belongs to and here is the function that I use it in:

public static string get_group(string user_id)
{
SqlConnection dbConnection = new SqlConnection();

dbConnection.ConnectionString =
ConfigurationSettings.AppSettings["myconstring"].ToString();
SqlCommand dbCmd = dbConnection.CreateCommand();
SqlDataReader dr = null;
dbCmd.CommandText = "EXECUTE GetRoles @user_id";
dbCmd.Parameters.Add("@user_id", SqlDbType.Int);
dbCmd.Parameters["@user_id"].Value = Convert.ToInt32(user_id);
string results = null;
dbConnection.Open();
dr = dbCmd.ExecuteReader();
results = dr.GetString(0).ToString();
dbConnection.Close();

return results;
}

Everytime I try running this function I get an error that says nothing could be read because the reader has no data.

View Replies !