CODEWITHSUNDEEP
sql-serversql-server-2014
Niharika
Niharika

Reputation: 71

Get month-wise data in SQL Server Query

I am trying to get data month wise for that I have created query but have some repeated record in my result as shown below using SQL Server.

SELECT DM.deptdesc, 
   PIM.itemcode, 
   PIM.itemdesc, 
   Isnull(Sum(IM.issuedqty), 0) AS 'Issued', 
   Datename(month, IM.dt)       AS MONTH, 
   Year(IM.dt)                  AS year 
FROM  poitemmaster AS PIM 
   inner JOIN issuematerial AS IM 
                ON PIM.itemcode = IM.itemcode 
   LEFT OUTER JOIN departmentmaster AS DM 
                ON PIM.deptcode = DM.deptcode 
WHERE  PIM.itemcode = 'ICMEL00001' 
GROUP  BY DM.deptdesc, 
      PIM.itemcode, 
      PIM.itemdesc, 
      IM.issuedqty, 
      Datename(month, IM.dt), 
      Year(IM.dt)

My query return this data

query default return data

but I want to like this

Actual I want to like this

I want the month-wise sum in quantity. What should I have to do for that?

Upvotes: 0

Views: 338

Answers (1)

Cedersved
Cedersved

Reputation: 1025

Remove the issuedqty column from the group by, and add a SUM() to the same column in the column list

SELECT DM.deptdesc, 
   PIM.itemcode, 
   PIM.itemdesc, 
   Sum(ISNULL(IM.issuedqty, 0) AS 'Issued', 
   Datename(month, IM.dt)       AS MONTH, 
   Year(IM.dt)                  AS year 
FROM  poitemmaster AS PIM 
   inner JOIN issuematerial AS IM 
                ON PIM.itemcode = IM.itemcode 
   LEFT OUTER JOIN departmentmaster AS DM 
                ON PIM.deptcode = DM.deptcode 
WHERE  PIM.itemcode = 'ICMEL00001' 
GROUP  BY DM.deptdesc, 
      PIM.itemcode, 
      PIM.itemdesc, 
      Datename(month, IM.dt), 
      Year(IM.dt)

Upvotes: 5

Related Questions