January 29, 2012 at 11:06 am
Hi All,
I have following Query and below is output, I need your help to get following results. I have also attached screen shot for your ready reference.
ISSU -First Transaction
ISSU -Last Transaction
PREC -First Transaction
PREC -Last Transaction
Query:
Select
CT_STOCKNO_P,
CT_STOCKSUMMARY,
CT_QUANTITY,
TR_QUANTITY,
TR_UNITCOST,
TR_TYPECODE,
TR_DATE
From ST_COMMODITYTYPE
Inner Join ST_TRANSACTIONS
ON ST_TRANSACTIONS.TR_STOCKNO=ST_COMMODITYTYPE.CT_STOCKNO_P
WHERE CT_STOCKNO_P='01-01-01-010' and (TR_TYPECODE = 'ISSU' OR TR_TYPECODE = 'PREC')
Output:
CT_STOCKNO_PCT_STOCKSUMMARYCT_QUANTITYTR_QUANTITYTR_UNITCOSTTR_TYPECODETR_DATE
01-01-01-010Acetone Cleaning Chemical287.51602.30PREC2007-07-28 00:00:00.000
01-01-01-010Acetone Cleaning Chemical287.52000.775PREC2007-10-31 00:00:00.000
01-01-01-010Acetone Cleaning Chemical287.53601.4528Issu2007-12-12 00:00:00.000
01-01-01-010Acetone Cleaning Chemical287.5251.20PREC2008-04-10 00:00:00.000
01-01-01-010Acetone Cleaning Chemical287.51600.9344PREC2009-03-25 00:00:00.000
01-01-01-010Acetone Cleaning Chemical287.51600.9703Issu2009-04-22 00:00:00.000
01-01-01-010Acetone Cleaning Chemical287.54800.9062PREC2009-04-27 00:00:00.000
01-01-01-010Acetone Cleaning Chemical287.51800.9094Issu2009-04-29 00:00:00.000
01-01-01-010Acetone Cleaning Chemical287.5200.9094Issu2010-03-14 00:00:00.000
01-01-01-010Acetone Cleaning Chemical287.510.9094Issu2010-03-29 00:00:00.000
01-01-01-010Acetone Cleaning Chemical287.550.9094Issu2011-02-09 00:00:00.000
01-01-01-010Acetone Cleaning Chemical287.540.9094Issu2011-02-12 00:00:00.000
01-01-01-010Acetone Cleaning Chemical287.540.9094Issu2011-02-12 00:00:00.000
01-01-01-010Acetone Cleaning Chemical287.550.9094Issu2011-02-19 00:00:00.000
01-01-01-010Acetone Cleaning Chemical287.520.9094Issu2011-02-20 00:00:00.000
01-01-01-010Acetone Cleaning Chemical287.50.50.9094Issu2011-02-26 00:00:00.000
January 29, 2012 at 12:24 pm
not entirly sure what you want your results to look like but i read that you want to re order the result set in your screenshot? or do you want to find 4 different results? 1 for each?
or are these transactions related for 1 client/user/etc ??
***The first step is always the hardest *******
January 29, 2012 at 12:34 pm
Yes, I want four different results. For Example in attached Screenshot
The First "Issu" Transaction made on '2007-12-12'
The Last "Issu" Transaction made on '2011-02-26'
The First "PREC" Transaction made on '2007-07-28'
The Last "PREC" Transaction made on '2009-04-27'
I have tried simple MIN() and MAX() Function but I am unable to achieve desired results.
Thanks in advance!
January 29, 2012 at 12:43 pm
any chance that you might provide some set up script and sample data for your two tables pls...we can then easily cut and paste into SSMS to give you a tried and tested answer.
eg....
---=== EXAMPLE SET UP SCRIPT ===---
CREATE TABLE [dbo].[Data](
[DateId] [datetime] NOT NULL,
[Data] [int] NOT NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Data]([DateId], [Data])
SELECT '20120101 00:00:00.000', 100 UNION ALL
SELECT '20120103 00:00:00.000', 50
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 29, 2012 at 12:59 pm
This is totally untested but may provide a pointer
Select
CT_STOCKNO_P,
CT_STOCKSUMMARY,
CT_QUANTITY,
TR_QUANTITY,
TR_UNITCOST,
TR_TYPECODE,
TR_DATE,
MIN(TR_DATE) OVER (PARTITION BY TR_TYPECODE) AS FIRSTDATE,
MAX(TR_DATE) OVER (PARTITION BY TR_TYPECODE) AS LASTDATE
From ST_COMMODITYTYPE
Inner Join ST_TRANSACTIONS
ON ST_TRANSACTIONS.TR_STOCKNO=ST_COMMODITYTYPE.CT_STOCKNO_P
WHERE CT_STOCKNO_P='01-01-01-010' and (TR_TYPECODE = 'ISSU' OR TR_TYPECODE = 'PREC')
Dave
January 29, 2012 at 11:05 pm
Dear Dave,
The said Query is Perfect for Single Stock No. For example as you used WHERE Clause for Stock No.(CT_STOCKNO_P='01-01-01-010') but when I remove this clause, same Min(TR_Date) and Max(TR_Date) is reflecting for the entire TR_DATE field.
Please see attached Picture 2.png for your ready reference.
January 30, 2012 at 1:02 am
Looking in BOL for the OVER clause http://msdn.microsoft.com/en-us/library/ms189461(v=sql.90).aspx"> http://msdn.microsoft.com/en-us/library/ms189461(v=sql.90).aspx we see
Aggregate Window Functions
<OVER_CLAUSE> :: =
OVER ( [ PARTITION BY value_expression , ... [ n ] ] )
which means you can partition by a number of different columns i.e typecode and stock number in the same clause.
Dave
January 30, 2012 at 5:10 am
Thanks Dave, its really help me a lot.
January 30, 2012 at 2:11 pm
Something like...
Select
CT_STOCKNO_P,
CT_STOCKSUMMARY,
CT_QUANTITY,
TR_QUANTITY,
TR_UNITCOST,
TR_TYPECODE,
MIN(CASE WHEN TR_TYPECODE = 'PREC' THEN TR_DATE END) AS MINPRECDATE,
MAX(CASE WHEN TR_TYPECODE = 'PREC' THEN TR_DATE END) AS MAXPRECDATE,
MIN(CASE WHEN TR_TYPECODE = 'ISSU' THEN TR_DATE END) AS MINISSUDATE,
MAX(CASE WHEN TR_TYPECODE = 'ISSU' THEN TR_DATE END) AS MAXISSUDATE,
From ST_COMMODITYTYPE
Inner Join ST_TRANSACTIONS
ON ST_TRANSACTIONS.TR_STOCKNO=ST_COMMODITYTYPE.CT_STOCKNO_P
WHERE CT_STOCKNO_P='01-01-01-010' and (TR_TYPECODE = 'ISSU' OR TR_TYPECODE = 'PREC')
January 31, 2012 at 1:06 am
Rich
That would work just as well for a single stock item. However, the OP goes onto to say
The said Query is Perfect for Single Stock No. For example as you used WHERE Clause for Stock No.(CT_STOCKNO_P='01-01-01-010') but when I remove this clause, same Min(TR_Date) and Max(TR_Date) is reflecting for the entire TR_DATE field.
Based on my suggestion I would expect the resulting statement to be something like
Select
CT_STOCKNO_P,
CT_STOCKSUMMARY,
CT_QUANTITY,
TR_QUANTITY,
TR_UNITCOST,
TR_TYPECODE,
TR_DATE,
MIN(TR_DATE) OVER (PARTITION BY TR_TYPECODE, CT_STOCKNO_P) AS FIRSTDATE,
MAX(TR_DATE) OVER (PARTITION BY TR_TYPECODE, CT_STOCKNO_P) AS LASTDATE
From ST_COMMODITYTYPE
Inner Join ST_TRANSACTIONS
ON ST_TRANSACTIONS.TR_STOCKNO=ST_COMMODITYTYPE.CT_STOCKNO_P
WHERE (TR_TYPECODE = 'ISSU' OR TR_TYPECODE = 'PREC')
Again this is totally untested
Dave
January 31, 2012 at 1:42 am
Thank you again Dave, this is Simple and Perfect Query and can easily caculate entire large rows.
January 31, 2012 at 1:52 am
I'm glad I could help and thanks for the feedback.
Dave
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply