none
How to SUM 3 Group in SQL server RRS feed

  • Pertanyaan

  • ALTER PROCEDURE [dbo].[sp_LaporanA_Penj_Pemasukan]
    	@year as integer,
    	@month as integer
    	
    --	@month int='',
    --@year int=''
    AS
    BEGIN
    	SET NOCOUNT ON;
    --IF @month = '' AND @year = ''
    --BEGIN
    
    SELECT 'Pengeluaran' AS Descrip, 
    --COUNT(1) AS Total,
    1 as total,
     SUM (amount) AS Price,
    0000 AS Fee_ke_KEI,0.0 AS Persen,'Pengeluaran' as Grp,
    'Pengeluaran Lain' as grp_penerimaan  
    FROM dbo.zCashOut 
    WHERE YEAR(cashOutDate)=@year AND MONTH(cashOutDate)=@month
    GROUP BY YEAR(zCashOut.cashOutDate), MONTH(zCashOut.cashOutDate) 
    	
    UNION	
    SELECT  'Penjualan'as Description, 
    --COUNT(1) as total,
    1 as total,
     sum(amount) as jumlah,
    0000 AS Fee_ke_KEI,0.0 AS Persen,'Penjualan' as Grp,'Penerimaan Penjualan' as grp_penerimaan
    FROM         dbo.invoice
    WHERE   (description NOT LIKE '%Uang Kursus%') AND (description NOT LIKE '%Uang Pendaftaran%') 
    and (description NOT LIKE '%januari%')and (description NOT LIKE '%februari%')and (description NOT LIKE '%maret%')
    and (description NOT LIKE '%april%') and (description NOT LIKE '%mei%')and (description NOT LIKE '%juni%')
    and (description NOT LIKE '%juli%')and (description NOT LIKE '%agustus%')and (description NOT LIKE '%september%')
    and (description NOT LIKE '%oktober%')and (description NOT LIKE '%november%')and (description NOT LIKE '%desember%')
    and YEAR(paidDate)=@year AND MONTH(paidDate)=@month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate) 
    
    UNION
    --Daftar Matematika
    SELECT 'Siswa yang mendaftar Matematika' AS Descrip, COUNT(1) AS Total, 250000 AS Price,
    250000 AS Fee_ke_KEI,0.5 AS Persen,'Matematika' as Grp,'Penerimaan' as grp_penerimaan
    FROM invoice INNER JOIN
    register ON invoice.studentID = register.studentID INNER JOIN
    zMajor ON register.majorID = zMajor.majorID
    WHERE (zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') 
    AND (zMajor.majorName NOT LIKE '%Bahasa Inggris EFL%') 
    AND (invoice.description LIKE '%Uang Pendaftaran : Rp 250,000%') 
    AND (invoice.description NOT LIKE '%penjualan%')
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    UNION
    --Daftar Inggris EE
    SELECT 'Siswa yang mendaftar Inggris EE' AS Descrip, COUNT(1) AS Total, 250000 AS Price,
    250000 AS Fee_ke_KEI,0.5 AS Persen,'Bahasa Inggris EE' as Grp,'Penerimaan' as grp_penerimaan 
    FROM invoice INNER JOIN
    register ON invoice.studentID = register.studentID INNER JOIN
    zMajor ON register.majorID = zMajor.majorID
    WHERE (zMajor.majorName NOT LIKE '%Matematika%') 
    AND (zMajor.majorName NOT LIKE '%Bahasa Inggris EFL%') 
    AND (invoice.description LIKE '%Uang Pendaftaran : Rp 250,000%') 
    AND (invoice.description NOT LIKE '%penjualan%')
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    
    UNION
    --Daftar Inggris EFL
    SELECT 'Siswa yang mendaftar Inggris EFL' AS Descrip, COUNT(1) AS Total, 250000 AS Price,
     250000 AS Fee_ke_KEI,0.5 AS Persen ,'Bahasa Inggris EFL' as Grp,'Penerimaan' as grp_penerimaan
    FROM invoice INNER JOIN
    register ON invoice.studentID = register.studentID INNER JOIN
    zMajor ON register.majorID = zMajor.majorID
    WHERE (zMajor.majorName NOT LIKE '%Matematika%') 
    AND (zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') 
    AND (invoice.description LIKE '%Uang Pendaftaran : Rp 250,000%') 
    AND (invoice.description NOT LIKE '%penjualan%')
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    UNION
    --TK & SD bayr FULL MAT
    SELECT 'Siswa TK & SD bayar full Matematika' AS Descrip, 
    COUNT(1) AS Total, 320000 AS Price,
    320000 AS Fee_ke_KEI,0.4 AS Persen,'Matematika' as Grp,'Penerimaan' as grp_penerimaan  
    FROM invoice INNER JOIN
    register ON invoice.studentID = register.studentID INNER JOIN
    zMajor ON register.majorID = zMajor.majorID
    WHERE (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') AND 
    (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EFL%') AND 
    (dbo.invoice.description NOT LIKE '%penjualan%') AND 
    ((dbo.invoice.description LIKE '%320,000%') OR (dbo.invoice.amount=320000)) 
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    UNION
    --TK & SD bayr FULL Bahasa Inggris EE
    SELECT 'Siswa TK & SD bayar full Bahasa Inggris EE' AS Descrip, 
    COUNT(1) AS Total, 320000 AS Price,
    320000 AS Fee_ke_KEI,0.4 AS Persen,'Bahasa Inggris EE' as Grp,'Penerimaan' as grp_penerimaan 
    FROM invoice INNER JOIN
    register ON invoice.studentID = register.studentID INNER JOIN
    zMajor ON register.majorID = zMajor.majorID
    WHERE (dbo.zMajor.majorName NOT LIKE '%Matematika%') AND 
    (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EFL%') AND 
    (dbo.invoice.description NOT LIKE '%penjualan%') AND 
    ((dbo.invoice.description LIKE '%320,000%') OR (dbo.invoice.amount=320000)) 
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    UNION
    --TK & SD bayr FULL Bahasa Inggris EFL
    SELECT 'Siswa TK & SD bayar full Bahasa Inggris EFL' AS Descrip, 
    COUNT(1) AS Total, 320000 AS Price,
    320000 AS Fee_ke_KEI,0.4 AS Persen,'Bahasa Inggris EFL' as Grp,'Penerimaan' as grp_penerimaan  
    FROM invoice INNER JOIN
    register ON invoice.studentID = register.studentID INNER JOIN
    zMajor ON register.majorID = zMajor.majorID
    WHERE (dbo.zMajor.majorName NOT LIKE '%Matematika%') AND 
    (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') AND 
    (dbo.invoice.description NOT LIKE '%penjualan%') AND 
    ((dbo.invoice.description LIKE '%320,000%') OR (dbo.invoice.amount=320000)) 
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    UNION
    --TK & SD bayar setengah Mat
    SELECT 'Siswa TK & SD bayar setengah Matematika' AS Descrip, 
    COUNT(1) AS Total, 160000 AS Price,
    160000 AS Fee_ke_KEI,0.4 AS Persen,'Matematika' as Grp,'Penerimaan' as grp_penerimaan  
    FROM invoice INNER JOIN
    register ON invoice.studentID = register.studentID INNER JOIN
    zMajor ON register.majorID = zMajor.majorID
    WHERE (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EFL%') AND 
    (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') AND 
    (dbo.invoice.description NOT LIKE '%penjualan%') AND 
    (dbo.invoice.description LIKE '%160,000%')
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    UNION
    --TK & SD bayar setengah Bahasa Inggris EFL
    SELECT 'Siswa TK & SD bayar setengah Bahasa Inggris EFL' AS Descrip, 
    COUNT(1) AS Total, 160000 AS Price,
    160000 AS Fee_ke_KEI,0.4 AS Persen,'Bahasa Inggris EFL' as Grp,'Penerimaan' as grp_penerimaan 
    FROM invoice INNER JOIN
    register ON invoice.studentID = register.studentID INNER JOIN
    zMajor ON register.majorID = zMajor.majorID
    WHERE(dbo.zMajor.majorName NOT LIKE '%Matematika%') AND
    (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') AND
    (dbo.invoice.description NOT LIKE '%penjualan%') AND 
    (dbo.invoice.description LIKE '%160,000%') 
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    
    UNION
    --TK & SD bayar setengah Bahasa Inggris EE
    SELECT 'Siswa TK & SD bayar setengah Bahasa Inggris EE' AS Descrip, 
    COUNT(1) AS Total, 160000 AS Price,
    160000 AS Fee_ke_KEI,0.4 AS Persen,'Bahasa Inggris EE' as Grp,'Penerimaan' as grp_penerimaan 
    FROM invoice INNER JOIN
    register ON invoice.studentID = register.studentID INNER JOIN
    zMajor ON register.majorID = zMajor.majorID
    WHERE(dbo.zMajor.majorName NOT LIKE '%Matematika%') AND
     (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EFL%') AND 
     (dbo.invoice.description NOT LIKE '%penjualan%') AND 
     (dbo.invoice.description LIKE '%160,000%') 
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    UNION
    --Siswa SMP ke atas bayar full Matematika
    SELECT 'Siswa SMP ke atas bayar full Matematika' AS Descrip, 
    COUNT(1) AS Total, 370000 AS Price,
    320000 AS Fee_ke_KEI,0.4 AS Persen,'Matematika' as Grp,'Penerimaan' as grp_penerimaan 
    FROM invoice INNER JOIN
    register ON invoice.studentID = register.studentID INNER JOIN
    zMajor ON register.majorID = zMajor.majorID
    WHERE(dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') AND
     (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EFL%') AND 
     (dbo.invoice.description NOT LIKE '%penjualan%') AND 
     ((dbo.invoice.description LIKE '%370,000%') OR (dbo.invoice.amount=370000)) 
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    UNION
    --Siswa SMP ke atas bayar full Bahasa Inggris EFL
    SELECT 'Siswa SMP ke atas bayar full Bahasa Inggris EFL' AS Descrip, 
    COUNT(1) AS Total, 370000 AS Price,
    320000 AS Fee_ke_KEI,0.4 AS Persen,'Bahasa Inggris EFL' as Grp,'Penerimaan' as grp_penerimaan  
    FROM invoice INNER JOIN
    register ON invoice.studentID = register.studentID INNER JOIN
    zMajor ON register.majorID = zMajor.majorID
    WHERE(dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') AND
     (dbo.zMajor.majorName NOT LIKE '%Matematika%') AND 
     (dbo.invoice.description NOT LIKE '%penjualan%') AND 
     ((dbo.invoice.description LIKE '%370,000%') OR (dbo.invoice.amount=370000))
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    UNION
    --Siswa SMP ke atas bayar full Bahasa Inggris EE
    SELECT 'Siswa SMP ke atas bayar full Bahasa Inggris EE' AS Descrip, 
    COUNT(1) AS Total, 370000 AS Price,
    320000 AS Fee_ke_KEI,0.4 AS Persen,'Bahasa Inggris EE' as Grp,'Penerimaan' as grp_penerimaan  
    FROM invoice INNER JOIN
    register ON invoice.studentID = register.studentID INNER JOIN
    zMajor ON register.majorID = zMajor.majorID
    WHERE(dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') AND
     (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EFL%') AND 
     (dbo.invoice.description NOT LIKE '%penjualan%') AND 
     ((dbo.invoice.description LIKE '%370,000%') OR (dbo.invoice.amount=370000))
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    UNION
    --Siswa SMP ke atas bayar setengah Mat
    SELECT 'Siswa SMP ke atas bayar setengah Matematika' AS Descrip, 
    COUNT(1) AS Total, 185000 AS Price,
    160000 AS Fee_ke_KEI,0.4 AS Persen,'Matematika' as Grp,'Penerimaan' as grp_penerimaan  
    FROM invoice INNER JOIN
    register ON invoice.studentID = register.studentID INNER JOIN
    zMajor ON register.majorID = zMajor.majorID
    WHERE(dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') AND
     (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EFL%') AND 
     (dbo.invoice.description NOT LIKE '%penjualan%') AND 
     (dbo.invoice.description LIKE '%185,000%') 
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    UNION
    --Siswa SMP ke atas bayar setengah Bahasa Inggris EFL
    SELECT 'Siswa SMP ke atas bayar setengah Bahasa Inggris EFL' AS Descrip, 
    COUNT(1) AS Total, 185000 AS Price,
    160000 AS Fee_ke_KEI,0.4 AS Persen,'Bahasa Inggris EFL' as Grp,'Penerimaan' as grp_penerimaan  
    FROM invoice INNER JOIN
    register ON invoice.studentID = register.studentID INNER JOIN
    zMajor ON register.majorID = zMajor.majorID
    WHERE(dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') AND
     (dbo.zMajor.majorName NOT LIKE '%Matematika%') AND 
     (dbo.invoice.description NOT LIKE '%penjualan%') AND 
     (dbo.invoice.description LIKE '%185,000%') 
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    
    UNION
    --Siswa SMP ke atas bayar setengah Bahasa Inggris EE
    SELECT 'Siswa SMP ke atas bayar setengah Bahasa Inggris EE' AS Descrip, 
    COUNT(1) AS Total, 185000 AS Price,
    160000 AS Fee_ke_KEI,0.4 AS Persen,'Bahasa Inggris EE' as Grp,'Penerimaan' as grp_penerimaan   
    FROM invoice INNER JOIN
    register ON invoice.studentID = register.studentID INNER JOIN
    zMajor ON register.majorID = zMajor.majorID
    WHERE(dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') AND
     (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EFL%') AND 
     (dbo.invoice.description NOT LIKE '%penjualan%') AND 
     (dbo.invoice.description LIKE '%185,000%') 
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    UNION
    --Siswa Beasiswa TK & SD Mat
    SELECT 'Siswa Beasiswa TK dan SD Matematika' AS Descrip, 
    COUNT(1) AS Total, 160000 AS Price,
    160000 AS Fee_ke_KEI,0.4 AS Persen,'Matematika' as Grp,'Penerimaan' as grp_penerimaan   
    FROM dbo.invoice INNER JOIN
    dbo.register ON dbo.invoice.studentID = dbo.register.studentID INNER JOIN
    dbo.zMajor ON dbo.register.majorID = dbo.zMajor.majorID INNER JOIN
    dbo.zRegisterType ON dbo.register.registerTypeID = dbo.zRegisterType.registerTypeID
    WHERE(dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') AND
    (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EFL%') AND
    (dbo.invoice.description NOT LIKE '%penjualan%') AND 
    (dbo.invoice.description LIKE '%160,000%') AND
    (dbo.zRegisterType.registerTypeID LIKE '%5%') 
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    
    UNION
    --siswa beasiswa MAT
    SELECT 'Siswa Beasiswa SMP ke Atas Matematika' AS Descrip, 
    COUNT(1) AS Total, 185000 AS Price,
    160000 AS Fee_ke_KEI,0.4 AS Persen,'Matematika' as Grp,'Penerimaan' as grp_penerimaan  
    FROM invoice INNER JOIN
    register ON invoice.studentID = register.studentID INNER JOIN
    zMajor ON register.majorID = zMajor.majorID INNER JOIN
    dbo.zRegisterType ON dbo.register.registerTypeID = dbo.zRegisterType.registerTypeID
    WHERE(dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') AND
     (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EFL%') AND 
     (dbo.invoice.description NOT LIKE '%penjualan%') AND 
     (dbo.invoice.description LIKE '%185,000%') AND
    (dbo.zRegisterType.registerTypeID LIKE '%5%')
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    UNION
    --Siswa Beasiswa TK & SD EE
    SELECT 'Siswa Beasiswa TK dan SD EE' AS Descrip, 
    COUNT(1) AS Total, 160000 AS Price,
    160000 AS Fee_ke_KEI,0.4 AS Persen,'Bahasa Inggris EE' as Grp,'Penerimaan' as grp_penerimaan   
    FROM dbo.invoice INNER JOIN
    dbo.register ON dbo.invoice.studentID = dbo.register.studentID INNER JOIN
    dbo.zMajor ON dbo.register.majorID = dbo.zMajor.majorID INNER JOIN
    dbo.zRegisterType ON dbo.register.registerTypeID = dbo.zRegisterType.registerTypeID
    WHERE(dbo.zMajor.majorName NOT LIKE '%Matematika%') AND
    (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EFL%') AND
    (dbo.invoice.description NOT LIKE '%penjualan%') AND 
    (dbo.invoice.description LIKE '%160,000%') AND
    (dbo.zRegisterType.registerTypeID LIKE '%5%') 
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    
    UNION
    --siswa beasiswa EE
    SELECT 'Siswa Beasiswa SMP ke Atas EE' AS Descrip, 
    COUNT(1) AS Total, 185000 AS Price,
    160000 AS Fee_ke_KEI,0.4 AS Persen,'Bahasa Inggris EE' as Grp,'Penerimaan' as grp_penerimaan   
    FROM invoice INNER JOIN
    register ON invoice.studentID = register.studentID INNER JOIN
    zMajor ON register.majorID = zMajor.majorID INNER JOIN
    dbo.zRegisterType ON dbo.register.registerTypeID = dbo.zRegisterType.registerTypeID
    WHERE(dbo.zMajor.majorName NOT LIKE '%Matematika%') AND
     (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EFL%') AND 
     (dbo.invoice.description NOT LIKE '%penjualan%') AND 
     (dbo.invoice.description LIKE '%185,000%') AND
    (dbo.zRegisterType.registerTypeID LIKE '%5%')
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    UNION
    --Siswa Beasiswa TK & SD EFL
    SELECT 'Siswa Beasiswa TK dan SD EFL' AS Descrip, 
    COUNT(1) AS Total, 160000 AS Price,
    160000 AS Fee_ke_KEI,0.4 AS Persen,'Bahasa Inggris EFL' as Grp,'Penerimaan' as grp_penerimaan   
    FROM dbo.invoice INNER JOIN
    dbo.register ON dbo.invoice.studentID = dbo.register.studentID INNER JOIN
    dbo.zMajor ON dbo.register.majorID = dbo.zMajor.majorID INNER JOIN
    dbo.zRegisterType ON dbo.register.registerTypeID = dbo.zRegisterType.registerTypeID
    WHERE(dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') AND
    (dbo.zMajor.majorName NOT LIKE '%Matematika%') AND
    (dbo.invoice.description NOT LIKE '%penjualan%') AND 
    (dbo.invoice.description LIKE '%160,000%') AND
    (dbo.zRegisterType.registerTypeID LIKE '%5%') 
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    
    UNION
    --siswa beasiswa EFL
    SELECT 'Siswa Beasiswa SMP ke Atas EFL' AS Descrip, 
    COUNT(1) AS Total, 185000 AS Price,
    160000 AS Fee_ke_KEI,0.4 AS Persen,'Bahasa Inggris EFL' as Grp,'Penerimaan' as grp_penerimaan  
    FROM invoice INNER JOIN
    register ON invoice.studentID = register.studentID INNER JOIN
    zMajor ON register.majorID = zMajor.majorID INNER JOIN
    dbo.zRegisterType ON dbo.register.registerTypeID = dbo.zRegisterType.registerTypeID
    WHERE(dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') AND
     (dbo.zMajor.majorName NOT LIKE '%Matematika%') AND 
     (dbo.invoice.description NOT LIKE '%penjualan%') AND 
     (dbo.invoice.description LIKE '%185,000%') AND
    (dbo.zRegisterType.registerTypeID LIKE '%5%')
    AND YEAR(invoice.paidDate) = @year
    AND MONTH(invoice.paidDate) = @month
    GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    --UNION
    ----Siswa SMP bayar 12 bulan
    --SELECT 'Siswa SMP bayar 12 bulan Matematika' AS Descrip, 
    --COUNT(1) AS Total, 4190000 AS Price 
    --FROM dbo.invoice INNER JOIN
    --dbo.register ON dbo.invoice.studentID = dbo.register.studentID INNER JOIN
    --dbo.zMajor ON dbo.register.majorID = dbo.zMajor.majorID
    --WHERE (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') AND 
    --(dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EFL%') AND 
    --(dbo.invoice.description NOT LIKE '%penjualan%') AND 
    --(dbo.invoice.amount LIKE '%4190000%')
    --AND YEAR(invoice.paidDate) = @year
    --AND MONTH(invoice.paidDate) = @month
    --GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    --UNION
    ----Siswa SMP bayar 6 bulan
    --SELECT 'Siswa SMP bayar 6 bulan Matematika' AS Descrip, 
    --COUNT(1) AS Total, 2120000 AS Price 
    --FROM dbo.invoice INNER JOIN
    --dbo.register ON dbo.invoice.studentID = dbo.register.studentID INNER JOIN
    --dbo.zMajor ON dbo.register.majorID = dbo.zMajor.majorID
    --WHERE (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') AND 
    --(dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EFL%') AND 
    --(dbo.invoice.description NOT LIKE '%penjualan%') AND 
    --(dbo.invoice.amount LIKE '%2120000%')
    --AND YEAR(invoice.paidDate) = @year
    --AND MONTH(invoice.paidDate) = @month
    --GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    --UNION
    ----Siswa TK & SD bayar 6 bulan
    --SELECT 'Siswa TK & SD bayar 6 bulan Matematika' AS Descrip, 
    --COUNT(1) AS Total, 1820000 AS Price 
    --FROM dbo.invoice INNER JOIN
    --dbo.register ON dbo.invoice.studentID = dbo.register.studentID INNER JOIN
    --dbo.zMajor ON dbo.register.majorID = dbo.zMajor.majorID
    --WHERE (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') AND 
    --(dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EFL%') AND 
    --(dbo.invoice.description NOT LIKE '%penjualan%') AND 
    --(dbo.invoice.amount LIKE '%1820000%')
    --AND YEAR(invoice.paidDate) = @year
    --AND MONTH(invoice.paidDate) = @month
    --GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    --UNION
    ----Siswa TK & SD bayar 12 bulan
    --SELECT 'Siswa TK & SD bayar 12 bulan Matematika' AS Descrip, 
    --COUNT(1) AS Total, 3590000 AS Price 
    --FROM dbo.invoice INNER JOIN
    --dbo.register ON dbo.invoice.studentID = dbo.register.studentID INNER JOIN
    --dbo.zMajor ON dbo.register.majorID = dbo.zMajor.majorID
    --WHERE (dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EE%') AND 
    --(dbo.zMajor.majorName NOT LIKE '%Bahasa Inggris EFL%') AND 
    --(dbo.invoice.description NOT LIKE '%penjualan%') AND 
    --(dbo.invoice.amount LIKE '%3590000%')
    --AND YEAR(invoice.paidDate) = @year
    --AND MONTH(invoice.paidDate) = @month
    --GROUP BY YEAR(invoice.paidDate), MONTH(invoice.paidDate), zMajor.majorName
    
    
    END
    

    how to GET SUM nett profit ??
    nett profit = PENERIMAAN - total pengeluaran - GROSS PROFIT`


    adulyajed

    Senin, 01 April 2013 07.59

Jawaban

  • Gunakan query berikut

    SELECT Penerimaan - TotalPengeluaran - GrossProfit AS [netProfit] FROM TabelOrView
    


    Ahmad Masykur http://www.masykur.web.id/

    • Ditandai sebagai Jawaban oleh Adulyajed Kamis, 04 April 2013 10.01
    Kamis, 04 April 2013 07.35

Semua Balasan

  • Jumlahkan seperti biasa. Hanya saja harus kembalikan ke prinsip dasar akuntansi, semua pengeluaran harus minus dan semua pemasukan harus plus. Jadi ketika ditotal tidak perlu menentukan formula x = a - b - c

    Ahmad Masykur http://www.masykur.web.id/

    Kamis, 04 April 2013 01.50
  • tapi masalahnya total dari penerimaan penjualannya itu sudah sya sum dari

    sum subtotal group,jadi bagaimana caranya menghitung dari

    isi sum group total penerimaan & sum group total pengeluaran & jg sum group Gross Profit,

    untuk hasil total penerimaan penjualan , total pengeluaran & gross profit

    sya menggunakan subreport jadi sya bingung untuk menghitung nett profitnya


    adulyajed

    Kamis, 04 April 2013 05.42
  • Bisa minta tolong sederhanakan pertanyaannya?

    Berikan ilustrasi atau contoh sederhana saja. Saya kesulitan membaca kode Anda.


    Ahmad Masykur http://www.masykur.web.id/

    Kamis, 04 April 2013 07.23
  • how to GET SUM nett profit ??
    nett profit = PENERIMAAN - total pengeluaran - GROSS PROFIT`

    adulyajed

    Kamis, 04 April 2013 07.28
  • Gunakan query berikut

    SELECT Penerimaan - TotalPengeluaran - GrossProfit AS [netProfit] FROM TabelOrView
    


    Ahmad Masykur http://www.masykur.web.id/

    • Ditandai sebagai Jawaban oleh Adulyajed Kamis, 04 April 2013 10.01
    Kamis, 04 April 2013 07.35