|
It might be helpfull if you divide this script into several simpeler stored procedures.
Complex tasks are always easier when broken into smaller pieces.
|
|
|
|
|
Hi guys.,
I had a kind of requirement where i will select my department in ddl and click on save button.,then the code should be saved in the format of selected department-S.no/Fin.Year.
For Eg:
If i select my Dept name as IT and my current financial year is
2012-04-01 2013-03-31
then output should be in the format of
IT-00001 / 12-13
(Dept-S.no/Fin.Year)
if a save one more record then,
output should be
IT-00002 / 12-13
If my Fin.Year changed to
2013-04-01 2014-03-31
then again the o/p should be
IT-00001 / 13-14
|
|
|
|
|
..and what's your question?
|
|
|
|
|
As Eddy said, you really should ask a question you want answered rather than state a problem.
Try looking into string concatenation, you are going to have to CAST or CONVERT the datetime and int into varchar values and concatenate them!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi guys.,
I had an grid view where i had placed an link button to print an report.In this button click event i need to call the SSRS report and need to get the output as pdf file.
I had used this below code,the code is running fine,but i'm unable to see the prompt to open/save pdf file.Plz reply ASAP.
protected void btnAuthenticateAndPrint_Click(object sender, EventArgs args)
{
try
{
LinkButton lb = (LinkButton)sender;
GridViewRow row = (GridViewRow)lb.NamingContainer;
Label lbOrderID = row.FindControl("lbOrderID") as Label;
int OrderId = Convert.ToInt32(lbOrderID.Text);
da = new SqlDataAdapter("Get_PODetails", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.AddWithValue("@MPDI_ID", OrderId);
ds = new DataSet();
da.Fill(ds, "PO");
if (ds.Tables["PO"].Rows.Count > 0)
{
lblPOId.Text=ds.Tables["PO"].Rows[0]["MPDI_ID"].ToString();
lblVendid.Text = ds.Tables["PO"].Rows[0]["MVDI_ID"].ToString();
lblBranch.Text = ds.Tables["PO"].Rows[0]["MBRI_ID"].ToString();
lblDate.Text = Convert.ToDateTime(ds.Tables["PO"].Rows[0]["MPDI_Date"]).ToString("dd-MM-yyyy");
}
rs = new RSWebService.ReportingService2005();
rsExec = new REWebService.ReportExecutionService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs.Url = "http://localhost/ReportServer/ReportService2005.asmx";
rsExec.Url = "http://localhost/ReportServer/ReportExecution2005.asmx";
byte[] Sendresults = null;
byte[] bytes = null;
string historyID = null;
string deviceInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";
string format = "PDF";
string encoding = null;
string mimeType = null;
string extension = null;
REWebService.Warning[] warnings = null;
string[] streamIDs = null;
string _reportName = @"/FIMO GOF Assets Reports/PURCHASE ORDER";
REWebService.ExecutionInfo ei = rsExec.LoadReport(_reportName, historyID);
REWebService.ParameterValue[] parameters = new REWebService.ParameterValue[4];
parameters[0] = new REWebService.ParameterValue();
parameters[0].Name = "MVDI_ID";
parameters[0].Value = lblVendid.Text;
parameters[1] = new REWebService.ParameterValue();
parameters[1].Name = "MBRI_ID";
parameters[1].Value = lblBranch.Text;
parameters[2] = new REWebService.ParameterValue();
parameters[2].Name = "MPDI_Date";
parameters[2].Value = lblDate.Text;
parameters[3] = new REWebService.ParameterValue();
parameters[3].Name = "ReportParameter1";
parameters[3].Value = lblPOId.Text;
rsExec.SetExecutionParameters(parameters, "en-us");
Sendresults = rsExec.Render(format, deviceInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);
MemoryStream ms = new MemoryStream(Sendresults);
if (format == "PDF")
{
Response.ContentType = "application/pdf";
Response.AddHeader("Content-disposition", "inline;filename=output.pdf");
Response.AddHeader("Content-Length", Sendresults.Length.ToString());
}
Response.OutputStream.Write(Sendresults, 0, Sendresults.Length);
Response.OutputStream.Flush();
Response.OutputStream.Close();
}
catch(Exception Ex)
{
throw Ex;
}
}
Thanks & Regards,
Vinay
|
|
|
|
|
Don't crosspost, choose one forum and stick to it.
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
Hi everyone. I have different databases that are related. During backup process, I will need to append other databases so that I will have a single file containing all the databases.
My problem now is to programmatically identify the individual databases in the single backup file. How do I accomplish this? Please help.
|
|
|
|
|
Coming from MS SQL Server I do not mix databases in backups, however I do use zip technology to gang together backup files. I have used WinRAR and 7Zip
|
|
|
|
|
Please any one can give me step by step use of struts2 simple demo with form-bean in struts2.xml file? and also demo of user bean?
|
|
|
|
|
What does this have to do with databases!
According to this [^]it is a development framework.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You are expecting too much of this site. Do some research with Google and look at the Java Tutorials[^] for the information you seek.
Also, in future please use the correct forum.
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
Hi
I want to select result for yearly report, i want total number of request raised per month and how many request solved per month,
it should show '0' if the month not having request and solved request.
Like
Month Total Requests Solved Request
1 0 0
2 34 30
3 38 38
4 5 5
5 78 67
6 10 10
7 10 9
8 12 11
9 90 89
10 24 23
11 12 10
12 11 09
please help me
|
|
|
|
|
VishwaKL wrote: please help me
With what? As it is now, there's not even a starting point.
Here's one[^].
|
|
|
|
|
Thanks for the reply, i am trying to achieve that, but i not able to do that, i tried all possibilities by searching google, so i want query to get the totla number of request raised, solved request month wise for a yearly report
|
|
|
|
|
Google is not an online repository where you can "get queries". I suggest trying (!) to write a select-query, group it by month, and use a subquery to get the correct data.
|
|
|
|
|
i agree that google is not a repository, i said i am trying with google help only, i am able to get either solved request or total request, but i want them to be combined as i showed in my question,
|
|
|
|
|
VishwaKL wrote: i said i am trying with google help only
The documentation on SQL is a bit more helpful. Google is mostly used for finding examples, not solutions.
VishwaKL wrote: i am able to get either solved request or total request, but i want them to be combined as i showed in my question,
Aight, show us what you got so far
|
|
|
|
|
thank you for your support
|
|
|
|
|
Since you did not post the structure of your table, I will have to give a very general asnwer.
Something like:
select month(ticket_created_date),count(ticket_created_date),count(ticket_closed_date)
from myTickets
group by month(ticket_created_date)
If this helps, Remeber to vote.
|
|
|
|
|
hi i am not having closed date field,
i have only requesteddate, and status of the ticket like open/close
|
|
|
|
|
The Group By hint should be more than enough for you to get started.
|
|
|
|
|
VishwaKL wrote: i have only requesteddate, and status of the ticket like open/close
This information was VITAL to the question. Do us all a favour,m in future include all the relevant information for a question - if you're asking for help writing a SQL Query, if you dont tell us the structure of your table, its IMPOSSIBLE to answer.
So given that info. The first place to start is to generate numbers 1-12 so you have something to join on (even for those months with zero requests).
A recursive common table expression can do this easily:
with months (num) as
(
SELECT 1
UNION ALL
SELECT num+1
from months
where num<12
)
select * from months
You'll also need a subquery to pull together the ticket data:
select
month(requesteddate) as month,
count(*) as count,
sum(case when status='Closed' THEN 1 ELSE 0 END) as resolved
from tickets
group by month(requesteddate)
You can then use this to LEFT JOIN your month table, and produce the final output you want:
with months (num) as
(
SELECT 1
UNION ALL
SELECT num+1
from months
where num<12
)
select m.num as Month,
isnull(t.count,0) as Total,
isnull(t.resolved,0) as Totalresolved
from months m
left join (
select
month(requesteddate) as month,
count(*) as count,
sum(case when status='Closed' THEN 1 ELSE 0 END) as resolved
from tickets
group by month(requesteddate)
) t
on m.num = t.month
Here's a live example: http://www.sqlfiddle.com/#!6/a8a7b/11[^]
Input data:
create table tickets
(
requesteddate datetime,
status varchar(10)
)
insert into tickets
values
('2012-01-10','closed'),
('2012-01-20','closed'),
('2012-01-30','closed'),
('2012-02-10','closed'),
('2012-02-20','closed'),
('2012-03-10','closed'),
('2012-03-20','closed'),
('2012-03-30','open')
Output:
MONTH TOTAL TOTALRESOLVED
1 3 3
2 2 2
3 3 2
4 0 0
5 0 0
6 0 0
7 0 0
8 0 0
9 0 0
10 0 0
11 0 0
12 0 0
|
|
|
|
|
thank you so much J4amieC,
it worked for me,
|
|
|
|
|
Select [MONTH],Count([Total Requests]),COUNT([Solved Request])
From Temp
Group By [MONTH]
|
|
|
|
|
select StockNumber,InventoryName,Year1,Month1,RECEIVE_FROM_PO,RECEIVE_XFER,RECEIVE_ADJ,
_RETURN,ADJUSTMENT,ISSUE,PT_ISSUE,TRANSFER, SUM(ABS(RECEIVE_FROM_PO + RECEIVE_XFER + RECEIVE_ADJ + _RETURN))
- SUM(ABS(ADJUSTMENT + ISSUE + PT_ISSUE + TRANSFER))
as BALANCE, BALANCE as OPENING_BAL
into final2
from dbo.drug_summary$
GROUP BY StockNumber,InventoryName,Year1,Month1,RECEIVE_FROM_PO,RECEIVE_XFER,RECEIVE_ADJ,
_RETURN,ADJUSTMENT,ISSUE,PT_ISSUE,TRANSFER,BALANCE
|
|
|
|
|