help with query - last day of the month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help with query - last day of the month

Author

Topic

jcarrallo
Starting Member

35 Posts

Posted - 2014-11-15 : 06:15:23

Hello Sqlteam,

I´m trying to work out this select but not getting anywhere-.
Please help.
many thanks,
jay
____________________
I need to get the sum of sales for the last date of each month group by custom and by month(fecha)

for example, for custom ='q' in month=8 I have 3 items in the last day of the month ='2014-08-15' totalling 13 and so on

__________________________
DECLARE @sales TABLE
(custom VARCHAR(10) NOT NULL,
fecha DATE NOT NULL,
sales NUMERIC(10, 2) NOT NULL);

INSERT INTO @sales(custom, fecha, sales)
VALUES ('q', '20140708', 51),
('q', '20140712', 3),
('q', '20140712', 3),
('q', '20140712', 4),
('q', '20140811', 3),
('q', '20140812', 1),
('q', '20140815', 5),
('q', '20140815', 6),
('q', '20140815', 2),
('q', '20140114', 7),
('q', '20140714', 24),
('q', '20140714', 24),
('x', '20140709', 25),
('x', '20140710', 16),
('x', '20140711', 36),
('x', '20140712', 23),
('x', '20140712', 35),
('x', '20140715', 57),
('c', '20140712', 97),
('c', '20140715', 71);

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-11-15 : 13:27:18

Try this:

select b.custom
      ,b.fecha
      ,sum(b.sales) as sales
  from (select custom
              ,max(fecha) as fecha
          from @sales
         group by custom
                 ,datepart(yyyy,fecha)
                 ,datepart(mm,fecha)
       ) as a
       inner join @sales as b
               on b.custom=a.custom
              and b.fecha=a.fecha
 group by b.custom
         ,b.fecha
 order by b.custom
         ,b.fecha

jcarrallo
Starting Member

35 Posts

Posted - 2014-11-15 : 13:52:15

Brilliant. Many thanks !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources