June 25, 2010 at 12:27 pm
Hi,
I have a table called XYZ . This table stores all this information shown below.
AdId Class OriginalDate AmtPaid
2741069 101 2010-06-21 125.00
2694907 101 2010-06-22 262.50
2741898 101 2010-06-22 262.50
2748110 101 2010-06-22 300.00
I want to display the above as follows
Class OriginalDate AmtPaid
101 2010-06-21 125.00
101 2010-06-22 825.00
Any help appreciated .
Thanks.
June 25, 2010 at 12:34 pm
this almost seams to simple of a question so I feal like I am missing somehting but.
select Class,OriginalDate,sum(AmtPaid)
from insertyourtablename
group by Class,OriginalDate
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 25, 2010 at 12:44 pm
I have also used the query below
SELECT DISTINCT A.Class,
A.Origdate,
SUM(A.totalpaid) As AmtPaid
FROM dbo.ad A
GROUP BY A.Class,A.Origdate,A.totalpaid
HAVING A.Origdate >= '6/21/2010' AND A.Origdate < '6/25/2010'
AND A.totalpaid <> 0.00
But it still returns
Class Origdate AmtPaid
101 2010-06-21 13:02:14.960 125.00
101 2010-06-22 14:25:37.343 262.50
101 2010-06-22 15:00:50.437 262.50
101 2010-06-22 15:52:48.647 300.00
I want to avoid the timestamp and group based on the class and date .
June 25, 2010 at 12:53 pm
Sorry about that I knew I was missing something. The problem is that when you display the date it has the time stamp and there for the nano secods make each vlue uniqu in the grouping so convert the the date to drop the time and poof they will all combine.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 25, 2010 at 1:08 pm
Sorry I meant to post the code. Man I am glad it is Friday.
SELECT DISTINCT A.Class,
CAST( CONVERT( CHAR(8), A.Origdate, 112) AS DATETIME),
SUM(A.totalpaid) As AmtPaid
FROM dbo.ad A
GROUP BY A.Class,CAST( CONVERT( CHAR(8), A.Origdate, 112) AS DATETIME),A.totalpaid
HAVING A.Origdate >= '6/21/2010' AND A.Origdate < '6/25/2010'
AND A.totalpaid <> 0.00
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 25, 2010 at 1:19 pm
I am getting the following error :
Column 'dbo.ad.origdate' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
June 25, 2010 at 1:24 pm
Dan.Humphries (6/25/2010)
I think this works....without using CONVERT or CAST....
declare @table table (id int, dy smalldatetime,amt numeric (18,2))
Insert Into @table
select 101,2010-06-21,125.00
union all select 101,2010-06-22,262.50
union all select 101,2010-06-22,262.50
union all select 101,2010-06-22,300.00
select * From @Table
select id
,dy
,SUM(amt)as AmountPaid
from @table
GROUP BY ID,DY
ORDER BY AmountPaid
FO
June 25, 2010 at 1:27 pm
If I use the query below
SELECT DISTINCT A.Class,
CAST( CONVERT( CHAR(8), A.Origdate, 112) AS DATETIME),
SUM(A.totalpaid) As AmtPaid
FROM dbo.ad A
GROUP BY A.Class,CAST( CONVERT( CHAR(8), A.Origdate, 112) AS DATETIME),A.totalpaid
HAVING CAST( CONVERT( CHAR(8), A.Origdate, 112)AS DATETIME) >= '6/21/2010' AND CAST( CONVERT( CHAR(8), A.Origdate, 112)AS DATETIME) < '6/25/2010'
AND A.totalpaid <> 0.00 AND A.Class = 101
Order By A.Class
I get the following result :
Class (No column name) AmtPaid
101 2010-06-21 00:00:00.000 125.00
101 2010-06-22 00:00:00.000 300.00
101 2010-06-22 00:00:00.000 525.00
June 25, 2010 at 1:28 pm
move the date parameter to a where clause and that should take care of it.
SELECT DISTINCT A.Class,
CAST( CONVERT( CHAR(8), A.Origdate, 112) AS DATETIME),
SUM(A.totalpaid) As AmtPaid
FROM dbo.ad A
where CAST( CONVERT( CHAR(8),A.Origdate, 112) AS DATETIME), between A.Origdate '6/21/2010' AND '6/25/2010'
GROUP BY A.Class,CAST( CONVERT( CHAR(8), A.Origdate, 112) AS DATETIME),A.totalpaid
HAVING AND A.totalpaid <> 0.00
sorry I was posting at the same time. I am not sure why it is doig that but I will try a few things. your query would work fine.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 25, 2010 at 1:54 pm
The following query
SELECT DISTINCT A.Class,
CAST( CONVERT( CHAR(8), A.Origdate, 112) AS DATETIME),
SUM(A.totalpaid) As AmtPaid
FROM dbo.ad A
where CAST( CONVERT( CHAR(8),A.Origdate, 112) AS DATETIME)>= '6/21/2010' AND CAST( CONVERT( CHAR(8),A.Origdate, 112) AS DATETIME) < '6/25/2010'
AND A.totalpaid <> 0.00
GROUP BY A.Class,CAST( CONVERT( CHAR(8), A.Origdate, 112) AS DATETIME),A.totalpaid
Order By A.Class
results in
Class (No column name) AmtPaid
101 2010-06-21 00:00:00.000 125.00
101 2010-06-22 00:00:00.000 525.00
101 2010-06-22 00:00:00.000 300.00
June 25, 2010 at 2:05 pm
declare @table table (id int, dy smalldatetime,amt numeric (18,2))
Insert Into @table
select 101,2010-06-21,125.00
union all select 101,2010-06-22,262.50
union all select 101,2010-06-22,262.50
union all select 101,2010-06-22,300.00
select * From @Table
select id
,dy
,SUM(amt)as AmountPaid
from @table
GROUP BY ID,DY
ORDER BY AmountPaid
FO
June 25, 2010 at 2:13 pm
you had one two many criteria in your group by also I would move the having to a where clause.
declare @table table (AdId int, class int, Origdate datetime,totalpaid numeric (10,2))
insert into @table (AdId,class,Origdate,totalpaid) values(2741069,101,'06/21/2010 13:02:14.960',125.00)
insert into @table (AdId,class,Origdate,totalpaid) values(2694907,101,'06/22/2010 14:25:37.343',262.50)
insert into @table (AdId,class,Origdate,totalpaid) values(2741898,101,'06/22/2010 15:00:50.437',262.50)
insert into @table (AdId,class,Origdate,totalpaid) values(2748110,101,'06/22/2010 15:52:48.647',300.00)
SELECT DISTINCT A.Class,
CAST( CONVERT( CHAR(8), A.Origdate, 112) AS DATETIME),
SUM(A.totalpaid) As AmtPaid
FROM @table A
WHERE CAST( CONVERT( CHAR(8), A.Origdate, 112)AS DATETIME) >= '6/21/2010' AND CAST( CONVERT( CHAR(8), A.Origdate, 112)AS DATETIME) < '6/25/2010'
AND A.totalpaid <> 0.00 AND A.Class = 101
GROUP BY A.Class,CAST( CONVERT( CHAR(8), A.Origdate, 112) AS DATETIME)
Order By A.Class
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 25, 2010 at 2:15 pm
falgunoza,
If the data was stored as smalldatetime I doubt he would be having this problem to start with. It is fairly typical to have to do a convert in my expierence smalldatetime is seldom used.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 25, 2010 at 2:25 pm
Thanks that worked .
June 25, 2010 at 2:40 pm
Dan.Humphries (6/25/2010)
falgunoza,If the data was stored as smalldatetime I doubt he would be having this problem to start with. It is fairly typical to have to do a convert in my expierence smalldatetime is seldom used.
But I changed table variable's date column data type to datetime and it still worked...Am I missing something I wonder....:unsure:
FO
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply