Dear All
I am very new to MS SQL Server and I am wondering is there some tool
which would allow me to build pivot tables in SQL more easily. At the
moment writing a query can be quite challenging and difficult.
Is there any software which allows you to do it more intuitively and
gives you some visual feedback about query you are building?
I would be very grateful for any help with this.
wujtehacjusz
I'm trying to select from a table with three columns. I want these columns to be spread out among multiple columns based on the values. I hope someone can shed some light on this. I might be able to use pivot, but don't know how the syntax would roll for this.
Here is the example of dummy values and the output I am trying to obtain.
drop table table1
create table table1
(Category int, Place int, Value int)
insert into table1 values
(1, 1, 20)
insert into table1 values
(1,2, 12)
insert into table1 values
(1,3, 30)
insert into table1 values
(2,1, 34)
insert into table1 values
(2,2, 15)
insert into table1 values
(2,3, 78)
select Category,
(select top 1 value from table1 where place = 1 and Category = t1.Category) as place1,
(select top 1 value from table1 where place = 2 and Category = t1.Category) as place2,
(select top 1 value from table1 where place = 3 and Category = t1.Category) as place3
from Table1 t1
group by Category
Thanks for the help.
I have two data tables:
1) Production data with column headers: Key, Facility, Line, Time, Output
2) Costs data with column headers: Key, Site, Cost Center, Time, Cost
The tables have a common key named obviously as Key. The data looks like this:
Key
Facility
Line
Time
Output
Alpha
I would like to have two pivot tables which I can filter with ONE slicer based on the column Key. The first pivot table shows row labels Facility, Line and column labels Time. Value field is Output. The second pivot table shows row labels Site, Cost Center, and column lables Time. Value field is Cost.How can I do this with Power Pivot? I tried by linking both tables above to a table with unique Keys in PowerPivot and then creating a PivotTable where I would have used the Key from the Keys table.
I need to develop a language specific dwh, meaning that descriptions of products are available from a SAP system in multiple languages. English is the most important language and that is the standard. But, there are also requirements of countries that wants productdescriptions in their language.Â
Productnr Productdesc Language
1       product    EN
1       produkt    DE
One option is to column the descriptions, but that is not very elegantly. I was thinking of using bridge tables to model this but you have to always select a language in a filter (I think)..
I'm thinking of a technical solution, such that when a user logs on, the language is determined and a view determines whether to pick a certain product table specific for a certain language. But then I don't have the opportunity to interchange the different language specific fields in a report (or in my case PowerPivot).
Hello Everybody,
Can anyone tell me how to create a pivot table in SQL Server?
I am attaching a picture of what the table should look like before and after the transformation. Thank you in advance.
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?
Hi,
I am still having trouble trying to find out how to use SQL for a pivot table. Can someone help me out, please?
Thanks
Wendy
Hi,
I'm wanting to manipulate data between two tables in the following manner.
Table1
Column1 Derived Column ValueX ValueY
1 0 100 100
2 0 40 60
3 0 30 70
4 0 90 85
5 0 10 102
Table 2
IDColumn Qty1 Qty2 Qty3 Qty4 Qty5
a 10 20 30 40 50
I need to take the data in columns Qty1 - Qty5 in Table2 and transpose it into the Derived Column in Table 1.
Leaving the ValueX and ValueY columns as they are relative to column1.
I believe this can be done using a Pivot Table, but cannot find enough clear information from which to learn how to do it.
Any help will be welcomed.
John
I have a database with table representing city blocks, houses, and people. On my main aspx page, I have a datagrid which displays a list of the blocks, followed by a count of the houses in each block, followed by a count of the people in each block. Right now I have something that works, but it is awful. Every time the page loads it makes a ton of connections to the database, and I have convoluted spaghetti code. Now I need to add more functionality, but I can't rightly do that until I find a more efficient way to do this.Step one. The program connects to the database and gets a list of the blocks using the statement "SELECT blockid FROM blocks"Step two: The program iterates through each blockid in the list and executes the statement "SELECT houseid FROM houses WHERE blockid = (whatever)"Step three: The program counts the rows returned from step two to determine the count of how many houses are in that block. Step four: The program iterates through each houseid from step two and exectues the statement "SELECT COUNT (personid) FROM people WHERE houseid = (whatever)" the result is added to a variable that keeps a running count.Step five: the final value of the variable in step four is the number of people in that block.My question for you is, how can this be done more efficiently? Can I group together some awesome SQL statement that will get these counts? I thought about doing something like "SELECT blockid (SELECT COUNT houseid FROM houses WHERE blockid = something) as HouseCount" but I can't figure out how I could take the value in the first column (blockid) and pass it to the inner select statement.Any thoughts on how to make this better? Below is the full code for my function, in case you want to examine in more detail. Also, I am in the process of changing the select statements into stored procedures, so don't beat me up too badly over that bit of ugliness in my function. Thanks.Private Function GetBlockDataSet() As DataSet Dim myconnection As SqlConnection Dim objDataAdapter As SqlDataAdapter Dim query, connectionstring As String Dim tempDS As New DataSet Dim houseDS As New DataSet Dim peopleDS As New DataSet Dim DC1 As New DataColumn Dim DC2 As New DataColumn Dim i, j, peoplecount As Int32 Dim DR, DR2 As DataRow
query = "SELECT blockid FROM blocks" connectionstring = configurationsettsing.appsettings("ConnectionString")
myconnection = New SqlConnection(connectionstring) objDataAdapter = New SqlDataAdapter(query, myconnection) objDataAdapter.Fill(tempDS, "BlockList")
DC1.DataType = System.Type.GetType("System.Int32") DC2.DataType = System.Type.GetType("System.Int32") DC1.ColumnName = "HouseCount" DC2.ColumnName = "PeopleCount" tempDS.Tables("BlockList").Columns.Add(DC1) tempDS.Tables("blockList").Columns.Add(DC2)
i = 0 For Each DR In tempDS.Tables("BlockList").Rows query = "SELECT houseid FROM Houses WHERE blockid = '" query &= tempDS.Tables("BlockList").Rows(i).Item(0) query &= "'" objDataAdapter = New SqlDataAdapter(query, myconnection) objDataAdapter.Fill(houseDS)
tempDS.Tables("BlockList").Rows(i).Item(1) = _ houseDS.Tables(0).Rows.Count tempDS.Tables("BlockList").Rows(i).Item(2) = 0 j = 0 peoplecount = 0 For Each DR2 In houseDS.Tables(0).Rows query = "SELECT COUNT (personid) FROM people WHERE HouseID = '" query &= houseDS.Tables(0).Rows(j).Item(0) query &= "'" objDataAdapter = New SqlDataAdapter(query, myconnection) objDataAdapter.Fill(peopleDS) peoplecount += peopleDS.Tables(0).Rows(0).Item(0) j = j + 1 peopleDS.Clear() Next tempDS.Tables("BlockList").Rows(i).Item(2) = peoplecount houseDS.Clear()
i = i + 1 Next
GetBlockDataSet = tempDS ' Here comes the garbage collection myconnection.Close() myconnection.Dispose() myconnection = Nothing objDataAdapter.Dispose() objDataAdapter = Nothing tempDS.Dispose() tempDS = Nothing houseDS.Dispose() houseDS = Nothing peopleDS.Dispose() peopleDS = Nothing DC1.Dispose() DC1 = Nothing DC2.Dispose() DC2 = Nothing
End Function
This statement returns date formatted 'yyyymmdhhnn'. But there has to be an easier way. Can someone help?
DECLARE @dt datetime;
SELECT @dt = GETDATE();
SELECT CONVERT(varchar(40),@dt,112) +
RIGHT('0' + CAST(DATEPART("hh", @dt) AS varchar(2)), 2) +
RIGHT('0' + CAST(DATEPART("mi", @dt) AS varchar(2)), 2) AS isodt;
Thank you.
I'm a having a problem with PIVOT tables.
I have a table that stores the results from multiple surveys, (see format below)... SurveyID RespondantID QuestionID Answer
=========================================================
1 1 1 X
1 1 2 Y
1 1 3 Z
1 2 1 R
1 2 2 S
1 2 3 T
14 10 1 A
14 10 2 B
14 11 1 C
14 11 2 D
I want to use a pivot table to convert the data it to a format like the following (for surveyID = 1)... RespondantID Q1 Q2 Q3
===============================
1 A B C
1 R S T
The 2 differences I need from a normal pivot query are....
(a) I don't want to summarise my results at all
(b) I have multiple surveys in the same table so need to use an extra clause to pick out info for the survey I am interested in.
So far I have come up with...
SELECT RespondantID, [1] As Q1, [2] As Q2, [3] As Q3, [4] As Q4, [5] As Q5, [6] As Q6, [7] As Q7, [8] As Q8, [9] As Q9, [10]As Q10 FROM (SELECT RespondantlD, QuestionlD, Answer FROM "3_Temp" WHERE SurveylD=3) AS preData PIVOT (COUNT(Answer) FOR QuestionlD IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) ) AS data ORDER BV RespondantlD
But it doesn't work and I can't figure out why (error message when executing in VBA = "RunSQL function requires a SQL argument"
Can anyone help?
I NEED KNOW IF SOMETHING KNOW CERTANLY .....ABOUT THIS :************************************************** ********************************PLACE A NAMED SET INTO A PIVOT TABLE : ALL ABOUT THIS..FORMULAS...FORMS...EXAMPLES.... ALL !ANOTHER : GET RELATIONSHIP ABOUT THE FIRST TROUBLE : SEE A NAMED SET FROM ACUBE ( OLAP)************************************************** ************************************************REALLY NEED ALL ABOUT YOU CAN SEND ME.AND WRITE ME ....A OLNLY FIN MANYQUESTIONS IN FORUMS....BUT NO ONE RESPONSES.....APARENTLY TOO MUCH PEOPLEHAVE THIS TROUBLE ...... I KNOW ....NO EVERY DAY .MEMBERS THAT POST IN THISWEBSITE..TAKE REPLY...BUT........, REALLY NEED INFO ABOUT .AS SOON ASPOSSIBLE....MI E-MAIL :Join Bytes!THANKS !.MY BEST WISHES...--hi all !
View 1 Replies View Related
Hello,
I just finished a lengthy process (for me) of writing this cursor that gets each row from the Fact table, and creates a running total of the billable hours (hours from rows where BillableType = 1). It starts over when the month changes, the year changes, or the employee changes.
I did this again for a running total of billable hours for the year.
I put these in 2 stored procedures and run them in an Execute SQL control flow task in my SSIS package. It seems like SSIS is designed to make this kind of procedure simpler, and I'm wondering if I'm doing this in the best way, or if there's a more efficient way to do this using tasks inside SSIS. Can anyone advise? Any help is greatly appreciated.
Best,
Andy
Don't feel obligated to read this cursor if you understand the problem above. It's not color coded because when I copied it from SQL I lost the tabbing on the case expressions.
Code Snippet
CREATE PROCEDURE YearTotalHours AS
DECLARE @FactBillingId Int;
DECLARE @dt DateTime;
DECLARE @EmpKey Int;
DECLARE @YearTotalHours Float;
DECLARE @NewYearTotalHours Float;
DECLARE @NewYear Int;
DECLARE @BillableType Int;
DECLARE FactBillingFinalRows CURSOR FOR
SELECT FactBillingId
FROM FactBillingFinal
OPEN FactBillingFinalRows
FETCH NEXT FROM FactBillingFinalRows
INTO @FactBillingId
WHILE @@FETCH_STATUS = 0
BEGIN
SET @EmpKey = (SELECT EmployeeKey FROM FactBillingFinal WHERE FactBillingId = @FactBillingId)
SET @dt = (SELECT dt FROM FactBillingFinal WHERE FactBillingId = @FactBillingId)
SET @BillableType = (SELECT BillableTypeKey FROM FactBillingFinal WHERE FactbillingId = @FactBillingId)
SET @NewYear = (SELECT Year(@dt))
SET @NewYearTotalHours =
CASE
WHEN @FactBillingId = 1 THEN
CASE
WHEN @BillableType = 1 THEN (SELECT Hours FROM FactBillingFinal WHERE FactBillingId = @FactBillingId)
ELSE 0
END
ELSE
CASE
WHEN @EmpKey <> (SELECT EmployeeKey FROM FactBillingFinal WHERE FactBillingId = @FactBillingId - 1)
THEN
CASE
WHEN @BillableType = 1 THEN (SELECT Hours FROM FactBillingFinal WHERE FactBillingId = @FactBillingId)
ELSE 0
END
ELSE
CASE
WHEN YEAR(@dt) = (SELECT YEAR(dt) FROM FactBillingFinal WHERE FactBillingId = @FactbillingId - 1)
THEN
CASE
WHEN @BillableType = 1
THEN (SELECT @YearTotalHours + (SELECT Hours FROM FactBillingFinal WHERE FactBillingId = @FactBillingId))
ELSE
CASE
WHEN @NewYearTotalHours IS NULL
THEN 0
ELSE @NewYearTotalHours
END
END
ELSE
CASE
WHEN @BillableType = 1 THEN (SELECT Hours FROM FactBillingFinal WHERE FactBillingId = @FactBillingId)
ELSE 0
END
END
END
END
UPDATE FactBillingFinal SET YearTotalHours = @NewYearTotalHours WHERE FactBillingId = @FactBillingId
SET @YearTotalHours = @NewYearTotalHours;
FETCH NEXT FROM FactBillingFinalRows
INTO @FactBillingId
END
CLOSE FactBillingFinalRows
DEALLOCATE FactBillingFinalRows
I have three tables, Users, DocType and Docs. In the DocType table there are multiple entries for allowed document types, the descriptions and other pertinent data. In the Docs table, there are all manner of documents. In the User table are the users.
The DocType and Docs tables are relational. DocType.ID = Docs.tID
The Users and Docs tables are relational. Users.ID = Docs.uID
Every user is allowed to have exactly one document of each type. Therefore if there are 10 document types in the DocType table, there may be as many as 10 matching documents in the Docs table.
What I need is a single record for each user returning a boolean for each document type, whether or not there is a matching record in the Docs table.
For example, there are 5 document types defined in the DocType table (types 1 - 5), so the DocType table has 5 rows. In the Docs table, there are 23 rows, and in the User table there are 10 rows. Given that each user may have only one of each DocType, there could be a maximum of 50 rows in the Docs table, but there are 23, meaning that on the average each user is missing one document.Now the challenge is to return a table of all the users (10 rows) with a boolean value for each of the rows in DocType (as columns) based on whether there is a value in the Docs table that matches both the DocType and User.
Script for finding row counts of two specific tables in each database of SQL in PIVOT format . e.g
Database || TableName1| Tablename2|| RowsCounts
Hi,
I have a table load which has load value for each hour.ie load_1,load_2...load_24... I want to find the max value between the 24 hourly loads and assign it to a variable say load_max...
Format of table
load_ID load_1 load_2 load_3 load_4 load_5 load_6...... load_24
1 2 4 5 6 7 8 23 56 44 22 64 33 67 24 345 34 75 57 24 23 24 24 66 789
These are the 24 load values with the load _id
I have lots of rows with load_id starting from 1- 100
Output should be to display the load_Id,load_max, load_min for each row...(after comparing the 24 loads with each other)
How can I do it with sql server.
Good afternoon,
I've here a shell plugin and it's compiling fine and can be viewed in BI Dev Studio when choosing the DM technique using the proper wizard.
I also have here a K-Means implementation that estimates the number of clusters using a statistical semi-empiric index (the PBM index).
This implementation is done in C# and works fine. But it has to receive all the data of the database (all variables for each row) in order to do the proper vectorial calculations in a CSR (Compact Sparse Rows) way.
Besides, as you know, K-Means needs all the data at once because of the clusters mean (centroid) calculation.
So, I have some questions:
1) Where to place the call to the K-Means implementation in the shell passing as argument an object holding all the data ?
2) After this call, with the data clustered, what other objects must be modified in order to use Microsoft Cluster Viewer ?
3) I will need to create a new column or a new table on the database to specify which data belongs to which cluster. Can I open an ADO connection as I normally do in other programs from inside the plugin or is there another (easier/better) way to do so ?
Thanks a lot once more.
Best regards,
-Renan Souza
Hey Guys,
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.
Thanks,
Surbha
How to pass dynamic values in xml path query?
WITH TEST AS (
SELECT TL.TERMINAL_ID,T.IP_ADDRESS, T.LOGICAL_CONNECT_STATUS, SI.SCHEDULER_ID,
SI.INSTRUCTION, SI.GROUP_ID, SI.MAX_READ_RETRIES, SI.DATA_CHAR, SI.SCHEDULE_TYPE,SI.FILEPATH_FLAG,
T.STATION_NAME,T.BANK_ID FROM SCHEDULERINFO SI Â
INNER JOIN TERMINALGROUP TGÂ ON SI.GROUP_ID = TG.GROUP_ID INNER JOIN TERMINALGROUPLINK TLÂ ON TG.GROUP_ID = TL.GROUP_ID
[Code] ....
I need to pass dynamic values in FOR SCHEDULER_ID COLUMN. Because I have huge data.
We are using MS Excel 2007 Pivot tables to access en SSAS 2005 Cube. Farly often when we reopen an excel spreadsheet with one or more pivot tables we get an error like this:
Excel found unreadable Content. Do you wish to repair
When we click yes the following log is shown:
Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition1.xml part (PivotTable cache)
Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition2.xml part (PivotTable cache)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable2.xml part (PivotTable view)
Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)
And all the pivot tables are converted to plain text.
I have read about this in KB 929766 but this do not apply since KPIs are not used.
The KB 943088 is more interesting but after upgrading all user with SP 1 the problem is still there, mostly when we open old excel-files that has been created without SP 1 but opened and saved once with SP 1. After that we are not able to open them at all, either with or without SP 1 installed.
Is there some way to €œsave€? pivot tables from destruction? I can€™t ask the users to rebuild all there spreadsheets that have been created prior SP1. What will happen when there is a new SP for Excel? Rebuild all spreadsheets and pivot tables again?
I work in a medical facility and and client confidentiality prevents loading my pivot tables to sharepoint at this time.
I am creating several reports that combine data from SQL Server, Cerner medical report DB and some lookup tables in Excel.
Everything works great on my desktop but I'm having trouble sharing my work.
Our normal routine is to drop a copy of our excel files in a Pass through file on the server that has strict access controls.
I think my main problem is my supervisor doesn't have powerpivot on his machine. that will be corrected tomorrow.
My question is: When close your powerpivot workbook, do all the connections go with it. Â If I just drop myproject.xlsx into the pass through will all the links to the varying data sources still be available?
Hi all,
I have the following tables
Tbl_Request
------------------------------------------------------------------------------------
RequestType NoOfPositionsRequired SkillCategory
Req1 10 .Net
Req2 3 Java
Req1 2 SQL
Req3 5 Java
----------------------------------------------------------------------------------
Tbl_User
------------------------------------------------------
ID SkillCategory Experienced
-----------------------------------------------------
101 Java 0
102 .Net 1
103 Java 1
104 SQL 1
105 .Net 0
106 J2EE 0
---------------------------------------------------
Experience is a bool column.
Required Output:
---------------------------------------------------------------------------------------------------------------------------------
SkillCategory Req1 Req2 Req3 TotalDemand Exp NonExp Total Supply
---------------------------------------------------------------------------------------------------------------------------------
.Net 12 0 0 12 1 1 2
Java 0 3 5 8 1 2 2
SQL 1 0 0 1 1 0 1
----------------------------------------------------------------------------------------------------------------------------------
Well the first half of it I am able to retrieve meaning the 'Demand' part by pivoting it from the table request and the next part i.e. 'Supply' is also obtained in the similar fashion.
Tbl_User may contain more skill categories than those mentioned in Tbl_Request. So the output should reflect only those categories that are existing in tbl_Request. How can we combine the both? I have taken both the outputs in two temp tables. Now I would like to know if I can combine them and show it as one output or if there is any other better way of doing it.
I am using a stored procedure which is called for my web application so I didn't go for views. Can someone tell me how to do it.
I have 2 tables below:
Table 1:
Product No Quantity
A 1
B 2
C 3
Table 2:
Product No Grade Quantity
A Good
 A Normal
 A Bad
 B Good
 B Bad
 C Good
C Normal
C Bad
In Table 2, Product No divided by Grade. I want to lookup the Quantity from Table 1 to Table 2. The same Product No will have 1 value, the other value is 0. The result for Column Quantity should be like this:
Table 2:
Product No Grade Quantity
A Good 1
A Normal 0
A Bad 0
B Good 2
B Bad 0
C Good 3
C Normal 0
C Bad 0
I am developing a database in PowerPivot and I am wondering how to create many relationships between the same 2 tables. All relationships must be active.
Let me give you a DUMMY example: let's say that the database has 2 tables, the Employee table and Manager table:
->Employee Table: Employee_name, Previous_Manager, Current_Manager
->Manager Table: Manager_Name
Because I have 2 manager fields in the employee table, I need to create 2 links between the employee and manager tables:
-> Link 1: Previous_Manager ---- Manager_Name
-> Link 2: Current_Manager ---- Manager_Name
Right now, one of the links is inactive...
Is there a way in PowerPivot to create 2 active links like that ?
I have Power Pivot version 11.0.3000.0 on Excel 2010 on Windows 7
hi,
i get just as frustrated each time i try to configure email alerts on failed jobs on ms sql, it is beyond me why microsoft couldn't just let you point out an SMTP server to send through and be done with it.
is there a way to avoid having to setup an email client on our sql 7 and 2000 servers through some 3rd party app or other simple solution?
thanks in advance,
daniel
I have this pivot table (I only post the static version as the problem only regards the single quotes)
SELECT * from(
select DATEPART(year,DeliverydatePackingSlip) as Year,
CASE WHEN DiffPromiseDateFirst < 0 Then '1 - too early'
WHEN DiffPromiseDateFirst = 0 Then '2 - on time'
ELSE '3 - too late' END as Delivery
from iq4bisprocess.FactOTDCustomer
WHERE OTD_Exclusion = 0)a
PIVOT ( COUNT(Year)
For Year
in ([2012],[2013],[2014],[2015])) as pvtNow, packing everything in a string parameter I always stumble over the single quotes. I tried to replace them with CHAR(39), I tried to define a parameter for each occurrence, but always get a syntax error. What am I doing wrong?declare @sql nvarchar(max)
declare @title1 nvarchar(20)
declare @title2 nvarchar(20)
declare @title3 nvarchar(20)
set @title1 = '1 - too early'
set @title2 = '2 - on time'
set @title3 = '3 - too late'
[Code] .....
exec sp_executesql @sqlThis would throw:Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'early'.
I have a table that is corrupted and want to remove and add a backup version of it. How can i remove this table and add it again preserving all the foregin key restraints, permissions, dependencies, etc? Simply exporting and importing does not work. I could painfully remove the table and then painfully reconnect it again, recreating all the foreign key restraints, etc, by hand; but there has to be an easier way! What is the How-to?
Thank you!
Llyal
I'm trying to figure out what solution (replication, mirroring, clustering) would work best for me.
I have been reading many articles in BOL and in this forum. Most talk about getting data TO a backup/standby/subscriber, but I can't find a lot of info regarding getting the data BACK after a disaster is over.
We have a main office and a disaster recovery facility. Most of the time there are no data updates at the disaster location. So, I need to get data to the disaster facility via WAN (latency is not a huge issue - end of day syncing is fine) for backup purposes. In the event of a disaster, the main office will be offline and data changes will happen at the disaster site. When the disaster is "over" and we return to the main office, what's the best scheme to reverse the data back to the main office to start business again? We are a financial company, and have gigabytes of relatively static data. Most changes are current day. So, to snapshot a 100GB database when I know only a few hundred MB changes a day doesn't seem feasible to me.
Most replication scenarios (at least from what I see) can't easily "reverse" the replication after a disaster situation. I'm looking at merge replication on a schedule which seems to look good, but was wondering if anyone else has any ideas or suggestions?
Greetings all,
I have a bit of brainteaser that's going to take some serious thought.
I'm importing information from .xls files into a SQL table. The problem is I need to check for dupes and increment certain fields on success of dupe find and then not insert or delete the dupes.
For example, if I have Adam, Turner, 32, 50 already in the table and someone tries to insert Adam, Turner, 32, 50...I need it to increment to read Adam, Turner, 64, 100 and not insert the record. (Notice 2 fields were incremented.)
With that, I have created an INSERT trigger as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Trigger [dbo].[trgInsertCheck]
ON [dbo].[MyTable]
FOR INSERT
AS
BEGIN
EXEC sp_UpdateDupes
EXEC sp_DeleteDupes
END
The first stored procedure checks for dupes and updates if any dupes are found as follows:
--------------------------------------------------------------------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_UpdateDupes] AS
DECLARE @FirstName varchar(20), @LastName varchar(20), @Age int, @Widgets int
DECLARE c1 CURSOR FOR
SELECT FirstName, LastName, Age, Widgets
FROM MyTable
GROUP BY FirstName, LastName, Age, Widgets
HAVING COUNT(*) > 1
OPEN c1
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE MyTable set Widgets = Widgets + @Widgets, Age = Age + @Age
WHERE FirstName = @FirstName AND LastName = @LastName
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
END
CLOSE c1
DEALLOCATE c1
Lastly, it finds all dupes, deletes them and inserts one row back in as follows:
--------------------------------------------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_DeleteDupes] AS
DECLARE @FirstName varchar(20), @LastName varchar(20), @Age int, @Widgets int --declare all fields in table
DECLARE c1 CURSOR FOR
SELECT FirstName, LastName, Age, Widgets
FROM MyTable
GROUP BY FirstName, LastName, Age, Widgets
HAVING COUNT(*) > 1
OPEN c1
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
WHILE @@FETCH_STATUS = 0
BEGIN
--Delete all dupes...the cursor remembers the current record
DELETE FROM MyTable
WHERE FirstName IN (SELECT FirstName FROM MyTable GROUP BY FirstName HAVING COUNT(FirstName) > 1)
AND LastName IN (SELECT LastName FROM MyTable GROUP BY LastName HAVING COUNT(LastName) > 1)
AND Age IN (SELECT Age FROM MyTable GROUP BY Age HAVING COUNT(Age) > 1)
AND Widgets IN (SELECT Widgets FROM MyTable GROUP BY Widgets HAVING COUNT(Widgets) > 1)
--insert the current record back into the table
INSERT INTO MyTable(FirstName, LastName, Age, Widgets) VALUES(@FirstName, @LastName, @Age, @Widgets)
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
END
CLOSE c1
DEALLOCATE c1
Is there an easier way to do this?
(I know Age doesn't make much sense in this example but just replace it with a field would logically be incremented such as wadgets.)
Adamus
When I was looking at this I knew that I've done this same issue before without using temp tables at all but I need a push to to jar it loose.
I have a table like this:
Balance
Date
1
200
2/14/2000
2
350
2/14/2000
3
32
2/14/2000
2
723
2/14/1998
3
354
2/14/1998
1
321
2/14/2000
2
673
2/14/1998
3
581
2/14/2000
2
574
2/14/1998
3
50
2/14/2000
1
10
2/14/2000
And essentially need this.
Total Balance Before 1/1/2000
Total Balance After 1/1/2000
1
0
531
2
1970
350
3
354
663
Right now I'm splitting it into two temp tables and then joining them together.
Temp Table 1 has the sum of the balance grouped by field1 before 1/1/2000 and Temp table 2 has the after 1/1/2000 criteria.
Table 1 gets updates with field1 values from table 2 that aren't already there. And then the balance field after 1/1/2000 is merged in.
Utimately this will be used in a SPROC for a Multivalued SSRS report.
I am trying to implement some powerview report in excel 2013 itself (not in sharepoint).
I have implemented a SSAS tabular model and deployed to server. Now my aim is to load this tables in the SSAS tabular database into powerpivot so that i can use this data for creating powerview reports. However when i tried to import data from SSAS, it asks database and MDX query. My requirement is to load the dimension and fact tables to power pivot as it is.
Dear all
I would like to produce a report on the data i have in my database probably in a pdf or excel or word document. What i have is SQL Server 2000 with .NET 1.1 platform.
Is there anything out there i could use?
I have done some work on reporting services but thats with SQL server 2005, but now i have to go a bit back.
Any advice?
cheers
I am building a site, I'd like to use SQL server, but once the site is finished it i for a organisation who do a lot of community voluntary work. So to keep the costs down I plan to install on their server a copy of MSDE as the datasource.
I know it is a simple question, but if I build the site using SQL server and deploy onto MSDE will the site still work?