Solved

# divide month from 1 to 31 and fill the value in

Posted on 2009-04-23
Medium Priority
237 Views
how can i fill the Qty in by divide the month from 1 to 31 depends on the pack_date
the table will display
inspector     1     2      3     4      5      6      7      8      9      10 ... 31
Adam          44   31           50            32                             66
Allen                   5      6     7      8                     50    21
Bob                            43   55                    70                              89
``````SELECT
pck_hdr.uf_emdainspector
,count(pck_hdr.pack_date)as Qty
,pck_hdr.pack_date
From pck_hdr
INNER JOIN pckitem on pckitem.pack_num = pck_hdr.pack_num
left outer join item on item.item = pckitem.item
where pck_hdr.uf_emdainspector is not null
and pck_hdr.pack_date between @Startdate and @Enddate
And (item.uf_emdapricebookstype = 'E'
or item.uf_emdapricebookstype = 'T')
group by pck_hdr.uf_emdainspector,pck_hdr.pack_date
order by pck_hdr.uf_emdainspector,pck_hdr.pack_date

inspector     1     2      3     4      5      6      7      8      9      10 ... 31
Adam          44   31           50            32                             66
Allen                   5      6     7      8                     50    21
Bob                            43   55                    70                              89
``````
0
Question by:wanlijun
• 3
• 3

LVL 27

Expert Comment

ID: 24221958
This pivot should do it.
``````SELECT inspector, [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]
FROM (
SELECT DATEPART(d,pck_hdr.pack_date) MonthDay,
pck_hdr.uf_emdainspector inspector
,count(pck_hdr.pack_date)as Qty
FROM From pck_hdr
INNER JOIN pckitem on pckitem.pack_num = pck_hdr.pack_num
left outer join item on item.item = pckitem.item
where pck_hdr.uf_emdainspector is not null
and pck_hdr.pack_date between @Startdate and @Enddate
And (item.uf_emdapricebookstype = 'E'
or item.uf_emdapricebookstype = 'T')
group by pck_hdr.uf_emdainspector,pck_hdr.pack_date
) P
PIVOT (SUM( Qty) FOR MonthDay IN ([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])
) pvt
``````
0

Author Comment

ID: 24238373
what about if i have 2 values?

SELECT DATEPART(d,pck_hdr.pack_date) MonthDay,
pck_hdr.uf_emdainspector inspector
,count(pckitem.co_line) as dockets,count(pck_hdr.pack_date)as Qty
FROM pck_hdr
INNER JOIN pckitem on pckitem.pack_num = pck_hdr.pack_num
left outer join item on item.item = pckitem.item
where pck_hdr.uf_emdainspector is not null
and pck_hdr.pack_date between @Startdate and @Enddate
And (item.uf_emdapricebookstype = 'E'
or item.uf_emdapricebookstype = 'T')
group by pck_hdr.uf_emdainspector,pck_hdr.pack_date
0

LVL 27

Expert Comment

ID: 24238418
What is the desired output you want to see?  It is hard to display 2 values in a cell in the grid.  PIVOT will only work on 1 value at a time, so you have to do two PIVOTS and either JOIN or UNION them together depending on what you want the output to look like.
0

Author Comment

ID: 24238441
inspector     1     2      3     4      5      6      7      8      9      10 ... 31
Adam  Qty         44   31           50            32                             66
Dockets  12  0     23     4                               5
Allen    Qty         5      6     7      8                     50    21
Dockets   89   11   20    4     1         2                             3
Bob      Qty                43   55                    70                              89
Dockets   18          12           5              10            8
0

Author Comment

ID: 24238629
can you show me how to do 2 pivots with join or union
0

LVL 27

Accepted Solution

Chris Luttrell earned 2000 total points
ID: 24238693
You will have to do that with a union something like below.  You will have to play with the columns Inspector and Type to get come up with a display you want and ordered right.
``````SELECT inspector, 'Qty' type, [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]
FROM (
SELECT DATEPART(d,pck_hdr.pack_date) MonthDay,
pck_hdr.uf_emdainspector inspector
,count(pck_hdr.pack_date)as Qty
FROM From pck_hdr
INNER JOIN pckitem on pckitem.pack_num = pck_hdr.pack_num
left outer join item on item.item = pckitem.item
where pck_hdr.uf_emdainspector is not null
and pck_hdr.pack_date between @Startdate and @Enddate
And (item.uf_emdapricebookstype = 'E'
or item.uf_emdapricebookstype = 'T')
group by pck_hdr.uf_emdainspector,pck_hdr.pack_date
) P
PIVOT (SUM( Qty) FOR MonthDay IN ([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])
) pvt
UNION ALL
SELECT inspector, 'Dockets' type, [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]
FROM (
SELECT DATEPART(d,pck_hdr.pack_date) MonthDay,
pck_hdr.uf_emdainspector inspector
,count(pckitem.co_line) as dockets
FROM From pck_hdr
INNER JOIN pckitem on pckitem.pack_num = pck_hdr.pack_num
left outer join item on item.item = pckitem.item
where pck_hdr.uf_emdainspector is not null
and pck_hdr.pack_date between @Startdate and @Enddate
And (item.uf_emdapricebookstype = 'E'
or item.uf_emdapricebookstype = 'T')
group by pck_hdr.uf_emdainspector,pck_hdr.pack_date
) P
PIVOT (SUM( dockets) FOR MonthDay IN ([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])
) pvt
Order by Inspector, type desc
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recovâ€¦
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
###### Suggested Courses
Course of the Month5 days, 1 hour left to enroll