same day date when criteria met

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

same day date when criteria met

Author

Topic

aivoryuk
Starting Member

9 Posts

Posted - 2010-11-29 : 15:47:19

Hi all

I am trying to return data from a table based on certain criteria and also has been in the same day.

For example column A contains the unique reference number, column B is the date and time and Column C is the value.

What I want to do is, if the first entry of the day for the unique reference has a value of greater than 200 then all further entries for the unique reference will be returned if they are in the same day.

Further to this if the value of the first entry is less than 200 then this will not be returned but all further entries for the unique reference for that day will still be returned.

Not sure where to start with this so any help would be appreciated.

Regards

Alex

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-11-29 : 16:40:33

I'm having a difficult time understanding your requirements. Can you clarify what you mean by a unique reference and how there coulod be multiple entries if they are unique?

=======================================
No matter what side of the argument you are on, you always find people on your side that you wish were on the other. -Jascha Heifetz, violinist (1901-1987)

aivoryuk
Starting Member

9 Posts

Posted - 2010-11-29 : 17:12:45

quote:
Originally posted by Bustaz Kool

I'm having a difficult time understanding your requirements. Can you clarify what you mean by a unique reference and how there coulod be multiple entries if they are unique?

Unique refence would be account number etc so each client has a different one. That client could transact multiple requests each day but they would all be attributed to that account number.

So if the value of the first transaction for that account number is of a value of 200 then all further entries for that account number for that day would be brought back.
If the first transaction was less than 200 but then they had a 2nd,3rd,4th etc transaction and it was the same day as the first then I would also want those brought back.

I hope that is a bit clearer.

Cheers

Alex

=======================================
No matter what side of the argument you are on, you always find people on your side that you wish were on the other. -Jascha Heifetz, violinist (1901-1987)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-11-29 : 19:18:43

Assuming that you are at SQL 2005 or higher...[CODE]declare @Table table (
CustID int not null,
EventDate datetime not null,
Value int not null
)

;with CustFirstEventPerDay
as (
select
t1.CustID,
t1.EventDate,
DateAdd(Day, DateDiff(Day, 0, t1.EventDate), 0) DateOnly,
t1.Value,
row_number() over (
partition by CustID, DateAdd(Day, DateDiff(Day, 0, t1.EventDate), 0)
order by CustID, DateAdd(Day, DateDiff(Day, 0, t1.EventDate), 0) DESC
) RowNum
from
@Table t1
where RowNum = 1
)
select
t.CustID,
t.EventDate,
t.Value
from
@Table t
inner join
CustFirstEventPerDay f
on t.CustID = f.CustID
and (
(t.EventDate = f.EventDate)
or
(DateAdd(Day, DateDiff(Day, 0, t.EventDate), 0) = f.DateOnly and f.Value > 200)
)[/CODE]HTH

=======================================
No matter what side of the argument you are on, you always find people on your side that you wish were on the other. -Jascha Heifetz, violinist (1901-1987)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-11-30 : 01:12:42

aivoryuk
Starting Member

9 Posts

Posted - 2010-12-02 : 09:13:45

Hi sorry for not getting back to this but I have been trying to work this out.

The code above didn't seem to work for the first table as it kept saying that the column name rownum didn't exist.

I understand the concept so manged to come up with this in the adveture works database (although this is not the database I am using but it is the same code.

with productorder as(
SELECT productnumber,dateadd(day,datediff(day,0,sellstartdate),0)dateonly,listprice,
ROW_NUMBER() OVER(partition by productnumber,dateadd(day,datediff(day,0,sellstartdate),0)
order by productnumber,dateadd(day,datediff(day,0,sellstartdate),0) DESC
) rownum
FROM production.Product)
Select*
into #tmp1
from productorder
where rownum=1

select p.productnumber,p.sellstartdate,p.listprice
from Production.Product as p
inner join #tmp1 tmp
on tmp.ProductNumber=p.ProductNumber
and ((tmp.dateonly=p.SellStartDate)
or(dateadd(day,datediff(day,0,p.SellStartDate),0)=tmp.dateonly and tmp.ListPrice>200))

This works if the first transaction of the day is over £200, it will then bring back all the matching records if in teh same day.
What I also would like it to do is if the first transaction is under £200 I then want it to bring back the rest of the records for that day eg the transaction time that are greater than the first record.

Hope this makes sense

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources