How do I pull data from a data set (SQL 10) for specific point in time?

5 pts.
Tags:
SQL
I need to pull a product ID, unit price and package price for each product, at a specific point in time from a data set. Data set is small, 5 columns, 968 rows. what I need is more specific. I need the unit price at the following points in time: Most days of each quarter; day 45 of each quarter, first calendar day of each quarter, and mid point of each quarter. My dates in the data are different points in time, not beginning or end of month or the quarter and range from 2017 back to 1993.  Below is a sample of the data: NDC is my product ID.    
NDC STARTDATE EndDate UNITPRICE PACKAGEPRICE 00169750111          2001-08-27 00:00:00.000 2002-01-15 00:00:00.000 3.975 39.75 00169750111          2002-01-16 00:00:00.000 2002-07-17 00:00:00.000 4.174 41.74 00169750111          2002-07-18 00:00:00.000 2002-12-18 00:00:00.000 4.508 45.08 00169750111          2002-12-19 00:00:00.000 2003-09-03 00:00:00.000 4.914 49.14 00169750111          2003-09-04 00:00:00.000 2004-03-15 00:00:00.000 5.356 53.56


Software/Hardware used:
SQL 10

Answer Wiki

Thanks. We'll let you know when a new response is added.
Try using a CASE in your select statement for each of the conditions you specified.
If this does not help post your code. The issue with the dates is we don’t know if they are calendar based or a fiscal calendar base. What may be a 1st qtr calendar could be a 3rd qtr calendar.
Something like this
SELECT part,
(CASE WHEN DATE >= ?? or DATE <= THEN UNITP END) AS uprice_1,
(CASE WHEN DATE >= ?? or DATE <= THEN UNITP END) AS uprice_2,
(CASE WHEN DATE >= ?? or DATE <= THEN UNITP END) AS uprice_3
FROM SALESDATA

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • hunshabbir7
    Hi,

    I think its not as simple by just selecting and fetching data from table with some condition. You'll first calculate dates for first, middle and day 45 of each quarter and then on the basis of that date fetch the data from table. I guess you need cursors to iterate through original data and temp tables to filter data.
    2,835 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: