Post reply

Data question?

  • May 11, 2012 at 5:58 am

    #257868

    Hi all,

    I have data in following format. The requirement is that user should be able to search the data on the criteria of DateOrdered thats string and it could be null. So for instance if @start and @end is like '1/1/2012' and '4/1/2012' , user should get the below #orders information based on that. any pointers please?

    Thank You!!

    create table #ITEM

    (

    Item_id int ,item_desc varchar(50) )

    INSERT INTO #ITEM (Item_id, Item_desc)

    SELECT 1,'DateOrdered'

    UNION ALL

    SELECT 2,'Color'

    UNION ALL

    SELECT 3,'Size'

    UNION ALL

    SELECT 4,'Shape'

    UNION ALL

    SELECT 5,'Material'

    select * from #ITEM

    drop table #ITEM

    create table #Orders

    (

    Order_id int ,item_id int

    ,order_detail varchar(40)

    ,shipped_on datetime)

    INSERT INTO #Orders (Order_id,item_id,order_detail, shipped_on)

    SELECT 11,1,'1/1/2012','5/1/2012 1:37:00' --dates are in mmddyy

    UNION ALL

    SELECT 22,2,'black','5/1/2012 1:37:00'

    UNION ALL

    SELECT 33,3,'50','5/1/2012 1:37:00'

    UNION ALL

    SELECT 44,4,'square','5/1/2012 1:37:00'

    UNION ALL

    SELECT 55,5,'iron','5/1/2012 1:37:00'

    INSERT INTO #Orders (Order_id,item_id,order_detail, shipped_on)

    SELECT 61,1,'2-3-2012','5/4/2012 3:07:20' --dates are in mmddyy , orderid is unique pk and Item id is unique pk

    UNION ALL

    SELECT 62,2,'green','5/4/2012 3:07:20'

    UNION ALL

    SELECT 63,3,'10','5/4/2012 3:07:20'

    UNION ALL

    SELECT 64,4,'round','5/4/2012 3:07:20'

    UNION ALL

    SELECT 65,5,'plastic','5/4/2012 3:07:20'

    INSERT INTO #Orders (Order_id,item_id,order_detail, shipped_on)

    SELECT 81,1,null,'9/4/2012 3:17:20' --dates are in mmddyy , orderid is unique pk and Item id is unique pk

    UNION ALL

    SELECT 82,2,'yellow','9/4/2012 3:17:20'

    UNION ALL

    SELECT 83,3,'20','9/4/2012 3:17:20'

    UNION ALL

    SELECT 84,4,'square','9/4/2012 3:17:20'

    UNION ALL

    SELECT 85,5,'nylon','9/4/2012 3:17:20'

    select * from #Orders

    drop table #Orders

  • May 11, 2012 at 6:06 am

    #1486236

    First of all you should store your dates as dates. SQL Server 2008 even has a date data type, use it.

  • May 11, 2012 at 6:39 am

    #1486264

    Thanks for the reply

    Yes, I did that , making date ordered a sepearte column as datetime and then inserting values in that, I filtered bad data as well using isdate() as advised here its just that I have trouble filtering data @start and @end and some times dateorderd is null as well.

  • May 11, 2012 at 8:32 am

    #1486332

    You can filter dates and exclude null values.

    ... WHERE dateordered IS NOT NULL

    AND (shipped_on BETWEEN @Start AND @End)

    Converting oxygen into carbon dioxide, since 1955.

  • May 11, 2012 at 8:57 am

    #1486365

    Thanks for the reply,

    Actually the data need to be filtered as

    dateordered between @start and @end

    Shipped_on has no significance here...

  • May 11, 2012 at 9:31 am

    #1486398

    You have no column named "dateordered".

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • May 11, 2012 at 9:35 am

    #1486401

    Oh you have a hybrid with EAV going on here. UGH!!! You really should just make the extra stuff columns in your order table and not as lookup values from another table. Otherwise you are fighting an uphill battle.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • May 11, 2012 at 12:09 pm

    #1486517

    I figured it out..Thanks everyone !!

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply