Transact SQL :: Sum Quantities For Given Transaction Type
Nov 4, 2015
My data is in 4 columns and multiple rows, like this
PartNo Quantity TransactionType TransactionDate
aaa 25 Incoming 2015-03-01
aaa 25 Incoming 2015-03-01
aaa 50 Transfer 2015-03-02
bbb 30 Incoming 2015-03-03
bbb 30 Transfer 2015-03-03
ccc 50 Incoming 2015-05-15
ccc 75 Incoming 2015-05-20
ccc 50 Transfer 2015-05-18
ccc 75 Transfer 2015-05-21
What I need to achieve is sum the quantities for a given transaction type, group it by Part Number and add an additional column where the Transaction Date for Transfer Type rows would become the Transfer Date. Each part would have one row. The resulting data would look like this.
I am working with SQL 2012 Express and I have a table with all transactions of invoices and payments for each customer. I am looking to find the last transaction detail of either two particular types of transactions (but not both) for each customer. So far I have tried various combinations around
SELECT MAX([sbt::dte]) OVER (PARTITION BY [sbt::code]) AS LastPayment, [sbt::folio], [sbt::typ], [sbt::net] FROM dbo.tblSalbatTxn WHERE ([sbt::typ] = 13 OR [sbt::typ] = 17)
Then there are some cases where customers have made more than the one of the same transaction type on that same last day which I would then like to sum the net value for that day of that transaction type.
I have also worked around this but it filtered the transaction type after it found the last transaction for each customer irrespective of it's transaction type.
SELECT TOP (100) PERCENT a.[cl::code], a.[cl::alpha], b.[sbt::folio], b.[sbt::typ] FROM dbo.tblSalAccounts AS a INNER JOIN dbo.tblSalbatTxn AS b ON a.[cl::code] = b.[sbt::code] WHERE (b.[sbt::dte] = (SELECT MAX([sbt::dte]) AS Expr1 FROM dbo.tblSalbatTxn WHERE (b.[sbt::typ] = 11 OR b.[sbt::typ] = 17) AND ([sbt::code] = b.[sbt::code])))
I have a table which basically stores multiple users' responses to a questionnaire. I want to calculate certain statistics on this data (for example: how many users selected a specific answer to a question). If there are many questions and possible answers, then this can get really inefficient. I was wondering what would be the best way to go about doing this.
Currently, I was thinking of using what I believe are called crosstabs:
Code:
SELECT (SELECT COUNT(*) FROM tableName WHERE Q1answer='value1'), (SELECT COUNT(*) FROM tableName WHERE Q1answer='value2'), (SELECT COUNT(*) FROM tableName WHERE Q2answer='value1'), etc...
Is this the best way to go about this or is this really inefficient?
I have two tables that can be created with sample data using the DDL at the bottom of this post. What I'm looking to do is update the QtyReceived column in tblPurchaseOrderLineDetail from the Qty column in tblReceivedItems. However, the tricky part that I can't figure out is splitting these quantities out over multiple lines. I should only be allowed to receive up to the QtyOrdered column in tblPurchaseOrderLineDetail.
For a specific example from the sample data we'll look at PurchaseOrderDetailID 28526. From the tblReceivedItems, there are three records with quantities of 48, 48, and 20. From the tblPurchaseOrderLineDetail there are three records of QtyOrdered of 55, 45, and 20. What I would like to happen is fulfill the records in the tblPurchaseOrderLineDetail sequentially (essentially in order of ExpectedDate). So, the QtyReceived would be 55, 45, and 16 for the corresponding records. If there is already a quantity in the QtyReceived column, but it's less than the QtyOrdered column, the quantity needs to be added to the column (not overwritten).
DDL To CREATE Sample Tables and Data:
CREATE TABLE [dbo].[tblReceivedItems]( [ID] [int] IDENTITY(1,1) NOT NULL, [PurchaseOrderDetailID] [int] NULL, [Qty] [int] NULL) SET IDENTITY_INSERT [dbo].[tblReceivedItems] ON INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (1, 28191, 48)
This table returns multiple records for an Invoice.
Based on the transactiontype_desc the Amount_Paid_DC is a different value. Trying to add up the amounts based on the transactiontype.
select DebtorNumber, InvoiceNumber, Sum(Amount_Invoiced_DC) AS InvAmt, case transactiontype_desc when 'Sales Invoice' then sum(Amount_Paid_DC) else 0 end as AmtPaid, case transactiontype_desc when 'Discount/Surcharge' and Amount_Paid_DC < 0 then sum(Amount_Paid_DC) else 0 end as DiscountAmt FROM BI50_BankTransactions_AR_InvcDt_H group by debtornumber, Invoicenumber
I have a table that i'm trying to pull the first transaction and last transaction per prodid. The first transaction, the ctrid should be 'TOPPEntry'.
sample raw data result prodid--------lineid------itemid---------ctrid----------createddate-- ================================================================= TOP02296228--Line15-----TTC3071-IR------TOPPEntry------2015-01-03 07:45:31.000--input TOP02296228--Line15-----TTC3071-IR------TOPCBFG--------2015-01-07 16:18:26.000--fg--done processing TOP02296229--Line15-----TTC3071-IR------TOPPEntry------2015-01-04 07:45:31.000--input TOP02296229--Line15-----TTC3071-IR------TOPLens--------2015-01-05 12:12:31.000--wip--wip means still in process TOP02296230--Line15-----TTC3071-IR------TOPPEntry------2015-01-05 08:45:31.000--input TOP02296230--Line15-----TTC3071-IR------TOPCBSCP-------2015-01-06 14:18:42.000--scrap--done processing
Is it possible to group the below code into an Island type scenario? The data represents people in a location. If they are in the same place one after another, to group and provide the min/max scenarios.
During our DR drill we found that the same code which used to run perfectly fine on our Primary Data Centre is running very slow on Disaster Recovery DB Server and there are Lot's of open transaction with sleeping status and waittype as 'AWAITING COMMAND'.CPU, Memory and disk utilization are good. The ping reply between the app server and the DB server is well within the limit's even blocking is not their, also nothing is reported in the error logs. We are using SQL server 2014 STD 64 BIT on Windows Server 2012.
select TOP 10 rec_id,trans_id,number_id,card_no,message_id,trans_datetimefrom [dbo].[trans_log] order by trans_datetime desc101, 1,34343, 99999, 200, 2015-11-23 12:27:25.710101,2,34343,99999,210,2015-11-23 12:27:26.710102,3,43434,88888,200,2015-11-23 12:28:26.714102,4,43434,88888,233,2015-11-23 12:28:27.710expected result:34343,99999,datediff(ss,'2015-11-23 12:27:26.710','2015-11-23 12:27:25.710') --difference between row 2 and row 143434,88888,datediff(ss,'2015-11-23 12:28:26.714','2015-11-23 12:28:27.710') --
difference between row 4 and row 3difference between row 6 and row 5...In the above query, I always want to find the difference in transaction date time between second and first row in a moving window.I have the unique id as rec_id to compare the next row with the previous row.
I am building as Search page whereby a user passes in a variable and depending on the variable type, different results will come back ...if the search criteria is '123 somewhere' it would be a string and we would search the address field. If the search criteria is '123' i want to search the address field as well as the id field.that being said, in TSQL is there a way to determine if the variable coming in is a string or an int?
Im working on Partition purge process, where I need to specify following statement:
SET @cmd = 'ALTER PARTITION FUNCTION ' + @function_name + '() MERGE RANGE (@range)' EXEC (@cmd); SET @cmd1 = 'ALTER DATABASE '+ db_name()+ ' REMOVE FILE ' + @partition_file EXEC (cmd1);
I want to put this statement in Begin Tran /Commit statement but getting error that it is not allowed. "ALTER DATABASE statement not allowed within multi-statement transaction"..what are my options to rollback in case there is a failure.
I have a transaction number in my mapping table. I have a matching transaction number in my PDHist table. Sometimes I have matching transaction numbers in my PD table, but not always. This is causing no records to be returned. I have a One to Many relationship between my mapping table and both PD and PDHist.
Also, I need to check for nulls in my foreign exchange table.I can’t post the SQL because this is a classified project. However, it should be something like this, I think.
Field is not listed as text in any of the databases it is a varchar(255) - and that can be changed if that is what causes the issue.
But here is my syntax which produces the error Msg 421, Level 16, State 1, Procedure, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
DECLARE @c NVARCHAR(MAX) WITH c1 AS ( SELECT [abcd] AS table_name FROM [intranet].[dbo].[soccerfieldinfo] where [abcd] IS NOT NULL ), c2 AS ( SELECT Row_Number() OVER (ORDER BY table_name) AS r
This is a common error for SQL Server, but I got it in a uncommon way.I have a table called - tblIDNumber where there are two columns - IDN_Number [NVarchar(200)] and Temp [BigInt]
If I run, SELECT * FROM dbo.tblIDNumber WHERE IDN_IDNumberTypeStaticValue = 33 AND IDN_Removed = 0 AND CAST(IDN_Number AS BIGINT) = 1
SQL Server give me the error: Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to bigint.
I first thought IDN_Number in type 33 has characters, but it doesn't, becasue the below query works!!!
UPDATE dbo.tblIDNumber SET Temp = CAST(IDN_Number AS BIGINT) WHERE IDN_IDNumberTypeStaticValue = 33 AND IDN_Removed = 0
To workaround, I ran the query,
UPDATE dbo.tblIDNumber SET IDN_Number = '123' WHERE IDN_IDNumberTypeStaticValue = 33 AND IDN_Removed = 0
and then I ran the first query, and SQL Server does NOT give me the same error - Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to bigint.
Second query approved there is nothing wrong from converting the value in IDN_Number to a BigInt, but the third query gave the hint that data might be the cause?????
finally, I found the root cause to be an index that the first query uses :
CREATE NONCLUSTERED INDEX [IX_tblIDNumber_Covering] ON [dbo].[tblIDNumber] ( [IDN_Removed] ASC, [IDNumberCode] ASC ) INCLUDE ( [IDN_Number], [IDN_Reference]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY] GO
If I remove the index, the first query works without the error.
I am new to T-SQL programming. I need to write a main procedures to execute multiple transactions. How could I structure the program so that each transaction will not abort if failed. Instead, the next transaction will keep running. At the very end the procedure will output the error and report them back to the main program in the output parameters. Looking for pseudo code.
I need to group by transactions at line item level. As the each item in the transaction may belong to different category and I'musing case statement to identify particular category for one column , I'm using sub-query to retrieve the results at item level.
We have a required to run multiple procedures in Single Go . And Error Occurred in any Procedure the it will rollback all the changes( Either all Proc run or None)
Problem Statement is its not capturing the Error Message from Either Proc1 or Proc 2., its Capturing the Flat Message (The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction). How do i capture the Error Occurred in Proc 1 or Proc 2 into Log Tables.
I have a transaction table to store all the transaction happened on a single day. as per my requirement I wrote the query like this select Currency Code,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where TransactionCode in ('BNT' ,'BCN','BTC','STC','SCN','SNT') group by TransactionCode,CurrencyCode,TransactionAmount order by CurrencyCode..I got the result like this
I want to append the column to the transaction table(60 million records in it.) ..
Our transaction table is being used in production.. but i have very less amount of time ..
Instead of alter table.. (IF we use the alter to take backup of table and do the processing it will take more time). Is there any way to append the column to the transaction table ..
CASE WHEN NULLIF(NHSNo2, '') IS NULL THEN 1 WHEN NULLIF(Surname, '') IS NULL THEN 2 WHEN NULLIF(Forename, '') IS NULL THEN 3 WHEN NULLIF(DOB, '') IS NULL OR DOB < '01/01/1900' THEN 4 WHEN NULLIF(AddressLine1, '') IS NULL THEN 5
[code]...
The above code worth great but ideally instead of returning a number Id like to return text for example
In the following code I want to compare 2 values: AccessVal and SQLVal. The values are stored as nvarchars, so I'm isolating the numeric values in a subquery. Notice I'm only selecting 1 row. The commented line, where I compare the values, is throwing the error.
SELECT QA_AutoID, AccessVal, SQLVal ,ROUND(ABS(CONVERT(float, AccessVal,1)),0) as AccessFloat ,ROUND(ABS(CONVERT(float, SQLVal,1)),0) as SQLFloat FROM QA WHERE QA_AutoID in ( SELECT TOP 1 QA_AutoID FROM QA WHERE ISNUMERIC(SQLVal) = 1 AND ISNUMERIC(AccessVal) = 1 ) --AND ROUND(ABS(CONVERT(float, AccessVal,1)),0) <> ROUND(ABS(CONVERT(float, SQLVal,1)),0) ORDER BY ROUND(ABS(CONVERT(float, AccessVal,1)),0) DESC ,ROUND(ABS(CONVERT(float, SQLVal,1)),0) DESC
Here is the output with the comparison commented out...
Here's what I get with the comparison line activated:
I've tried converting to numeric, int and bigint instead of float. I've tried CAST instead of CONVERT. Nothing works.
i am trying to run to get a list of computers from a table based on the available free space. I'd like to group them based on the available space, for example, up to 1 GB, 1-2 GB, etc.
However when i run this query, i get an error : Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '1GB To 2GB' to data type int.
I just need the date, hour, and minute...not the micro seconds. Do I have to DATEPART and concatenate each or is there any way to simply truncate the milliseconds from it? Or is there a date format to put extract and report on it as...
I am trying to convert all columns to rows in sql, but giving an error.
SELECT Employee, Orders FROM (SELECT CisId, [Z_Id], [ModuleType] FROM CIS) p UNPIVOT (Orders FOR Employee IN (CisId, [Z_Id], [ModuleType]) )AS unpvt;
Error: The type of column "Z_Id" conflicts with the type of other columns specified in the UNPIVOT list.If I remove "Z_Id" from selection then giving same error for ModuleType also.
I have this sql stored procedure in SQL Server 2012:
ALTER PROCEDURE [dbo].[CreateBatchAndSaveExternalCodes] @newBatches as dbo.CreateBatchList READONLY , @productId int , @cLevelRatio int , @nLevelRatio int AS set nocount on;
