Transform SQL select to QV expression

[#1]

Hello QV developers,

please, could anybody help me with transforming SQL select to QV expression? I have created an example QVW.

I want to see  Orders (work in progress in specified season) as is specified in this MS SQL select (orders in progress till 31.1. 2012).

select COUNT(*) as Order from KPI_Orders

where ((Order_date_income <='20120131 23:59:59' and (Order_date_closed >'20120131 23:59:59' or Order_closed=0))

            or (Order_date_income <='20120131 23:59:59' and Order_date_closed ='19000101 00:00:00'))

The correct number of this select in MS SQL is 54.

Thank you for any help!

Mirek

[Gysbert_Wassenaar · Accepted solution]

Once you cast the date strings to real dates the calculation will show the correct count:

count(DISTINCT(If (Order_date_income <=date#('20120131 23:59:59','YYYYMMDD hh:mm:ss') and (Order_date_closed >date#('20120131 23:59:59','YYYYMMDD hh:mm:ss') or Order_closed='0')

            or (Order_date_income <=date#('20120131 23:59:59','YYYYMMDD hh:mm:ss') and Order_date_closed =date#('19000101 00:00:00','YYYYMMDD hh:mm:ss')), Order)))

[#3]

Thank you, I found the problem with my date format, is different from date in SQL select. Now it's works and show the correct number 🙂

Now I need to find solution, how to put variables instead dates in expression. To be able specify the date in expression. Should I create "Data Island"?

[Gysbert_Wassenaar]

You don't need a data island calendar in this case. First create a new variable, for example vDate. Then add a Calendar object and connect it to a variable vDate. You can then use the Calendar object to select a date and vDate will be set to this value. You can then use vDate in your expression.

[#5]

Thank you very much for this elegant solution 🙂