SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tadhg88
Yak Posting Veteran

Ireland
62 Posts

Posted - 06/25/2013 :  07:20:42  Show Profile  Reply with Quote
Hi All,
I have been struggling with this query for the last couple of days, just wondering if anyone could point me back on track, any help or advice would really be appreciated.

I have a table containing requested customer Feedback data linked directly to the Product we would like to receive feedback on, with these Products then linked to the City the take place in. The third table should contain the customer feedback details if they completed one, which is linked to the Feedback table.

I'm trying to create a query to display a record for all requested feedback(Feedback) and whether or not feedback has been completed(TripAdvisorEmail) for a Product within the same City as the City this Feedback Product is linked to.


http://www.freeimagehosting.net/newuploads/e6w7s.jpg
Thanks again

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 06/25/2013 :  07:30:18  Show Profile  Reply with Quote
-- You can tweet the below query for your requirement...
SELECT FeedbackColumn, City_id , product_id-- list of columns
FROM d_Feedback df
JOIN d_feedback_tripAdvisorEmail tae ON df.feedback_id = tae.feedback_id
JOIN d_product dp dp.Product_id = df.Product_id

If this is not the exact solution, elaborate your requirement


--
Chandu
Go to Top of Page

tadhg88
Yak Posting Veteran

Ireland
62 Posts

Posted - 06/25/2013 :  07:44:05  Show Profile  Reply with Quote
thanks for taking the time to reply.
I was really hoping to have an additional column that has maybe a 1 if the completed feedback is in the same city as any previously completed feedback from this customer or a 0 if not.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 06/25/2013 :  07:51:15  Show Profile  Reply with Quote
quote:
Originally posted by tadhg88

thanks for taking the time to reply.
I was really hoping to have an additional column that has maybe a 1 if the completed feedback is in the same city as any previously completed feedback from this customer or a 0 if not.


Can you explain with sample input and expected output?
without sample data/complete table structures how can we get previous completed feedback ?
Do you have any DateTime Column ?

--
Chandu

Edited by - bandi on 06/25/2013 08:31:14
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/25/2013 :  12:11:38  Show Profile  Reply with Quote
FYI, Here are some link that can help you prepare your question with DDL, DML and Expected output. THat will help help us to help you better.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

tadhg88
Yak Posting Veteran

Ireland
62 Posts

Posted - 06/26/2013 :  05:50:29  Show Profile  Reply with Quote
Thanks for you're replies I really appreciate it, i have uploaded a pic of the results i would like to see here
http://www.freeimagehosting.net/newuploads/qhsnx.jpg

So if you look at the highlighted user tw, the last column displays whether or not the user has completed the feedback for this feedback_id(Completed), if they have not completed it(None) and i guess the difficult one where they have completed feedback within the same city, thanks again

---------------------------------------------------
-- Script
---------------------------------------------------
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#d_Feedback','U') IS NOT NULL
DROP TABLE #d_Feedback

CREATE TABLE #d_Feedback
(
[Feedback_id] [int] NOT NULL,
[User_email] NVARCHAR(15) NULL,
[Product_id] [int] NULL,
[booking_id] [int] NULL
)

IF OBJECT_ID('TempDB..#d_Product','U') IS NOT NULL
DROP TABLE #d_Product

CREATE TABLE #d_Product
(
[Product_id] [int] NOT NULL,
[city_id] [int] NULL
)

IF OBJECT_ID('TempDB..#d_Feedback_TA','U') IS NOT NULL
DROP TABLE #d_Feedback_TA

CREATE TABLE #d_Feedback_TA
(
[Feedback_id] [int] NOT NULL
)

--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY


INSERT INTO #d_Feedback
(feedback_id, User_email, Product_id, booking_id)
SELECT '39715','tw@dr.com','348','621747' UNION ALL
SELECT '39714','tw@dr.com','413','621746' UNION ALL
SELECT '39713','sm@dr.com','431','529787' UNION ALL
SELECT '39712','sm@dr.com','431','529787' UNION ALL
SELECT '39711','tw@dr.com','435','621665' UNION ALL
SELECT '39710','rf@ab.com','290','608919' UNION ALL
SELECT '39709','rf@ab.com','291','608920' UNION ALL
SELECT '39708','rf@ab.com','413','608911' UNION ALL
SELECT '39707','rf@ab.com','413','608912' UNION ALL
SELECT '39706','js@ao.com','324','596255'


INSERT INTO #d_Product
(product_id, city_id)
SELECT '290','1' UNION ALL
SELECT '291','1' UNION ALL
SELECT '324','1' UNION ALL
SELECT '348','1' UNION ALL
SELECT '364','1' UNION ALL
SELECT '413','3' UNION ALL
SELECT '431','1' UNION ALL
SELECT '435','1' UNION ALL
SELECT '456','1'

INSERT INTO #d_feedback_TA
SELECT 39715 UNION ALL
SELECT 39712 UNION ALL
SELECT 39707

SELECT df.Feedback_id, User_email, df.product_id, City_id, tae.Feedback_id Completed -- list of columns
FROM #d_Feedback df
LEFT JOIN #d_product dp ON dp.Product_id = df.Product_id
LEFT JOIN #d_feedback_TA tae ON df.feedback_id = tae.feedback_id
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/26/2013 :  05:58:34  Show Profile  Reply with Quote
Sorry but code posted doesnt have any columns by which you can group the rows together to check if feedback is completed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

tadhg88
Yak Posting Veteran

Ireland
62 Posts

Posted - 06/26/2013 :  08:39:53  Show Profile  Reply with Quote
the feedback has been completed if there is a record in the #d_Feedback_TA table
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/26/2013 :  09:18:48  Show Profile  Reply with Quote

SELECT user_email,
CASE WHEN MIN(city_id) OVER (PARTITION BY user_Email) = MAX(city_id) OVER(PARTITION BY user_Email)
THEN 'Feedback in same city' ELSE 'Feedback in different city' END
FROM #d_Feedback f
INNER JOIN #d_Product p
ON p.Product_id = f.Product_id
INNER JOIN #d_feedback_TA ta
ON ta.feedback_id = f.feedback_id


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

tadhg88
Yak Posting Veteran

Ireland
62 Posts

Posted - 06/26/2013 :  11:06:49  Show Profile  Reply with Quote
thanks its closer to what i need with a left join on TA table but it still doesnt return none in this column if there is no feedback completed, any ideas??


SELECT user_email,
CASE WHEN MIN(city_id) OVER (PARTITION BY user_Email) = MAX(city_id) OVER(PARTITION BY user_Email)
THEN 'Feedback in same city' ELSE 'Feedback in different city' END
FROM #d_Feedback f
INNER JOIN #d_Product p ON p.Product_id = f.Product_id
LEFT JOIN #d_feedback_TA ta ON ta.feedback_id = f.feedback_id
Go to Top of Page

tadhg88
Yak Posting Veteran

Ireland
62 Posts

Posted - 06/26/2013 :  11:17:17  Show Profile  Reply with Quote
thanks but that just gives the same results unfortunately
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/26/2013 :  11:19:16  Show Profile  Reply with Quote

SELECT user_email,
CASE WHEN ta.feedback_id IS NULL 
THEN 'None'
WHEN MIN(city_id) OVER (PARTITION BY user_Email) = MAX(city_id) OVER(PARTITION BY user_Email)
THEN 'Feedback in same city' 
ELSE 'Feedback in different city' 
END
FROM #d_Feedback f
LEFT JOIN #d_Product p ON p.Product_id = f.Product_id
LEFT JOIN #d_feedback_TA ta ON ta.feedback_id = f.feedback_id


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/26/2013 :  11:24:22  Show Profile  Reply with Quote
Try this:

SELECT p.product_id, f.Feedback_id, user_email,
CASE WHEN MIN(city_id) OVER (PARTITION BY user_Email) = MAX(city_id) OVER(PARTITION BY user_Email)
THEN 'Feedback in same city' ELSE 'Feedback in different city' END
FROM #d_Product p
LEFT JOIN #d_Feedback f ON p.Product_id = f.Product_id
LEFT JOIN #d_feedback_TA ta ON ta.feedback_id = f.feedback_id

Go to Top of Page

tadhg88
Yak Posting Veteran

Ireland
62 Posts

Posted - 06/26/2013 :  12:04:29  Show Profile  Reply with Quote
thanks this is very close to what i need but as you can see here
http://www.freeimagehosting.net/newuploads/qhsnx.jpg
if you focus on the user 'tw@dr.com' you can see the 3 different results this user can see in the last column
"Completed" if feedback_id in #d_feedback_TA - 39715
"SameCity" if feedback completed for same user within the same city - 39711
"None" if no feedback is completed, no record in #d_feedback_TA - 39714

SELECT f.feedback_id, city_id, user_email,
CASE WHEN ta.feedback_id IS NULL
THEN 'None'
WHEN MIN(city_id) OVER (PARTITION BY user_Email) = MAX(city_id) OVER(PARTITION BY user_Email)
THEN 'Feedback in same city'
ELSE 'Feedback in different city'
END
FROM #d_Feedback f
LEFT JOIN #d_Product p ON p.Product_id = f.Product_id
LEFT JOIN #d_feedback_TA ta ON ta.feedback_id = f.feedback_id

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/26/2013 :  13:00:42  Show Profile  Reply with Quote
Sounds like this then


SELECT f.feedback_id, city_id, user_email,
CASE 
WHEN ta.feedback_id IS NOT NULL
THEN 'Completed'  
WHEN p.city_id = MAX(CASE WHEN ta.feedbackid IS NOT NULL THEN city_id END) OVER (PARTITION BY User_Email) THEN 'SameCity'
WHEN ta.feedback_id IS NULL 
THEN 'None'
END
FROM #d_Feedback f
LEFT JOIN #d_Product p ON p.Product_id = f.Product_id
LEFT JOIN #d_feedback_TA ta ON ta.feedback_id = f.feedback_id


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/26/2013 :  13:30:56  Show Profile  Reply with Quote
Try this:



;WITH 
CTE1 AS
(SELECT city_id, user_email 
FROM #d_Feedback f
LEFT JOIN #d_Product p ON p.Product_id = f.Product_id
LEFT JOIN #d_feedback_TA ta ON ta.feedback_id = f.feedback_id
WHERE ta.feedback_id IS NOT NULL)
SELECT f.feedback_id, p.city_id,f.user_email, 
	CASE
	WHEN ta.feedback_id IS NOT NULL
	THEN 'Complete' 
	WHEN ta.feedback_id IS NULL  and p.city_id = C1.city_id
	THEN 'Feedback in same city'
	WHEN ta.feedback_id IS NULL and C1.city_id IS NOT NULL and  
							p.city_id <> C1.city_id
	THEN 'Feedback in different city'
	ELSE 'None' 
	END as Comment
FROM #d_Feedback f
LEFT JOIN #d_Product p ON p.Product_id = f.Product_id
LEFT JOIN #d_feedback_TA ta ON ta.feedback_id = f.feedback_id
LEFT JOIN CTE1 C1 on C1.User_email = f.User_email
ORDER BY f.User_email


Go to Top of Page

tadhg88
Yak Posting Veteran

Ireland
62 Posts

Posted - 06/27/2013 :  09:40:59  Show Profile  Reply with Quote
Spot on this is exactly what i was looking for, thanks a million for taking the time to look into this for me I really appreciate it, I have never used the the OVER or PARTITION keywords before, I would imagine there's thousands of tutorials online but is there any you would recommend to get a good understanding of how they work and can be correctly used??

thanks to you too MuMu88 i appreciate the effort and it was very close to what i was looking for thank you!!


quote:
Originally posted by visakh16

Sounds like this then


SELECT f.feedback_id, city_id, user_email,
CASE 
WHEN ta.feedback_id IS NOT NULL
THEN 'Completed'  
WHEN p.city_id = MAX(CASE WHEN ta.feedbackid IS NOT NULL THEN city_id END) OVER (PARTITION BY User_Email) THEN 'SameCity'
WHEN ta.feedback_id IS NULL 
THEN 'None'
END
FROM #d_Feedback f
LEFT JOIN #d_Product p ON p.Product_id = f.Product_id
LEFT JOIN #d_feedback_TA ta ON ta.feedback_id = f.feedback_id


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/27/2013 :  09:49:10  Show Profile  Reply with Quote
quote:
Originally posted by tadhg88

Spot on this is exactly what i was looking for, thanks a million for taking the time to look into this for me I really appreciate it, I have never used the the OVER or PARTITION keywords before, I would imagine there's thousands of tutorials online but is there any you would recommend to get a good understanding of how they work and can be correctly used??

thanks to you too MuMu88 i appreciate the effort and it was very close to what i was looking for thank you!!


quote:
Originally posted by visakh16

Sounds like this then


SELECT f.feedback_id, city_id, user_email,
CASE 
WHEN ta.feedback_id IS NOT NULL
THEN 'Completed'  
WHEN p.city_id = MAX(CASE WHEN ta.feedbackid IS NOT NULL THEN city_id END) OVER (PARTITION BY User_Email) THEN 'SameCity'
WHEN ta.feedback_id IS NULL 
THEN 'None'
END
FROM #d_Feedback f
LEFT JOIN #d_Product p ON p.Product_id = f.Product_id
LEFT JOIN #d_feedback_TA ta ON ta.feedback_id = f.feedback_id


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs





Welcome

I've learned these techniques mostly by following posts here and i use BOL/MSDN for reference

http://msdn.microsoft.com/en-us/library/ms189461.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000