SQL Query

[#1]

Hello Folk,

I am fetching the data from Sql database where i have to fetch data only for Current week and there is a Field called Date where Date is in Format MM/DD/YYYY HH:MM:SS. so what query i have to write for this so that it will only fetch Current Week Data.

Thanx

[datanibbler]

Hi vini,

I often had that same question, and I ended up going a diffferent way:

Do you know the "preceding LOAD"? I use that every time I have an SQL query - that way I don't have to bother with writing my filters in the SQL_SELECT, but instead I can write them in the LOAD and use the QlikView_syntax.

The preceding LOAD means there is only one pass of the data, not two as with the RESIDENT LOAD, so that's really about as easy as things can get.

HTH

Best regards,

DataNibbler

[#3]

Can u give me some Sample Query for this..

[richard_chilvers]

Hi DataNibbler

You make an interesting point.

I think you're suggesting the SQL SELECT gets all the unfiltered data and you use the LOAD to then filter down to what's required in the QV model? My understanding is that this is less efficient than filtering the data you SQL SELECT as this is the time and resource-consuming process?

Anyway, I guess Vini needs a hint about how to use WEEK functions in QV?

Regards

[MarcoWedel]

Hi,

you could try:

LOAD *
Where InWeek(Date#(Date,'MM/DD/YYYY hh:mm:ss'), ToDay(),0);
SQL SELECT
    SomeFields
FROM YourDBTable;

hope this helps

regards

Marco

[MarcoWedel]

The prefered solution might be to implement the where clause in the SQL part due to run time issues

[jagan · Accepted solution]

HI,

Fetch current week data by using where condition in SQL itself, it is the best solution.

In SQL Server:

Data:

SELECT

*

FROM TableName

WHERE DATEPART( wk , DateFieldName) =  DATEPART(wk, GetDate());

In Oracle:

Data:

SELECT

*

FROM TableName

WHERE to_char(DateFieldName,'WW') = to_char(SYSDATE,'WW');

Note: Replace DateFieldName with your actual date field name in table.

If you are not using ORacle or SQL Server then check for corresponding date functions.

Hope this helps you.

Regards,

Jagan.