November 5, 2008 at 12:49 pm
After looking at the posts for Max(Date) and trying several of them I am still unable to get the last download date since I have multiple status codes for a single download type.
This will return a download status but the status code is null.
select
b.client_id
, b.bu_id
, b.long_name
, MaxDate.download_type
, MaxDate.Transmit_Status_code
, MaxDate.Max_dwnld as Timestamp
from Transmit_Manifest tm (nolock)
join db_wh..loc_bu b (nolock)
on b.bu_id = tm.business_unit_id
Left outer join
(Select
tmd.business_unit_id
, tmd.download_type
, maxdate2.transmit_status_code
, Max(tmd.transmit_timestamp) as Max_dwnld
from Transmit_Manifest tmd (nolock)
left outer join
(Select
tmd2.transmit_status_code, Max(tmd2.transmit_timestamp) as Max_Status_Date
from transmit_manifest tmd2 (nolock)
group by tmd2.transmit_status_code) as MaxDate2
on tmd.transmit_timestamp = MaxDate2.Max_Status_date
where tmd.download_type = 'A' --A = Full Download
group by tmd.business_unit_id
, tmd.download_type
, maxdate2.transmit_status_code
) as MaxDate
on tm.business_unit_id = Maxdate.business_unit_id
where b.bu_id = tm.business_unit_id
and b.client_id = '1000002'
and tm.download_type = 'a'
and b.active = 'Y'
Group by b.client_id, b.bu_id, b.long_name
, MaxDate.download_type
, Maxdate.Max_dwnld
, MaxDate.Transmit_status_code
order by b.client_id, b.long_name
Results
client_idbu_idlong_name Download_TypeTransmit_Status_codeTimestamp
10000021002359300362 - Wayne, NJFull Download 11/5/2008
10000021002521306000 - Wayne, NJFull Download 11/5/2008
10000021002494307630 - Kinnelon, NJFull Download 11/5/2008
10000021002357334726 - Wayne, NJFull Download 11/5/2008
10000021000011336078 - Fort Lee, NJFull Download 11/5/2008
10000021002358337102 - Haskell, NJFull Download 11/5/2008
10000021002274337568 - Pequannock, NJFull Download 11/5/2008
Any help would be appreciated.
Thanks
November 5, 2008 at 2:26 pm
How about the DDL for the tables, some sample data (as insert statements that can be cut, paste, and executed in SSMS) to load into the tables, and the expected results from the query based on the sample data.
Any questions regarding this request, please read the article referenced below.
November 5, 2008 at 7:33 pm
Thanks for pointing me in the right direction.
Here is the sql to create a table and the test data.
--===== If the test table already exists, drop it
IF OBJECT_ID('SCINFO..Transmit_Manifest','U') IS NOT NULL
DROP TABLE Transmit_Manifest
--===== Create the test table with
CREATE TABLE [dbo].[Transmit_Manifest] (
[Manifest_ID] int NOT NULL,
[Business_unit_id] int NOT NULL,
[Download_Type] nvarchar(1) NOT NULL,
[Transmit_Status_Code] nvarchar(1) NOT NULL,
[Transmit_Timestamp] datetime NOT NULL,
PRIMARY KEY ([Manifest_ID])
)
--===== All Inserts into the IDENTITY column
--SET IDENTITY_INSERT dbo.Transmit_Manifest ON
--===== Insert the test data into the test table
INSERT INTO dbo.Transmit_Manifest (Manifest_ID, Business_unit_ID, Download_Type, Transmit_Status_Code, Transmit_Timestamp)
SELECT '1661452','1002359','E','A','Aug 16 2008 11:43AM' UNION ALL
SELECT '1661593','1002359','A','A','Aug 16 2008 2:41PM' UNION ALL
SELECT '1663149','1002359','P','A','Aug 19 2008 8:17AM' UNION ALL
SELECT '1666739','1002359','A','F','Aug 23 2008 12:59AM' UNION ALL
SELECT '1671107','1002359','A','R','Aug 28 2008 12:59AM' UNION ALL
SELECT '1673100','1002359','P','A','Aug 30 2008 12:46PM' UNION ALL
SELECT '1674561','1002359','R','X','Sep 2 2008 12:59AM' UNION ALL
SELECT '1678025','1002359','R','A','Sep 5 2008 12:59AM' UNION ALL
SELECT '1633744','1002521','A','A','Jul 26 2008 12:41AM' UNION ALL
SELECT '1633756','1002521','E','A','Jul 26 2008 12:48AM' UNION ALL
SELECT '1634901','1002521','E','A','Jul 26 2008 10:57AM' UNION ALL
SELECT '1636630','1002521','A','D','Jul 28 2008 2:58PM' UNION ALL
SELECT '1637766','1002521','E','A','Jul 30 2008 12:41AM' UNION ALL
SELECT '1641216','1002521','A','A','Aug 1 2008 12:51PM' UNION ALL
SELECT '1642161','1002521','A','X','Aug 2 2008 12:41AM' UNION ALL
SELECT '1658038','1002521','A','R','Aug 13 2008 12:41AM' UNION ALL
SELECT '1676424','1002521','E','A','Sep 3 2008 1:00PM'
When I run this query against the table I get two rows, one for each business unit id.
Select tm.Business_unit_id
, tm.Download_Type
, TM1.MaxTime
--, tm.Transmit_Status_Code
from transmit_Manifest tm
--Limits the data set to have the max timestamp
left outer join
(Select tmd.Business_unit_id, Max(tmd.Transmit_Timestamp) as MaxTime
from transmit_manifest tmd
group by tmd.Business_unit_id) as TM1
on TM1.business_unit_ID = tm.Business_unit_id
Where tm.download_type = 'A'
Group by tm.Business_unit_id, tm.Download_Type, tm1.MaxTime
--, tm.Transmit_Status_Code
As soon as I add the tm.Transmit_Status_Code, I then get 7 rows since the download type has multiple status codes with different timestamps. I
am looking to get the last Transmit_timestamp from the Download_Type with the associated Transmit_Status_Code. I have not been able to figure out the how to limit the data set in the left out join.
Thanks for any help.
November 5, 2008 at 9:47 pm
Just missing one thing, the expected results. Based on the sample data provided, what SHOULD the output look like. You should be able to put that together manually, knowing the basis of the logic you are attempting to implement.
November 5, 2008 at 11:31 pm
The expected results based on the data provided should be:
Business_Unit_ID Download_type Status_code timestamp
1002359 A R 8/28/2008 12:59:00 AM
1002521 A R 8/13/2008 12:41:00 AM
November 6, 2008 at 2:59 am
SELECT tm.Business_unit_id, tm.Download_Type, tm.transmit_status_code,TM.Transmit_Timestamp
FROM Transmit_Manifest TM
INNER JOIN
(
SELECT Business_unit_id, MAX(Manifest_ID) AS MaxManifestid
FROM Transmit_Manifest
WHERE download_type = 'A'
GROUP BY Business_unit_id ) MAXMANID
ON TM.MANIFEST_ID = MAXMANID.MaxManifestid
does this give you what you are looking for
regards Graham
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 6, 2008 at 9:05 am
Yes this gives me the desired results.
I was so focused on the timestamp I did not think about the Manifest_ID.
THANKS!!
Jason
November 6, 2008 at 9:16 am
Jason...you can get the same result using your timestamp
SELECT tm.Business_unit_id, tm.Download_Type, tm.transmit_status_code,TM.Transmit_Timestamp
FROM Transmit_Manifest TM
INNER JOIN
(SELECT MAX(Transmit_timestamp) AS MaxTimeStamp
FROM Transmit_Manifest
Where download_type = 'A'
GROUP BY Business_unit_id ) MAXTIME
ON TM.Transmit_Timestamp=MAXTIME.MaxTimeStamp
gah
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 6, 2008 at 9:54 am
When I tried the Max(timestamp) query on my larger data set, I got multiples on business units if the timestamps was exactly the same for the download types. Meaning that I got download_type 'A' and 'B'. My guess is that multiple downloads were done at the same time and given the same timestamp.
This is probably what threw me the first time around, trying to figure out an inner query to the inner query. I think the max(manifest_id) query is what I needed based on what I found when I ran the Max(timestamp) query.
Jason
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply