|
Hey all,
I have a need to create a retention report in mysql/sql server that I can use to find out how many customers have canceled within 30,60,90 days of their sale date.
So my query is something like this in mysql:
<br />
SELECT t1.sale_date, t1.primary_phone, t1.cancel_date<br />
FROM n.orders as t1<br />
where t1.sale_date between "2007-08-01" and "2007-08-31"<br />
order by t1.date_entered<br />
I need to be able to say something like and t1.cancel_date <= 30 days + sale_date.
Does this make sense? I'm not sure how to do this without adding a more complex script, but you guys are the experts!
Please help! Thanks
|
|
|
|
|
In SQL-Server you would use the DateAdd function to add 30 days.
|
|
|
|
|
Use the dateadd function
<br />
where t1.cancel_date <= dateadd("d", 30, sale_date)<br />
Hope it helps
Habetis bona deum
|
|
|
|
|
Thanks guys!
I still have a problem with my formatting somewhere because in mysql I get the error "no database is selected". I played around with the query and got another error saying that "n.dateadd does not exist". It seems like I need some parenthesis or something. Does anyone know the specifics of how to use this function right in mysql?
I've searched around, and I've had problem fitting it to my situation.
Thanks all!
|
|
|
|
|
Ok so dateadd in mysql is just date_add! Doh! My syntax is still off though because I'm getting 1064 errors.
I've got:
<br />
SELECT * FROM orders<br />
where date_entered between "2007-08-01" and "2007-08-31" and cxldate <> "0" and cxldate <= date_add('day', 30, date_entered)<br />
cxldate stands for cancel date
I get this error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '30, date_entered)' at line 2
|
|
|
|
|
Try here[^] for some more information.
|
|
|
|
|
Hi
I'm working on a dataset and I currently would like to perform the follwing statement on a tableadapter:
SELECT ElementName FROM Elements
WHERE
ElementId IN (69,70,88,123)
I wrote the following query in the table adapter
SELECT ElementName FROM Elements
WHERE
ElementId IN (@ListOfIds)
I've set the property of this parameter to be a string. But if I do "Preview Data"... It says me that he Can't convert the given string to int.....
Where am I doing wrong???
|
|
|
|
|
You can't do this because you are passing in a string containing the list. You need to do this by either writing a function to parse it or by writing dynamic SQL.
|
|
|
|
|
I'm hoping someone can give me some ideas. I have a program that works for most people that inserts photos into a database using a data adapter.
The problem is on one PC (so far that I've found) it doesn't insert the record or throw an error.
The unusual thing is the user can log into another PC and it works fine and other users can log into that PC and it doesn't work for them.
What do I look for on that PC that could be causing the problem? I am lost as to what to do to fix this PC. I don't think this is a programing question, but rather a hardware or registry question. Is there a better forum to post this in?
The biggest problem is just that the Update function returns that it updated 1 record which is what I expect, but the record is not there when I query it.
Thanks
-- modified at 13:52 Friday 30th November, 2007
Brent
|
|
|
|
|
dbrenth wrote: What do I look for on that PC that could be causing the problem?
Find what is different in that particular PC. It could be one of a multitude of things.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Yeah I knew that, but there could be thousands of differences and I was hoping someone could say look for this file or this key, etc. I was looking for a place to start.
Brent
|
|
|
|
|
SqlServerCe VISUAL STUDIO 2005 VISUAL BASIC help needed.
From an application I get SqlServerCe DATA TABLE with multiple occurrences of the same records. I need VISUAL STUDIO 2005 VISUAL BASIC code to DELETE the records that are written more than once.
|
|
|
|
|
Would you not be better just doing the delete in SQL?
|
|
|
|
|
Thanks for your reply. I can not do that because the table is the result of a program and is created many times. As many as the program is used. I can not change the program so I need to change the table every time is ceated. Thats why I need to write code in visual studio visual basic to correct the table and eliminate the additional records. I assume there is not a SQL command to do that.
|
|
|
|
|
|
Don't cross post. It is considered rude.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
An error has occured while establishing a connection
to the server. when connecting to sql server 2005,
this failure may be caused by the fact that under
the default settings sql server does not allow remote connections
|
|
|
|
|
- Open the SQL-Configuration Manager via 'Start->Programs->Microsoft SQL Server 2005->Configuration Tools->SQL Server Configuration Manager'
- Browse to 'SQL Server 2005 Network Configuration->Protocols for MSSQLSERVER' and enable the required protocols (e.g. the TCP/IP-Protocol)
This should solve your problem. If not, you can check the properties of the Server (Right-Click on the registered server in the SQL Management Studio). In the 'Connections' -Tab, check the option
'Allow remote connections to this server'.
|
|
|
|
|
Hi
I have 3 tables e.g A,B,C.
I am joining table A and table B on the bases of id
Select A.id,B.Name from A,B where A.id=B.id .
Now i want get records from table C basis on calculated A.id.
These all I want in One Query.
Is tht Possible?
Thanks
Care Career
|
|
|
|
|
U can use the following code
Select * from C where id in (Select A.id from A,B where A.id=B.id)
|
|
|
|
|
I want all the fields from 3 tables.
This query solves fethes data from C table only i want data From a or b also
SO can u help?
thnx
care career
|
|
|
|
|
Hi
try this query
Select A.id,B.Name,C.Calc From A,B,C Where A.id = B.id And A.id = C.Id
Or
Select A.Id, Name, Calc From A
Inner Join B On B.Id = A.Id
Inner Join C On C.Id = A.Id
Hope this will work but there should be relation between these tables
|
|
|
|
|
I want to use SQL 2005.after installing it on my system I wanted to create a table. How to do that?
How to fire a query?
I have used SQL 2000.
|
|
|
|
|
Master Mind`z wrote: I want to use SQL 2005.after installing it on my system I wanted to create a table. How to do that?
You need to create a database first. Then you can create the table in the database.
Master Mind`z wrote: How to fire a query?
Use the SQL Server Management Studio.
|
|
|
|
|
If you wish to become a great dba or even a good one then write your own scripts. To do this effectively you have to use "SQL SERVER BOOKS ONLINE" which are installed with SQLserver 2005. Learn or look at one new command a day and get feel for the commands.
First look up command : CREATE DATABASE
THEN look up command : CREATE TABLE
Have fun you have a good journey ahead of you.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
