SQL N, N-1m, N-2 etc query - 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)

SQL N, N-1m, N-2 etc query

Author

Topic

Buda56
Starting Member

14 Posts

Posted - 2009-08-05 : 01:19:50

Hi,
I would like to develop an SQL query that would produce the above type results. I need to create a query/report that returns items in a N, N-1 type senario also an aditional report that would give me a count. If the item is in N then it would not show in N-1, if it is not in N then it would be in a subsequent N-x dependant upon the date.
The data source is a list of machine names with dates when patched, a bit value that confirms the patching was successful, and I would like to produce a report that shows those that were patched and those that were not based on the month of the patch date ie. current month would be N, previous month would be N-1, 2 months previous would be N-2 etc.

Hope this makes sence..
Regards..
Peter

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-05 : 10:40:39

Hard to help without specifics (like DDL, DML, and expected output). But you'll probably need to make use of the built in function DATEDIFF.

How to post an "answerable" question:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

Buda56
Starting Member

14 Posts

Posted - 2009-08-05 : 19:42:56

Hi,
Ok sorry this is my first post here so here goes.
DDL:
[Code]
CREATE TABLE [dbo].[Master_SWD_PatchAll](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Account] [nvarchar](6) NULL,
[ServerName] [nvarchar](50) NULL,
[PDate] [datetime] NULL,
[PrStatus] [bit] NULL,
[PrSvcName] [nvarchar](max) NULL,
[PrSysStatus] [bit] NULL,
[PrSysSource] [nvarchar](max) NULL,
[PatchReq] [bit] NULL,
[PatchInst] [nvarchar](max) NULL,
[PatchInstStatus] [bit] NULL,
[PoSvcStatus] [bit] NULL,
[PoSvcName] [nvarchar](max) NULL,
[PoSysStatus] [bit] NULL,
[PoSysSource] [nvarchar](max) NULL,
[Comments] [varchar](max) NULL,
[Exempt] [bit] NULL,
CONSTRAINT [PK_Master_SWD_PatchAll] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
[/Code]

Not sure about the DML
[Code]
INSERT INTO [WPS_OPS].[dbo].[Master_SWD_PatchAll]
([Account]
,[ServerName]
,[PDate]
,[PrStatus]
,[PrSvcName]
,[PrSysStatus]
,[PrSysSource]
,[PatchReq]
,[PatchInst]
,[PatchInstStatus]
,[PoSvcStatus]
,[PoSvcName]
,[PoSysStatus]
,[PoSysSource]
,[Comments]
,[Exempt])
[/Code]

Haven't attempted a solution as not exactly sure as to how to go about this, all I can gather from the end result I require would be that I can get a count of Servers that meet criteria "N, N-1, N-2 etc" and perhaps have naother query that would return some sort of Drill down on the N, N-1 results so I can see the actual servers that meet the criteria.

The summary output would look something like:
N - 650
N-1 - 45
N-2 - 8
Etc

Detailed data would be something like:
Servername, Patchdate

In both instances above a Server that appears in N would not be in N-1, N-2 etc even though there would be an entry that would match that criteria.

I have thought of using datediff then grouping on the result but the issue of only appearing in one group is something I'm not sure how to deal with.

Hope this makes it clearer..
Regards..
Peter

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-05 : 20:52:05

I'm not following, post some sample data and then your desired output. Try to simplify the data to just illustrate what you are looking to do, we do not need the entire table layout, if you are not using all the columns.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Buda56
Starting Member

14 Posts

Posted - 2009-08-05 : 21:53:51

Hi,
Here is some sample data:
BBP FLNAPF007 16/03/2009
BBP FLNAPF007 16/04/2009
BBP FLNAPM004 16/03/2009
BBP FLNLCF006 16/03/2009
BBP FLNLCM006 16/04/2009
BBP FLNLKA015 16/03/2009
BBP FLNLKA016 16/02/2009
BBP FLNLKA017 16/03/2009
BBP FLNLKA018 16/04/2009
BBP FLNLKA019 16/03/2009
BBP FLNPKA005 16/03/2009
BBP FLNPKA006 16/03/2009
BBP FLNPKA008 16/03/2009
BBP FLNPKA014 16/04/2009
BBP FLNPKF002 16/04/2009
BBP FLNPKM003 16/03/2009
BBP FLNPKM023 16/03/2009

Sample summary out put would be:
N 5
N-1 11
N-2 1

Sample drill down output would be:
N BBP FLNPKA014 16/04/2009
N BBP FLNPKF002 16/04/2009
N BBP FLNLCM006 16/04/2009
N BBP FLNAPF007 16/04/2009
N BBP FLNLKA018 16/04/2009

N-1 BBP FLNAPF007 16/03/2009
N-1 BBP FLNAPM004 16/03/2009
N-1 BBP FLNLCF006 16/03/2009
N-1 BBP FLNLKA015 16/03/2009
N-1 BBP FLNLKA017 16/03/2009
N-1 BBP FLNLKA019 16/03/2009
N-1 BBP FLNPKA005 16/03/2009
N-1 BBP FLNPKA006 16/03/2009
N-1 BBP FLNPKA008 16/03/2009
N-1 BBP FLNPKM003 16/03/2009
N-1 BBP FLNPKM023 16/03/2009

N-2 BBP FLNLKA016 16/02/2009

Regards..
Peter

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-05 : 22:53:22

Still don't know if I'm following the sum lables, but this will give you the output you asked for.


declare @Mytable table(col1 varchar(3),col2 varchar(10),dt datetime)
Insert into @Mytable(Col1,col2,dt)
select 'BBP', 'FLNAPF007', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNAPF007', convert(datetime,'16/04/2009',104) union All
select 'BBP', 'FLNAPM004', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNLCF006', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNLCM006', convert(datetime,'16/04/2009',104) union All
select 'BBP', 'FLNLKA015', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNLKA016', convert(datetime,'16/02/2009',104) union All
select 'BBP', 'FLNLKA017', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNLKA018', convert(datetime,'16/04/2009',104) union All
select 'BBP', 'FLNLKA019', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKA005', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKA006', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKA008', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKA014', convert(datetime,'16/04/2009',104) union All
select 'BBP', 'FLNPKF002', convert(datetime,'16/04/2009',104) union All
select 'BBP', 'FLNPKM003', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKM023', convert(datetime,'16/03/2009',104)

declare @MYDATE datetime
set @MyDate =  convert(datetime,'16/04/2009',104)

select 'N' + case when datediff(Month,@MyDate,dt) = 0 then '' else convert(varchar(3),datediff(Month,@MyDate,dt)) end as SUMFIELD,count(*) as mycount
from @Mytable
group by dt
order by SumField

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Buda56
Starting Member

14 Posts

Posted - 2009-08-05 : 23:41:49

Hi,
thanks for that, the end result gives me the N, N-1 summary that I required but it also gives me duplicates in N and N-1. I need to have no duplicates in N or any subsequent N-x.

My next question I guess is how to apply the "Select" to an existing table of data??
[Code]
declare @Mytable table(col1 varchar(10),col2 varchar(20),dt datetime)
Insert into @Mytable(Col1,col2,dt)

select account, Servername, convert(datetime,[PDate], 104) FROM Master_SWD_PatchAll UNION ALL

declare @MYDATE datetime
set @MyDate = convert(datetime, GETDATE(),104)

select 'N' + case when datediff(Month,@MyDate,dt) = 0 then '' else convert(varchar(3),datediff(Month,@MyDate,dt)) end as SUMFIELD,count(*) as mycount
from @Mytable
group by dt
order by SumField

[/Code]

Unfortunately I get the following error "Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'declare'"

Regards..
Peter

matty
Posting Yak Master

161 Posts

Posted - 2009-08-06 : 01:36:00

quote:
Originally posted by Buda56

Hi,
thanks for that, the end result gives me the N, N-1 summary that I required but it also gives me duplicates in N and N-1. I need to have no duplicates in N or any subsequent N-x.

My next question I guess is how to apply the "Select" to an existing table of data??
[Code]
declare @Mytable table(col1 varchar(10),col2 varchar(20),dt datetime)
Insert into @Mytable(Col1,col2,dt)

select account, Servername, convert(datetime,[PDate], 104) FROM Master_SWD_PatchAll ~~UNION ALL~~

declare @MYDATE datetime
set @MyDate = convert(datetime, GETDATE(),104)

select 'N' + case when datediff(Month,@MyDate,dt) = 0 then '' else convert(varchar(3),datediff(Month,@MyDate,dt)) end as SUMFIELD,count(*) as mycount
from @Mytable
group by dt
order by SumField

[/Code]

Unfortunately I get the following error "Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'declare'"

Regards..
Peter

Buda56
Starting Member

14 Posts

Posted - 2009-08-06 : 06:56:00

Matty,
Unfortunately by removing the "Union All" I end up with multiple N's, N-1's, N-2's etc..

<Results>
NULL 585
N* 1
N-1 97
N-1 244
N-1 366
N-1 2
N-10 53
N-10 83
N-10 34
N-10 8
N-10 31
N-10 27
N-10 91
N-10 36
N-10 54
N-10 28
N-10 50
N-10 65
N-10 103
N-10 68
N-10 116
N-10 35
N-11 35
N-11 69
N-11 53
N-11 70
N-11 32
N-11 2
N-11 24
N-11 83
N-11 30
N-11 49
N-11 26
N-11 50
N-11 70
N-11 47
N-11 62
N-11 108
N-11 44
N-11 4
N-11 16
N-11 41
N-11 64
N-12 50
N-12 53
N-12 1
N-12 1
N-12 8
N-12 36
N-12 1
N-12 24
N-12 85
N-12 36
N-12 44
N-12 24
N-12 42
N-12 60
N-12 46
N-12 53
N-12 111
N-12 27
N-12 4
N-12 22
N-13 85
N-13 14
N-13 30
N-13 42
N-13 28
N-13 61
N-13 68
N-13 51
N-13 114
N-13 51
N-13 30
N-13 61
N-13 48
N-13 45
N-13 66
N-14 23
N-14 48
N-14 60
N-14 55
N-14 68
N-14 34
N-14 1
N-14 63
N-14 1
N-14 11
N-14 4
N-14 6
N-14 14
N-14 2
N-14 12
N-14 7
N-15 59
N-15 124
N-15 41
N-15 17
N-2 87
N-2 106
N-2 73
N-2 112
N-2 40
N-2 176
N-2 154
N-2 98
N-2 129
N-2 124
N-2 70
N-2 75
N-2 129
N-2 67
N-2 91
N-2 157
N-2 60
N-2 4
N-2 24
N-2 87
N-2 108
N-3 71
N-3 5
N-3 24
N-3 83
N-3 109
N-3 80
N-3 116
N-3 40
N-3 215
N-3 138
N-3 78
N-3 98
N-3 64
N-3 62
N-3 130
N-3 62
N-3 90
N-3 153
N-3 59
N-3 4
N-3 24
N-4 71
N-4 107
N-4 40
N-4 1
N-4 19
N-4 41
N-4 3
N-4 2
N-4 4
N-4 9
N-4 3
N-4 55
N-4 4
N-4 185
N-4 138
N-4 109
N-4 126
N-4 66
N-4 62
N-4 121
N-4 64
N-4 91
N-4 151
N-5 16
N-5 81
N-5 1
N-5 184
N-5 117
N-5 35
N-5 101
N-5 41
N-5 2
N-5 93
N-5 74
N-5 83
N-5 150
N-5 61
N-5 60
N-5 24
N-5 82
N-5 96
N-6 22
N-6 88
N-6 96
N-6 55
N-6 102
N-6 39
N-6 69
N-6 11
N-6 6
N-6 11
N-6 6
N-6 69
N-7 288
N-7 211
N-7 1
N-7 3
N-7 215
N-7 90
N-7 40
N-7 62
N-7 43
N-7 2
N-7 68
N-7 74
N-7 156
N-7 61
N-7 57
N-8 38
N-8 80
N-8 56
N-8 107
N-8 40
N-8 196
N-8 12
N-8 21
N-8 28
N-8 2
N-8 117
N-8 41
N-8 29
N-9 4
N-9 21
N-9 54
N-9 72
N-9 54
N-9 88
N-9 34
N-9 1
N-9 2
N-9 13
N-9 87
N-9 32
N-9 51
N-9 28
N-9 57
N-9 59
N-9 44
N-9 60
N-9 121
N-9 36
N-9 3
N-9 24
</Results>

Regards..
Peter

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-06 : 13:58:18

Since I can't actually see the data you are working with, my assumption is the duplicates are going to be due to the time of the record (i.e 1/1/2009 is differant than 1/1/2009 11:30 AM) if that is the case then just change the grouping to


select 'N' + case when datediff(Month,@MyDate,dt) = 0 then '' else convert(varchar(3),datediff(Month,@MyDate,dt)) end as SUMFIELD,count(*) as mycount
from @Mytable
group by datediff(Month,@MyDate,dt)
order by SumField

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Buda56
Starting Member

14 Posts

Posted - 2009-08-06 : 18:14:35

Vinnie,
Thanks that now gives me single N's, N-1's etc. I have one single question left with this and that is: "Is there anyway to return no duplicates in the N, N-1 etc i.e. a server in N should not appear in N-1 or any other variant on N-x and the same for any server that doesn't appear in N but appears in a varaint of N-x".

I was doing some work last night on a query that will show me the servername and the last patch month for each year which I will use to show a drill down.
<code>
SELECT vswp.ServerName, MAX(vswp.[Month]) AS 'Month',vswp.[Year]
FROM vwSWD_Win_PMonth vswp
WHERE vswp.[Year] = YEAR(GETDATE()) OR vswp.[Year] = (YEAR(GETDATE())-1)
GROUP BY vswp.ServerName,vswp.[Year]
ORDER BY vswp.ServerName
</code>

Gives results like
ADLDESA01 6 2009
ADLDESA02 6 2009
ADLDFEA01 12 2008
ADLDFEA01 7 2009
ADLDFEA02 11 2008
ADLDFEA02 6 2009
ADLDFEA03 12 2008
ADLDFEA03 7 2009
ADLDFEA04 11 2008
ADLDFEA04 6 2009
ADLDFEA09 12 2008
ADLDFEA09 7 2009
ADLDFEA10 7 2008
ADLDFEA11 12 2008
ADLDFEA11 7 2009
ADLDFEA13 12 2008
ADLDFEA13 7 2009
ADLDFEA14 12 2008
ADLDFEA14 7 2009
ADLDFEA15 12 2008
ADLDFEA15 7 2009
ADLDFEA16 12 2008
ADLDFEA16 7 2009
ADLDFEA17 12 2008
ADLDFEA17 7 2009
ADLDFEA18 11 2008
ADLDFEA18 7 2009
ADLDFEA19 10 2008
ADLDFEA19 7 2009
ADLDFEA20 12 2008
ADLDFEA20 7 2009
ADLDFEA21 12 2008
ADLDFEA21 7 2009
ADLDFEB01 12 2008
ADLDFEB01 7 2009

Regards..
Peter

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-06 : 23:40:47

I'm not following you. It will be a lot easier to answer your question if you post what you want your results to be using the above data.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Buda56
Starting Member

14 Posts

Posted - 2009-08-07 : 00:13:07

Vinnie,
Sorry for the confusion, what you provided is excellent and is what I require. The only extra thing I would like is there anyway to return no duplicates in the N, N-1 etc i.e. a server in N should not appear in N-1 or any other variant on N-x and the same for any server that doesn't appear in N but appears in a varaint of N-x.

My last post was only showing the work I am doing on getting a server list to match your summary view.

Regards..
Peter.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-07 : 14:31:59

Without you showing me sample data and exaclty what you want your results to be, all I can do is assume. But is this what you are looking for?

Basically for each Server if N exists it will show it and not show any prior n-x, and if doesn't exist then it will show all prior n-x.


declare @Mytable table(col1 varchar(3),col2 varchar(10),dt datetime)
Insert into @Mytable(Col1,col2,dt)
select 'BBP', 'FLNAPF007', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNAPF007', convert(datetime,'16/04/2009',104) union All
select 'BBP', 'FLNAPF007', convert(datetime,'16/04/2009',104) union All
select 'BBP', 'FLNAPF007', convert(datetime,'16/04/2009',104) union All
select 'BBP', 'FLNAPF007', convert(datetime,'16/04/2009',104) union All
select 'BBP', 'FLNAPF007', convert(datetime,'16/04/2009',104) union All
select 'BBP', 'FLNAPF007', convert(datetime,'16/04/2009',104) union All
select 'BBP', 'FLNAPF007', convert(datetime,'16/04/2009',104) union All
select 'BBP', 'FLNAPM004', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNLCF006', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNLCM006', convert(datetime,'16/04/2009',104) union All
select 'BBP', 'FLNLKA015', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNLKA016', convert(datetime,'16/02/2009',104) union All
select 'BBP', 'FLNLKA017', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNLKA017', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNLKA017', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNLKA017', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNLKA017', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNLKA018', convert(datetime,'16/04/2009',104) union All
select 'BBP', 'FLNLKA019', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKA005', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKA006', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKA006', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKA006', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKA006', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKA006', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKA006', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKA008', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKA014', convert(datetime,'16/04/2009',104) union All
select 'BBP', 'FLNPKF002', convert(datetime,'16/04/2009',104) union All
select 'BBP', 'FLNPKM003', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKM003', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKM003', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKM003', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKM003', convert(datetime,'16/03/2009',104) union All
select 'BBP', 'FLNPKM023', convert(datetime,'16/03/2009',104)

declare @MYDATE datetime
set @MyDate =  convert(datetime,'16/04/2009',104)


select Col2,'N' + case when datediff(Month,@MyDate,dt) = 0 then '' else convert(varchar(3),datediff(Month,@MyDate,dt)) end as SUMFIELD,count(*) as mycount
from @Mytable a
where datediff(Month,@MyDate,dt) = 0
group by Col2,datediff(Month,@MyDate,dt)
Union all
select Col2,'N' + case when datediff(Month,@MyDate,dt) = 0 then '' else convert(varchar(3),datediff(Month,@MyDate,dt)) end as SUMFIELD,count(*) as mycount
from @Mytable a
where not exists (Select * from @Mytable aa where aa.Col2 = a.Col2 and datediff(Month,@MyDate,aa.dt) = 0)
group by Col2,datediff(Month,@MyDate,dt)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Buda56
Starting Member

14 Posts

Posted - 2009-08-12 : 08:03:31

Vinnie,
Thanks that was exactly what I need, thank you for your time and patience.

Regards..
Peter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources