469,643 Members | 1,486 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,643 developers. It's quick & easy.

Query for making a monthly report

Hai



Pls help me to write a query



I am having a table with name stock

Which contains fields:

Serialno,pdate,pname,quantity,unitprice , total

Serialno->number

Pdate->purchasedate(date/time)

Pname-> Product name(Text)

Quantity->Quantity(Number)

Unitprice->unitprice(number)

Total-> Total(Number)

I want to construct query to find the report for a month
Jul 15 '07 #1
2 4007
Infide
28
Hai



Pls help me to write a query



I am having a table with name stock

Which contains fields:

Serialno,pdate,pname,quantity,unitprice , total

Serialno->number

Pdate->purchasedate(date/time)

Pname-> Product name(Text)

Quantity->Quantity(Number)

Unitprice->unitprice(number)

Total-> Total(Number)

I want to construct query to find the report for a month
What are you trying to see? Totals by quantity? Totals by unit price? Before you build a sql statement, the first thing you should ask in your mind is "what do i want to see?".

But try this for starters.

Expand|Select|Wrap|Line Numbers
  1. declare @begindate datetime, @enddate datetime
  2.  
  3. set @begindate = '1/1/2007'
  4. set  @enddate = '1/31/2007 23:59'
  5.  
  6.  
  7. Select SUM(quantity) as quantity, sum(unitprice) as price, sum(total) as total, datename(mm,pdate) as Month
  8. from Stock
  9. WHERE pdate between @BeginDate and @EndDate
  10. GROUP BY datename(mm,pdate)
  11.  
Jul 15 '07 #2
according to your table design we can find out total quantity and total amount
for each product for each month

Select Month(PDate), ProductName, Sum(Quantity), Sum(Total) From
Stock Group By Month(PDate), ProductName


pls try this
Jul 16 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Tom | last post: by
1 post views Thread by alipark | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.