|
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
|
|
|
|
|
Looks correct (enough) to me 
What's wrong with it? Missing table?
|
|
|
|
|
Opening balance of current year is the closing balance of the previous year, and closing balance of the current year would be opening balance of the next year.
If this is clear for you then you can calculate from your data. Let me know if you still having some issue.
Thanks
-Amit Gajjar (MinterProject)
|
|
|
|
|
Hi guys
I have a problem to select query.
For yearly report i am getting by my query
SELECT YEAR(RequestedDate) as Years, Month(RequestedDate) as MonthInNumbers,Count(Month(RequestedDate)) as MonthCount
FROM MYTABLE
GROUP BY YEAR(RequestedDate),Month(RequestedDate)
Year Month Total
2012 2 13
2012 3 61
2012 4 46
2012 5 48
2012 6 63
2012 10 54
2012 12 11
for this i want
Year Month Total
2012 1 0
2012 2 13
2012 3 61
2012 4 46
2012 5 48
2012 6 63
2012 7 0
2012 8 0
2012 9 0
2012 10 54
2012 11 0
2012 12 11
any help
|
|
|
|
|
Here it is
create table #tempMonths (monthNr int)
insert into #tempMonths (monthNr) values (1)
insert into #tempMonths (monthNr) values (2)
insert into #tempMonths (monthNr) values (3)
insert into #tempMonths (monthNr) values (4)
insert into #tempMonths (monthNr) values (5)
insert into #tempMonths (monthNr) values (6)
insert into #tempMonths (monthNr) values (7)
insert into #tempMonths (monthNr) values (8)
insert into #tempMonths (monthNr) values (9)
insert into #tempMonths (monthNr) values (10)
insert into #tempMonths (monthNr) values (11)
insert into #tempMonths (monthNr) values (12)
SELECT isnull( YEAR(RequestedDate) ,YEAR(getdate())) as Years,
isnull( Month(RequestedDate),temp.monthNr) as MonthInNumbers,temp.monthNr,
Count(Month(RequestedDate)) as MonthCount
FROM tblMYTABLE
right join #tempMonths temp on temp.monthNr = Month(RequestedDate)
GROUP BY YEAR(RequestedDate),Month(RequestedDate),temp.monthNr
drop table #tempMonths
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Why not use @TableVar instead of a temptable#, I wonder if I have asked you this question before!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi
Can you Explain me the usage of @TableVar and how that is helpful for my question
|
|
|
|
|
It makes no difference to the solution, Blue Boy has given you the definitive solution.
A temp table creates a table object in the temp database and writes the data to the hard drive. A table variable does this in memory, there are some benefits to both. A temp table can have indexes applied and a global temp table can be shared between procedures.
I default to table vars, BB probably defaults to temp tables, I was wondering if there was a reason.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your reply,
Will check the differences
|
|
|
|
|
Hi Blue_Boy
Thank you so much dude.  
|
|
|
|
|
Another option is the USE of Decode function that will also give u the correct result with out using the temp table or variable, if u can share the data of yr mytable then will try to give u the sql query.
|
|
|
|
|
I want to select closed requests per month and total request per month both
my table is like
RequestID,RequesterName,RequestStatus(OPen/close),RequestedDate.
From these fields i have to fetch that data,
|
|
|
|
|
select years, month, sum(MonthCount) from
(
SELECT to_char(trunc(RequestedDate, 'Year'), 'YYYY') as Years, to_char(trunc(RequestedDate, 'Month'), 'MON') as Month, count(to_char(trunc(RequestedDate, 'Month'), 'MON')) as MonthCount
FROM MYTABLE
group by to_char(trunc(RequestedDate, 'Year'), 'YYYY'), to_char(trunc(RequestedDate, 'Month'), 'MON')
union
select '2012' as years, 'JAN' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'FEB' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'MAR' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'APR' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'MAY' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'JUN' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'JULY' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'AUG' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'SEPT' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'OCT' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'NOV' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'DEC' as Month, 0 as MonthCount from dual
) group by years, month
|
|
|
|
|
Thanks deepak,
But i getting 2 errors after executing , i changed mytable to my real table, but it giving below mentioned errors
1) 'trunc' is not a recognized built-in function name
and
2)Incorrect syntax near ')'.
|
|
|
|
|
so yr query will be
select years, month, sum(MonthCount) from
(
SELECT YEAR(RequestedDate) as Years, Month(RequestedDate) as MonthInNumbers,Count(Month(RequestedDate))
FROM MYTABLE
group by YEAR(RequestedDate),Month(RequestedDate)
union
select '2012' as years, 'JAN' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'FEB' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'MAR' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'APR' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'MAY' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'JUN' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'JULY' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'AUG' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'SEPT' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'OCT' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'NOV' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'DEC' as Month, 0 as MonthCount from dual
) group by years, month
|
|
|
|
|
Hi,
I've developed a report in SSRS 2005. My query is returning more that 65,000 rows and I'm getting an error when exporting the report to excel. Any idea how to solve this?
The report can be exported successfully in PDF
Cheers
Berba
|
|
|
|
|
This is a limit of Excel. I beleive Excel 2010 allows for more than 65,000.
I think you only solution would be to break the results into multiple Excel documents.
Remember to vote.
|
|
|
|
|
Microsoft does not know that Excel can now hold more rows, even when you tell it to use the newer format.
|
|
|
|
|
65k rows is NOT a report, it is a data dump. You are using the wrong tool for the job, you should be exporting the data as csv either from the database or your client application NOT a reporting tool!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The report is loading fine on the asp.net webpage via the report viewer control.
It's just when exporting to excel the customer is getting an error. In PDF it is fine
|
|
|
|
|
berba wrote: It's just when exporting to excel the customer is getting an error
Which means it is the WRONG tool for the job. The tool has a limitation, as pointed out by David and djj.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am trying to do this in Microsoft Access. I seem to be unable to construct a 'simple' query. To illustrate, here is a sample table:
Field1 Field2 Amount
A New 10
D Old 5
G Old 1
H Old 15
H New 55
X Old 20
Z Old 100
Z New 20
I need to group by Field1 and only return the Field2 data for the record that has the maximum number in the Amount field. I tried to use the 'First' function but Microsoft Access sort the records internally so it does not return the right value even if I pre-sort the records with a sub-query. Do you have any advice? The result should look like the table below.
Desired Result:
Field1 Field2 Max(Amount)
A New 10
D Old 5
G Old 1
H New 55
X Old 20
Z Old 100
I have trouble getting the value of Field2 in the record containing to the maximum value of 'Amount.'
Any suggestions? Thanks in advance for your time!
modified 6-Dec-12 11:51am.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
