Ilmu Sql

SELECT
t.NAME AS Nama_tabel,
p.rows AS Jml_baris,
SUM(a.total_pages) * 8 AS Total_KB,
SUM(a.used_pages) * 8 AS Terpakai_KB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS Tdk_terpakai_KB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, p.Rows
ORDER BY
t.Name

select idcovjob, a.idprod, quantity*hna amount, 2013 year, 4 month, quantity, hna price
    --into #targetbychannel
    from (
        select idcovjob, a.idprod, sum(quantity) quantity
        from mtargetchanneldetail a
        where year=2013 and month=5 and idprod=242
        group by idcovjob, idprod
    ) a
    inner join (
        select a.idprod, hna from mproductprice a
        inner join (
            select idprod, max(startdate) startdate from mproductprice where isdeleted=0 and idprod=242 group by idprod
        ) b on (a.idprod=b.idprod and a.startdate=b.startdate)
    ) b on (a.idprod=b.idprod)
    order by 1

Saya punya table #x yang isinya seperti berikut

Saya ingin mengganti hasilnya dengan data seperti berikut:




berikut querynya

select * from (
    select idstructure, idcovjob1, (select top 1 idcovjob from #x where idstructure=a.idstructure and idcovjob<>a.idcovjob1) idcovjob2
    from (
        select distinct idstructure, (select top 1 idcovjob from #x where idstructure=a.idstructure) idcovjob1 from #x a
    ) a
   
    union
   
    select b.idstructure, idcovjob1, c.idcovjob idcovjob2 from (
        select idstructure, idcovjob1, (select top 1 idcovjob from #x where idstructure=a.idstructure and idcovjob<>a.idcovjob1) idcovjob2
        from (
            select distinct idstructure, (select top 1 idcovjob from #x where idstructure=a.idstructure) idcovjob1 from #x a
        ) a
    ) b
    inner join #x c on (b.idstructure=c.idstructure and idcovjob<>b.idcovjob1 and idcovjob<>b.idcovjob2)
) a
order by 1

alter function fincentivetrade2013_sales()
returns @sales
table
(
    iddistsales int, idproduct int, year int, month int, idcovjob varchar(20), product varchar(100), outlet  varchar(300),
    salesgross float, panel float, pbf float, oth float, diskon float, salesnet float
)
as
begin
   DECLARE @trdistributorsales TABLE
        (
            year int,
            month int,
            iddistsales bigint,
            price float,
            channelcode varchar(20),
            idoutlet int,
            idchannel int,
            idproduct int,
            prins float, PrinsHNA float,
            PrinXtra float, PrinPnel float, PrinE float
        )
       
        insert into @trdistributorsales(year, month, iddistsales, idproduct, idoutlet, prins, PrinsHNA, PrinXtra, PrinPnel, PrinE)
        select year(trxdate) year, month(trxdate) month, iddistsales, idproduct, idoutlet, prins, PrinsHNA, PrinXtra, PrinPnel, PrinE from trdistributorsales
        where year(trxdate)=2013 and month(trxdate)=1
       
        insert into @sales
        select iddistsales, idproduct, year, month, idcovjob, product, outlet, sum(salesgross) salesgross, sum(panel) panel, sum(pbf) pbf, sum(subtotal) oth, sum(diskon) diskon,
        sum(subtotal) + sum(panel) + sum(pbf) + sum(diskon) salesnet
        from (
            select year, month, idcovjob, iddistsales, a.idproduct, b.name product, a.idoutlet, c.name outlet,
            subtotal salesgross,
            case when tender=1 or panel=1 or pbf=1 then 0 else subtotal end subtotal,
            case when tender=1 then 0 when panel=1 then subtotal*0.5 else 0 end panel,
            case when tender=1 then 0 when pbf=1 then subtotal*0.25 else 0 end pbf,
            case when tender=1 then 0 when panel=1 then diskon/2 when pbf=1 then diskon/4 else diskon end diskon,
            tender   
            from (
                select year, month, c.idcovjob, a.iddistsales, a.idproduct, a.idoutlet,
                subtotal,
                case when channelcode in('08','09','8A', '8B') then 1 else 0 end tender,
                case when channelcode in('04','07','10') or a.prinpnel<>0 then 1 else 0 end panel,   
                case when d.outletid is not null then 1 else 0 end pbf,
                ( (isnull(a.Prins,0)*isnull(price,0)) + isnull(a.PrinsHNA,0) + isnull(a.PrinXtra,0) + isnull(a.PrinPnel,0) + isnull(a.PrinE,0) ) diskon
                from @trdistributorsales a
                inner join trclaimsales c on (a.iddistsales=c.iddistsales)
                left join moutletincentive d on (a.idoutlet=d.outletid)
                inner join mcoverjob e on (c.idcovjob=e.idcovjob)       
                where e.idsubcat=31 --and year(a.trxdate)=2013
                and claimtype='A' and isrelease='F' --and c.idcovjob=5435 --and a.iddistsales=10587390
            ) a
            inner join mproduct b on (a.idproduct=b.idprod)
            inner join moutlet c on (a.idoutlet=c.idoutlet)
            --where tender=0
        ) a
        where year=2013 --and month=1
        group by iddistsales, idproduct, year, month, idcovjob, product, outlet       

    return
end

alter function tes()
returns
@datas table
(
  nik varchar(20),
  officialname varchar(100)
)
as
begin
    DECLARE @data TABLE
    (
        nik varchar(20),
        officialname varchar(100)
    )

    INSERT INTO @datas (nik, officialname)
    select nik, officialname from memployee
   
    return
   
end

alter Function FN()
returns
@Names table(name varchar(200))
as
begin

   
    insert into @Names
    select officialname from memployee

    return
end

select
level_1, level_2, level_3, level_4, level_5,
idemp_1, idemp_2, idemp_3, idemp_4, idemp_5,
b.officialname emp_1, c.officialname emp_2, d.officialname emp_3, e.officialname emp_4, f.officialname emp_5,
case
    when start_1>=start_2 and start_1>=start_3 and start_1>=start_4 and start_1>=start_5 then start_1
    when start_2>=start_1 and start_2>=start_3 and start_2>=start_4 and start_2>=start_5 then start_2
    when start_3>=start_1 and start_3>=start_2 and start_3>=start_4 and start_3>=start_5 then start_3
    when start_4>=start_1 and start_4>=start_2 and start_4>=start_3 and start_4>=start_5 then start_4
    when start_5>=start_1 and start_5>=start_2 and start_5>=start_3 and start_5>=start_4 then start_5
end startdate, enddate
from (
    select
    level_1, level_2, level_3, level_4, level_5,
    idemp_1, idemp_2, idemp_3, idemp_4, idemp_5, start_1, start_2, start_3, start_4, start_5,
    case
        when end_1<=end_2 and end_1<=end_3 and end_1<=end_4 and end_1<=end_5 then end_1
        when end_2<=end_1 and end_2<=end_3 and end_2<=end_4 and end_2<=end_5 then end_2
        when end_3<=end_1 and end_3<=end_2 and end_3<=end_4 and end_3<=end_5 then end_3
        when end_4<=end_1 and end_4<=end_2 and end_4<=end_3 and end_4<=end_5 then end_4
        when end_5<=end_1 and end_5<=end_2 and end_5<=end_3 and end_5<=end_4 then end_5
    end enddate
    from (
        select
        level_1, level_2, level_3, level_4, level_5,
        b.idemp idemp_1, c.idemp idemp_2, d.idemp idemp_3, e.idemp idemp_4, f.idemp idemp_5,
        b.startdate start_1, b.enddate end_1,
        c.startdate start_2, c.enddate end_2,
        d.startdate start_3, d.enddate end_3,
        e.startdate start_4, e.enddate end_4,
        f.startdate start_5, f.enddate end_5
        from (
            select distinct a.idsubcat, a.idcovjob level_1, a.idcovjobpar level_2, b.idcovjobpar level_3, c.idcovjobpar level_4, d.idcovjobpar level_5
            from mcoverjobhistory a
            left join MCoverJobHistory b on (a.IdCovJobPar=b.IdCovJob)
            left join MCoverJobHistory c on (b.IdCovJobPar=c.IdCovJob)
            left join MCoverJobHistory d on (c.IdCovJobPar=d.IdCovJob)
            where a.isactive='T' and b.isactive='T' and c.isactive='T' and d.isactive='T'
            and a.idsubcat=31 and b.idsubcat=31 and c.idsubcat=31 and c.idsubcat=31
            and a.idjob=100
        ) a
        inner join mcovjobemployee b on (a.level_1=b.idcovjob and b.isactive='T')
        inner join mcovjobemployee c on (a.level_2=c.idcovjob and c.isactive='T')
        inner join mcovjobemployee d on (a.level_3=d.idcovjob and d.isactive='T')
        inner join mcovjobemployee e on (a.level_4=e.idcovjob and e.isactive='T')
        inner join mcovjobemployee f on (a.level_5=f.idcovjob and f.isactive='T')
    ) a
) a
inner join memployee b on (a.idemp_1=b.idemp)
inner join memployee c on (a.idemp_2=c.idemp)
inner join memployee d on (a.idemp_3=d.idemp)
inner join memployee e on (a.idemp_4=e.idemp)
inner join memployee f on (a.idemp_5=f.idemp)
order by 1, startdate