June 20, 2014 at 5:03 am
I have a table with large data. The structure along with the sample data of the table is as follows:
CREATE TABLE #SampleData
(
BaseYear INT,
TransYear INT,
ClientName VARCHAR(50),
DepID INT,
Revenue, INT,
TransactionDate DATETIME
)
INSERT INTO #SampleData
SELECT 2009,2013,'Robert Noble',1077,2356,'2015-03-16 00:00:00.000' UNION
SELECT 2011,2013,'Cally Atkins',1038,3265,'2009-11-01 00:00:00.000' UNION
SELECT 2008,2010,'Ava Evans',1020,3526,'2012-04-20 00:00:00.000' UNION
SELECT 2009,2011,'Riley Bennett',1095,3214,'2011-12-10 00:00:00.000' UNION
SELECT 2012,2013,'Wilma Hatfield',1087,2415,'2010-10-26 00:00:00.000' UNION
SELECT 2008,2008,'Roary Tran',1031,2411,'2009-09-22 00:00:00.000' UNION
SELECT 2009,2009,'Austin Mcintosh',1042,5656,'2013-12-06 00:00:00.000' UNION
SELECT 2009,2009,'Samuel Franco',1027,3256,'2010-03-29 00:00:00.000' UNION
SELECT 2010,2012,'Rose Cooper',1008,3562,'2010-05-06 00:00:00.000' UNION
SELECT 2008,2010,'Sage Myers',1095,2541,'2008-08-07 00:00:00.000'
Another table is the Target table:
CREATE TABLE #Target
(
StartDate DATETIME,
EndDate DATETIME,
targetValue INT
)
INSERT INTO #Target
SELECT '2008-06-01 00:00:00.000', '2014-06-30 00:00:00.000', 5000
Requirement is to get the data from #SampleData table for the selected range (Start Data and End Date will be passed by the user that will be used against TransactionDate). The user will pass Start Date, End Date, Number of Records and Period. Period can be daily, weekly, monthly, quarterly or yearly. So in simple words the data has to be returned for the given time period within the given range. For example the query should return data from #SampleData table for transactionDate between June 05, 2010 and June 30, 2014 for last 5 weeks(or days, months quarters or years).
To make things complicated if the data contains date that falls between the StartDate and EndDate from #Goal table there should be one more column returned 'targetValue' with the actual goalValue from the #Target table.
Can someone help in building the required query?
June 20, 2014 at 5:42 am
KnowledgeSeeker (6/20/2014)
I have a table with large data. The structure along with the sample data of the table is as follows:CREATE TABLE #SampleData
(
BaseYear INT,
TransYear INT,
ClientName VARCHAR(50),
DepID INT,
Revenue, INT,
TransactionDate DATETIME
)
INSERT INTO #SampleData
SELECT 2009,2013,'Robert Noble',1077,2356,'2015-03-16 00:00:00.000' UNION
SELECT 2011,2013,'Cally Atkins',1038,3265,'2009-11-01 00:00:00.000' UNION
SELECT 2008,2010,'Ava Evans',1020,3526,'2012-04-20 00:00:00.000' UNION
SELECT 2009,2011,'Riley Bennett',1095,3214,'2011-12-10 00:00:00.000' UNION
SELECT 2012,2013,'Wilma Hatfield',1087,2415,'2010-10-26 00:00:00.000' UNION
SELECT 2008,2008,'Roary Tran',1031,2411,'2009-09-22 00:00:00.000' UNION
SELECT 2009,2009,'Austin Mcintosh',1042,5656,'2013-12-06 00:00:00.000' UNION
SELECT 2009,2009,'Samuel Franco',1027,3256,'2010-03-29 00:00:00.000' UNION
SELECT 2010,2012,'Rose Cooper',1008,3562,'2010-05-06 00:00:00.000' UNION
SELECT 2008,2010,'Sage Myers',1095,2541,'2008-08-07 00:00:00.000'
Another table is the Target table:
CREATE TABLE #Target
(
StartDate DATETIME,
EndDate DATETIME,
targetValue INT
)
INSERT INTO #Target
SELECT '2008-06-01 00:00:00.000', '2014-06-30 00:00:00.000', 5000
Requirement is to get the data from #SampleData table for the selected range (Start Data and End Date will be passed by the user that will be used against TransactionDate). The user will pass Start Date, End Date, Number of Records and Period. Period can be daily, weekly, monthly, quarterly or yearly. So in simple words the data has to be returned for the given time period within the given range. For example the query should return data from #SampleData table for transactionDate between June 05, 2010 and June 30, 2014 for last 5 weeks(or days, months quarters or years).
To make things complicated if the data contains date that falls between the StartDate and EndDate from #Goal table there should be one more column returned 'targetValue' with the actual goalValue from the #Target table.
Can someone help in building the required query?
I think there is a flaw in your business rule.
"For example the query should return data from #SampleData table for transactionDate between June 05, 2010 and June 30, 2014 for last 5 weeks(or days, months quarters or years)."
Should read
"For example the query should return data from #SampleData table for transactionDate between June 05, 2010 and June 30, 2014 OR for last 5 weeks(or days, months quarters or years)."
How can it be both?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 20, 2014 at 5:54 am
No. The requirement is quite clear. If the user wants to find last 5 week's data from the table and he has the upper range for date say, June 10, 2010. He will be passing the dates and the data will be selected for last 5 weeks ending on June 10, 2010
June 20, 2014 at 6:01 am
KnowledgeSeeker (6/20/2014)
No. The requirement is quite clear. If the user wants to find last 5 week's data from the table and he has the upper range for date say, June 10, 2010. He will be passing the dates and the data will be selected for last 5 weeks ending on June 10, 2010
For this example you have quoted: -
For example the query should return data from #SampleData table for transactionDate between June 05, 2010 and June 30, 2014 for last 5 weeks
(or days, months quarters or years).
What range of dates should be included in the result?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 20, 2014 at 6:06 am
Sorry if the requirement is not clearly mentioned(English is not my first language). In the example I gave the dates the user passed are June 05, 2010 and June 30, 2014 and he has selected 5 weeks. So the data should be available for last 5 weeks ending on June 30, 2014(Last 5 weeks from June 30, 2014)
Hope it makes more sense now 🙂
June 20, 2014 at 6:08 am
The user will pass Start Date, End Date, Number of Records and Period. Period can be daily, weekly, monthly, quarterly or yearly.
The user will pass Start Date,End Date,No of Records(5),Weekly
which I think translates to get the last 5 weeks records for the given date range.
If user passes Start Date,End Date,No of Records(10),Quarters it would translate to
Get the last 10 quarters records for the given date range.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 20, 2014 at 6:12 am
KnowledgeSeeker (6/20/2014)
Sorry if the requirement is not clearly mentioned(English is not my first language). In the example I gave the dates the user passed are June 05, 2010 and June 30, 2014 and he has selected 5 weeks. So the data should be available for last 5 weeks ending on June 30, 2014(Last 5 weeks from June 30, 2014)Hope it makes more sense now 🙂
Ok, my first guess is correct, it's either/or.
Please clarify how you intend to distinguish between using startdate & enddate or using enddate & period.
Also, where's table #goal?
Also, can you confirm that the table #target will be used as the source of your parameters?
Also, how will you determine if "5" is days, weeks, months, or years?
Thanks.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 20, 2014 at 7:21 am
Exactly.
June 20, 2014 at 7:32 am
KnowledgeSeeker (6/20/2014)
Exactly.
Good. You have some guesses - which is nice.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 20, 2014 at 7:37 am
OK. Let me rephrase my requirement. There is a master table that contains records. In this table there is a column 'transactionDate'. There is another table 'Target' that is used to store the expected output of the company for the given time period. For example, from June 10, 2010 to June 30, 2012 the company has set a target of $500000.00
Now when the user logs in and selects the following parameters:
fromDate: Apr 01, 2008
toDate: Apr 30, 2014
NumberOfRecords: 6
period: 'weekly' (This can be daily, weekly, monthly, quarterly or yearly)
So the query will be translated as:
"Get the last 6 week records from master table where the 6th week ends on Apr 30, 2014"
Moving forward if the date of every week overlaps with the target dates there has to be another column added with the master table data 'targetValue'. For example in the 'Target' table the dates are June 10, 2010 to June 30, 2012 and these dates falls between Apr 01, 2008 and Apr 30, 2014. So for all the 6 weeks the target value would be $500000
This is the master table:
CREATE TABLE #SampleData
(
BaseYear INT,
TransYear INT,
ClientName VARCHAR(50),
DepID INT,
Revenue, INT,
TransactionDate DATETIME
)
INSERT INTO #SampleData
SELECT 2009,2013,'Robert Noble',1077,2356,'2015-03-16 00:00:00.000' UNION
SELECT 2011,2013,'Cally Atkins',1038,3265,'2009-11-01 00:00:00.000' UNION
SELECT 2008,2010,'Ava Evans',1020,3526,'2012-04-20 00:00:00.000' UNION
SELECT 2009,2011,'Riley Bennett',1095,3214,'2011-12-10 00:00:00.000' UNION
SELECT 2012,2013,'Wilma Hatfield',1087,2415,'2010-10-26 00:00:00.000' UNION
SELECT 2008,2008,'Roary Tran',1031,2411,'2009-09-22 00:00:00.000' UNION
SELECT 2009,2009,'Austin Mcintosh',1042,5656,'2013-12-06 00:00:00.000' UNION
SELECT 2009,2009,'Samuel Franco',1027,3256,'2010-03-29 00:00:00.000' UNION
SELECT 2010,2012,'Rose Cooper',1008,3562,'2010-05-06 00:00:00.000' UNION
SELECT 2008,2010,'Sage Myers',1095,2541,'2008-08-07 00:00:00.000'
and this is the target table:
CREATE TABLE #Target
(
StartDate DATETIME,
EndDate DATETIME,
targetValue INT
)
June 20, 2014 at 7:38 am
Sir, my reply was for Sachin Nandanwar.
June 20, 2014 at 8:02 am
How do you define a quarter ? Based on financial year or Calendar year ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 20, 2014 at 8:08 am
Are we good so far?
DECLARE @fromDate DATE = 'Apr 01 2008'
DECLARE @tODate DATE = 'Apr 30, 2014'
DECLARE @NumberOfRecords INT = 6
DECLARE @period VARCHAR(10) = 'monthly' -- (This can be daily, weekly, monthly, quarterly or yearly)
DECLARE @RangeStart DATE, @RangeEnd DATE
SELECT
@RangeStart = CASE
WHEN @period = 'daily' THEN DATEADD(DAY, 0-@NumberOfRecords, @tODate)
WHEN @period = 'weekly' THEN DATEADD(WEEK, 0-@NumberOfRecords, @tODate)
WHEN @period = 'monthly' THEN DATEADD(MONTH, 0-@NumberOfRecords, @tODate)
WHEN @period = 'quarterly' THEN DATEADD(QUARTER, 0-@NumberOfRecords, @tODate)
WHEN @period = 'yearly' THEN DATEADD(YEAR, 0-@NumberOfRecords, @tODate)
ELSE NULL END,
@RangeEnd = @tODate
DROP TABLE #SampleData
CREATE TABLE #SampleData
(
BaseYear INT,
TransYear INT,
ClientName VARCHAR(50),
DepID INT,
Revenue INT,
TransactionDate DATETIME
)
INSERT INTO #SampleData
SELECT 2009, 2013, 'Robert Noble', 1077, 2356, '2015-03-16 00:00:00.000' UNION
SELECT 2011, 2013, 'Cally Atkins', 1038, 3265, '2009-11-01 00:00:00.000' UNION
SELECT 2008, 2010, 'Ava Evans', 1020, 3526, '2012-04-20 00:00:00.000' UNION
SELECT 2009, 2011, 'Riley Bennett', 1095, 3214, '2011-12-10 00:00:00.000' UNION
SELECT 2012, 2013, 'Wilma Hatfield', 1087, 2415, '2010-10-26 00:00:00.000' UNION
SELECT 2008, 2008, 'Roary Tran', 1031, 2411, '2009-09-22 00:00:00.000' UNION
SELECT 2009, 2009, 'Austin Mcintosh', 1042, 5656, '2013-12-06 00:00:00.000' UNION
SELECT 2009, 2009, 'Samuel Franco', 1027, 3256, '2010-03-29 00:00:00.000' UNION
SELECT 2010, 2012, 'Rose Cooper', 1008, 3562, '2010-05-06 00:00:00.000' UNION
SELECT 2008, 2010, 'Sage Myers', 1095, 2541, '2008-08-07 00:00:00.000'
SELECT *
FROM #SampleData
WHERE TransactionDate BETWEEN @RangeStart AND @RangeEnd
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 20, 2014 at 8:17 am
Yes sir. Exactly.
June 20, 2014 at 8:19 am
Based on the 'toDate'. Last n quarters ending on 'toDate'.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply