?
Solved

divide month from 1 to 31 and fill the value in

Posted on 2009-04-23
6
Medium Priority
?
237 Views
Last Modified: 2012-05-06
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

Open in new window

0
Comment
Question by:wanlijun
  • 3
  • 3
6 Comments
 
LVL 27

Expert Comment

by:Chris Luttrell
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

Open in new window

0
 

Author Comment

by:wanlijun
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

by:Chris Luttrell
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:wanlijun
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

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

Accepted Solution

by:
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

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question