|
hi every buddy
well I'm trying to make a few queries to make reports of my project here and I wana grup the data according to months, or weeks or years
So I'm writing a query which is like
<br />
<br />
select m.manname+' '+prod.name as Item, SUM(s.salePrice) Total_Sale, dateName(month, b.billdate)+', '+datename(year,b.billdate) as sale_Month<br />
from sale s inner join purch p on s.purchid = p.purchid<br />
inner join manufacturer on p.manid = m.manid<br />
inner join products prod on p.catid = prod.catid<br />
inner join bill b on s.billid = b.billid<br />
group by m.manname+' '+prod.name, dateName(month, b.billdate)+', '+datename(year,b.billdate)<br />
<br />
So can u plz tell me how can we make groupings so that I can get seperate records for November 2007, December 2007 and so on u so forth u know.
thanks in advance
Rocky
|
|
|
|
|
|
instead of ....
group by m.manname+' '+prod.name, dateName(month, b.billdate)+', '+datename(year,b.billdate)
try ....
group by dateName(month, b.billdate)+', '+datename(year,b.billdate), m.manname+' '+prod.name
I'm not sure if the answer is that easy but it looks that way to me.
|
|
|
|
|
ok I"ll try that as well but I'm also looking in to date part too
|
|
|
|
|
I got the grouping when i use date part and seperate the month and year now but still I cant get it like a single string 'November 2007'
I have a bit awkward idea to make another view over it and concatenate those month and year columns in that
Rocky
|
|
|
|
|
If you have it grouping correctly is the problem what is getting displayed?
I did a query like below on an example table and the grouping seems to be working and so does the display:
select count(1), max(dateName(month, invcDate)+', '+datename(year,invcDate))
from invc
group by dateName(month, invcnbr)+', '+datename(year,invcnbr)
I had to add the max around the month string since it wasn't an aggregate, but it displays and groups.
|
|
|
|
|
well I made grouping with another idea u knwo.
I make a view like (name: my_view)
select m.manname+' '+prod.name item ,SUM(s.saleprice) total_sale, dateName(month, billdate) mnth, datename(year,billdate) yr from sale inner join bill on s.billid = b.billid group by dateName(month, billdate), datename(year,billdate), m.manname+' '+prod.name etc
and later I make a query on it like
select Item, total_sale, mnth+', '+yr from myView
Atfirst i used inline view for this but then I converted it into a seperate view. and now its groupin it correctly
But do u have any ideas on grouping on a weekly basis ?
Rocky
|
|
|
|
|
Hi All,
Desperately need some help, guidance and wisdom that I am pretty sure only you can give. I am trying to do some fancy pattern matching, however I am not sure if it is even possible in SQL.
Im trying to create an expert system, where the user can go online and self-diagnose their problem by typing it in to a textbox. The input from this textbox is then stored to a String variable. What I want to do is match any part of this input with any part of a stored Call Log Description (which is a field from the database).
For example;
User Input: "blah blah blah...I want to tell you that I am happy...blah blah blah"
Within Database: "blah blah blah...I am happy today as I...blah blah blah"
Result: "I am happy" pattern match has been found!
Is it possible to create an SQL statement to match part of a sentence with another part of a sentence?
Many thanks
Richard Powell
|
|
|
|
|
try using SUBSTRING() function in where clause with like operator for pattern searching.
KP
|
|
|
|
|
hello
i'm woring on the Create trigger query (for insert, update and delete)and it doesn't work.
the syantax i'm using is
Global.Instance.Trigger_Query="CREATE TRIGGER "+Global.Instance.Field_Name+"_trg ON "+ Global.Instance.Table_Name+" FOR INSERT AS "+Global.Instance.Field_Name+" + "+val+" GO";<br />
i got the syntax from the link that was suggested by another fellow developer earlier through the forum, but i've also tried many other syntaxes but not one of them is working.
please tell me what the problem is
awaiting ur replies
regards
Saira
-- modified at 9:04 Saturday 10th February, 2007
|
|
|
|
|
* What does Global.Instance.Trigger_Query actually evaluate to? (Not what it should evaluate to, but what it really contains)
* What does Global.Instance.Field_Name actually contain? (Not what it should have, but what it really contains)
* Ditto for Global.Instance.Table_Name and val
What do you do with this string once you have it? I presume this is part of some C# code somewhere. If that is wrong please say.
GO is not part of T-SQL. It is a batch delimiter used by Query Analyzer.
|
|
|
|
|
All the "Global.s"... have string values in them.
Global.Instance.Field_Name has the field name on which the trigger is to be applied.
Global.Instance.Table_name contains the name of the table
val contains an integer number.
Global.Instance.Trigeger_Query contains the mapped create trigger query.
what i'm trying to do is store the trigger query in a string type varible and then execute the mapped query through the ExecuteNonQuery() method.
i've used the same tehnique to map Create Table and Alter Table and Delete table queries and they worked fine.
|
|
|
|
|
Saira Tanwir wrote: All the "Global.s"... have string values in them.
Specifically, what values. You are injecting values in to a SQL String. Any of those values can cause it fail if the values are not sufficiently sanitised correctly.
Saira Tanwir wrote: Global.Instance.Trigeger_Query contains the mapped create trigger query.
Specifics. What EXACTLY does it contain. What does the expression evaluate to? What is the exact contents of the value.
Saira Tanwir wrote: what i'm trying to do is store the trigger query in a string type varible and then execute the mapped query through the ExecuteNonQuery() method.
 Mapped to what? As far as I can see it is just a query that does not have a result set. Without a result set there is nothing to map to.
You need to debug what is going on to see that the actual query that is produces is a valid one.
|
|
|
|
|
okay
the query string is
CREATE TRIGGER emp_id_trg ON employee FOR INSERT AS emp_id < 25
this is what the trigger should do at insertion time.
the error i get is
INcorrect syntax near ON
does this help
|
|
|
|
|
Saira Tanwir wrote: this is what the trigger should do at insertion time
What do you expect it to do? I don't see it doing anything. You have a conditional expression, that's it. If the condition is true what is supposed to happen? If the condition is false what is supposed to happen?
|
|
|
|
|
its quiet simple really
if the condition is true then insertion and updation should take place other wise an error message should be displayed.
that is however not the not the question. the question is as i mentioned earlier " Am i applyinf the correct syntax for this or not" cause if the syntax is correct the query is always executed no matter what u try to do in it.
my problem is the query i'm trying to execute does not ecexute at all.
Can u please give me an example query in which a user tries to create a trigger that works like this i.e
suppose, there is an attribute of salary. now the trigger against it the attribute is that whenever a salary amount is entered, a check should be applied checking that the salary is not more than a 6 digit number.
plz do send me a create trigger query through which the above task can be implemented
|
|
|
|
|
Saira Tanwir wrote: that is however not the not the question
Actually it is the question. You have a conditional expression and the syntax error is that you have not completed it. You set up the condition, but don't say what happens what happens when the condition is met.
Saira Tanwir wrote: my problem is the query i'm trying to execute does not ecexute at all.
You don't have even a query - You have only part of a conditional expression. It can't do anything because you've not told it what to do.
Saira Tanwir wrote: suppose, there is an attribute of salary. now the trigger against it the attribute is that whenever a salary amount is entered, a check should be applied checking that the salary is not more than a 6 digit number.
I'm assuming that by attribute you mean a column called salary.
CREATE TRIGGER emp_id_trg
ON employee
FOR INSERT
AS
DECLARE @salary int;
SELECT @salary = salary FROM inserted;
IF @salary >= 1000000
BEGIN
-- Do stuff here - the salary is greater than 6 digits
END
|
|
|
|
|
:->
can u also please elaborate the functionality of
DECLARE @salary int;<br />
SELECT @salary = salary FROM inserted;
what is 'FROM inserted'?
|
|
|
|
|
First to DECLARE a variable by the name of @salary and define it as an integer.
Then to SELECT the value from the salary column in the inserted table (a virtual table provided inside the trigger that contains all the inserted data)
|
|
|
|
|
Actually, you are probably better with some sort of constraint.
Set up the rule:
CREATE RULE validSalary AS @salary <1000000;
Bind the rule to the column:
EXEC sp_bindrule 'validSalary', 'Employee.Salary'
|
|
|
|
|
re Rules substitutes for triggers??
what i mean is that Triggers can be applied for three different operations i.e. for INSERT, DELETE and UPDATE
can rule be used likewise??
|
|
|
|
|
Saira Tanwir wrote: re Rules substitutes for triggers??
It isn't a substitute. But it seem to me that what you want in this instance is a rule. Not a trigger.
A trigger says "here are some additional actions to perform when something happens" a rule says "in order to allow this to happen, you must satisfy my condition"
A rule is applied on a column. It will be checked when data is inserted or updated. A rule cannot have an effect on a delete.
|
|
|
|
|
okay
then the validation rules that are applied on a column in MS Access(if u've worked in it) are at the backend Triggers or Rules? cause trying to do something similr to that an it would be a help if u would clear my concepts too.
|
|
|
|
|
No idea - I thought we were talking about SQL Server. I don't use Access.
|
|
|
|
|
Don't worry, we still are using Sql Server i just asked perchance
thanks a lot for the great help
until next time
Regards![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
Saira
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
