October 28, 2008 at 9:00 am
Thanks for the table structure and sample data.
SETUP
DECLARE @PropDet TABLE
(
PropId INT PRIMARY KEY,
UserId UNIQUEIDENTIFIER,
PropName NVARCHAR(50),
Resort NVARCHAR(50),
Col5 NVARCHAR(50),
Col6 NVARCHAR(50),
Col7 NVARCHAR(50),
Col8 NVARCHAR(50),
Col9 NVARCHAR(50),
Col10 NVARCHAR(50),
Col11 NVARCHAR(50),
Col12 NVARCHAR(50)
)
INSERT INTO @PropDet
(PropId, UserId, PropName, Resort)
SELECT '6','6EC16A5D-1257-49FA-AA1B-8AC745AF41E5','Villa Marky','Courchevel 1850' UNION ALL
SELECT '9','E3EEAE25-622E-4C6B-B782-FDA576C9B10B','Villa Mummypenny Elly ','Meribel' UNION ALL
SELECT '17','6EC16A5D-1257-49FA-AA1B-8AC745AF41E5','Chateaux La La','Meribel'
DECLARE @Rates TABLE
(
RowId INT PRIMARY KEY,
PropId INT,
col1 NVARCHAR(50)
)
INSERT INTO @Rates
(RowId, PropId, Col1)
SELECT '4','6','March' UNION ALL
SELECT '5','6','April' UNION ALL
SELECT '6','6','January' UNION ALL
SELECT '9','17','January' UNION ALL
SELECT '10','17','February' UNION ALL
SELECT '11','17','March' UNION ALL
SELECT '12','17','April' UNION ALL
SELECT '18','17','May' UNION ALL
SELECT '19','17','June' UNION ALL
SELECT '20','17','July' UNION ALL
SELECT '21','17','August'
This is Jack's query slightly modified and with the last piece added to it. (And all his formatting screwed up)
[font="Courier New"]SELECT
-- P.Col1,
P.PropID,
P.UserId,
--T.Col3,
--T.Col4,
DT.count_sum,
R.RateCount
FROM
@PropDet P JOIN
(
SELECT
--Col1,
PropID,
--COUNT(Col3)+COUNT(Col4)+
COUNT(Col5)+COUNT(Col6)+COUNT(Col7)+
COUNT(Col8)+COUNT(Col9)+COUNT(Col10)+COUNT(Col11)+COUNT(Col12) AS count_sum
FROM
@PropDet
GROUP BY
-- Col1
PropID
) AS DT ON
P.PropID= DT.PropID
-- Added This line for your second query:
LEFT JOIN (SELECT PropID, COUNT(*) RateCount FROM @Rates GROUP BY PropID) R ON P.PropID = R.PropID
--WHERE
--(T.UserId = @userid)
ORDER BY
P.PropID, P.UserID
[/font]
October 28, 2008 at 11:07 am
Well I've got to hand it to you....
I'm very grateful for your effort with this. It would have taken me forever to figure this out.
I hope you're always this helpful!
Once again thanks.
October 28, 2008 at 11:25 am
Hi Seth,
Just one more thing i've just come across:
It looks like if the number of rows retrieved in RateCount is 0 it returns NULL. Is it possible to have the query return "0" if this is the case?
I tried this but it didn't like the syntax:
SELECT
P.PropertyId,
P.UserId,
P.PropertyName,
P.Resort,
P.GoLiveStatus,
P.RenewalDate,
DT.count_ai,
ISNULL (R.RateCount,'0') as R.RateCount
FROM
PropertyDetails2 P JOIN
......
Can you help with this?
Thanks
October 28, 2008 at 11:31 am
ISNULL (R.RateCount,'0') as R.RateCount should be ISNULL (R.RateCount, 0) as RateCount
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 28, 2008 at 12:35 pm
It looks like if the number of rows retrieved in RateCount is 0 it returns NULL. Is it possible to have the query return "0" if this is the case?
I had an ISNULL in there, then removed it again before I posted it. Guess I should have left it! 😎
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply