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
 General SQL Server Forums
 New to SQL Server Programming
 How to get 1 single record

Author  Topic 

rds207
Posting Yak Master

198 Posts
Posted - 2010-07-13 : 18:40:28
Hi am trying to query to get onr single records from my table
here is the DDL of the table
CREATE TABLE [dbo].[CRMBUILDMETRICS](
[BuildID] [varchar](50) NULL,
[Jobname] [text] NULL,
[SubmittedBy] [varchar](50) NOT NULL,
[Action] [varchar](32) NOT NULL,
[StartTime] [datetime2](3) NULL,
[Duration] [int] NULL,
[FileCount] [int] NULL,
[ByteCount] [bigint] NULL,
[ProductLine] [varchar](37) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Here is my sample data:



BuildID	Jobname	    SubmittedBy	   Action	   StartTime	Duration	FileCount	ByteCount	ProductLine
C8700VXAAA1016	C8700VXAAA1016_04JUN2010_024845pm	rllynch	Build	2010-06-04 18:49:51.000	69	NULL	NULL	CSM8700.VX.1.0
C8700VXAAA1016	C8700VXAAA1016_04JUN2010_024845pm	rllynch	Compile	2010-06-04 18:50:17.000	0	NULL	NULL	CSM8700.VX.1.0
C8700VXAAA1016	C8700VXAAA1016_04JUN2010_024845pm	rllynch	Copy	2010-06-04 18:50:23.000	4	370	16496640	CSM8700.VX.1.0
C8700VXAAA1016	C8700VXAAA1016_04JUN2010_024845pm	rllynch	Other	NULL	52	NULL	NULL	CSM8700.VX.1.0
C8700VXAAA1016	C8700VXAAA1016_04JUN2010_024845pm	rllynch	Strip	2010-06-04 18:50:07.000	8	NULL	NULL	CSM8700.VX.1.0
C8700VXAAA1016	C8700VXAAA1016_04JUN2010_024845pm	rllynch	Sync	2010-06-04 18:50:00.000	5	NULL	NULL	CSM8700.VX.1.0




The conditions for getting one single record for a jobname is below, based on action i need get the value into one single transaction record for one jobname...



SELECT CAST(Jobname AS NVARCHAR(50)),
PRODUCTLINE,
SubmittedBy,
BuildID,
FILECOUNT,
(SELECT BYTECOUNT WHERE ACTION ='COPY')AS "COPY_BUILD_SIZE",
(SELECT DURATION WHERE ACTION = 'BUILD') AS "BUILD_TIME",
(SELECT DURATION WHERE ACTION = 'COMPLIE') AS "BUILD_TIME_COMPLIE",
(SELECT DURATION WHERE ACTION = 'PACK') AS "BUILD_TIME_PACK",
(SELECT DURATION WHERE ACTION = 'RECOMPLIE') AS "BUILD_TIME_RECOMPLIE"
FROM dbo.CRMBUILDMETRICS WHERE CAST(Jobname AS NVARCHAR(50))='C8700VXAAA1016_04JUN2010_024845pm'



The results with above query , 




JobName	PRODUCTLINE	SubmittedBy	BuildID	FILECOUNT	COPY_BUILD_SIZE	BUILD_TIME	BUILD_TIME_COMPLIE	BUILD_TIME_PACK	BUILD_TIME_RECOMPLIE
C8700VXAAA1016_04JUN2010_024845pm	CSM8700.VX.1.0	rllynch	C8700VXAAA1016	NULL	NULL	69	NULL	NULL	NULL
C8700VXAAA1016_04JUN2010_024845pm	CSM8700.VX.1.0	rllynch	C8700VXAAA1016	NULL	NULL	NULL	NULL	NULL	NULL
C8700VXAAA1016_04JUN2010_024845pm	CSM8700.VX.1.0	rllynch	C8700VXAAA1016	370	16496640	NULL	NULL	NULL	NULL
C8700VXAAA1016_04JUN2010_024845pm	CSM8700.VX.1.0	rllynch	C8700VXAAA1016	NULL	NULL	NULL	NULL	NULL	NULL
C8700VXAAA1016_04JUN2010_024845pm	CSM8700.VX.1.0	rllynch	C8700VXAAA1016	NULL	NULL	NULL	NULL	NULL	NULL
C8700VXAAA1016_04JUN2010_024845pm	CSM8700.VX.1.0	rllynch	C8700VXAAA1016	NULL	NULL	NULL	NULL	NULL	NULL





Could anybody please help how i get one single record for each job name?

rds207
Posting Yak Master

198 Posts
Posted - 2010-07-13 : 19:28:44
I tried this query it still gives me six records , why dont i get i single record...

Select 
C.BUILDID,
C.JOBNAME,
C.SubmittedBy,
C.COPY_BUILD_SIZE,
C.BUILD_TIME,
C.Compile_BUILD_TIME,
C.PACK_BUILD_TIME,
C.RECOMPLIE_BUILD_TIME,
C.SYNC_BUILD_TIME,
C.CopyPack_BUILD_TIME,
C.Strip_BUILD_TIME,
C.PackSBA_BUILD_TIME,
C.Copy_BUILD_TIME,
C.FILECOUNT,
C.PRODUCTLINE 
FROM (
SELECT 
BuildID,
CAST(Jobname AS NVARCHAR(50))as JobName,
SubmittedBy,
(SELECT BYTECOUNT WHERE ACTION ='Copy')AS "Copy_BUILD_SIZE",
(SELECT DURATION WHERE ACTION = 'Build') AS "Build_TIME",
(SELECT DURATION WHERE ACTION = 'Compile') AS "Compile_BUILD_TIME",
(SELECT DURATION WHERE ACTION = 'PACK') AS "PACK_BUILD_TIME",
(SELECT DURATION WHERE ACTION = 'ReCompile') AS "RECOMPLIE_BUILD_TIME",
(SELECT DURATION WHERE ACTION = 'SYNC') AS "SYNC_BUILD_TIME",
(SELECT DURATION WHERE ACTION = 'CopyPack') AS "CopyPack_BUILD_TIME",
(SELECT DURATION WHERE ACTION = 'Strip') AS "Strip_BUILD_TIME",
(SELECT DURATION WHERE ACTION = 'PackSBA') AS "PackSBA_BUILD_TIME",
(SELECT DURATION WHERE ACTION = 'Copy') AS "Copy_BUILD_TIME",
FILECOUNT,
PRODUCTLINE
FROM dbo.CRMBUILDMETRICS WHERE CAST(Jobname AS NVARCHAR(50))='C8700VXAAA1016_04JUN2010_024845pm')AS C
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2010-07-13 : 20:58:28
What is the version of SQL Server you are using ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

rds207
Posting Yak Master

198 Posts
Posted - 2010-07-13 : 21:28:37

SQL SERVER 2008

quote:
Originally posted by khtan

What is the version of SQL Server you are using ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2010-07-13 : 21:37:20
[code]
select *
from
(
select row_no = row_number() over (partition by Jobname order by <somecol> ), *
from CRMBUILDMETRICS
) d
where row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

rds207
Posting Yak Master

198 Posts
Posted - 2010-07-13 : 22:44:43
Please take a look at my query , it has some conditions to get one row of the data the expected output should be one row with all buildtimes for all action values......

quote:
Originally posted by khtan


select *
from
(
    select row_no = row_number() over (partition by Jobname order by <somecol> ), *
    from   CRMBUILDMETRICS
) d
where row_no = 1


KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2010-07-13 : 22:54:15
try

SELECT  BuildID,
        CAST(Jobname AS NVARCHAR(50))as JobName,
        SubmittedBy,
        SUM(CASE WHEN ACTION = 'Copy' THEN BYTECOUNT END) as [Copy_BUILD_SIZE],
        SUM(CASE WHEN ACTION = 'Build' THEN DURATION END) as [Build_TIME],
        SUM(CASE WHEN ACTION = 'Compile' THEN DURATION END) as [Compile_BUILD_TIME],
        SUM(CASE WHEN ACTION = 'PACK' THEN DURATION END) as [PACK_BUILD_TIME],
        SUM(CASE WHEN ACTION = 'ReCompile' THEN DURATION END) as [RECOMPLIE_BUILD_TIME],
        SUM(CASE WHEN ACTION = 'SYNC' THEN DURATION END) as [SYNC_BUILD_TIME],
        SUM(CASE WHEN ACTION = 'CopyPack' THEN DURATION END) as [CopyPack_BUILD_TIME],
        SUM(CASE WHEN ACTION = 'Strip' THEN DURATION END) as [Strip_BUILD_TIME],
        SUM(CASE WHEN ACTION = 'PackSBA' THEN DURATION END) as [PackSBA_BUILD_TIME],
        SUM(CASE WHEN ACTION = 'Copy' THEN DURATION END) as [Copy_BUILD_TIME],
        FILECOUNT,
        PRODUCTLINE
FROM    dbo.CRMBUILDMETRICS 
WHERE   CAST(Jobname AS NVARCHAR(50))='C8700VXAAA1016_04JUN2010_024845pm'
GROUP BY BuildID,
        CAST(Jobname AS NVARCHAR(50))as JobName,
        SubmittedBy,
        FILECOUNT,
        PRODUCTLINE


KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

rds207
Posting Yak Master

198 Posts
Posted - 2010-07-14 : 12:22:08
This is giving ...



BuildID	JobName	SubmittedBy	Copy_BUILD_SIZE	Build_TIME	Compile_BUILD_TIME	PACK_BUILD_TIME	RECOMPLIE_BUILD_TIME	SYNC_BUILD_TIME	CopyPack_BUILD_TIME	Strip_BUILD_TIME	PackSBA_BUILD_TIME	Copy_BUILD_TIME	FILECOUNT	PRODUCTLINE
C8700VXAAA1016	C8700VXAAA1016_04JUN2010_024845pm	rllynch	NULL	69	0	NULL	NULL	5	NULL	8	NULL	NULL	NULL	CSM8700.VX.1.0
C8700VXAAA1016	C8700VXAAA1016_04JUN2010_024845pm	rllynch	16496640	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	4	370	CSM8700.VX.1.0




What i am expecting is below, Please help :




BuildID	JobName	SubmittedBy	Copy_BUILD_SIZE	Build_TIME	Compile_BUILD_TIME	PACK_BUILD_TIME	RECOMPLIE_BUILD_TIME	SYNC_BUILD_TIME	CopyPack_BUILD_TIME	Strip_BUILD_TIME	PackSBA_BUILD_TIME	Copy_BUILD_TIME	FILECOUNT	PRODUCTLINE
C8700VXAAA1016	C8700VXAAA1016_04JUN2010_024845pm	rllynch	16496640	69	0	NULL	NULL	5	NULL	8	NULL	4	370	CSM8700.VX.1.0








quote:
Originally posted by khtan

try

SELECT  BuildID,
        CAST(Jobname AS NVARCHAR(50))as JobName,
        SubmittedBy,
        SUM(CASE WHEN ACTION = 'Copy' THEN BYTECOUNT END) as [Copy_BUILD_SIZE],
        SUM(CASE WHEN ACTION = 'Build' THEN DURATION END) as [Build_TIME],
        SUM(CASE WHEN ACTION = 'Compile' THEN DURATION END) as [Compile_BUILD_TIME],
        SUM(CASE WHEN ACTION = 'PACK' THEN DURATION END) as [PACK_BUILD_TIME],
        SUM(CASE WHEN ACTION = 'ReCompile' THEN DURATION END) as [RECOMPLIE_BUILD_TIME],
        SUM(CASE WHEN ACTION = 'SYNC' THEN DURATION END) as [SYNC_BUILD_TIME],
        SUM(CASE WHEN ACTION = 'CopyPack' THEN DURATION END) as [CopyPack_BUILD_TIME],
        SUM(CASE WHEN ACTION = 'Strip' THEN DURATION END) as [Strip_BUILD_TIME],
        SUM(CASE WHEN ACTION = 'PackSBA' THEN DURATION END) as [PackSBA_BUILD_TIME],
        SUM(CASE WHEN ACTION = 'Copy' THEN DURATION END) as [Copy_BUILD_TIME],
        FILECOUNT,
        PRODUCTLINE
FROM    dbo.CRMBUILDMETRICS 
WHERE   CAST(Jobname AS NVARCHAR(50))='C8700VXAAA1016_04JUN2010_024845pm'
GROUP BY BuildID,
        CAST(Jobname AS NVARCHAR(50))as JobName,
        SubmittedBy,
        FILECOUNT,
        PRODUCTLINE


KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site