[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • Last Modified:

sql Question

We have a table with the following columns

Date
Company
Status
Cost

Status values are either Sale or Purc

If Sale then Cost is a negative number

Many transactions occur in a month

I need to create a queryin the following format

Month (ddd-yy)
Purc
Sales

Month must be continuous through a period regardless of whether there have been any sales or purchases in that period.

Month                       Purc     Sales
Mar-06             0     -1000
Apr-06             456       -789
May-06                 0            0
Jun-06              318       -349

Can anyone help?
0
WingYip
Asked:
WingYip
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT CONVERT(varchar,[Date],112) Dt, SUM(Case When Status = 'Sale' then Cost else 0 end ) Sale, SUM(Case When Status = 'Purc' then Cost else 0 end ) Purchase
GROUP BY CONVERT(varchar,[Date],112)  
0
 
appariCommented:
try this

with months(Year_No, Month_No)
as
(Select 1
union Select Month_No + 1 from months where Month_No <12),
years(Year_No)
as( Select distinct year(Date) from yourtablename)
Select Year_No, Month_No,
SUM(Case When Status = 'Sale' then Cost else 0 end ) Sale, SUM(Case When Status = 'Purc' then Cost else 0 end ) Purchase
from
( months cross join years) as yearMons
left outer join yourTableName
on Year_No = year(Date)
and Month_No = month(Date)
group by Year_No, Month_No
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now