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
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.
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.
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
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
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:
>>>> 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):
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
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
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
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)
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,
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 ??????????
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
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
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
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
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))
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
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 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?
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.
@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
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)
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
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!
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:
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.
