Min,Max values along with other column value


  

CREATE TABLE MRMReadings
    ([SiteIncomeID] [int] IDENTITY(1,1) NOT NULL, [SiteID] nchar(5), [EDateTime] datetime NOT NULL, Income [nvarchar](50) NOT NULL)
;
    
INSERT INTO MRMReadings
    ([SiteID],  [EDateTime], [Income])
VALUES
    ('L0020',  '2015-05-19 05:00:26', '85.98'),
    ('L0020',   '2015-05-20 05:00:28', '145.98'),
    ('L0101',   '2015-05-19 22:07:43', '1,936.08'),
    ('L0101',   '2015-05-20 22:14:00', '1,438.89'),
    ('L0102',   '2015-05-20 21:16:26', '143.65'),
    ('L0102',   '2015-05-19 21:48:06', '243.50')
;



Select  [SiteID]
,Cast(Substring(Min(CAST([EDateTime] AS BINARY(8)) + CAST([Income] AS BINARY(12) )  ),1,8) as datetime) ReadStartDate                  
,Cast(Substring(Max(CAST([EDateTime] AS BINARY(8)) + CAST([Income] AS BINARY(8))),1,8) as datetime) ReadEndDate  
,Cast(Substring(Min(CAST([EDateTime] AS BINARY(8)) + CAST([Income] AS BINARY(16))),9,16) as nvarchar(50)) ReadStartIncome     
,Cast(Substring(Max(CAST([EDateTime] AS BINARY(8)) + CAST([Income] AS BINARY(16))),9,16) as nvarchar(50)) ReadEndIncome    
FROM   MRMReadings
Group by [SiteID]
 
   /*

   SiteID	ReadStartDate	ReadEndDate	ReadStartIncome	ReadEndIncome
L0020	2015-05-19 05:00:26.000	2015-05-20 05:00:28.000	85.98	145.98
L0101	2015-05-19 22:07:43.000	2015-05-20 22:14:00.000	1,936.08	1,438.89
L0102	2015-05-19 21:48:06.000	2015-05-20 21:16:26.000	243.50	143.65

*/
  



drop table MRMReadings

 

https://social.msdn.microsoft.com/Forums/en-US/ddb88538-4af8-4c16-88f2-d5522289dc7e/merge-2-rows-into-one-line-output?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s