how to constrain data within the query

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 2008 Forums

Transact-SQL (2008)

how to constrain data within the query

Author

Topic

nbritton
Starting Member

22 Posts

Posted - 2011-11-01 : 08:58:40

I am needing to produce a list of backups completed with volume by month by plugin, but i only want one backup by client by plugin. how can one do this. So far i have the following, but it is give me several fulls as the full runs each week and i cant constrain to the the last 7 days because i need to see trends by month.


Here is the code:

Select datename(mm,startdatetime_calc) AS date_month,plugin,
      SUM(case when [Plugin] = 'File System' then (TransferVolume)/ power(1024,3) else NULL end) as TransferVolume_FS,
      SUM(case when [Plugin] = 'DB2' then (TransferVolume)/ power(1024,3) else NULL end) as TransferVolume_DB2,
      SUM(case when [Plugin] = 'Exchange Server APM' then (TransferVolume)/ power(1024,3) else NULL end) as TransferVolume_EXCH,
      SUM(case when [Plugin] = 'MS SQL' OR [Plugin] = 'SQL Server APM' then (TransferVolume)/ power(1024,3) else NULL end) as TransferVolume_SQL,
      SUM(case when [Plugin] = 'NetVault Databases' then (TransferVolume)/ power(1024,3) else NULL end) as TransferVolume_NVDB,
      SUM(case when [Plugin] = 'Oracle APM' then (TransferVolume)/ power(1024,3) else NULL end) as TransferVolume_ORACLE,
      SUM(case when [Plugin] = 'Consolidate Incremental backups' then (TransferVolume)/ power(1024,3) else NULL end) as TransferVolume_Consolidated,
      SUM(case when [Plugin] = 'Lotus Notes' then (TransferVolume)/ power(1024,3) else NULL end) as TransferVolume_NOTES,
      SUM(case when [Plugin] = 'MySQL APM' then (TransferVolume)/ power(1024,3) else NULL end) as TransferVolume_MYSQL,
      SUM(case when [Plugin] = 'VMware Plugin' then (TransferVolume)/ power(1024,3) else NULL end) as TransferVolume_VMWARE

from NV_JOB_HISTORY
where [job title] like '%full%' AND [job title] not like '%daily%'
group by 
      datename(mm,startdatetime_calc),
      [plugin]

Here is some sample output from a client:

rownum              startdate                              starttime                    sysstarttime           enddate                                   endtime                             endsystime       jobid     instance         phase                  jobtitle                                  targetset       advoptset                   schedset                    optset                 selset                client     priority        runtime              transfersize                  status                                                     plugin                  masterserver                                                           
18           2011-03-09 00:00:00.000                1899-12-30 01:30:00.000                1299655800         2011-03-09 00:00:00.000                1899-12-30 02:14:04.000                1299658444         730         2              1              GET GETWEBA03 FS 01:30 Wednesday Weekly Full         GET        Weekly Full         01:30 Wednesday Full Weekly    Standard Windows Full  GETWEBA03 FS GETWEBA03                30           00:44:04               N/A        4.1MB   10.2GB  Backup Completed          Backup FNTSOMATCVSMASTER03           File System 10695475             2011-03-09 02:30:00.000                2011-03-09 03:14:04.000                NULL     March
6165       2011-03-23 00:00:00.000                1899-12-30 01:30:01.000                1300861801         2011-03-23 00:00:00.000                1899-12-30 08:55:21.000                1300888521         730         5              1              GET GETWEBA03 FS 01:30 Wednesday Weekly Full         GET        Weekly Full         01:30 Wednesday Full Weekly    Standard Windows Full  GETWEBA03 FS GETWEBA03                30           07:25:20               N/A        Unknown            Unknown            Scheduler aborted whilst job running     Backup                FNTSOMATCVSMASTER03           File System         0              2011-03-23 01:30:01.000                2011-03-23 08:55:21.000                NULL     March
31896    2011-04-27 00:00:00.000                1899-12-30 01:30:01.000                1303885801         2011-04-27 00:00:00.000                1899-12-30 05:23:09.000                1303899789         730         12           1              GET GETWEBA03 FS 01:30 Wednesday Weekly Full         GET        Weekly Full         01:30 Wednesday Full Weekly    Standard Windows Full  GETWEBA03 FS GETWEBA03                30           03:53:08               N/A        4MB       10.2GB  Backup Completed          Backup FNTSOMATCVSMASTER03           File System 10695475             2011-04-27 01:30:01.000                2011-04-27 05:23:09.000                NULL     April
45123    2011-06-22 00:00:00.000                1899-12-30 01:30:00.000                1308724200         2011-06-22 00:00:00.000                1899-12-30 01:53:49.000                1308725629         730         19           1              GET GETWEBA03 FS 01:30 Wednesday Weekly Full         NVSD    GET Weekly Full Dup      01:30 Wednesday Full Weekly    Standard Windows Full  GETWEBA03 FS                GETWEBA03       30           00:23:49               N/A        6.8MB   9.1GB    Backup Completed          Backup                FNTSOMATCVSMASTER03           File System         9542042                2011-06-22 01:30:00.000                2011-06-22 01:53:49.000       NULL     June
88264    2011-07-27 00:00:00.000                1899-12-30 01:30:01.000                1311748201         2011-07-27 00:00:00.000                1899-12-30 01:58:52.000                1311749932         730         23           1              GET GETWEBA03 FS 01:30 Wednesday Weekly Full         NVSD    GET Weekly Full Dup      01:30 Wednesday Full Weekly    Standard Windows Full  GETWEBA03 FS                GETWEBA03       30           00:28:51               N/A        5.9MB   9.4GB    Backup Completed          Backup                FNTSOMATCVSMASTER03           File System         9856614                2011-07-27 01:30:01.000                2011-07-27 01:58:52.000       NULL     July
95441    2011-08-03 00:00:00.000                1899-12-30 01:30:00.000                1312353000         2011-08-03 00:00:00.000                1899-12-30 02:21:13.000                1312356073         731         9              1              GET GETWEBA03 FS 01:30 Wednesday Monthly Full       NVSD    GET Yearly Full Dup         01:30 Wednesday Full Monthly  Standard Windows Full  GETWEBA03 FS                GETWEBA03       30           00:51:13               N/A        5.1MB   9.5GB    Backup Completed          Backup                FNTSOMATCVSMASTER03           File System         9961472                2011-08-03 01:30:00.000                2011-08-03 02:21:13.000       NULL     August
133495  2011-08-17 00:00:00.000                1899-12-30 01:30:00.000                1313562600         2011-08-17 00:00:00.000                1899-12-30 02:09:34.000                1313564974         730         25           1              GET GETWEBA03 FS 01:30 Wednesday Weekly Full         NVSD    GET Weekly Full Dup      01:30 Wednesday Full Weekly    Standard Windows Full  GETWEBA03 FS                GETWEBA03       30           00:39:34               N/A        4.3MB   9.5GB    Backup Completed          Backup                FNTSOMATCVSMASTER03           File System         9961472                2011-08-17 01:30:00.000                2011-08-17 02:09:34.000       NULL     August

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 11:53:15

not clear from your output your field names. Can you please fieldnames also on top of them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nbritton
Starting Member

22 Posts

Posted - 2011-11-01 : 12:38:39

Let me know if you need more. I think i captured the majority.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 12:46:52

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY client,plugin ORDER BY NEWID()) AS Rn,*
FROM... query
)t
WHERE Rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nbritton
Starting Member

22 Posts

Posted - 2011-11-01 : 22:42:13

I guess i am a little confused. Is that to replace the code above or append to it somewhere. If it is to replace then how would i create all teh adhoc columns for the sums i am doing?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 05:38:50

its not to replace but add the additional column to your existing query


SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY client,plugin  ORDER BY NEWID()) AS Rn,*
FROM(this part is your current query  )q
)t
WHERE Rn=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources