Professional Web Applications Themes

SUM() for current week - Microsoft SQL / MS SQL Server

Hi, I have orderTable, where I have orders of products for each country. I would like to create a select statement, which returns the quantity of orders for the current month and current week. Something like that: SELECT SUM(quantity) as monthQuantity, SUM(quantity) as weekquantity FROM orderTable WHERE productId=1 AND countryId=1 and entryDate=(current week or current month) Thank you for your answer, Simon Script: CREATE TABLE [dbo].[OrderTable] ( [productId] [int] NOT NULL , [countryid] [int] NOT NULL , [entryDate] [datetime] NOT NULL , [quantity] [int] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[OrderTable] WITH NOCHECK ADD CONSTRAINT [PK_OrderTable] PRIMARY KEY CLUSTERED ...

  1. #1

    Default SUM() for current week

    Hi,

    I have orderTable, where I have orders of products for each country.

    I would like to create a select statement, which returns the quantity of
    orders for the current month and current week.

    Something like that:

    SELECT SUM(quantity) as monthQuantity, SUM(quantity) as weekquantity FROM
    orderTable
    WHERE productId=1 AND countryId=1
    and entryDate=(current week or current month)

    Thank you for your answer,
    Simon

    Script:

    CREATE TABLE [dbo].[OrderTable] (
    [productId] [int] NOT NULL ,
    [countryid] [int] NOT NULL ,
    [entryDate] [datetime] NOT NULL ,
    [quantity] [int] NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[OrderTable] WITH NOCHECK ADD
    CONSTRAINT [PK_OrderTable] PRIMARY KEY CLUSTERED
    (
    [productId],
    [countryid],
    [entryDate]
    ) ON [PRIMARY]
    GO


    Simon Guest

  2. #2

    Default Re: SUM() for current week

    You can use the following somewhat generalised approach:

    SELECT DISTINCT o.product_id, o.country_id,
    (SELECT SUM(om.quantity) FROM orderTable om
    WHERE o.product_id = om.product_id
    AND o.country_id = om.country_id
    AND YEAR(om.entryDate) = YEAR(GETDATE())
    AND MONTH(om.entryDate) = MONTH(GETDATE()))
    as monthQuantity ,
    (SELECT SUM(ow.quantity) FROM orderTable ow
    WHERE o.product_id = ow.product_id
    AND o.country_id = ow.country_id
    AND YEAR(ow.entryDate) = YEAR(GETDATE())
    AND DATEPART(ww, ow.entryDate) = DATEPART(ww, GETDATE()))
    as weekQuantity
    FROM orderTable
    -- WHERE clause here

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Simon" <simon.zupanstud-moderna.si> wrote in message
    news:3f0bcaf6news.s5.net...
    > Hi,
    >
    > I have orderTable, where I have orders of products for each country.
    >
    > I would like to create a select statement, which returns the quantity of
    > orders for the current month and current week.
    >
    > Something like that:
    >
    > SELECT SUM(quantity) as monthQuantity, SUM(quantity) as weekquantity FROM
    > orderTable
    > WHERE productId=1 AND countryId=1
    > and entryDate=(current week or current month)
    >
    > Thank you for your answer,
    > Simon
    >
    > Script:
    >
    > CREATE TABLE [dbo].[OrderTable] (
    > [productId] [int] NOT NULL ,
    > [countryid] [int] NOT NULL ,
    > [entryDate] [datetime] NOT NULL ,
    > [quantity] [int] NULL
    > ) ON [PRIMARY]
    > GO
    >
    > ALTER TABLE [dbo].[OrderTable] WITH NOCHECK ADD
    > CONSTRAINT [PK_OrderTable] PRIMARY KEY CLUSTERED
    > (
    > [productId],
    > [countryid],
    > [entryDate]
    > ) ON [PRIMARY]
    > GO
    >
    >

    Jacco Schalkwijk Guest

  3. #3

    Default Re: SUM() for current week

    Falik,

    Your solution will sum the orders for the same month and weeks for different
    years, and also when a week starts in one month and finishes in the next, it
    will only sum the orders which were in the part of the week that falls in
    the month for which you run the query, not for the whole week. Unfortunately
    we have to live with a calendar system where months don't make up a whole
    number of weeks.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Falik Sher" <falikshotmail.com> wrote in message
    news:eDgg#UfRDHA.1324TK2MSFTNGP11.phx.gbl...
    > Pelase see below the example and tailor to ur need.
    >
    > You need to supply 2 para i.e. Month and week number
    >
    > use pubs
    >
    > declare wk int, mn int
    > set wk=21
    > set mn=5
    >
    > select sum(qty) qty_mon, sum(case when datepart(wk,ord_date) = wk then
    qty
    > else 0 end) qty_wk
    > from sales
    > where datepart(m,ord_date) = mn
    >
    > HTH
    > Falik
    >
    > "Simon" <simon.zupanstud-moderna.si> wrote in message
    > news:3f0bcaf6news.s5.net...
    > > Hi,
    > >
    > > I have orderTable, where I have orders of products for each country.
    > >
    > > I would like to create a select statement, which returns the quantity of
    > > orders for the current month and current week.
    > >
    > > Something like that:
    > >
    > > SELECT SUM(quantity) as monthQuantity, SUM(quantity) as weekquantity
    FROM
    > > orderTable
    > > WHERE productId=1 AND countryId=1
    > > and entryDate=(current week or current month)
    > >
    > > Thank you for your answer,
    > > Simon
    > >
    > > Script:
    > >
    > > CREATE TABLE [dbo].[OrderTable] (
    > > [productId] [int] NOT NULL ,
    > > [countryid] [int] NOT NULL ,
    > > [entryDate] [datetime] NOT NULL ,
    > > [quantity] [int] NULL
    > > ) ON [PRIMARY]
    > > GO
    > >
    > > ALTER TABLE [dbo].[OrderTable] WITH NOCHECK ADD
    > > CONSTRAINT [PK_OrderTable] PRIMARY KEY CLUSTERED
    > > (
    > > [productId],
    > > [countryid],
    > > [entryDate]
    > > ) ON [PRIMARY]
    > > GO
    > >
    > >
    >
    >

    Jacco Schalkwijk Guest

  4. #4

    Default Re: SUM() for current week

    Hi,

    your code works fine but it takes 9 seconds to execute.
    Is there really no other way?

    Thank you very much,
    Simon

    "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    news:#vRcCqfRDHA.212TK2MSFTNGP10.phx.gbl...
    > The code below won't use indexes on the entrydate column, but I left the
    > code for that for the sake of simplicity.
    > The following code will use indexes:
    >
    > SELECT DISTINCT o.product_id, o.country_id,
    > (SELECT SUM(om.quantity) FROM orderTable om
    > WHERE o.product_id = om.product_id
    > AND o.country_id = om.country_id
    > AND om.entryDate
    > BETWEEN CONVERT(CHAR(8), DATEADD(dd, -1*DATEPART(dd, GETDATE())
    +1,
    > GETDATE()), 112)
    > AND CONVERT(CHAR(8), DATEADD(mm, 1, DATEADD(dd, -1*DATEPART(dd,
    > GETDATE()) +1, GETDATE())), 112)
    > as monthQuantity ,
    > (SELECT SUM(ow.quantity) FROM orderTable ow
    > WHERE o.product_id = ow.product_id
    > AND o.country_id = ow.country_id
    > AND om.entryDate
    > BETWEEN CONVERT(CHAR(8), DATEADD(dd, -1*DATEPART(dw, GETDATE())
    +1,
    > GETDATE()), 112)
    > AND CONVERT(CHAR(8), DATEADD(dd, -1*DATEPART(dw, GETDATE()) +8,
    > GETDATE()), 112)
    > as weekQuantity
    > FROM orderTable
    > -- WHERE clause here
    >
    > You can ofcourse replace GETDATE() with a datetime variable.
    >
    > --
    > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > Database Administrator
    > Eurostop Ltd.
    >
    >
    > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > news:eC$sNafRDHA.2480tk2msftngp13.phx.gbl...
    > > You can use the following somewhat generalised approach:
    > >
    > > SELECT DISTINCT o.product_id, o.country_id,
    > > (SELECT SUM(om.quantity) FROM orderTable om
    > > WHERE o.product_id = om.product_id
    > > AND o.country_id = om.country_id
    > > AND YEAR(om.entryDate) = YEAR(GETDATE())
    > > AND MONTH(om.entryDate) = MONTH(GETDATE()))
    > > as monthQuantity ,
    > > (SELECT SUM(ow.quantity) FROM orderTable ow
    > > WHERE o.product_id = ow.product_id
    > > AND o.country_id = ow.country_id
    > > AND YEAR(ow.entryDate) = YEAR(GETDATE())
    > > AND DATEPART(ww, ow.entryDate) = DATEPART(ww, GETDATE()))
    > > as weekQuantity
    > > FROM orderTable
    > > -- WHERE clause here
    > >
    > > --
    > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > Database Administrator
    > > Eurostop Ltd.
    > >
    > >
    > > "Simon" <simon.zupanstud-moderna.si> wrote in message
    > > news:3f0bcaf6news.s5.net...
    > > > Hi,
    > > >
    > > > I have orderTable, where I have orders of products for each country.
    > > >
    > > > I would like to create a select statement, which returns the quantity
    of
    > > > orders for the current month and current week.
    > > >
    > > > Something like that:
    > > >
    > > > SELECT SUM(quantity) as monthQuantity, SUM(quantity) as weekquantity
    > FROM
    > > > orderTable
    > > > WHERE productId=1 AND countryId=1
    > > > and entryDate=(current week or current month)
    > > >
    > > > Thank you for your answer,
    > > > Simon
    > > >
    > > > Script:
    > > >
    > > > CREATE TABLE [dbo].[OrderTable] (
    > > > [productId] [int] NOT NULL ,
    > > > [countryid] [int] NOT NULL ,
    > > > [entryDate] [datetime] NOT NULL ,
    > > > [quantity] [int] NULL
    > > > ) ON [PRIMARY]
    > > > GO
    > > >
    > > > ALTER TABLE [dbo].[OrderTable] WITH NOCHECK ADD
    > > > CONSTRAINT [PK_OrderTable] PRIMARY KEY CLUSTERED
    > > > (
    > > > [productId],
    > > > [countryid],
    > > > [entryDate]
    > > > ) ON [PRIMARY]
    > > > GO
    > > >
    > > >
    > >
    > >
    >
    >

    Simon Guest

Similar Threads

  1. Replies: 3
    Last Post: March 9th, 05:19 PM
  2. D-day next week!
    By Dee Holmes in forum Adobe Illustrator Macintosh
    Replies: 37
    Last Post: June 10th, 05:10 PM
  3. how to get first day of the week
    By Dario Volaric in forum Macromedia Director Basics
    Replies: 2
    Last Post: February 16th, 01:18 PM
  4. Replies: 3
    Last Post: December 22nd, 10:42 PM
  5. Replies: 1
    Last Post: July 20th, 03:27 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