Is there a way to show multiple metrics in 1 SQL pivot operator. Basically, I have the Table1 and want the desired results is the Table2 format.
Table1
ACCOUNTS YEARREVENUEMARGIN
ACCOUNT1 2012100 50
ACCOUNT1 2013104 52
ACCOUNT1 2014108 54
[code]....
I am trying to pivot table DYNAMICALLY but couldn't get the desired result .
Here is the code to create a table
create table Report
(
deck char(3),
Jib_in float,
rev int,
rev_insight int,
jib_out float,
[Code] .....
Code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(deck)
FROM (SELECT p.deck FROM dbo.report AS p
GROUP BY p.deck) AS x;
[Code] ....
I need all the columns to be pivoted and show on the pivoted table. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!!
Hello. I have a little issue of trying to find the most efficient way do doing something.
Consider a table
mat age qty
aaa 5 100
aaa 7 300
aaa 12 400
aaa 3 700
What I want to get out is
Mat 0-4 5-9 10-14 15-19
aaa 700 400 400 0
I know I could do it with 4 views all joined but I hope I can use a CASE somehow ?
Thanks for you help
Gerry
I have two queries that give me the total sales amount for the current year, and the last year.
SELECT SUM([Sales (LCY)])
FROM [$Cust_ Ledger Entry] cle
LEFT OUTER JOIN dw.dim.FiscalDate fd
ON fd.CalendarDate = cle.[Posting Date]
WHERE [Customer No_] = '10135'
AND fd.CalendarYear = '2013'
[Code] ....
I would like to learn how to be able to make this a single query and end up with two columns and their summed up totals. Like it shows on the attached image.
This is my query without the columns I need:
SELECT
c.CustomerNumber
,c.Name
,c.ChainName
,c.PaymentTermsCode
,cle.CreditLimit AS 'CreditLimit'
,SUM(cle.Amount) AS 'Amount'
[Code] ....
I have a table of data that has a format similar to the following:
EventID | Event Date
--------------------
1 | 1/1/2014
2 | 2/8/2014
3 | 10/1/2014
4 | 2/5/2014
5 | 4/1/2014
6 | 9/1/2014
What I am trying to do is create a DAX formula to rank each event in the order that it happened for the year. So I want to end up with something like this. This way I can compare the events year over year as the events don't happen on any regular time schedule.
Event Date | Year | Rank
------------------------
1/1/2014 | 2014 | 1
2/8/2014 | 2014 | 2
10/1/2014 | 2014 | 3
2/5/2015 | 2015 | 1
4/1/2015 | 2015 | 2
9/1/2015 | 2015 | 3
I have tried to do this by creating a formula that will give me the day number of the year:
Day of Year =(YEARFRAC(CONCATENATE("Jan 1 ", YEAR([Event Date])),[Event Date])*360)+1
Then using rankX on this table, but I cant seem to get the proper result. Perhaps I am not understanding the use of rankX or going about this the right way.
=RANKX(FILTER(Event,EARLIER(Event[Event Year])=Event[Event Year]),Event[Day of Year])
or
=RANKX(All(Event[Event Year]),[Day of Year],,1,Dense)
Our business get orders through the week with the weekends (Fri & Sat) orders being higher than weekdays. Im wanting to graph this years data with last years and possible the years before but to compare days in such a way that the all the weekdays line up. so comparing 2015 week 1 with 2014 week 1 but with 03/01/2015 (Sat) lining up with 04/01/2014 (Sat) etc.
I'm looking for alternatives to adding or removing days from the dates to solve this issue, i have a date dimension table for the past 5 years that i can use to compare calendar week 201401 with calendar week 201501 but I am finding it a bit inflexable.
Attached is a file that generates a sample dataset from which I want to forecast a value for column 'Parcel Count 2014' from tomorrow (30/10/2014) till the end of December (31/12/2014). The forecast should be based on values from column 'Parcel Count 2013' for the same Month.
View 9 Replies View RelatedI have a table which can be downloaded from the link below. The table contains property Market Rent and period the new rent is applicable. I need to generate a report with parameter ( year and month) so when the user inputs year and month the associated market rent amount for that month is listed.
For example if the 1st market rent update was done in June 2014 ( $300) and the 2nd in Dec 2014 $(350), the property market rent from June to Nov should be $300 and from Dec 2014 till the next rent update $350. So if user inputs year 2014 and month August, the amount is $300 and if the user enters the year 2015 and month March amount is $350
[URL] ....
Below is the table with sample data
DECLARE @table TABLE ( PropCode INT ,PropStartDate DATE ,PropEndDate char(10) ,PropRentStartDate DATE ,PropRentEndDate DATE ,MarketRent INT)
INSERT INTO @table (PropCode,PropStartDate, PropEndDate,PropRentStartDate,PropRentEndDate, MarketRent) VALUES
(2718, '2013-01-30', 'NULL', '2012-11-29', '2013-07-21', 289.20) ,(2718, '2013-01-30', 'NULL', '2013-07-22', '2013-11-24', 289.20) ,(2718, '2013-01-30', 'NULL', '2013-11-25', '2014-06-14', 289.20) ,(2718, '2013-01-30', 'NULL', '2014-06-15', '2014-11-30', 299.18) ,(2718, '2013-01-30', 'NULL', '2014-12-01', '2015-01-02', 299.18) ,(2718, '2013-01-30', 'NULL', '2015-01-03', '2050-01-01', 310.00) ,(3901, '2014-05-27', 'NULL', '2014-06-09', '2014-11-30', 400.00) ,(3901, '2014-05-27', 'NULL', '2014-12-01', '2050-01-01', 400.00) ,(3960, '2014-10-31', 'NULL', '2014-11-05', '2016-11-05', 470.00)
Select * from @table
I have three tables: EMP (ID, NAME), EMPDATE (ID, STARTDATE, ENDDATE), YEAR(YEAR)
I would like to get the most recent date within a given year per each EMP? For example, EMPID 1 can be enrolled in many programs, each program has start end dates. I need to list the most recent date an employee was enrolled (max date between START AND END DATE which ever is most recent enrollment) for a given year. For example, for 2014 his/her most recent enrollment should be 10/23/2014 for year 2014 and 2013-10-24 for year 2013.
SELECT ID, EMP.NAME, DTE.StartDate, DTE.ENDDATE, year
FROM
EMP_DATE DTE join
EMP_INFO EMP on EMP.ID = DTE.ID join
YEAR YR on YR.YEAR = YEAR(DTE.STARTDATE)
DATA SAMPLE:
EMP
ID NAME
1 JOHN
EMP_INFO
ID STARTDATE ENDDATE
12013-10-24 2014-03-11
12014-06-13 2014-03-11
12014-06-15 2014-03-11
12014-09-08 2014-03-11
12014-09-12 2014-03-11
12014-09-14 2014-03-11
12014-01-13 2014-05-17
12014-05-14 2014-06-09
12014-06-10 2014-06-16
12014-08-31 2014-09-04
12014-09-05 2014-09-06
12014-09-07 2014-10-23
YEAR
Year
2012
2013
2014
2015
What's the best way to calculate a customers age and value by month and year?
I need to be able to calculate customer value by month and year, and then to calculate their age at each month in time. I've found a way of grouping sales by month and year that includes age for a particular contact like this:
select fh.contact_number
, concat(year(fh.transaction_date), '-', month(fh.transaction_date)) as transaction_month_year
, cast(fh.transaction_date as date) as transaction_date
, sum(fh.amount) as ttl_amount_in_month
[Code] .....
It seems to work, but any better way to achieve this?
I need to create a table which holds date information for our financial year.
I have all the dates between now and 2045 and the start of the week and the end of the week. What I also have is the first sunday of the previous week in the spreadsheet too.
Please see below attachment
What I need to autofill once I import these three dates into a database is the week and the month.
The difficulty surrounding the month is that, we start a new month on the FIRST Sunday of the month.
So dates 07/04/14 to 04/05/2014 would be month 1.
Month 2 would begin on 05/05/2014 as it is the day after the first Sunday of the month, and so on....Month 5 would start on the 04/08/14.
Need to script something that would automatically calculate the week and month for me on the basis on above, if I have the start date, end date and 1st sunday already in a table?
This is what I have for a query but i would like to take the year off. is there a way i can just have the month and day without the year attached?
View 3 Replies View RelatedI am trying to select records based on a year.
DECLARE @Date
SET @Date = 2012
DECLARE @Year int
SET @Year = (SELECT DATEPART(yyyy,@Date))
SELECT @Year AS Year
--SELECT *
-- FROM [Orders].[dbo].[Orders] od
-- WHERE .Orderl_Date < @Date;
----WHERE DATEPART(yyyy,@Date)= @Year
----WHERE DATEPART(yyyy,od.Order_Date)= @ArchiveYear
DECLARE @Date
SET @Date = 2012
DECLARE @Year int
SET @Year = (SELECT DATEPART(yyyy,@Date))
SELECT @Year AS Year
--SELECT *
-- FROM [Orders].[dbo].[Orders] od
-- WHERE .Orderl_Date < @Date;
----WHERE DATEPART(yyyy,@Date)= @Year
----WHERE DATEPART(yyyy,od.Order_Date)= @ArchiveYear
I have a table with Month , Year as varchar. I realized it was a big mistake. Since its getting too complicated to query this way.
Year Month Productname
2013 11 ACB
2013 11 CDE
I would now like to add another column called date and store these Year Month as a date to my existing table
Year Month ProductName Date
2013 11 ACB 2013-11-01
2013 11 CDE 2013-11-01
Is there a way I can do it for all the columns of the existing table ??
I need to build a report that compares a count on a certain day of the week by month by year by stacks. That is,for first Monday in October 2012 against first Monday in October 2013 for stack DM1 against first Monday in October 2014 stack DM1, same for second Monday, first Tuesday, second Tuesday, ect. Attached is a sample dataset and what I want to achieve.
View 9 Replies View RelatedI am trying to pivot data based on columns value in year column... but results are not showing up correctly. I want to see all columns after pivot.I want to Pivot based on year shown in the data but it can be dynamic as year can go for last 3 years
I am also using an inner join as i have two amount columns in my code and i want to show both amount columns for all displayed year.I am able to pivot but I need in output all the columns like this Id,MainDate, Year1,Year2,Year3(if any), AMT1 for YR1, AMT2 for Yr1, , AMT1 for YR2, AMT2 for Yr2, AMT1 for YR3, AMT2 for Yr3,
Here is some data:
-- CREATE TABLE [dbo].[TEMP](
--[FileType] [varchar](19) NOT NULL,
--[dType] [char](2) NOT NULL,
--[dVersion] [char](2) NOT NULL,
--[Id] [char](25) NOT NULL,
--[MainDate] [char](40) NULL,
[code]....
Can we pass dynamic values while pivoting?
Here is example
Declare @a date, @b date
set @a='2015-09-08 22:19:29.330'
set @b='2015-09-17 22:19:29.330'
create table #DateTemp(Full_Date_Text_YYYY_MM_DD datetime,Full_Date date)
insert into #DateTemp(Full_Date_Text_YYYY_MM_DD,Full_Date)
select '2015-09-09 00:00:00.000','2015-09-09'
[Code] ......
I like to create an SQL view to divide amount 300,000 between 12 month starting from Month July 2014 to June 2015 as shown below
Amount Month Year
25,000 July 2014
25,000 August 2014
25,000 September 2014
25,000 October 2014
25,000 November 2014
25,000 December 2014
25,000 January 2015
25,000 February 2015
.
.
.
.
25,000 June 2015
I have a simple 2 column dataset representing Date of session and Client Name (Please download workbook from here - [URL] .... The Financial Year runs from April 1 to March 31. I want to determine the number of sessions conducted in chosen financial year - 2. So if I select FY 2015-16 in a slicer, then, I want view the number of session conducted client wise in FY 2013-14. This is the formula calculated field formula I have written but when I drag this in the Pivot Table, I get an error message. In the calculated field window, when I click on Check for errors, then there are no errors at all.
=CALCULATE([Sessions conducted],DATESBETWEEN(calendar[Dates],EDATE(FIRSTDATE(calendar[Dates]),-24),EDATE(FIRSTDATE(calendar[Dates]),-12)-1))
I cannot identify my error.
I am trying to pivot some data as you would normally however I am trying to also group the pivot into three sub column groups too.
Basically the scenario is that I have three sub groups Budget, Person, RenewalDate for each Service (Service being the pivot point). So for each unique service I want to display the budget person and renewal date for each service by company.
I have created two tables to illustrate the base data and the required output.
How to do this dynamically because the number of Services is unknown, i.e. it could be 4 Services or 20, each with three sub columns, budget, person and renewal date.
Please find code below. It should be quite self explanatory as to what I am trying to do.
IMPORTANT:
1. I really need it to be dynamic
2. the Services are not standardised names, they are numbered for illustration purposes only, they vary in naming convention.
create table #BaseData
(
Company nvarchar(100),
Person nvarchar(50),
[Service] nvarchar(100),
Budget int,
RenewalDate datetime
)
[Code] .....
I'd like to get a extract table result, with a reference id primary key, showing the maximum dates for events and who was responsible for them. I can get the max(date) field in columns using PIVOT, but can't see a way to get the 'who' field without lots of LEFT JOINs.
Here's some test data and code which shows the principle:
CREATE TABLE #t
(
ref INT ,
id INT ,
who VARCHAR(10) ,
checkin DATE
[Code] ....
The result set is:
ref 1 who1 2 who2 3 who3 4 who4
123 2014-01-18 carol 2014-01-18 andy 2014-01-16 bill 2014-01-17 carol
456 NULL NULL 2014-01-17 NULL NULL NULL NULL NULL
Is there some way to avoid all the LEFT JOINs, maybe by using another PIVOT, to produce the same result?
I would like to have rows presented as columns. That's why I use the PIVOT function at the end.The resultset will be presented in Excel using an external connection to the view.
When I try to save the view I get the error
Msg 4104, Level 16, State 1, Procedure _TEST, Line 47
The multi-part identifier "vk.OppCode" could not be bound.
Code (restricting the columns that I actually have to the relevant columns only):
USE [DBTest]
GO
/****** Object: View [dbo].[_TEST] Script Date: 23-10-2014 17:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
I have two VB.NET dataclient program A abd B.
What i want to do is .. to find the best way to insert the high speed data( that comes at every 10 ms) to the sql server express database table .
I have two options to store the data in the table . One is to insert all the data in one row , the other is to divide it into many rows in the same table . In approach A i will get like 10 records per second and in approach B i may get i may get 30 - 50 records per second based on the amount of data that is comming . i.e i am creating a new row in the table for every extra column added in Approach A and duplication the other columns.
I wanted to find the better way to insert the data based on the performace metrics like CPU usage and memory usage.
I have two dynamic pivot tables that I need to join. The problem I'm running into is that at execution, one is ~7500 characters and the other is ~7000 characters.
I can't set them both up as CTEs and query, the statement gets truncated.
I can't use a temp table because those get dropped when the query finishes.
I can't use a real table because the insert statement gets truncated.
Do I have any other good options, or am I in Spacklesville?
I have a query
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(name)
[Code] ....
and so on.
I've tried putting my group by everywhere but it's not working. I'm missing something but what?
I am having trouble figuring out why the following code throws an error:
declare
@cols nvarchar(50),
@stmt nvarchar(max)
select @cols = ('[' + W.FKStoreID + ']') from (select distinct FKStoreID from VW_PC_T) as W
select @stmt = '
select *
[Code] ...
The issue that I am having is:
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '[' to data type int.
I know that I have to use the [ ] in order to run the dynamic sql. I am not sure what is failing and why as the syntax seems to be clean to me (obviously it is not).
I need to convert the column to rows but within group. example
Group Name Value
p a 1
p b 2
p c 3
p d 4
q a 5
q b 6
q d 7
r a 8
r b 9
r c 10
r d 11
This need to be transposed to :
Group a b c d
p1234
q56NULL7
r891011
I currently have data stored in a temporary table and I would like to transpose the data into a better format. I would like for the query to be dynamic since one of the tables currently has over 500 columns.
The attached file provides an example of the table structure along with sample data. Below the first set of data is the desired final format.
I have 2 tables (#raw1 & #raw2). Each has a year (yr) and month (mnth) and a count (cnt1 / cnt2) field. I need a table created as follows:
2013 2014 2015
cnt1 cnt2 cnt1 cnt2 cnt1 cnt2
jan 5 77 77 8 88
etc....
Normally, I would ask about a pivot but since it is months down one side and year and columns on the top, I don't know if a pivot will work.
create table #raw1 ([yr] int, [mnth] int, [cnt1] int)
insert into #raw1 values
(2013, 1, 5)
, (2013, 2, 5)
, (2013, 3, 5)
, (2013, 4, 5)
, (2013, 5, 5)
, (2013, 6, 5)
[code]....
My table structure is like
col1 col2 col3 col4 col5 col6
abc. def. 3fg. 59j. 567. 596040
abc. def. 3fg. 59j. 567. 596042
abc. def. 3fg. 59j. 567. 596043
abc. def. 3fg. 59j. 567. 596044
edf. ijk. rkl. 1fh. 567. 596045
edf. ijk. rkl. 1fh. 567. 596046
edf. ijk. rkl. 1fh. 567. 596047
edf. ijk. rkl. 1fh. 567. 596048
edf. ijk. rkl. 1fh. 567. 596049
And I am trying to get the above data , gel them ino col 6 by comma separated
col1 col2 col3 col4 col5 col6
abc def 3fg 59j 567 596040,567 596042,567 596043,567 596044
edf ijk rkl 1fh 567 596045,596046,596047,596048,596049
Can I get an example query for this...
I need to pivot my records in columns into rows. Here is sample data:
create table #columnstorows
(
Payervarchar (5),
IDvarchar (5),
ClMvarchar (2),
Paid1float,
[Code] ....
Desired result:
Payer1Payer2ID1ID2Paid1APaid1BPaid2APaid2BPaid3APaid3B
U001 U002 00100276.58 19.53 153.4896.43 53.48 200
Is it possible to pivot the output data into multiple rows?
I wanted one row for deleted data and another row for Inserted data, I was looking at UNION ALL and CROSS APPLY but to no avail.