CODEWITHSUNDEEP
sql-server
RARV
RARV

Reputation: 31

Generate values

I have the following scenario, I have a customer that on my db he buy particular combination of information, what I mean is that currently the table show the name of the customer, Category, place, yearmonth, country, C1,C2,delivery,frequency and an example looks like something like this.

Customer    Categ   Place   YM      Country C1  C2  delivery    Frequency
ALB        Clock    WM      201609  France  1   1    name   Trimestral- 3,6,9,12
ALB        car      NewE    201610  Spain   1   0           Trimestral- 3,6,9,12
ALC        TV       AWS     201611  Guate   1   0           Trimestral- 3,6,9,12
ALB        smart    Eb      201612  Japan   1   1           Trimestral- 3,6,9,12

Sometime delivery have name and some other it wont depending on the customer also C2 depend on the frequency and it is related to YM.

Let's say if frequency is trimestral well I need C2 to be 1 when the month is 3/6/9/12 but I would to have to option to generate the value from 201701 till 201712 and the other only to generate values when it is 201703/201706 and so on.

All this value will be generate using the last four months of current year.

If you have any question, please let me know because I know it is kind of tricky

I am using case sentence but it is duplicating combination what I mean is creating a row with c2 = 1 and c2 = 0

Customer    Categ   Place   YM      Country C1  C2  delivery    Frequency
ALB        Clock    WM      201609  France  1   1    name   Trimestral- 3,6,9,12
ALB        Clock    WM      201609  France  1   0    name   Trimestral- 3,6,9,12
case
        when @Frecuencia_De_Proceso = 'Anual' and cast(right([Año_Mes],2) as int) % 12 = 0 and b.cadena <> 'Club Co'  then 1
        when @Frecuencia_De_Proceso in ('Bimensual','Bimensual 2,4,6,8...', 'Bimensual II') and cast(right([Año_Mes],2) as int) % 2 = 0 and b.cadena <> 'Club Co'  then 1
        when @Frecuencia_De_Proceso in ('Bimensual I')and cast(right([Año_Mes],2) as int) % 2 = 1 and b.cadena <> 'Club Co'   then 1


        when @Frecuencia_De_Proceso = 'Trimestral I' and cast(right([Año_Mes],2) as int) % 3 = 1 and b.cadena <> 'Club Co'  then 1
        when @Frecuencia_De_Proceso = 'Trimestral I' and cast(right([Año_Mes],2) as int) % 3 = 2 and b.cadena <> 'Club Co'  then 1
        when @Frecuencia_De_Proceso in('Trimestral- 3,6,9,12', 'Trimestral III') and cast(right([Año_Mes],2) as int) % 3 = 0 and b.cadena <> 'Club Co' then 1


        when @Frecuencia_De_Proceso = 'Semestral' and  cast(right([Año_Mes],2) as int) % 6 = 0 and b.cadena <> 'Club Co' and  @Especial = '' then 1

        when @Frecuencia_De_Proceso = 'Mensual'  and b.cadena <> 'Club Co' then 1 
        when  @Especial = 'Especial' and Año_Mes in (201610,201611,201612) and b.cadena <> 'Club Co' then 1
       else  

      0 end ) as [Entregar]

Maybe because I want to generate unique values and don't know how please help or advice how to do it.

Here is the whole query..

DECLARE @startnum INT=201701
DECLARE @endnum INT=201712
;
WITH gen AS (
    SELECT @startnum AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=@endnum
)
SELECT * into #Temp FROM gen
option (maxrecursion 13)



select distinct Customer, Category,Place,b.num,country,

C1, 

case
        when Frecuency = 'Anual' and cast(right([Año_Mes],2) as int) % 12 = 0 and a.Place <> 'Club Co'  then 1

        when Frecuency in ('Bimensual','Bimensual 2,4,6,8...', 'Bimensual II') and cast(right([Año_Mes],2) as int) % 2 = 0 and a.Place <> 'Club Co'  then 1
        when Frecuency in ('Bimensual I')and cast(right([Año_Mes],2) as int) % 2 = 1 and a.Place <> 'Club Co'   then 1


        when Frecuency = 'Trimestral I' and cast(right([Año_Mes],2) as int) % 3 = 1 and a.Place <> 'Club Co'  then 1
        when Frecuency = 'Trimestral I' and cast(right([Año_Mes],2) as int) % 3 = 2 and a.Place <> 'Club Co'  then 1
        when Frecuency in('Trimestral- 3,6,9,12', 'Trimestral III') and cast(right([Año_Mes],2) as int) % 3 = 0 and a.Place <> 'Club Co' then 1


        when Frecuency = 'Semestral' and  cast(right([Año_Mes],2) as int) % 6 = 0 and a.Place <> 'Club Co'  then 1

        when Frecuency = 'Mensual'  and a.Place <> 'Club Co' then 1 

       else  

      0 end  as  C2,



case when [name] <> '' then '' else [name] end as [name],


Frecuency,Cortes 

--into [XtraTest].dbo.OP2017_B 

from DB.dbo.Prod  a,
#Temp b
where Año_Mes between 201608 and 201612 and Frecuency  not in ('BackData','Cancelado')
and Frecuency in ('Bimensual','Bimensual 2,4,6,8...', 'Bimensual II')

order by Customer,num,Place

Upvotes: 0

Views: 68

Answers (1)

SqlZim
SqlZim

Reputation: 38033

updated with testing data

I think you need to get your distinct rows from db.dbo.Prod before doing the cross join with your generated values.

You also might have Trimestral I twice instead of having a Trimestral II, noted in the code below.

Your case statement could be optimized a little bit too, like so:

Rextester: http://rextester.com/VQA87142

create table Prod (Customer varchar(32),Categ varchar(32),Place varchar(32),YM int,Country varchar(32),C1 int,delivery varchar(32),Frequency varchar(32));
insert into Prod  (Customer,Categ,Place,YM,Country,C1,delivery,Frequency) values
 ('ALB','Calendar','NewE',201610,'Spain' ,1,'','Anual')
,('ALB','Clothes','Eb'  ,201609,'Japan' ,1,'','Semestral')
,('ALB','Manga','Eb'  ,201612,'Japan' ,1,'','Mensual')
,('ALB','Razor','Eb'  ,201612,'Japan' ,1,'','Bimensual I')
,('ALB','Toy','Eb'  ,201612,'Japan' ,1,'','Bimensual II')
,('ALB','Clock','WM'  ,201609,'France',1,'name','Trimestral I')
,('ALB','car'  ,'NewE',201610,'Spain' ,1,'','Trimestral II')
,('ALC','TV'   ,'AWS' ,201611,'Guate' ,1,'','Trimestral III')
,('ALB','Widget','Club Co',201608,'Denmark',1,'','Trimestral- 3,6,9,12');


declare @startnum int=201701;
declare @endnum   int=201712;

with gen as (
    select @startnum as num,  mth=month(convert(date,convert(nvarchar(8),@startnum)+'01',120))
    union all
    select num+1, mth=month(convert(date,convert(nvarchar(8),num+1)+'01',120)) from gen where num+1<=@endnum
)
select * into #Temp from gen
option (maxrecursion 12);

with DistinctProd as (
  select distinct 
    Customer
  , Category = Categ
  , Place
  , country
  , C1
  , [Name] = case when [Delivery] <> '' then '' else [Delivery] end 
  , Frequency
  --, Cortes 
  from Prod a
  where YM between 201608 and 201612
    and Frequency not in ('BackData','Cancelado')
    --and Frequency in ('Bimensual','Bimensual 2,4,6,8...', 'Bimensual ii')
)
, GenC2 as (
select 
    Customer
  , Category
  , Place
  , b.num
  , country
  , C1
  , C2 = case
           when a.Place = 'Club Co' 
             then 0
           when Frequency = 'Anual' 
             and mth % 12 = 0 
               then 1
           when Frequency in ('Bimensual','Bimensual 2,4,6,8...', 'Bimensual ii') 
             and mth % 2 = 0
               then 1
           when Frequency in ('Bimensual I')
             and mth % 2 = 1 
               then 1
           when Frequency = 'Trimestral I' 
             and mth % 3 = 1 
               then 1
           when Frequency = 'Trimestral II' -- I think this is meant to be Trimestral II' 
             and mth % 3 = 2
               then 1
           when Frequency in('Trimestral- 3,6,9,12', 'Trimestral iii') 
             and mth % 3 = 0 
               then 1
           when Frequency = 'Semestral' 
             and  mth % 6 = 0 
               then 1
           when Frequency = 'Mensual' 
             then 1 
          else 0 
          end
  , [Name] 
  , Frequency
  , Mth
  --, Cortes 
--into [XtraTest].dbo.op2017_B 
  from DistinctProd as a
    cross join #Temp as b 
    )
    select * 
    from GenC2 
    --where c2=1
  order by Customer,Place,Category,num

Upvotes: 1

Related Questions