June 18, 2010 at 2:34 pm
Well I'm not sure where even to start with this one. I have a table of transactions for a given piece of equipment, will say a pressure gauge that is handed out and used over a month, then returned and assessed. Here is my sample data:
CREATE TABLE #Transactions
(
TransID INT IDENTITY(1,1) Primary Key,
EquipID INT,
TransType nvarchar(50),
TransVal float,
TransDate datetime
)
BEGIN
INSERT INTO #Transactions(EquipID,TransType,TransVal,TransDate)
SELECT 40,'IssueDate',null,'2010-01-01' UNION ALL
SELECT 40,'Pressure1',0.25,'2010-01-04' UNION ALL
SELECT 40,'Pressure2',0.35,'2010-01-08' UNION ALL
SELECT 40,'Pressure3',0.26,'2010-01-16' UNION ALL
SELECT 40,'Pressure3',2.22,'2010-01-16' UNION ALL
SELECT 40,'ReturnDate',null,'2010-01-30' UNION ALL
SELECT 40,'Result',25.5,'2010-02-15'
END
SELECT * FROM #Transactions
Drop table #Transactions
GO
The desired output of this code would be to take all of the transactions and place them into a record form that can be loaded into a report. If in any case one of the transaction types is duplicated I would need to return all of the data for each and every instance of that duplication. I could make this work using PIVOT I believe, but you have to use an aggregate on the values which will only take one instace of the duplicated transaction (Pressure3) in my example case.
I can't really give a description of what I have done, as I haven't got anywhere. Here is the what the required output of the sample data should look like after all is said and done.
CREATE TABLE #RequiredOutput
(
EquipID INT,
IssueDate Datetime,
Pressure1 nvarchar(50),
Pressure2 nvarchar(50),
Pressure3 nvarchar(50),
Pressure1Date Datetime,
Pressure2Date Datetime,
Pressure3Date Datetime,
ReturnDate nvarchar(50),
Result nvarchar(50),
ResultDate Datetime
)
BEGIN
INSERT INTO #RequiredOutput
SELECT 40,'2010-01-01',0.25,0.35,0.26,'2010-01-01','2010-01-08','2010-01-16','2010-01-30',25.5,'2010-02-15' UNION ALL
SELECT 40,'2010-01-01',0.25,0.35,2.22,'2010-01-01','2010-01-08','2010-01-16','2010-01-30',25.5,'2010-02-15'
END
SELECT * FROM #RequiredOutput
GO
June 18, 2010 at 3:02 pm
Check out the two cross-tabs and pivots links in my signature block.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 18, 2010 at 3:22 pm
WayneS (6/18/2010)
Check out the two cross-tabs and pivots links in my signature block.
Thanks, that looks very informative, I'll give it a look over. Also never thought of using IDENTITy() for generating dates, I always just CROSS joined with tables loaded with values 0-100+ and constrained in WHERE clause. Wow, each day I spend learning this SQL stuff the more stupid I realise I was the day before.
June 18, 2010 at 3:58 pm
loki1049 (6/18/2010)
Wow, each day I spend learning this SQL stuff the more stupid I realise I was the day before.
ditto! :w00t:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply