February 4, 2014 at 9:17 am
Hello,
how can I write a sql select statement using case
I have a column called type id which contains id's of 34,35,36
I need a case expression for this in sql select statement
-->labor amount / fresh fruits + labor amount +small supplies * 100
for labor amount - 35
fresh fruits- 34
small supplies - 36
February 4, 2014 at 10:06 am
Please read the first article in my signature and post the missing information. We need a create table statement, some sample data and the expected outcome. Thanks
February 4, 2014 at 10:17 am
Please find the attachment for the database records, and below is the select statement
SELECT
G.FiscalYear,G.SystemID, I.SchoolID,SUM(I.AMOUNT) as 'invoiceamt1',
CAST (SUM(ISNULL(I.AMOUNT,0) - ISNULL(I.OtherAmount,0)) AS NUMERIC(18,2)) AS 'invoiceamt',
datename(month,dateadd(month,MONTH(R.FundMonth),0)-1) as 'FundMonthName',
G.SystemID+'-'+VS.SystemName as 'SystemName',I.SchoolId+'-'+VS.SchoolName as 'SchoolName',
ReimbursementTypeID, L.LookupValueName,convert(varchar(30),G.StartDate,107) + ' to ' + convert(varchar(30),G.EndDate,107) AS 'GrantsPeriod',SC.OperatingDay
FROM grantsystem G
INNER JOIN request R on G.Grantsystemid = R.GrantSystemID
INNER JOIN invoice I on R.requestid = I.requestid
INNER JOIN SchoolConfig AS SC ON G.SystemID = SC.SystemID AND G.FiscalYear = SC.FiscalYear
INNER JOIN vlookups L on I.Reimbursementtypeid = L.lookupid
INNER JOIN vSchool VSon G.SystemID = VS.SystemID AND I.SchoolID = VS.SchoolID
WHERE G.fiscalyear=@FiscalYear AND G.SystemID = @SystemId AND
I.SchoolID=Case When @SchoolId ='0' Then I.SchoolID Else @SchoolId End AND
MONTH(R.FundMonth)=Case When @FundMonth='0' Then MONTH(R.FundMonth) Else CONVERT(INT,@FundMonth) End
AND R.requesttypeID = @ReqStatus AND
SC.SchoolID=Case When @SchoolId ='0' Then SC.SchoolID Else @SchoolId End AND
(MONTH(SC.FundMonth) = @FundMonth)
AND (I.SchoolID=Case When @SchoolId ='0' Then I.SchoolID Else @SchoolId End )
AND (I.IsActive=1) AND (R.IsActive=1) AND (G.IsActive=1) AND (SC.IsActive = 1)
GROUP BY G.FiscalYear, G.SystemID, I.SchoolID, R.FundMonth,VS.SystemName,VS.SchoolName,
ReimbursementTypeID,L.LookupValueName,sc.OperatingDay,G.StartDate,G.EndDate
ORDER BY fiscalyear, SystemID, SchoolID, r.FundMonth, ReimbursementTypeID,sc.OperatingDay,G.StartDate,G.EndDate
END
February 4, 2014 at 12:54 pm
Please take the time to read the article that Keith referenced. We can't do anything with this big query you posted. We have no idea what the tables and data are like. Then you posted a picture of...well we don't know what that is. Is that sample data? Is that the desired output? What is that query supposed to do? In short, you haven't provided anywhere near enough information for anybody to be able to help.
Here is the basic syntax for a case expression. How that might relate to your situation is anybody's guess.
case [something to evaluate]
when [some condition] then [some value]
...
else [optional default value]
end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 4, 2014 at 2:02 pm
Ditto what Sean said. There's many people who want to help but we just can't based on what you have provided thus far.
That said, I am going to take a shot in the dark here... Perhaps you are looking for something like this?
DECLARE @yourtable TABLE
(invoiceAmt money not null,
FundMonthName varchar(16) not null,
SystemName varchar(30) not null,
SchoolName varchar(100) not null,
ReimbursementTypeID int not null,
LookupValueName varchar(30) not null);
INSERT @yourtable
SELECT 3073.75, 'October', '648-Dougleas County', '0184-Dorsett Shoals...', 34,'Fruits and Veggies' UNION ALL
SELECT 366.46, 'October', '648-Dougleas County', '0184-Dorsett Shoals...', 35,'Labor' UNION ALL
SELECT 52.72, 'October', '648-Dougleas County', '0184-Dorsett Shoals...', 36,'Small Supply' UNION ALL
SELECT 68.21, 'October', '648-Dougleas County', '0184-Dorsett Shoals...', 37,'Labor';
--SELECT * FROM @yourtable;
WITH rbtype AS
(SELECT
MAX(CASE WHEN ReimbursementTypeID=34 THEN invoiceAmt END) AS fruitVeg,
MAX(CASE WHEN ReimbursementTypeID=36 THEN invoiceAmt END) AS SmallSupply,
MAX(CASE WHEN ReimbursementTypeID IN (35,37) THEN invoiceAmt END) AS labor
FROM @yourtable)
SELECT labor/fruitVeg + labor + SmallSupply * 100
FROM rbtype;
Note that I am using a temp variable (@yourtable) so you can copy/paste what I put together and test it locally. Again, this is a shot in the dark but might help get you closer. 😉
Minor Edit: code alignment
-- Itzik Ben-Gan 2001
February 5, 2014 at 7:23 am
Thank You so much...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply