November 8, 2007 at 5:28 am
Guys hi,
i have a table that has the following columns
examination_centre (TC_number), product (Skillcards and /or examinations, the revenue_type),
quantity, and date_Stamp (dates without time information).
This table refers to an educational organization. The examination centres order from the educational organization Skilcards and examinations in order to perform ths examinations.
I want to present the products each examination centre purchased, by year, for the previous 3 years. So the code (simple) is
SELECT
TC_NUMBER
, REVENUE_TYPE
, YEAR(DATE_STAMP) AS YEAR
, CONVERT(INT, SUM(QUANTITY)) AS QUANTITY
FROM
VW_CPD_TESTS_SKILLCARDS
WHERE
TC_NUMBER LIKE ('%' + @Centre + '%')
AND YEAR(DATE_STAMP) > YEAR(GETDATE()) - 3
GROUP BY
TC_NUMBER
, YEAR(DATE_STAMP)
My problem is that i must put a variable ( eg. @period, 1 for academic, 2 for annual) that defines whether the year is academic or annual (normal) year. So I guess the corresponding group by, should be based on this choice.
The academic year 2006 starts September 2006 and ends June 2007
but if the user select annual year, then it is the psysical year 1/1/2006
31/12/2006. Keep in mind that the years presented are not fixed. They are just 3 years and on from the current year. For example when 2008 come, the report will automatically present 2006, 2007, 2008, as columns.
How can i achive this?
Can you help? The query does not calculate only this, and any changes i want to be kept as smart and short as possible...
Your help please, would be appreciated! 🙂
November 8, 2007 at 6:13 am
I've tested this as best I could without sample data, but all that your really doing here is saying that if the @period is 'A' for Academic and the month is less than July (7) then subtract a year from the actual year.
SELECT
TC_NUMBER
,REVENUE_TYPE
,CASE WHEN @period = 'A' AND MONTH(DATE_STAMP) < 7 THEN
YEAR(DATE_STAMP) - 1
ELSE
YEAR(DATE_STAMP)
END AS YEAR
,CONVERT(INT, SUM(QUANTITY)) AS QUANTITY
FROM
@VW_CPD_TESTS_SKILLCARDS
WHERE
TC_NUMBER LIKE ('%' + @Centre + '%')
AND
CASE WHEN @period = 'A' AND MONTH(DATE_STAMP) < 7 THEN
YEAR(DATE_STAMP) - 1
ELSE
YEAR(DATE_STAMP)
END > YEAR(GETDATE()) - 3
GROUP BY
TC_NUMBER
,REVENUE_TYPE
,CASE WHEN @period = 'A' AND MONTH(DATE_STAMP) < 7 THEN
YEAR(DATE_STAMP) - 1
ELSE
YEAR(DATE_STAMP)
END
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 8, 2007 at 6:18 am
November 8, 2007 at 6:43 am
Jason hi,
the query you send me,
declare @period nvarchar
declare @centre nvarchar(4)
set @centre='0009'
set @period='A'
SELECT
TC_NUMBER
,REVENUE_TYPE_ID
,CASE
WHEN @period = 'A' AND MONTH(DATE_STAMP) < 7
THEN YEAR(DATE_STAMP)-1
ELSE
YEAR(DATE_STAMP)
END AS [YEAR]
,CONVERT(INT, SUM(QUANTITY)) AS QUANTITY
FROM
VW_CPD_TESTS_SKILLCARDS
WHERE
TC_NUMBER LIKE ('%' + @Centre + '%') AND
REVENUE_TYPE_ID=22 AND
CASE
WHEN @period = 'A' AND MONTH(DATE_STAMP) < 7
THEN YEAR(DATE_STAMP) - 1
ELSE YEAR(DATE_STAMP) END > YEAR(GETDATE()) - 3
GROUP BY
TC_NUMBER
,REVENUE_TYPE_Id
,CASE
WHEN @period = 'A' AND MONTH(DATE_STAMP) < 7
THEN YEAR(DATE_STAMP) - 1
ELSE YEAR(DATE_STAMP)
END
and the following query that i made for checking...
SELECT
TC_NUMBER
,REVENUE_TYPE_ID
,CONVERT(INT, SUM(QUANTITY)) AS QUANTITY
FROM
VW_CPD_TESTS_SKILLCARDS
WHERE
TC_NUMBER='0009'
AND REVENUE_TYPE_ID=22
AND DATE_STAMP >= '2005-09-01 00:00:00.000' /* FIRST SEPTEMBER */
AND DATE_STAMP <= '2006-06-30 00:00:00.000' /* END OF JUNE */
GROUP BY
TC_NUMBER
, REVENUE_TYPE_ID
do not produce the same results.
You query gives me a quantity for year 2005 of 630 units
my query for sep 2005 to june 2006 gives a quantity for 521 units..
Is there any possibility to advice me of what might be wrong?
November 8, 2007 at 6:54 am
The only thing I can see is the
TC_NUMBER LIKE ('%' + @Centre + '%')
vs.
TC_NUMBER = '0009'
Are the results still different if you change that part of the test query?
FYI, if you put the "[ code ]" and "[ /code ]" (remove spaces) around your code when you post, it will keep the formatting.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 8, 2007 at 6:58 am
Dfalir (11/8/2007)
.... Keep in mind that the years presented are not fixed. They are just 3 years and on from the current year. For example when 2008 come, the report will automatically present 2006, 2007, 2008, as columns.
OK, this may be the problem. My query sums the quantity for anything AFTER the 3 year ago Academic year and you are restricting it to that year only.
What happens when you remove the upper bound of your query
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 8, 2007 at 7:19 am
No this does not affect the query, all it does is that it selects the centre. But let me tell you something.
If a perform a sum for revenue_type=22 (just a product) for the whole year 2005
where...
AND DATE_STAMP >= '2005-01-01 00:00:00.000' /* FIRST DAY YEAR */
AND DATE_STAMP <= '2005-12-31 00:00:00.000' /* LAST DAY YEAR */
it produces 622 units
if a perform an academic year with YOUR query for 2005 i.e 1st sept 2005 - 30/june 2006
it produces 734
and if i perform a sum of quantity just within the same date range i.e
where ...
AND DATE_STAMP >= '2005-09-01 00:00:00.000' /* FIRST SEPTEMBER */
AND DATE_STAMP <= '2006-06-30 00:00:00.000' /* END OF JUNE */
it produces 688
for the same product, the same year (2005) the same examination centre.
any ideas? if you are able to help from your work
November 8, 2007 at 7:23 am
Jason,
i do not restrict your query. I run your query as exactly you have given me.
the i have another query just sum of quantity for the year and that centre with specifc date
one query with 1 sept 2005 to 30 june 2006
one query with 1 jan 2005 to 31 dec 2005
but the above dates are in another quer like
SELECT
TC_NUMBER
, REVENUE_TYPE_ID
, CONVERT(INT, SUM(QUANTITY)) AS QUANTITY
, month(date_stamp) as [month]
, year(date_stamp) as [year]
FROM
VW_CPD_TESTS_SKILLCARDS
WHERE
TC_NUMBER='0011'
AND REVENUE_TYPE_ID=22
AND DATE_STAMP >= '2005-09-01 00:00:00.000' /* FIRST SEPTEMBER */
AND DATE_STAMP <= '2006-06-30 00:00:00.000' /* END OF JUNE */
GROUP BY
TC_NUMBER
, year(date_stamp)
, month (date_stamp)
, REVENUE_TYPE_ID
November 8, 2007 at 7:26 am
I think we're not querying with the same restrictions here. My queries do NOT incorporate an end date as you defined in the first post. What do you get when you run the straight summing query with ...
AND DATE_STAMP >= '2005-09-01 00:00:00.000'
and NO <= part?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 8, 2007 at 7:37 am
First things first - you REALLY should be putting the academic year into a calculated field in your table so you don't keep recalculating it. Make it persisted, and put and index on it too.... That way - it becomes MUCH easier to read, and will run MUCH faster.
Second - Jason's "academic year" runs from JULY 1 through JUNE 30 (which I think is the correct definition, since it's a FULL year). You probably want to make sure you don't have anyone in summer school.
Two more questions:
- Why are you summing FIRST then converting to INT? if that your version of rounding, or is your quantity not stored as an int? If Door #2, the fact that you're summing means you're doing an implicit conversion, so the second convert is useless.
- In Jason's the WHERE clause has a LIKE, and you use an = (like he pointed out). That will produce VERY different results if you have TC numbers like '200091' for example...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 8, 2007 at 7:38 am
o Jason please let me tell you what i want in case i did not made it clear.
Suppose i am a user and run the report with period beeng normal. that is each year start on the 1 of january and finshes on 31 of december,
Then i want to see 3 lines, one for each year i.e 2005, 2006, 2007, a sum of the quantity for each year.
Now if i run the query and choose academic period i want to see the SAME three lines
one for year 2005, one for year 2006 and one for year 2007. BUT now the start of the year 2005 will be 1 of september 2005 and the end of the "year" 2005 will be the 30 june of 2006. this is because it is an academic year.
so again i will see three lines but the start and the end of each year lest say year 2005) will be the september of that year till the end of june of next year i.e 2006
did i make it clear?
November 8, 2007 at 7:45 am
Dfalir,
Take a look at Matt's post. He's said it a bit better than I have. But I believe my code does exactly what you stated. Other than your definition doesn't handle anything that may occur between July 1 and Aug 31.
😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 8, 2007 at 7:45 am
Matt, thank you for your comments. However let me say this
a) if the management tells me the academic year is from 1st of September (2005) till the End of June (2006) and so on for the year, it is pointless to argue differently. 🙂
b) convert int, is because it is stored as varchar. However this is just a minor problem. Please do not deal with it. I can easily convert it to anything. My problem is how to dynamically group according to the "year" the user wants when he runs the query, wether it is an actual year, or an academic year. 🙂
So all i want is to group by (as jason helps) by the "perceived year" when the user chooses academic or as the normal year when he chooses the actual year. any ideas welcome 🙂
Sincere thanks to both of you for your time and concern...
November 8, 2007 at 7:49 am
guys, 1 of july to 30 of august does not exist as dates for our company when we choose academic year. WE ARE CLOSED! lol. These months do not exist in the calendar! 🙂
The problem is if you have any idea in order to exclude these months and group by as i have stated in my previous posts. Otherwise it would be fairly simple i think... 🙂 . That is why I asked your help. 🙂
November 8, 2007 at 7:54 am
I'm not arguing what management is calling it - I'm pointing out those are apples and oranges. What I was getting at with the year comment was that academic year 2006 in Jason's definition=7/1/06-6/30/07. In your definition, that's 9/1/06-6/30/07. There's a 2 month discrepancy, which could be where you get those extra records (the school i used to work for had a session from july-Aug, so we'd have to consider a full 12 months, and not ten.
Just for giggles - have you tried replacing the LIKE syntax with the - syntax? And you might care to run a query to see what you get when you ask for
....
AND DATE_STAMP >= '2005-07-01 00:00:00.000' /* FIRST SEPTEMBER */
AND DATE_STAMP < '2005-09-01 00:00:00.000' /* END OF JUNE */
...
If that returns ANY records, then Jason's result will be different from yours.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply