June 21, 2017 at 2:24 pm
Hi,
I'm a relative newbie on the SQL front and as such I'd be grateful if someone could give me a few pointers on the following queries. I've pulled together the following query but I'd like to tweak it to achieve the following:
SELECT [FinancialYear]
,[EvidencedSpend]
,[Role Category]
,RTRIM([Role Classification]) AS 'Role Classification'
,[Unit of Purchase]
,[No of Chargeable Days/Hours]
,[Price per Unit]
,[Temporary Worker pay Rate ]
,[Supplier Fee Percentage Applied if applicable ]
,[Supplier Fee Charged Per Day £]
,[Management Fee Charged Per Hour / Day £]
,[Expenses/Disbursements]
FROM [MISO_Views].[mi].[MI_RMXXX]
WHERE FinancialYear IN ('2015/16','2016/17','2017/18') AND NOT LotNumber = '0'
Thanks in advance,
Snook
June 21, 2017 at 3:22 pm
The_Snook - Wednesday, June 21, 2017 2:24 PMHi,I'm a relative newbie on the SQL front and as such I'd be grateful if someone could give me a few pointers on the following queries. I've pulled together the following query but I'd like to tweak it to achieve the following:
SELECT [FinancialYear]
,[EvidencedSpend]
,[Role Category]
,RTRIM([Role Classification]) AS 'Role Classification'
,[Unit of Purchase]
,[No of Chargeable Days/Hours]
,[Price per Unit]
,[Temporary Worker pay Rate ]
,[Supplier Fee Percentage Applied if applicable ]
,[Supplier Fee Charged Per Day £]
,[Management Fee Charged Per Hour / Day £]
,[Expenses/Disbursements]
FROM [MISO_Views].[mi].[MI_RMXXX]
WHERE FinancialYear IN ('2015/16','2016/17','2017/18') AND NOT LotNumber = '0'
- Return the 'EvidencedSpend' field as 2 d.p.
- Insert a calculated field so that if the 'Unit of Purchase' = 'Hour' it returns 8 * 'Price per unit' field otherwise return what is in the 'Price per unit' field which will be the day rate.
- One of the categories in the 'Role Category' field is listed twice because for one of the financial years it is input as "Finance, Audit & Accountancy" whereas in the other FY's it is 'Finance, Audit & Accountancy' (no speech bubbles). Is it possible to cleanse the data in this field so that the speech bubbles are omitted?
Thanks in advance,
Snook
I would offer a few suggestions. First, if at all possible change your column names so there are no spaces or reserved words. Those are just a nightmare to deal with and you have to constantly wrap them in [brackets][which][makes][typing][and][reading][far][more][difficult].
Return the 'EvidencedSpend' field as 2 d.p.
- What does that mean?
Insert a calculated field so that if the 'Unit of Purchase' = 'Hour' it returns 8 * 'Price per unit' field otherwise return what is in the 'Price per unit' field which will be the day rate.
- You can do this with a case expression.
Case [Unit of Purchase] when 'Hour' then 8 else 1 end * [Price per unit]
One of the categories in the 'Role Category' field is listed twice because for one of the financial years it is input as "Finance, Audit & Accountancy" whereas in the other FY's it is 'Finance, Audit & Accountancy' (no speech bubbles). Is it possible to cleanse the data in this field so that the speech bubbles are omitted?
HUH??? What is a speech bubble and how is that relevant here?
The absolute best thing you could do to get help would be to read the first link in my signature for best practices when posting questions.
_______________________________________________________________
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/
June 21, 2017 at 3:35 pm
On the 2 d.p., I'm assuming 'two decimal places' is the intent.
For that, you'll just need to do an appropriate CAST/CONVERT to a DECIMAL with a scale of 2 and whatever precision is appropriate for your data.
To know exactly how to do that would require knowing more about your data (is everything starting as an integer, and you need to introduce decimal places, or does it currently have a scale>4 that you need to reduce? How do you want to handle rounding? etc.)
On the speech bubbles bit, do you just mean a double quotation mark character (i.e., SELECT CHAR(34))? If so, and that character isn't a legitimate part of any other Role Category, then you should be able to use REPLACE to replace the double quotation mark with an empty string.
A lot of guessing going into this, though 🙂 We'll really need some clarification from you; providing sample data and such as in the link Sean mentioned would be great.
Cheers!
EDIT: Added my guess on the speech bubbles.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply