• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

PIVOT RESULT IN SQL SERVER 2008R2

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
JOLEEJJ
Asked:
JOLEEJJ
  • 10
  • 5
1 Solution
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
JOLEEJJAuthor Commented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
JOLEEJJAuthor Commented:
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
 
PortletPaulCommented:
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
 
JOLEEJJAuthor Commented:
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
 
PortletPaulCommented:
@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
 
JOLEEJJAuthor Commented:
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
 
PortletPaulCommented:
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
 
JOLEEJJAuthor Commented:
Thanks Paul.  This is what I needed.

Cheers,

Joe
0
 
PortletPaulCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

  • 10
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now