Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 7 day sales analysis query

Author  Topic 

usafelix
Posting Yak Master

165 Posts

Posted - 2014-08-31 : 12:23:39
Anyone can give sql query for this requirement output and default get the current day and time as day 7 with count start of 7 day before. Today is Sun.Thus start day is last sun.

now the time is 22:00 & group by shop plus underline +24 hour format

Sun Mon Tue Wed Thur Fri Sat Sun Total
Shop A 20:00-21:00 $2 $10 $15 $5 $2 $10 $0 $100 $xxx
Shop A 21:00-22:00 $1 $10 $15 $5 $2 $10 $0 $100 $xxx
Shop A 22:00-23:00 $1 $10 $15 $5 $2 $10 $0 $no sales $xxx
Total $4 $20 $30 $10 $4 $20 $0 $200 $xxx
-------------------------------------------------------------------
Shop Z 22:00-23:00 $20 $15 $5 $2 $10 $0 $no sales $xxx
Shop Z 23:00-00:00 $10 $15 $5 $2 $10 $0 $no sales $xxx
Total $30 $30 $10 $4 $20 $0 $no sales $xxx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-08-31 : 22:59:34
You have to at least show us how your table looks like.

Please do post your table DDL, sample data in DML format




KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2014-09-01 : 08:39:45
this is sales table sturcture and createdatetime format is 20140831 23:38:33:xxx

shop_id,createdatetime,sku,qty,amount
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-01 : 13:53:02
What we need id:

1. CREATE TABLE statements for each table
2. INSERT INTO statement to populate each table with test data
3. Your query so far.
4. The results of running your query on the test tables from steps 1 and 2
5. The desired results of a complete, correct query that meets your requirements.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-09-02 : 03:12:42
looking at your other thread, i guess this is what you want


select shop_id,
hr = convert(varchar(5), dateadd(hour, hr, '00:00'), 108)
+ '-'
+ convert(varchar(5), dateadd(hour, hr + 1, '00:00'), 108),
[SUN], [MON], [TUE], [WED], [THU],[FRI], [SAT]
from
(
select shop_id,
weekdy = upper(left(datename(weekday, createdatetime), 3),)
hr = datepart(hour, createdatetime),
amount
from sales_table
where .... -- place any where condition here
) d
pivot
(
sum(amount)
for weekdy in ([SUN], [MON], [TUE], [WED], [THU],[FRI], [SAT])
) p



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2014-09-02 : 07:03:49
I am compile this query have prompt error message " incorrect syntax error " , where is the wrong coding ?

select xsodetail.shopcode,
hr = convert(varchar(5), dateadd(hour, hr, '00:00'), 108)
+ '-'
+ convert(varchar(5), dateadd(hour, hr + 1, '00:00'), 108),
[SUN], [MON], [TUE], [WED], [THU],[FRI], [SAT]
from
(
select xsodetail.shopcode,
weekdy = upper(left(datename(weekday, xsodetail.ccreatedatetime), 3),)
hr = datepart(hour, xsodetail.createdatetime),
xsodetail.actualsalesamt
from xsodetail
where xsodetail.createdatetime = Convert(varchar, GETDATE(),112)
) d
pivot
(
sum(xsodetail.actualsalesamt)
for weekdy in ([SUN], [MON], [TUE], [WED], [THU],[FRI], [SAT])
) p
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-09-02 : 09:39:50
[code]
select
xsodetail.shopcode,
hr = convert(varchar(5), dateadd(hour, hr, '00:00'), 108)
+ '-'
+ convert(varchar(5), dateadd(hour, hr + 1, '00:00'), 108),
[SUN], [MON], [TUE], [WED], [THU], [FRI], [SAT]
from
(
select
xsodetail.shopcode,
weekdy = upper(left(datename(weekday, xsodetail.ccreatedatetime), 3))
hr = datepart(hour, xsodetail.createdatetime),
xsodetail.actualsalesamt
from xsodetail
where xsodetail.createdatetime >= dateaddday(day, datediff(day, 0, getdate()), 0)
and xsodetail.createdatetime < dateaddday(day, datediff(day, 0, getdate()), 1)
) d
pivot
(
sum(xsodetail.actualsalesamt)
for weekdy in ([SUN], [MON], [TUE], [WED], [THU], [FRI], [SAT])
) p
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2014-09-02 : 21:51:21
our sql server 2008. I am copy your query run our server then prompt error in below :
incorrect syntax near 'hr'
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-09-02 : 22:15:45
sorry typo error
select 
xsodetail.shopcode,
hr = convert(varchar(5), dateadd(hour, hr, '00:00'), 108)
+ '-'
+ convert(varchar(5), dateadd(hour, hr + 1, '00:00'), 108),
[SUN], [MON], [TUE], [WED], [THU], [FRI], [SAT]
from
(
select
xsodetail.shopcode,
weekdy = upper(left(datename(weekday, xsodetail.ccreatedatetime), 3)),
hr = datepart(hour, xsodetail.createdatetime),
xsodetail.actualsalesamt
from xsodetail
where xsodetail.createdatetime >= dateadd(day, datediff(day, 0, getdate()), 0)
and xsodetail.createdatetime < dateadd(day, datediff(day, 0, getdate()), 1)
) d
pivot
(
sum(xsodetail.actualsalesamt)
for weekdy in ([SUN], [MON], [TUE], [WED], [THU], [FRI], [SAT])
) p



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2014-09-02 : 23:55:25
error . The column prefix 'xsodetail' does not match with a table name or alias name used in a query, error code 107. Please look at it and help to solve. Thanks
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-09-03 : 04:30:58
PIVOT operator does not like alias
select 
shopcode,
hr = convert(varchar(5), dateadd(hour, hr, '00:00'), 108)
+ '-'
+ convert(varchar(5), dateadd(hour, hr + 1, '00:00'), 108),
[SUN], [MON], [TUE], [WED], [THU], [FRI], [SAT]
from
(
select
xsodetail.shopcode,
weekdy = upper(left(datename(weekday, xsodetail.ccreatedatetime), 3)),
hr = datepart(hour, xsodetail.createdatetime),
xsodetail.actualsalesamt
from xsodetail
where xsodetail.createdatetime >= dateadd(day, datediff(day, 0, getdate()), 0)
and xsodetail.createdatetime < dateadd(day, datediff(day, 0, getdate()), 1)
) d
pivot
(
sum(actualsalesamt)
for weekdy in ([SUN], [MON], [TUE], [WED], [THU], [FRI], [SAT])
) p



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2014-09-03 : 08:29:10
I don't understanding what is your mean "pivot opertor does not like alias", you mean what is action to do ? it is delete the pivot this command and run again in sql studio ?
Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2014-09-03 : 08:36:05
I have update this query again and run a time but still prompt The column prefix 'xsodetail' does not match with a table name or alias name used in a query, error code 107
select
a.shopcode,
hr = convert(varchar(5), dateadd(hour, hr, '00:00'), 108)
+ '-'
+ convert(varchar(5), dateadd(hour, hr + 1, '00:00'), 108),
[SUN], [MON], [TUE], [WED], [THU], [FRI], [SAT]
from
(
select
a.shopcode,
weekdy = upper(left(datename(weekday, a.createdatetime), 3)),
hr = datepart(hour, a.createdatetime),
a.actualsalesamt
from xsodetail a
where a.createdatetime >= dateadd(day, datediff(day, 0, getdate()), 0)
and a.createdatetime < dateadd(day, datediff(day, 0, getdate()), 1)
) d
pivot
(
sum(a.actualsalesamt) for weekdy in ([SUN], [MON], [TUE], [WED], [THU], [FRI], [SAT])
) p
Go to Top of Page
   

- Advertisement -