I am wondering if there is a way that we could do a nesting case statement in an SQL Query?
This is what I have now... FicaWages = CASE WHEN (UPR00900.FICAMWGS_1 + UPR00900.FICAMWGS_2) >= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0' WHEN UPR00400.SBJTSSEC = 0 THEN '0' ELSE (UPR30100.GRWGPRN - (SELECT SUM(UPR30300.UPRTRXAM) FROM UPR30300 WHERE UPR30300.EMPLOYID = UPR00100.EMPLOYID AND UPR30300.PAYROLCD LIKE '3%' AND AUCTRLCD = 'UPRCC00000007')) END
What I want:
FicaWages = CASE
WHEN
('Sql Statement') = 'GRM'
THEN
CASE
WHEN (UPR00900.FICAMWGS_1+UPR00900.FICAMWGS_2)
>= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0'
WHEN UPR00400.SBJTSSEC = 0 THEN '0'
ELSE (UPR30100.GRWGPRN - ('Sql Statement'))
END
ELSE
CASE
WHEN (UPR00900.FICAMWGS_1+UPR00900.FICAMWGS_2)
>= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0'
WHEN UPR00400.SBJTSSEC = 0 THEN '0'
ELSE (UPR30100.GRWGPRN - ('Sql Statement'))
END
END
I have 2 piece of code designed to do the same thing. My problem is, i'm not getting the same results.
Code 1 where the results are correct
Code:
select Count(*) as TotalCount, Sum(DistAmt) as TotalSum from table1 inner join table2 on table2.id = table1.id where MailTypeID = 3 AND MailEventID = 2 and table1.IsActive = 1
code 2 - Y is correct, but Z is not, and not only is it not correct, but it is returning a number which equals more then the total rows from the table.
Code:
select Y = sum(case when mailtypeid = 3 and maileventid = 2 and IsActive = 1 then distamt else 0 end), Z = count(case when mailtypeid = 3 and maileventid = 2 and IsActive = 1 then 1 else 0 end) from table1 inner join table2 on table2.id = table1.id
Is there a way to use more criteria in a CASE statement other than CASE WHEN expression THEN value ELSE value END
I need to test if the count is greater than 0. If so, then perform the case statement, else return zeros. Currently there are entries where the values are blank. These blank values are causing errors in the application and unfortunately, I am not able to update these values.
So far I have the following, but I am getting an error stating "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference."
Thanks in advance!
Code:
IF @Qid = 4 SELECT @Exp as Status, COUNT(*) AS Total, @CourseID as CourseID,
(SELECT question FROM tableQuestions WHERE qid = @Qid) AS Question,
IF COUNT(*)>0 THEN
1.0 * SUM(CASE WHEN A.Q1 > 1 THEN 1 ELSE 0 END) / COUNT(*) AS Positive, 1.0 * SUM(CASE WHEN A.Q1 = 0 THEN 1 ELSE 0 END) / COUNT(*) AS Neutral, 1.0 * SUM(CASE WHEN A.Q1 < 0 THEN 1 ELSE 0 END) / COUNT(*) AS Negative, 1.0 * SUM(CASE WHEN A.Q1 = 0 THEN 1 ELSE 0 END) / COUNT(*) AS NA ELSE 0 AS Positive, 0 AS Neutral, 0 AS Negative, 0 AS NA
END IF
FROM table1 A INNER JOIN table2 B ON A.SessionID = B.SessionID
WHERE (B.CourseID = @CourseID) AND (A.SubmitDate >= @BeginDate) AND (A.SubmitDate <=@EndDate)
Does the ELSE have to state "WHEN [BG_STATUS] <> 'Blocked' and [BG_STATUS] <> 'Closed', etc? Do I have to delineate for the ELSE statement everything that BG_STATUS is *not* equal to? Seems there ought to be a way but I can't find it.
SELECT BG_SEVERITY AS 'Severity', SUM(CASE WHEN [BG_STATUS] = 'Blocked' THEN 1 ELSE 0 END) as 'Blocked', SUM(CASE WHEN [BG_STATUS] = 'Closed' THEN 1 ELSE 0 END) as 'Closed', SUM(CASE WHEN [BG_STATUS] = 'Customer Test' THEN 1 ELSE 0 END) as 'Customer Test', SUM(CASE WHEN [BG_STATUS] = 'Deferred' THEN 1 ELSE 0 END) as 'Deferred',
I am creating a stored procedure which receives the following 4 variables. However, VacancySectorID is the only madatory variable.
@VacancySectorID int = NULL, (mandatory) @VacancyRegionID int = NULL, optional) @VacancyTypeID int = NULL, (optional) @VacancyKeywords nvarchar(64) = NULL, (optional)
My objective is to dynamically build the WHERE statement which in turn will return either one or more of the variables and the required data. The following code works for a single if else but it appears that you cannot have more than one if else statement.
Anyones input would be greatly appreciated.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Public_GetVacancyListingBySearchCriteria] ( @VacancySectorID int = NULL, @VacancyRegionID int = NULL, @VacancyTypeID int = NULL, @VacancyKeywords nvarchar(64) = NULL ) AS --SET NOCOUNT ON; IF @VacancySectorID IS NULL AND @VacancyRegionID Is NULL AND @VacancyTypeID IS NULL AND @VacancyKeywords IS NULL BEGIN SELECTviewVacancies.* FROMviewVacancies WHEREVacancyActive = 1 AND VacancySectorID = @VacancySectorID ORDER BYVacancyPosted DESC, VacancyTitle END ELSE BEGIN SELECTviewVacancies.* FROMviewVacancies WHEREVacancyActive = 1 ORDER BYVacancyPosted DESC, VacancyTitle END
whats the most efficient way to do this? I have a column that contains three values. A, B, C If colVal = A add one to batch a, if B add one to batch b...ect . Then display all three values
hi, I want to know how can i equate the following case statements into one Condition? Following is my Code
(case when ws.Action_Taken_ID not in(3,17,18) then '-1' else '0' end) as istechfcr, (case when datediff(day,dbo.Usp_Get_Date(pr.Set_Serial_Number),oh.WO_Record_Date) <= q.Product_Gurantee_Months + 6 then '-1' else '0' end) as istechfcr, (case when substring(ltrim(rtrim(ws.Symptom)),1,1) not in('X') then '-1' else '0' end) as istechfcr, (case when ws.Action_Taken_ID not in(8,15,21,25) then '-1' else '0' end) as istechfcr,
Hi all,I have to translate an Access query into sql. The query has thefollowing statement. I know SQL doesn't support iif, so can someone tellme how to use the case statement to get the same result?select field1,IIf(Grand_total-50>0, grand_total-50, 0) AS field2,field3Thanks.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I'm trying to make a view that uses organization name from one tableand contact first and last name from another table. In the view Ihave a field that I want to show Organization followed by the maincontact. Problem is if the organization field or name field is NULLthen it doesn't show anything. If one field is empty I still want itto show the other field in the table.Example:Org1--ContactOrg2--ContactOrg3 (Still shows org even without a contact name)Contact (Still shows contact even without an org name)Tried using a CASE statement but didn't work.
Does anyone know if there is a way to look at a value in a variable and based on that value run different Data Flow Tasks?
For example, let's say I have an SSIS package that contains a variable named Client and 3 separate Data Flow Tasks. I would like to do this: if Client = 1 then run Data Flow 1 else if Client = 2 then run Data Flow 2 else run Data Flow 3.
I'am trying to program a nested CASE statements (if this is not possible, does anyone have any alternate suggestions ?) and I'm getting syntax errors. The statement is:
SELECT @cmdLine = CASE @BackupType WHEN 1 THEN Select @tmpStr = CASE @initFlag WHEN 1 THEN 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH INIT, NOUNLOAD, NAME = ' + @backupJobName + ' , SKIP , STATS = 10, NOFORMAT' ELSE 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH NOINIT, NOUNLOAD, NAME = ' + @backupJobName + ' , SKIP , STATS = 10, NOFORMAT' END WHEN 2 THEN Select @tmpStr = CASE @initFlag WHEN 1 THEN 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH DIFFERENTIAL, INIT , NOUNLOAD, NAME = ' + @backupJobName + ', SKIP, STATS = 10, NOFORMAT' ELSE 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH DIFFERENTIAL, NOINIT , NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT' END WHEN 3 THEN Select @tmpStr = CASE @initFlag WHEN 1 THEN 'BACKUP Log ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH INIT, NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT' ELSE 'BACKUP LOG ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH NOINIT, NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT' END ELSE '' END
Is there a way of improving the SUM statements below? When I run the query without them it's very fast but with them it takes ages
SELECT r.dbPatID, p.dbPatFirstName, p.dbPatLastName, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName , SUM(CASE WHEN t.LedgerAmount > 0 AND t.LedgerType !=29 AND t.LedgerType !=30 AND t.LedgerType != 31 AND t.LedgerType != 1 OR t.LedgerType = 16 THEN t.LedgerAmount ELSE 0.00 END) AS Charges, SUM(CASE WHEN t.LedgerAmount < 0 AND t.LedgerType != 1 AND t.LedgerType != 16 AND t.LedgerType != 45 OR t.LedgerType = 29 OR t.LedgerType = 30 OR t.LedgerType = 31 THEN t.LedgerAmount ELSE 0.00 END) AS Payments,
Hi i am having some trouble with a nested case statement, what i want to do is set the value of a new column called Result depending on a series of case statements. Basically i want to check Test.Webstatus = 'Rd' and FinalResult = 'true' if this is true i want it to set the value in the Results field to ReportableResult + '~' + ReportableUnitDisplay then go through all the limits fields adding either the value of the field or 'blank' onto the end of the value in the Results field, depending on if the limits field has Null or a value in it. Producing a value in the Results field similiar to: 10~kg:10:5:2:1 or 10~kg:blank:5:blank:1 etc
select ClientRef, Sample.WebStatus as SampleStatus, Analysis, FinalResult, Test.WebStatus, 'Result' = Case when Test.WebStatus = 'Rd' and FinalResult = 'true' then Case Case when UpperCriticalLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank' else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperCriticalLimit end Case when UpperWarningLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank' else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperWarningLimit end Case when LowerWarningLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank' else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperWarningLimit end Case when LowerCriticalLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank' else ReportableResult + '~' + ReportableUnitDisplay + ':' + LowerCriticalLimit end end when FinalResult = 'false' then Null else Test.WebStatus from Job inner join sample on Job.JobID = Sample.JobID inner join Test on Sample.SampleID = Test.SampleID left join Result on Test.TestID = Result.TestID
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
Hi, I have this update statement that works, it updates the totalamount to calc amount, but I want to update totalamount only when it is not equal to calcamt.I have tried many things but in vain.Can some one please help me. How do i use case statements to update only when totalamount!=calcamt.
update c set totalamount= calcamt from Prepay c JOIN (select sum(amt) as calcamt, payid from pay group by payid )b ON b.payid= c.payid where c.cust_no='somenum'
This query brings back 2 rows for each record, one for each case statement. How can I change this to bring in the contsupp.notes for both types of data? Or could it be fixed in the joins?
select contact1.accountno, contact1.key5 as "Ch#", contact2.uexpsort as "Sort", contact1.company as "Church", contact1.contact as "Editor", contact1.phone1 as "Phone", contact2.uemerg as "Emergency", contact1.Address1 as "Address", contact1.city as "City", contact1.state as "State", contact1.zip as "Zip", contact2.ubulletnqt as "Qty", contact2.ubullcolor as "BullColor", contact2.ubarcode as "Barcode", contact2.udelivery as "Delivery", contact2.uoutputdev as "OutputDev", contact2.utimedeliv as "Time", contact2.ucolor as "DelivColor", contact2.ucollated as "Collated", contact2.ustapled as "Stapled", case when contsupp.contsupref = 'Delivery Notes' then contsupp.notes end as "Deliverynotes", case when contsupp.contsupref = 'Cover Change Slip' then contsupp.notes end as "CoverChangeSlip"
from Contact1 inner join contact2 on Contact1.Accountno = Contact2.Accountno inner join contsupp on Contact1.Accountno=Contsupp.Accountno where contact1.key5 not like '' and contsupp.contsupref='Delivery Notes' or contsupp.contsupref ='Cover Change Slip' order by contact1.key5
Is it possible to use nested case statements in the SQL for your dataset when you are using Reporting Services? I keep getting an error saying "Unable to parse expression" and my report won't run.
I know this is quite a complex statement, so at first I was worried that maybe I had brackets in the wrong places, but I've been through the code and made sure that every opening bracket has an equivalent closing bracket, and everything appears to be OK in that respect. So I'm thinking that maybe the structure of my nested case statements is incorrect? The inner case statement is necessary to calculate whether a transaction is due, overdue or not due. The outer case statement depends on the result of the inner case statement.
Basically, we only want the calculations following the "THEN" in the outer case statement to be executed if the result of the inner case statement is "not due" and Field2 is greater than zero. If either of those criteria aren't met, then it should go straight to the ELSE NULL END statement and stop.
Hi, I have this update statement that works, it updates the totalamount to calc amount, but I want to update totalamount only when it is not equal to calcamt.I have tried many things but in vain.Can some one please help me. How do i use case statements to update only when totalamount!=calcamt.
update c set totalamount= calcamt from Prepay c JOIN ( select sum(amt) as calcamt, payid from pay group by payid )b ON b.payid= c.payid where c.cust_no='somenum'
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'
I know I should know the answer to this, but I just can't quite get the syntax down
Code: Select case when zipCode = '10185' Then 'Deliver' Else when zipCode = '2309' And paid = 'Yes' Then 'Deliver' Else When zipCode = '1291' And paid = 'Yes' Then 'Deliver' Else When zipCode = '88221' And paid = 'No' Then 'Hold' Else when zipCode = '34123' Then 'Deliver' End From postalDeliveryDatabase
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]
Hi I have a stored procedure and i am trying to add case statements to them.. but i am getting an Error. which is Msg 125, Level 15, State 3, Procedure udf_EndDate, Line 34 Case expressions may only be nested to level 10.
And This is my sproc-- ================================================ -- Template generated from Template Explorer using: -- Create Scalar Function (New Menu).SQL -- -- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below. -- -- This block of comments will not be included in -- the definition of the function. -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:<Author,,Name> -- Create date: <Create Date, ,> -- Description:<Description, ,> -- ============================================= Create FUNCTION [dbo].[udf_EndDate] (@PeriodId int) RETURNS datetime AS
BEGIN DECLARE @Month int, @Year char(4)
SELECT @Month = [Month], @Year = Cast([Year] as char(4)) FROM Period WHERE PeriodId = @PeriodId
RETURN CASE @Month WHEN 1 THEN '1/31/' + @Year ELSE CASE @Month WHEN 2 THEN '2/28/' + @Year ELSE CASE @Month WHEN 3 THEN '3/31/' + @Year ELSE CASE @Month WHEN 4 THEN '4/30/' + @Year ELSE CASE @Month When 5 Then '5/31/' + @Year ELSE CASE @Month When 6 Then '6/30/' + @Year ELSE CASE @Month When 7 Then '7/31/' + @Year ELSE CASE @Month When 8 Then '8/31/' + @Year ELSE CASE @Month When 9 Then '9/30/' + @Year ELSE CASE @Month When 10 Then '10/31/' + @Year ELSE CASE @Month When 11 Then '11/30/' + @Year ELSE CASE @Month When 12 Then '12/31/' + @Year ELSE null END END END END END END END END END END END END END
I am using Visual Web Developer Express 2005 as a test environment. I have it connected to a SQL 2000 server. I would like to use a Select Case Statement with the name of a column from a SQL Query as the Case Trigger. Assuming the SQLDataSource is named tCOTSSoftware and the column I want to use is Type, it would look like the following in classic ASP: Select Case tCOTSSoftware("Type") Case 1 execute an SQL Update Command Case 2 execute a different SQL Update Command End Select What would a comparable ASP.Net (Visual Basic) statement look like? How would I access the column name used in the SQLDataSource?
I am trying to gather counts for table imports made for files from friday - sunday and create a variable that will be the body of an email. I'd like to use either a case statement or a while statement since the query is the same but the values must be collected for each day (friday, saturday and sunday) and will all be included in the same email.
I have declared all variables appropriately but I will leave that section of the code out.
Select @ifiledate = iFileDate from tblTelemark where iFileDate = CASE WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-3, 101), '/','') THEN
Select @countfri1 = Count(*) from tbl1 Select @countfri2 = Count(*) from tbl2 Select @countfri3 = Count(*) from tbl3 Select @countfri4 = Count(*) from tbl4
WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-2, 101), '/','') THEN Select @countsat1 = Count(*) from tbl1 Select @countsat2 = Count(*) from tbl2 Select @countsat3 = Count(*) from tbl3 Select @countsat4 = Count(*) from tbl4
WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-1, 101), '/','') THEN Select @countsun1 = Count(*) from tbl1 Select @countsun2 = Count(*) from tbl2 Select @countsun3 = Count(*) from tbl3 Select @countsun4 = Count(*) from tbl4
SELECT COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [New Visitors], COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [Returning Visitors] FROM content_hits_tbl WHERE (hit_date BETWEEN DATEADD(mm, - 1, GETDATE()) AND GETDATE())
=======================
How do I add up both COUNT/CASE columns? Would it be: SUM([New Visitors] + [Returning Visitors]) AS Total
I tried this and it doesn't work. I get invalid column names error for both.
I have even tried: SUM([COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)] + [COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)]) AS Total
You would think that there would be some gui functionality in VS08 that would do this...
I have a table that keeps track of all changes that were performed in an application. There is a column called "old value" and column called "new value". There are some values in the application that don't require data therefore the "old value" or "new value" values can be empty. These columns are an nvarchar data type because the value can be text or numbers or dates. An example is "ReceivedDate". There is a report that is generated based on this table.
What is happening is the query in the report dataset is adding dates when it should be displaying empty. They query is using "CASE/WHEN/THEN". What I need is "When the column is "RecievedDate" and it is not null then convert it to a date". This is for formatting purposes.
This is an example of the table:
UpdateColumn Old Value New Value ReceivedDate 7/8/2015 5:00:00 AM ReceivedDate 7/8/2015 12:00:00 AM 7/9/2015 5:00:00 AM ReceivedDate 7/9/2015 12:00:00 AM
So, the first time it was updated there was no value but it was replaced with July 8, 2015 and so on.This is what the report is displaying
This is the query:
CASE UpdateColumn ... WHEN 'ReceivedDate' THEN (replace(convert(varchar(11),CONVERT ( date, oldvalue ), 106), ' ', '-') ) ... I tried adding CASE UpdateColumn ... WHEN 'ReceivedDate' IS NOT NULL THEN (replace(convert(varchar(11),CONVERT ( date, oldvalue ), 106), ' ', '-') ) ...
I would like to know how I can add the following sample code to my Source data on Data Flow on SSIS, or what other options there are. The main issue is time as we have talking about 100's of millions of rows
select Sample, CASE WHEN Sample IS NULL THEN NULL WHEN SUBSTRING(Sample, 1, 6) IS NULL THEN ' ' ELSE RTRIM(SUBSTRING(Sample, 1, 6)) END AS [Sample_1_6] from TestTable
what I have done at this stage is just to Create a SQL task with a Insert into
INSERT INTO [dbo].[TestTable1] ([Sample] ,[Sample_1_6]) select Sample, CASE WHEN Sample IS NULL =THEN NULL WHEN SUBSTRING(Sample, 1, 6) IS NULL THEN ' ' ELSE RTRIM(SUBSTRING(Sample, 1, 6)) END AS [Sample_1_6] from TestTable
If there is a way adding this to a dataflow so I van use fast load that would really be the best solution. I know there are derived columns, but would this really be faster than the straight insert into in a SQL Task? If this is the way to go what is the code I would use in the derived column or any other option.
I have a question on date manipulation functions and CASE statements
My sql is passed the following parameter's and performs a select using a manipulation on these date param's to get a start and end date range depending on the conditions;-
monthColHeader = eg 'Feb 2015' defaultStartDate and defaultEndDate filterStartDate and filterEndDate.
These are my conditions;-
if defaultStart and End = filterStart and End use monthColHeader for the date range if defaultStart and End != filetrStart and End AND the month/year of filterStart and filterEnd match then use the filterStart & End month/Year with the monthColHeader to get the date range if defaultStart and End != filetrStart and End AND the month/year of filterStart and filterEnd DON't match use filterStart Day and monthColHeader for our start date and monthColHeader for our end date.
When I say use monthColHeader I mean like this;-
(r.dbAddDate >= (CAST('@Request.monthColHeader ~' AS DATETIME)) AND r.dbAddDate < DATEADD(mm,1,'@Request.monthColHeader ~'))
This sql works for converting say 'Feb 2015' to '2015-02-01' & '2015-02-28'....
I am using SQL Server 2005 and fairly new at using SQL Server. I am having problems using a Case statements in the select list while have a group by line. The SQL will parse successfully but when I try to execute the statement I get the following error twice :
Column 'dbo.REDEMPTIONHISTORY.QUANTITY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Below is the my sql statement:
SELECT dbo.DateOnly(TH.TransactionDate) AS RptDate, RH.Item,
ItemRef =
Case
when RH.Quantity < 0 then Sum(RH.Quantity)
when RH.Quantity >= 0 then Sum(0)
end
FROM dbo.RHISTORY AS RH INNER JOIN dbo.TRANSHISTORY AS TH ON RH.TRANSACTIONID = TH.TransactionID
WHERE (dbo.DateOnly(TH.TransactionDate) BETWEEN '10-1-2007' AND '10-5-2007') AND (RH.TransactionCode IN (13, 14, 15, 16))
Group by dbo.DateOnly(TH.TransactionDate), RH.Item
The TransHistory table contains, primary key transactionid, TransactionDate and the RHistory contains all the details about the transaction, the RHistory table is joined to the TransHistory table by foreign key TransactionID. I am trying to get totals for same item on the same day.
Any help will be greatly appreciated. I am also having trouble using If..Then statements in a select list and can not fin the correct syntax to use for that.
Hello. I'm trying to reduce some code in my stored procedure and I'm running into lots of errors. I'm somewhat of a novice with SQL and stored procedures so any help would be beneficial. I have a SP that gets a page of user data and is also called when sorting by one of the columns (this data is placed in a repeater, btw). I quickly learned that I wasn't able to pass in string parameters the way I had hoped in order to handle the ORDER BY and direction (ASC/DESC) so I'm trying to work around this. So far I've tried the following with many errors.WITH Users AS ( SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @OrderBy='FirstName' AND @Direction='DESC' THEN (FirstName + ' DESC') WHEN @OrderBy='FirstName' THEN FirstName WHEN @OrderBy='LastName' AND @Direction='DESC' THEN (LastName + ' DESC') WHEN @OrderBy='LastName' THEN LastName END ) AS Row, UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName FROM vRF_Users )
SELECT UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName FROM Users WHERE Row BETWEEN @StartRowIndex AND @EndRowIndex
I've tried a combination of similar things with parenthesises, without, doing "THEN FirstName DESC" without concatenating anything, etc. I also tried: DECLARE @OrderByDirection varchar(32) DECLARE @DESC varchar(4) SET @DESC = ' DESC'
IF @Direction = 'DESC' BEGIN SET @OrderByDirection = (@OrderBy + @DESC) END And then writing my case statemet like this:ORDER BY CASE WHEN @Direction='DESC' THEN @OrderByDirection ELSE @OrderBy ENDObviously this didn't work either. Is there any way to gracefully accomplish this or do I just have to use a bunch of if/else statements and lots of redundant code to evaluate all my @OrderBy and @Direction parameters???
