• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 176
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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