I'd like to get the column ID_NumofAttach to be populated by the total number of ID_Attachlevel column by the same ID_BegAttach or ID_EndAttach and populate where ID_Attachlevel is 0.
We have some requirement in PowerBI reports. Here we have a table and having Date, Events columns. Below is the sample data..we are creating a measure to calculate the average of the event count for month.We need a measure for calculating Average of Event count per month= sum(Events for a month)/numberofdays in the month.Example for January month : sum(343423)/31 (31 number of days in January)
When we write this measure using DAX query in Excel we are getting semantic error.Tried sample formula : Average:=SUM([Events])/EOMONTH([EventDate],1)
writing this DAX command for measure.After having this data ready, we are creating PowerBI reports on this data.
I want find out Total number records in one table without using select statment. Some body as told to me there is system table you can find total number of records. Any body give me systable name. Thanks Jack
I want find out Total number records in one table without using select statment. Some body as told to me there is system table you can find total number of records. Any body give me systable name. Thanks Jack
I have a table of Customers & their data in about 20 Columns.
I have another table that has potential Customers with 3 Columns.
I want to append the records from Table 2 onto Table 1 to the Columns with the same names.
I've thought of using UNION ALL or Select Insert but I'm mainly stuck on the most efficient way to do this.
There is also no related field that can be used to join the data as these Customers in table 2 have no Customer ID yet as they're only potential Customers.
Can I just append the 3 columns from Table 2 to the same 3 columns in table 1?
HiI'm migrating from Access til MySQL.Works fine so far - but one thing is nearly killing me:I got the count of total records in a variabel - (antalRecords)I got the count for the Field Q1 where the value value is = 'nej'Now I just need to calculate how many % of my records have the value 'nej'I access this worked very fine - but with MySQL ( and ASP) I just cant getit right!!! I go crazy ....My code looks like this :strSQL="SELECT COUNT(Q1) AS Q1_nej FROM Tbl_evaluering " &_"WHERE Q1 = 'NEJ' "set RS = connection.Execute(strSQL)antal_nej = RS("Q1_nej")procent_nej = formatNumber((antal_nej),2)/antalrecords * 100Hope ...praying for help ...Please ;-)best wishes -Otto - Copenhagen
I have a challenge and I'm not sure the best route to go. Consider the following dataset.
I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.
I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month. I need to pull the difference of todays cumulative total less yesterdays. So when my total for today is 30,000 and yesterday's is 28,800, my sales for today would be 1,200. I want to write this to a new field but I just can't seen to get the net sales for the day. Here is some sample data. For daily sales for 6-24 I want to see 2,000, for 6-25 3,000, 6-26 3,500, and 6-27 3,500. I'm thinking a case when but can't seem to get it right.
I have a transformation where final result set give me 25 rows of data. Now before I put into destination table, I need to add another column which will show how many total records we have. Like.
My dataset:
A 20 abc B 24 mnp c 44 apq
Now I need to add another column within my transformation before I store the result set to destination like this:
A 20 abc 3 b 24 mnp 3 c 44 apq 3
Here. new column gives count of total rows in our dataset which was 3.
How can I achieve this? Can I use derive column to this?
If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015' DECLARE @enddate date = '10-31-2015' DECLARE @afterTime time = '17:00' SELECT General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
Our monitoring tool shows that our production system periodically experiencing large rate - up to 800 memory pages/sec. How to find out which particular queries, S.P., processes that initiate this?
Hi, I want to create a web application which will give the information of the total databases in a server or local machine. also I want to know about the number of tables, its properties in each databases. Pls help me.
Is it possible to assign to a variable, then add to it later on? When I run the below, all I get is 3 rows affected I never see the value printed. What i am wanting to do is each loop sum the numbers so 2+1+3 =6 so in the end @sumofallnumbers = 6 Create Table #Test ( randarnumbers int ) Insert Into #Test Values ('2'), ('1'), ('3') Declare @sumofallnumbers int, @nbr int Declare c1 Cursor For Select randarnumbers FROM #Test
declare @table table ( ParentID INT, ChildID INT, Value float ) INSERT INTO @table SELECT 1,1,1.2
[code]....
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending
I am building a query. I have a table with 4 columns and need to try and put the times together. There are some inconsistencies with this, and i'm hoping to exclude them.. Here is a sample table:
Function 1 = Clock In Type 1 Function 2 = Clock Out Type 1 Function 3 = Clock In Type 2 Function 4 = Clock Out Type 2
Basically what I need to do is take the time from rows with Function 1 and match it with Function 2 so I can get a total time of the clock in. Function 3 rows need to match up with Function 4 rows so I can get another set of total times. There may be more clock in rows then clock out rows or more clock out rows then clock in rows, and there may be multiple clock ins & outs per day per employee. I'm basically trying to get totals for each Clock In/Out type.
One of my measures is in seconds and I would like to convert it to hours.
It is simply a number in decimal format
the expression I tried is this [Measures].[OPEN DURATION] /60
and I ge this message Error 1 Errors in the metadata manager. The measure expression of the OPEN DURATION measure contains the [(60)] operand , which could not be resolved. 0 0
All help is appreciated, thank you in advance. I have tried a couple different syntax's with no luck.
aaaaa bbbbb date result ------- ------- -------- --------- a 100 7-7-15 a 100 7-7-15 a 100 8-7-15 b 200 7-7-15 b 200 8-7-15 b 200 8-7-15
I need this (divide bbbbb by row count as result, group by "aaaaa and date":
aaaaa bbbbb date result ------- ------- -------- --------- a 100 7-7-15 50 a 100 7-7-15 50 a 100 8-7-15 100 b 200 7-7-15 200 b 200 8-7-15 100 b 200 8-7-15 100
Can I do it?
My SQL Code is
(date=FEC, RESULT=COSTECUB, aaaaaa=TRACTA, bbbbb=TRAPR3) : SELECT VTRA00F.TRACTA, ACRDES, TRAPRL, TRAPR1, TRAPR2, TRAPR3, CASE WHEN TRAPR3 IS NULL THEN (CASE WHEN (ALLQDE+ALLQCN+ALLQCR+ALLQMR+ALLQNN+ALLQNR+ALLQV1+ALLQV2) <= TRAPRL THEN (TRAPR1*(ALLQDE+ALLQCN+ALLQCR+ALLQMR+ALLQNN+ALLQNR+ALLQV1+ALLQV2)) ELSE (TRAPR2*
(idAuxiliarPF(BIGINT+PK), pf(BIGINT+FK), Data(DateTime), Descr(NVARCHAR))that has aprox. 50k rows.
I need to create a partition of the data to join to another table, the query that i have:
SELECT ROW_NUMBER() OVER (PARTITION BY pf ORDER BY Data DESC, idAuxiliarPF DESC) AS RN, pf, Data, Descr FROM dbo.PFAuxiliar WHERE Data <= GETDATE()This query takes around 40 seconds to return the results
If i remove the Descr column, the query it takes no time.
SELECT ROW_NUMBER() OVER (PARTITION BY pf ORDER BY Data DESC, idAuxiliarPF DESC) AS RN, pf, Data FROM dbo.PFAuxiliar WHERE Data <= GETDATE()I have two indexes, Clustered (idAuxiliarPF), NONClustered(pf).
I have client table which has client_id Eff_from and Eff_to columns.Eff_from and Eff_to are the dates that client is eligible for service. I need to know the average number of days from the day that he became not eligible and new eligibility date .
I have a new problem with doing a restore of a number of databases using powershell. The script I'm using is based mainly on this one (Part 2 in particular): [URL] .....
The problem I'm having is around the RedgateGetDatabaseName function. My hunch is that its down to the different version of red gate and how sqlbackup works. Basically when the call is made to the function it is returning both the Database Name and the number of row's that the SQL command in the function has ran. I've tried to include a SET NOCOUNT ON at the start of the SQL command in the function but its still returning the now count.
How to get the result similar to Ex2, instead of Ex1. (ie., case-number is in sequential order then no need to break), And it should suit large dataset, I will finetune, if any performance issue.
I am working now on optimization of an update query for a particular table and I want to measure the number of page splits after each update. How to check it?
We have a big software that run a warehouse distribution center, written in .NET Backhand is a SQL Server 2008 R2 STD database.
Now, it seems there is a problem with the sessions not being properly closed after each call to the DB. Here is the message got form SQL:
DESCRIPTION:A new connection was rejected because the maximum number of connections on session ID 57 has been reached. Close an existing connection on this session and retry.
In the .NET code, connection is made with the following code:
If oConn Is Nothing Then oConn = New SqlConnection If oConn.State = ConnectionState.Open Then oConn.Close() With oConn .ConnectionString = "Server=" & Server & ";Database=" & DB & ";User ID=" & User & ";Password=" & Pass & ";Connection Timeout=" & 5 & ";MultipleActiveResultSets=" & True .Open() End With
This code is called once, at the opening of the software
StoredProc are call with the code: Try oCmd = New SqlCommand With oCmd .Connection = oConn .CommandType = CommandType.StoredProcedure
[Code] .....
So every Command is closed after execution, yet, they stay active in the SQL Server. Is there something I'm missing here?
I have a table where I would like to update the document number row for 3k rows. The problem I have is that the documents come in sets of two (version 1 and 2) but both have different numbers. Picture it like this below:
DOCNUM: 4445787 Version 1 DOCNUM: 4445790 Version 2
It should be the same docnum (ie 4445787 Version 1, 4445787 Version 2).
The challenge is how can we assign the new docnum for version 1 to be also for version 2 as well. Basically in SQL we need a way to
1. Find a way to distinguish the pair of documents in the target db that are the same even though they have different docnums.
I have a table of customer sales (dbo].[CustomerSales] which has a complete record od sales of our products for the past 5 years. I would like to create a report that extracts all the sales per customer on one row ie [CustomerID] 00011 has had 25 sales in the past 5 years and I would like to have their sales from left to right (starting with their earliest sale date at the beginning [SaleDate] field on the one row. I would also like to include the store location field [Location] along with the date the sale took place. So in other words the extract would look something like: