MS SQL Server :: TSQL Questions

3 TSQL Questions.....Help Needed...

Oct 20, 2007

You are troubleshooting a T-SQL stored procedure with a developer using SQL Server 2005. There are several update transactions and calls to other stored procedures within the stored procedure, and you suspect one of them is failing. You don��t know which one. You don��t want to heavily modify the stored procedure with error detection. What can you do to easily trap the error?

A stored procedure is called from an outwardly facing web application. The stored procedure uses dynamic SQL to retrieve data from a table. Is this a ��safe�? thing to do? Why or why not?

A developer writes a T-SQL view that joins data from two tables. The code is as follows��

SELECT
C.contactName,
C.contactTelNo,
F.fundManagerName,
F.fundManagerTelNo
FROM
Contacts C, FundManagers F
WHERE
C.fundManagerId *= F.fundManagerId

When the view is executed, the developer notices differing result sets on a daily basis. Why?

View 20 Replies View Related

Very Basic Questions About Tsql Sprocs

Apr 2, 2008

Hi,

I really confused , I wanna get an rowid on sql 2000 table so I have created a sproc and it's syntax is OK
How can I check it on sql query analyzer? this sql server 2000
Also How can I use that in select statement?

thanks..

here is my select statement which I have to use sproc inside
select custid,ordernum,sku,amount,
dbo.get_rownums (custid,ordernum,sku ) ???
from tp_cod cod

here is my sproc:
CREATE PROCEDURE [dbo].[get_rownums] @custid as varchar(10),@ordernum as varchar(5),@sku as varchar(10) , @i as int output
AS
BEGIN
DECLARE @SkuID as varchar(10)
--DECLARE @i as int
DECLARE got_sku CURSOR FOR
Select sku from tp_cod where custid=@custid and ordernum=@ordernum
set nocount on
set @i=0
OPEN got_sku
FETCH NEXT FROM got_sku INTO @SkuID
WHILE @@FETCH_STATUS = 0
BEGIN
Set @i =@i + 1
if @SkuID=@sku
begin
return @i
end
else
begin FETCH NEXT FROM got_sku INTO @SkuID end
END
CLOSE got_sku
DEALLOCATE got_sku
END
GO

View 25 Replies View Related

Equivalent Tsql For Sql Server 2000 Is Needed [from Sql Server 2005 Only Tsql]

Nov 19, 2007

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 1 Replies View Related

SQL License Questions And Other Questions &&>&&>&&>&&>

Mar 3, 2006

1. Is it legal and OK to use a MSDN SQL copy on a production environment or is it strickly for test environments ??

2. If I own a legal copy of SQL 7 with 5 cals, can I legally use SQL MSDE and have more than 5 people access my SQL server or am I also limited to 5 users as my original ??

Sorry I am a newbie at this SQL thing.

View 1 Replies View Related

TSQL Or SQL CLR?

Aug 9, 2006

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 2 Replies View Related

TSQL Please Help

Jan 8, 2006

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 1 Replies View Related

Tsql Help!

Mar 14, 2001

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 1 Replies View Related

TSQL Help !

Jun 5, 2001

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

Tsql

Oct 1, 2001

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 1 Replies View Related

TSQL

Oct 1, 2001

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 2 Replies View Related

TSQL Help

Jun 5, 2000

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 3 Replies View Related

Tsql Help

Dec 6, 2000

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 1 Replies View Related

TSQL Help

Aug 4, 2004

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 4 Replies View Related

Tsql Help

May 9, 2007

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

SP Or TSQL

Nov 30, 2007

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

Thanks in Advance

RKNAIR

View 6 Replies View Related

Tsql Help

Mar 4, 2008

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 4 Replies View Related

TSQL Help

Aug 22, 2005

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 2 Replies View Related

How Can This Be Done Using TSQL?

Feb 22, 2008

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 8 Replies View Related

TSQL - Using WHERE

Dec 25, 2007

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 8 Replies View Related

Tsql

Oct 6, 2007

Hi,

Knows anybody solution for this problem:

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

Thx a lot

View 5 Replies View Related

I Need Help With This Tsql Statement

Apr 2, 2007

Every time I try this statement I keep getting a syntext error near count I must be over looking something can some one help me with this.

SELECT 'Quarter 1' as 'qtr'       count(jobid) as 'transcount',       count(distinct job.patientid) as 'patientcount',       sum(job.LANGUAGE_TCOST) as 'lcost',       Sum(job.LANGUAGE_DISC_COST) as 'dlcost',       avg(LANGUAGE_DISC) as 'avgLDisc',       (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled',       (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL',       (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL',       sum(LANGUAGE_DISC) as 'avgPercentDiscL',       JOB.JURISDICTION,       PAYER.PAY_COMPANY,       PAYER.PAY_CITY,       PAYER.PAY_STATE,       PAYER.PAY_SALES_STAFF_ID,       JOB.INVOICE_DATE       INVOICE_AR.INVOICE_DATE AS EXPR1,       INVOICE_AR.AMOUNT_DUE
FROM JOB        INNER JOIN INVOICE_AR                ON JOB.JOBID = INVOICE_AR.JOBID       LEFT OUTER JOIN PAYER                ON PAYER.PAYERID = JOB.PAYER.ID       LEFT OUTER JOIN STATES                ON JOB.JURISDICTION = STATES.INITIALS
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))AND         (PAYER.PAYCOMPANY like '%' + @Company + '%')                Group By        JOB.JURISDICTION        PAYER.PAY_COMPANY        PAYER.PAY_CITY        PAYER.PAY_STATE        PAYER.PAY_SALES_STAFF_ID,        JOB.INVOICE_DATE,        INVOICE_AR.INVOICE_DATE,        INVOICE_AR.AMOUNT_DUE
UNION ALL
SELECT 'Quarter 2' as 'qtr'       count(jobid) as 'transcount',       count(distinct job.patientid) as 'patientcount',       sum(job.LANGUAGE_TCOST) as 'lcost',       Sum(job.LANGUAGE_DISC_COST) as 'dlcost',       avg(LANGUAGE_DISC) as 'avgLDisc',       (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled',       (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL',       (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL',       sum(LANGUAGE_DISC) as 'avgPercentDiscL',       JOB.JURISDICTION,       PAYER.PAY_COMPANY,       PAYER.PAY_CITY,       PAYER.PAY_STATE,       PAYER.PAY_SALES_STAFF_ID,       JOB.INVOICE_DATE       INVOICE_AR.INVOICE_DATE AS EXPR1,       INVOICE_AR.AMOUNT_DUE
FROM JOB        INNER JOIN INVOICE_AR                ON JOB.JOBID = INVOICE_AR.JOBID       LEFT OUTER JOIN PAYER                ON PAYER.PAYERID = JOB.PAYER.ID       LEFT OUTER JOIN STATES                ON JOB.JURISDICTION = STATES.INITIALS
WHERE      (INVOICE_AR.AMOUNT_DUE > 0)AND       (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND         (MONTH(INVOICE_AR.INVOICE_DATE) IN (4,5,6))AND         (PAYER.PAYCOMPANY like '%' + @Company + '%')                Group By        JOB.JURISDICTION        PAYER.PAY_COMPANY        PAYER.PAY_CITY        PAYER.PAY_STATE        PAYER.PAY_SALES_STAFF_ID,        JOB.INVOICE_DATE,        INVOICE_AR.INVOICE_DATE,        INVOICE_AR.AMOUNT_DUE
UNION ALL
SELECT 'Quarter 3' as 'qtr'       count(jobid) as 'transcount',       count(distinct job.patientid) as 'patientcount',       sum(job.LANGUAGE_TCOST) as 'lcost',       Sum(job.LANGUAGE_DISC_COST) as 'dlcost',       avg(LANGUAGE_DISC) as 'avgLDisc',       (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled',       (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL',       (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL',       sum(LANGUAGE_DISC) as 'avgPercentDiscL',       JOB.JURISDICTION,       PAYER.PAY_COMPANY,       PAYER.PAY_CITY,       PAYER.PAY_STATE,       PAYER.PAY_SALES_STAFF_ID,       JOB.INVOICE_DATE       INVOICE_AR.INVOICE_DATE AS EXPR1,       INVOICE_AR.AMOUNT_DUE
FROM JOB        INNER JOIN INVOICE_AR                ON JOB.JOBID = INVOICE_AR.JOBID       LEFT OUTER JOIN PAYER                ON PAYER.PAYERID = JOB.PAYER.ID       LEFT OUTER JOIN STATES                ON JOB.JURISDICTION = STATES.INITIALS
WHERE      (INVOICE_AR.AMOUNT_DUE > 0)AND       (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND         (MONTH(INVOICE_AR.INVOICE_DATE) IN (7,8,9))AND         (PAYER.PAYCOMPANY like '%' + @Company + '%')                Group By        JOB.JURISDICTION        PAYER.PAY_COMPANY        PAYER.PAY_CITY        PAYER.PAY_STATE        PAYER.PAY_SALES_STAFF_ID,        JOB.INVOICE_DATE,        INVOICE_AR.INVOICE_DATE,        INVOICE_AR.AMOUNT_DUE
UNION ALL
SELECT 'Quarter 4' as 'qtr'       count(jobid) as 'transcount',       count(distinct job.patientid) as 'patientcount',       sum(job.LANGUAGE_TCOST) as 'lcost',       Sum(job.LANGUAGE_DISC_COST) as 'dlcost',       avg(LANGUAGE_DISC) as 'avgLDisc',       (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled',       (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL',       (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL',       sum(LANGUAGE_DISC) as 'avgPercentDiscL',       JOB.JURISDICTION,       PAYER.PAY_COMPANY,       PAYER.PAY_CITY,       PAYER.PAY_STATE,       PAYER.PAY_SALES_STAFF_ID,       JOB.INVOICE_DATE       INVOICE_AR.INVOICE_DATE AS EXPR1,       INVOICE_AR.AMOUNT_DUE
FROM JOB        INNER JOIN INVOICE_AR                ON JOB.JOBID = INVOICE_AR.JOBID       LEFT OUTER JOIN PAYER                ON PAYER.PAYERID = JOB.PAYER.ID       LEFT OUTER JOIN STATES                ON JOB.JURISDICTION = STATES.INITIALS
WHERE      (INVOICE_AR.AMOUNT_DUE > 0)AND       (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND         (MONTH(INVOICE_AR.INVOICE_DATE) IN (10,11,12))AND         (PAYER.PAYCOMPANY like '%' + @Company + '%')                Group By        JOB.JURISDICTION        PAYER.PAY_cOMPANY        PAYER.PAY_CITY        PAYER.PAY_STATE        PAYER.PAY_SALES_STAFF_ID,        JOB.INVOICE_DATE,        INVOICE_AR.INVOICE_DATE,        INVOICE_AR.AMOUNT_DUE         Order By 'QTR' asc

View 4 Replies View Related

Tsql Loop

Apr 16, 2007

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 2 Replies View Related

Need Help With Tsql Script

Apr 18, 2007

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 4 Replies View Related

TSQL Question

Jun 14, 2007

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 7 Replies View Related

Recursive Tsql

Feb 26, 2008

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 12 Replies View Related

[OT?] TSQL Function

May 9, 2008

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 2 Replies View Related

TSQL Query Help

Mar 2, 2004

Is there a better way to write this query?

SELECT t1.title,t1.record_id,
(SELECT SUM(t2.amount1) FROM table2 t2 WHERE t2.parent_id = t1.record_id)AS amt1,
(SELECT SUM(t2.amount2) FROM table2 t2 WHERE t2.parent_id = t1.record_id)AS amt2,
(SELECT SUM(t2.amount3) FROM table2 t2 WHERE t2.parent_id = t1.record_id)AS amt3
FROM table1 t1

View 13 Replies View Related

Please Help!! TSQL Problems!!

Mar 23, 2004

hi everyone,

although, this is not an asp.net problem but i really need your help!
For example, i had 3 tables (TableA, TableB, TableC)

(Relationship example)

(TableA) (TableB) (TableC)
ID 1-------- 1 ----1
TableAID --------ID -
TableCID ID----------

if i have a relationship same as the above, but the TableB only contain the value for TableA but no TableC. how can i generate out the data for TableB if a data is missing in one field.

(Sql statement) 'This is my sql statement that cannot output anydata from TableB
select * from TableB where
TableA.ID = TableB.TableAID And
TableC.ID = TableB.TableCID

Thanks everyone

View 5 Replies View Related

TSQL Question

Nov 22, 2004

Can anyone throw me an example of how to return rows from TableA and JOIN TableB but only join 1 row of TableB in the join?

In other words: There are mulitple rows in TableB related to TableA's rows. I only want 1 TableB row returned with each row TableA row.PSUEDOCODE:
SELECT a.name,a.phone,b.orderid,b.orderdate
FROM TableA a
INNER JOIN TableB b ON ?TOP 1? b.id = a.id

View 4 Replies View Related

TSQL Problems

Sep 2, 2005

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 3 Replies View Related

TSQL Question

Mar 27, 2001

In the SP below, I set the value of @resultcount by EXECuting a SQL string. When I try to use the value in @resultcount in the line after the EXEC statement, I get the error 'must declare @resultcount'. Why does the declare for the variable get wiped out after the EXEC and what can I do to get around this?

CREATE PROCEDURE CheckForEmpty
@tablename varchar(50)
AS
declare @resultcount integer
declare @sSql varchar(255)
--SP to check for rowcount in passed table name. Useful in DTS steps to stop load process if no rows in sending table.

set @sSql = 'set @resultcount = (select count(*) from ' + @tablename + ')'

exec (@sSql)

if @resultcount = 0 RETURN(-100) else return(0)

View 2 Replies View Related

TSQL ( Urgent )

May 11, 2001

Let's say a table with two columns as below
col1 col2
w101 1
w101 2
w102 4

How can I use above table and place records as below in another table
col1 col2
w101 1,2
w102 4

Thanks for your time.
Ray

View 2 Replies View Related