How to get result through SQL querry

Question

0.00/5 (No votes)

See more:

I have one sql table (Payable) it has columns as

cID|stdId|Cet|Apr |May |Jun |Jul |Aug |Sep |Oct |Nov |Dec |Jan |Feb |Mar
1  | std1| 1 |100 | 100| 100|200 |200 |300 |400 |500 |500 |600 |700 |500
1  | std1| 2 |100 | 100| 100|200 |200 |300 |400 |500 |500 |600 |700 |500

another table (Receipt)

cID|stdId|Cet|Apr |May |Jun |Jul |Aug |Sep |Oct |Nov |Dec |Jan |Feb |Mar
1  | std1| 1 |100 | 100| 100| 0  |0   |0   |0   |0   |0   |0   |0   |0
1  | std1| 2 |50  | 50 | 50 | 0  |0   |0   |0   |0   |0   |0   |0   |0

my questions is how to write sql query to show report as

Quarter-I(Apr to Jun)

Particulars             |  Amount  | 
       Cat1             |  300     |
       Cat2             |  300     |
Total Due for Quarter-I |  600     |
Total Received Amount   |  450     |  this value comes from Receipt table
Fine                    |   0      |
Total Payable Amount    |  150     |  this amount should show in Quarter-II

Quarter-II (Jul-Sep)

Particulars             |  Amount  | 
       Cat1             |  700     |
       Cat2             |  700     |
Total Due for Quarter-II| 1400     |
Total Received Amount   |    0     |  
Last Quarter Pending    |  150     |
Fine                    |    0     |
Total Payable Amount    | 1550     |

Similarly for Quarter-III(Oct-Dec) and Quarter-IV (Jan - Mar)

someone please help!........

[edit]code tags changed to pre tags to preserve formatting.[/edit]

What I have tried:

......................................................

Posted 3-Nov-16 0:57am

nityanand NCR

Updated 4-Nov-16 22:59pm

v5

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)

Member 9831003 · Accepted Answer · 2016-11-03T03:28:00

Solution 1

May be you can use roll up and cube (extension of group by)

Posted 3-Nov-16 3:28am

Member 9831003

Comments

nityanand NCR 4-Nov-16 3:33am

Sir I need help

Member 9831003 8-Nov-16 4:05am

Thanks, if possible can you please make some insert statement for these tables.

nityanand NCR 11-Nov-16 4:18am

186	21	2013/48	1	2100	0	0	0	0	0	0	0	0	0	0	0
187	21	2013/48	2	6000	0	0	0	0	0	0	0	0	0	0	0

Member 9831003 4-Nov-16 9:25am

Send your table schema with sample data

nityanand NCR 4-Nov-16 23:03pm

CREATE TABLE [dbo].[Receipt](
[Auto_Sr_No] [int] IDENTITY(1,1) NOT NULL,
[ReceiptNo] [varchar](20) NULL,
[cls_SecId] [int] NULL,
[S_Adm_No] [varchar](50) NULL,
[FHeadId] [int] NULL,
[Apr] [numeric](18, 0) NULL,
[May] [numeric](18, 0) NULL,
[Jun] [numeric](18, 0) NULL,
[Jul] [numeric](18, 0) NULL,
[Aug] [numeric](18, 0) NULL,
[Sep] [numeric](18, 0) NULL,
[Oct] [numeric](18, 0) NULL,
[Nov] [numeric](18, 0) NULL,
[Dec] [numeric](18, 0) NULL,
[Jan] [numeric](18, 0) NULL,
[Feb] [numeric](18, 0) NULL,
[Mar] [numeric](18, 0) NULL,
[Mode_Payment] [varchar](50) NULL,
[bank_Name] [varchar](50) NULL,
[ChqNo] [varchar](50) NULL,
[ChqDated] [datetime] NULL,
[For_the_Month] [varchar](500) NULL,
[Paid_date] [datetime] NULL,
[TotalAmount] [numeric](18, 0) NULL,
[Fine] [numeric](18, 0) NULL,
[Concession] [numeric](18, 0) NULL,
[payableAmount] [numeric](18, 0) NULL,
[usr] [varchar](500) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[Payable](
[F_Id] [int] IDENTITY(1,1) NOT NULL,
[cls_SecId] [int] NULL,
[S_Adm_No] [varchar](50) NULL,
[FHeadId] [int] NULL,
[Apr] [numeric](18, 0) NULL,
[May] [numeric](18, 0) NULL,
[Jun] [numeric](18, 0) NULL,
[Jul] [numeric](18, 0) NULL,
[Aug] [numeric](18, 0) NULL,
[Sep] [numeric](18, 0) NULL,
[Oct] [numeric](18, 0) NULL,
[Nov] [numeric](18, 0) NULL,
[Dec] [numeric](18, 0) NULL,
[Jan] [numeric](18, 0) NULL,
[Feb] [numeric](18, 0) NULL,
[Mar] [numeric](18, 0) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO