How to build this partial SUM...
-
I have a table, say for sales data based on months. Two columns, SALES is typed money and YEARMONTH is typed char(6). I want to build a query for two sums: the sales before 201012 and sales after 201101. My difficulty is in composing conditions on char() data. I know that "=" works. Does ">" or "<" work?
Best, Jun
-
I have a table, say for sales data based on months. Two columns, SALES is typed money and YEARMONTH is typed char(6). I want to build a query for two sums: the sales before 201012 and sales after 201101. My difficulty is in composing conditions on char() data. I know that "=" works. Does ">" or "<" work?
Best, Jun
What you did so far for writing query which you need?
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.cacttus.com
-
I have a table, say for sales data based on months. Two columns, SALES is typed money and YEARMONTH is typed char(6). I want to build a query for two sums: the sales before 201012 and sales after 201101. My difficulty is in composing conditions on char() data. I know that "=" works. Does ">" or "<" work?
Best, Jun
You could convert to integer then use > <
-
I have a table, say for sales data based on months. Two columns, SALES is typed money and YEARMONTH is typed char(6). I want to build a query for two sums: the sales before 201012 and sales after 201101. My difficulty is in composing conditions on char() data. I know that "=" works. Does ">" or "<" work?
Best, Jun
Try this: select (sum(sales) from myTable where yearmonth <= '201012') as Sales1, (Select sum(sales) from myTable where yearmonth >= '201101') as Sales2
-
I have a table, say for sales data based on months. Two columns, SALES is typed money and YEARMONTH is typed char(6). I want to build a query for two sums: the sales before 201012 and sales after 201101. My difficulty is in composing conditions on char() data. I know that "=" works. Does ">" or "<" work?
Best, Jun
Jun Du wrote:
Does ">" or "<" work?
yes, all operators work on strings that represent numbers, provided the number of characters is constant (i.e. leading zeroes are used when fewer significant digits would be present). Under these conditions alphabetical and numerical sort order is the same [ADDED] for positive integer numbers [/ADDED]. and yes, date sorting chronologically and numerically works the same provided your date, time or datetime is structured from most significant to least significant, so year first, then month, then day, then hour. therefore, when having YYYYMM (with leading zeroes in MM!), all sort orders are the same (alpha, numeric, chrono). Nevertheless, it is always wise to store date, time, datetime information in an appropriate type, not a string. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Please use <PRE> tags for code snippets, they improve readability.
CP Vanity has been updated to V2.3modified on Sunday, June 5, 2011 1:03 PM
-
Jun Du wrote:
Does ">" or "<" work?
yes, all operators work on strings that represent numbers, provided the number of characters is constant (i.e. leading zeroes are used when fewer significant digits would be present). Under these conditions alphabetical and numerical sort order is the same [ADDED] for positive integer numbers [/ADDED]. and yes, date sorting chronologically and numerically works the same provided your date, time or datetime is structured from most significant to least significant, so year first, then month, then day, then hour. therefore, when having YYYYMM (with leading zeroes in MM!), all sort orders are the same (alpha, numeric, chrono). Nevertheless, it is always wise to store date, time, datetime information in an appropriate type, not a string. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Please use <PRE> tags for code snippets, they improve readability.
CP Vanity has been updated to V2.3modified on Sunday, June 5, 2011 1:03 PM
You are right about storing the date/time info in the appropriate format: the cases when it makes sense to store it differently are few and far between. The only exception to this rule that I've seen was when we needed to select across multiple dates (e.g. first ten minutes of each hour between 9 and 16). Indexing on the hour and minute components, stored in separate two-character fields, helped us avoid a full-table scan.
-
You are right about storing the date/time info in the appropriate format: the cases when it makes sense to store it differently are few and far between. The only exception to this rule that I've seen was when we needed to select across multiple dates (e.g. first ten minutes of each hour between 9 and 16). Indexing on the hour and minute components, stored in separate two-character fields, helped us avoid a full-table scan.
-
Any reason you didnt make the hour and minute columns numeric, say tinyint? Hours and minutes are, after all, numeric.
I'd prefer tinyints too, but it was our client's table. We were providing a solution to monitor, filter, and visualize the data for them.