August 26, 2011 at 3:20 pm
hello,
i have a data to be processed in t-sql. The data looks like below:
ordernumber session_time session_id email_id
H78278 2011-09-09 10 54 1 welcome
H78278 2011-09-08 11 12 2 _BCvgsjhj
H78278 2011-09-08 16 14 3 _Bnhjuik
I need to find how many orders where attributed to email_id = ' _BCvgsjhj'. The complication is that it should the maximum or next maximum session_id for that ordernumber. I don't know how to approach this problem? Please help.
Many thanks,
Kind regards
debra
August 26, 2011 at 3:30 pm
It's fairly a simple problem. Can i (we) see the expected result out of the sample data u posted?
August 26, 2011 at 3:39 pm
And i am NOT quite clear with ur ask.can you elaborate more?
August 26, 2011 at 3:51 pm
hello,
so the result would be
email_id = _BCvgsjhj created 1 order
debra
August 26, 2011 at 3:54 pm
complication arises in the following format:
ordernumber session_time session_id email_id
H78278 2011-09-09 10 54 1 welcome
H78278 2011-09-08 11 12 2 _BCvgsjhj
H78278 2011-09-08 11 18 3 _BCvgsjhj
H78278 2011-09-08 16 14 4 _Bnhjuik
here the email_id = _BCvgsjhj should generate 1 order and not 2
August 26, 2011 at 4:12 pm
Which one you want to be chosen ? Session_id = 2 or Session_id = 3 ?
August 26, 2011 at 11:35 pm
i want to choose the latest one..
August 26, 2011 at 11:50 pm
select ordernumber session_time session_id email_id
from table
where session_time=(select max(session_time) from table)
tat will be possible solution by my understanding of ur question!!
August 26, 2011 at 11:52 pm
select ordernumber session_time session_id email_id
from table
where session_time=(select max(session_time) from table)
and email_id=(your desired email)
tat will be possible solution by my understanding of ur question!!
August 26, 2011 at 11:53 pm
Here is the script i am using
select client_order_number as 'client_order_number'
,order_date as 'order_date'
,((convert(datetime,(right(session_start_date,4)
+ substring(session_start_date,3,2) + left(session_start_date,2))))+
(session_start_time)) as 'session_start_datetime'
,mmc_vendor as 'mmc_vendor'
,mmc_placement as 'mmc_placement'
,mmc_item as 'mmc_item'
,rank() over (partition by client_order_number
order by ((convert(datetime,(right(session_start_date,4)
+ substring(session_start_date,3,2) + left(session_start_date,2))))+
(session_start_time))) as 'session_order'
from dbo.tmp_coremetrics
----------------------------------------------------------------------------------
select client_order_number
,max(session_order) as 'max_session_order'
,mmc_vendor as 'mmc_vendor'
into dbo.[tmp_coremetrics_order_lookup_max_session]
from dbo.tmp_coremetrics_order_lookup
group by client_order_number,mmc_vendor
order by client_order_number,mmc_vendor
---------------------------------------------------------------------------------------
the first one would rank the data according to session_order it created..the second one takes the max session order and puts in table. This is fine. But the problem is when an order has mmc_vendor as 'default,freesearch' in the second table then i want it to go back to first table iterate it until it finds mmc_vendor as 'email'..this iteration is where i am stuck.. 🙁
August 27, 2011 at 12:06 am
below is the raw text file to make things clearer which uses the above script:
client_order_number order_date session_start_time mmc_vendor mmc_placement mmc_item
H4246723 20100813 20100813111301 email _bmuy welcom
H4246723 20100813 20100813111302 email _bmuy welcom
H4246723 20100813 20100813111303 email _bmuy welcom
H4246723 20100813 20100813111304 email _bmuy welcom
H4246723 20100813 20100813111305 email _bmuy welcom
H4246723 20100813 20100813111306 email _bmuy welcom
H4246723 20100813 20100813114506 email _bmuy welcom
H4246723 20100813 20100813114507 email _bmuy welcom
H4246723 20100813 20100813114508 google _bmuz welcom
H4246727 20100813 20100813114509 email _bmui welcom
H4246727 20100813 20100813114506 email _bmui welcom
H4246727 20100813 20100813114507 email _bmui welcom
H4246727 20100813 20100813114508 google _bmui welcom
H4246727 20100813 20100813114509 free_search _bmui welcom
in the above data, i need to attribute 1 to _bmui given the order H4246727 was free_search in the last session. when this happens it needs to go back iterate until it finds 'email' and counts that as 1 order..
August 27, 2011 at 9:47 am
well i solved the problem..was excited to share how i did it:
with abc as
(select client_order_number,mmc_vendor,max(session_order) as 'max_session' from
dbo.tmp_coremetrics_order_lookup
group by client_order_number,mmc_vendor)
,abc1 as
(select * from dbo.tmp_coremetrics_order_lookup x
where x.client_order_number in (select client_order_number from abc where mmc_vendor =' free_search')
)
select * into dbo.temporary from abc1
--select * from dbo.temporary
---begin looping here---
declare @max-2 int,@min int
,@mmc_vendor nvarchar(20),@mmc_placement nvarchar(30),@yes nvarchar(20)
set @yes='hello'
set @min-2 = 10;
set @max-2 = 14;
--select @min-2 = min(session_order) from dbo.temporary ;
--select @max-2 = max(session_order) from dbo.temporary ;
print @min-2
print @max-2
begin
select @mmc_vendor = mmc_vendor from dbo.temporary where id=@max;
select @mmc_placement = mmc_placement from dbo.temporary where id=@max;
print @mmc_vendor
print @mmc_placement
if @mmc_vendor = ' email' and @mmc_placement = ' _bmui'
begin
update dbo.temporary
set order_attribution = 'Y'
from dbo.temporary
where 1=1 and id = @max-2
print @yes
set @min-2=@max
end
if @max-2>@min
begin
set @max-2 =@max -1;
end
end
this attributes only one order..
thanks for replying,
kind regards,
debra
August 27, 2011 at 4:14 pm
nairdeepa (8/27/2011)
well i solved the problem..was excited to share how i did it:with abc as
(select client_order_number,mmc_vendor,max(session_order) as 'max_session' from
dbo.tmp_coremetrics_order_lookup
group by client_order_number,mmc_vendor)
,abc1 as
(select * from dbo.tmp_coremetrics_order_lookup x
where x.client_order_number in (select client_order_number from abc where mmc_vendor =' free_search')
)
select * into dbo.temporary from abc1
--select * from dbo.temporary
---begin looping here---
declare @max-2 int,@min int
,@mmc_vendor nvarchar(20),@mmc_placement nvarchar(30),@yes nvarchar(20)
set @yes='hello'
set @min-2 = 10;
set @max-2 = 14;
--select @min-2 = min(session_order) from dbo.temporary ;
--select @max-2 = max(session_order) from dbo.temporary ;
print @min-2
print @max-2
begin
select @mmc_vendor = mmc_vendor from dbo.temporary where id=@max;
select @mmc_placement = mmc_placement from dbo.temporary where id=@max;
print @mmc_vendor
print @mmc_placement
if @mmc_vendor = ' email' and @mmc_placement = ' _bmui'
begin
update dbo.temporary
set order_attribution = 'Y'
from dbo.temporary
where 1=1 and id = @max-2
print @yes
set @min-2=@max
end
if @max-2>@min
begin
set @max-2 =@max -1;
end
end
this attributes only one order..
thanks for replying,
kind regards,
debra
Good lord, NO! 😀 The RBAR in that solution will take a month of Sundays to run. Please take a look at the first link in my signature line for how to post some "readily consumable" data. If you'd take the time to do that, I'll show you a high performance set based methods to do this.
As a side bar, 99.99% of the time, if it has a While loop in it, it's wrong. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 27, 2011 at 10:34 pm
Hi Jeff,
I am not clear on what you just said. could you please elaborate?
Many thanks,
August 28, 2011 at 7:55 am
Jeff Moden (8/27/2011)
Good lord, NO! 😀 The RBAR in that solution will take a month of Sundays to run. Please take a look at the first link in my signature line for how to post some "readily consumable" data. If you'd take the time to do that, I'll show you a high performance set based methods to do this.
As a side bar, 99.99% of the time, if it has a While loop in it, it's wrong. 😉
Hi debra
When Jeff mentions 'RBAR'....he is telling you that your solution is not the most effective solution...'RBAR' = Row By Agonizing Row...if you scroll to the bottom of his posts you will find links that you can click on to take you to other pages with more details
Jeff is also asking that you provide some scripts that will CREATE/INSERT data that fully represent the problem that you have...if you post a script that provides this, then it is much easier for us to replicate in our own systems and then work on, to provide you with a potential solution.
Please click on this link http://www.sqlservercentral.com/articles/Best+Practices/61537/ to undertand what helps us help you.....if you follow the advice and post as described in the link then I am sure that you will receive faster and tested solutions.
I would strongly suggest that you also provide a clear definition and example of the result that you require...based on the data that you have provided...this will assist greatly and negate possible confusion.
Back to your post....
the data you have provided in different posts and the solution you posted......dont really seem to tie together easily....this is probably because I haven't fully understood your requirements....and further reason to please post as requested by Jeff.
That said...here is a possible solution, based on my, limited, understanding of what you really require.
Please note that this is only one method...there are other methods that may be more appropriate...these may be dependent upon your volume of data and a clear definition from you of what you expect the results to contain
---=====++++++++++++++++++++++++++++++++++++++
USE TempDB
;
---conditionally drop the test table
IF OBJECT_ID('TempDB.dbo.TMPDATA','U') IS NOT NULL
DROP TABLE TempDB.dbo.TMPDATA
---CREATE a table based on your 'raw text file' you posted
CREATE TABLE [dbo].[TMPDATA](
[client_order_number] [nvarchar](50) NULL,
[order_date] [int] NULL,
[session_start_time] [bigint] NULL,
[mmc_vendor] [nvarchar](50) NULL,
[mmc_placement] [nvarchar](50) NULL,
[mmc_item] [nvarchar](50) NULL
) ON [PRIMARY]
GO
---Populate the table above with the data that you provided
---Note...I have slighly altered the session_start_time data for readability
INSERT INTO [dbo].[TMPDATA]([client_order_number], [order_date], [session_start_time], [mmc_vendor], [mmc_placement], [mmc_item])
SELECT N'H4246723', 20100813, 20100813111301, N'email', N'_bmuy', N'welcom' UNION ALL
SELECT N'H4246723', 20100813, 20100813111302, N'email', N'_bmuy', N'welcom' UNION ALL
SELECT N'H4246723', 20100813, 20100813111303, N'email', N'_bmuy', N'welcom' UNION ALL
SELECT N'H4246723', 20100813, 20100813111304, N'email', N'_bmuy', N'welcom' UNION ALL
SELECT N'H4246723', 20100813, 20100813111305, N'email', N'_bmuy', N'welcom' UNION ALL
SELECT N'H4246723', 20100813, 20100813111306, N'email', N'_bmuy', N'welcom' UNION ALL
SELECT N'H4246723', 20100813, 20100813111307, N'email', N'_bmuy', N'welcom' UNION ALL
SELECT N'H4246723', 20100813, 20100813111308, N'email', N'_bmuy', N'welcom' UNION ALL
SELECT N'H4246723', 20100813, 20100813111309, N'google', N'_bmuz', N'welcom' UNION ALL
SELECT N'H4246727', 20100813, 20100813114505, N'email', N'_bmui', N'welcom' UNION ALL --- altered from 20100813114509 to 20100813114505
SELECT N'H4246727', 20100813, 20100813114506, N'email', N'_bmui', N'welcom' UNION ALL
SELECT N'H4246727', 20100813, 20100813114507, N'email', N'_bmui', N'welcom' UNION ALL
SELECT N'H4246727', 20100813, 20100813114508, N'google', N'_bmui', N'welcom' UNION ALL
SELECT N'H4246727', 20100813, 20100813114509, N'free_search', N'_bmui', N'welcom'
---==== One solution could be the following, however we do not know your volume of data to provide the most efficient method.
;WITH CTE AS
(
SELECT client_order_number, order_date, mmc_placement, mmc_vendor,session_start_time,
Row_number() OVER (PARTITION BY client_order_number ORDER BY session_start_time DESC) rn
FROM tmpdata
WHERE mmc_vendor = 'email'
)
select * from CTE where rn = 1
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply