Dynamic View - Add A Column To Display Running Count In Table
Jul 22, 2014
I have a view created from only one table.
VW_Stats ( Tab_Name,Load_Status,User,....)
Tab_Name & Load_Status columns provides the information about Name of table and status of data getting loaded to this table.
To this I would like to add a column to display the running count in the table, I mean how many records so far loaded, as below the recordCount coming from the same table tbl_collection
How would I list the users in the users table that have duplicate IDs or count of IDs > 1?The UserName field is unique. State UserName First Last ID City CountTX Kkeaton Kathryn Keaton 1001 Dallas 2TX KakiKeaton Kathryn Keaton 1001 Dallas 2I think I have to use a subselect? If I use group by then it won't show both records. It shows only one of them.Thanks Craig
I am displaying the red line in the beginning of the current week column in the matrix table. But for few resources the red line is missing. For Example in below screenshot, refer the 'Adrian Larkin' resource. Here is the SSRS report border expression which is comparing the [TimeByDay] with the current Week First Monday Date to display the red bar -
Here is the dataset result set. If you refer the column [TimeByDay], it has no record for the date 11/05/2015 for the Adrian resource as he has no assignment for the date 11-05-2015.display the red missing line red bar using the expression or any other solution.
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501 SELECT 1,2,3,4,'CurrentSetting' FROM TableA ta WHERE tblid = @tblid UNION SELECT 1,2,3,4,'PreviosSetting' FROM Tableb tb WHERE tblid = @tblid
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?
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);
I have created a trigger in SQLExpress which looking for any insertion to a specific table in a database:
Create Trigger Tg_Insert on [tablename] FOR Insert AS Select * FROM Inserted --------------------------------------------------------- Assume there is nothing wrong with this trigger as I have already checked the output with Query tool. My question is how can I display the newly inserted data to the client? In my case, Console application. I want to do something like Console.WriteLine("data from inserted table here"); How can I achieve this? FYI, I'm using VS2005 with sqlexpress 2005. Thanks in advance.
Hi,please help me, i have a data driven site,i need to display a table who look like this Name Total Used RemainingA 10 5 5B 20 13 7C 12 12 if you would look on the field of total, used and remaining, they look like an excel formula which is total - used = remaining or vise versa.but the problem in this table is the database, it look like thisname On/OffA 1A 1A 0and soon....... B 1B 0B 1and soon.... until C so how do i count the total, is the number of A's or B's or C's in the whole database then Group By the 'name' fieldthen how do i count the used is the number of ON in the On/Off field while remaining is counting the Off,so my question is i have RUN this using 3 different gridview each has its own datasource, that look like this for the used select name, count(on/off) from myTable where on/off = 1 group by name for the remaining select name, count(on/off) from myTable where on/off = 0 group by name and for the total, just used the same Select but without the where clause,i get this problem when the field "used" (please refer from the 1st table) it has a blank value, which is because my data base show that in deed name "C" has not been used, but eve though it should show '0' instead of a blank, so how can i do that???please help me, thanks
I am having two table i.e( tbl_oldEmployee , tbl_NewEmployee ),which is having Column name Employee Name and City same in both table but inside column data is different in different table.but i want to view the Employee name and City from tbl_NewEmployee to tbl_oldEmployee which is having EmployeeId common with tbl_oldEmployee extra record also required (i.e tbl_NewEmployee having 6 record and tbl_oldEmployee having 10 record,so i need to display data from tbl_oldEmployee but first 6 record which id match with tbl_NewEmployee id should be replaced and extra data from tbl_oldEmployee also display).
I have a table with a column AttributeNumber and a column AttributeValue. The data is like this:
OrderNo. AttributeNumber AttributeValue 1.-Order_1 2001 A 2.-Order_1 2002 B 3.-Order_1 2003 C 4.-Order_2 2001 A 5.-Order_2 2002 B 6.-Order_2 2003 C
So the logic is as follows:
I need to display in my query the values are coming from Order_1, means AttributreValues coming from AttibuteNumbers: 2001,2002,2003...and Order_2 the same thing.
Not sure how to create my Select here since the values are in the same table
I have a table for blog comments I want to add a column that counts the number of comments for each article. existing table looks like this: CommentID ArticleID (FK) commentAuthor authorEmail comment commentDate (getDate()) I would like to add a column that counts the number of total comments for each article.This will give me what I want using the VS query tool: "SELECT COUNT(comment) AS Expr1 FROM UserComments WHERE (articleid = @articleid)" But can I add that to the table somehow so it does it automagically???
I m using SQL Server 2000. I have Tabel named Topic and have a column name lineage. lineage has data like following: //////546707//546707//546707/43213/ Now I want to get records who has only one "/" in it's crreponding lineage column. Can somone tell me how to do that in SQL Server 2000? Thanks Khushbu
My goal is to with one update statement, fill TABLE1.counter (currently empty) with the total count of TABLE2.e# (employee). Also, if TABLE1.e# isn't in TABLE2.e# then it sets it to "0" (TABLE1.e# 8 and 9 should have a counter of 0) This is for sqlplus.
e.g. TABLE2:
e# -- 1 2 3 4 5 5 6 7 7 1 2 3 4 5 UPDATE TABLE1 SET counter = ( SELECT COUNT(TABLE2.e#) FROM TABLE2 INNER JOIN TABLE1 ON (TABLE2.e# = TABLE1.e#) GROUP BY TABLE2.e#);
i have a table with productID and OrderID. For ech product there are orders. So for each productID there are lot Of OrderID's are present. My data like
The boss has decided that the data from a table we have on one database (containing daily data) needs to be copied to a "history" table on a different database, on a different server.
The transfer will probably be done with a scheduled stored procedure, and all columns will be transferred EXCEPT for two columns in the source table, which are not present in the destination table. This means instead of an "INSERT Dest SELECT * FROM Source" I have to do an "INSERT Dest SELECT yada,yada,yada... FROM Source" in order to disregard the columns we don't care about. NO problem.
I was thinking (you should smell trouble - and sawdust burning at this point) that this leaves us open to a punch below the belt later when a new column is added to the Source table, since we are using a definitive list of columns to move rather than a *, and the new column could be added without the otherwise system-generated, *-sponsored "gentle reminder" that it also needs to be added to the Dest table. I'm not as convinced as The Boss that "Oh, I'm sure we'll remember to add it to the history table when that happens" :lol:
So, long story shorter (nah...too late for that) - I figure I can write a check at each day's historical transfer on the count of columns in one table verses the other, and send an email or fail the job if the count doesn't make sense.
For example, if the Source table has 34 columns, since we don't care about two of them, the Dest table should have 32 columns, if things are still in synch. OK, I think (insert burning sawdust smell here), I can use a SysObjects thang to count rows. BOL points out INFORMATION_SCHEMA.COLUMNS as a possibility.
Here is the code I think (sawdust) can be used:select ((select count(*) from SourceDb.INFORMATION_SCHEMA.columns where table_name = 'Source') - (select count(*) from HISTSERVER.DestDb.INFORMATION_SCHEMA.columns where table_name = 'Dest')) as ColumnDiff
Trouble is...this fails because apparently the INFORMATION_SCHEMA thang doesn't do well with remote servers.
Can anyone figure a way around this, or suggest an alternative? I'm still looking, but thought I'd also toss it out onto the board for your generous consideration.
Thanks - and can you BELIEVE the year is already 1/12th of the way OVER?!?!?! Paul
how count column in pivot table- and add result row i need to calculate each column for example day1 day2 day3 day4 day5 ------------------------------------------------------------------------- 1 2 1 2 3 1 2 3 2 2 2 3 2 1 2 2 3 0 0 0 -----------------------------------------------------------new result row ok ok 1|2|3 1 3
i need to check each column if i have twice each number if not show the missing number TNX
Code Block DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT) DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP @BaseDate SMALLDATETIME, @NumDays TINYINT SELECT @WantedDate = '20080301', -- User supplied parameter value @BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'), @NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))
IF @Numdays > 28 BEGIN SELECT p.ID, p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11], p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21], p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30], p.[31] FROM ( SELECT ID, DATEPART(DAY, Date) AS theDay, ShiftID FROM v_Employee WHERE Date >= @BaseDate AND Date < DATEADD(MONTH, 1, @BaseDate) ) 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], [31]) ) AS p END
I am having hard time to populate dynamic column based on parameters.For eg. Parameter-->Dates : Date(1) ,Date(2),Date(3)....Date (n) (Multiple-Parameters is selected by drop down list ) Report format: Id | Currency |Country |District |Date(1)- transaction |Date(2)-transaction |Date(n) 1 | $$ |USA |123 |523238 |643763573 |62376264
Now requirement is If user enter 1 date then only single column date(1) populate if enter (n) dates then n column Date(1) ,Date(2),Date(3)....Date (n) populate. kindly suggest me any solution requirement.
I tried to bind Visibility property of column with Parameters passed.So that user can see only those column for which they pass values.I am getting error "Index was outside the bounds of the array"
I write this expression under Column(Date)-->Property-->visibility --> hidden--> expression =iif(len(Parameters!VALUEDATE.Value(1)) > "0",false,true) Kindly suggest any working solution .Thank you
I am trying to work on a database with 3 tables. To make it easier I have created a couple of temp tables to work out the syntax.
CREATE TABLE #owner ( [NameId] tinyint IDENTITY(1,1) NOT NULL, [Name] varchar(50) NOT NULL )
INSERT INTO #owner VALUES ('ME'); INSERT INTO #owner VALUES ('Other');
CREATE TABLE #propertyType ( [TypeId] tinyint IDENTITY(1,1) NOT NULL, [Name] varchar(50) NOT NULL )
INSERT INTO #propertyType VALUES ('Home'); INSERT INTO #propertyType VALUES ('Car');
CREATE TABLE #property ( [NameId] tinyint NOT NULL, [TypeId] tinyint NOT NULL, [Value] varchar(50) NOT NULL )
INSERT INTO #property VALUES (1,1, 'Blue'); INSERT INTO #property VALUES (1,2, 'Black'); INSERT INTO #property VALUES (2,1, 'Red'); INSERT INTO #property VALUES (2,2, 'Black');
DROP TABLE #owner; DROP TABLE #propertyType; DROP TABLE #property
| NameId | Name | | 1 | ME| | 2 | other |
| TypeId | Name | | 1 | Home | | 2 | Car |
| NameId | TypeId | Value | | 1 | 1 | Blue | | 1 | 2 | Black | | 2 | 1 | Red | | 2 | 2 | Black |
Where property value is some arbitrary detail. The real propertyType has 50 or 60 rows and not every property has all of the values. I am trying to create a pivot table that would look like so that I can present the data in an easier to understand format:
[Owner | Home | Car ] [ME | Blue | Black ] [Other| Red | Black ]
The propertyTypes are added often, and I don't really have the ability to change them. There is a unique constrant on property on nameid and typeid so there will never be two of the same property with the same owner. Any help would be very helpful.
I'm hoping someone can help with with a task I've been given. I need to write a trigger which will act effectively as a method of automatically distributing of incoming call ticket records. See DDL below for creation of the Assignment table, which holds information on the call ticket workload.
SELECT COUNT(CallID) AS [Total Calls], AssignmentGroup, Assignee FROM #Assignment GROUP BY AssignmentGroup, Assignee ORDER BY COUNT(CallID) DESC , AssignmentGroup, Assignee
What I need to do is write a trigger for on INSERT to automatically update the Assignee column with the name of the person who currently has the least active calls. For example, using the data above, the next PC Support call will go to Mickey Mouse, and the next two Service Desk calls will go to Jim Smith.
So, the logic for the trigger would be
UPDATE #Assignment SET Assignee = (SELECT Assignee FROM #Assignment WHERE COUNT(CallID) = MIN(COUNT(CallID))
But that's only the logic, and obviously it doesn't work with the syntax being nothing like correct.
Does any one have an idea or pointers as to how I should go about this?
I have added a Slowly Changing Dimension transformation to an SSIS package and have launched the Wizard to edit it. After selecting the source (a SQL Server 2005 instance), if I select a very large table (9+ million rows), I'm encountering two strange behaviors: 1. The wizard hangs for several minutes before displaying the columns from that table. 2. The wizard does not display the primary key column. This, of course, is the column I want to designate as the "business key", but can't because it's not displayed. I know this is more like a fact table than a dimension, but this is not a data warehouse. This is just a very large table, and I need to update a field in certain records based on the contents of a source text file. Is there another transformation I should use to perform updates?
Not sure if this is ever going to be possible, but I'm trying to do a dynamic join on EXECUTE, but the execution string has to come from a table column.... i.e
select * from (select table1.theSql,table1.userid from table1 ) as a
Is there any way to get the base table/column from a view by supplying the view name/column name_in_view? I know about INFORMATION_SCHEMA.VIEW_COLUMN_USAGE, but it doesn't always work. Try it on a regular database, not pubs. Any help is appreciated!
I am using SqlServer 2005 and I have created small sample table with culumns that have Extended properties. FirstName have [First Name] extended propery called M_Caption. Then I created a view from the table to use in an access database. My problem is, I can't get the table column extended properties to show up in the view column extended properties.
Here is the table
/****** Object: Table [dbo].[Person] Script Date: 04/07/2008 14:42:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Person]( [Id] [int] NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Caption', @value=N'First Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'FirstName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Caption', @value=N'Last Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'LastName'
I created this view
/****** Object: View [dbo].[View_1] Script Date: 04/07/2008 14:50:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[View_1] AS SELECT FirstName, LastName FROM dbo.Person GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[40] 4[20] 2[20] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = "Person" Begin Extent = Top = 6 Left = 38 Bottom = 99 Right = 189 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 1440 Alias = 900 Table = 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOrder = 1410 GroupBy = 1350 Filter = 1350 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1'
However the issue is with [1],[2],[3] columns. Those are the number of days of the month. If today is the 3rd day of the month, we only need to show 3 days. So the final table has column [1],[2],[3] and @AccountType and @Total .
We want to run this query everyday to get the moth to date values.If we run this tomorrow, it will have 4 date columns [1], [2],[3],[4] and @AccountType and @Total .
I want to display the number of records for each state in a database. I'm using a strongly types dataset. In my method, I have the followingSELECT COUNT(ID) AS iTotal, STATEFROM membersWHERE (Suspend = 0)GROUP BY STATEORDER BY STATEIn the code behind of my page I have Dim mateAdapter As New WAPTableAdapters.membersTableAdapter Dim mates As WAP.membersDataTable Dim mate As WAP.membersRow mates = mateAdapter.GetDataState For Each mate In mates Select Case mate.STATE Case "AK" LabelAK.Text = mate.ID. End Select Next What should LabelAK.Text = mate.ID. be for me to be able to display the number of records that have 'AK' in the state field?Diane
Hi, I have a table that I insert a member's country into every timesomeone signs up. What I'd like to do is pull information from the DBsuch that I can see each country and the number of users from each.For example:Argentina 10Brazil 5Canada 3I'm having trouble writing the SQL for this...any suggestions?Thanks,Erik
SELECT name = SUBSTRING(name, 1, 1), total = COUNT(SUBSTRING(name, 1, 1)) FROM products GROUP BY SUBSTRING(name, 1, 1)
the assignment said that,
quote:"Do not display the letter and count unless at least three product names begin with the letter."
but i got the errors when I try following.
WHERE total >= 3
quote:Msg 207, Level 16, State 1, Line 4 Invalid column name 'total'.
WHERE COUNT(SUBSTRING(name, 1, 1)) >= 3
quote:Msg 147, Level 15, State 1, Line 1 An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
