February 9, 2009 at 11:36 pm
My query is like i want to get the customer name who has accessed the application with 48 hours and how for many times.
example
referencenumber datecreated Customer name
1234567 01/01/2009 xxxxxxxxx
2345678 01/01/2009 xyzcab
3456789 01/01/2009 xxxxxxxxx
4567890 01/02/2009 xxxxxxxxx
........... ........... ........
1245678 01/06/2009 xxxxxxxxx
2567980 01/06/2009 xxxxxxxxx
5689755 01/23/2009 xxxxxxxxx
and so on
based on the datecreated i want to know how many times xxxxxxxxx has accessed the application with in 48 hours from 1st record to the n number of records
from the example above , i should get 3 records between 01/01/2009 and 01/02/2009 and from 01/06/2009 i get 2 records.
i hopoe you understand my problem here,
will cursor hold gud for this solution, anything pls let me know the logic here. i appreciate your help in advance
February 10, 2009 at 1:02 am
Hello,
I think it is the other way round - you have to describe the logic, then we can look for solution. We don't know what you expect...
How do you know which row is first (or earlier than some other) - in other words, on which column we should use ORDER BY?
What is the "first record" - is that meant overall from all rows of the given customer in the table, or from some part (like first after a given date)?
What do you mean by "n" rows? Is that a way of saying that you want all rows within 48 hours and don't know how many there will be, or is that a parameter that you want to enter (like "if there are more rows than 5, return only the first 5")?
How are we supposed to check 48 hours, if your dates do not contain time, only date? Is your data sample simplified and in reality there is time as well, or did you mean 2 days? If there is really only day, what is meant by 48 hours - is it the starting day and the next day?
Please, post the data as CREATE TABLE and INSERT INTO statements, so that we can work with it, and use date format that will work regardless of settings (e.g. YYYYMMDD).
You might wish to check GETDATE() and DATEDIFF functions in BOL to get started. As to the cursor, no, you don't need cursor for this unless I misunderstood something in your post.
February 10, 2009 at 9:12 am
If you post as Vladan requests and look at the articles linked in my signature it would be easier to provide you an answer.
I think you are looking for something like this:
SELECT
[Customer Name]
MIN(datecreated) AS first_access,
MAX(datecreated) AS last_access,
COUNT(datecreated) AS no_times_accessed
FROM
tableA
WHERE
datecreated >= DATEADD(hour, -48, GETDATE()) AND
datecreated < GETDATE()
GROUP BY
[Customer Name]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply