Hi all..
i have a question about TSQL,
how it is possible to write this querry correctly:
Code SnippetSELECT dtEvent.EventNo as "Event ID:", FROM LIKE '%dtEvent%' AS dtEvent,WHERE dtMachine.id = dtEvent.agentmachineid
in the "FROM" part is a problem....LIKE '%%' isn't correct, but i don't know how can i write this query correctly...
by the way: for example just "dtEvent" is known ...but not the fullname like "dtEvent_XXXXXXXX"
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
I have a table with a DateTime column called 'cutoff'. Is it possible to grab the TOP 1 record ordered by the cutoff column based on the current date?SELECT TOP 1 * FROM mytable WHERE cutoff >= GetDate() ORDER BY cutoff...works only if there is a cutoff < the current date. If GetDate() is greater then latest cutoff date it returns nothing. In this case, is it possible to just return the latest record? I'd like to keep this to 1 query if possible? :-)
I was hoping to get a little input on a problem I'm having.
In the DataAccessLayer of my application, I have a "search" function written that takes a bunch of parameters (in the form of a class object) and depending on what each of the paramaters are set to (to include search type parameters) it builds an appropriate select statement. The issue is that my company has recently decided to require all DataAccessLayer functions to use TableAdapters. TableAdapters can use StoredProceedures, and StoredProceedures can make external calls (it all seems a bit backward to me, but there does seem to be a bit of logic in that TableAdapters contain a connection string which is set in the app.Config file)... Anyway, here's an example of how I am doing it currently, and I was hoping someone could suggest a way I could do it with either TSQL, or otherwise. If CLR is the way to go, how does that work?
internal static dsStrongTypeDataSet GetAll(clsMyClass inData) { bool first = true; dsStrongTypeDataSet data = new dsStrongTypeDataSet (); string selectStatement = "Select * from tblMyTable where "; //There is one of these if statements for each parameter if ((inData.Paramater1 != null)) { if (!first)//not as important in this section of code, //but there are areas where there are up to 30 parameters selectStatement += " and "; if (inData.SrchParameter1 == SearchType.Fuzzy) selectStatement += " Column1 LIKE ('%" + inData.Parameter1 + "%') "; else if (inData.SrchParameter1 == SearchType.Literal) selectStatement += "Column1 = '" + inData.Parameter1 + "'"; first = false; } //More if statements like above for EVERY parameter possible SqlCommand selectCommand = new SqlCommand(selectStatement, CorrectSqlConnection()); SqlDataAdapter dataAdapter = new SqlDataAdapter(selectCommand); dataAdapter.Fill(data, "tblMyTable"); return data; }
Hi, I hope that someone can help me understand why my query is not returning what I expect. When I run this query:
SELECT DISTINCT(TransactionKey) FROM Transactions_Fact WHERE DateKey = 14550 AND TransactionKey BETWEEN 1 AND 90000000 AND TransactionKey NOT IN (SELECT DISTINCT(TransactionKey) FROM tmpTransactions WHERE TranDate = 14550 AND TransactionKey BETWEEN 1 AND 90000000)
I get 150 rows back, which is what I expect. However, if I leave out the 'AND TransactionKey BETWEEN 1 AND 90000000', then I don't get back anything?!?
SELECT DISTINCT(TransactionKey) FROM Transactions_Fact WHERE DateKey = 14550 AND TransactionKey NOT IN (SELECT DISTINCT(TransactionKey) FROM tmpTransactions WHERE TranDate = 14550)
Any ideas as to what I'm missing here? It seems like it should at least return the same 150 rows. Thanks for any help.
I am trying to return an ouput parameter from my query. I have tested the stored proceedure extensivly within the SQL Management Studio, and I know it works fine, so that means the error is somewhere within my code, but for the life of my I cant figure where. Here is my stored proc: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
-- ============================================= -- Author:Name -- Create date: -- Description: -- ============================================= ALTER PROCEDURE [dbo].[tblSiteVisits_FindOfficerName] @VisitID int, @OfficerName varchar(100) output AS BEGIN -- Variables Declare @OfficerID int --Declare @OfficerName varchar(100) -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; Select @OfficerID = (Select top 1 OfficerID from tblSiteVisitOfficers Where VisitID = @VisitID)
IF (@OfficerID Is Null) BEGIN -- Get the None Registered Officer Select @OfficerName = (Select top 1 OfficerOther from dbo.tblSiteVisitOfficers Where VisitID = @VisitID) print 'Got unregistered Officer ' + @OfficerName END ELSE BEGIN -- Get the Registered Officer Select @OfficerName = (Select OfficerFName + ' ' + OfficerLname from dbo.tblOfficers Where OfficerID = @OfficerID) print 'Got Registered Officer ' + @OfficerName END END
And here is the code I am using to access this proceedure:1 Dim blah As String 2 Dim conn2 As New SqlConnection() 3 Dim cmd2 As New SqlCommand() 4 conn2.ConnectionString = _ConnString 5 cmd2.Connection = conn2 6 cmd2.CommandType = CommandType.StoredProcedure 7 cmd2.CommandText = "dbo.tblSiteVisits_FindOfficerName" 8 9 cmd.Parameters.AddWithValue("@VisitID", Convert.ToInt32(row("VisitID"))) 10 cmd.Parameters.Add("@OfficerName", SqlDbType.VarChar, 100) 11 cmd.Parameters("@OfficerName").Direction = ParameterDirection.Output 12 Try 13 conn.Open() 14 cmd.ExecuteNonQuery() 15 blah = cmd.Parameters("@OfficerName").Value.ToString() 16 17 Catch ex As Exception 18 Throw ex 19 Finally 20 conn.Close() 21 End Try 22
However there I never recieve the output value, and because of the way my database is structures, there is no possible way, that there is no output value.If anyone can help, that would be great, kind regards.
I have 2 views (ITMISSUES,USR_VIEW_RPTS) I want to retreive (Count of Issues Alis name)-noofissues,total issqty, total (sum of Total Receipt Qty Alis name)- rctqty between a range of items and between a range of dates.How can i do it.
SELECT COUNT(DOCNUMBR)AS NOOFISSUES,ABS(SUM(TRXQTY))AS TOTISSQTY, MAX(ITMISSUES.DOCDATE)AS LASTISSDATE,SUM(QTY) AS RCQTY from ITMISSUES INNER JOIN USR_VIEW_RPTS ON ITMISSUES.ITEMNMBR=USR_VIEW_RPTS.ITEMNMBR WHERE ITMISSUES.ITEMNMBR BETWEEN 'xyz' AND 'xyz' AND ITMISSUES.DOCDATE BETWEEN '2004-08-01' AND '2004-08-31'
Hi,I am using a .dqy file to import some rows from a sql server 2003database to microsoft excel. Unfortunately, I have am importing morerows than excel can handle. Is there a way around this?It appears that there is no equivalent of the LIMIT(offset, number ofrows to return) function of mysql in tsql. Had there been anequivalent, I would have created multiple .dqy files, which would querythe database for pages of results as appropriate. There is an upperlimit to the number of records that there can be in the database, sothis would have been a feasible solution.Also, I must use .dqy files (or something equivalen) because thequeries are invoked from a website, and it is necessary to download theresults, sort/filter, etc. (in excel).Thanks for any suggestions.
Is there any possibility in SQL server 2005 to find out the source of some sql-query result? I need some kind of meta information about result I got. For example:
[if query is] select t1.id as id1, t2.id as id2, 67 as col67 from t1, t2
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
Me any my team are soon going to work on a performance critical application. My team has some experience of writing SQL, however we have not done performance oriented coding.
I am looking for a comphrehensive document which lists information for writing good SQL with performance. Please guide if there is such a document or web site.
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.
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
