All geek questions in one place

SQL Server Query by Week

See my query below and the results

(SELECT count(*) as Count, p.regionid, a.territory,
CASE WHEN DATEPART(day,p.prodate) < 8 THEN '1' 
  ELSE CASE WHEN DATEPART(day,p.prodate) < 15 then '2' 
    ELSE CASE WHEN  DATEPART(day,p.prodate) < 22 then '3' 
      ELSE CASE WHEN  DATEPART(day,p.prodate) < 29 then '4'     
       ELSE '5'   
      END
    END
  END
END as WeekNumber 
from pros p 
inner join
GTS_Account a 
on a.locationGTSNo = p.BillToCustNo
  where a.account like '%abc%' and
  p.prodate between '2014-02-01' and '2014-02-28' 
group by p.ProDate,p.regionid,a.territory)

results

╔═══════╦══════════╦═══════════╦════════════╗
β•‘ Count β•‘ regionid β•‘ territory β•‘ WeekNumber β•‘
╠═══════╬══════════╬═══════════╬════════════╣
β•‘     3 β•‘ LTL      β•‘         2 β•‘          1 β•‘
β•‘    23 β•‘ RDFS     β•‘         2 β•‘          1 β•‘
β•‘    10 β•‘ VOLUME   β•‘         2 β•‘          1 β•‘
β•‘    22 β•‘ RDFS     β•‘         2 β•‘          1 β•‘
β•‘    10 β•‘ VOLUME   β•‘         2 β•‘          1 β•‘
β•‘     1 β•‘ EXP      β•‘         2 β•‘          2 β•‘
β•‘     3 β•‘ LTL      β•‘         2 β•‘          2 β•‘
β•‘    25 β•‘ RDFS     β•‘         2 β•‘          2 β•‘
β•‘    11 β•‘ VOLUME   β•‘         2 β•‘          2 β•‘
β•‘     1 β•‘ BRK      β•‘         2 β•‘          2 β•‘
β•‘     6 β•‘ VOLUME   β•‘         2 β•‘          2 β•‘
β•‘     1 β•‘ BRK      β•‘         2 β•‘          3 β•‘
β•‘     4 β•‘ EXP      β•‘         2 β•‘          3 β•‘
β•‘     1 β•‘ LTL      β•‘         2 β•‘          3 β•‘
β•‘    20 β•‘ RDFS     β•‘         2 β•‘          3 β•‘
β•‘     4 β•‘ EXP      β•‘         2 β•‘          4 β•‘
β•‘     4 β•‘ LTL      β•‘         2 β•‘          4 β•‘
β•‘    30 β•‘ RDFS     β•‘         2 β•‘          4 β•‘
β•‘    15 β•‘ VOLUME   β•‘         2 β•‘          4 β•‘
β•šβ•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•β•β•

This shows data for every day of the week, I just want it to show the total for each region / per week, and not per day. Thanks

3-13-14 I would like to show the Month and Year column for this data, please below, it should show only one row for each region / week.

SELECT convert(nvarchar,DATEPART(YYYY,p.prodate))  + ' ' +
CASE WHEN DATEPART(month,p.prodate) = 1 THEN 'JAN' 
    WHEN DATEPART(month,p.prodate) = 2 THEN 'FEB' 
    WHEN DATEPART(month,p.prodate) = 3 THEN 'MAR' 
    WHEN DATEPART(month,p.prodate) = 4 THEN 'APR' 
    WHEN DATEPART(month,p.prodate) = 5 THEN 'MAY' 
    WHEN DATEPART(month,p.prodate) = 6 THEN 'JUN' 
    WHEN DATEPART(month,p.prodate) = 7 THEN 'JUL' 
    WHEN DATEPART(month,p.prodate) = 8 THEN 'AUG' 
    WHEN DATEPART(month,p.prodate) = 9 THEN 'SEP' 
    WHEN DATEPART(month,p.prodate) = 10 THEN 'OCT' 
    WHEN DATEPART(month,p.prodate) = 11 THEN 'NOV' 
    WHEN DATEPART(month,p.prodate) = 12 THEN 'DEC' 
    ELSE '6'
    END AS MONTHnAME,
count(*) as Count, p.regionid, 
CASE WHEN DATEPART(day,p.prodate) < 8 THEN '1' --+ ' ' +convert(nvarchar,DATEPART(mm,p.prodate))
     WHEN DATEPART(day,p.prodate) < 15 then '2' --+ ' ' +convert(nvarchar,DATEPART(mm,p.prodate))
    WHEN  DATEPART(day,p.prodate) < 22 then '3' --+ ' ' +convert(nvarchar,DATEPART(mm,p.prodate))
    WHEN  DATEPART(day,p.prodate) < 29 then '4'  --+ ' ' +convert(nvarchar,DATEPART(mm,p.prodate))   
    ELSE '5' + ' ' +convert(nvarchar,DATEPART(mm,p.prodate)) 
END as WeekNumber,a.territory,'rrts' from pros p inner join GTS_Account a on a.locationGTSNo = p.BillToCustNo
  where a.account like '%rrts%' and
  p.prodate between '2014-02-01' and '2014-02-28' group by 
  CASE 
            WHEN Datepart(day, p.prodate) < 8 THEN '1' 
            WHEN Datepart(day, p.prodate) < 15 THEN '2' 
            WHEN Datepart(day, p.prodate) < 22 THEN '3' 
            WHEN Datepart(day, p.prodate) < 29 THEN '4' 
            ELSE '5' 
          END
  ,p.prodate,a.territory, p.regionid
  order by p.regionid, weeknumber

MONTHnAME Count regionid ( ) 2014 FEB 1 BRK 1 0002 rrts 2014 FEB 1 BRK 2 0002 rrts 2014 FEB 1 BRK 2 0002 rrts 2014 FEB 1 BRK 3 0002 rrts 2014 FEB 2 BRK 4 0002 rrts 2014 FEB 1 BRK 4 0002 rrts 2014 FEB 1 DED 3 0002 rrts 2014 FEB 3 EXP 1 0002 rrts 2014 FEB 1 EXP 1 0002 rrts 2014 FEB 2 EXP 1 0002 rrts 2014 FEB 3 EXP 1 0002 rrts 2014 FEB 2 EXP 1 0002 rrts 2014 FEB 1 EXP 2 0002 rrts 2014 FEB 1 EXP 2 0002 rrts 2014 FEB 2 EXP 2 0002 rrts 2014 FEB 1 EXP 2 0002 rrts 2014 FEB 4 EXP 3 0002 rrts 2014 FEB 1 EXP 3 0002 rrts 2014 FEB 6 EXP 3 0002 rrts 2014 FEB 3 EXP 3 0002 rrts 2014 FEB 7 EXP 3 0002 rrts 2014 FEB 1 EXP 4 0002 rrts 2014 FEB 4 EXP 4 0002 rrts 2014 FEB 2 EXP 4 0002 rrts 2014 FEB 3 EXP 4 0002 rrts

+4
1

, , p.prodate

, case WHENs

SELECT Count(*) AS Count, 
       p.regionid, 
       a.territory, 
       CASE 
         WHEN Datepart(day, p.prodate) < 8 THEN '1' 
         WHEN Datepart(day, p.prodate) < 15 THEN '2' 
         WHEN Datepart(day, p.prodate) < 22 THEN '3' 
         WHEN Datepart(day, p.prodate) < 29 THEN '4' 
         ELSE '5' 
       END      AS WeekNumber 
FROM   pros p 
       INNER JOIN gts_account a 
               ON a.locationgtsno = p.billtocustno 
WHERE  a.account LIKE '%abc%' 
       AND p.prodate BETWEEN '2014-02-01' AND '2014-02-28' 
GROUP  BY CASE 
            WHEN Datepart(day, p.prodate) < 8 THEN '1' 
            WHEN Datepart(day, p.prodate) < 15 THEN '2' 
            WHEN Datepart(day, p.prodate) < 22 THEN '3' 
            WHEN Datepart(day, p.prodate) < 29 THEN '4' 
            ELSE '5' 
          END, 
          p.regionid, 
          a.territory

, with, .

WITH cte 
     AS (SELECT 
                p.regionid, 
                a.territory, 
                CASE 
                  WHEN Datepart(day, p.prodate) < 8 THEN '1' 
                  WHEN Datepart(day, p.prodate) < 15 THEN '2' 
                  WHEN Datepart(day, p.prodate) < 22 THEN '3' 
                  WHEN Datepart(day, p.prodate) < 29 THEN '4' 
                  ELSE '5' 
                END AS WeekNumber 
         FROM   pros p 
                INNER JOIN gts_account a 
                        ON a.locationgtsno = p.billtocustno 
         WHERE  a.account LIKE '%abc%' 
                AND p.prodate BETWEEN '2014-02-01' AND '2014-02-28') 
SELECT Count(*) As Count,  
       regionid, 
       territory, 
       weeknumber 
FROM   cte 
GROUP  BY weeknumber, 
          regionid, 
          territory
+3

Source: https://habr.com/ru/post/1531368/

More articles:


All Articles