April 15, 2015 at 11:36 pm
It would be appreciated if anyone could assist with the following select problem I am trying to solve.
Select is from three tables, with a join required from Tbl1 to Tbl2 then from Tbl2 to Tbl3.
Tbl1cnt_fileMain data about physical file tracking
Tbl2std_bxctAttribute of file
Tbl3std_bmMultiple sub attributes of file (moves)
Tble3 holds multiple rows for a file with a date that I would like to get only the MAX(DATE) for.
I am getting a row for each sub attribute and have been unable to figure out how to get a single row having only the sub attribute with the latest date.
Below are scripts to create the three tables with a single row of data for Tbl1 & 2 and 3 rows for Tbl3. And the query as it stands so far.
I tried to create a sub query to get only the MAX(DATE) but am currently struggling to understand how to make it work.
Thanks
CREATE TABLE [dbo].[cnt_file](
[file_no] [binary](20) NOT NULL,
[file_key2] [binary](64) NOT NULL,
[file_internal_no] [binary](8) NOT NULL,
[file_key5] [binary](53) NOT NULL,
[file_type] [char](3) NOT NULL,
[file_name01] [char](50) NOT NULL,
[file_name02] [char](50) NOT NULL,
[file_name03] [char](50) NOT NULL,
[file_name04] [char](50) NOT NULL,
[file_dept] [char](4) NOT NULL,
[file_loc] [char](4) NOT NULL,
[file_create_d] [datetime] NULL,
[file_closed_d] [datetime] NULL,
[file_archive_d] [datetime] NULL,
[file_old_no] [binary](30) NULL,
[file_description1] [char](250) NOT NULL,
[file_description2] [char](75) NOT NULL,
[file_barcode_no] [binary](20) NULL,
[file_last_doc_no] [char](4) NOT NULL,
[file_trap_reason] [char](25) NOT NULL,
[file_active_until] [datetime] NULL,
[file_disposal] [char](10) NOT NULL,
[file_part_no] [binary](4) NOT NULL,
[file_latest_part] [char](1) NOT NULL,
[file_standard] [char](1) NOT NULL,
[file_key7] [binary](102) NOT NULL,
[file_sec] [binary](6) NULL,
[file_permanent] [char](1) NULL )
GO
INSERT INTO [dbo].[cnt_file]
([file_no]
,[file_key2]
,[file_internal_no]
,[file_key5]
,[file_type]
,[file_name01]
,[file_name02]
,[file_name03]
,[file_name04]
,[file_dept]
,[file_loc]
,[file_create_d]
,[file_closed_d]
,[file_archive_d]
,[file_old_no]
,[file_description1]
,[file_description2]
,[file_barcode_no]
,[file_last_doc_no]
,[file_trap_reason]
,[file_active_until]
,[file_disposal]
,[file_part_no]
,[file_latest_part]
,[file_standard]
,[file_key7]
,[file_sec]
,[file_permanent] )
VALUES
(0x3030312F3130302F3031312F3030303032202020
,0x3030332041444D494E495354524157484F4C4543555252454E54204153544F434B294F4646454E444552202020202020
,0x3030303030343933
,0x30303341444D494E495354524154494F4E204F4620544845204445504152544D454E54204153202020202020
,'POL'
,'ADMINISTRATION OF STUFF AS A WHOLE'
,'CURRENT ASSETS (EQUIPMENT-INVENTORY-STOCK)'
,'CUSTOMER'
,'PROVISION OF STUFF TO CUST'
, ''
, ''
,'1997-03-17 00:00:00.000'
,'1997-07-30 00:00:00.000'
,'2008-03-20 00:00:00.000'
,0x3034302F3131302F3032302F303030303220202020202020202020202020
,'BLAH BLAH BLAH BLAH BLAH BLAH BLAH BLAH BLAH BLAH BLAH BLAH BLAH BLAH BLAH '
,''
,0x3037313920202020202020202020202020202020
,0007
,'Warnings go here'
,'2001-11-27 00:00:00.000'
,'POL'
,0x30303031
,'Y'
,1
,0x30303341444D494E495354524154494F4E204F4620544845204445504152544D454E542020202020202020
,0x303030303031
,'N' )
GO
----
CREATE TABLE [dbo].[std_bxct](
[bxct_box_no] [binary](20) NOT NULL,
[bxct_file_no] [binary](20) NOT NULL,
[bxct_part_no] [binary](4) NOT NULL,
[bxct_doc_regno] [binary](10) NOT NULL,
[bxct_db_name] [binary](10) NOT NULL
)
GO
INSERT INTO [dbo].[std_bxct]
([bxct_box_no]
,[bxct_file_no]
,[bxct_part_no]
,[bxct_doc_regno]
,[bxct_db_name])
VALUES
(0x4C52532031303133362020202020202020202020
,0x3030312F3130302F3031312F3030303032202020
,0x30303031
,0x20202020202020202020
,0x43454E5452414C202020 )
GO
----
CREATE TABLE [dbo].[std_bm](
[bm_box_no] [binary](20) NOT NULL,
[bm_date] [datetime] NULL,
[bm_time] [binary](4) NOT NULL,
[bm_ao_no] [char](10) NOT NULL,
[bm_source] [char](1) NOT NULL,
[bm_space_no] [char](20) NOT NULL
)
GO
INSERT INTO [dbo].[std_bm]
([bm_box_no]
,[bm_date]
,[bm_time]
,[bm_ao_no]
,[bm_source]
,[bm_space_no])
VALUES
(0x4C52532031303133362020202020202020202020
,'2008-07-14 00:00:00.000'
,0x31343438
,''
,'R'
,'AUSDOC' )
INSERT INTO [dbo].[std_bm]
([bm_box_no]
,[bm_date]
,[bm_time]
,[bm_ao_no]
,[bm_source]
,[bm_space_no])
VALUES
(0x4C52532031303133362020202020202020202020
,'2008-09-18 00:00:00.000'
,0x31323234
,''
,'R'
,'BOXIT' )
INSERT INTO [dbo].[std_bm]
([bm_box_no]
,[bm_date]
,[bm_time]
,[bm_ao_no]
,[bm_source]
,[bm_space_no])
VALUES
(0x4C52532031303133362020202020202020202020
,'2011-05-27 00:00:00.000'
,0x31363231
,''
,'R'
,'RECALL' )
GO
declare @file_num varchar(20)
set @file_num = '001/100/011/00002'
SELECT
CAST(cntfl.file_no AS VARCHAR) [File No:]
,CAST(cntfl.file_part_no AS VARCHAR) [Part No:]
,CAST(cntfl.file_internal_no AS VARCHAR) [Internal No:]
,CAST(stdbxct.bxct_box_no AS VARCHAR) [Box:]
,stdbm.bm_space_no [At:] -- Needed as a single row
,(SELECT TOP 1 MAX(stdbm.bm_date) -- Needed as a single row
FROM std_bm AS stdbm
WHERE stdbm.bm_box_no = stdbxct.bxct_box_no) [Since:]
,CAST(cntfl.file_barcode_no AS VARCHAR) [Barcode:]
,CASE CAST(file_key2 AS VARCHAR (3))
WHEN '001' THEN '001 - ACTIVE'
WHEN '002' THEN '002 - CLOSED'
WHEN '003' THEN '003 - ARCHIVED'
WHEN '004' THEN '004 - INTERMEDIATE STORAGE'
WHEN '005' THEN '005 - DESTROYED'
WHEN '006' THEN '006 - MISSING/LOST'
ELSE ''
END [Disposition:]
,cntfl.file_name01 [Title 1:]
,cntfl.file_name02 [Title 2:]
,cntfl.file_name03 [Title 3:]
,cntfl.file_name04 [Title 4:]
,cntfl.file_create_d [Date Created:]
,cntfl.file_closed_d [Date Closed:]
,cntfl.file_active_until [Active Until:]
,cntfl.file_trap_reason [Warning:]
FROM cnt_file AS cntfl
/*
If the LEFT JOIN condition is not satisfied, then any column in your SELECT statement that begins with stdbxct or stdaud will be shown as NULL.
An INNER JOIN would filter those out.
*/
LEFT JOIN std_bxct AS stdbxct ON stdbxct.bxct_file_no = cntfl.file_no
LEFT JOIN std_bm AS stdbm ON stdbm.bm_box_no = stdbxct.bxct_box_no
WHERE file_no = @file_num
April 16, 2015 at 1:50 am
Can you please post expected output as well?
-- Gianluca Sartori
April 16, 2015 at 12:17 pm
This is a guess at the output you are looking for:
declare @file_num varchar(20)
set @file_num = '001/100/011/00002'
SELECT
CAST(cntfl.file_no AS VARCHAR) [File No:]
,CAST(cntfl.file_part_no AS VARCHAR) [Part No:]
,CAST(cntfl.file_internal_no AS VARCHAR) [Internal No:]
,CAST(stdbxct.bxct_box_no AS VARCHAR) [Box:]
,stdbm.[At:]
,stdbm.MaxDate [Since:]
,CAST(cntfl.file_barcode_no AS VARCHAR) [Barcode:]
,CASE CAST(file_key2 AS VARCHAR (3))
WHEN '001' THEN '001 - ACTIVE'
WHEN '002' THEN '002 - CLOSED'
WHEN '003' THEN '003 - ARCHIVED'
WHEN '004' THEN '004 - INTERMEDIATE STORAGE'
WHEN '005' THEN '005 - DESTROYED'
WHEN '006' THEN '006 - MISSING/LOST'
ELSE ''
END [Disposition:]
,cntfl.file_name01 [Title 1:]
,cntfl.file_name02 [Title 2:]
,cntfl.file_name03 [Title 3:]
,cntfl.file_name04 [Title 4:]
,cntfl.file_create_d [Date Created:]
,cntfl.file_closed_d [Date Closed:]
,cntfl.file_active_until [Active Until:]
,cntfl.file_trap_reason [Warning:]
FROM cnt_file AS cntfl
/*
If the LEFT JOIN condition is not satisfied, then any column in your SELECT statement that begins with stdbxct or stdaud will be shown as NULL.
An INNER JOIN would filter those out.
*/
LEFT JOIN std_bxct AS stdbxct ON stdbxct.bxct_file_no = cntfl.file_no
LEFT JOIN
(
Select bm_box_no, bm_date MaxDate, bm_space_no [at:],
ROW_NUMBER() over(partition by bm_box_no order by bm_date desc) RowNum
from std_bm
) as stdbm on stdbm.bm_box_no = stdbxct.bxct_box_no and stdbm.RowNum = 1
WHERE file_no = @file_num
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 19, 2015 at 4:09 pm
@spaghettidba, apologies for the delay in replying. Other high priority work intervened. LinksUp reply below gave what was needed. However thanks for looking at the post, and I take your point that posting the desired result would have helped. 🙂
April 20, 2015 at 8:27 am
L30 (4/19/2015)
I will have to study your solution a bit, I don't understand the syntax as yet. :w00t:
The key is the Row_Number function. Row_Number does just that. It numbers the returned rows in sequential order. But what makes it a very useful function is the partition and order clauses. The partition creates a "window" of rows that are ordered on box_no. When a new partition starts, ie, the box_no changes, the row numbering starts over. Then within each partition, it orders the rows by date in descending order. Since you only wanted the earliest date for each box_no, looking at rowNum = 1 gives us just that!
If you have any other questions about this, don't hesitate to ask.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 20, 2015 at 8:09 pm
LinksUp (4/20/2015)
The key is the Row_Number function. Row_Number does just that. It numbers the returned rows in sequential order. But what makes it a very useful function is the partition and order clauses. The partition creates a "window" of rows that are ordered on box_no. When a new partition starts, ie, the box_no changes, the row numbering starts over. Then within each partition, it orders the rows by date in descending order. Since you only wanted the earliest date for each box_no, looking at rowNum = 1 gives us just that!
If you have any other questions about this, don't hesitate to ask.
Thanks, that explanation helps more than you would believe.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply