I built a query that brings in 'Discounts' (bolded) to the Order detail by using the bolded syntax below. I started off by running the query without the bolded lines and got exactly what I was looking for but without the ‘Discount’ column. When I tried to add the ‘Discount’ into the query, it duplicated several order lines. Although total ‘Discount’ column ties out to the total amount expected in that column, ‘Total Charges’ are now several times higher than before.
For example, I get 75 records when I run without the bolded syntax and I get several hundred results back when adding back in the bolded syntax when i should still be getting 75 records, just with an additional column ‘PTL Discount’ subtotaled.My question is, how to I introduce a new select or join from another table without duplicating the original data?
SELECT Gruppo_Assegnatario, [100] as stato1, [101] as stato2, [102] as stato3 FROM ( select
[Code] ...
That extracts only zeros (columns "stato1", "stato2", "stato3"):
Gruppo_Assegnatariostato1stato2stato3 SDB_BE Vita Antiriciclaggio0 00 SDB_BE Vita Assistenza clienti000 SDB_BE Vita Emissione000 SDB_BE Vita Gestione Rendite000 SDB_BE Vita Liquidazioni000
[Code] ....
Unlike the "SourceTable":
select CASE_ID_, Stato, Gruppo_Assegnatario FROM TicketInevasiPerGruppoEStato extracts
CASE_ID_ Stato Gruppo_Assegnatario HD0000003736734 AssegnatoSDB_GBS Variazione HD0000003736739 AssegnatoSDB_GBS Variazione HD0000003736743 AssegnatoSDB_GBS Variazione HD0000003736783 AssegnatoSDB_GBS Variazione HD0000003736806 SospesoSDB_BE Vita Selezione
[Code] ....
How can I get the right count in the first data mining replacing the zeros (columns "stato1", "stato2", "stato3")?
I would like to use a dynamic pivot table in my data source view. It seems that a named query can be only one sql statement. So, I cannot use my multi-statement procedure that creates a dynamic pivot table output.
What is the best course of action here? I could hard-code my pivot table query. I could maintain a redundant table in the pivot format. Do I have any good options?
Create view All_employee AS SELECT Emp_Name, Emp_code FROM dbo.Employee UNION ALL SELECT Emp_Name, Emp_code FROM Emp_201402.Employee
But we have a different "Schema" for same table because we have archive table with same table name but with different schema name. Now we have req to make view which contain data of all table. But I can't seem to figure out how to do it in a view.
SET NOCOUNT ON DECLARE @Count INT, @TotalCount INT, @SQL VARCHAR( MAX ) DECLARE @Schema TABLE ( ID INT, NAME VARCHAR(512) ) INSERT INTO @Schema SELECT ROW_NUMBER() OVER (ORDER BY SCHEMA_ID), Name FROM sys.schemas where name like '%emloyee%' ORDER BY schema_id ASC
[Code] ....
Don' think that works.
Is this possible with a view or it other way to do it?
Is it possible to generate automatic refresh of excel 2013 table which displays some table of a power pivot model on file open?? I dont want to use pivottable (which supports this ...)
This program gets the values of A and B passed in. They are for table columns DXID and CODE. The textbox GET1 is initialized to B when the page is loaded. When I type another value in GET1 and try to save it, the original initialized value gets saved and not the new value I just typed in. A literal value, like "222" saves but the new GET1.TEXT doesn't.
Instead of doing a Count for the Pivot (the count will always be either 0 or 1 due to the design of the table being used), I would like to return an "X" for those records with a count of 1, and return a blank (otherwise null) for those records with a count of 0. So, the result set would look like:
ItemKey Description Aflatoxin Coliform Bacteria E_Coli Fumonisin Melamine Moisture Mold Salmonella Vomitoxin (DON) Yeast 1000 Item1000 X X X X X 1024 Item1024 X X X X X 135 Item135 X X X X X 107 Item107 X X X 106 Item106 X X X X X
I tried using a Case statement within the PIVOT portion, but I either did it incorrectly or it's not possible to do use a Case within the Pivot. Can I easily accomplish this?
In our contract management system, each contract has over 100 reference fields attached to it. These are all stored in single table with contract ID, reference GUID and value as the columns.
So you will have multiple rows for each contract....one for each of the reference fields and then the value attached to that reference.
I want to return the data so there is one row per contract with the reference fields as columns and the reference field values as the column data.
Can this be done using PIVOT as I have tried but not had any success?
Can someone help me parsing this ms-access PIVOT sql-statement to ams-sql-server sql-statement?Many thanks in advanceTRANSFORM Count(KlantenStops.id) AS AantalVanidSELECT KlantenStops.Uitvoerder, KlantenStops.KlantFROM KlantenStopsGROUP BY KlantenStops.Uitvoerder, KlantenStops.KlantPIVOT DatePart("m",leverdatum,1,0) In("1","2","3","4","5","6","7","8","9","10","11","12");
I 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,
I am trying to create a trigger on a table. Let's call it table ABC. Table looks like this:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ABC]( [id] [uniqueidentifier] NOT NULL,
[Code] ....
When someone updates a row on table ABC, I want to insert the original values along with the current date and time getdate() into table ABCD with the current date and time into the updateDate field as defined below:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ABCD]( [id] [uniqueidentifier] NOT NULL,
[Code] .....
The trigger I've currently written looks like this:
/****** Object: Trigger [dbo].[ABC_trigger] Script Date: 4/10/2015 1:32:33 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[ABC_trigger] ON [dbo].[ABC]
[Code] ...
This trigger works, but it inserts all of the rows every time. My question is how can I get the trigger to just insert the last row updated?
I can't sort by uniqueidentifier in descending as those can be random.
SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand, dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number], dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],
[Code] ....
I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.
This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).
The question is how do I PIVOT and preserve the percentage of ownership?
I am looking for a way to create a stored procedure that will show inventory availability. I would like to show the Inventory Name, The Date, and if the inventory is "checked out" using the ID name of the person who has the item.
For example it would look like this:
-------------------------------------------------------------------------------------------------- Inventory Name | 10/24/2015 | 10/25/2015 | 10/26/2015 | 10/27/2015 | 10/28/2015 -------------------------------------------------------------------------------------------------- Laptop | Tom | Tom | Tom | Avail | Avail Projector | Avail | Avail | Avail | Avail | Bob Air Card | Bob | Bob | Bob | Bob | Bob
It seems like I want to do a pivot table but there really is no aggregate so I am not sure what to use.
I have a stored proc I want to convert it to either a Normal Query using A while loop or a set based operation/recursive cte as I want to run it for multiple CompanyNames. I get the error message as An INSERT EXEC statement cannot be nested when I execute if for Multiple Companies using another Cursor
If I convert it to a Function I get the below error message
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function
converting this query to a normal query or let me know if there is any change which need to done to work with multiple companynames.
CREATE PROC [dbo].[USPT] @CompanyName varchar(50),@tablename varchar(50) AS BEGIN -- EXEC [USPT] 'xyz corp','Sales Header' DECLARE @str1 VARCHAR (MAX) set @str1 = ' DECLARE @No VARCHAR (MAX)
I have written a script to pivot a table into multiple columns.
The script works when run on its own but gives an error when i try to create a view or aprocedure from the same script. The temporary table #.... does not work so i have converted it to a cte.
Here is a copy of the script below
-- Dynamic PIVOT IF OBJECT_ID('#External_Referrals') IS NULL DROP TABLE #External_Referrals; GO DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);
The above table shows a parent child relationship with the hierarchy shown in column level. for each parent (IDs 1, 9, 11), I want the first child level where the column IsAutoCalculate = 0
so for parent Id 1, the rows to be returned is of level 1 as that is the first child row of this parent with IsAutoCalculate = 0. The rows with level 2 should not be returned
For parent id 3, the rows to be returned will be with level 2 as this is the first child row of this parent with IsAutoCalculate = 0
SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber,UCASE(Buildings.BuildingName) AS BuildingName,Buildings.MasterPlanCode, Buildings.UniformBuildingCode,Buildings.FunctionalCategoryCode, Buildings.OwnershipCode,Buildings.ConditionCode, Format$([BasicGrossArea],"0000000") ASdBasicGrossArea, Format$([CoveredUnenclosedGrossArea],"0000000") ASdCoveredUnenclosedGrossArea,IIf(Month([DateOccupancy])>9,Month([DateOccupancy]),"0" &Month([DateOccupancy])) & Year([DateOccupancy]) AS dDateOccupancy,Buildings.YearConstructed, Format$([NumberLevels],"00") ASdNumberLevels, Format$([UnrelatedGrossArea],"0000000") ASdUnrelatedGrossArea, Buildings.YearLatestImprovement,UCASE(Buildings.Address) AS Address, Buildings.CityCode,CityCodes.CountyCode, Format$([Circulation],"0000000") AS dCirculation,Format$([PublicToiletArea],"0000000") AS dPublicToiletArea,Format$([Mechanical],"0000000") AS dMechanical,Format$([Custodial],"0000000") AS dCustodialFROM CityCodes INNER JOIN Buildings ON CityCodes.CityCode =Buildings.CityCodeORDER BY "01C", Buildings.BuildingNumber, Buildings.BuildingName;Please if anyone can help me in Converting the above given Access Queryto Sql Server. I don't know which function to use for format$, IIF. Iwould really appreciate your suggestions.Thanks,
This is because a date of 04/16/2014 will show as 160416 in the first part of the field I need to parse it out of, thus becoming 04162014.
From there I then need to convert this "date" into a legitimate SQL datetime type, so that I can then run a DATEDIFF to compare it to when the record was actually entered, which is a separate field in the table, and already in datetime format.
When I use the below statement, I am getting the message that, "Conversion failed when converting date and/or time from character string."
I have a pivot table that connects to our data warehouse via a PowerPivot connection. The data contains a bunch of comment fields that are each between 250 and 500 characters. I've set the columns in this pivot table to have the 'Wrap Text' set to true so that the user experience is better, and they can view these comment fields more clearly.
However, whenever I refresh the data, the text wrapping un-sets itself. Interestingly, the 'Wrap Text' setting is still enabled, but I have to go and click it, then click it again to actually wrap the text. This is very burdensome on the user, and degrading the experience.
Any way to make this text wrapping stick so that I don't have to re-set it every time I refresh the data?
The excel pivot table is very powerful for the user, with dragging and dropping fields at will on the web page, but I can only get it work when run from the web server directly. The data is in SS2000 300,000 rows, get external data in Excel, then publish pivot table as HTM with interactivity. It seems to look for c:myodbc.dsn in the C: drive of the pc the user is on rather than the server, and I cannot use IP Addressing to relate to the dsn file. any ideas ?
We currently have a report generated weekly running script which creates an excel file and pivot tables. Using pivot tables we can drag and drop the various required fields and the report changes instantly. But i was told there is a limitation of size while using pivot tables.
Can this report be easily created with almost all the flexibility in reporting services, if so could you please guide me with some sample report or tutorials.
currently there is a some store procedures thats run every week using a DTS manually, is there a way of automating this.
I've imported a number of excel sheets into a Power Query Table. All seems to appear ok until I load the data. Of the 15k rows around 2k have a similar error where it cannot convert an integer to type string as below example
Expression.Error: We cannot convert the value 40 to type Text. Details: Value=40 Type=Type
The columns in question are all of integer type, I've looked through the M query and there is no conversion to string taking placeThe values where we don't get the error are also integers hence the intriguing question is why does the error occur on a subset and not the others. I suspect there is a limit to the number of errors also somewhere internally M query is converting the column to text for some reason.
I am dealing with what I believe is Oracle that is the source of a SQL View.
I am seeing a data type of Integer in the View, but I am not able to see what makes up that View. When I query the View, I can see that an Integer data type column is storing a blank space. I use ISNUMERIC(ColumnName) = 0 and there are a lot of rows that show as a zero length blank space, or text, or something. I just know that it is not an Integer.
I have attempted to CAST and Convert this value, but it will not. I have changed the data type on the table that is being inserted in too, and it still fails with a Conversion error. I have tried REPLACE(), but still the same conversion error.
