Using CTE

Question

Post reply

Using CTE

luissantos

SSCertifiable

Points: 7391
More actions
April 1, 2011 at 5:00 pm

#237322

Hello comunity
I post this script because i need to return my sales by:
CUST_no, Valor Coberto, Valor sem Cobertura
the script is :
-- create test table
create table #Table (seguro varchar(30), Pais varchar(30), CUST_NO varchar(100), VendaSeguras numeric(8,2) )
--Insert records
insert into #Table (seguro , Pais , CUST_NO , VendaSeguras )
values
('1-seguro','porto',15100,25000)
insert into #Table (seguro , Pais , CUST_NO , VendaSeguras )
values
('2- n/Seguro','Real',12402,4000)
-- sample code with CTE
;WITH CTE_RowNum(Tipo,Pais, CUST_NO , valor) AS(
--SELECT CUST_NO ,
--ROW_NUMBER() OVER(Partition by CUST_NO ORDER BY CUST_NO, Mes),
SELECT seguro ,Pais, CUST_NO , VendaSeguras
FROM #Table )
,CTE_PivDisc (CUST_NO, D1) AS(
SELECT CUST_NO, [1]
FROM (
SELECT Tipo ,Pais, CUST_NO , valor
FROM CTE_RowNum
) p
PIVOT (
SUM (Valor)
FOR Tipo in ([1])
) AS pvt)
, CTE_PivRule (CUST_NO, R2) AS(
SELECT CUST_NO, [2]
FROM (
SELECT Tipo ,Pais, CUST_NO , valor
FROM CTE_RowNum
) p
PIVOT (
sum (Valor)
FOR Tipo IN ([2])
) AS pvt)
SELECT CTE_PivRule.cust_no,
SUM(D1) [VALOR Coberto],SUM(R2) [Valor sem cobertura]
FROM CTE_PivRule
LEFT OUTER JOIN CTE_PivDisc
ON CTE_PivRule.cust_no = CTE_PivDisc.cust_no
GROUP BY CTE_PivRule.cust_no
The problem is the column
CUST_no have my customernumber but the columns: Valor Coberto, Valor sem Cobertura are both = NULL
i need to put on theses 2 columns values for
first : if '1-seguro' then column Valor Coberto must have the value : 25000€
else if '2- n/Seguro' then column Valor sem Cobertura must have the value : 4000€
Anyone could give my an explanation about why does not work, because i want begin use this type of CTE whit PIVOT or NOT.
Many thanks
Luis Santos

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

luissantos SSCertifiable Points: 7391 More actions · Answer 1

Hello comunity

Don´t worry anymore about my problem , but i solve them.

best regards

Luis Santos

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

luissantos (4/3/2011)
Hello comunity
Don´t worry anymore about my problem , but i solve them.
best regards
Luis Santos

Great that you yourself had tried and solved the problem.can you post how did you achive your goal so that peoples who tried to solve the solution might know what is the answer,so that peoples will give you better solution than you have tried or vise versa.

Thanks
Parthi