TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Oct 29, 2007
Hi guys,
I have the query below (running okay):
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.
View 3 Replies
ADVERTISEMENT
Jul 23, 2005
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,
View 18 Replies
View Related
Nov 6, 2005
Is there a shortcut to spelling out column names when you are doing a select statement?
For instance could you write Select 1, 5, 6 from table where whatever...
I tried this but didn't get any results so if you can I must be using wrong syntax.
Thanks
!
View 2 Replies
View Related
Aug 21, 2007
I am trying to order by the field and direction as provided by input parameters @COLTOSORTBY and @DIR while using a CTE and assigning Row_Number, but am running into syntax errors.
Say I have a table called myTable with columns col1,col2,col3,
Here's what I'm trying to do
with myCTE AS
(
Select
col1
,col2
,col3
,row_number() over (order by
case when(@DIR = 'ASC') then
case when @COLTOSORTBY='col1' then col1 asc
when @COLTOSORTBY='col2' then col2 asc
else col3 asc
end
else
case when @COLTOSORTBY='col1' then col1 desc
when @COLTOSORTBY='col2' then col2 desc
else col3 desc
end
end
from myTable
)
Please let me know what i can do with minimal code repetition and achive my goal of dynamically sorting column and direction. I do not want to use dynamic SQL under any circumstance.
Thanks.
View 7 Replies
View Related
Aug 28, 2015
Here is my requirement
Table 1 Order
order ID, Sales order ID order date, order type
Table 2 Order details
Order Details ID, Order ID, Order Stage
Table 3 Related Order details
Order ID(FK to Order ID), Related Order Details ID(FK to Order Details ID), Related Order ID( FK to Order ID)
Here is example
Table 1 Order
1, 1234, 2015-01-01, Refill
2, 1234, 2015-02-02, Extension
Table 2 Order Details
1, 1, Approved
2, 1, Approved
3, 2, Rejected
Table 3 Related Order Details
2, 1, 1
2, 2, 1
I have to Select Order, Order Details and Order Status
Order Status is determined from Order Stage as follows:
If, at least one order detail line(from Order Details and Related Order details table) is approved, that Order status=Approved.
For the example, Order Status of Order ID=2, is Approved based on order status for order details lines 3(from table 2) and order details ID 1 and 2 (from table 3)
How to combined order stage from table 2 and table 3 and then compute order status.
View 3 Replies
View Related
May 7, 2008
The stored procedure, below, results in this error when I try to compile...
Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69
Incorrect syntax near the keyword 'ORDER'.
However the select statement itself runs perfectly well as a query, no errors.
The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs.
What gives with this?
Thanks in advance
R.
The code:
Code Snippet
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF object_id('InsertImportedReportData ') IS NOT NULL
DROP PROCEDURE InsertImportedReportData
GO
-- =============================================
-- Author: -----
-- Create date:
-- Description: inserts imported records, marking as duplicates if possible
-- =============================================
CREATE PROCEDURE InsertImportedReportData
-- Add the parameters for the stored procedure here
@importedReportID int,
@authCode varchar(12)
AS
BEGIN
DECLARE @errmsg VARCHAR(80);
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--IF (@authCode <> 'TX-TEC')
--BEGIN
-- SET @errmsg = 'Unsupported reporting format:' + @authCode
-- RAISERROR(@errmsg, 11, 1);
--END
DECLARE srcRecsCursor CURSOR LOCAL
FOR (SELECT
ImportedRecordID
,ImportedReportID
,AuthorityCode
,[ID]
,[Field1] AS RecordType
,[Field2] AS FormType
,[Field3] AS ItemID
,[Field4] AS EntityCode
,[Field5] AS LastName
,[Field6] AS FirstMiddleNames
,[Field7] AS Title
,[Field8] AS Suffix
,[Field9] AS AddressLine1
,[Field10] AS AddressLine2
,[Field11] AS City
,[Field12] AS [State]
,[Field13] AS ZipFull
,[Field14] AS OutOfStatePAC
,[Field15] AS FecID
,[Field16] AS Date
,[Field17] AS Amount
,[Field18] AS [Description]
,[Field19] AS Employer
,[Field20] AS Occupation
,[Field21] AS AttorneyJob
,[Field22] AS SpouseEmployer
,[Field23] As ChildParentEmployer1
,[Field24] AS ChildParentEmployer2
,[Field25] AS InKindTravel
,[Field26] AS TravellerLastName
,[Field27] AS TravellerFirstMiddleNames
,[Field28] AS TravellerTitle
,[Field29] AS TravellerSuffix
,[Field30] AS TravelMode
,[Field31] As DptCity
,[Field32] AS DptDate
,[Field33] AS ArvCity
,[Field34] AS ArvDate
,[Field35] AS TravelPurpose
,[Field36] AS TravelRecordBackReference
FROM ImportedNativeRecords
WHERE ImportedReportID IS NOT NULL
AND ReportType IN ('RCPT','PLDG')
ORDER BY ImportedRecordID -- this should work but gives syntax error!
);
END
View 3 Replies
View Related
May 15, 2008
Greetings,
I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.
How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name, A.index
FROM
...
...
ORDER BY A.[Index], A.Name ASC
END
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name
FROM
...
...
ORDER BY A.[Index]
END
Thanks
View 14 Replies
View Related
Aug 31, 2005
Ok, I inherited this database and there is a field that stopres a selectstatement. Is there anyway possible to execute the value of the fieldwithin a select statement?For example:the table:Name "george"lookupForName "Select orders from Ordertable"So maybe something like select name, execute(lookupforname) as ordersSorry, I didn't design this, just inherited :)george
View 1 Replies
View Related
Mar 17, 2008
Hello friends , I have table (MoneyTrans) with following structure
[Id] [bigint] NOT NULL,
[TransDate] [smalldatetime] NOT NULL,
[TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw'
[Amount] [money] NOT NULL
I need to write a query to generate following output
Trans Date, total deposits, total withdrawls, closing balance
i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate )
I am working on this for past two days with out getting a right solution. Any help is appreciated
Sara
View 5 Replies
View Related
Mar 25, 2008
Hello,
Is it possible to generate a identityfield dynamically upon select, like this:
SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username ASC
I want the output to be:
1 - Name12 - Name23 - Name3
The reason for this, is that i want to change the sort order in many diffrent ways, but i need to get the IDs from 1-?? even when the sort order changes.
Like:
SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username DESC
I want the output to be:
1 - Name32 - Name23 - Name1
Patrick
View 4 Replies
View Related
Oct 5, 2001
Hi
I have tabelA, Which has 10 columns, I need to select 10 column values only no field names. Is there any way I can select only table values not field names. I don't want to see field name in my query result set. Please let me know. I appreciate your help.
Thanks
Regards
-Leong
View 2 Replies
View Related
Dec 4, 2006
Hello,
i am pretty new to asp. I am trying to do a select statement for sending an email to everyone who is not an admin. the code is below, i know it must be fairly simple, yet i do not know how to do it. With the code below, I select everyone. I want to know how to do it properly, similar to the second which does not work.
Dim cmd As New OleDbCommand("SELECT Username, Pass, Gender, FirstName, LastName, Email, NickName FROM tblUsers", conn)
DOES NOT WORK:
Dim cmd As New OleDbCommand("SELECT Username, Pass, Gender, FirstName, LastName, Email, NickName FROM tblUsers WHERE Admin = 'N'", conn)
Thanks in advance.
View 3 Replies
View Related
Aug 13, 2014
I've got a encrypted column in sql which holds the password field, e.g. TPSK9RlOz0/2BhuQntVeaBda+9g=, is their a way in a select statement to get the password ?
View 3 Replies
View Related
Aug 4, 2015
I have a query that displays a bunch of fields, specifically a createdon field and closedate field.I need to find the diff in days between date1 and date2 and only disiplay those results.For example: where the NumDays = a certain value. For example, 81.I have the NumDays displaying in the query, but I don't know how to reference these values in the where clause.
SELECT
TBU.businessunitidnameAS 'BU Name',
LEADS.statecodenameAS 'Status',
LEADS.statuscodeAS 'Status Code',
LEADS.accountidnameAS 'Account Name',
[code]...
View 5 Replies
View Related
Jan 24, 2007
I do a SELECT * from table command in an ASP page to build a text fileout on our server, but the export is not to allow a field name rows ofrecords. The first thing I get is a row with all the field names. Whydo these come in if they are not part of the table records? How do Ieliminate this from being produced? Here's the ASP code....<html><head><title>Package Tracking Results - Client Feed</title></head><body><%' define variablesdim oConn ' ADO Connectiondim oRSc ' ADO Recordset - Courier tabledim cSQLstr ' SQL string - Courier tabledim oRSn ' ADO Recordset - NAN tabledim nSQLstr ' SQL string - NAN tabledim objFSO ' FSO Connectiondim objTextFile ' Text File' set and define FSO connection and text file object locationSet objFSO = CreateObject("Scripting.FileSystemObject")'Set objTextFile =objFSO.CreateTextFile(Server.MapPath("textfile.txt"))'Response.Write (Server.MapPath("textfile.txt") & "<br />")Set objTextFile = objFSO.OpenTextFile("C: extfile.txt",2)' write text to text file'objTextFile.WriteLine "This text is in the file ""textfile.txt""!"' SQL strings for Courier and NAN tablescSQLstr = "SELECT * FROM Courier"' set and open ADO connection & oRSc recordsetsset oConn=Server.CreateObject("ADODB.connection")oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &"c:/Database/QaTracking/QaTracking.mdb" & ";"set oRSc=Server.CreateObject("ADODB.Recordset")oRSc.Open cSQLstr, oConnResponse.ContentType = "text/plain"Dim i, j, tmpIf Not oRSc.EOF ThenFor i = 1 To oRSc.Fields.CountobjTextFile.Write oRSc.Fields(i-1).NameIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineWhile Not oRSc.EOFFor i = 1 To oRSc.Fields.CountIf oRSc.Fields(i-1) <"" Thentmp = oRSc.Fields(i-1)' If TypeName(tmp) = "String" Then' objTextFile.Write "" &_'Replace(oRSc.Fields(i-1),vbCrLf,"") & ""' ElseobjTextFile.Write oRSc.Fields(i-1)' End IfEnd IfIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineoRSc.MoveNextWendEnd IfobjTextFile.CloseSet objTextFile = NothingSet objFSO = NothingoRSc.CloseSet oRSc = NothingoConn.CloseSet oConn = Nothing%></body></html>
View 1 Replies
View Related
Jan 3, 2008
I have this SELECT statement.
SELECT [issueID], [name] FROM [MyIssue]
What I wanted to do is in addition to the above statement, I want to add two run time fields like this:
99 [issueID],'All Issues' [name]
So let's say the above select statements generates this list:
Summer 2007 Issue
Winter 2007 Issue
The two addition fields will make the result list like this:
01 Summer 2007 Issue
02 Winter 2007 Issue
99 All Issues
How do I accomplish this? Any help is much appreciated.
View 5 Replies
View Related
May 21, 2007
Hi,
I have a field called "Starting DateTime" and I want to convert into my local time. I can convert it in the report with the expression "=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Starting_DateTime.Value)", but that is too late. I want to convert it in the Select statement of the query.
Can anyone help me please?
Thx
View 6 Replies
View Related
Sep 3, 2007
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View 3 Replies
View Related
Jan 8, 2008
Hey gang,
I've got a query and I'm really not sure how to get what I need. I've got a unix datasource that I've setup a linked server for on my SQL database so I'm using Select * From OpenQuery(DataSource, 'Query')
I am able to select all of the records from the first two tables that I need. The problem I'm having is the last step. I need a field in the select statement that is going to be a simple yes or no based off of if a customer number is present in a different table. The table that I need to look into can have up to 99 instances of the customer number. It's a "Note" table that stores a string, the customer number and the sequence number of the note. Obviously I don't want to do a straight join and query because I don't want to get 99 duplicates records in the query I'm already pulling.
Here's my current Query this works fine:
Select *From OpenQuery(UnixData, 'Select CPAREC.CustomerNo, CPBASC_All.CustorCompName, CPAREC.DateAdded, CPAREC.Terms, CPAREC.CreditLimit, CPAREC.PowerNum
From CPAREC Inner Join CPBASC_All on CPAREC.CustomerNo = CPBASC_All.CustomerNo
Where DateAdded >= #12/01/07# and DateAdded <= #12/31/07#')
What I need to add is one more column to the results of this query that will let me know if the Customer number is found in a "Notes" table. This table has 3 fields CustomerNo, SequenceNo, Note.
I don't want to join and select on customer number as the customer number maybe repeated as much as 99 times in the Notes table. I just need to know if a single instance of the customer number was found in that table so I can set a column in my select statement as NotesExist (Yes or No)
Any advice would be greatly appreciated.
View 2 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Jul 20, 2005
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies
View Related
Apr 8, 2006
In Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB
I created the following parameter:Dim strName As String Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50) parameterBName.Value = strName myCommand.Parameters.Add(parameterBName)
I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER "
error is:Line 1: Incorrect syntax near 'V'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'V'.
Source Error:
Line 59: Line 60: Line 61: myCommand.ExecuteNonQuery() 'Execute the query
View 2 Replies
View Related
Jul 13, 2015
Consider the following data:
create table #test
(id int
,color varchar(20)
)
insert into #test
(id, color)
values
(1, 'blue'),(2, 'red'),(3,'green'),(4,'red,green')
if I wanted to run a query to select any records that had red in the color field, how would I do that? Not the one with only red, but a query that would give me both record number 2 and record number 4.
View 9 Replies
View Related
Jul 4, 2006
Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E
can any one help me in this?
please give me a sample query.
Thanks and Regards,
Kiran Suthar
View 7 Replies
View Related
May 5, 2015
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
View 7 Replies
View Related
Oct 20, 2014
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
View 1 Replies
View Related
Aug 10, 2006
I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View 1 Replies
View Related
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
Sep 5, 2007
Hi guys,
I need help with this one...
Iam Trying to understand how to use the statement WITH
I am running the code below, but getting error.
note: I have SQL SERVER 2005 in my PC, but retrieving data from the SQL SERVER 2000 (in the server)
Thanks in advance,
Aldo.
Code Snippet
WITH MyCTE (FILTER, SORTGROUP)
AS
(
SELECT ACCOUNTS.FILTER, ACCOUNTS.SORTGROUP FROM ACCOUNTS
)
SELECT * FROM MyCTE AS CTE_01;
Error Messages:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
View 6 Replies
View Related
Feb 21, 2001
Good morning one and all,
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.
Any and all help appreciated,
Thanx Gurmi
View 1 Replies
View Related
Mar 4, 2008
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
View 2 Replies
View Related
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
