Database

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.

Mycroft Holmes8-Dec-12 20:57

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

VishwaKL9-Dec-12 19:06

9-Dec-12 19:06

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

Mycroft Holmes9-Dec-12 19:15

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

VishwaKL9-Dec-12 19:19

9-Dec-12 19:19

Thanks for your reply,

Will check the differences

VishwaKL9-Dec-12 18:37

9-Dec-12 18:37

Hi Blue_Boy

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

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.

VishwaKL10-Dec-12 19:31

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,

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

VishwaKL10-Dec-12 21:46

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

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

SSRS 2005

berba6-Dec-12 6:32

berba

6-Dec-12 6:32

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

David Mujica6-Dec-12 7:59

David Mujica

6-Dec-12 7:59

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.

Corporal Agarn6-Dec-12 9:30

Corporal Agarn

6-Dec-12 9:30

Microsoft does not know that Excel can now hold more rows, even when you tell it to use the newer format.

Mycroft Holmes6-Dec-12 16:57

6-Dec-12 16:57

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

berba6-Dec-12 18:06

berba

6-Dec-12 18:06

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

Mycroft Holmes6-Dec-12 21:43

Re: SQL Group By Question

6-Dec-12 21:43

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

SQL Group By Question

loyal ginger6-Dec-12 5:27

loyal ginger

6-Dec-12 5:27

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.

Blue_Boy6-Dec-12 10:11

Blue_Boy

6-Dec-12 10:11

You can try this query

SQL

SELECT mt.Field1,
       (
           SELECT TOP 1 mt2.Field2
           FROM   myTable mt2
           WHERE  mt2.Field1 = mt.Field1
           ORDER BY
                  mt2.Amount DESC
       ) AS Field2,
       MAX(mt.Amount)
FROM   myTable mt
GROUP BY
       mt.Field1

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: SQL Group By Question

loyal ginger31-Dec-12 5:48

loyal ginger

31-Dec-12 5:48

Thanks for your time!

Issues installing ODP.NETx64

cpp_prgmer6-Dec-12 4:18

cpp_prgmer

6-Dec-12 4:18

I just got a new 64bit machine and I'm migrating my code from my old 32bit machine. I wanted to install Oracle Data Providor for .NET, the 64bit one and I selected 64-bit ODAC 11.2 Release 5 (11.2.0.3.20) Xcopy for Windows x64 bit it had a note:
"Important: The 32-bit Oracle Developer Tools for Visual Studio download is required for Entity Framework design-time features".
So I installed the 32 bit Oracle Data Provider for .NET which is ODAC 11.2 Release 5 and Oracle Developer Tools for Visual Studio (11.2.0.3.20) with the hope that I could do the 64 bit installation on top of that.
Now the 64bit installation does not go through and I am not able to uninstall the 32 bit installation and I have no clue how to get out of this mess.

Sorry this is my first time installing as well as posting, experts please guide me.

Eddy Vluggen6-Dec-12 14:37

Eddy Vluggen

6-Dec-12 14:37

"Virtual Machine"

Don't use your primary development-machine as a testing-ground; use a virtual machine. I know it's slower, but it's also a lot safer.

Bastard Programmer from Hell Suspicious | :suss:

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

Peter_in_27806-Dec-12 15:12

Peter_in_2780

6-Dec-12 15:12

Sounds like your advice comes a bit late...

Cheers,
Peter

Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012

Mycroft Holmes6-Dec-12 16:52

6-Dec-12 16:52

I imagine he is trying to set up his primary development machine, not just testing a new environment.

Never underestimate the power of human stupidity
RAH

Mycroft Holmes6-Dec-12 16:53