CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008selectstored-procedures
Dinesh Reddy Alla
Dinesh Reddy Alla

Reputation: 1717

How to display records in week order

I am having table containing record count 12000 and now i need to display as week basis as shown below

ID            Date                   count
1       4 week of November 2014       10
2       1 week of December 2014       120
3       2 week of December 2014       60

I tried as 

SELECT CONVERT(VARCHAR(150),datepart(day, datediff(day, 0, date)/7 * 7)/7 + 1) 
+ ' week of ' + CONVERT(VARCHAR(150),datename(month,date)) 
+ ' ' + CONVERT(VARCHAR(150),datepart(YEAR,date))
AS DisplayDate, 
COUNT(date) AS LettersCount
FROM tblV400_Leads_Letters 
WHERE DATEDIFF(DAY,date, GETDATE()) <= 28
GROUP BY date

And it gives out put as 

 ID            Date                   count
 1    4 week of November 2014          10
 2    2 week of December 2014          74
 3    2 week of December 2014          78
 4    2 week of December 2014          59
 5    1 week of December 2014          79
 6    1 week of December 2014          68
 7    1 week of December 2014          60
 8    1 week of December 2014          68
 9    1 week of December 2014          83

Upvotes: 2

Views: 98

Answers (2)

Veera
Veera

Reputation: 3492

Here are 2 different ways, both are assuming the week starts on monday.

If your weeks cut on monthchange so saturday 2012-09-01 and sunday 2012-09-02 is week 1 and monday 2012-09-03 is week 2 use this:

SELECT CAST(datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, date), 0)), 0), date - 1) + 1 AS VARCHAR(1))
+ ' week of ' + CONVERT(VARCHAR(150),datename(month,date)) 
+ ' ' + CONVERT(VARCHAR(150),datepart(YEAR,date))
AS DisplayDate, 
COUNT(date) AS LettersCount
FROM tblV400_Leads_Letters 
WHERE DATEDIFF(DAY,date, GETDATE()) <= 100
GROUP BY CAST(datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, date), 0)), 0), date - 1) + 1 AS VARCHAR(1))
    + ' week of ' + CONVERT(VARCHAR(150),datename(month,date)) 
    + ' ' + CONVERT(VARCHAR(150),datepart(YEAR,date))

If you want weeks to be whole, so they belong to the month in which they start: So saturday 2012-09-01 and sunday 2012-09-02 is week 4 and monday 2012-09-03 is week 1 use this:

SELECT CAST(datepart(day, datediff(day, 0, date)/7 * 7)/7 + 1 AS VARCHAR(1))
+ ' week of ' + CONVERT(VARCHAR(150),datename(month,date)) 
+ ' ' + CONVERT(VARCHAR(150),datepart(YEAR,date))
AS DisplayDate, 
COUNT(date) AS LettersCount
FROM AECCFORECASTLOG 
WHERE DATEDIFF(DAY,date, GETDATE()) <= 100
GROUP BY CAST(datepart(day, datediff(day, 0, date)/7 * 7)/7 + 1 AS VARCHAR(1))
    + ' week of ' + CONVERT(VARCHAR(150),datename(month,date)) 
    + ' ' + CONVERT(VARCHAR(150),datepart(YEAR,date))

Upvotes: 1

Dan
Dan

Reputation: 10680

You are grouping by the date column, which means you will get one record output for each distinct value of date. Make sure that the expression in your GROUP BY clause, is exactly the same as what you are actually outputting:

SELECT
    CONVERT(VARCHAR(150),datepart(day, datediff(day, 0, date)/7 * 7)/7 + 1) 
+ ' week of ' + CONVERT(VARCHAR(150),datename(month,date)) 
+ ' ' + CONVERT(VARCHAR(150),datepart(YEAR,date))
AS DisplayDate, 
COUNT(date) AS LettersCount
FROM tblV400_Leads_Letters 
WHERE DATEDIFF(DAY,date, GETDATE()) <= 28
GROUP BY
    /* This is simply copied from the SELECT part of the query: */
    CONVERT(VARCHAR(150),datepart(day, datediff(day, 0, date)/7 * 7)/7 + 1) 
+ ' week of ' + CONVERT(VARCHAR(150),datename(month,date)) 
+ ' ' + CONVERT(VARCHAR(150),datepart(YEAR,date))

To avoid repeating code, you could wrap everything in a subselect:

SELECT DisplayDate, COUNT(*) FROM (
    SELECT CONVERT(VARCHAR(150),datepart(day, datediff(day, 0, date)/7 * 7)/7 + 1) 
    + ' week of ' + CONVERT(VARCHAR(150),datename(month,date)) 
    + ' ' + CONVERT(VARCHAR(150),datepart(YEAR,date)) AS DisplayDate
    FROM tblV400_Leads_Letters 
    WHERE DATEDIFF(DAY,date, GETDATE()) <= 28
) T
GROUP BY DisplayDate

Also, I'm not completely sure what this code does: CONVERT(VARCHAR(150),datepart(day, datediff(day, 0, date)/7 * 7)/7 + 1). But if you're trying to get the week number, an easier solution is simply to use datepart(week, date) or perhaps datepart(iso_wk, date) if you live in Europe.

Upvotes: 1

Related Questions