Posts by cgyrob

Why are you creating constants when you can just use the date functions in the where clause?

SELECT sum(Salesamount)
   FROM Table
   WHERE salesdate BETWEEN   DATEADD(yy,DATEDIFF(yy,0,getdate()),0) --'First Day of Year'
   AND DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))) --'Last Day of Year'

Just replace where I had @userInput with getdate() function

SELECT sum(Salesamount)FROM TableWHERE YEAR(salesdate) = YEAR(getdate())

You can do the same with the date functions example.

There are alot of ways to do this here are a couple examples.

Using Year() function

Select sum(Salesamount)
From Table
where Year(salesdate) = Year(@userInput)

using Date functions

Select sum(Salesamount)
From Table
where salesdate between DATEADD(yy,DATEDIFF(yy,0,@userInput),0) --'First Day of Year' 
and DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@userInput)+1,0))) --'Last Day of Year'

You don't directly use triggers in a C# app. If you have a trigger on update for the Student table it will be triggered when you send an update statement from your code. If you want the db to do jobs before an update put this code in a stored procedure as this is not what triggers are designed to do.

You can't use the alias in the boolean test plus you should use a having clause to test the count(*) and not the where clause.

Select title, count(*) As Cnt 
From poss_titles 
Group By title
Having count(*) > 1 
Order By count(*) desc

as for only showing fields that don't exist in another table you can use exists condition.

Select title, count(*) As Cnt 
From poss_titles pt
Where not exists (select title from other_table ot where ot.title = pt.title)
Group By title
Having count(*) > 1 
Order By count(*) desc

Try this

Select a,b,c,d,e,f from 
(
 (SELECT a, b, c, d, e, f, DATE 
  FROM table1 WHERE a=10 AND b=1) 
  UNION 
 (SELECT a, b, c, d, e, f, DATE 
  FROM table1 WHERE a=11 AND b=1) 
)
  ORDER BY DATE DESC

You need to encapsulate the whole union to order them after all the records are retrieved.

I beleive you need to use a full outer join with a coelesce on the 3 keys to get your desired result.

SELECT coalesce(a.Recordingid,b.recordingid), coalesce(a.Connection,b.Connection), coalesce(a.Sequenceno,b.Sequenceno), b.Code, b.Cause
FROM dbo.Table_1 a 
Full OUTER JOIN dbo.Table_2 b on a.recordingid = b.recordingid AND a.connection = b.connection AND a.Sequenceno = b.Sequenceno

Use double ampersands && in your if statement.

When you have a higher reputation you get more greeen squares in your header file (I guess it is supposed to signify how competent the responders are) as well I think it might have a reflection on your own altering power (I really haven't figured out how the reputation altering power is assessed but it would make sense).

I beleive it is more important to most that the thread is marked as solved as new members have very limited reputation altering power. Geeks love to help they just need some sort of external gratification for it.

Hiope that helps.

By only doing subqueries you basically created a cartesian join. It displayed a record for every possible permutation of the query.

There is a simple explantion of cartesian (cross joins) here.

http://www.dba-oracle.com/t_garmany_9_sql_cross_join.htm

As for the community. It is expected when a question you ask is solved you set the thread to solved.

Reputation points can be given as well by clicking on the link below the user name. I beleive your reputation altering power is determined by your own reputation and number of threads participated in as well as solved, possibly the reason it is encouraged to mark threads solved when complete as well as keeping the site clean.

You can see what you reputation altering power is by looking in your account by clicking on your own link.

Have a good one.

What rownum = 1 is doing is returning only the top row of employee information for each job_id. without the rownum =1 the query will return all matches which is why you get the error message.

BabyDBA's solution should return the same results and is actually a much better way to go as there is only 1 scan of the table and not 3 plus it is alot cleaner and easier to read.

So to answer your question it is possible to get the distinct value of one field while displaying multiple fields.

I know it is possible as I have done similar queries in the past using subqueries. I provided something I put together quickly, not a very good example but it does accomplish what you were inquiring about.

select distinct a.JOB_ID, 
(Select EMPLOYEE_ID from EMPLOYEE b where b.JOB_ID = a.JOB_ID and rownum = 1) , 
(Select LAST_NAME from EMPLOYEE c where c.JOB_ID = a.JOB_ID and rownum = 1)
from EMPLOYEEE

I'm not quite sure what you are trying to achieve.

If you just want the job_id then grab it seperately, if you want to see all the employees associated which each job_id you have to return all rows for each job_id, unless you have exact duplicate rows which would not make sense in your db design.

What you are asking to achieve doesn't seem to make any sense without context of your ultimate goal.

Try something like this.

SELECT DISTINCT emp.Job_ID, a.Employee_ID, a.Last_Name
FROM Employees emp
Join (Select job_id,employee_id,Lastname 
        From Employees)a on emp.Job_id = a.Job_id
ORDER BY a.Last_Name

Did you try using wild cards.

Select * from Table1
Where Name LIKE ('%' + @Name + '%')

Glad I could help. Can you mark the thread solved if your problem has been fixed.

Thanks.

The first thing you need to do is replace the left join with an Inner join. Right now you are joining all the customers even if they do not have any orders which is a waste of resources when you are looking for the customers that rank in the top5 of orders.

If that alone does not solve your problem you can try using a derived table to calculate the top 5 customers.

SELECT customers_firstname, customers_lastname,o.Number
FROM customers 
INNER JOIN (Select customer_id, count(*) as Number from orders Group by customer_id order by Number desc limit 5)AS o
on customers.customers_id = o.customer_id ;

Let me know if this helps.

Have you tried joining the subquery in the from clause instead of using the inline view.

You should use code blocks as it makes it alot easier to read.

[/code}

Did you try an inline view? something like this maybe

[code=sql]
select
distinct c.name as 'Company Name',
ss.name as 'Program'
,a.application_number as 'Application Number'
,a.sites as 'Site Count'
,convert(varchar(10), a.accreditation_start_date, 101) as 'Start of Accreditation'
,convert(varchar(10), aag.app_submission_due_date, 101) as 'App Due Date'
,aag.invoice_fee as 'Previous Accreditation Fee Paid'
,'' as 'POS Cycle'
,LTRIM((select i.firstName from individual i where i.individualid = aag.pc_id_mysql)) + ' ' +
RTRIM((select i.lastName from individual i where aag.pc_id_mysql = i.individualid)) as 'Primary Contact'
,(select i.email from individual i where i.individualid = aag.pc_id_mysql) as 'Primary Contact Email'
,(select i.title from individual i where i.individualid = aag.pc_id_mysql)as 'Primary Contact Title'
, LTRIM((select i.firstName from individual i where i.individualid = aag.am_id_mysql)) + ' ' +
RTRIM((select i.lastName from individual i where aag.am_id_mysql = i.individualid)) as 'Account Manager'
,(Select top 1 ast.application_status_description 
	from application_status ast where ast.application_status_idd = asl.status 
	order by entered_date desc) as 'Status'
,convert(varchar(10), a.accreditation_expiration, 101) as 'Expiration Date'
,(select 
convert(varchar(10),asl.user_date, 101)) as 'AC Decision Date'

from
[application] a
inner join accreditation_agreement aag on
a.client_id = aag.company_id
inner join company c on
a.company_id_mysql = c.companyid
inner join application_accreditation aa on 
a.application_id = aa.application_id 

inner join standard_set ss on 
aa.standard_set_id = ss.standard_set_id 
inner join application_status_log asl on
a.application_id = asl.application_id
inner join application_status ast on
asl.status = ast.application_status_id 

WHERE C.name not like 'URAC%'
order by 
'Company Name'

I can't test so not sure if this syntax will work. The other syntax that you can …

At my previous company we also resorted to using dotdefender after we came under a serious sql injection attack. The product worked very well and we were able to get it up and running very fast. It took a few more weeks to modify the product to not block some of our own queries. The product gave us the ability to maintain our business while we totally re-designed and developed an updated version with updated security.

It also comes with decent reporting so you can track what ip's are launching the attacks and the queries they are using.

They still maintain the dotdefender for the added layer of security but the product definately saved the company alot of money and possibly the business altogether.

just my 2 cents added to Danielos.

Here is a link to an article on creating an event schedule in mysql, unfortunately I don't use mysql so it is the best I can do for you.

http://dev.mysql.com/tech-resources/articles/event-feature.html

If this doesn't work possibly you can use windows task scheduler to call the job.

I don't know mysql very much but if you schedule a job it should not care if a file is open or not.

Schedule a job to run the script at whatever interval you want.

Mamtha,

If this solved your question can you please mark the thread solved.

thanks.

Yes intersects work well and are much more readable.

Try something like this

Select customer from Table
Where purchasedate between trunc(sysdate,'MM') and LAST_DAY (TO_DATE (trunc(sysdate,'MM')))
and product = 'ABC'
INTERSECT
Select customer from table 
where purchasedate between add_months(trunc(sysdate,'MM'),-11) and trunc(sysdate,'MM')-1
and product = 'ABC'

This will only show a customer that has purchased a particular product in the current month as well as in the last 11 months.

I think we would need a little more details to what you are asking.

Do you want a list of all customers that purchased a specific product in the current month only IF they had purchased the same product in the past 11 months?

The product will be a provided parameter?

It also sounds like this information would be held in multiple tables, are you not interested in how to pull these together; just how to make this comparison?

If you need to know how to join the tables a schema description of the tables would be required.

Try this

SELECT MAKE.MAKE, MAKE.count, color.Color, color.count 
FROM(
       SELECT MAKE, count(*)count
       FROM cars 
       Group by MAKE
    )MAKE,
   (   SELECT MAKE, color, count(*)count
       From cars
       Group by MAKE,color
   )color
Where make.make = color.make

Nevermind, I take it the attachment is the table schema.

Is it important to do it all in one query. What are you using the resultset for because if you do it in one query you will most likely get back something like the following.

BMW 2 RED 1
BMW 2 BLACK 1
AUDI 8 RED 3
AUDI 8 BLACK 2
AUDI 8 BLUE 3
etc....

I actually remember reading both of these threads and had commented on one of them but I still did not correlate them.

@sknake - thanks, I didn't realize there were multiple threads relating to this db.

I don't see where you are getting the alias 'c'

Besides that i think you are making it more difficult then it has to be.

select *
from dbo.tblLevelOneApprover a, dbo.tblLevelTwoApproverToLevelOneApprover b
Where convert(int, b.level_two_emplid) <> convert(int, a.emplid)

Since you didn't give too much information I thought of a few other interpetations of what you asked.

If field1 is a numeric value you might want the sum of the field grouped by field2

Select field2, sum(field1)
From Table
Group by field2

or you might want to know how many records of field1 there are that are also rouped by field2

Select field2, field1, count(*)
From table
Group by field2, field1

I'm sure one of the 3 syntax's will work for what you are asking.

do you mean something like this

Select field2,count(Field1)
From Table
Group by field2

Link82

Group by does exactly that; groups your result set by the fields provided, in the example provided by sknake. GROUP BY tblManagers.managerID, tblManagers.managerLastName, tblManagers.managerFirstName This will give you the number of rows selected grouped by Managers Id, Last Name, First Name so you get results like the following.

Id LastName First Name Count
1 Smith John 10
2 Jones Bill 8
3 Reilly Scott 15

Without the group by you would just get a list of all the records.

Id LastName First Name Count
1 Smith John 1
2 Jones Bill 1
2 Jones Bill 1
1 Smith John 1
3 Reilly Scott 1
3 Reilly Scott 1
...

I have not used [no lock], never thought about it before sknake's post. I would love an explanation of why it is important to use in this situation and others.

Learn a new thing everyday.

I can't see any easy way to parse this string in a single select statement. I would try each individual piece at a time and then once you can parse each piece put them back together.

You might also look into the PARSENAME function as it might help with the parsing.

Sorry I couldn't help more, maybe someone else might have some suggestions.

You should be able to create a stored procedure and try to break off a piece at a time.

If you don't need the data live maybe you can make a dts package to run daily to parse these strings and load them into the new fields.

I have question.

How were these records imported into the table in the first place?

It would have been easier to parse them correctly when they were initially loaded.

I have never used filemaker but it is a relational db so their is most likely either a sql window or graphic sql designer like in acccess.

If there isn't you will have to find out how to join tables in filemaker, then join the items table to the costs table where the expiry date is null.

Simple sql

Select a.Item_name, b. Costs
From items a, costs b
Where a.id = b.Item_id
and b.expiry_date is null

Sorry I couldn't help with the filemaker syntax, there might be someone on here that knows the product.

You should have a seperate table which holds your costs and is referenced to the item table.

Items
--------
id
item_name
Desc
etc.

costs
----------
id
items_id
Costs
effective_date
expiry_date

When you need to update a cost you set the expiry_date for the current record and insert a new record in costs for the items_id. This way you will also have historical data for your changing costs. When referencing the costs you just have to select the active record for the item.