CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
jt123
jt123

Reputation: 23

GROUP BY Conditions

For example I have a table with orders. The table has a key (Order.No), a date where the order was opened (Order.Open) and a date where the order was closed (Order.Close).

No          Open        Close
---------   ----------  ----------
2013-1208   2013-03-11  2013-03-26
2013-1272   2013-03-11  2013-03-11
2013-1273   2013-03-11  2013-03-11
2013-1274   2013-03-11  2013-03-11
2013-1275   2013-03-11  2013-03-11
2013-1280   2013-03-11  2013-06-26
2013-1281   2013-03-11  2013-04-18
2013-1282   2013-03-11  2013-03-14
2013-1287   2013-03-12  2013-04-18
2013-1291   2013-03-12  2013-03-12

Now I want to make a query, where I can find out for each month how many orders were still open on the last day of the month.

For example I want to find out how many orders were still open on the last day of January:

The order was closed on or after the first day of February and the order was opened before the first day of February:

SELECT COUNT(Order.No) 'Open', '1' 'Month', '2013' 'Year' FROM Orders
WHERE (Orders.Open < '2013-02-01') AND (Orders.Close >= '2013-02-01')

Now if I want to get this information for each month I would have to do:

SELECT COUNT(Order.No) 'Open', '1' 'Month', '2013' 'Year'  FROM Orders
WHERE (Orders.Open < '2013-02-01') AND (Orders.Close >= '2013-02-01')

UNION

SELECT COUNT(Order.No) Open, '2' Month, '2013' 'Year'  FROM Orders
WHERE (Orders.Open < '2013-03-01') AND (Orders.Close >= '2013-03-01')

UNION

SELECT COUNT(Order.No) 'Open', '3' 'Month', '2013' 'Year' FROM Orders
WHERE (Orders.Open < '2013-04-01') AND (Orders.Close >= '2013-04-01')

UNION

SELECT COUNT(Order.No) 'Open', '4' Month, '2013' Year FROM Orders
WHERE (Orders.Open < '2013-05-01') AND (Orders.Close >= '2013-05-01')

Can I somehow simplify this query so I don't have to write this for every month and year?

The required output would be something like:

Open    Month   Year
----    -----   ----
684     1       2013
683     2       2013
760     3       2013
659     4       2013

Upvotes: 2

Views: 192

Answers (7)

Jithin Shaji
Jithin Shaji

Reputation: 6073

Jt, Please see, whether this is working.

Let @Order be the table.

DECLARE @Order TABLE (No VARCHAR(20),[OPEN] Date,[Close] Date)

SELECT      YEAR([OPEN]),
            MONTH([OPEN]),
            SUM(IIF([Close] > DATEADD(DAY, 1,EOMONTH([OPEN])),1,0)) 
FROM        @Order 
GROUP BY    YEAR([OPEN]),MONTH([OPEN])

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

First generate all months you want to see in your results. Then join each month with the open orders and count per month.

with months as
(
  select 
    datefromparts(year(min(opened)), month(min(opened)), 1) as startdate
  from orders
  union all
  select
    dateadd(month, 1, startdate)
  from months
  where dateadd(month, 1, startdate) <= cast(getdate() as date)
)
select 
  year(months.startdate) as yr, month(months.startdate) as mon, count(orders.opened) as cnt
from months
left join orders 
  on orders.opened <= eomonth(months.startdate)
  and coalesce(orders.closed, '2999-12-31') > eomonth(months.startdate)
group by year(months.startdate), month(months.startdate)
order by year(months.startdate), month(months.startdate);

Here is the SQL fiddle: http://sqlfiddle.com/#!6/12ee7/3.

In SQL Server 2008 the functions DATEFROMPARTS and EOMONTH are missing. Replace them by 

cast( cast(year(min(opened)) as varchar) + '-' + cast(month(min(opened)) as varchar) + '-01' as date) as startdate

and

dateadd(day, -1, dateadd(month, 1, months.startdate))

Here is the SQL fiddle for SQL Server 2008: http://sqlfiddle.com/#!3/12ee7/5.

Upvotes: 3

GarethD
GarethD

Reputation: 69769

You can just enter the list of months/years you require and join to them, e.g.

SELECT  [Open] = COUNT(o.No),
        [Month] = DATEPART(MONTH, d.[Date]),
        [Year] = DATEPART(YEAR, d.[Date])
FROM    (VALUES
            ('2013-01-01'),
            ('2013-02-01'),
            ('2013-03-01'),
            ('2013-04-01'),
            ('2013-05-01'),
            ('2013-06-01')
        ) d (Date)
        LEFT JOIN Orders AS o
            ON o.[Open] < d.[Date]
            AND o.[Close] >= d.[Date]
GROUP BY d.Date;

If you have a calendar table then you can use this instead of a list of hard coded dates.

If you don't want to hardcode the dates you want then you can fairly easily generate a list on the fly, first generate a list of numbers:

WITH E1 AS -- 10 ROWS
(   SELECT  N = 1
    FROM    (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)
), E2 AS -- 10 X 10 = 100 ROWS
(   SELECT  N = 1
    FROM    E1 CROSS JOIN E1 AS E2
), E3 AS -- 100 x 100 = 10,000 ROWS
(   SELECT  N = 1
    FROM    E2 CROSS JOIN E2 AS E3
)
SELECT  N = ROW_NUMBER() OVER(ORDER BY N)
FROM    E3;

This is just an example, but will generate 10,000 sequential numbers, realistically you probably don't need to report on 10,000 months, but it doesn't hurt to demonstrate. Then you can turn this list of numbers into a list of dates:

WITH E1 (N) AS 
(   SELECT 1 
    FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)
), 
E2 (N) AS (SELECT 1 FROM E1 CROSS JOIN E1 AS E2), 
E3 (N) AS (SELECT 1 FROM E2 CROSS JOIN E2 AS E3)
SELECT  [Date] = DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY N) - 1, '19000101')
FROM    E3;

Then you can use this as your main table and LEFT JOIN to Orders:

WITH E1 (N) AS 
(   SELECT 1 
    FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)
), 
E2 (N) AS (SELECT 1 FROM E1 CROSS JOIN E1 AS E2), 
E3 (N) AS (SELECT 1 FROM E2 CROSS JOIN E2 AS E3),
Dates AS 
(   SELECT  [Date] = DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY N) - 1, '19000101')
    FROM    E3
)
SELECT  [Open] = COUNT(o.No),
        [Month] = DATEPART(MONTH, d.[Date]),
        [Year] = DATEPART(YEAR, d.[Date])
FROM    Dates AS d
        LEFT JOIN Orders AS o
            ON o.[Open] < d.[Date]
            AND o.[Close] >= d.[Date]
WHERE   d.Date >= '20130101' -- OR WHATEVER DATE YOU LIKE
AND     d.Date < GETDATE();

For some further reading on generating and using a numbers/dates table, both static and on the fly, take a look at this series:

Upvotes: 2

mvisser
mvisser

Reputation: 670

Try this

SELECT DISTINCT COUNT(no) OVER (PARTITION BY DATEPART(mm,Open)) AS [Open]
                DATEPART(mm,Open) AS [Month], 
                DATEPART(mm,Open) AS [Year]
FROM dbo.tble

Upvotes: 1

atom.gregg
atom.gregg

Reputation: 1007

First, create a month series so you don't have any gaps in your result set. Then join this to your Order table on the conditions needed.

Group the results by the month series and done.

;with months as (
    select cast('2013-01-01' as datetime) as dt union all
    select cast('2013-02-01' as datetime) union all
    select cast('2013-03-01' as datetime) union all
    select cast('2013-04-01' as datetime) union all
    select cast('2013-05-01' as datetime) union all
    select cast('2013-06-01' as datetime) union all
    select cast('2013-07-01' as datetime) union all
    select cast('2013-08-01' as datetime) union all
    select cast('2013-09-01' as datetime) union all
    select cast('2013-10-01' as datetime) union all
    select cast('2013-11-01' as datetime) union all
    select cast('2013-12-01' as datetime)
)
select count(o.No), datepart(month, m.dt) month, datepart(year, m.dt) year
from months m
left join Orders o 
    on o.Open < dateadd(month, m.dt, 1)
    and o.Close >= dateadd(month, m.dt, 1)
group by m.dt

Upvotes: 1

Panoptik
Panoptik

Reputation: 1142

Also try this. You can change condition to get better results

SELECT COUNT(`no`) Open, 
       DATE_FORMAT(`open`,'%Y-%m') as period, 
       month(`open`) Month, 
       year(`open`) Year
  FROM `orders`
WHERE DATE_FORMAT(`close`, '%Y-%m') <> period
GROUP BY period

Upvotes: 2

Rashid
Rashid

Reputation: 293

SELECT COUNT(Order.No) Open, '1' Month, '2013' Year FROM Orders
group by Orders.Open, Orders.Close

Upvotes: 1

Related Questions