SQL Custom Query Help

[Steven_Crosby]

First time posting and I'm no SQL guru 🙂

Trying to setup a SQL Query to allow customer to view a list of documents based on a set of pre-defined keyword values (account numbers) but be able to enter in a specific date at the time the query is ran. The alternative to being able to select a custom date is just to get around the problem described below.

In Web Client when we use the query we have a Date selection box to enter in a specific date. If we leave the date blank the query runs fine using previous business date. But if we fill in a specific date, the query bombs out with "Error processing request."

In practice the customer would run the query and always be looking for the previous business date so as-is the query would be fine on tue-friday. The problem comes on Monday morning when the query needs to search under friday, not sunday. So, if we can either get the custom date selection to work that would be good, or if there is a way to code the query so it would just work mon-fri that would work too. Again the key is that when they run the query on Monday it needs to search under Friday's date instead of Sunday.

From: 

itemdata

Where:

itemtypenum=29 and itemnum in
(select itemnum from hsi.keygroupdata103 where kg51 in (

111111,

222222,

333333,

444444 

) and itemdate>=LEFT(getdate()-1,11))

Order:

itemname

[Steven_Crosby]

ok, I think I might have figured out getting the date keyword field to work.

So, in Diagnostics Console I captured this come through if I fill in the date keyword. I'm guessing if I remove the "and itemdate>=LEFT(getdate()-1,11))" and then just fill in the date keyword each time it should complete without error.

and itemdate>=LEFT(getdate()-1,11)) AND ((itd.itemdate BETWEEN { ts '2013-04-23 00:00:00.000' } AND { ts '2013-04-23 23:59:59.998' }))

I made the change to the query but I'm guessing I need to recycle the app pool and I can't do that during production hours so I'll have to test tomorrow.

[Ansley_Ingram]

Hi S-Guy -

With a SQL Based Custom Query you can't have an input but you should be able to include some additional logic in the WHERE clause to figure out what day of the week it is to determine how many days to go back. I'm assuming you're using SQL Server because of the GETDATE() function so the sample below is written for T-SQL.

SELECT *FROM hsi.itemdataWHERE itemtypenum = 29 AND	(DATEPART(DW,GETDATE()) = 1 -- Today is Sunday		AND itemdate>= CAST(GETDATE()-2 AS DATE)) -- Last Business Day 2 days ago	OR	(DATEPART(DW,GETDATE()) = 2 -- Today is Monday		AND itemdate>= CAST(GETDATE()-3 AS DATE)) -- Last Business Day 3 days ago	OR	(DATEPART(DW,GETDATE()) IN (3, 4, 5, 6, 7) -- Today is Tues-Saturday		AND itemdate>= CAST(GETDATE()-1 AS DATE)) -- Last Business Day 1 day agoORDER BY itemdate

There are some size limitations to the fields in SQL Custom Queries so you may need to remove the comments to get it to fit in the Configuration dialog. You should be able to test this in the Thick Client with just a re-launch.

Hope that helps.

Ansley

[Edwin_D_Souza]

Hello

I am trying to write a sql query for custom query but why does it keep displaying columns from itemdata. I want to use itemdatapage to get the page count. Can someone please help?

[Tonya_Ruff]

What exactly are you trying to display to the user via the query (I see you mention page counts) and do you use Oracle or SQL Server? If you used the query given in the answer above as a base query you are seeing fields from itemdata probably because of the "select *" which select all fields in the table.