Professional Web Applications Themes

SQL Query Help needed..... - Microsoft SQL / MS SQL Server

instead of sum(datasent) try count(datasent) "Post Mortem" <postmrtmnetvision.net.il> wrote in message news:eGn1DHvPDHA.2852tk2msftngp13.phx.gbl... > Hi all, > your help is needed..... > i need to build a query that gives me one column as a sum and the other as > the date > for instance i want to know how many users i had during the last > month.....and i want it by weeks > then i need the sum of all the users per week > > something like this..... > > Date Sum of Users > > > 10/5/2003 25 > > 17/5/2003 20 > > 24/5/2003 10 ...

  1. #1

    Default Re: SQL Query Help needed.....

    instead of sum(datasent) try count(datasent)


    "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    news:eGn1DHvPDHA.2852tk2msftngp13.phx.gbl...
    > Hi all,
    > your help is needed.....
    > i need to build a query that gives me one column as a sum and the other as
    > the date
    > for instance i want to know how many users i had during the last
    > month.....and i want it by weeks
    > then i need the sum of all the users per week
    >
    > something like this.....
    >
    > Date Sum of Users
    >
    >
    > 10/5/2003 25
    >
    > 17/5/2003 20
    >
    > 24/5/2003 10
    >
    > 31/5/2003 35
    >
    >
    > this is the sql i wrote.....
    > i tried all possible combinations
    > SELECT DISTINCT Sum(DataSent) as KbGenerated,LogDate FROM ExpiredLogs
    WHERE
    > DATEPART(wk,LogDate)= DATEPART(wk,(select LogDate where logdate between
    > '06/23/2001' AND '06/30/2003')) AND CompanyId = 1 GROUP BY
    > DATEPART(wk,LogDate),logdate
    >
    >
    > this is what i got
    >
    > KbGenerated LogDate
    > ----------- --------------------------------------------------
    --
    > --
    > 0 2003-05-12 20:22:19.000
    > 0 2003-05-25 10:45:12.000
    > 7709 2003-05-23 10:44:15.000
    > 17009 2003-05-01 10:44:26.000
    > 17109 2003-05-25 10:44:23.000
    > 17209 2003-05-24 10:44:20.000
    > 17309 2003-05-26 10:44:18.000
    > 17409 2003-05-02 10:44:30.000
    > 17609 2003-05-03 10:45:14.000
    > 17709 2003-05-26 10:40:30.000
    > 27709 2003-05-06 10:45:17.000
    > 37709 2003-05-05 10:45:19.000
    > 37709 2003-05-21 10:44:03.000
    > 47709 2003-05-07 10:45:22.000
    > 47709 2003-05-22 10:44:12.000
    > 57709 2003-05-08 10:45:24.000
    > 67709 2003-05-09 10:45:26.000
    > 77709 2003-01-26 10:45:30.000
    >
    >
    > i also tried this
    >
    > SELECT DISTINCT Sum(DataSent) as KbGenerated,LogDate FROM ExpiredLogs
    WHERE
    > LogDate Between '06/23/2001' AND '06/30/2003' AND CompanyId = 1 GROUP BY
    > DATEPART(dd,LogDate) ,logdate
    >
    > i got this
    >
    > KbGenerated LogDate
    > ----------- ------------------------------------------------------
    > 0 2003-05-12 20:22:19.000
    > 0 2003-05-25 10:45:12.000
    > 7709 2003-05-23 10:44:15.000
    > 17009 2003-05-01 10:44:26.000
    > 17109 2003-05-25 10:44:23.000
    > 17209 2003-05-24 10:44:20.000
    > 17309 2003-05-26 10:44:18.000
    > 17409 2003-05-02 10:44:30.000
    > 17609 2003-05-03 10:45:14.000
    > 17709 2003-05-26 10:40:30.000
    > 27709 2003-05-06 10:45:17.000
    > 37709 2003-05-05 10:45:19.000
    > 37709 2003-05-21 10:44:03.000
    > 47709 2003-05-07 10:45:22.000
    > 47709 2003-05-22 10:44:12.000
    > 57709 2003-05-08 10:45:24.000
    > 67709 2003-05-09 10:45:26.000
    > 77709 2003-01-26 10:45:30.000
    >
    >
    > cam someone please tell me what im doing wrong?
    > thanx in advance
    > Post Mortem
    >
    >

    A Chandra Sekhar Guest

  2. #2

    Default Re: SQL Query Help needed.....

    Without sample data and DDL I can only guess. Here's my guess:

    CREATE TABLE dbo.ExpiredLogs
    (
    CompanyID int DEFAULT 1 NOT NULL,
    LogDate datetime DEFAULT GETDATE() NOT NULL,
    DataSent int NOT NULL,
    CONSTRAINT PK_ExpiredLogs
    PRIMARY KEY CLUSTERED (CompanyID,LogDate)
    ON [PRIMARY]
    )
    go
    INSERT dbo.ExpiredLogs
    SELECT 1,'20030508',15
    UNION ALL
    SELECT 1,'20030509',10
    UNION ALL
    SELECT 1,'20030511',10
    UNION ALL
    SELECT 1,'20030512',10
    UNION ALL
    SELECT 1,'20030518',5
    UNION ALL
    SELECT 1,'20030519',5
    UNION ALL
    SELECT 1,'20030526',10
    UNION ALL
    SELECT 1,'20030527',25
    go


    First of all, you can't include LogDate in the Group By clause if you want
    to group by week. Putting LogDate in the GROUP BY clause forces the query to
    create a single record for each LogDate, which is not what you want, judging
    by your desired results. So, the first step is to get the sum per week.
    This:

    SELECT DATEPART(wk,LogDate) WeekNumber, Sum(DataSent) as KbGenerated
    FROM ExpiredLogs
    WHERE LogDate BETWEEN '20030501' and '20030531'
    GROUP BY DATEPART(wk,LogDate)

    will give you this:
    WeekNumber KbGenerated
    19 25
    20 20
    21 10
    22 35

    Now you have some choices. My choice would be to create a table
    pre-populated with rows containing the WeekNumber and WeekEndingDate for all
    the weeks in the year, allowing you to join the above query to the
    WeekMaster table to get the dates. Like this:

    CREATE TABLE WeekMaster (
    CalYear int NOT NULL,
    WeekNumber int NOT NULL,
    WeekEndingDate datetime NOT NULL,
    CONSTRAINT PK_WeekMaster
    PRIMARY KEY CLUSTERED (CalYear,WeekNumber)
    ON [PRIMARY]
    )
    go
    INSERT WeekMaster
    SELECT 2003,19,'20030510'
    UNION ALL
    SELECT 2003,20,'20030517'
    UNION ALL
    SELECT 2003,21,'20030524'
    UNION ALL
    SELECT 2003,22,'20030531'

    This allows you to do this:

    Select WeekEndingDate, KbGenerated
    FROM WeekMaster w INNER JOIN (
    SELECT DATEPART(wk,LogDate) WeekNumber, Sum(DataSent) as KbGenerated
    FROM ExpiredLogs
    WHERE LogDate BETWEEN '20030501' and '20030531'
    GROUP BY DATEPART(wk,LogDate)) q
    ON CalYear = 2003 AND w.WeekNumber =q.WeekNumber

    HTH,
    Bob Barrows




    Post Mortem wrote:
    > Hi all,
    > your help is needed.....
    > i need to build a query that gives me one column as a sum and the
    > other as the date
    > for instance i want to know how many users i had during the last
    > month.....and i want it by weeks
    > then i need the sum of all the users per week
    >
    > something like this.....
    >
    > Date Sum of Users
    >
    >
    > 10/5/2003 25
    >
    > 17/5/2003 20
    >
    > 24/5/2003 10
    >
    > 31/5/2003 35
    >
    >
    > this is the sql i wrote.....
    > i tried all possible combinations
    > SELECT DISTINCT Sum(DataSent) as KbGenerated,LogDate FROM ExpiredLogs
    > WHERE DATEPART(wk,LogDate)= DATEPART(wk,(select LogDate where logdate
    > between '06/23/2001' AND '06/30/2003')) AND CompanyId = 1 GROUP BY
    > DATEPART(wk,LogDate),logdate
    >
    >
    > this is what i got
    >
    > KbGenerated LogDate
    > -----------
    > ----------------------------------------------------


    Bob Barrows Guest

  3. #3

    Default SQL Query Help needed.....

    Hi all,
    your help is needed.....
    i need to build a query that gives me one column as a sum and the other as
    the date
    for instance i want to know how many users i had during the last
    month.....and i want it by weeks
    then i need the sum of all the users per week

    something like this.....

    Date Sum of Users


    10/5/2003 25

    17/5/2003 20

    24/5/2003 10

    31/5/2003 35


    this is the sql i wrote.....
    i tried all possible combinations
    SELECT DISTINCT Sum(DataSent) as KbGenerated,LogDate FROM ExpiredLogs WHERE
    DATEPART(wk,LogDate)= DATEPART(wk,(select LogDate where logdate between
    '06/23/2001' AND '06/30/2003')) AND CompanyId = 1 GROUP BY
    DATEPART(wk,LogDate),logdate


    this is what i got

    KbGenerated LogDate
    ----------- ----------------------------------------------------
    --
    0 2003-05-12 20:22:19.000
    0 2003-05-25 10:45:12.000
    7709 2003-05-23 10:44:15.000
    17009 2003-05-01 10:44:26.000
    17109 2003-05-25 10:44:23.000
    17209 2003-05-24 10:44:20.000
    17309 2003-05-26 10:44:18.000
    17409 2003-05-02 10:44:30.000
    17609 2003-05-03 10:45:14.000
    17709 2003-05-26 10:40:30.000
    27709 2003-05-06 10:45:17.000
    37709 2003-05-05 10:45:19.000
    37709 2003-05-21 10:44:03.000
    47709 2003-05-07 10:45:22.000
    47709 2003-05-22 10:44:12.000
    57709 2003-05-08 10:45:24.000
    67709 2003-05-09 10:45:26.000
    77709 2003-01-26 10:45:30.000


    i also tried this

    SELECT DISTINCT Sum(DataSent) as KbGenerated,LogDate FROM ExpiredLogs WHERE
    LogDate Between '06/23/2001' AND '06/30/2003' AND CompanyId = 1 GROUP BY
    DATEPART(dd,LogDate) ,logdate

    i got this

    KbGenerated LogDate
    ----------- ------------------------------------------------------
    0 2003-05-12 20:22:19.000
    0 2003-05-25 10:45:12.000
    7709 2003-05-23 10:44:15.000
    17009 2003-05-01 10:44:26.000
    17109 2003-05-25 10:44:23.000
    17209 2003-05-24 10:44:20.000
    17309 2003-05-26 10:44:18.000
    17409 2003-05-02 10:44:30.000
    17609 2003-05-03 10:45:14.000
    17709 2003-05-26 10:40:30.000
    27709 2003-05-06 10:45:17.000
    37709 2003-05-05 10:45:19.000
    37709 2003-05-21 10:44:03.000
    47709 2003-05-07 10:45:22.000
    47709 2003-05-22 10:44:12.000
    57709 2003-05-08 10:45:24.000
    67709 2003-05-09 10:45:26.000
    77709 2003-01-26 10:45:30.000


    cam someone please tell me what im doing wrong?
    thanx in advance
    Post Mortem


    Post Mortem Guest

  4. #4

    Default Re: SQL Query Help needed.....

    sorry....but thats not what i need.....i need the sum.....and anyways thats
    not my problem.....
    my problem is getting a column with the full date, a column with the
    Datepart and a column with the sum
    like this.....

    Month KbGenerated FullDate

    5 786529 1/5/2003


    "A Chandra Sekhar" <csapatnaikhotmail.com> wrote in message
    news:uj1j$QvPDHA.3880tk2msftngp13.phx.gbl...
    > instead of sum(datasent) try count(datasent)
    >
    >
    > "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    > news:eGn1DHvPDHA.2852tk2msftngp13.phx.gbl...
    > > Hi all,
    > > your help is needed.....
    > > i need to build a query that gives me one column as a sum and the other
    as
    > > the date
    > > for instance i want to know how many users i had during the last
    > > month.....and i want it by weeks
    > > then i need the sum of all the users per week
    > >
    > > something like this.....
    > >
    > > Date Sum of Users
    > >
    > >
    > > 10/5/2003 25
    > >
    > > 17/5/2003 20
    > >
    > > 24/5/2003 10
    > >
    > > 31/5/2003 35
    > >
    > >
    > > this is the sql i wrote.....
    > > i tried all possible combinations
    > > SELECT DISTINCT Sum(DataSent) as KbGenerated,LogDate FROM ExpiredLogs
    > WHERE
    > > DATEPART(wk,LogDate)= DATEPART(wk,(select LogDate where logdate between
    > > '06/23/2001' AND '06/30/2003')) AND CompanyId = 1 GROUP BY
    > > DATEPART(wk,LogDate),logdate
    > >
    > >
    > > this is what i got
    > >
    > > KbGenerated LogDate
    >
    - --------------------------------------------------
    > --
    > > --
    > > 0 2003-05-12 20:22:19.000
    > > 0 2003-05-25 10:45:12.000
    > > 7709 2003-05-23 10:44:15.000
    > > 17009 2003-05-01 10:44:26.000
    > > 17109 2003-05-25 10:44:23.000
    > > 17209 2003-05-24 10:44:20.000
    > > 17309 2003-05-26 10:44:18.000
    > > 17409 2003-05-02 10:44:30.000
    > > 17609 2003-05-03 10:45:14.000
    > > 17709 2003-05-26 10:40:30.000
    > > 27709 2003-05-06 10:45:17.000
    > > 37709 2003-05-05 10:45:19.000
    > > 37709 2003-05-21 10:44:03.000
    > > 47709 2003-05-07 10:45:22.000
    > > 47709 2003-05-22 10:44:12.000
    > > 57709 2003-05-08 10:45:24.000
    > > 67709 2003-05-09 10:45:26.000
    > > 77709 2003-01-26 10:45:30.000
    > >
    > >
    > > i also tried this
    > >
    > > SELECT DISTINCT Sum(DataSent) as KbGenerated,LogDate FROM ExpiredLogs
    > WHERE
    > > LogDate Between '06/23/2001' AND '06/30/2003' AND CompanyId = 1 GROUP BY
    > > DATEPART(dd,LogDate) ,logdate
    > >
    > > i got this
    > >
    > > KbGenerated LogDate
    > > ----------- ------------------------------------------------------
    > > 0 2003-05-12 20:22:19.000
    > > 0 2003-05-25 10:45:12.000
    > > 7709 2003-05-23 10:44:15.000
    > > 17009 2003-05-01 10:44:26.000
    > > 17109 2003-05-25 10:44:23.000
    > > 17209 2003-05-24 10:44:20.000
    > > 17309 2003-05-26 10:44:18.000
    > > 17409 2003-05-02 10:44:30.000
    > > 17609 2003-05-03 10:45:14.000
    > > 17709 2003-05-26 10:40:30.000
    > > 27709 2003-05-06 10:45:17.000
    > > 37709 2003-05-05 10:45:19.000
    > > 37709 2003-05-21 10:44:03.000
    > > 47709 2003-05-07 10:45:22.000
    > > 47709 2003-05-22 10:44:12.000
    > > 57709 2003-05-08 10:45:24.000
    > > 67709 2003-05-09 10:45:26.000
    > > 77709 2003-01-26 10:45:30.000
    > >
    > >
    > > cam someone please tell me what im doing wrong?
    > > thanx in advance
    > > Post Mortem
    > >
    > >
    >
    >

    Post Mortem Guest

  5. #5

    Default Re: SQL Query Help needed.....

    Try this and try to be more precise in your questions not to mention the
    loss of DDL i DML statements:

    CREATE TABLE dbo.ExpiredLogs
    (
    CompanyID int DEFAULT 1 NOT NULL,
    LogDate datetime DEFAULT GETDATE() NOT NULL,
    DataSent int NOT NULL,
    CONSTRAINT PK_ExpiredLogs
    PRIMARY KEY CLUSTERED (CompanyID,LogDate)
    ON [PRIMARY]
    )
    go
    INSERT dbo.ExpiredLogs
    SELECT 1,'20030508',15
    UNION ALL
    SELECT 1,'20030509',10
    UNION ALL
    SELECT 1,'20030511',10
    UNION ALL
    SELECT 1,'20030512',10
    UNION ALL
    SELECT 1,'20030518',5
    UNION ALL
    SELECT 1,'20030519',5
    UNION ALL
    SELECT 1,'20030526',10
    UNION ALL
    SELECT 1,'20030626',10
    UNION ALL
    SELECT 1,'20030826',10
    UNION ALL
    SELECT 1,'20030726',10
    UNION ALL
    SELECT 1,'20030527',25
    UNION ALL
    SELECT 1,'20020527',25
    UNION ALL
    SELECT 1,'20010527',25
    go


    /*SELECT DATEPART(year, LogDate) YearNumber, DATEPART(wk,LogDate)
    WeekNumber, Sum(DataSent) as KbGenerated
    FROM ExpiredLogs
    GROUP BY DATEPART(year, LogDate), DATEPART(month,
    LogDate),DATEPART(wk,LogDate)
    */
    CREATE TABLE WeekMaster (
    CalYear int NOT NULL,
    WeekNumber int NOT NULL,
    WeekEndingDate datetime NOT NULL,
    CONSTRAINT PK_WeekMaster
    PRIMARY KEY CLUSTERED (CalYear,WeekNumber)
    ON [PRIMARY]
    )
    go
    INSERT WeekMaster
    SELECT 2003,19,'20030510'
    UNION ALL
    SELECT 2003,20,'20030517'
    UNION ALL
    SELECT 2003,21,'20030524'
    UNION ALL
    SELECT 2003,22,'20030531'
    UNION ALL
    SELECT 2003,26,'20030629'
    UNION ALL
    SELECT 2003,30,'20030727'
    UNION ALL
    SELECT 2003,35,'20030831'
    UNION ALL
    SELECT 2002,22,'20020527'
    UNION ALL
    SELECT 2001,22,'20010527'


    Select MonthNumber, q.WeekNumber, WeekEndingDate, KbGenerated
    FROM WeekMaster w
    JOIN (
    SELECT DATEPART(year, LogDate) YearNumber, DATEPART(month, LogDate)
    MonthNumber, DATEPART(wk,LogDate) WeekNumber, Sum(DataSent) as KbGenerated
    FROM ExpiredLogs
    GROUP BY DATEPART(year, LogDate), DATEPART(month, LogDate),
    DATEPART(wk,LogDate)) q
    ON w.WeekNumber = q.WeekNumber and w.CalYear = q.YearNumber

    drop table ExpiredLogs
    drop table WeekMaster


    --
    Dean Savovic
    [url]www.teched.hr[/url]


    "Post Mortem" <postmrtmnetvision.net.il> wrote in message
    news:Op9uYuvPDHA.2480tk2msftngp13.phx.gbl...
    >
    > Hi bob,
    > First of all thanx.....:)
    > your guess was very close....
    > i still have the problem.....the first part u wrote regarding the date
    part
    > per week i got......
    > and works fine.....
    > then u said there were a couple of options but mentioned only one.....and
    > sadly ....that is not the one i can take,.....
    > any other thoughts, ideas?!
    > thanx again
    >
    >
    > "Bob Barrows" <reb_01501> wrote in message
    > news:ugvPqgvPDHA.2244TK2MSFTNGP11.phx.gbl...
    > > Without sample data and DDL I can only guess. Here's my guess:
    > >
    > > CREATE TABLE dbo.ExpiredLogs
    > > (
    > > CompanyID int DEFAULT 1 NOT NULL,
    > > LogDate datetime DEFAULT GETDATE() NOT NULL,
    > > DataSent int NOT NULL,
    > > CONSTRAINT PK_ExpiredLogs
    > > PRIMARY KEY CLUSTERED (CompanyID,LogDate)
    > > ON [PRIMARY]
    > > )
    > > go
    > > INSERT dbo.ExpiredLogs
    > > SELECT 1,'20030508',15
    > > UNION ALL
    > > SELECT 1,'20030509',10
    > > UNION ALL
    > > SELECT 1,'20030511',10
    > > UNION ALL
    > > SELECT 1,'20030512',10
    > > UNION ALL
    > > SELECT 1,'20030518',5
    > > UNION ALL
    > > SELECT 1,'20030519',5
    > > UNION ALL
    > > SELECT 1,'20030526',10
    > > UNION ALL
    > > SELECT 1,'20030527',25
    > > go
    > >
    > >
    > > First of all, you can't include LogDate in the Group By clause if you
    want
    > > to group by week. Putting LogDate in the GROUP BY clause forces the
    query
    > to
    > > create a single record for each LogDate, which is not what you want,
    > judging
    > > by your desired results. So, the first step is to get the sum per week.
    > > This:
    > >
    > > SELECT DATEPART(wk,LogDate) WeekNumber, Sum(DataSent) as KbGenerated
    > > FROM ExpiredLogs
    > > WHERE LogDate BETWEEN '20030501' and '20030531'
    > > GROUP BY DATEPART(wk,LogDate)
    > >
    > > will give you this:
    > > WeekNumber KbGenerated
    > > 19 25
    > > 20 20
    > > 21 10
    > > 22 35
    > >
    > > Now you have some choices. My choice would be to create a table
    > > pre-populated with rows containing the WeekNumber and WeekEndingDate for
    > all
    > > the weeks in the year, allowing you to join the above query to the
    > > WeekMaster table to get the dates. Like this:
    > >
    > > CREATE TABLE WeekMaster (
    > > CalYear int NOT NULL,
    > > WeekNumber int NOT NULL,
    > > WeekEndingDate datetime NOT NULL,
    > > CONSTRAINT PK_WeekMaster
    > > PRIMARY KEY CLUSTERED (CalYear,WeekNumber)
    > > ON [PRIMARY]
    > > )
    > > go
    > > INSERT WeekMaster
    > > SELECT 2003,19,'20030510'
    > > UNION ALL
    > > SELECT 2003,20,'20030517'
    > > UNION ALL
    > > SELECT 2003,21,'20030524'
    > > UNION ALL
    > > SELECT 2003,22,'20030531'
    > >
    > > This allows you to do this:
    > >
    > > Select WeekEndingDate, KbGenerated
    > > FROM WeekMaster w INNER JOIN (
    > > SELECT DATEPART(wk,LogDate) WeekNumber, Sum(DataSent) as KbGenerated
    > > FROM ExpiredLogs
    > > WHERE LogDate BETWEEN '20030501' and '20030531'
    > > GROUP BY DATEPART(wk,LogDate)) q
    > > ON CalYear = 2003 AND w.WeekNumber =q.WeekNumber
    > >
    > > HTH,
    > > Bob Barrows
    > >
    > >
    > >
    > >
    > > Post Mortem wrote:
    > > > Hi all,
    > > > your help is needed.....
    > > > i need to build a query that gives me one column as a sum and the
    > > > other as the date
    > > > for instance i want to know how many users i had during the last
    > > > month.....and i want it by weeks
    > > > then i need the sum of all the users per week
    > > >
    > > > something like this.....
    > > >
    > > > Date Sum of Users
    > > >
    > > >
    > > > 10/5/2003 25
    > > >
    > > > 17/5/2003 20
    > > >
    > > > 24/5/2003 10
    > > >
    > > > 31/5/2003 35
    > > >
    > > >
    > > > this is the sql i wrote.....
    > > > i tried all possible combinations
    > > > SELECT DISTINCT Sum(DataSent) as KbGenerated,LogDate FROM ExpiredLogs
    > > > WHERE DATEPART(wk,LogDate)= DATEPART(wk,(select LogDate where logdate
    > > > between '06/23/2001' AND '06/30/2003')) AND CompanyId = 1 GROUP BY
    > > > DATEPART(wk,LogDate),logdate
    > > >
    > > >
    > > > this is what i got
    > > >
    > > > KbGenerated LogDate
    > > > -----------
    > > > ----------------------------------------------------
    > >
    > >
    > >
    >
    >

    Dean Savovic Guest

  6. #6

    Default Re: SQL Query Help needed.....

    Could you be more explicit about why you cannot take that option? You don't
    have the ability to create a WeekMaster table? I can assure you that this is
    not the only situation where you will find such a table useful. You can even
    create an automatic process to generate the rows in it annually. What's the
    problem?

    If you really can't create a WeekMaster table, then we need to come up with
    an expression or a function to calculate the WeekEndingDate on the fly,
    using the WeekNumber from DatePart. I think I will wait for your reply
    before proceeding, as your reply may indicate the method that should be
    used.

    Bob Barrows


    Post Mortem wrote:
    > Hi bob,
    > First of all thanx.....:)
    > your guess was very close....
    > i still have the problem.....the first part u wrote regarding the
    > date part per week i got......
    > and works fine.....
    > then u said there were a couple of options but mentioned only
    > one.....and sadly ....that is not the one i can take,.....
    > any other thoughts, ideas?!
    > thanx again
    >
    >
    > "Bob Barrows" <reb_01501> wrote in message
    > news:ugvPqgvPDHA.2244TK2MSFTNGP11.phx.gbl...
    >> Without sample data and DDL I can only guess. Here's my guess:
    >>
    >> CREATE TABLE dbo.ExpiredLogs
    >> (
    >> CompanyID int DEFAULT 1 NOT NULL,
    >> LogDate datetime DEFAULT GETDATE() NOT NULL,
    >> DataSent int NOT NULL,
    >> CONSTRAINT PK_ExpiredLogs
    >> PRIMARY KEY CLUSTERED (CompanyID,LogDate)
    >> ON [PRIMARY]
    >> )
    >> go
    >> INSERT dbo.ExpiredLogs
    >> SELECT 1,'20030508',15
    >> UNION ALL
    >> SELECT 1,'20030509',10
    >> UNION ALL
    >> SELECT 1,'20030511',10
    >> UNION ALL
    >> SELECT 1,'20030512',10
    >> UNION ALL
    >> SELECT 1,'20030518',5
    >> UNION ALL
    >> SELECT 1,'20030519',5
    >> UNION ALL
    >> SELECT 1,'20030526',10
    >> UNION ALL
    >> SELECT 1,'20030527',25
    >> go
    >>
    >>
    >> First of all, you can't include LogDate in the Group By clause if
    >> you want to group by week. Putting LogDate in the GROUP BY clause
    >> forces the query to create a single record for each LogDate, which
    >> is not what you want, judging by your desired results. So, the first
    >> step is to get the sum per week. This:
    >>
    >> SELECT DATEPART(wk,LogDate) WeekNumber, Sum(DataSent) as KbGenerated
    >> FROM ExpiredLogs
    >> WHERE LogDate BETWEEN '20030501' and '20030531'
    >> GROUP BY DATEPART(wk,LogDate)
    >>
    >> will give you this:
    >> WeekNumber KbGenerated
    >> 19 25
    >> 20 20
    >> 21 10
    >> 22 35
    >>
    >> Now you have some choices. My choice would be to create a table
    >> pre-populated with rows containing the WeekNumber and WeekEndingDate
    >> for all the weeks in the year, allowing you to join the above query
    >> to the WeekMaster table to get the dates. Like this:
    >>
    >> CREATE TABLE WeekMaster (
    >> CalYear int NOT NULL,
    >> WeekNumber int NOT NULL,
    >> WeekEndingDate datetime NOT NULL,
    >> CONSTRAINT PK_WeekMaster
    >> PRIMARY KEY CLUSTERED (CalYear,WeekNumber)
    >> ON [PRIMARY]
    >> )
    >> go
    >> INSERT WeekMaster
    >> SELECT 2003,19,'20030510'
    >> UNION ALL
    >> SELECT 2003,20,'20030517'
    >> UNION ALL
    >> SELECT 2003,21,'20030524'
    >> UNION ALL
    >> SELECT 2003,22,'20030531'
    >>
    >> This allows you to do this:
    >>
    >> Select WeekEndingDate, KbGenerated
    >> FROM WeekMaster w INNER JOIN (
    >> SELECT DATEPART(wk,LogDate) WeekNumber, Sum(DataSent) as KbGenerated
    >> FROM ExpiredLogs
    >> WHERE LogDate BETWEEN '20030501' and '20030531'
    >> GROUP BY DATEPART(wk,LogDate)) q
    >> ON CalYear = 2003 AND w.WeekNumber =q.WeekNumber
    >>
    >> HTH,
    >> Bob Barrows
    >>
    >>
    >>
    >>
    >> Post Mortem wrote:
    >>> Hi all,
    >>> your help is needed.....
    >>> i need to build a query that gives me one column as a sum and the
    >>> other as the date
    >>> for instance i want to know how many users i had during the last
    >>> month.....and i want it by weeks
    >>> then i need the sum of all the users per week
    >>>
    >>> something like this.....
    >>>
    >>> Date Sum of Users
    >>>
    >>>
    >>> 10/5/2003 25
    >>>
    >>> 17/5/2003 20
    >>>
    >>> 24/5/2003 10
    >>>
    >>> 31/5/2003 35
    >>>
    >>>
    >>> this is the sql i wrote.....
    >>> i tried all possible combinations
    >>> SELECT DISTINCT Sum(DataSent) as KbGenerated,LogDate FROM
    >>> ExpiredLogs WHERE DATEPART(wk,LogDate)= DATEPART(wk,(select LogDate
    >>> where logdate between '06/23/2001' AND '06/30/2003')) AND CompanyId
    >>> = 1 GROUP BY DATEPART(wk,LogDate),logdate
    >>>
    >>>
    >>> this is what i got
    >>>
    >>> KbGenerated LogDate
    >>> -----------
    >>> ----------------------------------------------------

    Bob Barrows Guest

  7. #7

    Default Re: SQL Query Help needed.....

    Hi once again bob.....again thanx alot for your time.....
    i really really didnt think this was so complicated....all i wanted to do is
    retrieve a datepart and next to it the full date......
    is creating the new table a necessary step?? isnt there an easier
    solution.....
    logisticaly speaking?
    i thought i would be able to solve this in a query....and that would be the
    ideal way for me
    :(




    "Bob Barrows" <reb_01501> wrote in message
    news:uLHpbmwPDHA.1720TK2MSFTNGP11.phx.gbl...
    > Could you be more explicit about why you cannot take that option? You
    don't
    > have the ability to create a WeekMaster table? I can assure you that this
    is
    > not the only situation where you will find such a table useful. You can
    even
    > create an automatic process to generate the rows in it annually. What's
    the
    > problem?
    >
    > If you really can't create a WeekMaster table, then we need to come up
    with
    > an expression or a function to calculate the WeekEndingDate on the fly,
    > using the WeekNumber from DatePart. I think I will wait for your reply
    > before proceeding, as your reply may indicate the method that should be
    > used.
    >
    > Bob Barrows
    >
    >
    > Post Mortem wrote:
    > > Hi bob,
    > > First of all thanx.....:)
    > > your guess was very close....
    > > i still have the problem.....the first part u wrote regarding the
    > > date part per week i got......
    > > and works fine.....
    > > then u said there were a couple of options but mentioned only
    > > one.....and sadly ....that is not the one i can take,.....
    > > any other thoughts, ideas?!
    > > thanx again
    > >
    > >
    > > "Bob Barrows" <reb_01501> wrote in message
    > > news:ugvPqgvPDHA.2244TK2MSFTNGP11.phx.gbl...
    > >> Without sample data and DDL I can only guess. Here's my guess:
    > >>
    > >> CREATE TABLE dbo.ExpiredLogs
    > >> (
    > >> CompanyID int DEFAULT 1 NOT NULL,
    > >> LogDate datetime DEFAULT GETDATE() NOT NULL,
    > >> DataSent int NOT NULL,
    > >> CONSTRAINT PK_ExpiredLogs
    > >> PRIMARY KEY CLUSTERED (CompanyID,LogDate)
    > >> ON [PRIMARY]
    > >> )
    > >> go
    > >> INSERT dbo.ExpiredLogs
    > >> SELECT 1,'20030508',15
    > >> UNION ALL
    > >> SELECT 1,'20030509',10
    > >> UNION ALL
    > >> SELECT 1,'20030511',10
    > >> UNION ALL
    > >> SELECT 1,'20030512',10
    > >> UNION ALL
    > >> SELECT 1,'20030518',5
    > >> UNION ALL
    > >> SELECT 1,'20030519',5
    > >> UNION ALL
    > >> SELECT 1,'20030526',10
    > >> UNION ALL
    > >> SELECT 1,'20030527',25
    > >> go
    > >>
    > >>
    > >> First of all, you can't include LogDate in the Group By clause if
    > >> you want to group by week. Putting LogDate in the GROUP BY clause
    > >> forces the query to create a single record for each LogDate, which
    > >> is not what you want, judging by your desired results. So, the first
    > >> step is to get the sum per week. This:
    > >>
    > >> SELECT DATEPART(wk,LogDate) WeekNumber, Sum(DataSent) as KbGenerated
    > >> FROM ExpiredLogs
    > >> WHERE LogDate BETWEEN '20030501' and '20030531'
    > >> GROUP BY DATEPART(wk,LogDate)
    > >>
    > >> will give you this:
    > >> WeekNumber KbGenerated
    > >> 19 25
    > >> 20 20
    > >> 21 10
    > >> 22 35
    > >>
    > >> Now you have some choices. My choice would be to create a table
    > >> pre-populated with rows containing the WeekNumber and WeekEndingDate
    > >> for all the weeks in the year, allowing you to join the above query
    > >> to the WeekMaster table to get the dates. Like this:
    > >>
    > >> CREATE TABLE WeekMaster (
    > >> CalYear int NOT NULL,
    > >> WeekNumber int NOT NULL,
    > >> WeekEndingDate datetime NOT NULL,
    > >> CONSTRAINT PK_WeekMaster
    > >> PRIMARY KEY CLUSTERED (CalYear,WeekNumber)
    > >> ON [PRIMARY]
    > >> )
    > >> go
    > >> INSERT WeekMaster
    > >> SELECT 2003,19,'20030510'
    > >> UNION ALL
    > >> SELECT 2003,20,'20030517'
    > >> UNION ALL
    > >> SELECT 2003,21,'20030524'
    > >> UNION ALL
    > >> SELECT 2003,22,'20030531'
    > >>
    > >> This allows you to do this:
    > >>
    > >> Select WeekEndingDate, KbGenerated
    > >> FROM WeekMaster w INNER JOIN (
    > >> SELECT DATEPART(wk,LogDate) WeekNumber, Sum(DataSent) as KbGenerated
    > >> FROM ExpiredLogs
    > >> WHERE LogDate BETWEEN '20030501' and '20030531'
    > >> GROUP BY DATEPART(wk,LogDate)) q
    > >> ON CalYear = 2003 AND w.WeekNumber =q.WeekNumber
    > >>
    > >> HTH,
    > >> Bob Barrows
    > >>
    > >>
    > >>
    > >>
    > >> Post Mortem wrote:
    > >>> Hi all,
    > >>> your help is needed.....
    > >>> i need to build a query that gives me one column as a sum and the
    > >>> other as the date
    > >>> for instance i want to know how many users i had during the last
    > >>> month.....and i want it by weeks
    > >>> then i need the sum of all the users per week
    > >>>
    > >>> something like this.....
    > >>>
    > >>> Date Sum of Users
    > >>>
    > >>>
    > >>> 10/5/2003 25
    > >>>
    > >>> 17/5/2003 20
    > >>>
    > >>> 24/5/2003 10
    > >>>
    > >>> 31/5/2003 35
    > >>>
    > >>>
    > >>> this is the sql i wrote.....
    > >>> i tried all possible combinations
    > >>> SELECT DISTINCT Sum(DataSent) as KbGenerated,LogDate FROM
    > >>> ExpiredLogs WHERE DATEPART(wk,LogDate)= DATEPART(wk,(select LogDate
    > >>> where logdate between '06/23/2001' AND '06/30/2003')) AND CompanyId
    > >>> = 1 GROUP BY DATEPART(wk,LogDate),logdate
    > >>>
    > >>>
    > >>> this is what i got
    > >>>
    > >>> KbGenerated LogDate
    > >>> -----------
    > >>> ----------------------------------------------------
    >
    >

    Post Mortem Guest

  8. #8

    Default Re: SQL Query Help needed.....

    I don't understand your point. A new table is a GOOD thing! It will DECREASE
    the complexity of this query, as well as improve its perfomance. Also, it
    will be useful in other situations as well.

    The complicating factor here is that there is no simple built-in way to get
    the week-ending date of a specific week, given the week number. Yes, it can
    be done, but doing it will degrade the performance of the query in which you
    are doing it. SQL is just not designed to do something like this
    efficiently. However, here is one way that should perform passably:

    Create Procedure GetTotalPerWeek (
    year int,
    month tinyint,
    company int)
    AS

    DECLARE firstWeekEndDate datetime,
    startdate datetime,
    enddate datetime
    SET firstWeekEndDate = cast(year as char(4)) + '0101'
    WHILE datepart(wk,firstWeekEndDate) = 1
    BEGIN
    SET firstWeekEndDate = DATEADD(d,1,firstWeekEndDate)
    END
    SET firstWeekEndDate = DATEADD(d,-1,firstWeekEndDate)
    SET startdate = cast(year as char(4)) + RIGHT('0' + cast(month as
    varchar(2)),2)
    + '01'
    SET enddate = DATEADD(d,-1,(DATEADD(month,1,startdate)))

    SELECT
    DATEADD(wk,WeekNumber-1, firstWeekEndDate) WeekEndingDate,
    KbGenerated FROM (
    SELECT DATEPART(wk,LogDate) WeekNumber,
    Sum(DataSent) as KbGenerated
    FROM ExpiredLogs
    WHERE LogDate BETWEEN startdate and enddate
    AND CompanyID = company
    GROUP BY DATEPART(wk,LogDate)) q
    go
    exec GetTotalPerWeek 2003,5,1

    HTH,
    Bob Barrows

    Post Mortem wrote:
    > Hi once again bob.....again thanx alot for your time.....
    > i really really didnt think this was so complicated....all i wanted
    > to do is retrieve a datepart and next to it the full date......
    > is creating the new table a necessary step?? isnt there an easier
    > solution.....
    > logisticaly speaking?
    > i thought i would be able to solve this in a query....and that would
    > be the ideal way for me
    > :(
    >
    >
    >
    >
    > "Bob Barrows" <reb_01501> wrote in message
    > news:uLHpbmwPDHA.1720TK2MSFTNGP11.phx.gbl...
    >> Could you be more explicit about why you cannot take that option?
    >> You don't have the ability to create a WeekMaster table? I can
    >> assure you that this is not the only situation where you will find
    >> such a table useful. You can even create an automatic process to
    >> generate the rows in it annually. What's the problem?
    >>
    >> If you really can't create a WeekMaster table, then we need to come
    >> up with an expression or a function to calculate the WeekEndingDate
    >> on the fly, using the WeekNumber from DatePart. I think I will wait
    >> for your reply before proceeding, as your reply may indicate the
    >> method that should be used.
    >>
    >> Bob Barrows
    >>
    >>
    >> Post Mortem wrote:
    >>> Hi bob,
    >>> First of all thanx.....:)
    >>> your guess was very close....
    >>> i still have the problem.....the first part u wrote regarding the
    >>> date part per week i got......
    >>> and works fine.....
    >>> then u said there were a couple of options but mentioned only
    >>> one.....and sadly ....that is not the one i can take,.....
    >>> any other thoughts, ideas?!
    >>> thanx again
    >>>
    >>>
    >>> "Bob Barrows" <reb_01501> wrote in message
    >>> news:ugvPqgvPDHA.2244TK2MSFTNGP11.phx.gbl...
    >>>> Without sample data and DDL I can only guess. Here's my guess:
    >>>>
    >>>> CREATE TABLE dbo.ExpiredLogs
    >>>> (
    >>>> CompanyID int DEFAULT 1 NOT NULL,
    >>>> LogDate datetime DEFAULT GETDATE() NOT NULL,
    >>>> DataSent int NOT NULL,
    >>>> CONSTRAINT PK_ExpiredLogs
    >>>> PRIMARY KEY CLUSTERED (CompanyID,LogDate)
    >>>> ON
    >>>> [PRIMARY] )
    >>>> go
    >>>> INSERT dbo.ExpiredLogs
    >>>> SELECT 1,'20030508',15
    >>>> UNION ALL
    >>>> SELECT 1,'20030509',10
    >>>> UNION ALL
    >>>> SELECT 1,'20030511',10
    >>>> UNION ALL
    >>>> SELECT 1,'20030512',10
    >>>> UNION ALL
    >>>> SELECT 1,'20030518',5
    >>>> UNION ALL
    >>>> SELECT 1,'20030519',5
    >>>> UNION ALL
    >>>> SELECT 1,'20030526',10
    >>>> UNION ALL
    >>>> SELECT 1,'20030527',25
    >>>> go
    >>>>
    >>>>
    >>>> First of all, you can't include LogDate in the Group By clause if
    >>>> you want to group by week. Putting LogDate in the GROUP BY clause
    >>>> forces the query to create a single record for each LogDate, which
    >>>> is not what you want, judging by your desired results. So, the
    >>>> first step is to get the sum per week. This:
    >>>>
    >>>> SELECT DATEPART(wk,LogDate) WeekNumber, Sum(DataSent) as
    >>>> KbGenerated FROM ExpiredLogs
    >>>> WHERE LogDate BETWEEN '20030501' and '20030531'
    >>>> GROUP BY DATEPART(wk,LogDate)
    >>>>
    >>>> will give you this:
    >>>> WeekNumber KbGenerated
    >>>> 19 25
    >>>> 20 20
    >>>> 21 10
    >>>> 22 35
    >>>>
    >>>> Now you have some choices. My choice would be to create a table
    >>>> pre-populated with rows containing the WeekNumber and
    >>>> WeekEndingDate for all the weeks in the year, allowing you to join
    >>>> the above query
    >>>> to the WeekMaster table to get the dates. Like this:
    >>>>
    >>>> CREATE TABLE WeekMaster (
    >>>> CalYear int NOT NULL,
    >>>> WeekNumber int NOT NULL,
    >>>> WeekEndingDate datetime NOT NULL,
    >>>> CONSTRAINT PK_WeekMaster
    >>>> PRIMARY KEY CLUSTERED (CalYear,WeekNumber)
    >>>> ON
    >>>> [PRIMARY] )
    >>>> go
    >>>> INSERT WeekMaster
    >>>> SELECT 2003,19,'20030510'
    >>>> UNION ALL
    >>>> SELECT 2003,20,'20030517'
    >>>> UNION ALL
    >>>> SELECT 2003,21,'20030524'
    >>>> UNION ALL
    >>>> SELECT 2003,22,'20030531'
    >>>>
    >>>> This allows you to do this:
    >>>>
    >>>> Select WeekEndingDate, KbGenerated
    >>>> FROM WeekMaster w INNER JOIN (
    >>>> SELECT DATEPART(wk,LogDate) WeekNumber, Sum(DataSent) as
    >>>> KbGenerated FROM ExpiredLogs
    >>>> WHERE LogDate BETWEEN '20030501' and '20030531'
    >>>> GROUP BY DATEPART(wk,LogDate)) q
    >>>> ON CalYear = 2003 AND w.WeekNumber =q.WeekNumber
    >>>>
    >>>> HTH,
    >>>> Bob Barrows
    >>>>
    >>>>
    >>>>
    >>>>
    >>>> Post Mortem wrote:
    >>>>> Hi all,
    >>>>> your help is needed.....
    >>>>> i need to build a query that gives me one column as a sum and the
    >>>>> other as the date
    >>>>> for instance i want to know how many users i had during the last
    >>>>> month.....and i want it by weeks
    >>>>> then i need the sum of all the users per week
    >>>>>
    >>>>> something like this.....
    >>>>>
    >>>>> Date Sum of Users
    >>>>>
    >>>>>
    >>>>> 10/5/2003 25
    >>>>>
    >>>>> 17/5/2003 20
    >>>>>
    >>>>> 24/5/2003 10
    >>>>>
    >>>>> 31/5/2003 35
    >>>>>
    >>>>>
    >>>>> this is the sql i wrote.....
    >>>>> i tried all possible combinations
    >>>>> SELECT DISTINCT Sum(DataSent) as KbGenerated,LogDate FROM
    >>>>> ExpiredLogs WHERE DATEPART(wk,LogDate)= DATEPART(wk,(select
    >>>>> LogDate where logdate between '06/23/2001' AND '06/30/2003')) AND
    >>>>> CompanyId = 1 GROUP BY DATEPART(wk,LogDate),logdate
    >>>>>
    >>>>>
    >>>>> this is what i got
    >>>>>
    >>>>> KbGenerated LogDate
    >>>>> -----------
    >>>>> ----------------------------------------------------

    Bob Barrows Guest

Similar Threads

  1. Help needed with Spry Grow/Shrink Query!
    By supanaught in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 1
    Last Post: November 4th, 06:27 PM
  2. MS Access Query Help Needed
    By hertelt in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 2nd, 10:57 PM
  3. SQL Query help needed
    By craig_uk in forum Macromedia ColdFusion
    Replies: 0
    Last Post: June 15th, 09:57 AM
  4. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  5. Sql Query for new users in the system.....Help Needed
    By Jacco Schalkwijk in forum Microsoft SQL / MS SQL Server
    Replies: 8
    Last Post: July 2nd, 02:37 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139