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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy