|
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
|
|
|
|
|
You could convert to integer then use > <
|
|
|
|
|
Try this:
select (sum(sales) from myTable where yearmonth <= '201012') as Sales1, (Select sum(sales) from myTable where yearmonth >= '201101') as Sales2
|
|
|
|
|
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.3 modified 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.
|
|
|
|
|
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.
|
|
|
|
|
Hi,
Just trying to get some test data out of one our DB - We have a table that has a Typeid (int) column and a details (nvarchar(1000)) column
What I'd like to do is get the top 1 details for each typeid (there are about 40 distinct typeid) so something along the lines of
select top 1 typeid,details from auditlog group by typeid,details,
But where the query returns the top 1 details column for each eventtypeid
like
Typid details
1 details1
2 details2
....
40 details40
Does that make sense?
|
|
|
|
|
Use distinct
Select distinct typeid,details from auditlog order by typeid
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
|
|
|
|
|
Thanks for the reply.
I don't think I made it clear in my original post so apologies, but the details column could be anything (it's actually an XML fragment) so distinct will bring back all rows anyway or at least more than 1 row per eventid.
I think I'm after a sort of "top 1 distinct" combination.
|
|
|
|
|
So, can you post data structure in your table and result which you want to get from that data?
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
|
|
|
|
|
What I'd like to do is get the top 1 details for each typeid
How do you know that a particular detail is 'top' among its peer details? Does picking a particular item matter at all? If it does not matter which row you pick as your 'top', try this:
select typeid, min(details) from auditlog group by typeid it will decide that a detail is 'top' if its description comes first alphabetically.
If this is not what you need, you would have to provide another column to determine which detail among many possibilities for the same typeid is to be selected.
|
|
|
|
|
I agree with the above post. You need a column like, timestamp, along with your other columns to group the TypeIds. This way you could do something like max(timestamp) and get the most recent logged event.
Good luck.
|
|
|
|
|
This did it thanks.
apologies for the vagueness, well I knew what I meant.
To hopefully clear up any questions - the table can have many thousands of rows each one holds the details of application events used for auditing purposes.
There are about 40 event types that are logged (eventid) and the details column holds the data associated with a particular event
e.g
<AuditFragment xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<LogText>10.10.10.10 (10.10.10.10) POST http:
<LogTextToDisplay>10.10.10.10 (10.10.10.10) POST http:
</AuditFragment>
this one describes a simple page view.
Other columns are things like hold details of user ids, timestamps etc but in this instance were not required I jut needed one example of each event type to test a reporting application.
Reading that I could have been clearer in my original post.
Thanks again everyone.
|
|
|
|
|
Without sample data its hard to give you a best solution, give this a try
select distinct a.typeid, (select top 1 details from auditlog where typeid = a.typeid) [details]
from auditlog
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
Hi
When building a website, which method will increase performance, Using one connection in a global variable and using it to execute all queries or opening a connection for each user (Session)?
Please advice.
|
|
|
|
|
|
Normally the second.
But, again normally, there are far better reasons for doing that than performance.
|
|
|
|
|
I want to find distinct country and count of each country from a country table.
How should I do?
Result:
Country | Count
Japan | 10
UK | 8
Canada | 12
***** Programme comme si dept soutien technique. est plein de tueurs en série et ils savent adresse de votre domicile. *****
|
|
|
|
|
select country, count(country) from countrytable
group by country
// ♫ 99 little bugs in the code,
// 99 bugs in the code
// We fix a bug, compile it again
// 101 little bugs in the code ♫
|
|
|
|
|
|
Thank You, I missed group by.
***** Programme comme si dept soutien technique. est plein de tueurs en série et ils savent adresse de votre domicile. *****
|
|
|
|
|
Is is not working for null country and does not give correct count
Hepl ?
***** Programme comme si dept soutien technique. est plein de tueurs en série et ils savent adresse de votre domicile. *****
|
|
|
|
|
Use isnull for your count field.
e.g
select country, count(isnull(country,0)) from countrytable
group by country
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
|
|
|
|
|
If country is null, it need not be shown.
I would say
select country, count(country) from countrytable
where country is not null
group by country
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
