August 28, 2011 at 12:28 pm
nairdeepa (8/27/2011)
Hi Jeff,I am not clear on what you just said. could you please elaborate?
Many thanks,
Gah covered that pretty well for me. He also provided an example of how to provide the "readily consumable" data for your problem as well as providing a solution.
The question now is, does Gah's solution do the trick for you?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 28, 2011 at 12:41 pm
Jeff Moden (8/28/2011)
nairdeepa (8/27/2011)
Hi Jeff,I am not clear on what you just said. could you please elaborate?
Many thanks,
Gah covered that pretty well for me. He also provided an example of how to provide the "readily consumable" data for your problem as well as providing a solution.
The question now is, does Gah's solution do the trick for you?
Sidebar....Jeff
I altered my SSC nickname a few days ago....however it appears that you are still picking up "gah"....I am currently posting with a different nickname....have you any ideas why?
when I review my posts I see my new nickname...seems you dont ???
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 28, 2011 at 2:33 pm
Thanks Gah for the clarifications. I will follow the forum requirements as suggested.Regarding your solution, I did try to execute the same, but it does not give me any results. Am i missing something here?
Thanks for your help!
August 28, 2011 at 2:42 pm
Hi Gah,
Sorry about my earlier post, I forgot to put the right parameters. 'email' is written as ' email' with space in my dataset. your solution works for me, and i am absolutely thrilled by it. How does row_number work? I am very new to sql as you would have guessed it. I know there is lots of material online for row_number, maybe i should go thru them.
Thanks a lot for your timely help,
debra
August 28, 2011 at 4:27 pm
Hello Gah,
Based on your query, I was able to modify mine. Many thanks to you..My query now looks like below 🙂
use crm_project;
go
;With abc as
(select client_order_number as 'client_order_number'
,(convert(datetime,(left(session_start_date,4)
+ substring(session_start_date,3,2) + right(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,(left(session_start_date,4)
+ substring(session_start_date,3,2) + right(session_start_date,2)+ ' '
+ (session_start_time))))) as 'session_order'
from dbo.tmp_coremetrics)
, abc2 as
(select client_order_number,mmc_vendor,mmc_placement,max(session_order)as
'max_session' from abc
group by client_order_number,mmc_vendor,mmc_placement
having mmc_vendor = ' free_search')
,abc3 as
(select client_order_number, mmc_placement, mmc_vendor,session_start_datetime,
Row_number() OVER (PARTITION BY client_order_number ORDER BY session_start_datetime DESC) rn
from abc where client_order_number in ( select client_order_number from abc2))
, abc4 as (select client_order_number, mmc_placement,mmc_vendor, session_start_datetime
from abc3 where mmc_placement = ' _bmui' and mmc_vendor = ' email')
select * into dbo.tmp from
(select client_order_number,max(session_start_datetime) as 'max_date' from abc4
group by client_order_number) x
update dbo.tmp_coremetrics_order_lookup
set order_attribution = 'Y'
from dbo.tmp_coremetrics_order_lookup x
inner join dbo.tmp y
on x.client_order_number = y.client_order_number
and x.session_start_datetime = y.max_date
go
This works out fine, order is attributed to only one desired column.
debra
August 28, 2011 at 4:48 pm
J Livingston SQL (8/28/2011)
Jeff Moden (8/28/2011)
nairdeepa (8/27/2011)
Hi Jeff,I am not clear on what you just said. could you please elaborate?
Many thanks,
Gah covered that pretty well for me. He also provided an example of how to provide the "readily consumable" data for your problem as well as providing a solution.
The question now is, does Gah's solution do the trick for you?
Sidebar....Jeff
I altered my SSC nickname a few days ago....however it appears that you are still picking up "gah"....I am currently posting with a different nickname....have you any ideas why?
when I review my posts I see my new nickname...seems you dont ???
It's the unique avatar you have... I didn't even look at the name. I just said "that's Gah" and knew the op was in good hands. 🙂
Now that I finally look at the name, you're new "handle" is coming through just fine.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2011 at 10:22 am
nairdeepa (8/28/2011)
Hello Gah,Based on your query, I was able to modify mine. Many thanks to you..My query now looks like below 🙂
use crm_project;
go
;With abc as
(select client_order_number as 'client_order_number'
,(convert(datetime,(left(session_start_date,4)
+ substring(session_start_date,3,2) + right(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,(left(session_start_date,4)
+ substring(session_start_date,3,2) + right(session_start_date,2)+ ' '
+ (session_start_time))))) as 'session_order'
from dbo.tmp_coremetrics)
, abc2 as
(select client_order_number,mmc_vendor,mmc_placement,max(session_order)as
'max_session' from abc
group by client_order_number,mmc_vendor,mmc_placement
having mmc_vendor = ' free_search')
,abc3 as
(select client_order_number, mmc_placement, mmc_vendor,session_start_datetime,
Row_number() OVER (PARTITION BY client_order_number ORDER BY session_start_datetime DESC) rn
from abc where client_order_number in ( select client_order_number from abc2))
, abc4 as (select client_order_number, mmc_placement,mmc_vendor, session_start_datetime
from abc3 where mmc_placement = ' _bmui' and mmc_vendor = ' email')
select * into dbo.tmp from
(select client_order_number,max(session_start_datetime) as 'max_date' from abc4
group by client_order_number) x
update dbo.tmp_coremetrics_order_lookup
set order_attribution = 'Y'
from dbo.tmp_coremetrics_order_lookup x
inner join dbo.tmp y
on x.client_order_number = y.client_order_number
and x.session_start_datetime = y.max_date
go
This works out fine, order is attributed to only one desired column.
debra
Hi debra
I am glad to hear that your code works...however I see 4 CTEs and a further temp table ...all to update one row ???
I am not entirely sure what you are trying to do, and without necessary CREATE TABLE and INSERT code it is very difficult to help you further.
Maybe I am wrong but I beleive this could be simplified.
I appreciate that you are new to SQL and therefore suggest that if you can find time to provide the requested code we may be able to help you "tidy" your code....we would also require expected results.
so...can you provide for
dbo.tmp_coremetrics and dbo.tmp_coremetrics_order_lookup
kind regards
gah
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 29, 2011 at 10:15 pm
Hello Gah,
Thanks for responding. The tables have not changed at all. Just one column is added which is order_attribution. To start from scratch, below is the code which creates and inserts the tables as required:
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
,order_attribution 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
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'
---Now what is the problem..basically the order_attribution column should be 'Y' in the row that has mmc_vendor = 'email' and mmc_placement ='_bmui' if the last order_date is through mmc_vendor = 'free_search'. To put it simply what happens is that when mmc_vendor is 'free_search', the pointer (or watever u call it in sql) should iterate back with same client_order_number until it finds the next column which has mmc_vendor as 'email'
and mmc_placement ='_bmui'. What this means is that the order is originally coming from an email so we want through to be attributed.
I hope this should make sense now. Forget about all the tables i built. This is my mail problem. I may not have developed the most efficient code. That's not my priority. I need to get the job done in this short span of time. Thanks a lot for your help,
Kind regards,
Debra
August 30, 2011 at 9:59 am
Hi,
Could some one help me with this 🙁
Thanks,
Debra
August 30, 2011 at 2:25 pm
Give this a try:
update a
set order_attribution = 'Y'
from TMPDATA a
where
mmc_vendor = 'email'
and mmc_placement = '_bmui'
and exists (
select 1
from TMPDATA b
where
b.session_start_time > a.session_start_time
and b.mmc_vendor = 'free_search'
)
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
August 30, 2011 at 10:58 pm
thanks..i will try this one and let u know.
August 30, 2011 at 11:00 pm
the only problem i see though i have not tried this.is that it might update everything..and i want only the last one to be updated
August 31, 2011 at 12:33 am
try this debra
probably requires some more work
---=====++++++++++++++++++++++++++++++++++++++
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 data
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
,order_attribution nvarchar(50) null
) ON [PRIMARY]
GO
---Populate the table with the data that you provided
---Note...I have also added some extra data to further test the code
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
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' UNION ALL
---===== additional data added from here to test further your requirements
---===== please add/amend to create a "real life" view of your actual data
SELECT N'H4246728', 20100813, 20100813114505, N'email', N'_bmui', N'welcom' UNION ALL
SELECT N'H4246728', 20100813, 20100813114506, N'email', N'_bmui', N'welcom' UNION ALL
SELECT N'H4246728', 20100813, 20100813114507, N'email', N'_bmui', N'welcom' UNION ALL
SELECT N'H4246728', 20100813, 20100813114508, N'free_search', N'_bmui', N'welcom' UNION ALL
SELECT N'H4246728', 20100813, 20100813114509, N'email', N'_bmui', N'welcom' UNION ALL
SELECT N'H4246729', 20100813, 20100813114505, N'email', N'_bmui', N'welcom' UNION ALL
SELECT N'H4246729', 20100813, 20100813114506, N'email', N'_bmui', N'welcom' UNION ALL
SELECT N'H4246729', 20100813, 20100813114507, N'google', N'_bmui', N'welcom' UNION ALL
SELECT N'H4246729', 20100813, 20100813114508, N'google', N'_bmui', N'welcom' UNION ALL
SELECT N'H4246729', 20100813, 20100813114509, N'free_search', N'_bmui', N'welcom'
---==== display table with intitial data
SELECT *
FROM TMPDATA
ORDER BY client_order_number,
session_start_time
---=== update TMPDATA.....there are other methods that may prove more efficient and more elegant, particularly based on data volumes..
---=== suggest we wait for the 'gurus' <grin>
---=== what are your data volumes ???
---=== NB...there are no indexes ,,,this will improve performance on larger data sets
UPDATE TMPDATA
SET order_attribution = N'Y'
FROM TMPDATA AS C
INNER JOIN (SELECT x.client_order_number,
MAX(session_start_time) AS MAXSESS
FROM (SELECT client_order_number,
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 client_order_number IN (SELECT DISTINCT client_order_number
FROM TMPDATA
WHERE mmc_vendor = 'free_search')) x
WHERE ( mmc_vendor = N'email' )
AND ( mmc_placement = N'_bmui' )
GROUP BY client_order_number) AS T
ON C.client_order_number = T.client_order_number
AND C.session_start_time = T.MAXSESS
---==== display updated table
SELECT *
FROM TMPDATA
WHERE order_attribution = 'Y'
---=== have included toddassd solution and results as well for comparison
UPDATE a
SET order_attribution = 'Y'
FROM TMPDATA a
WHERE mmc_vendor = 'email'
AND mmc_placement = '_bmui'
AND EXISTS (SELECT 1
FROM TMPDATA b
WHERE b.session_start_time > a.session_start_time
AND b.mmc_vendor = 'free_search')
---==== display updated table
SELECT *
FROM TMPDATA
WHERE order_attribution = 'Y'
--- WHICH IS CORRECT???
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 31, 2011 at 7:15 am
nairdeepa (8/30/2011)
the only problem i see though i have not tried this.is that it might update everything..and i want only the last one to be updated
Umm...when we say "try this" or something like that, we don't mean try it in your production system. Test the code on sample data, test the code on dev database, test X 100 scenarios until you are positive it works the way you want. Right?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
August 31, 2011 at 1:34 pm
toddasd (8/31/2011)
nairdeepa (8/30/2011)
the only problem i see though i have not tried this.is that it might update everything..and i want only the last one to be updatedUmm...when we say "try this" or something like that, we don't mean try it in your production system. Test the code on sample data, test the code on dev database, test X 100 scenarios until you are positive it works the way you want. Right?
will be interested to see if either of us have interpreted the requirements correctly...hopefully OP will post back
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply