I am trying to find a solution in order to make a pivot dynamically. One of my department charge every month all the sales figure in one table and I need to pick up the last two months archived in order to make a pivot and to see if something is changed or not. What I am trying to do is to have these last two months dynamically. create table forum (customer varchar (50), nmonth varchar(6), tot int, archived datetime)
insert into forum values ('Pepsi','201503',100,'2015-04-28'),
('Pepsi','201504',200,'2015-04-28'),
('Texaco','201503',600,'2015-04-28'),
('Texaco','201504',300,'2015-04-28'),
[code]...
As you can see I have to change manually the values underlined every months but it's a temporary solution. How can I set up the last two months in a dynamic way?
I got a table which gets populated by stor proc where we pivot the Sum(Balance of mortgage) by YYYYMM for the whole duration of the loan term.
I have a requirement to rename the column header where the previous month end balance period be renamed to P0.
if we run the report today, then the balance as at 31/09 should show under column P0 which now shows under 201509 and then P0 keeps shifting with each month run.
I have a table that has the records as shown below
Id      From       To 1 Delhi     Noida 2 Faridabad  Kanpur 3 Noida Delhi 4 Kanpur Faridabad 5 Banglore Mumbai 6 G H 7 I J 8 Mumbai Banglore
I want the results in the following order
5 Banglore Mumbai 8 Mumbai Banglore 1 Delhi Noida 3 Noida Delhi 2 Faridabad Kanpur 4 Kanpur Faridabad 6 G H 7 I J
Suppose as in above records There is a complete roundtrip from Banglore to Mumbai and Mumbai to Banglore.
How to get a list of values to actually display in correct order in either a slicer or when on an axis on a pivot table?
I currently have the below list and have tried to add a preceding numeric (ex. "1. <=0") or preceding blank space, neither of which is visually great. Is there another way?
PeopleFields PersonID FieldDefID FieldValue 1 1 John 1 2 Smith 2 1 Alice 2 2 Johnson
Of course, we need to be able to search and display this data in a tabular format like: PersonID PersonType FirstName LastName 1 Consultant John Smith 2 Partner Alice Johnson
We have been building dynamic queries based on which fields are needed (users can select the fields), e.g.:
SELECT p.PersonID, p.PersonType, pf1.FieldValue AS 'First Name', pf2.FieldValue AS 'Last Name' FROM People p LEFT JOIN PeopleFields pf1 ON (p.PersonID=pf1.PersonID AND pf1.FieldDefID=1) LEFT JOIN PeopleFields pf2 ON (p.PersonID=pf2.PersonID AND pf2.FieldDefID=2)
This is very flexible but slow. We've done lots of optimization but can't get this below 5 seconds for common scenarios.
So I'm back to the drawing board now trying to figure out a better way to approach this. I'm wondering if it would be better (if even possible) to break this out into some sort of view or table UDF that would contain a full representation of all person data, pre-joined. Problem is, this is almost certainly going to have to involve dynamic SQL since we can't know anything about what fields are defined. I think that rules out any sort of view or table UDF, no?
Does anyone have a suggestion for a good approach? Thanks.
Any way to create a measure that filters the second column to mimic the behavior of only filtering for slicer for the Open Pool Date values as per the image below. Ultimately, I need to create a measure that only includes accounts that were opened 6 months prior to the month row context.
Trying to get the values in the second column to only include aging accounts 6 months prior instead of 12 months prior.
I have a column called demand and one called orders. I want to divide the demand column figure by the order column figure and put the new values in a column called AOV.
You may had this problem before and know how to do that or may be can suggest me with some ideas. I am in a process of creating a report wich has 3 groups and the data at the moment grouped by Company - Product - Customer. I want to be able to change grouping order and respectively the data in report , something like Product - Company - Customer or Customer - Product- Company. (just example) Is that possible to do at all? May I add some drag and drop functionality and integrate in my report ?
I have a report that displays data based on the last 12 months. Is there a way I can order the columns (header and data) based on the month it was run. eg. If I were to run the report in March, I want the columns to be ordered like this:
MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, JAN, FEB
If run the report in April, I want the columns to be ordered like this:
APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, JAN, FEB, MAR
So, the columns in the report are always ordered (12 months backward) based on the month it was run.
hi,I was pulling up a report in SQL, and I wanted the records to be ordered by dates descending. However, I found this ordering was only fine enough to order records by dates (not hours or minutes) (within the same date, records were ordered so that the latest entered were at the bottom). I wonder if anyone else has encouted this problem before, or I am doing something wrong.Thanks very much.
If we use: select * from ..... , normally, it will return an ordered result ( may be order by ID ), but how can we make an random order select statement. It mean every time we run the query, the result will be different from the other ?
As you can see, the above table has cumulative data. 1. It calculates the number of Products submitted till a particular date- weekly 2. The date columns should increase dynamically(if the dates in base table increases) each time the query is executed For ex: the next date would be 11/28/2007 I tried something like, it gives me count of €˜b€™ level and €˜p€™ level products by week declare @date1 as datetime select @date1 = '6/30/2007' while (@date1 != (select max(SDate) from dbo.TrendTable)) begin set @date1 = @date1 + 7 select Level, count(Products) from dbo.TrendTable where SDate < @date1 group by Level end what I think is required is a pivot that dynamically adds the columns for increase in date range. /Pls suggest if any other way of achieving it. Pls help!!!
Is it possible to include a currency symbol in an amount-field in PowerPivot/Pivottable based on a Currency column in a table? Something as the same as with SSAS MD. And I don't want fixed values in my code.
SELECT ID,new_unit,mhlka_id,mhlka, DATEPART(DAY, Date) AS theDay, ShiftID FROM v_Employee WHERE Date >= @BaseDate AND Date < DATEADD(MONTH, 1, @BaseDate)
ORDER BY unit desc, Fname
) AS y PIVOT ( min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30]) ) AS p END
the all code
DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT)
DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP
@BaseDate SMALLDATETIME,
@NumDays TINYINT
SELECT @WantedDate = '20080401', -- User supplied parameter value
how to order by month name query returns Dec13,Mar14,Jan14,Nov13 .. etc but i want to Nov13,Dec13,Jan14,Mar14 ... etc
declare @cols as varchar(max), @query as varchar(max) set @cols =STUFF((select ','+QUOTENAME(tb3.month) FROM ( select distinct (DATENAME(MONTH,dtDate)+''+CONVERT(varchar(5),YEAR(dtDate)) ) as month from tableA) tb3 FOR XML PATH(''), TYPE ).value('.','NVARCHAR(MAX)'),1,1,'') set @query ='SELECT '+@cols +' from (SELECT DATENAME(MONTH,dtDate) + CONVERT(varchar(5),YEAR(dtDate)) as month,Price FROM tableA ) tb pivot ( sum(Price) for monthin('+@Cols+')) p 'exec(@query)
the number of results per CaseID are varable some have 1 record others up to 36 records..I need to pivot the results based off of the competed date Name has 20 distinct values but we can have diplicates...so something like
Case Name Date 1 This 2015-01-01 1 That 2015-01-03 2 That 2013-01-01 2 Then 2014-12-03 2 This 2015-01-01 2 There 2015-04-03 3 This 1999-01-01 3 That 2001-01-02 3 This 2006-01-01
looks like this
1 This That 2 That Then This There 3 This That This
I have a table with all my orders lines, I need to identify (DAX formula) for a specific customer which is the first order of a specific article (first date filtered for customer and article) and use this information to tag the order line for that article for the specific customer as "Newly Ordered article" if the order date = first order date or as "Reordered article" if the date on the specific order line is subsequent to the first order date.
I imagine I need to combine a Lookup date filtered for customer and article and use it with a IF formula.
Similar to this I would also like to define that if the first order for a product for the specific custumer is older then a certain date, then this would be defined as and historical customer for the specific article, if the first order on the article is more recent the a specific date, then this will be defined as "new customer for that article".
I'm using a Contoso database running on Power BI Desktop (Version: 2.28.4190.122). The databiase is actually a Contoso.pbix file provided for a book [URL]. I have a DAX query that summarizes a Products table by Product[Manufacturer] and gives a count of products by the manufacturers.My Problem is that the ORDER BY COUNTROWS doesn't work.
why the order by clause doesn't work when I'm confined to the Products table while the order by clause does work when I start with the Sales table? The numbers are correct in both queries.Â
This query doesn't sort
EVALUATE SUMMARIZE ( Product, Product[Manufacturer], "ProductCount", COUNTROWS ( Product ) ) ORDER BY COUNTROWS ( Product ) DESC
Log-in access web-page.When page is accessed a stored procedure is executed that will provide a list of all the RA(Problem -- when a new building is added, this stored procedure must be opened and syntax added)..Results are stored in a drop down list on the page.Create a table holds list of all buildings something similar to this
Create Table [dbo].[BuildingInformation] ( ID INT IDENTITY NOT NULL, BuildingName varchar(50), RAName varchar(50)
[code]....
Alter the stored procedure so that when it is run it will iterate the BuildingInformation table and compare the value in BuildingInformation to the values in the field BuildingName of the view [RA]. If all buildings contained in the table [BuildingInformation] exist in the view [RA] nothing will change. If their are values in the [BuildingInformation] table that do not exist in the view [RA], the view [RA] will be dropped and recreate with a 'Union ALL' for each building in the table
SELECT Production_Date, Production_Order, LogicalVat KPI_Category, 'Probiotic Amt Consumed' KPI_Data1_Name,'RC Amt Consumed' KPI_Data2_Name FROM ( SELECT Production_Date , NULL Production_Order, LogicalVat, ReportValue ReportValue FROM BIReports.dbo.r_VatMake WHERE Production_Date between '10/27/2015' and '10/27/2015'
[code].....
Now the attributes changed into like below where the number after RC is dynamic and I can't use the query above anymore
Example: RC Amt Consumed - RC 6 RC Amt Consumed - RC 7 RC Amt Consumed - RC 8
Probiotic attribute changed into like below where number after PROB is dynamic
Probiotic Amt Consumed - PROB 15 Probiotic Amt Consumed - PROB 16 Probiotic Amt Consumed - PROB 17
I have persons who speaks multiple languages and they are in one table, each row is added if he/she speaks multiple languages. Instead I want to add additional columns and load the data.(what I have shown in the desired output)
name language ------------- ron  english ron  french ron  spanish andy  english andy  hindi kate  english
Desired output
name language1 language2 language3 language4 language5 language6 ----------------------------------------------------------------- ron  english  french   spanish andy english  hindi Kate englishÂ
Given a table that has three columns that together create a key and two columns that together define NameValue pairs, how can the difference between instances of values be calculated and displayed?One table is used to contain periodic dumps of data from various sources. Because this is an early stage of development for the software project instead of having explicit columns that contain specific data the table contains name/value pairs. This allows the software to export anything to the database table. When this data is imported, earch row shares the same key (three columns containing a machine type, serial number and a timestamp), a name that identifies the data and a string that contains the actual data. While this arrangement makes it trivial to support the addition of any data that the software developers want to export, it makes it less obvious as to how to generate reports.Let's make an example. Lets assume that there are two vending machines, each of which has just 3 snacks and each of which generates two separate reports.
Type Sn Timestamp Name Value A 1 2015-08-15 12:34 Snick 5 A 1 2015-08-15 12:34 Mars 10 A 1 2015-08-15 12:34 MandM 0B 2 2015-08-15 15:31 Snick 1 B 2 2015-08-15 15:31 Mars 9 B 2 2015-08-15 15:31 MandM 0A 1 2015-08-21 09:12 Snick 11 A 1 2015-08-21 09:12 Mars 18
[code]...
So, the names of the values become the report's columns. The reports are sorted by timestamp, then by type, then by serial number.The value associated with the previous row that shares the same name is subtracted from the value of the next row in which the same name occurs and that becomes the displayed value in the report.
I have a table which I would like to group on several columns, and for the Contract number, I'd like a maximum of four different columns which would contain pivoted information.
Here is my DDL:
CREATE TABLE [dbo].[SV00403]( [CUSTNMBR] [char](15) NOT NULL, [ADRSCODE] [char](15) NOT NULL, [Contract_Number] [char](11) NOT NULL, [WSCONTSQ] [int] NOT NULL,
-- drop table #temp create table #temp(ID bigint, Description varchar(50), ET varchar(200), ET_Status varchar(50), ET_Date datetime, ET_IsValid varchar(3))
insert into #temp select * from (values (1,'Test','A', 'Ack','08/15/2015', 'Yes'),(1,'Test','B', 'Nack','08/17/2015', 'Yes'),(1,'Test','C', 'Ack','08/21/2015', 'Yes')) a(ID, Description, ET, ET_Status, ET_Date, ET_IsValid)
I want to pivot this. My expected result look like this.
ID - Description - ET_A_Status - ET_A_Date - ET_A_IsValid -  ET_B_Status - ET_B_Date  - ET_B_IsValid - ET_C_Status  - ET_C_Date - ET_C_IsValidÂ
1  - Test   - 'Ack'    - '2015-08-15 00:00:00.000'  - 'Yes'   -  'Nack'  - '2015-08-17 00:00:00.000'  - 'Yes'  - 'Ack'   - '2015-08-21 00:00:00.000' -  'Yes'
I'm trying to Pivot and I keep getting an "Invalid Column Name" error, which I can't figure out since, if I run the query and exclude the Pivot statement, the query runs fine.
Columns ItemNmbr Char(31) not null SetupTime_I Numeric(19,5) not null WCID_IÂ Char(11) not null select ItemNmbr,SetupTime_I, WCID_I from RT010130 Results
Now run select ItemNmbr,SetupTime_I, WCID_I from RT010130 pivot (sum(SetupTime_I) for WCID_I in ([BLA01],[URE02])) PVT
And I get an Invalid Column Name error for both SetupTime_I and WCID_I - which, as far as I can tell, is demonstrably incorrect.
itemID | part 1Â Â Â Â Â Â Â Â | A 1Â Â Â Â Â Â Â Â | B 2Â Â Â Â Â Â Â Â | A 2Â Â Â Â Â Â Â Â | A 2Â Â Â Â Â Â Â Â | A 3Â Â Â Â Â Â Â Â | C
I need the table to look like the following:
itemID | part1 | part2 | part 3 1        | A       | B     | null 2        | A       | A     | A 3        | C       | null   | null
There will _never_ be more than three parts to an item, and it does not matter what order they are in.
If I run the following command in a Query window it works:
RESTORE DATABASE CIS_Source_Data_Test FROM DISK = 'y:CIS_Source_Data_backup_2015_06_17_085557_7782407.bak' WITH RECOVERY, REPLACE
If I dynamically put together the command and store it in variable @cmd and then execute it using exec sp_executesql @cmd or exec (@cmd) it does not work. I get the following:
Msg 2745, Level 16, State 2, Procedure CIS_Source_Data_Refresh, Line 92 Process ID 62 has raised user error 50000, severity 20. SQL Server is terminating this process. Msg 50000, Level 20, State 1, Procedure CIS_Source_Data_Refresh, Line 92 RESTORE DATABASE is terminating abnormally. Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Why it won't work when I try to create and run it dynamically?
