WHERE CLAUSE

[phani1979]

Hello eveyone..
I have a table like sales_target..
the columns like sales_month,sales_year,sales_target..
Now what i require is
I have to  show the sales_target for the year 2004 month wise..I need to show that data how much sales achieved from "jan" to "Dec", i have to show for 8 months sales target in row wise..
how to show that
can u please me.. i had stuck up with this task
thank u evryone
sudhakar

[mikegreen]

Are you doing this in FM or Reportstudio?

Just add a filter on the sales month and sales year columns and let the user get prompted to enter their values.

or, you could hard-code a query subject to only include 2004 and Jan, Feb, Mar, etc etc...

or, you could convert each month and year into a real date, then use a between clause.

regards,

Mike

[phani1979]

Hey Mike
Thank  for ur reply,,
Iam doing this in FM.. My Report is a comlex report...

[mikegreen]

If you are doing the filtering at the query subject level, then I would make a calculation that makes a date out of your Month and Year columns... something like
CAST((month  + '/1/' + year) AS DATE)

Then you can use a regular between clause and let the user (or reportstudio creator) pick their date range like a normal date.

[bdybldr]

Can you provide an example of the output you're looking for?

[phani1979]

I have one table.. sales_targert
cloumns as sales_year and sales_month and sales_target..
sales_month              sales_year         sales_target                 2004salestarget
---------------                ------------           -----------------                 --------------------
1                                2004                       100000
2                                2004                         350000
3                                 2004                        4500000

now i wanna shows that total sales target achieved in each month in 2004 should be displayed in 2004 salestraget column

[bdybldr]

Phani,
I would recommend using a tabular set.  It works basically in the same way as a union query.  You can combine the results of two disimilar queries into one, as long as you have the same number of query items and they're the same data type..

Query1:

Select [sales_month], [sales_year], [sales_target], 0 AS 2004salestarget
from...
where...

UNION

Query2:

Select [sales_month], [sales_year], 0 AS sales_target, [2004salestarget]
from...
where...

Then set aggregate property to "Total". 

Hope this helps...