March 10, 2011 at 9:32 am
I am trying to pull the correct date from a set of records. Using this code to set up the scenario:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
Item varchar(30),
Location varchar(30),
Qty decimal(21,8),
TranDate datetime
)
--===== Insert the test data into the test table
INSERT INTO #mytable
(Item, Location, Qty, TranDate)
SELECT '1A103336','FQAHold', -1.00000000, '2010-09-08 14:54:22.000' UNION ALL
SELECT '1A103336','FQAHold', -1.00000000, '2011-01-12 10:02:17.000' UNION ALL
SELECT '1A103336','FQAHold', 1.00000000, '2010-08-09 10:16:43.000' UNION ALL
SELECT '1A103336','FQAHold', 1.00000000, '2011-01-11 10:09:11.000' UNION ALL
SELECT '1A103336','FQAHold', 1.00000000, '2011-02-14 09:51:20.000' UNION ALL
SELECT '1A103336','FQAHold', 1.00000000, '2011-03-08 06:52:57.000' UNION ALL
SELECT '1A103336', 'Quarantine', -2.00000000, '2009-09-13 16:31:44.040' UNION ALL
SELECT '1A103336', 'Quarantine', -1.00000000, '2009-09-10 17:18:39.000' UNION ALL
SELECT '1A103336', 'Quarantine', -1.00000000, '2011-01-11 10:09:11.000' UNION ALL
SELECT '1A103336', 'Quarantine', 2.00000000, '2010-12-29 10:21:07.000' UNION ALL
SELECT '1A103336', 'Quarantine', 3.00000000, '2009-09-04 12:09:10.000'
--==== Select the records
SELECT Item,
Location,
SUM(Qty) Qty,
DATEDIFF(DAY, MAX(TranDate), GETDATE()) LastAddDate
FROM #mytable
GROUP BY ITEM, Location
We get the following results:
Item Location Qty LastAddDate
------------------------------ ------------------------------ ---------------------------------------- -----------
1A103336 FQAHold 2.00000000 2
1A103336 Quarantine 1.00000000 58
The LastAddDate column is the difference between today and the last transaction, grouped by location.
What I actually need for this column is the FIRST TranDate for a POSITIVE Qty. For the first record I need to calculate from the date '2010-08-09 10:16:43.000' , and for the second record I need to calculate from the date '2010-12-29 10:21:07.000', so the results should look like this:
Item Location Qty LastAddDate
------------------------------ ------------------------------ ---------------------------------------- -----------
1A103336 FQAHold 2.00000000 213
1A103336 Quarantine 1.00000000 71
Is there any way to do this? If I need to provide more information I will gladly do it.
Thank you.
March 10, 2011 at 10:29 am
This works (add after your INSERT):
--===== If the temp table already exists, drop it
IF OBJECT_ID('TempDB..#temp','U') IS NOT NULL DROP TABLE #temp
--===== Create the temp table with
CREATE TABLE #temp
(
Item varchar(30),
Location varchar(30),
Qty decimal(21,8),
LastAddDate datetime NULL
)
--==== Select the records
INSERT INTO #temp
(Item, Location, Qty, LastAddDate )
SELECT Item,
Location,
SUM(Qty) Qty,
NULL
FROM #mytable
GROUP BY Item, Location
MERGE #temp AS target
USING ( SELECT Item, Location, MIN(TranDate)
FROM #mytable
WHERE Qty > 0
GROUP BY Item, Location ) AS source (Item, Location, TranDate)
ON ( target.Item = source.Item AND target.Location = source.Location )
WHEN MATCHED
THEN UPDATE SET target.LastAddDate = source.TranDate;
SELECT *
FROM #temp
March 10, 2011 at 11:01 am
Okay, we're almost there. Combining our codes, I now have:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #temp
--===== Create the test table with
CREATE TABLE #mytable
(
Item varchar(30),
Location varchar(30),
Qty decimal(21,8),
TranDate datetime
)
CREATE TABLE #temp
(
Item varchar(30),
Location varchar(30),
Qty decimal(21,8),
LastAddDate datetime
)
--===== Insert the test data into the test table
INSERT INTO #mytable
(Item, Location, Qty, TranDate)
SELECT '1A103336','FQAHold', -1.00000000, '2010-09-08 14:54:22.000' UNION ALL
SELECT '1A103336','FQAHold', -1.00000000, '2011-01-12 10:02:17.000' UNION ALL
SELECT '1A103336','FQAHold', 1.00000000, '2010-08-09 10:16:43.000' UNION ALL
SELECT '1A103336','FQAHold', 1.00000000, '2011-01-11 10:09:11.000' UNION ALL
SELECT '1A103336','FQAHold', 1.00000000, '2011-02-14 09:51:20.000' UNION ALL
SELECT '1A103336','FQAHold', 1.00000000, '2011-03-08 06:52:57.000' UNION ALL
SELECT '1A103336', 'Quarantine', -2.00000000, '2009-09-13 16:31:44.040' UNION ALL
SELECT '1A103336', 'Quarantine', -1.00000000, '2009-09-10 17:18:39.000' UNION ALL
SELECT '1A103336', 'Quarantine', -1.00000000, '2011-01-11 10:09:11.000' UNION ALL
SELECT '1A103336', 'Quarantine', 2.00000000, '2010-12-29 10:21:07.000' UNION ALL
SELECT '1A103336', 'Quarantine', 3.00000000, '2009-09-04 12:09:10.000'
--==== Select the records
INSERT INTO #temp
(Item, Location, Qty, LastAddDate )
SELECT Item,
Location,
SUM(Qty) Qty,
NULL
FROM #mytable
GROUP BY Item, Location
MERGE #temp AS target
USING ( SELECT Item, Location, MIN(TranDate)
FROM #mytable
WHERE Qty > 0
GROUP BY Item, Location ) AS source (Item, Location, TranDate)
ON ( target.Item = source.Item AND target.Location = source.Location )
WHEN MATCHED
THEN UPDATE SET target.LastAddDate = source.TranDate;
SELECT *
FROM #temp
However, when I do that, I get the following errors:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'AS'.
Piecing it out, I find that the error is with the MERGE statement.
What am I doing wrong?
Thanks.
March 10, 2011 at 11:05 am
What version of SQL Server are you using?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 10, 2011 at 12:13 pm
SQL 2000
March 10, 2011 at 12:57 pm
Well, that explains it - my code was written (and runs) under 2008 R2.
I will try to ocme up with something backward compatible.
March 10, 2011 at 1:12 pm
Thanks. I apologize for that. I am used to working in 2005 and 2008. I forgot this was 2000. I appreciate your efforts on this.
March 10, 2011 at 1:37 pm
How about this?
SELECT Item,
Location,
SUM(Qty) Qty,
DATEDIFF(DAY, ( select MAX(TranDate) from #mytable T_inner
where T_inner.Item = T_Outer.Item and
T_inner.Location = T_Outer.Location
and T_inner.Qty > 0)
, GETDATE()) LastAddDate
FROM #mytable T_Outer
GROUP BY ITEM, Location
I just made the the MAX into a subquery, thats all
March 10, 2011 at 2:05 pm
Yes, that would work. For larger tables I would do this:
SELECT Item,
Location,
SUM(Qty) Qty,
MAX( CASE
WHEN Qty < 0 THEN NULL -- aggregate functions ignore nulls
ELSE DATEDIFF( ... )
END ) LastAddDate
FROM #mytable
GROUP BY Item, Location
March 11, 2011 at 12:19 am
Thank you very much. Your help is greatly appreciated.
Steve
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply