Solved

PIVOT RESULT IN SQL SERVER 2008R2

Posted on 2013-06-16
15
371 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
  • 10
  • 5
15 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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
 

Author Comment

by:JOLEEJJ
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:JOLEEJJ
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
@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
Comment Utility
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 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
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
Comment Utility
Thanks Paul.  This is what I needed.

Cheers,

Joe
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Find correct record using  ;WITH 7 18
t-sql splitting name column 5 22
replication - alerts? 4 19
Sql query for filter 12 21
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now