Transact SQL :: CASE Order Of Evaluation

Sep 24, 2015

I have the following query in a user-defined function. It accepts a single string parameter, but for the sake of simplicity, I have substituted actual strings in the query. It basically checks the passed string. If it ends with "Id", it strips off the "Id" and returns the resulting string. If it ends with "Id" followed by a digit, it strips that off and returns the string.

SELECT CASE
WHEN LEN('IncidentViolationId') > 2 AND RIGHT('IncidentViolationId', 2) = 'Id' THEN LEFT('IncidentViolationId', LEN('IncidentViolationId') - 2)
WHEN PATINDEX('%Id[0-9]', 'IncidentViolationId') > 1 THEN LEFT('IncidentViolationId', PATINDEX('%Id[0-9]', 'IncidentViolationId') - 1)
ELSE 'IncidentViolationId' END

This code has worked flawlessly for quite some time and all of a sudden I get "Invalid length parameter passed to left function". I understand why LEFT() would normally fail if I passed it a -1 for the second parameter, but in this scenario, asI understand it, it never should have reached the second WHEN condition since the first one evaluates to true. Why and why all of a sudden?

View 10 Replies


ADVERTISEMENT

Transact SQL :: Order By Used With Case Statement

Sep 24, 2015

I use Order By Used with Case Statement and may be this is the reason for which i am getting error

Windowed functions can only appear in the SELECT or ORDER BY clauses.

Here is my complete sql where i use Row_Number function for pagination with sorting.

DECLARE @StartIndex INT
DECLARE @EndIndex INT
DECLARE @SortColumn VARCHAR(MAX)
DECLARE @SortDirection VARCHAR(MAX)
SET @StartIndex = 1

[Code] ....

View 7 Replies View Related

Evaluation Order For TextBox Field In Matrix In RS

May 27, 2008

Hi Guys,

Does anyone knows what is the evaluation order for expressions attached to "Value" property and "BackgroundColor" property for a field.

My problem is if a field is being changed in its value property to a particular value, will this changed value be recognised immediately by the Backgroundcolor property during its rule evaluation, it does not seem to be the case.

View 1 Replies View Related

Wrong Evaluation Of A Variable With Expression In Case It Is Used Simultaneously By Several Tasks

Aug 22, 2007



Hi guys,

I have experienced problem while trying to use variable with expression based on several other variables in tasks running parallel.

The details are as following:

There is a SSIS package with simple Control flow: one Script Task which actually do nothing and two Execute Process Tasks, they run after Script Task in parallel. Then there are three simple (EvaluateAsExpression = False) string variables ServerName, Folder and JobNumber with values ServerName = €œ\test€?, Folder = €œtest€? and JobNumber = €œ12345€?. And there is one variable FullPath with expression @[User:: ServerName] + "\" + @[User::Folder] + "_" + @[User::JobNumber]. All the variables are of the Package scope. Then in Execute Process Tasks I have similar expressions based on FullPath variable: Execute Process Task 1 has expression @[User::FullPath] + "\date.bat" and Execute Process Task 2 has @[User::FullPath] + "\time.bat" one. As you understand these expressions define what exactly task should execute.

Then I€™m going to execute package from command line so appropriate XML configuration file has been created. The file contains following values for variables described above: ServerName = €œ\LiveServer€?, Folder = €œJob€? and JobNumber = €œ33091€?.

After series of consequent executions I have got following log file:

€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?

€¦ Execute Process Task 2€¦ Executing the process €œ\Test est_12345 ime.bat€?



€¦ Execute Process Task 1€¦ Executing the process €œ\Test est_12345date.bat€?

€¦ Execute Process Task 2€¦ Executing the process €œ\LiveServerJob_33091 ime.bat€?



€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?

€¦ Execute Process Task 2€¦ Executing the process €œ\Test est_12345 ime.bat€?



€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?

€¦ Execute Process Task 2€¦ Executing the process €œ\LiveServerJob_33091 ime.bat€?



€¦



As you can see one of Execute Process Tasks usually receive correct value of the expression (based on values of variables from the configuration file) while another - incorrect one (based on €œdefault€? values of variables set directly in package). Sometimes wrong value appears in Task 1, next time in Task 2. Situations when both expressions in tasks evaluated correctly are very rare.

Then if you add some more Execute Process Tasks with similar expressions in the package (for ex. simply by copying existing tasks) you€™ll get a good chance to catch error like this:



OnError,,,Execute Process Task 1,,,8/17/2007 2:07:12 PM,8/17/2007 2:07:12 PM,-1073450774,0x,Reading the variable "User::FullPath" failed with error code 0xC0047084.



OnError,,,Execute Process Task 1,,,8/17/2007 2:07:12 PM,8/17/2007 2:07:12 PM,-1073647613,0x,The expression "@[User::FullPath] + "\time.bat"" on property "Executable" cannot be evaluated. Modify the expression to be valid.



Seems variable with expression FullPath is locked during evaluation by one of the parallel tasks in such a way that another task can€™t read it value correctly. Can someone help me with the issue? Maybe there are some options I missed which could prevent such behavior of application? Please let me know how I can make the package work correctly.

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

ORDER BY CASE

Dec 22, 2006

this is my query="SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON(i.ItemID=L.ItemID) WHERE L.instructorID='12232' AND courseID='12' ANDtype='Audio' order by CASE WHEN Sortkey is not null then 1 else 0 end"My SortKey can be NULL. Here's the output I am getting:(the || is to denote sortkey column)37542 Tape 1 ||37544 Tape 2 ||37819 Symphony1 ||37820 Symphony2 ||37821 Symphony3 ||37828 Symphony ||60962 Test ||61570 New Test Record |Africa|61572 Test 3 |Africa 1|63186 Music for Strings |Brazil|I want use Sortkey when it is not null. desired output:61570 New Test Record |Africa|61572 Test 3 |Africa 1|63186 Music for Strings |Brazil|37542 Tape 1 ||37544 Tape 2 ||37819 Symphony1 ||37820 Symphony2 ||37821 Symphony3 ||37828 Symphony ||60962 Test ||

View 10 Replies View Related

2 Case Statements In Order By

Oct 31, 2013

It will be part of the stored proc, but for now I couldn't even get it running in ssms. It will be two parameters/variables, one for order by column name and other for order by direction, i.e. desc or asc.I have tried following three ways, but none is working:

(1)
order by case when @Sort_by= '[A_ID]' AND @Sort_Dir ='Desc' then A_ID end desc
case when @Sort_by= '[A_ID]' AND @Sort_Dir ='Asc' then A_ID end asc

(2)
order by case when @Sort_by= '[A_ID]' AND @Sort_Dir ='Desc' then A_ID desc end
case when @Sort_by= '[A_ID]' AND @Sort_Dir ='Asc' then A_ID asc end

(3)
ORDER BY CASE @Sort_by when '[A_ID]' then [A_ID] end
Case @Sort_Dir when 'Desc' then desc end

View 3 Replies View Related

Alternative To CASE In The Order By Statement?

Feb 15, 2008

 The query below at the bottom works, but when I try to add the line below (a third line/argument in the CASE statement) then I get an error. It is not possible to have a table name and a DESC (sort order) in the same CASE line/argument. Are there other ways to accomplish this, because I would like to sort with both "h.ObjectType" and "h.ObjectType DESC"?             WHEN @SortBy = 2 THEN h.ObjectType DESC      SELECT weight, ObjectText.ObjectId, ObjectText.ObjectType, ObjectText.Title, ObjectText.ShortText    FROM @hits h INNER JOIN ObjectText ON h.id = ObjectText.ObjectId AND h.ObjectType = ObjectText.ObjectType    WHERE ObjectText.LanguageCode = @LanguageCode    ORDER BY        CASE WHEN @SortBy = 0 THEN weight             WHEN @SortBy = 1 THEN h.ObjectType        END    DESCRegards, Sigurd 

View 4 Replies View Related

ORDER BY, CASE, With Multiple Columns

Nov 7, 2003

I'm unable to specify multiple columns in my order by statement if i use a case statement.
Does anyone know why this is, or what syntax would make this work?

Thanks


SELECT ....
ORDER BY (CASE Lower(@SortExpression)
WHEN 'prodname' THEN prodname, prodprice
WHEN 'prodsize' THEN prodsize, prodname
WHEN 'prodprice' THEN prodprice, prodname
Else prodcompany, prodname
END)

View 5 Replies View Related

Sort Order/case-sensitivity

Aug 30, 2000

I have a case-insensitive sort order on my SQL7, NT4, SP1,2 installation. However, there are times when I want to do case-sensitive search ciriteria.

Is there something like the pseudo code:
SET SORT ORDER CASE-SENSITIVE ON
SELECT * FROM table WHERE name = 'Black'
SET SORT ORDER CASE-SENSITIVE OFF

..that doesn't return 'BLACK'

thanks in advance for your time
BL

View 1 Replies View Related

Sort Order And Case Sensitivity

Jul 20, 2005

I have a query which filters records containing uppercase andLowercase i.e.Smith and SMITH, Henderson and HENDERSON etc.Is there a way that I can filter only those records that contain thefirst uppercase letter and the remaining lowercase letters for myquery i.e. Smith , HENDERSON etc.Thanks

View 6 Replies View Related

SQL 2012 :: Using CASE In Order To Update Columns To Be Either A Or B

May 13, 2015

I am relatively new to complex queries and need creating a query using a CASE in order to update columns to be either A or B. A few things about this is that I am joining tables from linked servers as well. This is the last part. I execute the query and receive the error:

Incorrect syntax near the keyword 'from'.

select (select FirstName from [ZZZXXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as supervisorFirstName,
(select LastName from [ZZZXXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as supervisorLastName,
(select PersonID from [ZZZXXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as SupervisorEmployeeID,

[Code] .....

View 4 Replies View Related

SQL 2012 :: Order Of Preference In Case Statement

Sep 9, 2015

I'm trying to set up a statement that gives me a field called 'BINNO' if the payor = Commercial. But, I have a few customers that don't have Commercial. They have a Payor of Grant or Part D. How would I set up a statement that looks for Commerical 1st...then Grant or Part D. I started with this

case when inscomp.payor = 'COMMERCIAL' then INSCOMP.BINNO

View 9 Replies View Related

Column Aliases In Case Statement In Order By

Jan 18, 2007

Hi All,

I have this query :

select col1, col2, col3, col4, col5,..... , (select col99 from tab2) as alias1 from tab1 where <condition>
order by
case @sortby
when 'col1' then col1,
when 'col2' then col2,
when 'col3' then col3,
when 'col99' then col99
end

when i execute the above query it gives me the following error message.

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'col99'.

Thanks in advance.

Thanvi.

View 5 Replies View Related

Order By Case Cast Convert Error

Jul 23, 2005

I have created a SQL Stored Procedure that uses a Case statement todetermine the Order By. For one of the Case statements I am trying toturn a Char field into Datetime in for the Order By, however I can notget it to work. Can someone please take a look and my code below andtell me what I am doing wrong. Thank you.ORDER BYCASE WHEN @SortBy = 'dttm_stamp' THEN dttm_stamp End,CASE WHEN @SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),Event_Date1,101) as datetime) End,CASE WHEN @SortBy = 'FullName' THEN Emp_lastname + ', ' +Emp_firstname End,CASE WHEN @SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +Emp_firstname End DESC,CASE WHEN @SortBy = 'Emp_SSN' THEN Emp_SSN End

View 11 Replies View Related

Error Using Case Expression In Order By Statement

Feb 22, 2008

I have been trying to get the following Selects to work using a case expression in the order by section.

I know I can easily separate out the two statements but I want to do a select using the case statement below ;
however, I keep getting error 16 --"Order by items must appear in the select list if the statement contains a union.

If remove the case statement and put order by "internalID desc" I receive no errors. Moreover, when I take out the union statement and execute the two select statements with each including the case expression it runs as planned.

Can anyone tell what the problem is here? I have combed the web looking for an answer, but it seems that the statement is valid.


Thanks
J
declare @date
set @date = '2001'



select internalID from section_data_v3

union

select internalID from section_data_v4

order by
case when (@date = '2001') then internalID end desc

View 5 Replies View Related

Multiple CASE Statements - Three Fields To Be True In Order To Return Y

Feb 19, 2014

How do I properly write a CASE statement for the following business logic:

I want three of the following fields to be true in order to return 'Y'.

For example:

If field name 'Accepted' = 1 AND field 'StepNo' = '1' and field 'Visit' = 'V1'

Otherwise, I want it to return 'N'.

I have tried the following code below and it is not working.

, CASE WHEN Accepted = '1' AND StepNo ='1' AND Visit ='V1'
THEN 'Y' ELSE 'N' END AS 'StatusQ (Col N)'

View 2 Replies View Related

Using Case Statement To Determine Order By Field And Direction (asc Or Desc) When Using Row_number

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

Transact SQL -- How Do I Case &>?

Dec 8, 2005

Hello, I am having some transact SQL problems.

ALLOWED:

Column1,
CASE FOO
WHEN 1 THEN 'Hello'
ELSE 'Goodbye'
END as MyValue,
Column2


NOT ALLOWED:

Column1,
CASE FOO
WHEN > 1 THEN 'Hello'
ELSE 'Goodbye'
END as MyValue,
Column2


The problem seems to be the > symbol. Apparantly the case statement is only for equality checks, and nothing else.

So, what can I do to fix this? I have these kinds of comparisons all over.

~Le

View 2 Replies View Related

Transact SQL :: Using TOP And ORDER BY

Oct 9, 2015

I have a table where on all business days per month, for different trades, a price is uploaded, so a simplified extract:

Trade   PublishingDate   Price
10       2Jan15               100
:
10       31Jan15             150
20       2Jan15               75
:
20       31Jan15             110
:

I'm trying to write a view that will:

a) Give me the average for each trade, per month.
b) Give me the average of the last 7 days, for each trade, per month.

The first one is easy:
SELECT TradeWSRate.Trade,
CASE MONTH(TradeWSRate.PublishedDate) WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'

[code]...

Column "TradeWSRate.PublishedDate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.How can I change the above select to work?  Ideally I want to do this all in one view, and not use functions.

View 5 Replies View Related

Transact SQL :: CASE Statement For A WHERE

Sep 25, 2015

I've been beating my head against this for a bit and haven't been able to figure this out I want to pull 1 set of values between a date time range if @Report = '1' but if @Report = 2 or 3 I want it to drop 3 of the parameters so it will pull all items. by dropping the where parameters for @BeginRangeDate and @LookOutDate and L.COLineStatus. My report works great if I go in and run Report 1 with the date parameters in the where statement and also works great for Reports 2, 3 if I REM out those 3 items in the Where statement. Here is what I currently have

CASE@Report
WHEN'1'THEN
WHEREL.RequestedShipDate>=@BeginRangeDate
ANDL.RequestedShipDate<=@LookoutDate
ANDDO.ForecastDate>=@ForecastDate

[Code] .....

View 7 Replies View Related

Transact SQL :: How To Use More Than 10 Case Statements

May 12, 2015

I need to breakdown some information with just initials (sometimes 2, sometimes 3 if the initials are already used) SQL Server throws the error of:

Msg 125, Level 15, State 4, Line 1

Case expressions may only be nested to level 10.

What should I alter, or how should I write this query so I can use all of the needed case statements? (their are actually about 24 when statements, but this is just to get a working example to display)

Select
case
when employee_name Like 'Jorge Jones' Then 'JJ'
when employee_name Like 'Mike Mikes' Then 'MM'
when employee_name Like 'Albert Alvarez' Then 'AA'
when employee_name Like 'Hernandez-Sotata' Then 'HS'

[code]....

View 5 Replies View Related

Transact SQL :: Order By Month

Apr 21, 2015

I am using below query i want to order it by month so that data is displayed like

Select (count(I.Colour)) as Litres,
DATEName(MONTH,ShipDate) as [Loaded Date] from InkData I
group by DATEName(MONTH,ShipDate)
order by DATEName(MONTH,ShipDate)

Right now it is not ordering by month, it is ordering alphabetically.I want this to be ordered like

Jan
Feb
March

View 6 Replies View Related

Transact-SQL Help - CASE Statement And Group By

Jul 20, 2005

I've always been mistified why you can't use a column alias in the group byclause (i.e. you have to re-iterate the entire expression in the group byclause after having already done it once in the select statement). I'mmostly a SQL hobbiest, so it's possible that I am not doing this in the mostefficient manner. Anyone care to comment on this with relation to thefollowing example (is there a way to acheive this without re-stating theentire CASE statement again in the Group By clause?):Select 'Age' =CaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))end,max(SubmittedOn), COUNT(SCRID) AS NbrSCRsFrom SCRViewWHERE(StatusSort < 90) ANDCustomerID = 8 andUserID = 133group byCaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))endOrder by max(submittedon) descThanks,Chad

View 4 Replies View Related

Transact SQL :: CASE Expression - NULL

Jun 19, 2015

DECLARE @I1 VARCHAR(5),
@I2 VARCHAR(5)
;
WITH cte
AS (SELECT 1 AS i,
'val1' AS j UNION ALL
SELECT 2,
'val2')

[code]....

Why @i1 is null but not @i2 ? I'm trying to assign values after grouping by the column.

View 17 Replies View Related

Transact SQL :: Grouping With Case Statement

Aug 9, 2015

In the below query, I can get the individual/single group by columns as well as multiple but I cannot control the order in which I would like to group by the data.

So lets say I want to group the data by OS->browser->browser_version(just one example) then I cannot achieve that as the order of OS column comes later in the query.

I know one option would be to write a dynamic SQL but i dont want to do that because of performance reasons. Any other way this can be achieved?

select 
case when @include_browser =
1 then browser_name end as browser_name, 
case when @include_browser_version
= 1 then browser_version end
as browser_version,

[Code] ....

View 4 Replies View Related

Transact SQL :: CASE WHEN - Adding Collate Into It

Aug 20, 2015

Tried a few ways and I can ge this to work at the end in then WHEN part. Just struggling to put this together to be accepted as a CASE WHEN statement, probably missing the obvious.

Case when Postcode like '%[abcdefghijklmnopqrstuvwxyz%]' then 'Lowercase Postcode' else 'Postcode OK' end as [DQPostcode]

collate Latin1_General_CS_AS

Simple terms looking for all instances of Lowercase characters in the Postcode field

View 5 Replies View Related

Transact SQL :: Can Have Multiple Statements Under CASE-THEN

Jun 3, 2015

So I'm thinking if I can have multiple statements within the CASE-THEN..or do I have to CASE out each individually? Kind of like this....

CASE
WHEN [AddressType] = 'M'
THEN [MailingAddress].[Address1]
[MailingAddress].[Address2]
[MailingAddress].[City]
[MailingAddress].[State]
[MailingAddress].[Zipcode]
WHEN [AddressType] = 'D'
THEN [DefaultAddress].[Address1]
[DefaultAddress].[Address2]
[DefaultAddress].[City]
[DefaultAddress].[State]
[DefaultAddress].[Zipcode]

View 3 Replies View Related

Transact SQL :: CASE Expression In WHERE Clause?

Nov 6, 2015

I have a scenario where the End User is going to select two parameters.  @AgentID and @Location If the End User selects @AgentID parameter, in my WHERE clause, I do not want to check the location condition. By default, the agent knows their location already.

If the End User select @Location parameter, in my WHERE clause, I still want to check the Agent condition.

@AgentID parameter would populate either with "All" or their actual agent# (76754 for example).

@Location parameter would populate either with "All" or their actual location (Birmingham or Bessemer for example)Sample of my current WHERE clause:

WHERE
 
 gl.ReceiptDate >= @BeginDate and gl.ReceiptDate < DATEADD(D,1,@EndDate)
 AND gl.AgentId = CASE WHEN @AgentID = 'All' THEN gl.AgentID ELSE @AgentID END
 AND gl.Location = @Location

View 9 Replies View Related

Transact SQL :: Case Statement With Different Columns

Oct 20, 2015

Is it possible to combine a CASE statement with two different columns to retrieve data into one result column? In one column it has multiple JobCode, but this needs to be divided.  The only way I can see to do this is take the data from another column to get the results.Ex. JobCode - has one code for several job descriptions (there are about 30), but all within this code cannot have the same level of access.  So I need to divide them out, and put them in one column for AccessLevel. 

 JobTitle - has one code for one job, (but there are over 100).I want to pull from both columns to get the results I need to assign appropriate access level in one column.

Case JobCode     (they all have same job code, but everyone in this job code should not have same access)
When '45' Then '1' (Principal, Asst. Prin, or any Administrator, Counselors)
When '25' Then '2' (this could be teacher, etc. )      
Case JobTitle   (this is how access should be)
        When '12345' Then '1' (this is Administration only) 

[code]....

View 12 Replies View Related

Transact SQL :: Update Statement With Case

Nov 11, 2015

I am trying to run the below but I get an error of 'Incorrect syntax ')''  --- I have tried every angle I can think of around the parens to fix this but nothing I do is working.

UPDATE abcdefg
SET [Date] = GETDate(),
[readytogo] =
(
CASE WHEN [customername] NOT IN (Select [customername] from [server].[database].[dbo].[view])
THEN 'Yes'
ELSE
'Needs Verification'

[code]....

View 5 Replies View Related

Transact SQL :: Order By A Column From Another Table

Sep 2, 2015

I have two tables, one is called (questions), the second one (answers).

questions columns are (ID,questionTitle)

answers columns are (ID,questionID,answer, answerDate)

I use this query to load data: "SELECT q.questionTitle,COUNT (a.ID),a.answerDate FROM questions q LEFT JOIN answers a ON q.ID=a.questionID" the query is easy, but my problem which I can't solve is how can I fetch the data ordered by the column answerDate, I mean I want the first record to be the one which has the most recent answer and so on.

View 12 Replies View Related

Transact SQL :: Get Active Order Dollars

Sep 18, 2015

This is kind of a follow-up on my previous post "Get 15th and last day trans".  Our client has asked for open sales orders dollars for a given date range to include order dollars active as of the 15th of the month and the last day of the month. 

Below is what I started and I hard-coded the Jan15Open and JanEndOpen so you could see what I am trying to accomplish.  So, for example, if I pass @StartDate of '01-01-2013' and @EndDate of '12-31-2014' I could get back results for 01-15-2013, 01-31-2015, 02-15-2013, 02-28-2013, etc. all the way to 12-31-2014.  Note that the results will depend on the TransDate datetime column.  Once I get the query correct I plan to create a temp table to hold the results so I can report them back in a web page. 

ALTER PROCEDURE [dbo].[afa_selSalesOrdersOpen]
@StartDatedatetime = null,
@EndDatedatetime = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

[Code] ....

View 9 Replies View Related







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