SQL Script help

Below is what I currently have for my statement.  I'm looking for a way to have data for 5 days each in its own column.  I've never done a script like this.  See below for what I want.  I was thinking a case statement but that will for me to put the print_date which will cause issues showing location_id more then once as I only want one row for each location_id.

SELECT        company_id, location_id, COUNT(pick_ticket_no) AS pt_count_day1
FROM            oe_pick_ticket
WHERE        (delete_flag = 'n') and (print_date BETWEEN GETDATE() - 1 AND GETDATE() - 0)
GROUP BY company_id, location_id
ORDER BY location_id

2015-10-09_12-16-02.jpg
Thank and let me know if you need more info!
LVL 1
abarefootAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
;with cte as
(SELECT  company_id, location_id, pick_ticket_no, delete_flag,
      row_number() Over(Partition By company_id, location_id order by print_date) as rn
FROM oe_pick_ticket
WHERE delete_flag = 'n' and print_date BETWEEN GETDATE() - 1 AND GETDATE()
)
Select company_id, location_id
from cte
Where rn<6
Pivot (Count(pick_ticket_no)
For rn In ([1], [2], [3], [4], [5])) As pvt;

Open in new window

It will be much better it you supply some data (in electronic format not image) so I could test the solution.

edited..
0
abarefootAuthor Commented:
0
Mike EghtebasDatabase and Application DeveloperCommented:
what you have posted is the output. I was interested in the input data to start from.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

abarefootAuthor Commented:
Use this one.  Sheet1 is what I want the data to look like and Sheet 2 is the data I'm pulling from.
Book1.xlsx
0
Mike EghtebasDatabase and Application DeveloperCommented:
Where is print_date column, there is no data for it. Please make sure all of the data used are included in your sample data.
0
abarefootAuthor Commented:
Sorry about that.
Book1.xlsx
0
Scott PletcherSenior DBACommented:
SELECT        company_id, location_id,
              SUM(case when print_date >= GETDATE() - 5 AND print_date < GETDATE() - 4 then 1 else 0 end) AS pt_count_day1,
              SUM(case when print_date >= GETDATE() - 4 AND print_date < GETDATE() - 3 then 1 else 0 end) AS pt_count_day2,
              SUM(case when print_date >= GETDATE() - 3 AND print_date < GETDATE() - 2 then 1 else 0 end) AS pt_count_day3,
              SUM(case when print_date >= GETDATE() - 2 AND print_date < GETDATE() - 1 then 1 else 0 end) AS pt_count_day4,
              SUM(case when print_date >= GETDATE() - 1 AND print_date < GETDATE() - 0 then 1 else 0 end) AS pt_count_day5
FROM            oe_pick_ticket
WHERE        (delete_flag = 'n') and (print_date BETWEEN GETDATE() - 5 AND GETDATE() - 0)
GROUP BY company_id, location_id
ORDER BY location_id
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:
abarefoot,

I am sure the solution from Scott (from whom we learn from) will be the best solution you will ever have.

Mike
0
abarefootAuthor Commented:
Scott,  Thanks this is exactly what I'm looking for!
0
PortletPaulfreelancerCommented:
There is just one thing else to consider about Scott's query.
GETDATE() returns date and TIME, I think it needs to be "truncated" to a time of 00:00:00

This could be done in a number of different ways but here I'd just add a small CROSS JOIN which adds the truncated getdate() as a column I have called today, see line 8, and that is substituted for getdate in all other references
SELECT        company_id, location_id, 
              SUM(case when print_date >= d.today - 5 AND print_date < d.today - 4 then 1 else 0 end) AS pt_count_day1,
              SUM(case when print_date >= d.today - 4 AND print_date < d.today - 3 then 1 else 0 end) AS pt_count_day2,
              SUM(case when print_date >= d.today - 3 AND print_date < d.today - 2 then 1 else 0 end) AS pt_count_day3,
              SUM(case when print_date >= d.today - 2 AND print_date < d.today - 1 then 1 else 0 end) AS pt_count_day4,
              SUM(case when print_date >= d.today - 1 AND print_date < d.today - 0 then 1 else 0 end) AS pt_count_day5
FROM            oe_pick_ticket
CROSS JOIN (SELECT dateadd(day, datediff(day,0, GETDATE() ), 0) AS today) AS D
WHERE        (delete_flag = 'n') and (print_date BETWEEN d.today - 5 AND GETDATE() )
GROUP BY company_id, location_id
ORDER BY location_id
;

Open in new window

In all other respects the query is the same as Scott's
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.