June 1, 2009 at 5:50 am
Hi there,
I am a total novice when it comes to T-SQL (I can do very simple select, from, where and join statements)
I have the following query that I have inherited -
SELECTdbo.[CORE_CO-PLACE].[place-ref],
dbo.[CORE_CO-PLACE].[address1],
dbo.[CORE_CO-PLACE].[address2],
dbo.[CORE_CO-PLACE].[address3],
(SELECT DISTINCT LSH.[SUB-STATUS]
FROM [IH_IH-LOCATION-STAT-HIST] LSH
WHERE LSH.[PLACE-REF] = dbo.[IH_IH-LOCATION-STAT-HIST].[PLACE-REF]
AND LSH.[SUB-STAT-START] between '30/04/2009' and '01/06/2009'
AND LSH.[SUB-STATUS] LIKE ('V_')
AND LSH.[ACTUAL-DATE] IS NOT NULL) AS STATUS,
dbo.[IH_IH-LOCATION].[location-type],
dbo.[IH_RE-TENANCY].[tncy-end],
dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS],
dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET],
DATEDIFF(day, dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS],
dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET]) as Days_with_Voids,
DATEDIFF(day, dbo.[IH_RE-TENANCY].[tncy-end],
dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET]) as Total_Days_to_RTL,
SUM(DATEDIFF(day, dbo.[IH_RE-TENANCY].[tncy-end], dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET])) -
(DATEDIFF(day, dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS], dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET])) as Days_Void_outside_VoidsTeam,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'ARENT'
AND PC.[END-DATE] IS NULL) AS ARENT,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'CABLE'
AND PC.[END-DATE] IS NULL) AS CABLE ,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'CTAX'
AND PC.[END-DATE] IS NULL) AS CTAX,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'FOYSP'
AND PC.[END-DATE] IS NULL) AS FOYSP,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'HEAT'
AND PC.[END-DATE] IS NULL) AS HEAT,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'MHSTV'
AND PC.[END-DATE] IS NULL) AS MHSTV,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'PROHTG'
AND PC.[END-DATE] IS NULL) AS PROHTG,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'SEWER'
AND PC.[END-DATE] IS NULL) AS SEWER,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'SHELSP'
AND PC.[END-DATE] IS NULL) AS SHELSP,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'WATER'
AND PC.[END-DATE] IS NULL) AS WATER
FROMdbo.[CORE_CO-PLACE]
FULL JOIN dbo.[IH_IH-LOCATION]
ON dbo.[CORE_CO-PLACE].[PLACE-REF] = dbo.[IH_IH-LOCATION].[PLACE-REF]
FULL JOIN dbo.[IH_RE-TNCY-PLACE]
ON dbo.[IH_IH-LOCATION].[PLACE-REF] = dbo.[IH_RE-TNCY-PLACE].[PLACE-REF]
FULL JOIN dbo.[IH_RE-TENANCY]
ON dbo.[IH_RE-TNCY-PLACE].[TNCY-SYS-REF] = dbo.[IH_RE-TENANCY].[TNCY-SYS-REF]
FULL JOIN dbo.[IH_IH-LOCATION-STAT-HIST]
ON dbo.[IH_IH-LOCATION-STAT-HIST].[PLACE-REF] = dbo.[IH_IH-LOCATION].[PLACE-REF]
FULL JOIN dbo.[IH_IH-PLACE-CHG]
ON dbo.[CORE_CO-PLACE].[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
WHEREdbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS] IS NOT NULL
ANDdbo.[IH_IH-PLACE-CHG].[END-DATE] IS NULL
ANDdbo.[IH_IH-PLACE-CHG].[ACCOUNT-CODE] = 'ARENT'
ANDdbo.[IH_RE-TENANCY].[term-reason] <> 'TYPE'
AND CONVERT(DATETIME, dbo.[IH_RE-TENANCY].[tncy-end]) between '30/04/2009' and '01/06/2009'
andCONVERT(DATETIME, dbo.[IH_RE-TENANCY].[tncy-end]) < GETDATE()
ANDCONVERT(DATETIME, dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET]) < GETDATE()
AND CONVERT(DATETIME, dbo.[IH_RE-TENANCY].[tncy-end], 103) < CONVERT(DATETIME, dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET],103)
GROUP BY dbo.[CORE_CO-PLACE].[place-ref],
dbo.[CORE_CO-PLACE].[address1],
dbo.[CORE_CO-PLACE].[address2],
dbo.[CORE_CO-PLACE].[address3],
dbo.[IH_IH-LOCATION].[SUB-STATUS],
dbo.[IH_IH-LOCATION].[location-type],
dbo.[IH_RE-TENANCY].[tncy-end],
dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS],
dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET],
dbo.[IH_IH-PLACE-CHG].[NET-AMT],
dbo.[IH_IH-LOCATION].[LOCATION-STS],
dbo.[IH_IH-LOCATION-STAT-HIST].[CREATED-DATE],
dbo.[IH_IH-LOCATION-STAT-HIST].[PLACE-REF],
dbo.[IH_IH-PLACE-CHG].[END-DATE],
dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
However, when I run this I get the following error message -
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Any help would be appreciated as I have no idea where to start on this.
Thanks
June 1, 2009 at 6:08 am
The reason you would get an error like that is going to be down to one of the many correlated-subqueries you have in the select part of your query.
because each row returned in your result set can only have 1 value per column per row, you need to make sure that your correlated-subqueries only return one value for each row...
does this make sense?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 1, 2009 at 6:13 am
~The query is also a bit confusing I see you have GROUP BY clause but I can't find any aggreation functions in your select.
So I'm guessing this is what you are trying to do, based on the fact that your subquery is join back the a table of the same name in your from clause.
Here is an example of one of the returned rows:
--Example of your column
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'HEAT'
AND PC.[END-DATE] IS NULL) AS HEAT,
--possible change
SUM(CASE WHEN [IH_IH-PLACE-CHG].[ACCOUNT-CODE] = 'HEAT' THEN CONVERT(MONEY,ISNULL(PC.[NET-AMT],0.00)) ELSE 0.00 END) as [Heat]
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 1, 2009 at 6:14 am
Sorry for my ignorrance but do you mean queries such as -
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'CTAX'
AND PC.[END-DATE] IS NULL) AS CTAX,
June 1, 2009 at 6:16 am
If I were you I would isolate the sub query that's causing the problem, so...
Comment out all sub queries and run.
Introduce them back in one at a time.
As soon as one errors put a TOP 1 and an ORDER BY in the subquery and see if it works
If it does, then the problem is that you have two results for the record in question - perhaps they have 2 sub status or something.
To see if this is the case look at the table with the sub status and the ID - see the IDs that appear twice etc...
Not the most technical of anwsers, but that's what I'd do!
June 1, 2009 at 6:17 am
exactly correct, see my example above try doing that will all the subqueries you basically just change the column alias from heat to say sewer etc and change the case statement to check for sewer instead of hear, etc etc
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 1, 2009 at 6:19 am
Thanks guys - I will tackle the problem as suggested.
Appreciate the swift response. I'll let you know how I get on.
June 1, 2009 at 6:25 am
out of interest. am I correct in saying that this query should report the total financials per grouped by each place?, if so then you going to need to do some summing 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 1, 2009 at 6:33 am
Sorry me again...
Two other things I noticed in your query, and which out know the actual relationships and meanings of the tables/fields I might be wrong but...
I have noticed that you are doing a lot converting in your where clause, if those fields are already dates then don't convert them to dates.
Secondly, I notice that in your main where clause you have a filter:
ANDdbo.[IH_IH-PLACE-CHG].[ACCOUNT-CODE] = 'ARENT'
However in your subqueries you are joining back to the same table as above by filtering the field by different values...
this might throw your results.... but like I said I'm not sure what you trying to return but just something for you to double check...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 1, 2009 at 7:26 am
Still not working I'm afraid -
I have changed the Sub Queries from
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'WATER'
AND PC.[END-DATE] IS NULL) AS WATER
to
SUM(CASE WHEN [IH_IH-PLACE-CHG].[ACCOUNT-CODE] = 'WATER' THEN CONVERT(MONEY,ISNULL([IH_IH-PLACE-CHG].[NET-AMT],0.00)) ELSE 0.00 END) as [Water]
and am still getting the same error message
SELECTdbo.[CORE_CO-PLACE].[place-ref],
dbo.[CORE_CO-PLACE].[address1],
dbo.[CORE_CO-PLACE].[address2],
dbo.[CORE_CO-PLACE].[address3],
(SELECT DISTINCT LSH.[SUB-STATUS]
FROM [IH_IH-LOCATION-STAT-HIST] LSH
WHERE LSH.[PLACE-REF] = dbo.[IH_IH-LOCATION-STAT-HIST].[PLACE-REF]
AND LSH.[SUB-STAT-START] between '30/04/2009' and '01/06/2009'
AND LSH.[SUB-STATUS] LIKE ('V_')
AND LSH.[ACTUAL-DATE] IS NOT NULL) AS STATUS,
dbo.[IH_IH-LOCATION].[location-type],
dbo.[IH_RE-TENANCY].[tncy-end],
dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS],
dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET],
DATEDIFF(day, dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS],
dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET]) as Days_with_Voids,
DATEDIFF(day, dbo.[IH_RE-TENANCY].[tncy-end],
dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET]) as Total_Days_to_RTL,
SUM(DATEDIFF(day, dbo.[IH_RE-TENANCY].[tncy-end], dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET])) -
(DATEDIFF(day, dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS], dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET])) as Days_Void_outside_VoidsTeam
--SUM(CASE WHEN [IH_IH-PLACE-CHG].[ACCOUNT-CODE] = 'ARENT' THEN CONVERT(MONEY,ISNULL([IH_IH-PLACE-CHG].[NET-AMT],0.00)) ELSE 0.00 END) as [Arent],
--SUM(CASE WHEN [IH_IH-PLACE-CHG].[ACCOUNT-CODE] = 'CABLE' THEN CONVERT(MONEY,ISNULL([IH_IH-PLACE-CHG].[NET-AMT],0.00)) ELSE 0.00 END) as [Cable],
--SUM(CASE WHEN [IH_IH-PLACE-CHG].[ACCOUNT-CODE] = 'CTAX' THEN CONVERT(MONEY,ISNULL([IH_IH-PLACE-CHG].[NET-AMT],0.00)) ELSE 0.00 END) as [Ctax],
--SUM(CASE WHEN [IH_IH-PLACE-CHG].[ACCOUNT-CODE] = 'FOYER' THEN CONVERT(MONEY,ISNULL([IH_IH-PLACE-CHG].[NET-AMT],0.00)) ELSE 0.00 END) as [Foyer],
--SUM(CASE WHEN [IH_IH-PLACE-CHG].[ACCOUNT-CODE] = 'HEAT' THEN CONVERT(MONEY,ISNULL([IH_IH-PLACE-CHG].[NET-AMT],0.00)) ELSE 0.00 END) as [Heat],
--SUM(CASE WHEN [IH_IH-PLACE-CHG].[ACCOUNT-CODE] = 'MHSTV' THEN CONVERT(MONEY,ISNULL([IH_IH-PLACE-CHG].[NET-AMT],0.00)) ELSE 0.00 END) as [mhstv],
--SUM(CASE WHEN [IH_IH-PLACE-CHG].[ACCOUNT-CODE] = 'PROHTG' THEN CONVERT(MONEY,ISNULL([IH_IH-PLACE-CHG].[NET-AMT],0.00)) ELSE 0.00 END) as [Prohtg],
--SUM(CASE WHEN [IH_IH-PLACE-CHG].[ACCOUNT-CODE] = 'SEWER' THEN CONVERT(MONEY,ISNULL([IH_IH-PLACE-CHG].[NET-AMT],0.00)) ELSE 0.00 END) as [Sewer],
--SUM(CASE WHEN [IH_IH-PLACE-CHG].[ACCOUNT-CODE] = 'SHELSP' THEN CONVERT(MONEY,ISNULL([IH_IH-PLACE-CHG].[NET-AMT],0.00)) ELSE 0.00 END) as [Shelsp],
--SUM(CASE WHEN [IH_IH-PLACE-CHG].[ACCOUNT-CODE] = 'WATER' THEN CONVERT(MONEY,ISNULL([IH_IH-PLACE-CHG].[NET-AMT],0.00)) ELSE 0.00 END) as [Water]
FROMdbo.[CORE_CO-PLACE]
FULL JOIN dbo.[IH_IH-LOCATION]
ON dbo.[CORE_CO-PLACE].[PLACE-REF] = dbo.[IH_IH-LOCATION].[PLACE-REF]
FULL JOIN dbo.[IH_RE-TNCY-PLACE]
ON dbo.[IH_IH-LOCATION].[PLACE-REF] = dbo.[IH_RE-TNCY-PLACE].[PLACE-REF]
FULL JOIN dbo.[IH_RE-TENANCY]
ON dbo.[IH_RE-TNCY-PLACE].[TNCY-SYS-REF] = dbo.[IH_RE-TENANCY].[TNCY-SYS-REF]
FULL JOIN dbo.[IH_IH-LOCATION-STAT-HIST]
ON dbo.[IH_IH-LOCATION-STAT-HIST].[PLACE-REF] = dbo.[IH_IH-LOCATION].[PLACE-REF]
FULL JOIN dbo.[IH_IH-PLACE-CHG]
ON dbo.[CORE_CO-PLACE].[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
WHEREdbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS] IS NOT NULL
ANDdbo.[IH_IH-PLACE-CHG].[END-DATE] IS NULL
--ANDdbo.[IH_IH-PLACE-CHG].[ACCOUNT-CODE] = 'ARENT'
ANDdbo.[IH_RE-TENANCY].[term-reason] 'TYPE'
AND CONVERT(DATETIME, dbo.[IH_RE-TENANCY].[tncy-end]) between '30/04/2009' and '01/06/2009'
andCONVERT(DATETIME, dbo.[IH_RE-TENANCY].[tncy-end]) < GETDATE()
ANDCONVERT(DATETIME, dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET]) < GETDATE()
AND CONVERT(DATETIME, dbo.[IH_RE-TENANCY].[tncy-end], 103) < CONVERT(DATETIME, dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET],103)
GROUP BY dbo.[CORE_CO-PLACE].[place-ref],
dbo.[CORE_CO-PLACE].[address1],
dbo.[CORE_CO-PLACE].[address2],
dbo.[CORE_CO-PLACE].[address3],
dbo.[IH_IH-LOCATION].[SUB-STATUS],
dbo.[IH_IH-LOCATION].[location-type],
dbo.[IH_RE-TENANCY].[tncy-end],
dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS],
dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET],
dbo.[IH_IH-PLACE-CHG].[NET-AMT],
dbo.[IH_IH-LOCATION].[LOCATION-STS],
dbo.[IH_IH-LOCATION-STAT-HIST].[CREATED-DATE],
dbo.[IH_IH-LOCATION-STAT-HIST].[PLACE-REF],
dbo.[IH_IH-PLACE-CHG].[END-DATE],
dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
The above is now how the code reads.
It is meant to pull back the tenancies that have ended within a months range and then calculate the void turnaround or the property.
All the subqueries are in order to pull back all the different charges that are held against that property. As these are held in the same database table, I presume that is why the author has choosen to use subqueries?
June 2, 2009 at 9:09 am
I have received the same message when trying to update a table with a trigger. You might check to see if there is an active trigger.
June 2, 2009 at 9:09 am
Hi Guys I have managed to work out what the issue was - it was with the followinf sub query -
(SELECT DISTINCT LSH.[SUB-STATUS]
FROM [IH_IH-LOCATION-STAT-HIST] LSH
WHERE LSH.[PLACE-REF] = dbo.[IH_IH-LOCATION-STAT-HIST].[PLACE-REF]
AND LSH.[SUB-STAT-START] between '30/04/2009' and '01/06/2009'
AND LSH.[SUB-STATUS] LIKE ('V_')
AND LSH.[ACTUAL-DATE] IS NOT NULL) AS STATUS,
As this property had more than one VOID STATUS (begining with V) it was stopping the whole query from running.
In the Interim before I can correct the data on the database my query is as follows and it pulls back data -
SELECTdbo.[CORE_CO-PLACE].[place-ref],
dbo.[CORE_CO-PLACE].[address1],
dbo.[CORE_CO-PLACE].[address2],
dbo.[CORE_CO-PLACE].[address3],
(SELECT DISTINCT LSH.[SUB-STATUS]
FROM [IH_IH-LOCATION-STAT-HIST] LSH
WHERE LSH.[PLACE-REF] = dbo.[IH_IH-LOCATION-STAT-HIST].[PLACE-REF]
AND LSH.[SUB-STAT-START] between '30/04/2009' and '01/06/2009'
AND LSH.[SUB-STATUS] LIKE ('V_')
AND LSH.[ACTUAL-DATE] IS NOT NULL) AS STATUS,
dbo.[IH_IH-LOCATION].[location-type],
dbo.[IH_RE-TENANCY].[tncy-end],
dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS],
dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET],
DATEDIFF(day, dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS],
dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET]) as Days_with_Voids,
DATEDIFF(day, dbo.[IH_RE-TENANCY].[tncy-end],
dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET]) as Total_Days_to_RTL,
SUM(DATEDIFF(day, dbo.[IH_RE-TENANCY].[tncy-end], dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET])) -
(DATEDIFF(day, dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS], dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET])) as Days_Void_outside_VoidsTeam,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'ARENT'
AND PC.[END-DATE] IS NULL) AS ARENT,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'CABLE'
AND PC.[END-DATE] IS NULL) AS CABLE ,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'CTAX'
AND PC.[END-DATE] IS NULL) AS CTAX,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'FOYSP'
AND PC.[END-DATE] IS NULL) AS FOYSP,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'HEAT'
AND PC.[END-DATE] IS NULL) AS HEAT,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'MHSTV'
AND PC.[END-DATE] IS NULL) AS MHSTV,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'PROHTG'
AND PC.[END-DATE] IS NULL) AS PROHTG,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'SEWER'
AND PC.[END-DATE] IS NULL) AS SEWER,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'SHELSP'
AND PC.[END-DATE] IS NULL) AS SHELSP,
(SELECT CASE WHEN CONVERT(MONEY, PC.[NET-AMT]) IS NULL THEN 0.00
ELSE CONVERT(MONEY, PC.[NET-AMT]) END
FROM dbo.[IH_IH-PLACE-CHG] PC
WHERE PC.[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
AND PC.[ACCOUNT-CODE] = 'WATER'
AND PC.[END-DATE] IS NULL) AS WATER
FROMdbo.[CORE_CO-PLACE]
FULL JOIN dbo.[IH_IH-LOCATION]
ON dbo.[CORE_CO-PLACE].[PLACE-REF] = dbo.[IH_IH-LOCATION].[PLACE-REF]
FULL JOIN dbo.[IH_RE-TNCY-PLACE]
ON dbo.[IH_IH-LOCATION].[PLACE-REF] = dbo.[IH_RE-TNCY-PLACE].[PLACE-REF]
FULL JOIN dbo.[IH_RE-TENANCY]
ON dbo.[IH_RE-TNCY-PLACE].[TNCY-SYS-REF] = dbo.[IH_RE-TENANCY].[TNCY-SYS-REF]
FULL JOIN dbo.[IH_IH-LOCATION-STAT-HIST]
ON dbo.[IH_IH-LOCATION-STAT-HIST].[PLACE-REF] = dbo.[IH_IH-LOCATION].[PLACE-REF]
FULL JOIN dbo.[IH_IH-PLACE-CHG]
ON dbo.[CORE_CO-PLACE].[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
WHEREdbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS] IS NOT NULL
ANDdbo.[IH_IH-PLACE-CHG].[END-DATE] IS NULL
ANDdbo.[IH_IH-PLACE-CHG].[ACCOUNT-CODE] = 'ARENT'
ANDdbo.[IH_RE-TENANCY].[term-reason] 'TYPE'
AND dbo.[IH_IH-LOCATION].[PLACE-REF] '2115150200'
AND CONVERT(DATETIME, dbo.[IH_RE-TENANCY].[tncy-end]) between '30/04/2009' and '01/06/2009'
andCONVERT(DATETIME, dbo.[IH_RE-TENANCY].[tncy-end]) < GETDATE()
ANDCONVERT(DATETIME, dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET]) < GETDATE()
AND CONVERT(DATETIME, dbo.[IH_RE-TENANCY].[tncy-end], 103) < CONVERT(DATETIME, dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET],103)
GROUP BY dbo.[CORE_CO-PLACE].[place-ref],
dbo.[CORE_CO-PLACE].[address1],
dbo.[CORE_CO-PLACE].[address2],
dbo.[CORE_CO-PLACE].[address3],
dbo.[IH_IH-LOCATION].[SUB-STATUS],
dbo.[IH_IH-LOCATION].[location-type],
dbo.[IH_RE-TENANCY].[tncy-end],
dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS],
dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET],
dbo.[IH_IH-PLACE-CHG].[NET-AMT],
dbo.[IH_IH-LOCATION].[LOCATION-STS],
dbo.[IH_IH-LOCATION-STAT-HIST].[CREATED-DATE],
dbo.[IH_IH-LOCATION-STAT-HIST].[PLACE-REF],
dbo.[IH_IH-PLACE-CHG].[END-DATE],
dbo.[IH_IH-PLACE-CHG].[PLACE-REF]
The issue I am now having is when I try to import directly into Excel, via a datasource I get the following error -
The conversion of a char data type to a datetime data type resulted in an out or-range datatime value.
Any ideas? Is this to do with the CONVERT function? The date fields are held as dates in the database already? How would I go about writing the code without the Convert function?
Thanks
June 2, 2009 at 9:14 am
Sounds like one of the charater dates isn't a real date, like 2009-02-30. You will need check the data, perhaps running a query on the problem table looking for records where ISDATE(yourdatecolumn) = 0.
June 3, 2009 at 3:43 am
Thanks for your reply Lyn.
I have checked the dats and they all look correct to me.
I have run the query in Query Analyser and the results are pulled back successfully. When I copy and paste the results directly into Excel it works fine.
However when trying to attached Excel to the view in order for an end user to refresh the data as of when they require - that is when I get the error -
The conversion of a char data type to a datetime data type resulted in an out or-range datatime value.
Any other ideas?
Thanks for your help
June 4, 2009 at 9:11 am
Have you done what I suggested above? From your post it is difficult to determine.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply