Transact SQL :: How To Sum Records In Series
Dec 3, 2015
I have the following store procedure which list a certain amount of product
SELECT P.Name, P.ProductCode, P.Quantity, P.ItemUnitWeight, P.Weight, P.WeightUnitMeasureID, P.IsDeleted, UNIT.UnitMeasureCode, P.StoreID, dbo.v_ProductItemWeight.TotalWeight,
dbo.v_ProductItemWeight.UsedWeight, Production.ProductCategory.Name AS CategoryName, Production.ProductCategory.ProductCategoryID, Production.ProductItem.Quantity AS UnitQuantity,
Production.ProductItem.UsedQuantity
FROM Production.Product AS P INNER JOIN
[Code] ....
This store procedure produce the following result set :
Result Set
From the result set above, in fact what I need to get is a single records for each ProductCode and adding 2 fiels which are the Sum(UNitQuantity) and the Sum(UsedQuantity)
How can I add those 2 field in my querry and group it by ProductCode ?
View 13 Replies
ADVERTISEMENT
Mar 17, 2014
I have a series of records based on empid where I want to identify the empid that may have discrepancies listed. I have some empids that are listed more than once and have different DOB's. In the example I am trying to Create a DOB_ERROR column and either say yes if the DOB doesn't match the other records in the file with the same empid.
SELECT
Empid,
DOB,
CASE WHEN DOB = DOB THEN 'No' ELSE 'Yes' END AS DOB_ERROR,
City,
St,
Gender
FROM Emp
WHERE EMPID IN
('12335', '23456', '545432','231245')
View 3 Replies
View Related
May 15, 2015
i have a time series table which generates a flag for every 10 mins. the table looks as below. I am trying to write a sql query which gives me the start date and end date of a particular flag, each time the flag starts. i tried to do it using cursors and actually joining the table to itself based on a 10 min offset but couldn't get the required results.
startdate enddate
flag
2009-11-02 23:30 2009-11-02 23:50
-999
2009-12-08 04:50 2009-12-08 04:50 -990
View 3 Replies
View Related
May 31, 2007
Hi, all experts here,
Thank you very much for your kind attention.
I encountered a very strange problem again. Why the time series displayed on the chart are so strange? The Key time column I chose for my time series algorithm is cal_month(e.g 199001...), but why the date displayed on the time series chart is like :05/06/2448? (it should be like 199001..?) What is that data? And where exactly did it come from? What is the exact cause of this?
Hope it is clear for your help.
I am really confused on this and thanks a lot for your kind advices and help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 1 Replies
View Related
Oct 21, 2015
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT
Student.StudentSurname + ', ' + Student.StudentForename AS Fullname,
HomeworkLogLine.HomeworkLogLineTimestamp,
HomeworkLog.HomeworkLogDescription,
ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).
View 3 Replies
View Related
Oct 28, 2015
I have a big table which has fields(ID, RecordDate, Value). The records were 15 minutes interval, start at yyyy-mm-dd 00:00:00 and end at yyyy-mm-dd 23:45:00 for each day. Some records may be missing.how can I do the query:if the every 15 min records exists in the day, do sum(value) for the day
else do something else.I would like see difference between SQL 2008 and 2012.
View 8 Replies
View Related
Oct 1, 2015
I have 2 tables:
create table #InsuredLoc (
PolGK int,
InsuredLocGK varchar(20),
InsuredLocEffDt datetime,
InsuredLocType varchar(20),
InsuredLocStatus varchar(20),
InsuredLocAddress varchar(50),
PolEdrGk varchar(20),
[Code] ...
I will need to join the #InsuredLoc table to the #PolicyEndorsement table using PolGk and PolEdrGk and get the min(BkgDt) and min(PolEdrRowUpdateDt) for the distinct list of InsuredLocType, InsuredLocStatus, and InsuredLocAddress fields from the #InsuredLoc table above.
I will also need the min(InsuredLocEffDt) and the max(InsuredLocUpdateDt) from the #InsuredLoc table for those records. So after the first run, i should get the following:
I tried to use CTE's with ranking, but some records are dropping off and I'm not sure why.
View 6 Replies
View Related
Oct 11, 2015
I want to know one small query..
id Name
1 hi
2 how
3 are
4 you
6 can
7 do
8 not
9 did
10 to
I deleted some records now my table have below mentioned rows..
id Name
1 hi
2 how
4 you
6 can
8 not
10 to
I want to know the missing records in my table.
OUTPUT IS. 3,7,9
how can i do that using sql query.
View 11 Replies
View Related
Jul 23, 2015
In my database I have an Audit table, that keeps track of teams worked upon the same record in a workflow.I need to find out how many records have been returned to the first team for correction ?The column 'Status' is numbered from 1 to 6 and Column 'EditTime' saves the time when record has been edited.how many records have been returned for correction and identify those records.
View 9 Replies
View Related
Jun 1, 2015
IF OBJECT_ID('dbo.AuditLog','U') IS NULL BEGIN
CREATE TABLE [dbo].[AuditLog]
[code]....
[AuditLogId] [bigint] IDENTITY(1,1) NOT NULL,
[DateCreated] [datetime] NOT NULL,
[TableName] [nvarchar](200) NULL,
i have the Audit_log table and the trigger above on Account table..it is working fine and i have some 1000 records in Audit log table , but it did not fire for some customers and i found trigger did not fire for 60 plus customers any possible reasons ??? that trigger did not fire ? Below are the timings account have been created but trigger did not log them
2015-04-29 10:22:08.243
2015-04-29 11:01:49.150
2015-04-29 11:08:16.617
2015-04-29 11:40:34.407
[code]...
View 17 Replies
View Related
Sep 2, 2015
I have a stored procedure that attempts to INSERT @BatchSize number of records at a time into a table. Currently, I have @BatchSize set to load 50,000 at a time. The table I am inserting from has a little over 67,000 records.
When I execute the procedure with NOCOUNT left off, the procedure seems to run indefinitely, and the count of records returned surpasses what I have in the source table. However, only 50,000 records are inserted into the table.
Below is my code:
begin try
--error catching variables:
declare @Error_NumberLocal int
,@Error_MessageLocal varchar(4000)
,@Error_SeverityLocal int
,@Error_StateLocal int
,@Error_ProcedureLocal varchar(200)
,@Error_LineLocal int
,@User_NameLocal varchar(200)
[code]....
What is the problem with the looping structure that would cause this issue?
View 6 Replies
View Related
Aug 13, 2015
I have a table with 5 columns, let say ID,PersionID, Date, Type,Qty and source data looks like this
ID PersonID Date Type Qty
1 1 01/01/2011 Accept 5
2 1 01/01/2011 Accept 5
3 2 02/01/2010 Accept 10
4 2 02/01/2010 Deny 20
5 3 02/01/2012 Accept 15
[Code] .....
Output should look like this..look for only Type=Accept until deny is reached. After Deny,if there is a Accept ignore it.
ID PersonID Date Type Qty
1 1 01/01/2011 Accept 5 (show only one Accept row=1 becoz Type is Accept and date is same,Qtyis
same)
3 2 02/01/2010 Accept 10 (show Accept row=3,ignore deny row)
5 3 02/01/2012 Accept 15 (show Accept row=5)
6 4 05/05/2012 Accept 25 (show Accept rows=6,7 and ignore Deny & Accept rows = 8,9)
7 4 07/08/2012 Accept 20
11 6 01/01/2011 Accept 5 (show Accept rows=11,12 because Qty is different)
12 6 01/01/2011 Accept 15
Create Sample Table (ID int null, PersonID Int null, Date Datetime null , Type varchar(10) null, Qty int null)
Insert into sample values (1 ,1,'01/01/2011','Accept',5),
(2,1,'01/01/2011','Accept',5),
(3,2,'02/01/2010','Accept',10),
(4,2,'02/01/2010','Deny',20),
(5,3,'02/01/2012','Accept',15),
(6,4,'05/05/2012','Accept',25),
(7,4,'07/08/2012','Accept',20),
(8,4,'07/08/2012','Deny',5),
(9,4,'09/23/2012','Accept',23),
(10,5,'09/08/2012','Deny',12),
(11,6,'01/01/2011','Accept',5),
(12,6,'01/01/2011','Accept',15)
View 4 Replies
View Related
May 7, 2015
I would like to use the following code for querying summary records with paging.
DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 1
SET @RowspPage = 10
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY create_date) AS NUMBER,
* FROM summary
) AS TBL
WHERE NUMBER BETWEEN ((@PageNumber - 1) * @RowspPage + 1) AND (@PageNumber * @RowspPage)
ORDER BY create_date
Paging is implemented for fast response since the data pool is very large up to 10000000.
The above query works fine in testing. However, in reality, since new records are keep inserting to the tables, I have concern about the accuracy of viewing another page of result.
E.g. At 12:00pm, the result of page 1 (5 per page) is
R20, R19, R18, R17, R16
After 2 mins, 12:02pm, the user press next page button
Since records R21, R22, R23, R24, R25, R26 are inserted
page 2 result would be R21, R20, R19, R18, R17
So the result is showing many records same as page 1 which has already been seen. Could this situation be improved?
View 2 Replies
View Related
Apr 22, 2015
I have one table with many records in the table. Each time a record is entered the date the record was entered is also saved in the table. I need a query that will find all the missing records in the table. So if I have in my table:
ID Date Location
1 4/1/2015 bld1
2 4/2/2015 bld1
3 4/4/2015 bld1
I want to run a query like
Select Date, Location FROM [table] WHERE (Date Between '4/1/2015' and '4/4/2015') and (Location = bld1)
WHERE Date not in
(Select Date, Location FROM [table])
And the results would be:
4/3/2015 bld1
View 17 Replies
View Related
Aug 13, 2015
I have following query which is created dynamically as -
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1889147436' AND SEND_DATE = '1941-03-04';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1115509374' AND SEND_DATE = '1991-09-01';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1515579671' AND SEND_DATE = '1941-05-24';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1795509670' AND SEND_DATE = '1958-01-14';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1915508672' AND SEND_DATE = '1961-09-07';
Here till " UPDATE BUILDTABLE SET BUILD_ID = '984137'" is the same clause for all queries, but "where" condition is different for all queries. I have to update more than 500 UPDATE statements(like above) in one call. Currently I am concatenating all the queries in string Builder which is time consuming.I want to increase performance of application.Any other class like BulkCopy ?
View 18 Replies
View Related
Apr 25, 2015
Am witnessing a very strange issue when i try to insert some records . I get the message in the SSMS like 5,10 etc rows affected. When i check for the same records in the table none of them is present.
This problem then automatically goes away after some time and all get backs to normal. I only have the access to that database no other user have the access. Totally confused about the all of a sudden new behavior of the database.
View 10 Replies
View Related
Aug 22, 2015
My table is test and I have an ID and DateTest columns
I would like to count the weeks with more then one record.
So far I got this and return the weeks with 1 record per week. How can I count the weeks with more then one record
select sum(c)
from (
select c = count( id) over (partition by id, datepart(week, DateTest))
from test where id = '1' and DateTest >= '7-7-2015'
) a where c = 1
View 11 Replies
View Related
Oct 2, 2015
I have a table that have student names and their birth dates just like below
a) tblStudentInfo
which shows records like below (Birth Dates are shown in Year-Month-Day format)
ID StudentName BirthDate
1 ABC 2002-12-25
2 DEF 2002-09-10
3 GHI 2002-09-19
4 JKL 2002-06-10
[code]...
I want to perform a query that should display the upcoming birthday of all students according to today.Lets say, today is 2015-10-02, so the query should display the result according to today's date just like below
ID StudentName BirthDate
1 ABC 2002-12-25
2 VWX 2002-01-01
3 STU 2002-02-03
4 PQR 2002-03-05
[code]...
View 3 Replies
View Related
Oct 26, 2015
I am working on one logic. I have a table as below.
Declare @Table table (ID int, StartDate datetime, EndDate datetime)
Insert Into @Table (ID, StartDate, EndDate)
Values (1, '2013-01-01', '2013-12-31')
, (1, '2014-01-01', '2014-06-30')
, (2, '2014-01-01', '2014-07-31')
, (2, '2014-08-02', '2014-08-30')
select * from @Table
I need as below.
ID, StartDate, EndDate
1, '2013-01-01', '2014-06-30'
2, '2014-01-01', '2014-07-31'
2, '2014-08-02', '2014-08-30'
Means If ID is same for two or more than two records then difference between first row's EndDate and second row's StartDate is 1 day then we should get one record as output. How can we built this logic in T-SQL ?
View 12 Replies
View Related
Sep 3, 2015
How do I align output from two columns into a single column? Below are the DDL/DML as required with sample data.
CREATE TABLE #Users(
UserID VARCHAR(7),
G1 VARCHAR(30),
G2 VARCHAR(30),
G3 VARCHAR(30),
[code]....
View 5 Replies
View Related
Sep 29, 2015
I need to compare records between two tables. There is no ID in the tables to do a simple join between them. So, what I'm looking for is: get the first record from table1 and read all record from table2 and give me back the most similar record. The String Distance is a predefined function.
Select a.table1
,b.table2
from table1 a, table2 b
where StringDistance (''a.table1,'b.table2') >90
View 4 Replies
View Related
Oct 22, 2015
Every day the timestamp is changed on all rows in one of the table(table has 1 mio records).
What is the best way to find out which query is doing this?
Could be also query which updates only one record(or couple of them) but is executed for each record once.
I have been looking sys.dm_exec_query_stats and sys.dm_exec_sql_text for past but couldn't find.
I can't use event notifications since there is service broker disabled.
View 5 Replies
View Related
Sep 14, 2004
Hi all,
I have to insert records using transact iin the stored procedure. I have some thing like:
DECLARE @Err varchar(100)
DECLARE insertQ varchar(1000)
SET @Err = 'Insertion data'
SET @insertQ =('INSERT INTO dbo.T_ERRORLOG (ERROR_DESCR) VALUES(' + @Err + ')')
EXEC insertQ
But it don't work. Can you help me to solve the problem plz?
Thanks a lot
TT
View 4 Replies
View Related
Nov 4, 2015
I would like to use sp_send_dbmail, but I only want to send mail if there are any records returned.
I have found some solutions, but you must always first check if there is any record and later you can call "sp_send_dbmail" and within you must again query database for results.
What I want to do is to query database just once, because I dont want to use server performance two times. Query is bit complicated.
View 11 Replies
View Related
Nov 18, 2015
How can I prevent duplicate records in the query
With
Property AS
(
SELECT
*
FROM dbo.MulkiyetBase
),
Document AS
(
SELECT
[code]....
View 4 Replies
View Related
Nov 22, 2015
I have a question about SQL Server.
Table patient:
create table patient (pn int,code int,date date,doctorcode int)
insert into patient (pn,code,date,doctorcode)
values
(1,10,'2015-02-19','100),
(1,10,'2015-02-19','101),
(1,10,'2015-02-19','102),
[Code] ...
Table Patientref:
create table patientref
(pn int,code int, sdate date,edate date,status int)
insert into patientref(pn,code,sdate,edate,status)
values
(1,10,'2015-02-13','2015-02-19',1),
(1,10,'2015-02-19','2015-03-24',2),
[Code] ...
Here we need consider patient dates that fall between sdate and edate of the patientrefs table, and then we need to consider the highest status values in order (for example, the highest values in order - 2 is first highest, 4 is second highest, 3 is third highest, and 1 is fourth highest value)
If the date falls between multiple different sdate and edate with the same status values, then we need to consider the latest sdate value and from that entire record we need to extract that value.
Examples: patient
pn | code | date | doctorcode
2 | 10 |2015-02-12 | 101
2 | 10 |2015-02-13 | 102
2 | 10 |2015-02-14 | 103
Table : Patientref:
pn | code | sdate | edate | Status
2 | 10 |2015-02-08 | 2015-02-19 | 4
2 | 10 |2015-02-09 | 2015-02-19 | 2
2 | 10 |2015-02-10 | 2015-02-19 | 2
2 | 10 |2015-02-11 | 2015-02-18 | 1
Here, pn=2 values have dates which fall between sdate and edate of patientref table. Then we give highest values status is 2, and status 2 values have two records, then we go for max sdate(latest sdate). Then this pn=2 latest sdates is 2015-02-10 and we need to retrieve the corresponding edate and status values.
pn = 4donot have sdate and edate and status values dut not fall conditon
Based on this, the desired output is below:
pn | code | date | doctorcode | sdate |edate |status
1 | 10 |2015-02-19 | 100 |2015-02-19 |2015-03-24 | 2
1 | 10 |2015-02-19 | 101 |2015-02-19 |2015-03-24 | 2
1 | 10 |2015-02-19 | 102 |2015-02-19 |2015-03-24 | 2
2 | 10 |2015-02-12 | 101 |2015-02-10 |2015-02-19 | 2
[Code] ...
I tried it like this:
select p.pn,p.code,p.[date],p.doctorcode,pr.sdate,pr.edate,pr.[status] from patient p
outer apply (select top 1 pr.pn,pr.code,pr.sdate,pr.edate,pr.[status] from patientref pr
where pr.pn=p.pn and pr.code=p.code and p.date between pr.sdate and pr.edate
order by case when pr.status=2
then 1 when pr.status=4 then 2
when pr.status=3 then 3
when pr.status=1 then 4 end ,pr.sdate
)pr
but this query not given expected result.here when dos not fall between sdate and edate that records not given in the above query. I required that records also.if not fall b/w condition then we need retrive that records empty values for that records.
View 7 Replies
View Related
Oct 7, 2015
I am trying to get count of records by month wise when they select year .It was showing the out put correctly but its showing months arer in numbers,but I want to display Jan,Feb ...
SELECT DISTINCT Standard, COUNT(Standard) AS Total,month(ReportDate) Month
FROM CPTable where
year(ReportDate) = '2015'
GROUP BY Standard, Standard ,
month(ReportDate)
Output
Standard Total Month
NULL 0 1 //Jan
NULL 0 2 //Feb
NULL 0 3
NULL 0 4
NULL 0 5
OSHA 18001, 1 5
NULL 0 6
NULL 07
NULL 08
OSHA 18001,158
TL 9000,18
NULL 09
OSHA 18001,139
View 4 Replies
View Related
Dec 3, 2010
I have a table that "Geography" that has the following columns: city, state, zip
There are tons of duplicate cities in this table. I ran this query and it shows me the number of occurrences of each city. I want to delete all the duplicates except for 1. I don't want to do this manually as there are a lot of records.
What would the SQL look like to delete the duplicate records but keep at least one?
View 9 Replies
View Related
Jul 23, 2015
I need to update more than one record at once. I have ~ 100 records that I have to update and don't want to execute query 100 times.
My query looks like this:
Update Table1
Set Table1.field1 = ( select Table2.field1 from Table2 where Table2.field2 IN ('a', 'b', 'c')
where Table1.field2 IN ( 'a', 'b', 'c')
It obviously failed because subquery returned more than one value and error message stated that I can't use '=' operator in this case.
My question: how could I update the same column from many records in one execution?
View 10 Replies
View Related
Aug 30, 2015
This is a bit lengthy, but lets say we have three tables
a) tblSaleStatementCustomer
b) tblCreditors
c) tblReceiptDue
which shows records like below
Table 1 - tblSaleStatementCustomer
ID CustomerName VoucherType Outbound Inbound CustomerType
----------------------------------------------------------------------------------------------
1 ABC Sales 10000 0 Dealer
2 MNC Sales 9000 0 Dealer
3 MNC Sales 4000 0 Dealer
Table 2 - tblCreditors
ID Name OpeningBalance
----------------------------------------------------------------------------------------------
1 ABC 20000
2 MNC 15000
3 XBM 18000
4 XYZ 12000
View 2 Replies
View Related
Nov 15, 2015
Lets say I have a table - tblProducts
View 4 Replies
View Related
Jul 21, 2015
What I would like to do is to have a TSQL Select return the number of records in the Result as if TOP (n) had not been used. Example:I have a table called Orders containing more than 1.000 records with OrderDate = '2015/07/21' and my client application has a threshold for returning records at 100 and therefore the TSQL would look like
SELECT TOP (100) * FROM Orders Where OrderDate = '2015/07/21' ORDER by OrderTime Desc
Now I would like to "tell" the client that only 100 of 1.000 records are shown in the client application grid. Is there a way to return a value indicating that if TOP (100) had not been used the resultset would have been 1.000. I know I could create the same TSQL using COUNT() (SELECT COUNT(*) FROM Orders Where OrderDate = '2015/07/21' ORDER by OrderTime Desc) and return that in a variable in the SELECT statement or even creating the COUNT() as a subquery and return it as a column, but I would like to avoid running multiple TSQL's. Since SQL Server already needs to select the entire recordset and sort it (ORDER BY) and return only the first 100 the total number of records in the initial snapshot must somehow be available.
View 6 Replies
View Related
Oct 14, 2015
This one is making my head hurt! Trying to figure out how to query for records between date range. The records have a start_date and an end_date field. The end_date field maybe null.
For example, say you wanted to see the records of everyone checked into a hotel during a given date range. You need to account for the people that checked in before you @start_date parameter and may check out after your @end_date parameter.
fyi- As for the null end_date field, think of this as they have checked in and not sure when they will checkout yet.
View 7 Replies
View Related
