mattech06 (6/18/2014)

---

that works for the sample but when I apply it to the working sql I get a problem with the group by

;WITH Main as

(

SELECT

DISTINCT t.transactionID, b.TransDate, b.TransType,...

gurbanov.1984 (6/18/2014)

---

thanks for your precious time

but why us # RG_SalesOut_Report (test pattern) that it give us?

baseline data about 12 million lines and how you want to write such...

sqlnaive (6/18/2014)

---

I was able to get the much needed solution. Here are the queries to generate the data:

create table #t11

(col1 varchar(10), col2 int, col3 varchar(10))

insert into #t11

select 'XYZ', 12, 'RXM'...

gurbanov.1984 (6/18/2014)

---

3 performs the query:

1) adds counter

2) adds a column Code

3) sorts the three columns

example

ClientID Product StatementID M_Sales

9 SKU1 01/05/2014 1

9...

Post the query you're using for the first result set, and the usual sample data. I'd guess a combination of ROW_NUMBER() to identify the rows to keep, and FOR XML...

gurbanov.1984 (6/18/2014)

---

...

thanks for your precious time

but why you do not use order by

it is a basic condition for the calculation field (M_Sales), if we miss, the query gets...

gurbanov.1984 (6/18/2014)

---

ClientIDCustomerGroupStatementIDBrandProductAmountValue_CPM_SALES

988439All Channels01.05.2014Brand 1SKU 1274,7009880

988370All Channels01.05.2014Brand 1SKU 2259,8236220

988285All Channels01.05.2014Brand 1SKU 3148,9573520

988275All Channels01.05.2014Brand 1SKU 1137,3504950

988231All Channels01.05.2014Brand 1SKU 2129,9118110

988134All Channels01.05.2014Brand 1SKU 4136,9365050

988033All Channels01.05.2014Brand 1SKU 4136,9365050

988023All Channels01.05.2014Brand 1SKU 2389,7354330

987950All Channels01.05.2014Brand 1SKU 1137,3504950

987950All Channels01.05.2014Brand...

Can you post your solution please kbhanu15? It would be very handy for other folks who...

Peter.Frissen (6/17/2014)

---

Thanks,

My problem is a bit more complex, I thought about this concatenation-solution but actually this colB is not one column but several columns. I could still concatenate the values...

rodjkidd (6/16/2014)

---

ChrisM@Work (6/16/2014)

---

Ed Wagner (6/16/2014)

---

GilaMonster (6/14/2014)

---

Yup, that's pretty much how I feel.

I have little interest in sport. Most of the time that's fine. This week,...

itortu (6/16/2014)

---

this is my script, create and insert.

i want to point out that if i run this script:

select * from [dbo].[salesquotestest] WHERE prioriquotedate IN (SELECT MAX(prioriquotedate) FROM [dbo].[salesquotestest] )

i...

-- Table @CurrentQuoteItemComponentTable cqic doesn't play any useful role in the query.

SELECT

[PriorQuoteNumber]= s.SalesQuoteNumber,

[PriorItemNumber]= s.ItemNumber,

[PriorQuoteDate]= s.RequestedDeliveryDate

FROM dw.cost.vStd_SalesQuoteCostLineBOM s

WHERE s.CustomerNumber = @CustomerNumber

AND s.SalesQuoteNumber <> @SalesQuoteNumber

AND s.RequestedDeliveryDate = (

SELECT [most recent delivery...

TomThomson (6/16/2014)

---

ChrisM@Work (6/16/2014)

---

Ed Wagner (6/16/2014)

---

A lot of people sure do like it, though. I played soccer when I was 10 (before soccer was fashionable in the US), but...

Luis Cazares (6/16/2014)

---

Nice work Chris,

I would make a small modification because it's losing values from the last day.

;WITH RecordedMsgs (CallDate, Extension, CallerID, CalledID) AS (

SELECT '2011-12-04 01:59:59.999', 'night', 1, 20...

DROP TABLE #RecordedMsgs

;WITH RecordedMsgs (CallDate, Extension, CallerID, CalledID) AS (

SELECT '2011-12-04 01:59:59.999', 'night', 1, 20 UNION ALL

SELECT '2011-12-04 02:00:00.000', 'morn', 1, 30 UNION ALL

SELECT '2011-12-04 07:59:59.999', 'morn', 2, 30 UNION...