asked on

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

Thank and let me know if you need more info!

Mike Eghtebas

;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..

abarefoot

ASKER

OK
Book1.xlsx

Mike Eghtebas

what you have posted is the output. I was interested in the input data to start from.

abarefoot

ASKER

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 Eghtebas

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.

abarefoot

ASKER

Sorry about that.
Book1.xlsx

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

abarefoot,

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

Mike

abarefoot

ASKER

Scott, Thanks this is exactly what I'm looking for!

PortletPaul

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