none
Every Week Number in a Year

    Question

  • I want to display the week number for every year starting with 2013 and going forward.  I have an Order Detail table that contains the following:

    OrderNbr, RequestedShipDate, Item, Quantity

    I'm trying to create a sql view that will have the following:

    Item, Quantity, WeekNbr, Year1

    My Order Detail table only has records in it for a few weeks but I want to see every week in the year and then in my quantity field I will fill it with 0 when there is not an order for that week/year combination.

    So the result set would look something like this:

    Item, Quantity, WeekNbr, Year1

    ABC, 0, 1, 2014

    ABC, 0, 2, 2014

    ABC, 8, 3, 2014

    and so on.

    Thursday, May 15, 2014 11:39 AM

Answers

All replies

  • You can use DATEPART for this, e.g.:

    SELECT DATEPART(wk, GETDATE()).


    Hope this helps,

    Thursday, May 15, 2014 11:42 AM
  • Right but getdate() gives me today's date, I need a way to calculate or get the week number for every week in the year even when I don't have a date field that contains that week.

    My order table doesn't have an order for week 1 or week 2 but I still want to show a value of 0 for Week 1 and Week 2 in 2014.

    Thursday, May 15, 2014 11:46 AM
  • For all dates of an year, you need to build calendar table, which is the best choice for you:

    http://social.technet.microsoft.com/wiki/contents/articles/22776.t-sql-calendar-table.aspx

    • Marked as answer by doddsw916 Thursday, May 15, 2014 3:16 PM
    Thursday, May 15, 2014 12:11 PM
  • CREATE FUNCTION dbo.WeekOfMonth(@now_day DATETIME)
    RETURNS INT
    AS
    BEGIN
      RETURN DATEPART(week, @now_day)
        - DATEPART(week, CONVERT(CHAR(6), @now_day, 112)+'01')
        + 1
    END
    GO

    SET DATEFIRST 1
    SELECT dbo.WeekOfMonth('20140601') AS [1st]
    SELECT dbo.WeekOfMonth('20140609') AS [2nd]

    drop function WeekOfMonth

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Thursday, May 15, 2014 12:19 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. You have no idea. Temporal data should use ISO-8601 formats. You do not know the week number standards either! Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    I want to display the week number for every year starting with 2013 and going forward. I have an Order Detail table that contains the following:

    CREATE TABLE Order_Details 
    (order_nbr CHAR(10) NOT NULL
     REFERENCES Orderes (order_nbr)
     ON DELETE CASCADE,
     gtin CHAR(15) NOT NULL, 
     PRIMARY KEY (order_nbr, gtin),
     requested_ship_date DATE NOT NULL,
     order_qty INTEGER NOT NULL
      CHECK (order_qty > 0);

    >> I'm trying to create an SQL view that will have the following:
    gtin, order_qty, weeknbr, year1.

    My Order Detail table only has records [sic: rows are not records] in it for a few weeks but I want to see every week in the year and then in my order_qty field [sic: columns  are not fields] I will fill it with 0 when there is not an order for that week/year combination. <<

    You got the basic terms wrong! Rows are not records; columns are not fields. You did no research at all. Your item identifier should be an industry standard, like GTIN, EAN, UPC, etc. 

    Putting the year and week in separate columns is a design flaw called “attribute splitting” that we do not do. You need a calendar table with the ISO week date in it:

    week_date CHAR(10)NOT NULL
      CHECK (week_date LIKE '[12][0-9][0-9][0-9]W[0-5][0-9]-[1-7]'),

    SELECT X.gtin, X.week_name, SUM(X.order_qty) AS order_qty_tot
      FROM (SELECT D.gtin, D.order_qty, 
                   SUBSTRING(C.week_date, 1, 7) AS week_name
              FROM  Calendar AS C LEFT OUTER JOIN Order_Details AS D Calendar AS C
              ON  C.cal_date = D.requested_ship_date)
            AS X
     GROUP BY X.gtin, X.week_name;

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, May 15, 2014 2:31 PM
  • What you need to do is to create a calendar table with all week information. Then do a left join wit your table to fetch details for the week if present in your order table

    So something like

    SELECT DATEPART(wk,f.[Date])AS weekNo,od.*
    FROM dbo.CalendarTable(@StartDate,@EndDate,0,0)f
    LEFT JOIN OrderDetail od
    On DATEPART(wk,od.RequestedShipDate)=DATEPART(wk,f.[Date])
    

    The UDF CalendarTable will generate the calendar details for based on values you pass for Start and EndDates.

    see

    http://visakhm.blogspot.in/2010/02/generating-calendar-table.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, May 15, 2014 3:06 PM