Re: SQL Query To select result for monthly - Database Discussion Boards

Re: SQL Query To select result for monthly

Eddy Vluggen9-Dec-12 23:18

9-Dec-12 23:18

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.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

Re: SQL Query To select result for monthly

VishwaKL9-Dec-12 23:20

VishwaKL

9-Dec-12 23:20

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,

Re: SQL Query To select result for monthly

Eddy Vluggen9-Dec-12 23:24

Eddy Vluggen

9-Dec-12 23:24

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 Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

Re: SQL Query To select result for monthly

VishwaKL9-Dec-12 23:27

VishwaKL

9-Dec-12 23:27

thank you for your support

Re: SQL Query To select result for monthly

David Mujica10-Dec-12 3:16

David Mujica

10-Dec-12 3:16

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.
Thumbs Up | :thumbsup:

Re: SQL Query To select result for monthly

VishwaKL10-Dec-12 14:48

VishwaKL

10-Dec-12 14:48

hi i am not having closed date field,

i have only requesteddate, and status of the ticket like open/close

Re: SQL Query To select result for monthly

Shameel10-Dec-12 21:34

Shameel

10-Dec-12 21:34

The Group By hint should be more than enough for you to get started.

Re: SQL Query To select result for monthly

J4amieC11-Dec-12 0:11

J4amieC

11-Dec-12 0:11

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:

SQL

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:

SQL

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:

SQL

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:

SQL

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

Re: SQL Query To select result for monthly

VishwaKL11-Dec-12 14:54

VishwaKL

11-Dec-12 14:54

thank you so much J4amieC,

it worked for me,

Re: SQL Query To select result for monthly

MS SQL DEVELOPER10-Dec-12 23:35

MS SQL DEVELOPER

10-Dec-12 23:35

Select [MONTH],Count([Total Requests]),COUNT([Solved Request])
From Temp
Group By [MONTH]

How to get the closing balance to be the opening balance of the following month

vusamozi9-Dec-12 19:23

vusamozi

9-Dec-12 19:23

SQL

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

Re: How to get the closing balance to be the opening balance of the following month

Eddy Vluggen9-Dec-12 23:13

Eddy Vluggen

9-Dec-12 23:13

Looks correct (enough) to me Thumbs Up | :thumbsup:

What's wrong with it? Missing table?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

Re: How to get the closing balance to be the opening balance of the following month

AmitGajjar11-Dec-12 7:17

AmitGajjar

11-Dec-12 7:17

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)

Select All Months and related data

VishwaKL6-Dec-12 22:21

VishwaKL

6-Dec-12 22:21

Hi guys

I have a problem to select query.

For yearly report i am getting by my query

SQL

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

Re: Select All Months and related data

Blue_Boy7-Dec-12 1:18

Blue_Boy

7-Dec-12 1:18

Here it is

SQL

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.

Re: Select All Months and related data

Mycroft Holmes8-Dec-12 20:57

Mycroft Holmes

8-Dec-12 20:57

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

Re: Select All Months and related data

VishwaKL9-Dec-12 19:06

VishwaKL

9-Dec-12 19:06

Hi
Can you Explain me the usage of @TableVar and how that is helpful for my question

Re: Select All Months and related data

Mycroft Holmes9-Dec-12 19:15

Mycroft Holmes

9-Dec-12 19:15

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

Re: Select All Months and related data

VishwaKL9-Dec-12 19:19

VishwaKL

9-Dec-12 19:19

Thanks for your reply,

Will check the differences

Re: Select All Months and related data

VishwaKL9-Dec-12 18:37

VishwaKL

9-Dec-12 18:37

Hi Blue_Boy

Thank you so much dude. Big Grin | :-D

Re: Select All Months and related data

Deepak Kr110-Dec-12 19:27

Deepak Kr1

10-Dec-12 19:27

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.

Re: Select All Months and related data

VishwaKL10-Dec-12 19:31

VishwaKL

10-Dec-12 19:31

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,

Re: Select All Months and related data

Deepak Kr110-Dec-12 21:02

Deepak Kr1

10-Dec-12 21:02

Hope this will work

SQL

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

Re: Select All Months and related data

VishwaKL10-Dec-12 21:46

VishwaKL

10-Dec-12 21:46

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 ')'.

Re: Select All Months and related data

Deepak Kr110-Dec-12 22:25

Deepak Kr1

10-Dec-12 22:25

Hey i just used the trunc method for oracle, u can replace to yr old year and month method to get the result, but the concept is that we will use the union clause to get the data for those month for which we dont have the data than we will do a union with yr old query then on the top of that put a sum for monthcount with group by month and year.

so yr query will be

SQL

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

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.