CODEWITHSUNDEEP
sql-serversql-server-2008
Sravanti
Sravanti

Reputation: 1407

SQL query to fetch all results in one query

Test data:

    id  date       company  location  
   ----------------------------------
    1   03/01/2016  ABC      india     
    2   03/25/2016  ABC      us
    3   02/24/2016  ABC      india
    4   02/25/2016  ABC      us
    5   03/02/2016  ABC      india

Query #1

select count(id) 
from table 
where company = 'ABC' 
  and date between 03/01/2016 and 03/31/2016

Query #2

select count(id) 
from table 
where company = 'ABC' 
  and date between 02/01/2016 and 02/29/2016

Need to calculate location wise count for current and previous months. How to write a SQL query to return location wise like the below in one query?

Expected result:

   company currentmonth   Previousmonth   location
    ABC      2                   1          india
    ABC      1                   1           us

Upvotes: 1

Views: 107

Answers (2)

Jorge Campos
Jorge Campos

Reputation: 23381

Try:

select company,
       sum(case when month(date)=month(getdate()) 
                        then 1 else 0 end) as currentMonth,
       sum(case when month(date)=month(dateadd(MONTH,-1,getdate())) 
                        then 1 else 0 end) as Previuosmonth,
       location 
  from yourTable
 where month(date) between month(dateadd(MONTH,-1,getdate())) and month(getdate())
   AND company='ABC'
 group by company, location

Since you made two queries and filter both with current month (march) and previous. I decided to use the function MONTH to extract the month from the current date (getdate()) of the system and subtract 1 that way you will always have the current month and the previous one.

EDIT

As pointed out by @DhananjayaKuppu I fixed the problem for when you have a month as January, since the month function returns an integer it would return 0 in my calculation. Now it is fixed.

Upvotes: 3

Dhananjaya Kuppu
Dhananjaya Kuppu

Reputation: 1322

You may try some thing like subtract current date yearmm format (ex: 201603) and yearmm format of date from table (ex:201603) if it result to 0 treat as current month, else previous month, hope it will help:

SELECT company,
       location,
       sum(iif(d = 0, 0, 1)) cm,
       sum(iif(d = 0, 1, 0)) pm        
FROM (SELECT company, 
       location, 
       CONVERT(INT,
         CONVERT(CHAR(4), GetDate(), 120) + 
         CONVERT(CHAR(2), GetDate(), 101)
       ) -
       CONVERT(INT, 
         CONVERT(CHAR(4), date, 120) + 
         CONVERT(CHAR(2), date, 101)
       )  as d
FROM table) vw
GROUP BY company, location

Upvotes: 2

Related Questions