Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Need Query Help

  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Need Query Help

    I am trying to write a SQL query that returns the number of products that each customer has and lists the products on one line.

    For example I have a database that contains CustomerID and Product and obviously if the Customer has multiple products there are multiple rows in the database. So if CustomerID '123' has Products 'X', 'Y' and 'Z' I want a query that lists the following

    CustomerID #_of_Products Products
    123 3 X, Y, Z

    How would I go about doing this? Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  3. #3
    Join Date
    Feb 2004
    Posts
    3
    Does this method work for SQL 8.0 which is what I am using?

    I am getting the error message "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

    Thanks

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    --use test

    --drop table TBClientData
    create table TBClientData (customerid int, Products varchar(100))
    insert into TBClientData select 1,'x'
    insert into TBClientData select 1,'y'
    insert into TBClientData select 2,'z'
    insert into TBClientData select 2,'a'
    insert into TBClientData select 2,'t'
    insert into TBClientData select 2,'q'
    insert into TBClientData select 1,'k'
    insert into TBClientData select 3,'l'

    go


    Create FUNCTION fn_Concat (@col int )
    returns varchar(1000)
    as
    begin
    declare @x varchar(1000)
    set @x=''
    select @x=@x+','+products from TBClientData where customerID = @col
    set @x=substring(@x,2,len(@x))
    return (@x)
    end
    go


    --query
    select customerID, dbo.fn_Concat (customerID) as products from
    TBClientData group by customerID

    --results
    customerID,products
    1,x,y,k
    2,z,a,t,q
    3,l

  5. #5
    Join Date
    Feb 2004
    Posts
    3
    That worked perfect! Thanks!

  6. #6
    Join Date
    Feb 2004
    Posts
    64
    I am having problem to post the my question. I tried few times posting it. I am new user to this forum. I don't find my post on this site and didn't get any email so i am not sure anyone replied for my post Please help. I have urgent quetion. I am registerd user. Thanks in advance.

  7. #7
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    I can see only this posting. could you post again? or please post it here in this thread?

  8. #8
    Join Date
    Feb 2004
    Posts
    64
    Thanks. Here is the thing

    Need a help to write a query.
    I have a table with three columns.
    A,B,C

    A = Identity column
    B = Units
    C = Datetime

    The data in table looks like as follows
    A B C
    --------------------------------
    1 2701 1-31-04 09:40am
    2 2701 1-31-04 09:49am
    3 2701 1-31-04 10:40am
    4 2701 1-31-04 10:04am
    5 2701 1-31-04 02:40pm
    6 2701 1-31-04 03:35pm
    7 2701 1-31-04 04:41pm
    8 2701 1-31-04 05:40pm

    Result of query should looks like as follows

    B 9:00-2:00 2:00-5:00 5:00-10:00
    ----------------------------------------
    2701 4 3 1

    I want to group by Unit and by time buckets. Can anyone help me with this? Thanks in advance.

  9. #9
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    --Cross tab query
    use tempdb
    go
    create table mytable (A int, B int, c datetime)
    go
    insert into mytable select 1, 2701,' 1-31-04 09:40am'
    insert into mytable select 2, 2701,' 1-31-04 09:49am'
    insert into mytable select 3, 2701,' 1-31-04 10:40am'
    insert into mytable select 4, 2701,' 1-31-04 10:04am'
    insert into mytable select 5, 2701,' 1-31-04 02:40pm'
    insert into mytable select 6, 2701,' 1-31-04 03:35pm'
    insert into mytable select 7, 2701,' 1-31-04 04:41pm'
    insert into mytable select 8, 2701,'1-31-04 05:40pm'
    go

    SELECT B,
    sum(CASE WHEN (C >convert(datetime,left(convert(varchar(35),C,120), 11) +'09:00:00') and C<=convert(datetime,left(convert(varchar(35),C,120 ),11) +'14:00:00') ) THEN 1 ELSE 0 END) AS [9:00-2:00],
    sum(CASE WHEN (C >convert(datetime,left(convert(varchar(35),C,120), 11) +'14:00:00') and C<=convert(datetime,left(convert(varchar(35),C,120 ),11) +'17:00:00') ) THEN 1 ELSE 0 END) AS [2:00-5:00],
    sum(CASE WHEN (C >convert(datetime,left(convert(varchar(35),C,120), 11) +'17:00:00') and C<=convert(datetime,left(convert(varchar(35),C,120 ),11) +'20:00:00') ) THEN 1 ELSE 0 END) AS [5:00-10:00]
    FROM mytable
    GROUP BY B

  10. #10
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    --Result

    B,9:00-2:00,2:00-5:00,5:00-10:00
    2701,4,3,1

  11. #11
    Join Date
    Feb 2004
    Posts
    64
    Thanks alot. I am going to try it with 100 of diff units and see the result. Thanks again.

  12. #12
    Join Date
    Feb 2004
    Posts
    64
    Whats wrong in following sql statement. Even I have rows between time 10:00pm to 2:00am in the morning it is returning 0 records. If I run it for diff time range like 6:00am to 3:00pm it works fine. Please help me.

    Select z.State,d.Affiliate,count(*)
    From Call_Center_Data c ,Zip_PZA z, DNIS d
    where c.zip_code = z.zip_code
    and c.dnis_code = d.dnis
    and c.Response_Date between '2-23-04' and '2-28-04'
    and CONVERT(char(12), c.Response_Date,8) between '22:00:00' and '02:00:00'
    group by z.state,d.Affiliate

    Thanks in advance.

  13. #13
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    because your hourly time is defined backward.

    You should use

    between '22:00:00' and '23:59:59'

    or

    '00:00:00' and '02:00:00'

  14. #14
    Join Date
    Feb 2004
    Posts
    64
    I want the records between 10:00pm to morning 2:00am so 10:00= 22:00:00 and 2:00am is 2:00:00.
    I am confuse with your answer Claire.

  15. #15
    Join Date
    Sep 2002
    Posts
    5,938
    You can't compare time only in different days, have to specify date and time.

Posting Permissions

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