Very Urgent - Help Required on Set Analysis

[#1]

Hi,

   I want someone to please quickly write correct Expression for the below mentioned SQL query. Though it is simple but my expression is giving wrong results again and again. PFA is screenshot of Qlikview report. Kindly note Year and Customer are report selections here.

I am executing an SQL query at my database and I am getting expected values there.

Following is the query:

SELECT	COUNT(DISTINCT a.p_id) AS [Routine],
		b.month,
		b.year

FROM	Table1 a
		INNER JOIN Table2 b ON
		a.rountine_id = b.routine_id
		INNER JOIN Time c ON
		a.customer_id = c.customer_id AND
		a.date_of_routine BETWEEN c.open_date AND c.close_date

WHERE	b.customer_id = 5 AND
		a.flag IS NULL AND
		a.base IS NULL AND
		a.time_frame IS NULL AND
		a.routine_code_id NOT LIKE '%F' AND
		c.year = 2012

GROUP BY        c.month,

c.year

ORDER BY        c.year, c.month

Your quick response is required and shall be appreciated.

Thanks.

Busigence.

[#2]

Im not sure i understand your demand.

Dont you have an datedimension?

Otherwise you could load this into the script:

Load*;

SELECT	COUNT(DISTINCT a.p_id) AS [Routine],
		b.month,
		b.year

FROM	Table1 a
		INNER JOIN Table2 b ON
		a.rountine_id = b.routine_id
		INNER JOIN Time c ON
		a.customer_id = c.customer_id AND
		a.date_of_routine BETWEEN c.open_date AND c.close_date

WHERE	b.customer_id = 5 AND
		a.flag IS NULL AND
		a.base IS NULL AND
		a.time_frame IS NULL AND
		a.routine_code_id NOT LIKE '%F' AND
		c.year = 2012

GROUP BY        c.month,

c.year

ORDER BY        c.year, c.month

[alexandros17]

I think you need a graph with year and month as dimension and sum(Routine) as expression ...

Are you sure the query is correct?

[orital81]

Hi Busigence

Since you have a "BETWEEN" function it you will need to use Interval Match function in your script.

This usualy make the querry more complicated, if you want a quick solution I would

take Thomas's suggestion and use the SQL as is:

connection string

Load *;

SQL Select ....

Ori