[OT?] TSQL Function
May 9, 2008Hi,
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
ADVERTISEMENT
TSQL Function
Jun 20, 2008Hey guys,
I have been trying to get a function to work but have been having a hard time. The tables that are used are a translation and validation tables. But the translation can be from different tables. What i would like the function to do is take in a 'description of a type' and a language code and send back the translated version. The issue is that the table is dynamic and there is my issue with my function.
ALTER FUNCTION [dbo].[Trans]
(
@Trans_description AS NVarchar(200),
@languageid AS nvarchar(10)
)
RETURNS nvarchar(50)
AS
BEGIN
DECLARE
@return AS nvarchar(50),
@TABLENAME AS nvarchar(50),
@sql AS nvarchar(100)
set @TableName = (select top(1) listtable from validationType where validationtypeDesc = @Trans_description)
SET @return =
(SELECT TOP (1) ValidationDesc FROM [translationvalidationlist] WHERE validationpartyid IN
(SELECT validationpartyid FROM @TableName WHERE validationtypePartyid IN
(select validationtypepartyid FROM validationtype WHERE validationtypeDesc = @Trans_description
))AND LANGUAGEID = @languageid)
RETURN @return
END
Is there anyway to do a @return = exec(@sql) of course sql would be the current @return. Right now i get the error Must declare the table variable "@TableName".
Thank you so much for all your help.
TSQL - Using Sum Function
Sep 18, 2007Hi guys,
I am looking for a solution to the problem described below (SQL SERVER 2000).
Thanks in advance for any help,
Aldo.
The script below:
Code Snippet
SELECT
Data.[Invoice No] AS 'InvoiceNo',
[JurnalTrans.REF2] AS 'JurnalTrans.REF2',
[JurnalTransMoves.SUF] AS 'JurnalTransMoves.SUF',
[RowIDDataLevel] AS 'RowIDDataLevel',
CASE WHEN RowIDDataLevel = 1 THEN CAST(Paid AS int) ELSE '0' END AS 'Paid' ,
CASE WHEN RowIDDataLevel = 1 THEN CAST([JurnalTransMoves.SUF] - [Paid] AS int) ELSE '0' END AS 'OutstandingBalance'
FROM CTE AS Data
INNER JOIN (SELECT [Invoice No], SUM([JurnalTransMoves.SUF]) AS Paid FROM CTE GROUP BY [Invoice No]) AS SUMSUF ON SUMSUF.[Invoice No] = DATA.[Invoice No]
ORDER BY [Accounts.ACCOUNTKEY], Data.[Invoice No], [RowIDDataLevel]
Gives these results:
Invoice No JurnalTrans.REF2 JurnalTransMoves.SUF RowIDDataLevel Paid OutstandingBalance
----------- ---------------- ---------------------- -------------- ----------- ------------------
5752 NULL 1155 1 1810 -655
5752 2032 400 2 0 0
5752 2033 155 3 0 0
5752 2034 100 4 0 0
5754 NULL 1732.5 1 2482 -750
5754 2035 750 2 0 0
16687 NULL 2555.8 1 2555 0
Where:
1810 (Paid) = 1155 + 400 + 155 + 100
2482 = 1732 + 750
and so on...
What I need is to get:
instead of 1810 ==> 655 = 400 + 155 + 100
instead of 2482 ==> 750
and so on...
In order to do that, I tried inserting "WHERE RowIDDataLevel Not In (1) " in the line:
Code Snippet
INNER JOIN (SELECT [Invoice No], SUM([JurnalTransMoves.SUF]) AS Paid FROM CTE WHERE RowIDDataLevel Not In (1) GROUP BY [Invoice No]) AS SUMSUF ON SUMSUF.[Invoice No] = DATA.[Invoice No]
and then getting the following:
InvoiceNo JurnalTrans.REF2 JurnalTransMoves.SUF RowIDDataLevel Paid OutstandingBalance
----------- ---------------- ---------------------- -------------- ----------- ------------------
5752 NULL 1155 1 655 500
5752 2032 400 2 0 0
5752 2033 155 3 0 0
5752 2034 100 4 0 0
5754 NULL 1732.5 1 750 982
5754 2035 750 2 0 0
That it is almost what I need, but not good enough because it makes the line marked in red to disappear because of RowIDDataLevel =1
16687 NULL 2555.8 1 2555 0
to disappear.
Greatest Function In TSQL
Aug 20, 2007Hi All,
I need to find the highest of two numbers and lowest of the two numbers.
I read on the web that Greatest and least functions in sql can help me do that.
but when i use
greatest in my query it gives me this error
GREATEST is not a recognized built-in function name.
all I have is
declare @first int, @second int
set @first='12'
set @second='14'
select GREATEST(@first,@second)
Can somebody point me in the right direction.
Thanks so much
TSQL - Using More Than One Argument In LIKE Function
Aug 19, 2007Hi guys,
I am using the LIKE function combined with a CASE WHEN to change a long list of words, but the list is too long...
Is there any posibility to insert more than one argument into one like function...?
Any other good ideas?
Below an example of the code I am using..
Thanks in advance,
Aldo.
Code Snippet
Case
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument01%' THEN 'Result'
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument02%' THEN 'Result'
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument03%' THEN 'Result'
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument04%' THEN 'Result'
ELSE ''
END AS 'Result'
DLookup Function In TSQL
May 24, 2008
Hi all,
Access has a DLookup function that allow you to look up a value in another table based on a criteria, how is this functionality achieved in TSQL?
Thanks
Regards
Melt
Problem With Round Function In TSQL
Feb 13, 2008Trying below instructions
create table t
(
indexvalue float
)
go
insert t
values (109.1)
insert t
values (109.3)
insert t
values (109.5)
insert t
values (109.9)
go
select *
from t
select sum (indexvalue) / count (*)
from t
select round (sum(indexvalue)/count (*), 1) -- this line is result of round (109.45) shows 109.4 that is incorrect
from t --why round function doesn't work correctly in this select
select round (109.45, 1) -- but this line results 109.5 that is correct
TSQL Function To Return Numeric Value Of Non Numeric Field
Jul 20, 2006I need to replace Access Val() functions with similiar function in sql.
i.e. Return 123 from the statement: SELECT functionname(123mls)
Return 4.56 from the satement: SELECT functionname(4.56tonnes)
Any one with ideas please
Thanks
George
Equivalent Tsql For Sql Server 2000 Is Needed [from Sql Server 2005 Only Tsql]
Nov 19, 2007Can 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
Help Convert MS Access Function To MS SQL User Defined Function
Aug 1, 2005I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View 3 Replies View RelatedIn-Line Table-Valued Function: How To Get The Result Out From The Function?
Dec 9, 2007Hi all,
I executed the following sql script successfuuly:
shcInLineTableFN.sql:
USE pubs
GO
CREATE FUNCTION dbo.AuthorsForState(@cState char(2))
RETURNS TABLE
AS
RETURN (SELECT * FROM Authors WHERE state = @cState)
GO
And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.
I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:
shcInlineTableFNresult.sql:
USE pubs
GO
SELECT * FROM shcInLineTableFN
GO
I got the following error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'shcInLineTableFN'.
Please help and advise me how to fix the syntax
"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.
Thanks in advance,
Scott Chang
A Function Smilar To DECODE Function In Oracle
Oct 19, 2004I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...
ali
Using RAND Function In User Defined Function?
Mar 22, 2006Got some errors on this one...
Is Rand function cannot be used in the User Defined function?
Thanks.
Pass Output Of A Function To Another Function As Input
Jan 7, 2014I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...
I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.
I Want A Function Like IfNull Function To Use In Expression Builder
Jul 24, 2007Hi,
I wonder if there a function that i can use in the expression builder that return a value (e.g o) if the input value is null ( Like ifnull(colum1,0) )
i hope to have the answer because i need it so much.
Maylo
TSQL Or SQL CLR?
Aug 9, 2006Hello 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
TSQL Please Help
Jan 8, 2006Hi
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
Tsql Help!
Mar 14, 2001simple 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
TSQL Help !
Jun 5, 2001Please 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))
Tsql
Oct 1, 2001Sql 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
TSQL
Oct 1, 2001HI,
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
TSQL Help
Jun 5, 2000Hi,
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
Tsql Help
Dec 6, 2000Hi,
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
TSQL Help
Aug 4, 2004I 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?
Tsql Help
May 9, 2007I 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
SP Or TSQL
Nov 30, 2007Hi
Is there any sp or command to deactivate and activate all constraints in a database...
Thanks in Advance
RKNAIR
Tsql Help
Mar 4, 2008I 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"
TSQL Help
Aug 22, 2005A 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 RelatedHow Can This Be Done Using TSQL?
Feb 22, 2008I 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!
TSQL - Using WHERE
Dec 25, 2007Hi 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.
Tsql
Oct 6, 2007Hi,
Knows anybody solution for this problem:
http://blog.vyvojar.cz/dotnet/archive/2007/10/05/226475.aspx
Thx a lot
I Need Help With This Tsql Statement
Apr 2, 2007Every 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
Tsql Loop
Apr 16, 2007I 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