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..
abarefootAuthor Commented:
Mike EghtebasDatabase and Application DeveloperCommented:
what you have posted is the output. I was interested in the input data to start from.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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.
abarefootAuthor Commented:
Sorry about that.
Book1.xlsx
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

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
abarefootAuthor Commented:
Scott,  Thanks this is exactly what I'm looking for!
PortletPaulEE Topic AdvisorCommented:
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
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.