March 20, 2016 at 2:33 pm
I would like to ask if anyone would be able to help out with this. I would be more than happy to upload the data (need some help with that). But what I am needing is the following.
1. I need to know which people (the first column in the dataset) attending the golf event in 2013 out to current.
2. Then find out if that person returned the next year
3. Also if that person returned to the event after skipping a couple of years.
March 20, 2016 at 2:42 pm
suggest you read this link and post back with suggested details
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 21, 2016 at 10:03 am
I have included the file now.
March 21, 2016 at 10:22 am
Stephen crocker (3/21/2016)
I have included the file now.
pk...based on your data that you have now supplied....what are your expected results, based on this data?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 21, 2016 at 10:27 am
What i'm needing on the data is to find the records that their first time at the Golf Event found in the GOLF<year> column, then if they came back the next year or not.
so for example
1. I need to know who the people were that their first Golf event was in GOLF2013 then did those people comeback in 14, 15, 16 is so which ones.
2. Then I need to know the same thing for Golf2014 15 and 2016 is the easy one just seeing who has one previously attended event (the second column) and 2016 is a one.
I'm having a hard time trying to figure out the 15 to 16 so if they came 16 did they come in 14.
I'm trying to manipulate the data for analysis in Tableau but want to figure this out in SQL First or do I already have what I need to put it into tableau and analyze the data their.
I hope this makes since.
March 21, 2016 at 10:28 am
having now looked at your sample data, I am none the wiser.
you have 39 rows...all of whom it "appears" attended in year 2013 (assume that "1" = attendance).....but none of them have attended in any other years (all other columns = 0)
can I ask if this "table" is actually how your data is being stored or is this a "pivot view" of some other recordsource?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 21, 2016 at 10:35 am
OOps try this one as the data source. Sorry I was messing with the query earlier.
March 21, 2016 at 10:43 am
Stephen crocker (3/21/2016)
OOps try this one as the data source. Sorry I was messing with the query earlier.
ok...will take a look later
can I repeat my earlier question please
is this "table" actually how your data is being stored or is this a "pivot view" of some other recordsource?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 21, 2016 at 10:44 am
It's a pivoted view i'm querying against.
March 21, 2016 at 10:47 am
maybe easier if we worked on the recordsource rather than the pivot me thinks.
can you supply sample data for the base recordset?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 21, 2016 at 10:48 am
sure, I think I can pull that together, might be later today
March 22, 2016 at 7:56 am
heres a few thoughts
hopefully code comments explain
EDIT -- error in code posted...please new post below
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 22, 2016 at 10:12 am
oop error in previous code
try this
USE tempdb
GO
-- create a temp table that unpivots the data you previously provided as "mytable"
-- only create records where there was actual attendance ie column = 1
-- have added additional column to replace itr_cst_key -- only for readabilty and checking
-- this isnt part of the solution only to provide data that MAY represent your original recordsource
-- have also added code to rename the columns to describe the "year"......you could do this with Dynamic SQL if you prefer
SELECT * , DENSE_RANK() OVER (ORDER BY itr_cst_key) rn INTO #golfvisits FROM (
SELECT itr_cst_key, 2010 as attenddate FROM mytable WHERE (GOLF2010 = 1) UNION ALL
SELECT itr_cst_key, 2011 as attenddate FROM mytable WHERE (GOLF2011 = 1) UNION ALL
SELECT itr_cst_key, 2012 as attenddate FROM mytable WHERE (GOLF2012 = 1) UNION ALL
SELECT itr_cst_key, 2013 as attenddate FROM mytable WHERE (GOLF2013 = 1) UNION ALL
SELECT itr_cst_key, 2014 as attenddate FROM mytable WHERE (GOLF2014 = 1) UNION ALL
SELECT itr_cst_key, 2015 as attenddate FROM mytable WHERE (GOLF2015 = 1) UNION ALL
SELECT itr_cst_key, 2016 as attenddate FROM mytable WHERE (GOLF2016 = 1)) x
-------------------------------------------------------------------------
-- using above as the recordsource
DECLARE @curyear AS INT= 2013; -- alter for other start years
DECLARE @year1 AS INT= @curyear + 1;
DECLARE @year2 AS INT= @curyear + 2;
DECLARE @year3 AS INT= @curyear + 3;
-------------------------------------------------------------------------
WITH cte as (
SELECT rn
FROM #golfvisits
GROUP BY rn
HAVING(MIN(attenddate) = @curyear)
)
SELECT cte.rn,
COUNT(g.attenddate) AS totalvisits,
SUM(CASE WHEN g.attenddate = @year1 THEN 1 ELSE 0 END) AS y1,
SUM(CASE WHEN g.attenddate = @year2 THEN 1 ELSE 0 END) AS y2,
SUM(CASE WHEN g.attenddate = @year3 THEN 1 ELSE 0 END) AS y3
INTO #visits -- comment out if rename columns is not required
FROM cte
LEFT OUTER JOIN (SELECT rn, attenddate
FROM #golfvisits
WHERE (attenddate > @curyear)) g ON cte.rn = g.rn
GROUP BY cte.rn;
-------------------------------------------------------------------------
-- following only required if you want "YEAR" as column name in results
EXEC tempdb..sp_rename '#visits.y1', @year1, 'COLUMN'
EXEC tempdb..sp_rename '#visits.y2', @year2, 'COLUMN'
EXEC tempdb..sp_rename '#visits.y3', @year3, 'COLUMN'
SELECT * FROM #visits;
-------------------------------------------------------------------------
DROP TABLE #golfvisits;
DROP TABLE #visits;
edit changed to include those who only attended once
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 23, 2016 at 8:56 pm
Hey, this looks challenging for my level and I am trying to follow the whole thread but I am not clear on exact request.
So, let me first confirm if this is what you are after..
Rule 1:
Pull, list of people that went to Golf event in "given year" (say for example, 2012) and that was their first Golf event
Rule 2:
From Rule 1 set, then pull list of people that returned the next year (in above case, they went in 2012 & in 2013)
Rule 3:
From Rule 2 set, then pull list of people returned after "skipping" couple of years
(in above case, Rule 2 people did not go in 2014 & in 2015 but return in 2016)
So, in short if I am taking 2012 as my base year then you want to pull list of all people who went in 2012 for the first time and also went in 2013 and did not go in 2014 & 2015 and returned in 2016
Does that sound right? Or Am I missing anything? Please clarify..
All that being said, I think if you can change your data to unpivot then your job might be easier..
itr_cst_keyYEARGolfEventFlagTotalYrsGolfReg
00339AD4-1E7F-43C9-A592-69D886AAB77F199602
00339AD4-1E7F-43C9-A592-69D886AAB77F199702
00339AD4-1E7F-43C9-A592-69D886AAB77F199802
Here is the code for that if you are interested..
--drop table mytable_Unpivot
;with cte as
(
SELECT itr_cst_key ,TotalYrsGolfReg, [YEAR] ,GolfEventFlag
FROM [dbo].mytable
UNPIVOT
(
GolfEventFlag FOR [Year] IN (
[GOLF1996]
,[GOLF1997]
,[GOLF1998]
,[GOLF1999]
,[GOLF2000]
,[GOLF2001]
,[GOLF2002]
,[GOLF2003]
,[GOLF2004]
,[GOLF2005]
,[GOLF2006]
,[GOLF2007]
,[GOLF2008]
,[GOLF2009]
,[GOLF2010]
,[GOLF2011]
,[GOLF2012]
,[GOLF2013]
,[GOLF2014]
,[GOLF2015]
,[GOLF2016]
) ) AS P
)
select itr_cst_key, replace(Year,'GOLF','') YEAR, GolfEventFlag,TotalYrsGolfReg
into mytable_Unpivot
from cte
select *
from mytable_Unpivot
Order by itr_cst_key, Year
Thanks.
March 23, 2016 at 11:32 pm
It can be done with a combination of UnPivot and Crosstab query.
;WITH cte
AS (SELECT itr_cst_key,
totalyrsgolfreg,
Replace([year], 'GOLF', '')Year,
GolfEventFlag
FROM [dbo].mytable
UNPIVOT ( golfeventflag
FOR [Year] IN ( [GOLF2013],
[GOLF2014],
[GOLF2015],
[GOLF2016] ) ) AS T)
SELECT T.itr_cst_key,
T.GolfEventFlag,
T.Year,
Max(CASE WHEN T.year = 2013 THEN T.golfeventflag
ELSE 0 END) ReturnIn2013,
Max(CASE WHEN T.year = 2014 THEN T.golfeventflag
ELSE 0 END) ReturnIn2014,
Max(CASE WHEN T.year = 2015 THEN T.golfeventflag
ELSE 0 END) ReturnIn2015,
Max(CASE WHEN T.year = 2016 THEN T.golfeventflag
ELSE 0 END) ReturnIn2016
FROM cte T
GROUP BY T.itr_cst_key,
T.year,
T.golfeventflag
ORDER BY T.itr_cst_key,T.year
Have a look at the attachment with this reply.So basically with this query you can filter the itr_cst_key and the year and at the same time check if the user did attend in the previous/next year.
For example:If you filter itr_cst_key='147BB8A7-BB00-44AD-B6ED-9C06D4053E76' and year=2013 the query will return if the user attended in year 2013,2014,2015 and 2016
If your table has additional years you can add this part in the cross tab query based on the no of years you may want to add and add the years to the UnPivot clause.
Max(CASE WHEN T.year = 'NewYear' THEN T.golfeventflag
ELSE 0 END) ReturnInNewYear
EDIT: Removed a redundant APPLY clause
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply