so how is it possible
i have mainly two tables
Disbursement & Bank
*
Disbursement Table*
CREATE TABLE [dbo].[Disbrsment](
[ID] [int] IDENTITY(1,1) NOT NULL,
[form_no] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[sanction_date] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[letter_no] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[sanction_amount] [money] NOT NULL,
[receipt_date] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[subsidy_date] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[subsidy_amount] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[usr_amount] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[loan_amount] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[subsidy_no] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[issue_bank] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[issue_branch] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[dis_date] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[reason] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[status] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dis_date1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Disbrsment] PRIMARY KEY CLUSTERED
(
[form_no] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
*Bank*
CREATE TABLE [dbo].[bank](
[id] [int] IDENTITY(1,1) NOT NULL,
[bank_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[phy_target] [int] NOT NULL,
[send_details_letter] [int] NULL,
[accept] [int] NULL,
[distribute] [int] NULL,
[pending] [int] NULL,
[reject] [int] NULL,
[fin_target] [bigint] NOT NULL,
CONSTRAINT [PK_bank] PRIMARY KEY CLUSTERED
(
[bank_name] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
my need to generate report
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bank Name* | Total | sent letter
| Sanction |
Reject | Pending |
* | till last month | Current month |
till last Month | current month | till last Month | current month | till
last Month | current month |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
I m able to find data according to financial year separately when status is
Sanction,Pending,Reject
1. From financial year to current date
2 From financial year to last month
but problem is how to merge all dta in a table
I m using Query to find data from financial year to current date
///current financial year
SELECT b.bank_name,avg(b.phy_target) as
phy_target,avg(b.send_details_letter) as send_details_letter,avg(b.accept)as
accept,avg(b.reject) as reject,sum(d.Counts) as
Counts,avg(d.sanction_amount) as sanction_amount,avg(d.usr_amount) as
usr_amount,avg(d.subsidy_amount) as subsidy_amount
FROM dbo.bank AS b INNER JOIN
(SELECT count(convert(varchar(10),cast(sanction_date as
datetime),101)) as Counts,issue_bank, status, SUM(CAST(sanction_amount AS
int)) AS sanction_amount, SUM(CAST(usr_amount AS int)) AS usr_amount,
SUM(CAST(subsidy_amount
AS int)) AS subsidy_amount
FROM dbo.Disbrsment
where cast(sanction_date as datetime)
between CONVERT(CHAR(4), YEAR(CONVERT(DATETIME, CONVERT(CHAR(10),
GETDATE(), 101), 101))-1) + '/04/01'
and convert(varchar(10), getdate(),101)
GROUP BY issue_bank, status,sanction_date) AS d
ON b.bank_name = d.issue_bank
WHERE (d.status = 'Sanction') and d.issue_bank=b.bank_name
group by b.bank_name
& Financial year to last month
(SELECT b.bank_name,avg(b.phy_target) as
phy_target1,avg(b.send_details_letter) as
send_details_letter1,avg(b.accept)as accept1,avg(b.reject) as
reject1,sum(d.Counts) as Counts1,avg(d.sanction_amount) as
sanction_amount1,avg(d.usr_amount) as usr_amount1,avg(d.subsidy_amount) as
subsidy_amount1
FROM dbo.bank AS b INNER JOIN
(SELECT count(convert(varchar(10),cast(sanction_date as
datetime),101)) as Counts,issue_bank, status, SUM(CAST(sanction_amount AS
int)) AS sanction_amount, SUM(CAST(usr_amount AS int)) AS usr_amount,
SUM(CAST(subsidy_amount
AS int)) AS subsidy_amount
FROM dbo.Disbrsment
where cast(sanction_date as datetime)
between CONVERT(CHAR(4), YEAR(CONVERT(DATETIME, CONVERT(CHAR(10),
GETDATE(), 101), 101))-1) + '/04/01'
and CONVERT(varchar(10), DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm,
0, DATEADD(MM, - 1, GETDATE())) + 1, 0)), 101)
GROUP BY issue_bank, status,sanction_date) AS d
ON b.bank_name = d.issue_bank
WHERE (d.status = 'Sanction') and d.issue_bank=b.bank_name
group by b.bank_name)
separately i fetch record according to status either Sanction or Pending or
Reject
but how to combine Sanction,pending,Reject in a table.
Thank you
On Fri, May 13, 2011 at 11:50 AM, Cerebrus <zorg...@sify.com> wrote:
> Create the desired output in SQL itself.
>
> The answer to your question is no, it's not possible. This because a
> Dataset counts as a Datasource and only a single datasource can be
> specified for any data control. Anyway, the DGV would have no idea how
> to display the data even if multiple sources were possible.
>
> On May 12, 1:02 pm, "Vicky ................."
> <vikas.arora....@gmail.com> wrote:
> > Hello Sir,
> >
> > I have a table name Disbursement it contains several columns like
> > form_no,dis_date,sanction_date etc..
> >
> > My Requirement is to count all disbursement date like( select
> > count(dis_date) from disbursement).
> > And Another requirement is to show last month counting for same field.
> >
> >
> ----------------------------------------|------------------------------------------------------------
> > Sent Data |
> > ----------------------------------------|
> > | Old Value | Current Value |
> > | | |
> > | | |
> > | | |
> > | | |
> > | | |
> >
> > I have these kind of 14 columns in table & need to calculate old & new
> value
> > for each column.
> >
> >
> >
> > On Thu, May 12, 2011 at 1:15 PM, Cerebrus <zorg...@sify.com> wrote:
> > > Assuming it were possible, how would you display the two datasets at
> > > the same time? What kind of UI are you planning?
> >
> > > On May 12, 10:31 am, "Vicky ................."
> > > <vikas.arora....@gmail.com> wrote:
> > > > Hello Guys,
> >
> > > > Is it possible to attach a Datagridview with two different dataset in
> c#
> > > > Windows Application ?
> >
> > > > Thanx
> >
> > > --
> > > You received this message because you are subscribed to the Google
> > > Groups "DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML
> > > Web Services,.NET Remoting" group.
> > > To post to this group, send email to
> dotnetdevelopment@googlegroups.com
> > > To unsubscribe from this group, send email to
> > > dotnetdevelopment+unsubscr...@googlegroups.com
> > > For more options, visit this group at
> > >http://groups.google.com/group/dotnetdevelopment?hl=en?hl=en
> > > or visit the group website athttp://megasolutions.net- Hide quoted text
> -
> >
> > - Show quoted text -
>
> --
> You received this message because you are subscribed to the Google
> Groups "DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML
> Web Services,.NET Remoting" group.
> To post to this group, send email to dotnetdevelopment@googlegroups.com
> To unsubscribe from this group, send email to
> dotnetdevelopment+unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/dotnetdevelopment?hl=en?hl=en
> or visit the group website at http://megasolutions.net
>
--
You received this message because you are subscribed to the Google
Groups "DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML
Web Services,.NET Remoting" group.
To post to this group, send email to dotnetdevelopment@googlegroups.com
To unsubscribe from this group, send email to
dotnetdevelopment+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/dotnetdevelopment?hl=en?hl=en
or visit the group website at http://megasolutions.net
