Pivot Results With Columns For Movements In Period
Mar 7, 2014
I have a query which returns the movements to and from our warehouse stock, as well as the current stock for each depot and how much is on order. What I need is a kind of pivot so that each item is shown just once, and then summarises the movements in 4 extra columns: Last 30 days, 30-60 days, 60-90 days and 90-120 days. How can I achieve this with my query below? A sample of some of the results is also shown.
What I would like to end up with is a pivot table of each account, the trigger code and service codes attached to that account, and the rate for each.
I have been able to dynamically get the pivot, but I'm not joining correctly, as its returning every dynamic column, not just the columns of a trigger code. The code below will return the account and trigger code, but also every service code, regardless of which trigger code they belong to, and just show null values.
What I would like to get is just the service codes and the appropriate trigger code for each account.
SELECT @cols = STUFF((SELECT DISTINCT ',' + ServiceCode FROM TriggerTable FOR XML PATH(''), TYPE ).value('(./text())[1]', 'VARCHAR(MAX)') ,1,2,'')
I have a cube with a Period Dimension, in which the hierarchy is as follows:
Year Quarter Month Day
Users access the data via a pivot table.
The problem is that whenever a new month comes in, the pivot table doesn't automatically display data regarding the new month. Can someone please help me here.
I have a [Fin Period] column which outputs a number between 1 and 12 depending on the financial period. For numbers 0-9 this is output as a single digit. How can I format this so it places a 0 in front of these numbers but not in front of periods 10-12?
Imagine a 5 column dataset with the following fields - Organiser, Date of Invoice, Total Invoice Value, Reimbursements and Service tax charged. Using the PowerPivot, I want to determine the "Growth in Professional fee billed over the previous period" - please note that previous period need not be previous year because a client may be billed after a gap of 1-2 years as well.To compute growth, I first need to determine the absolute value of Professional fee billed over the previous period. This is where I am getting stuck. Since the billing periods for different clients need not be consecutive, I cannot use the SAMEPERIODLASTYEAR function.
In trying to solve the problem, I tried to frame a calculated field formula but could not do so. Therefore, I tried solving it via a calculated column formula in the PowerPivot window. My idea here was to determine the client wise previous financial year for each row and then use this column in a calculated field formula to get my desired result. I am getting an error when I write this formula (see Billing data tab of PowerPivot window).
Hello, I would like to exclude the time period after 5.30pm and before 8.30am in my results. The time is in a 13 digit timestamp format which is the same as a standard unix timestamp with 3 digits which are microseconds.
I used:
dataadd(ss, TTIME/1000, '1970-01-01')AS time
to create a column with a readable time in it.
Here is a screenshot: http://www.abtecnet.com/timescreenshot.jpg
Can anyone help me with this. Thanks very much. Andrew
I need to query the count of an occurrence in a given period of time, so I created this query. But it does not give me any results.
SELECT TOP 1000 o.[ID] ,o.[TimeOfOrder] ,x.[StreeLine1] FROM [SC].[dbo].[bvc_Order] o FULL JOIN SC.dbo.xmlAddressRead x ON o.ID= x.id WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE()))) < 48 GROUP BY x.[StreeLine1], o.ID, o.TimeOfOrder HAVING COUNT(x.[StreeLine1])>1
Then I change the query slightly and I ask it to show me the ones that are going to '599 Ships Landing Way' and it gives me 356 results! The Query doesn't crash, but it doesn't give me the results I need. What did I do incorrectly?
SELECT TOP 1000 o.[ID] ,o.[TimeOfOrder] ,x.[StreeLine1] FROM [SC].[dbo].[bvc_Order] o FULL JOIN SC.dbo.xmlAddressRead x ON o.ID= x.id WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE()))) < 48 AND x.[StreeLine1]='599 Ships Landing Way'
Power Pivot .... I have a model established that has a date table, Order table, Sales rep table, and a financials table that all feed each other nicely.
The issue is that some of the individual sales reps are also on sales "Teams" of 2-4 people, and I am not sure how to account for that when I am building pivot tables without relying on slicers and CTRL clicking.
I also don't want to resort to programmatically duplicating rows via SQL.
I have a single column returned from a select statement. How can I have this returned as a vertical string? I looked into using PIVOT but my scenario seems too simple to use Pivot. I'm not requiring any aggregate functions or anything.
is it possible to replicate this in SSRS I wonder??I have included the code of the fields used and a snapshot of some data, and also how the Pivot looks in Excel.
I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL DROP TABLE #Pyr GO SELECT vst_int_id, [4981] AS Primary_Ins, [4978] AS Secondary_Ins,
[code]....
The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.". The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT". I will post the definitions from another effort below.
I am trying to pivot my data results so that instead of showing multiple rows for each product a client has it will show one line for each client and concatenate all of their products together.
For example, if I run the following query:
SELECT [Year], [Client_Name], [Product_Name] FROM My.dbo.Table GROUP BY [Year], [Client_Name], [Product_Name]
I have a table with 40k terms and I need to map these to a set of objects where each object is represented as a column(tinyint). The object/column name is represented as a guid and columns are added/removed dynamically to support new objects for a set of terms.
I think I need to then convert this set of rows to a table which I can join to the object runtime table to start these objects if the column has a count/sum greater than 0. This is the table I think I need in order to join on guids to the runtime table:
NAME Count =========== guid10 guid22 guid32 guid40 guid51
I don't know how to construct this table for the former table. I think it may be a pivot table, but I don't know. I have the column names:
SELECT NAME FROM SYSCOLUMNS WHERE ID = OBJECT_ID(#Temp) ORDER BY COLID
NAME is a sysname, which doesn't seem to cast into a guid, also a problem when joining the runtime table with this #Temp table.
I also don't want to use a cursor to construct a table.
I am workong on this Pivot select. At it is last part, I want to find a way to re-arrange the column order. Here is the test data:
if OBJECT_ID ('tempdb..#temp') is not null drop table #temp go create table #temp(b_date varchar(15), Gender varchar(5), Admin_date varchar(15), S_ID int, TEST_NAME varchar(5),SCHOOL_YEARvarchar(5), PART_DESCRIPTIONvarchar(15),Test_Score int)
[code]....
But the output requries the last couple columns to be SCHOOL_YEAR, TEST_NAME, i.e. they come after the scores.I thought the easiest way will be to select into #temp2, then extract from there. Even that, I couldn't get that to work with the select pivot.
I have a simple query showing customer net invoice value by month and work type. What I would like to do is to summarise the data so that for every customer I see the net invoice value for each work type for each month.
I would like to see this as: Group, Customer, Period and then the individual work types, of which there are always 8, as columns with the net invoice value under each of them. How can I accomplish this best? Sample code and results are below:
select [Group], [Customer], [Period], [Work_Type], Isnull([Invoiced],0) [Net] from vw_R_Damages
Group Customer Period Work_Type Net ------------------------------------------------------------ ------------------------------------------------------------ --------------------------------- -------------------- --------------------- JARVIS CONTRACTING LIMITED JARVIS CONTRACTING LIMITED 2011-12 Breakdown / Call out 0.00 JARVIS CONTRACTING LIMITED JARVIS CONTRACTING LIMITED 2011-12 Breakdown / Call out 0.00 BARRATT BARRATT NORTHAMPTON 2011-12 Breakdown / Call out 0.00
I have a table the records the results of three different tests that are graded on a scale of 1-7. The table looks something like this. PersonId TestA TestB TestC
1 4 5 4 2 6 2 4 3 5 5 6 4 1 5 1
I would like to have a SQL statement that would pivot all this data into something like this
Test 1 2 3 4 5 6 7 A 1 0 0 1 1 1 0 B 0 1 0 0 3 0 0 C 1 0 0 2 0 1 0
Where the value for each number is a count of the number of people with that result.
The best solution that I have been able to come up with is to pivot each test and UNION ALL the results together. Is there a way to do this in a single statement?
(If this has already been covered I apologize, but I could not find the solution.)
I currently have a pivot table that is working great but I need to add to it. The below code is giving me the total ServiceTime per date, which is dynamic. I need to split this service time out depending on the stage of this note.
1) If the note has been signed 2) If the note has been signed and countersigned
SUM(CASE WHEN countersigned_id IS NULL AND signed_id IS NOT NULL THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) ELSE 0 END) as PendingServiceTime,
SUM(CASE WHEN countersigned_id IS NOT NULL THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) ELSE 0 END) as ApprovedServiceTime
How do I add this to my pivot table query? I am thinking that I need to have two seperate queries and join them together some how.
SELECT lastname + ', ' + firstname as FullName, [12/3/2007], [12/4/2007], [12/5/2007] FROM (SELECT p.LastName, p.FirstName, t.ServiceDate,
ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) AS ServiceTime
FROM dbo.allNotes(8) AS t LEFT JOIN dbo.note_Collateral_provider AS d ON d.note_Collateral_id = t.ID LEFT JOIN dbo.Personnel as p ON d.personnel_id = p.ID LEFT JOIN dbo.Clients as c on t.ClientID = c.ID LEFT JOIN fPayor(8) fp on fp.noteId = t.id and fp.dbTable = 'collateral' LEFT JOIN dbo.payor py ON py.ID = substring(fp.fPayorName, 41, 19) LEFT JOIN dbo.payorinfo pyInfo ON pyInfo.ID = py.payorinfoid WHERE t.AgencyID = 8 AND t.tableName = 'collateral' AND t.not_billable_reason_id IS NULL AND VOID_ID IS NULL AND ((t.signed_id IS NOT NULL AND t.countersigned_id IS NULL) OR (t.countersigned_id IS NOT NULL)) AND t.ServiceDate BETWEEN CONVERT(DATETIME, '12/03/2007') AND CONVERT(DATETIME, '12/05/2007') ) rs Pivot (SUM(rs.ServiceTime) FOR rs.ServiceDate IN ([12/3/2007], [12/4/2007], [12/5/2007]
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,
I'm trying to write a query that puts the data present in the "Year" column as if they were in a row (column definitions)..making sums of pieces and values.. or.. to be more clear..
Hi, I just read on web that we can not use grouping columns in a variable when using PIVOT operator. For example like, USE AdventureWorks GO SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5 FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p PIVOT ( COUNT (PurchaseOrderID) FOR EmployeeID IN ( [164], [198], [223], [231], [233] ) // cannot put these in a variable like @Col ) AS pvt ORDER BY VendorID;
Though it can be achieved using when making the query using dynamic sql. If some can make it clear why it is possible using dynamic sql and not with the above code.
I am trying to do a PIVOT on a query result, but the column names created by the PIVOT function are dynamic.
For example (modified from the SQL Server 2005 Books Online documentation on the PIVOT operator) :
SELECT Division, [2] AS CurrentPeriod, [1] AS PreviousPeriod FROM ( SELECT Period, Division, Sales_Amount FROM Sales.SalesOrderHeader WHERE ( Period = @period OR Period = @period - 1 ) ) p PIVOT ( SUM (Sales_Amount) FOR Period IN ( [2], [1] ) ) AS pvt
Let's assume that any value 2 is selected for the @period parameter, and returns the sales by division for periods 2 and 1 (2 minus 1).
Division CurrentPeriod PreviousPeriodA 400 3000 B 400 100 C 470 300 D 800 2500 E 1000 1900
What if the value @period were to be changed, to say period 4 and it should returns the sales for periods 4 and 3 for example, is there a way I can change to code above to still perform the PIVOT while dynamically accepting the period values 4 and 3, applying it to the columns names in the first SELECT statement and the FOR ... IN clause in the PIVOT statement ?
Need a way to represent the following [2] and [1] column names dynamically depending on the value in the @period parameter.
[2] AS CurrentPeriod, [1] AS PreviousPeriod
FOR Period IN ( [2], [1] )
I have tried to use the @period but it doesn't work.
I found this Microsoft article for creating crosstab-like queries in SSMS.Is it possible, however, to create this same query if I do not know what the values for the columns will be? Using their example for my problem, I will not know what the values in the "IN" criteria will be because my query would be for a "rolling" 12 months (thus causing that IN criteria to change every month).I've tried declaring variables to pull in the values, but since this will eventually go into a view, I don't think that I can use declared variables.
