Results 1 to 6 of 6

Thread: sp syntax help

  1. #1
    Join Date
    Jun 2006
    Posts
    66

    Unanswered: sp syntax help

    CREATE PROCEDURE [dbo].[getnumberpaidpersite]
    @fromdate datetime,
    @todate datetime,
    @userid int
    AS
    BEGIN
    declare @paid int
    -- SET NOCOUNT ON added to prevent extra result sets from

    SET NOCOUNT ON;


    SELECT count(id) as mycount,usertype from payments where received>=@fromdate and received<=@todate group by usertype

    return
    END


    with the above sp

    this returns now

    usertype mycount

    001 11
    002 6

    based on the dates

    now what i really want is to take bring the count for the week of @fromdate so that it should return

    usertype sunday monday tuesday wed thursday friday (only until the @fromdate) (so if @fromdate is monday it will only return sunday and monday)

    001 1 3 4 1 1 1
    002 1 2 3 0 0 0

    can someone help me with this?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Depends on the version. A combination of case and group by works in either version and,personally,is my preference. You can use the new pivot syntax in 2005.

    Example:
    http://jdixon.dotnetdevelopersjourna...0_and_2005.htm
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2006
    Posts
    66
    so i got a function to get the first day but not sure how to put this all together.....anyway thanks for your help

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Getting the first day is the hard bit. Repeating this for each subsquent day should be trivial. WHat have you got?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    May 2007
    Posts
    49
    Hope following will help -

    Create PROCEDURE [dbo].[usp_Matrix]
    (
    @dtStartFrom datetime
    )
    AS
    Begin
    DECLARE @select varchar(8000)
    DECLARE @sql varchar(8000)
    DECLARE @startDate datetime
    DECLARE @endDate datetime
    DECLARE @sumfunc varchar(100)
    DECLARE @pivot varchar(100)
    DECLARE @table varchar(100)

    SET NOCOUNT ON
    SET ANSI_WARNINGS OFF

    -- Set required variables
    set @table = 'Payments'
    set @sumfunc = 'count(id)'
    set @pivot = '(DATENAME (weekday, received))'

    Create table #tempdateDiff
    (DayName varchar(100), diff int)
    insert into #tempdateDiff
    values ('Sumday', 0)
    insert into #tempdateDiff
    values ('Monday', 1)
    insert into #tempdateDiff
    values ('TuesDay', 2)
    insert into #tempdateDiff
    values ('Wednesday', 3)
    insert into #tempdateDiff
    values ('Thursday', 4)
    insert into #tempdateDiff
    values ('Friday', 5)
    insert into #tempdateDiff
    values ('Saturday', 6)

    Create table ##pivot (pivot varchar(200))
    Select @startDate = DateAdd(d,(diff* -1), @dtStartFrom) from #tempdateDiff
    where DayName = (Select DATENAME (weekday, @dtStartFrom))

    set @endDate = @dtStartFrom
    Set @select = 'Select usertype from Payments where received between '''+ Convert(varchar, @startDate, 101) +''' and ''' + convert(varchar, @endDate, 101) + ''' group by userType'


    drop table #tempdateDiff

    while (@startDate <= @endDate)
    Begin
    INSERT INTO ##pivot VALUES (DATENAME (weekday, @startDate))
    set @startDate = dateadd(d, 1, @startDate)
    End

    SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
    SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
    stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
    + '''' + convert(varchar(100), pivot) + '''' + ' THEN ' ) + ', ' FROM ##pivot
    DROP TABLE ##pivot
    SELECT @sql=left(@sql, len(@sql)-1)

    SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
    set @select = @select
    exec (@select)
    SET ANSI_WARNINGS ON
    End
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

Posting Permissions

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