How to TWO PIVOT Table Data SUBTRACT

Question

0.00/5 (No votes)

See more:

Fees_Transaction Table Data Show Here

DGV

Regno	Class	Jan	Feb	Mar
A100	I	50	650	400
A200	I	100	250	0

FeeMaster Table Data Show Here

DGV				
Regno	Class	Jan	Feb	Mar
A100	I	650	750	1000
A200	I	650	750	1000

I Need OUTPUT LIKE THIS

Regno	Class	Jan	Feb	Mar
A100	I	600	100	600
A200	I	550	500	1000

Please Tell me How Can I Do this

I am using CODE

VB

Dim strSQL11
        strSQL11 = "SELECT STUFF((SELECT DISTINCT ',['+ FeesType +']' FROM FeeMaster WHERE Class='" & cboClass.Text & "' For XMl Path('')), 1, 1, '')"
        Dim DaAp11 As New SqlDataAdapter(strSQL11, con)
        Dim Dset11 As New DataTable
        DaAp11.Fill(Dset11)


        Dim strSQL10
        strSQL10 = "SELECT * FROM (SELECT stdAdmission.Regno, stdAdmission.Class,  Fees_Transaction.FeeType," & _
        " Fees_Transaction.Amount_Paid FROM Fees_Transaction INNER JOIN stdAdmission ON Fees_Transaction.Regno=stdAdmission.Regno) FeeMaster" & _
        " PIVOT (Sum(Amount_Paid) for FeeType in (" + Dset11.Rows(0).Item("Column1") + "))as PVT where Class='" & cboClass.Text & "'"
        Dim DaAp10 As New SqlDataAdapter(strSQL10, con)
        Dim Dset10 As New DataTable
        DaAp10.Fill(Dset10)
        DGV1.DataSource = Dset10


        Dim strSQL12
        strSQL12 = "SELECT * FROM (SELECT stdAdmission.Regno, stdAdmission.Class,  FeesMaster.FeeType," & _
        " FeesMaster.FeeAmount FROM FeesMaster INNER JOIN stdAdmission ON FeesMaster.Regno=stdAdmission.Regno) FeeMaster" & _
        " PIVOT (Sum(FeeAmount) for FeeType in (" + Dset11.Rows(0).Item("Column1") + "))as PVT where Class='" & cboClass.Text & "'"
        Dim DaAp12 As New SqlDataAdapter(strSQL12, con)
        Dim Dset12 As New DataTable
        DaAp12.Fill(Dset12)
        DataGridView2.DataSource = Dset12

Posted 22-Feb-13 4:32am

Navas Khanj

Updated 22-Feb-13 8:29am

Maciej Los

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Accepted Answer · 2013-02-22T07:57:00

Solution 1

Add this code after your code block

VB

Dim strSQL13
strSQL13 = "select * from (" & strSQL10  & ") as t1   Except    select * from (" & strSQL12  & ") as t2"
Dim DaAp13 As New SqlDataAdapter(strSQL13, con)
        Dim Dset13 As New DataTable
        DaAp13.Fill(Dset13)
        DataGridView3.DataSource = Dset13

Happy coding!
:)

Posted 22-Feb-13 7:57am

Aarti Meswania

Updated 23-Feb-13 6:00am

v3

Comments

Navas Khanj 23-Feb-13 1:54am

It' s Show ERROR : Incorrect syntax near 'Substract'.

Aarti Meswania 23-Feb-13 12:01pm

instead of subtract use keyword 'Except' or 'Minus' :)

Navas Khanj 23-Feb-13 14:41pm

Thanks

Aarti Meswania 24-Feb-13 8:36am

welcome! :)
Glad to help you! :)