Solved

PIVOT RESULT IN SQL SERVER 2008R2

Posted on 2013-06-16
15
399 Views
Last Modified: 2013-06-17
Hi

I have 3 columns namely EntryDate,EnteredBy,Boxcodes in a table.  I want to know how much BoxCodes each person (enteredby) has entered in all entrydates.  The following pivot table query doesn't sums up the total for each person for each day.


SELECT 'TotalBoxes' AS TotalBoxes,EntryDate AS [DOE],EnteredBy AS [USERS]
FROM
(SELECT BoxCode,EntryDate,EnteredBy FROM Patient_vw
 WHERE EntryDate > '2013-05-01' AND BoxCode IS NOT NULL
 GROUP BY BoxCode,EntryDate,EnteredBy)AS  pw
PIVOT
(
Count(BoxCode)
FOR BoxCode IN ([DOE],[USERS])
) AS Pvt

Please help on this

Thanks

Joe
0
Comment
Question by:JOLEEJJ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 5
15 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39252368
Can you provide a small sample of this data?
SELECT BoxCode, EntryDate, EnteredBy FROM Patient_vw

does the field [EntryDate] have times like 09:17:12? (or is it always 00:00:00?)

what does the expected result look like?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39252376
one more, sorry,
how any different codes are used in the field [BoxCode]?

let's assume for a moment that the answer to this is 'A','B','C'

are you expecting a result like this?

TotalBoxes EntryDate   EnteredBy  count_of_A   count_of_B   count_of_C  
TotalBoxes 2013-05-01 PortletPaul  100              50                 25
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39252743
Perhaps this approach will work for you, it arranges the pivot columns by vales of BoxCode and then shows the count per person, per day. So it loos like this for A B C in BoxCode:
TOTALBOXES    ENTEREDBY  ENTRYDATE      A    B    C
TotalBoxes    Grumpy    May, 02 2013    1    1    1
TotalBoxes    Happy     May, 03 2013    1    1    1
TotalBoxes    Sleepy    May, 04 2013    1    1    1
TotalBoxes    Sneezy    May, 01 2013    1    1    1

Open in new window

the code is:
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(BoxCode) 
            FROM Patient_vw
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT TotalBoxes, EnteredBy, EntryDate , ' + @cols + ' from 
            (
                select 
                    ''TotalBoxes'' as TotalBoxes      
                    , EnteredBy
                    , EntryDate
                    , BoxCode
                from Patient_vw
           ) AS x
            pivot 
            (
                 count(BoxCode)
                for BoxCode in (' + @cols + ')
            ) p '

exec (@query)
;

Open in new window

and a simulation may be inspected here: http://sqlfiddle.com/#!3/2d9b6/1
0
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 

Author Comment

by:JOLEEJJ
ID: 39254795
Hi All,

The sample result from this query :

"SELECT EntryDate,EnteredBy,BoxCode FROM
Patient_vw
WHERE EntryDate > '2013-05-01' "

is as follows:

EntryDate                                EnteredBy    BoxCode
2013-05-01 09:30:01.677      STEPHEN           K0644691
2013-05-01 09:30:42.080      STEPHEN           K0644692
2013-05-01 09:31:35.593      MARTIN           K0644608
2013-05-02 09:32:06.233      JAMES           K0644691
2013-05-02 09:32:55.340      KEVIN           K0644691
2013-05-02 09:34:35.430      KEVIN           K0644648
2013-05-03 09:35:21.433      TAYLOR           K0644695

Now, I want the Pivot query should look like this:

Entry Date                               EnteredBy    TotalBoxes
2013-05-01                              Stephen       2
2013-05-01                              Martin         1
2013-05-02                              James          1
2013-05-02                              KEVIN          2
2013-05-03                              TAYLOR        1

Thanks

Joe
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39254993
that's not a pivot, just a group by will do this job, however we do have to deal with the time in the datetime field. thanks for the sample data and expected results - this makes a huge difference. back soon.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39255010
produced results:
ENTRY DATE	ENTEREDBY	TOTALBOXES
05-01-2013	MARTIN		1
05-01-2013	STEPHEN		2
05-02-2013	JAMES		1
05-02-2013	KEVIN		2
05-03-2013	TAYLOR		1

-- produced by this code:
select
  convert(varchar,dateadd(day, datediff(day,0, EntryDate ), 0), 110) as [Entry Date]
, EnteredBy
, count(*) as TotalBoxes
from Patient_vw
group by
  dateadd(day, datediff(day,0, EntryDate ), 0)
, EnteredBy
order by
  dateadd(day, datediff(day,0, EntryDate ), 0)
, EnteredBy

Open in new window

see this working at: http://sqlfiddle.com/#!3/ae204/6
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39255024
some small corrections:

this requirement IS a 'pivot' but does not require use of the 'pivot feature' (sorry bad wording earlier)

for the requested yyyy-mm-dd format, use format mask 120 but need to remove hh:mm:ss, so use left() for this.

the amended query is
ENTRY DATE	ENTEREDBY	TOTALBOXES
2013-05-01	MARTIN		1
2013-05-01	STEPHEN		2
2013-05-02	JAMES		1
2013-05-02	KEVIN		2
2013-05-03	TAYLOR		1

-- produced by
select
  left(convert(varchar,dateadd(day, datediff(day,0, EntryDate ), 0), 120),10) as [Entry Date]
, EnteredBy
, count(*) as TotalBoxes
from Patient_vw
group by
  dateadd(day, datediff(day,0, EntryDate ), 0)
, EnteredBy
order by
  dateadd(day, datediff(day,0, EntryDate ), 0)
, EnteredBy

Open in new window

see this at: http://sqlfiddle.com/#!3/ae204/8
0
 

Author Comment

by:JOLEEJJ
ID: 39255070
Hi

I am sorry it is my mistake, I haven't given the proper data.  I know you can get the result by grouping.  However my data contains duplicate BoxCodes.  The grouping bring the duplicate as another records.


EntryDate                                EnteredBy    BoxCode
2013-05-01 09:30:01.677      STEPHEN           K0644691
2013-05-01 09:30:42.080      STEPHEN           K0644692
2013-05-01 09:30:42.080      STEPHEN           K0644692
2013-05-01 09:30:42.080      STEPHEN           K0644692
2013-05-01 09:30:42.080      STEPHEN           K0644693
2013-05-01 09:31:35.593      MARTIN           K0644608
2013-05-02 09:32:06.233      JAMES           K0644691
2013-05-02 09:32:55.340      KEVIN           K0644691
2013-05-02 09:34:35.430      KEVIN           K0644648
2013-05-03 09:35:21.433      TAYLOR           K0644695

in the above example, for stephen it should be 3 total boxes instead of 5 boxes.

Thanks

Joe
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39255113
for a total of 3, not 5, you either exclude some data, or you include BoxCodes into the calculations, like this:
ENTRY DATE	ENTEREDBY	BOXCODE	TOTALBOXES
2013-05-01	MARTIN		K0644608	1
2013-05-01	STEPHEN		K0644691	1
2013-05-01	STEPHEN		K0644692	3
2013-05-01	STEPHEN		K0644693	1
2013-05-02	JAMES		K0644691	1
2013-05-02	KEVIN		K0644648	1
2013-05-02	KEVIN		K0644691	1
2013-05-03	TAYLOR		K0644695	1

select
  left(convert(varchar,dateadd(day, datediff(day,0, EntryDate ), 0), 120),10) as [Entry Date]
, EnteredBy
, BoxCode
, count(*) as TotalBoxes
from Patient_vw
group by
  dateadd(day, datediff(day,0, EntryDate ), 0)
, EnteredBy
, BoxCode
order by
  dateadd(day, datediff(day,0, EntryDate ), 0)
, EnteredBy
, BoxCode

Open in new window

Please visit http://sqlfiddle.com/#!3/f253f/2, there are 2 queries to consider there. the one you see above AND one using the 'pivot feature'
-- and as an example of pivot - which is vastly different

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(BoxCode) 
            FROM Patient_vw
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT TotalBoxes, EnteredBy, [Entry Date] , ' + @cols + ' from 
            (
                select 
                    ''TotalBoxes'' as TotalBoxes      
                    , EnteredBy
                    , left(convert(varchar,dateadd(day, datediff(day,0, EntryDate ), 0), 120),10) as [Entry Date]
                    , BoxCode
                from Patient_vw
           ) AS x
            pivot 
            (
                 count(BoxCode)
                for BoxCode in (' + @cols + ')
            ) p '

exec (@query)
;

Open in new window

0
 

Author Comment

by:JOLEEJJ
ID: 39255161
Hi

Your result set repeats the EnteredBy more than once.  It should be  a distinct value and also
I have more 100000 boxcodes entereed with distinct value of more than 5000 on various dates by various users.

So, my result set should be how many boxes are entered for distinct dates for distinct users.

Thanks

Joe
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39255197
@JOLEEJJ

thank you, please then explain - in detail - how I arrive at a total of 3 and not 5

>>in the above example, for stephen it should be 3 total boxes instead of 5 boxes.
(your words not mine)

as you can see I have been trying quite hard to meet your every desire, but you are changing the rules each time. As I said before, to arrive at a total of 3 for Stephen on  2013-05-01 we either ignore some records, or we get more rows, or we get a total of 5.

ENTRY DATE      ENTEREDBY      BOXCODE      TOTALBOXES
2013-05-01      STEPHEN            K0644691      1 --<< why is this ignored?
2013-05-01      STEPHEN            K0644692      3
2013-05-01      STEPHEN            K0644693      1 --<< why is this ignored?

you have not explained "something" that is in your mind - but not yet in mine.
0
 

Author Comment

by:JOLEEJJ
ID: 39255232
Dear Paul,

I am sorry if I have not made clear.  Anyway,  Let us take the user STEPHEN.
Yes. I agree he has entered 5 boxes.  But what I need is the total count of distinct boxes.
In this case, the distinct boxes are: 1) K064491, 2) K0644692, 3) K0644693
Therefore the total number of boxes that STEPHEN has entered should be only 3 not 5 because box K0644692 has been repeated 3 times.
It may not make sense logically but we need to arrive the distinct no of boxes entered by each user for each day.
I hope I have made it clear this time.
Thanks for your patience.
I really appreciate it.

Joe
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39255315
Excellent - thanks. You have provided your own solution.... just use "distinct boxcode"
see line 12 below
ENTRY DATE	ENTEREDBY	TOTALBOXES
2013-05-01	MARTIN		1
2013-05-01	STEPHEN		3
2013-05-02	JAMES		1
2013-05-02	KEVIN		2
2013-05-03	TAYLOR		1

-- produced by
select
  left(convert(varchar,dateadd(day, datediff(day,0, EntryDate ), 0), 120),10) as [Entry Date]
, EnteredBy
, count(distinct BoxCode) as TotalBoxes
from Patient_vw
group by
  dateadd(day, datediff(day,0, EntryDate ), 0)
, EnteredBy
order by
  dateadd(day, datediff(day,0, EntryDate ), 0)
, EnteredBy

Open in new window

http://sqlfiddle.com/#!3/f253f/4
0
 

Author Closing Comment

by:JOLEEJJ
ID: 39255392
Thanks Paul.  This is what I needed.

Cheers,

Joe
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39255404
v.happy I got there in the end - I'm a bit slow it seems today.

just as a tip, with sample data and expected results I could arrive at the solution - before then I was really guessing (and hoping to attract your attention to my dilemma)...

thanks for the prompt closure, that's always appreciated. Cheers, Paul
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

687 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