Data moth wise

Question

Post reply

Data moth wise

Saran

Right there with Babe

Points: 760

June 4, 2020 at 2:47 pm

#3760520

Hi All,

I just trying to get data month wise, following is just sample table. I would like to get a result like, only average of one entry month wise, for an year it should be 12 rows. The problem is the table is loading from other sources, it has lot of duplicates and per day it has almost 10+ entry.

Need something like this.

A, USA, 1000, 2019-07

A, USA, 1020, 2019-08

A, USA, 1050, 2019-09

etc...

--drop table #tbl_monthly_get_data
create table #tbl_monthly_get_data
(Name varchar(200), country varchar (200), amount bigint, Date datetime)

insert into #tbl_monthly_get_data values ('A', 'USA', 1000,'2019-07-04 12:48:01.683')
insert into #tbl_monthly_get_data values ('A', 'USA', 1001,'2019-07-05 12:48:01.683')
insert into #tbl_monthly_get_data values ('A', 'USA', 1002,'2019-07-06 12:48:01.683')


insert into #tbl_monthly_get_data values ('B', 'UK', 2000,'2019-08-04 12:48:01.683')
insert into #tbl_monthly_get_data values ('B', 'UK', 2001,'2019-08-05 12:48:01.683')
insert into #tbl_monthly_get_data values ('B', 'UK', 2002,'2019-08-06 12:48:01.683')

insert into #tbl_monthly_get_data values ('A', 'India', 1010,'2019-09-04 12:48:01.683')
insert into #tbl_monthly_get_data values ('B', 'India', 2040,'2019-09-05 12:48:01.683')
insert into #tbl_monthly_get_data values ('A', 'India', 4000,'2019-09-06 12:48:01.683')

select * from #tbl_monthly_get_data




select name,country,avg(amount) as avg_amount,
DATEPART(yyyy, Date)as Year,     DATEPART(mm, Date) as Month
--,month(t.Upload_date) as Date

from #tbl_monthly_get_data 
--where name= 'A' and country='USA'
--and date >= getdate ()-360
group by name,country,Date

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

Since your result set that shows what you want to display doesn't contain results that match with your sample data in the 3rd column, what are you looking for in the third column? A sum of the Amount for the month or what?

Edit... never mind. I see you're looking for an average.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

Try this...

 SELECT  Name
        ,country
        ,avg_amount = AVG(amount)
        ,[YYYY-MM]  = CONVERT(CHAR(7),DATEADD(mm,DATEDIFF(mm,0,[Date]),0),23)
   FROM #tbl_monthly_get_data 
  --WHERE name= 'A' 
  --  AND country='USA'
  --  AND [Date] >= DATEADD(yy,-1,GETDATE())
  GROUP BY Name,country,DATEDIFF(mm,0,[Date])
  ORDER BY Name,country,[YYYY-MM]
;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Saran Right there with Babe Points: 760 More actions · Answer 3

Thank you Sir! This works what I expected.

Happy learning.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

Thanks for the feedback and you're welcome. The key now is, since you're the one that has to support the code, do you understand how it works? Especially the DATEDIFF/DATEADD stuff?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Saran Right there with Babe Points: 760 More actions · Answer 5

I have tried and run each separately, the middle part DATEDIFF, I could not get it.

 select top 3 [Date] from #tbl_monthly_get_data
 select top 3 DATEDIFF(mm,0,[Date]) from #tbl_monthly_get_data
 select top 3 CONVERT(CHAR(7),DATEADD(mm,DATEDIFF(mm,0,[Date]),0),23) from #tbl_monthly_get_data

This reply was modified 4 years, 5 months ago by Saran.
This reply was modified 4 years, 5 months ago by Saran.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 6

DATEDIFF(mm,0,[Date]) calculates the number of month boundaries crossed (in effect, returns a count of months) since day "0", which is the 1st of January, 1900. That means that each date is basically assigned to "the month" it is a part of.

The DATEADD simply takes the number of months the DATEDIFF produced and adds them back to day "0", which effectively returns the 1st of whatever month the DATE is in.

All of that was to be able to "group by the month of the Date".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Saran Right there with Babe Points: 760 More actions · Answer 7

Thank you, I never know, date filed can accept zero and default start date is 1900-01-01 00:00:00.000.

select DATEDIFF(mm,0,getdate()) -- DATEDIFF ( datepart , startdate , enddate )

select DATEADD(mm,DATEDIFF(mm,0,getdate()),0)

select DATEADD(mm,0,0)-- DATEADD (datepart , number , date )

select DATEADD(mm,1445,0)