Tsql Case

Aug 12, 2004

im 1 day new to sql server and trying to build a full name for a sp
so i am testing using :

DECLARE @SALvarchar(4)
DECLARE @Fnamevarchar(15)
DECLARE @MIvarchar(10)
DECLARE @Lnamevarchar(15)
DECLARE @Titlevarchar(25)
DECLARE @Contactvarchar(50)


SET @SAL = 'Mr.'
SET @Fname= 'Conrad'
SET @MI= null
SET @Lname= 'Bains'
SET @Title= null

SELECT @Contact +
CASE
WHEN @Sal IS NULL THEN '' + @Fname + ' '
WHEN @Mi IS NULL THEN + @Lname
ELSE +@Mi + ' ' + @Lname
WHEN @Title IS NULL THEN + ''
ELSE +', ' + @Title
END

the else is wrong not sure how to handle any help on my simpleton ?

THnks

View 4 Replies


ADVERTISEMENT

Tsql Case Statement

Sep 12, 2007

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 1 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 Server 2008 :: Change Text Format From Case Sensitive To Case Insensitive?

Aug 31, 2015

How can I change my T-SQL text editor from text sensitive to text insensitive?

View 2 Replies View Related

Case Insensitivity Is On Server Wide: Tables Render Case Sensative...

Jan 6, 2005

Hello:

I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?

Any suggestions?

View 4 Replies View Related

Case Insensitive Searching In Sql Server 2000 When It's Case Sensitive

May 4, 2007

Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
 
thanks in advance.

View 3 Replies View Related

HELP! Case Insensitive Database On Case Sensitive Server

Aug 17, 2005

We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????

View 4 Replies View Related

Doing A Case-sensitive Query In A Case-insensitive Database

May 29, 2008

I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?

Jacob

View 5 Replies View Related

Transact SQL :: Upper Case To Lower Case Conversion

May 4, 2015

I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?

View 7 Replies View Related

Can You Use Replication From A Case Sensitive Db To A Case Insensitive Db?

Aug 19, 2007

I am curious with using replication in sql server 2005 one way from db A (source) replicating to db B(destination) in which db A has a collation of CS and db B has a collation of CI.  Will there be any problems with this scenario? Thanks in advance! 

View 2 Replies View Related

Problem Using Result From CASE In Another CASE Statement

Nov 5, 2007

I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.

Previously, I had been duplicating the CASE logic for both columns, like so:




Code Block...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE
[logic for condition 1]
THEN 'Condition 1 text'
WHEN [logic for condition 2]
THEN 'Condition 2 text'
WHEN [logic for condition 3]
THEN 'Condition 3 text'
WHEN [logic for condition 4]
THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...






This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.

This is what I'd like to do:



Code Block
...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,


shipment_status_text =

CASE shipment_status

WHEN 1 THEN 'Condition 1 text'

WHEN 2 THEN 'Condition 2 text'

WHEN 3 THEN 'Condition 3 text'

WHEN 4 THEN 'Condition 4 text'

ELSE 'Error'

END,
...remainder of SQL view...


This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.

Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?

Thanks,

Jason

View 1 Replies View Related

Case Sensitivity When A User Enters Data Into The Database. How To Deal With Case Sensitivity.

Sep 6, 2007

I am working on a C#/asp.net web application. The application has a text box that allows a user to enter a name. The name is then saved to the database.
Before the name is saved to the database, I need to be able to check if the name already exists in the database. The problem  here is that what if the name is in the database as "JoE ScMedLap" and somoene enters the name as "Joe Schmedlap" which already exists in the database,but just differs in case.
In other words how do deal with case sensitiviy issues.

View 2 Replies View Related

Restore Of Case Insensitive Database To A Case Sensitive Database - SQL Server 2000

Jul 20, 2005

Yesterday I received a response to my CI/CS Collation problem and therecommendation was to try and restore a CI Collation database to a CSCollation database. After creating a blank CS database a full restore(Force restore over existing database) does change the Collation toCI. I'm unsure as to how I can restore without changing theCollation. Any suggestions?

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







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