July 24, 2012 at 12:33 am
Hi,
About 8 hours ago, i started to get into SQL as it is a new requirement for work.
We have the tables built and need to create reports for testing using SQL.
I wanted to upload an image but can't as it required source from the web. so i will try to explain (i know i will confuse you unless someone can tell me how to upload directly to this site)
The application is a simple time capture of people hours.
The concerning tables includes Actuals (main source of day to day data, contains dates), Resources (people detail), Classes (what people are. e.g welders), rates (welders rates 01/02/2010 at $10, 15/03/2012 at $20)
So the problem is to get the right rate to the right date depending on the person class
If the welder works between 01/02/2010 to 14/03/2012, then s/he should be paid at $10. From the 15/03/2012 onwards, s/he should be paid at $20.
the results i an looking for is something like this:
Date NameHrsRateTotal
01/02/2010Bob1010xxx
15/07/2011John410xxx
13/03/2012Bob1210xxx
15/03/2012James920xxx
NOTE: this will be across an entire project so hard coding the values in is impractical. don't worry about the total. I am focused on getting the right rate for each record.
So far i have two codes
SELECT actuals.actual_date, resource_class_rates.rate_base
FROM actuals, resource_class_rates, resources, resource_classes
Where actuals.resource_id = resources.resource_id
AND resources.resource_class_id = resource_classes. resource_class_id
AND resource_classes.resource_class_id = resource_class_rates. resource_class_id;
This is a basic code to bring both values from two tables together, not the right results
SELECT DISTINCT resource_class_rates.rate_base
FROM actuals, resource_class_rates, resources, resource_classes
Where resource_class_rates.resource_class_id = '1'
AND resource_class_rates.date_of_effect = '01-Feb-2000';
Second code relates to finding a specific record and pulling the right rate. this works as is. note 1 and 01-FEB-2000 will need to be changed, just testing the code.
When i put them together
SELECT actuals.actual_date, (SELECT DISTINCT resource_class_rates.rate_base
FROM actuals, resource_class_rates, resources, resource_classes
Where resource_class_rates.resource_class_id = '1'
AND resource_class_rates.date_of_effect = '01-Feb-2000'FROM actuals, resource_class_rates, resources, resource_classes) as rates
Where actuals.resource_id = resources.resource_id
AND resources.resource_class_id = resource_classes. resource_class_id
AND resource_classes.resource_class_id = resource_class_rates. resource_class_id;
This kind of work but when i change the 1 and 01-Feb-2000 values to the field names it causes an error as the sql inside the SELECT statement is returning multiple values.
What is the best way to structure the SQL to achieve the required results above.
July 24, 2012 at 1:06 am
You can post images as an attachment of your post. When writing a reply, scroll to the bottom of the window to post options. There you can upload attachments.
Regarding your question: if you want the best help on this site, you need to provide sample data in the form of DML and the tables in the form of DDL (see the first link in my signature).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 24, 2012 at 8:15 pm
Not sure why you're hard coding the Feb date into your queries. The "effective date" should already be in a Rates table.
As Koen suggests, post your DDL and sample data and someone here can surely help you better.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 25, 2012 at 5:19 pm
Members,
Thanks, I used the FEB as i am just learning SQL and was testing the code to see if it works. Below is a similar problem i am facing
http://stackoverflow.com/questions/2306462/sql-join-against-date-ranges
The difference is the above issues only has two tables, if you look at my attached excel or data model, I have 4 tables.(I think this is what you are after) Note that the excel show an example of one person and how data is to be displayed. I did this to make it simple to understand but I would like the SQL to perform across all people with their own rates depending on what class they are.
Please don't send me the actual code, best way to learn is to try and figure it out myself first but would like a guiding hand in terms of how to structure the SQL.
When I really get stuck then further assistance will be greatful.
With the below code,
WITH IndexedExchangeRates AS (
SELECT Row_Number() OVER (ORDER BY Date) ix,
Date,
Rate
FROM ExchangeRates
),
RangedExchangeRates AS (
SELECT CASE WHEN IER.ix=1 THEN CAST('1753-01-01' AS datetime)
ELSE IER.Date
END DateFrom,
COALESCE(IER2.Date, GETDATE()) DateTo,
IER.Rate
FROM IndexedExchangeRates IER
LEFT JOIN IndexedExchangeRates IER2
ON IER.ix = IER2.ix-1
)
SELECT T.Date,
T.Amount,
RER.Rate,
T.Amount/RER.Rate ConvertedAmount
FROM Transactions T
LEFT JOIN RangedExchangeRates RER
ON (T.Date > RER.DateFrom) AND (T.Date <= RER.DateTo)
I believe this SQL is close to my requirements as it compares the rate to the date but it only factors in that the rate table contains data for one set (Class in my terms, ExchangeRate in example terms) where my tables have multiple types of rates in the one table.
First, where and how do I add additional code to make the SQL look for the type of class as well as the rates? (if Iknow how to do this, then the connecting the actual to the resource to the class should be simple)
Second, is there a better way to write the code or is the above a good starting point?
Third,is the below breakdown of the code below the right understanding of how it works?
Code below is about creating a new index and resorting the date in descending order?
IndexedExchangeRates AS (
SELECT Row_Number() OVER (ORDER BY Date) ix,
Date,
Rate
FROM ExchangeRates
Code Below is able comparing the rates to make sure there is a record in there with a date?
RangedExchangeRates AS (
SELECT CASE WHEN IER.ix=1 THEN CAST('1753-01-01' AS datetime)
ELSE IER.Date
END DateFrom,
COALESCE(IER2.Date, GETDATE()) DateTo,
IER.Rate
FROM IndexedExchangeRates IER
LEFT JOIN IndexedExchangeRates IER2
ON IER.ix = IER2.ix-1
Below code is the heart of the SQL doing the calulation once the above code have found the right rate for the right date?
SELECT T.Date,
T.Amount,
RER.Rate,
T.Amount/RER.Rate ConvertedAmount
FROM Transactions T
LEFT JOIN RangedExchangeRates RER
ON (T.Date > RER.DateFrom) AND (T.Date <= RER.DateTo
Fouth, any other advice will be helpful.
Regards
Balgar
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply