Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2006
    Posts
    42

    Unanswered: plz solve for this tasks

    could some one help in giveing solution for this tasks..
    [CODE]
    Task 1 : Get the first day and last day of the week


    Ex: input : '04/12/2006'

    Required ouput :

    start_date End_date
    04/09/2006 04/15/2006

    Note : 1. start_date should be sunday
    2. end_date should be saturday




    Task 2 -- Find the product sales week wise


    Find the product sales week wise

    Output Required:

    gasstationID productClass week1 week2 week3 week4
    -------------------- ------------ ----------- ----------- ----------- -----------
    111 DSL 1 0 0 0
    111 GAS 2 0 1 1
    222 DSL 1 0 1 1
    222 GAS 1 0 0 0


    create table trial_sales(
    gasstationID bigint,
    salesDate smalldatetime,
    gallons bigint,
    productClass varchar(4))


    insert into trial_sales values(111,'11/01/2005',10000,'GAS')
    insert into trial_sales values(111,'11/02/2005',1000,'GAS')
    insert into trial_sales values(111,'11/02/2005',20000,'DSL')
    insert into trial_sales values(222,'11/01/2005',10000,'GAS')
    insert into trial_sales values(222,'11/02/2005',10000,'DSL')
    insert into trial_sales values(111,'11/15/2005',1000,'GAS')
    insert into trial_sales values(222,'11/15/2005',10000,'DSL')
    insert into trial_sales values(111,'11/25/2005',1000,'GAS')
    insert into trial_sales values(222,'11/25/2005',10000,'DSL')





    Task 3 Display unique values


    Display unique values in

    create table t (colA varchar(25))

    insert into t values('apple')
    insert into t values('bat')
    insert into t values('car')

    ouput required:

    a
    b
    c
    e
    l
    p
    r
    t



    Task : 4

    I have a table in the database I am working with with the following columns:
    (I have simplified this down for the sake of clarity)
    int p1
    int p2
    int p3
    int s1
    date d1
    string s1

    There is no primary key and the table more resembles a log than anything.
    e.g.

    p1 p2 p3 s1 d1 s1
    1 12 14 100 23May comment 2
    1 12 14 800 22May comment 1
    1 12 14 300 24May comment 3
    1 12 15 100 22May comment 2
    1 12 15 800 21May comment 1
    2 12 15 100 23May comment 1

    What I want to do is exttract the rows from the table where the combination
    of p1 p2 and p3 is distinct/unique and the date is the most recent one for
    all combinations of p1, p2, p3. So from the table above I would end up with
    the resultset:

    p1 p2 p3 s1 d1 s1
    1 12 14 300 24May comment 3
    1 12 15 100 22May comment 2
    2 12 15 100 23May comment 1





    Task 5


    say i have the following data...
    uId(int PK), productcode(int), quantity(int)
    1,12,5
    2,12,3
    3,12,5
    4,11,9


    ....and i have an order for productcode 12 with quantity of 6 products.
    i need to select only enough rows to reach a quantity of 6.
    in this case, i would want the first two rows which would give me a sum of
    8.
    how can i do this without looping through each record, getting the quantity
    and exiting the loop when i have a sum >= 6?



    Task 6

    I have two tables as below, TABLE1 and TABLE2.


    TABLE 1: Base

    ID PName PPrice
    -------------------------------------
    1 A 30
    2 B 20

    TABLE 2: History

    ID Ldate Amount
    -------------------------------------
    1 2005/8/7 50


    The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign key.
    What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
    it will return the result as below:


    Ldate PName Amount
    ------------------------------------
    2005/8/7 A 50
    2005/8/7 B null


    and when I pass the date of 2005/8/8, it will return the result as below:

    Ldate PName Amount
    ------------------------------------
    2005/8/8 A null
    2005/8/8 B null



    Task : 7

    Reverse the Name

    create table sample_judge(
    judge_name varchar(50))
    go

    insert into sample_judge values ('J Smith')
    insert into sample_judge values ('M Samuel')
    insert into sample_judge values ('S Shan')
    insert into sample_judge values ('K Anand')
    insert into sample_judge values ('K K K K Anand')
    insert into sample_judge values ('K R Shanth Anand')
    insert into sample_judge values ('K R Shanth Anand Kumara Velu')

    go

    Expected Output :

    Smith J
    Samuel M
    Shan S
    Anand K
    Anand K K K K
    Shanth Anand K R
    Shanth Anand Kumara Velu K R


    [\CODE]

    thanks in adv
    chakri

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by chakri
    could some one help in giveing solution for this tasks..
    no, because we don't do homework assignments

    but we would be happy to help you do your assignment, one question at a time



    Task 1 : Get the first day and last day of the week

    feed the given date into the DATEPART function using weekday (dw) datepart
    The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week.
    then subtract this weekday number minus 1 from the given date using DATEADD function with day parameter

    this gives you the first day of the week for the given date

    for the last day of the week, add 6 days to the first day of the week

    when you have understood this, and have written your solultion to task 1, you will be ready to tackle task 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2006
    Posts
    42
    thanks for your help, well can i know about this

    create table t (colA varchar(25))

    insert into t values('apple')
    insert into t values('bat')
    insert into t values('car')

    ouput required:

    a
    b
    c
    e
    l
    p
    r
    t

    thanks in adv.,
    chakri

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i will give you a hint: you need to extract 1-character substrings of a string value

    do you know of any function that can extract substrings?

    hint: SUBSTRING

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2006
    Posts
    42
    thank you..for your help..

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What was your solution? I have one floating about in my noodle but I am curious how you solved it
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •