Trying to use DateAdd() in SubQuery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Trying to use DateAdd() in SubQuery

Author

Topic

bconner
Starting Member

48 Posts

Posted - 2010-07-30 : 15:29:03

I have a Sub-Query that I want to pull the Chg from seven days ago related to the date in the Main-Query. There seems to be an issue with the DateAdd function I am using but I can't figure out what it is......

I keep getting the following error:

Msg 4145, Level 15, State 1, Line 87
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

Below is the code I am using:


SELECT
       A.[DIVISION]
      ,[PT_ID]
      ,[INVOICE]
      ,[PATIENT_NAME]
      ,[FSC]
      ,[TXN_CODE]
      ,[LINE_ITEM]
      ,[TXN_DESCRIPTION]
      ,[CPT]
      ,[QTY]
      ,[DATE]
      ,A.[CHG]
      ,B.CHG
      ,[PAY]
      ,[ADJ]
      ,[CREDIT]
      ,[DEBIT]
      ,[DEDAMT]
      ,[COINS]
      ,[APPRAMT]
      ,[REJ]
      ,[PRIFSC]
      ,[SECFSC]
      ,[LOC]
      ,[SVC_DATE]
      ,[REF_PHY]
      ,[FSC#]
      ,[MOD1]
      ,[MOD2]
      ,[MOD3]
      ,[REF_LOC]
      ,[DX_CODE]
      ,[ACCESSION]
      ,[MRN]
      ,[GRP]
      ,[PROVIDER]
      ,[CHARGE_TYPE]

FROM

(SELECT [DIVISION]
      ,[PT_ID]
      ,[INVOICE]
      ,[PATIENT_NAME]
      ,[FSC]
      ,[TXN_CODE]
      ,[LINE_ITEM]
      ,[TXN_DESCRIPTION]
      ,[CPT]
      ,[QTY]
      ,[DATE]
      ,[CHG]
      ,[PAY]
      ,[ADJ]
      ,[CREDIT]
      ,[DEBIT]
      ,[DEDAMT]
      ,[COINS]
      ,[APPRAMT]
      ,[REJ]
      ,[PRIFSC]
      ,[SECFSC]
      ,[LOC]
      ,[SVC_DATE]
      ,[REF_PHY]
      ,[FSC#]
      ,[MOD1]
      ,[MOD2]
      ,[MOD3]
      ,[REF_LOC]
      ,[DX_CODE]
      ,[ACCESSION]
      ,[MRN]
      ,[GRP]
      ,[PROVIDER]
      ,[CHARGE_TYPE]
  FROM [TEST].[dbo].[GRP 9 TRANS MAY 10_TEST]
  WHERE txn_code = '99') A
  LEFT JOIN 
     (SELECT
       [DIVISION]
       ,chg 
       FROM [TEST].[dbo].[GRP 9 TRANS MAY 10_TEST]
       WHERE (txn_code ='99' and DATEADD(DAY,-7,convert(datetime,[DATE],101)))
       GROUP BY
       [DIVISION]
       ,chg) B ON A.DIVISION = B.DIVISION

Brian

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-30 : 15:39:35

You are not comparing the result of the DATEADD against anything...
Is this what you need?

SELECT
       A.[DIVISION]
      ,[PT_ID]
      ,[INVOICE]
      ,[PATIENT_NAME]
      ,[FSC]
      ,[TXN_CODE]
      ,[LINE_ITEM]
      ,[TXN_DESCRIPTION]
      ,[CPT]
      ,[QTY]
      ,[DATE]
      ,A.[CHG]
      ,B.CHG
      ,[PAY]
      ,[ADJ]
      ,[CREDIT]
      ,[DEBIT]
      ,[DEDAMT]
      ,[COINS]
      ,[APPRAMT]
      ,[REJ]
      ,[PRIFSC]
      ,[SECFSC]
      ,[LOC]
      ,[SVC_DATE]
      ,[REF_PHY]
      ,[FSC#]
      ,[MOD1]
      ,[MOD2]
      ,[MOD3]
      ,[REF_LOC]
      ,[DX_CODE]
      ,[ACCESSION]
      ,[MRN]
      ,[GRP]
      ,[PROVIDER]
      ,[CHARGE_TYPE]

FROM

(SELECT [DIVISION]
      ,[PT_ID]
      ,[INVOICE]
      ,[PATIENT_NAME]
      ,[FSC]
      ,[TXN_CODE]
      ,[LINE_ITEM]
      ,[TXN_DESCRIPTION]
      ,[CPT]
      ,[QTY]
      ,[DATE]
      ,[CHG]
      ,[PAY]
      ,[ADJ]
      ,[CREDIT]
      ,[DEBIT]
      ,[DEDAMT]
      ,[COINS]
      ,[APPRAMT]
      ,[REJ]
      ,[PRIFSC]
      ,[SECFSC]
      ,[LOC]
      ,[SVC_DATE]
      ,[REF_PHY]
      ,[FSC#]
      ,[MOD1]
      ,[MOD2]
      ,[MOD3]
      ,[REF_LOC]
      ,[DX_CODE]
      ,[ACCESSION]
      ,[MRN]
      ,[GRP]
      ,[PROVIDER]
      ,[CHARGE_TYPE]
  FROM [TEST].[dbo].[GRP 9 TRANS MAY 10_TEST]
  WHERE txn_code = '99') A
  LEFT JOIN 
     (SELECT
       [DIVISION]
       ,chg 
       FROM [TEST].[dbo].[GRP 9 TRANS MAY 10_TEST]
       WHERE txn_code ='99'
       GROUP BY
       [DIVISION]
       ,chg) B ON A.DIVISION = B.DIVISION
WHERE CONVERT(DATETIME,A.DATE) >= dateadd(d,-7,dateadd(d, datediff(d, 0, getdate()), 0))

bconner
Starting Member

48 Posts

Posted - 2010-07-30 : 15:45:50

vijayisonly,
I am comparing it to a field named [DATE] in the Subquery table. I am not pulling the [DATE] field into the subquery but I am referencing it in the SubQuery Where clause.

Brian

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-30 : 15:48:51

quote:
I am comparing it to a field named [DATE] in the Subquery table

What is the "it" you are referring to? This

DATEADD(DAY,-7,convert(datetime,[DATE],101))

will give you just the difference in days ...which field do you want to compare it against?

Why don't you explain your whole requirement...maybe there is a better solution.

bconner
Starting Member

48 Posts

Posted - 2010-07-30 : 16:29:02

I have a table that stores daily transactions by Location. Every Day each location must log into the Database and enter their totals. I want to pull reports that sum up the Transactions Trended by Day by Location. Here is the tricky part if a Location did not enter a Transaction for a given day (Null) I want the query to grab the last three entries on the same day from the prior 3 weeks and calculate an average as a place holder for the missing day.

Example:
The site enter the following transactions

Dallas,6/5/2010,$45,monday
Dallas,6/6/2010,$35,tuesday
Dallas,6/7/2010,$50,wednesday
Dallas,6/8/2010,$25,thursday
Dallas,6/9/2010,$56,friday
Dallas,6/12/2010,$45,monday
Dallas,6/13/2010,$35,tuesday
Dallas,6/14/2010,$25,wedensday
Dallas,6/15/2010,$25,thursday
Dallas,6/16/2010,$56,friday
Dallas,6/19/2010,$45,monday
Dallas,6/20/2010,$35,tuesday
Dallas,6/21/2010,$37.5,wedensday day was missing so query needs to take prior 2 or 3 wedensdays and calculated avg
Dallas,6/22/2010,$25,thursday
Dallas,6/23/2010,$56,friday

Brian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources