Query Issues - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Issues

Author

Topic

namrnaahmad
Starting Member

4 Posts

Posted - 2009-10-10 : 06:20:13

Data Avialable is Like This:

sDevice F7 sPocketName sDate sTime
SPMM-X '000220' P1 7/09/09 9:00
SPMM-X '000230' P2 7/09/09 9:00
SPMM-X '000250' P3 7/09/09 9:00

SPMM-X '000220' P1 7/09/09 9:10
SPMM-X '000230' P2 7/09/09 9:10
SPMM-X '000250' P3 7/09/09 9:10

SPMM-X '000220' P1 7/09/09 9:30
SPMM-X '000230' P2 7/09/09 9:30
SPMM-X '000250' P3 7/09/09

And output required is as follow (Last record of the Day of date '7/09/09' along with sum ):

Name P1 P2 P3 Date Time SUM
SPMM-X 220 230 250 7/09/09 9:30 700

I have written query separately mention below like this :

SELECT Top (1) CONVERT(INT, F7) as "Phase I Energy" FROM SP001001 where Sdevice='SPMM-X' and sDeviceId='00' and sdate='09/10/2009' and F7<>'' and sPocketName='P1' order by ntime desc

SELECT Top (1) CONVERT(INT, F7) as "Phase II Energy" FROM SP001001 where Sdevice='SPMM-X' and sDeviceId='00' and sdate='09/10/2009' and F7<>'' and sPocketName='P3' order by ntime desc

SELECT Top (1) CONVERT(INT, F7) as "Phase III Energy" FROM SP001001 where Sdevice='SPMM-X' and sDeviceId='00' and sdate='09/10/2009' and F7<>'' and sPocketName='P3' order by ntime desc

But the output comes individually

Kindly guide me the make output of query like this

Last record of the Day of date '7/09/09' along with sum

Name P1 P2 P3 Date Time SUM
SPMM-X 220 230 250 7/09/09 9:30 700

Namran Ahmed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-10 : 07:49:44

[code]DECLARE @Sample TABLE
(
sDevice CHAR(6),
F7 CHAR(6),
sPocketName CHAR(2),
sDate DATETIME,
sTime DATETIME
)

INSERT @Sample
SELECT 'SPMM-X', '000220', 'P1', '7/09/09', '9:00' UNION ALL
SELECT 'SPMM-X', '000230', 'P2', '7/09/09', '9:00' UNION ALL
SELECT 'SPMM-X', '000250', 'P3', '7/09/09', '9:00' UNION ALL
SELECT 'SPMM-X', '000220', 'P1', '7/09/09', '9:10' UNION ALL
SELECT 'SPMM-X', '000230', 'P2', '7/09/09', '9:10' UNION ALL
SELECT 'SPMM-X', '000250', 'P3', '7/09/09', '9:10' UNION ALL
SELECT 'SPMM-X', '000220', 'P1', '7/09/09', '9:30' UNION ALL
SELECT 'SPMM-X', '000230', 'P2', '7/09/09', '9:30' UNION ALL
SELECT 'SPMM-X', '000250', 'P3', '7/09/09', '9:30'

SELECT sDevice AS [Name],
MAX(CASE WHEN sPocketName = 'P1' THEN CAST(F7 AS INT) ELSE '' END) AS P1,
MAX(CASE WHEN sPocketName = 'P2' THEN CAST(F7 AS INT) ELSE '' END) AS P2,
MAX(CASE WHEN sPocketName = 'P3' THEN CAST(F7 AS INT) ELSE '' END) AS P3,
CONVERT(CHAR(8), sDate, 1) AS [Date],
CONVERT(CHAR(5), sTime, 114) AS [Time],
SUM(CAST(F7 AS INT)) AS [SUM]
FROM (
SELECT sDevice,
F7,
sPocketName,
sDate,
sTime,
DENSE_RANK() OVER (PARTITION BY sDevice ORDER BY sDate DESC, sTime DESC) AS recID
FROM @Sample
) AS d
WHERE recID = 1
GROUP BY sDevice,
sDate,
sTime[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources