January 3, 2008 at 9:29 am
Hi,
I have a table with 5 columns, 4 of these make up the primary key.
The first to third will be date, store, transaction number. So this might be one overall transaction
The 4th value is product number within the transaction, which overall gives it its uniqueness as there might be more than one product number per transaction number.
The 5th column can have the entry 1 or 2 in it.
I want to run a query which will select all these rows, but if the 5th column of any
of the rows within the same transaction is 2, it will show the value 2 in the 5th
column for all the rows for this transaction number
date store trans_number prod_number random
1/1/8 46 878787 1 1
1/1/8 46 878787 2 2
1/1/8 46 878787 3 1
Therefore in the above scenario I want to run a report where it selects all these rows,
but for any same date\store\trans_number I want to display 2 in the 5th column (or a derived column) for each prod_number ie taking the above table I want to display 2,2,2 in the last (or derived) column rather than what it actually is ie 1,2,1
Hope that makes sense
thanks
January 3, 2008 at 9:46 am
You are doing a cross tab report, moving columns to rows. Search for cross tab and check out code examples.
January 4, 2008 at 2:15 am
select t1.date,t1.store,t1.trans_number,t1.prod_number
,case when maxrandom=2 then 2 else random end as random
from Mytable t1
,(select date,store,trans_number,max(random) maxrandom from Mytable group by date,store,trans_number ) tempTable
where t1.date=tempTable.date and t1.store=tempTable.store and t1.trans_number=tempTable.trans_number
http://transactsql.blogspot.com/
January 4, 2008 at 5:00 am
Thanks for your help on this guys but please bear with me as cross tabs is something I've not dealt with before.
ZeyNep, unfortunately I cant get this to work at all but I'm sure its just me or maybe something syntax related!?
The other difficulty is that I need to include this as part of an overall query. Anyway in the query you provided what is maxrandom in line 2? Can you also check the syntax and maybe break it down so I can understand it more easily.
Maybe it'll be easier to show you the code I've started and where it has to slot in. I'll then not get confused changing column names I've used to get this answer with what I really have to code. So here it is, with the bit in /* */ the piece I need to code.
Thanks once again...
select m.market_code as [MARKET],
'TBSI' as [BRAND],
d.SiebelTransNumber as [ORDH_NUM],
/*
Insert into here....
When the highest d.line_type within a transaction group is 2 THEN return 'Return Order'
ELSE return 'Sales Order'
A transaction group is made up of d.Source_Channel, d.Trans_date, d.Site, d.Register, d.Trans_Number
*/
d.line_number as [ORDL_NUM],
CASE d.line_type
WHEN '1' THEN 'Normal Sales'
WHEN '2' THEN 'Sales Return'
END AS [ORDL_TYPE],
d.global_article_code as [PRODUCT],
cast((d.gross_value/d.line_quantity) as decimal (38,2)) as [UNIT PRICE],
d.line_quantity as [QUANTITY],
d.line_value as [ORDL_AMOUNT],
NULL as [RETURN_REASON_CD],
NULL as [PROMOTION_CODE],
NULL as [CAMPAIGN_CODE]
from detail d
inner join market m
on d.site = m.store
group by d.Source_Channel, d.Trans_date, d.Site, d.Register, d.Trans_Number,
m.Market_Code, d.SiebelTransNumber, d.Line_type, d.line_number,
d.Global_Article_Code, d.Gross_Value, d.Line_Quantity, d.Line_value
January 4, 2008 at 5:01 am
Sorry, I should have confirmed that...
When the highest d.line_type within a transaction group is 2 THEN return 'Return Order' FOR ALL THE ROWS THAT MAKE UP THAT TRANSACTION GROUP ie instead of 1,2,1 make 2,2,2
January 4, 2008 at 5:29 am
HI All,
Have you tried using the OVER clause?
Something like this should get you on your way.
SELECT
date
,store
,Trans_number
,prod_number
,random
,MAX(random ) OVER(PARTITION BY date ,store,Trans_number ) AS 'Max'
FROM dbo.YourTable
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 4, 2008 at 5:42 am
Thanks Chris but isn't that SQL2005 only ?
January 4, 2008 at 5:46 am
Hi,
Yip that is only in 2005. Sorry what version is being used for this problem?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 4, 2008 at 6:38 am
SQL2000, I put the question in the 2000 forum as I suspected 2005 would have an easier method. Useful tip anyway though 😉
January 4, 2008 at 7:17 am
I've searched the cross tab reports and found nothing similar, a precise example would be much appreciated.
ZeyNep, I've also got a little further with your query but whichever way I try it it will only return one row per transaction group ie the max value. I need the max value to be populated for each row in the transaction 'group' of rows.
Thanks for your help so far.
January 4, 2008 at 7:30 am
Hi Guys,
How about trying this in 2000
SELECT
[1].Col1
,[1].Col2
,[1].Col3
,[1].Col4
,[Max]
FROM dbo.Temp [1]
INNER JOIN (SELECT Col1,Col2,Col3,MAX(Col4) as [MAX] FROM dbo.Temp GROUP BY Col1,Col2,Col3) [2]
ON [1].[Col1] = [2].Col1
AND [1].[Col2] = [2].Col2
AND [1].[Col3] = [2].Col3
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 8, 2008 at 2:56 am
Christopher, that works a treat!! Thanks v much guys.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply