June 11, 2015 at 10:15 am
I have a below table:
--===== If the test table already exists, drop it
IF OBJECT_ID('tempdb..#complaints') IS NOt NULL
DROP TABLe #complaints
--===== Create the test table with
create table #complaints([Fiscal Year] float,
[Fiscal Week] float,
[Fiscal Month] float,
[Complaint #] float)
-===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #complaints ON
INSERT INTO #complaints
([Fiscal Year], [Fiscal Week], [Fiscal Month], [Complaint #])
SELECT 2015, 14, 5, 12 UNION ALL
SELECT 2015, 15, 5, 11 UNION ALL
SELECT 2014, 14, 5, 18 UNION ALL
SELECT 2014, 15, 5, 12 UNION ALL
SELECT 2014, 16, 5, 10 UNION ALL
SELECT 2013, 16, 5, 29 UNION ALL
SELECT 2014, 17, 5, 9 UNION ALL
SELECT 2013, 17, 5, 20 UNION ALL
SELECT 2013, 18, 6, 22 UNION ALL
SELECT 2014, 18, 6, 25 UNION ALL)
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF
******************************************************
Query to be made: Below table needs to be created:
create table #results( [Fiscal Week] float,
[Fiscal Month] float,
[C1] float,
[C2] float)
Where C1: [Complaint #] from table #complaints for current year (2015) if it exist for that year and a particular month of current year else it will be of previous year (i.e. 2014)
C2: If data for current year and a particular month exist it will be #complaints for previous year (2014) else it will be of previous to previous year (2013).
Example:
Result table:
weekC1c2
141218
151112
161029
172920
182522
Thanks for help.
June 11, 2015 at 10:59 am
Rough and dirty, and won't work unless there are at least two years for each week.
Convert to an outer join if you need to take that into consideration.
SELECT a.[Fiscal Week], a.[Complaint #], b.[Complaint #]
FROM [#complaints] a, #complaints b
WHERE a.[Fiscal Week] = b.[Fiscal Week]
AND a.[Fiscal Year] = (SELECT MAX(ax.[Fiscal Year]) FROM #complaints ax WHERE ax.[Fiscal Week] = a.[Fiscal Week])
AND b.[Fiscal Year] = a.[Fiscal Year] -1
Regards
Wayne
June 11, 2015 at 11:07 am
This gives the expected results with the data supplied, but you haven't said what the expected behavior should be when 2012 and 2014 have data, but 2013 and 2015 don't. Do you want the most recent previous data or do you want to display a NULL. This will show the most recent data available, based on the fact that when 2015 is missing, you're showing 2014 and 2013 instead of showing a NULL for 2015 and the appropriate value for 2014.
;
WITH complaints_ordered AS
(
SELECT
Fiscal_Week,
Complaint_Num AS c1,
LAG(Complaint_Num) OVER(PARTITION BY Fiscal_Week ORDER BY Fiscal_Year) AS c2,
ROW_NUMBER() OVER(PARTITION BY Fiscal_Week ORDER BY Fiscal_Year DESC) AS rn
FROM #complaints
)
SELECT Fiscal_Week, C1, C2
FROM complaints_ordered
WHERE rn = 1
ORDER BY Fiscal_Week
Also, IMNSHO you should almost never use spaces in column or table names. I only ever use spaces in column names when I am creating an ad hoc query to send to someone who is not a techie.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 11, 2015 at 11:38 am
I would like to display NULL.
June 11, 2015 at 12:06 pm
drew.allen (6/11/2015)
Also, IMNSHO you should almost never use spaces in column or table names.
+1000.
Is this homework? I'm asking because you have things in your OP that don't make sense. For example, you're turning identity insert on and off, but you don't have an identity column. You also have your year, week and month as the float data type, but they should be integers.
June 11, 2015 at 12:24 pm
This is also one of several threads asking the same question. Just haven't taken the time to locate them all.
June 11, 2015 at 1:47 pm
sqlinterset (6/11/2015)
I would like to display NULL.
Then you will need to add a CASE statement like so
;
WITH complaints_ordered AS
(
SELECT
Fiscal_Week,
Complaint_Num AS c1,
CASE WHEN LAG(Fiscal_Year) OVER(PARTITION BY Fiscal_Week ORDER BY Fiscal_Year) = Fiscal_Year - 1
THEN LAG(Complaint_Num) OVER(PARTITION BY Fiscal_Week ORDER BY Fiscal_Year) END AS c2,
ROW_NUMBER() OVER(PARTITION BY Fiscal_Week ORDER BY Fiscal_Year DESC) AS rn
FROM #complaints
)
SELECT Fiscal_Week, C1, C2
FROM complaints_ordered
WHERE rn = 1
ORDER BY Fiscal_Week
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 18, 2015 at 6:44 am
When i am using this query its working fine in SSMS, but when i am creating excel pivot table to fetch SQL server data through this query then it gives me below error:
excel warning changed database context to
any help?
June 18, 2015 at 7:02 am
It's not an error. It's a message informing you that the database you're using has been changed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2015 at 7:03 am
I have a vague memory of something like this, but I don't remember the exact solution. Therefore, I will use the force. 😛
1. Check your connection string to make sure you're connecting to the right database in your "Initial Catalog" parameter.
2. If that doesn't do the trick, try using three-part naming conventions in your table names.
June 18, 2015 at 7:12 am
This is the sequence of errors when running the same query through excel pivot option
1)Changed database context to XXXXX
2) Problems obtaining data
3) A pivot table using connection 'YYYY' has failed to refresh.
June 18, 2015 at 7:16 am
sqlinterset (6/18/2015)
This is the sequence of errors when running the same query through excel pivot option1)Changed database context to XXXXX
2) Problems obtaining data
3) A pivot table using connection 'YYYY' has failed to refresh.
Can you post the exact set of SQL statements you are using in the Excel pivot table connection?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 18, 2015 at 7:45 am
Tables which already exist in database i am using 3 name table convention but there are two tables which are created in database itself, where i can't use 3 name convention. Like, complaints_ordered below is local table and can't be used with three name.
;
WITH complaints_ordered AS
(
SELECT
Fiscal_Week,
Complaint_Num AS c1,
LAG(Complaint_Num) OVER(PARTITION BY Fiscal_Week ORDER BY Fiscal_Year) AS c2,
ROW_NUMBER() OVER(PARTITION BY Fiscal_Week ORDER BY Fiscal_Year DESC) AS rn
FROM #complaints
)
SELECT Fiscal_Week, C1, C2
FROM complaints_ordered
WHERE rn = 1
ORDER BY Fiscal_Week
June 18, 2015 at 8:05 am
sqlinterset (6/18/2015)
Tables which already exist in database i am using 3 name table convention but there are two tables which are created in database itself, where i can't use 3 name convention. Like, complaints_ordered below is local table and can't be used with three name.;
WITH complaints_ordered AS
(
SELECT
Fiscal_Week,
Complaint_Num AS c1,
LAG(Complaint_Num) OVER(PARTITION BY Fiscal_Week ORDER BY Fiscal_Year) AS c2,
ROW_NUMBER() OVER(PARTITION BY Fiscal_Week ORDER BY Fiscal_Year DESC) AS rn
FROM #complaints
)
SELECT Fiscal_Week, C1, C2
FROM complaints_ordered
WHERE rn = 1
ORDER BY Fiscal_Week
This doesn't look like an entire set of statements from the Command Text box of an Excel connection.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 18, 2015 at 8:14 am
Thank You all. problem got solved. I removed the database name that i was using like "Use DDDD". Once i removed it, data was fetched in excel Thanks.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply