March 15, 2013 at 1:58 am
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[sales_report](
[salesid] [bigint] NOT NULL,
[productName] [varchar](100) NULL,
[Qty] [bigint] NULL,
[Date] [date] NULL,
CONSTRAINT [PK_sales_report] PRIMARY KEY CLUSTERED
(
[salesid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (1, N'dettol', 6, CAST(0xDD360B00 AS Date))
INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (2, N'hamam', 10, CAST(0xDE360B00 AS Date))
INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (3, N'sweet', 10, CAST(0xDF360B00 AS Date))
INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (4, N'test', 23, CAST(0xDC360B00 AS Date))
INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (5, N'boost', 10, CAST(0xDF360B00 AS Date))
INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (6, N'horlicks', 2, CAST(0xDD360B00 AS Date))
INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (7, N'shampo', 5, CAST(0xDE360B00 AS Date))
here i want
ProductName14151617Total
---------------------------------
dettol 06006
hamam0010010
boost0001010
horlicks02002
shampo00505
sweet0001010
test2300023
March 15, 2013 at 2:10 am
can you explain a little bit about the result set ...
what's 14,15,16,17 ...
on what column it is based upon ???
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
March 15, 2013 at 2:15 am
Could you pls elaborate your question!!
March 15, 2013 at 2:19 am
14,15 means Date like 14-03-2013,15-03-2013....etc.
March 15, 2013 at 2:22 am
i want to show sales details by daily basis and sum of Qty.
March 15, 2013 at 8:05 am
vanapandi (3/15/2013)
i want to show sales details by daily basis and sum of Qty.
So do you only want to show columns for days? What happens when you have more than 1 months worth of data? Do you only want to show days that you actually have data or do you want days in the middle when there are no sales?
This type of thing is not simple to put together and it requires more information from you. You will need to do a dynamic cross tab and either a tally or calendar table depending on how you want your results.
You should probably expand your date ranges a little bit so there are some holes and it crosses months. Then explain clearly what you want for output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply