mardi 28 février 2012 04:11

hello,
i have a table as below
TABLE [dbo].[item_sale]
==============
[id] [int] IDENTITY(1,1) NOT NULL,
[shakhaname] [nvarchar](150)
[tarik] [datetime] NULL,
[kharidarName] [nvarchar](150)
[vaarasnaam] [nvarchar](50)
[gaamName] [nvarchar](150)
[itemcategory] [nvarchar](50)
[itemname] [nvarchar](150)
[utpadakName] [nvarchar](150)
[biyaranvarg] [nvarchar](50)
[paaknaam] [nvarchar](150)
[paakvistar] [float] NULL,
[banavatlotno] [nvarchar](50)
[vechanvigat] [nvarchar](50)
[vechanbhavperkl] [float] NULL,
[vechanjatho] [float] NULL,
[kulkimmat] [float] NULL,
[billno] [varchar](20)
[sale_type] [nvarchar](15)
[sale_comm] [float] NULL,
[rahat_amt] [float] NULL,
[entry_dt] [datetime] NULL,
[entry_by] [nvarchar](50

I have to display records in column format based on the entry_dt, BILLNO field in the below mentioned format

entry_dt Billno kharidarname gaamname itemname kulkimmat rahat_amt cashonhand(kulkimmat-rahat_amt)
===== === ======== ======= ====== ====== ====== ====================

in the above result format the columns keep adding themself coz there are a maximum of 10 items in each bill
Need guidance for generating an SP or a query please
Im doing this proj in VB.net

thanks rgds
hari vaag

HV

Fractionné Naomi NMicrosoft Community Contributor, Moderator mardi 28 février 2012 04:32 New question - old thread
Fusionné KJian_ mercredi 29 février 2012 06:35 duplicate

mardi 28 février 2012 04:39

Modérateur

0

The idea will be the same as the thread you linked to:

;with cte as (select BillNo, entry_dt, ItemName, 
row_number() over (partition by BillNo, entry_dt order by ItemName) as Rn from YourTable)

select * from cte PIVOT (max(ItemName) 
FOR Rn IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) pvt

The above will create a table with fixed number of items per record (10). If you want to generate dynamic structure (using current max number per bill), then you will need to do it dynamically, but it still will be the same idea.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

mardi 28 février 2012 05:13

0

Hi,

The following link might be helpful for you.

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/ff160cf9-16d0-4b6b-8190-1fbd4c6e1aa1

Srikrishna

mercredi 29 février 2012 00:55

1

hi,

it's not clear to me what you are trying "to rotate"... anyway, you can perhaps have a look at the following code to see if it fits your needs..

SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.t (
	Id int NOT NULL IDENTITY,
	BillNo int NOT NULL,
	Value decimal(18,4) NOT NULL
	);
GO
INSERT INTO dbo.t 
	VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10);

INSERT INTO dbo.t 
	VALUES (2, 1), (2, 2), (2, 3), (2, 4), (2, 5), (2, 6), (2, 7);
GO
WITH cte AS (
	SELECT *, ROW_NUMBER() OVER (PARTITION BY BillNo ORDER BY Id) AS [r]
		FROM dbo.t
		WHERE 1 = 1 -- your filter conditions
	)
SELECT c.BillNo,
	SUM(CASE WHEN r = 1 THEN Value ELSE NULL END) AS [col1],
	SUM(CASE WHEN r = 2 THEN Value ELSE NULL END) AS [col2],
	SUM(CASE WHEN r = 3 THEN Value ELSE NULL END) AS [col3],
	SUM(CASE WHEN r = 4 THEN Value ELSE NULL END) AS [col4],
	SUM(CASE WHEN r = 5 THEN Value ELSE NULL END) AS [col5],
	SUM(CASE WHEN r = 6 THEN Value ELSE NULL END) AS [col6],
	SUM(CASE WHEN r = 7 THEN Value ELSE NULL END) AS [col7],
	SUM(CASE WHEN r = 8 THEN Value ELSE NULL END) AS [col8],
	SUM(CASE WHEN r = 9 THEN Value ELSE NULL END) AS [col9],
	SUM(CASE WHEN r = 10 THEN Value ELSE NULL END) AS [col10]
	FROM cte c
	GROUP BY BillNo;
GO
DROP TABLE dbo.t;
--<------------
BillNo      col1     col2    col3    col4    col5    col6    col7    col8    col9    col10
----------- -------- ------- ------- ------- ------- ------- ------- ------- ------- --------
1           1.0000   2.0000  3.0000  4.0000  5.0000  6.0000  7.0000  8.0000  9.0000  10.0000
2           1.0000   2.0000  3.0000  4.0000  5.0000  6.0000  7.0000  NULL    NULL    NULL
Warning: Null value is eliminated by an aggregate or other SET operation.

first I "numbered" the rows of each BillNo with the ROW_NUMBER function, then I aggregated them with the GROUP BY clause manually rotating the rows to columns based on the ROW_NUMBER value, using the CASE WHEN sintax to filter the desired row/column... but it's completely manual.. if your design will in the future allow, say, 11 rows/column you have to modify the projection... or you completely dinamically build it on the fly, similarly to the following,

SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.t (
	Id int NOT NULL IDENTITY,
	BillNo int NOT NULL,
	Value decimal(18,4) NOT NULL
	);
GO
INSERT INTO dbo.t 
	VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10);

INSERT INTO dbo.t 
	VALUES (2, 1), (2, 2), (2, 3), (2, 4), (2, 5), (2, 6), (2, 7);
GO
DECLARE @maxRow int;
WITH cte AS (
	SELECT ROW_NUMBER() OVER (PARTITION BY BillNo ORDER BY Id) AS [r]
		FROM dbo.t
		WHERE 1 = 1 -- your filter conditions
	)
	SELECT @maxRow = MAX(r)
		FROM cte;

DECLARE @nro int = 1;
DECLARE @cmd varchar(1000) = '';
WHILE @nro <= @maxRow BEGIN
	SET @cmd = @cmd + CASE WHEN LEN(@cmd) <> 0 THEN ',' + CHAR(13) + CHAR(10) ELSE '' END
		+ 'SUM(CASE WHEN r = ' + CONVERT(varchar, @nro) + ' THEN Value ELSE NULL END) AS [col' + CONVERT(varchar, @nro) + ']';
	SET @nro +=1;
END;
SET @cmd = 'WITH cte AS (
	SELECT *, ROW_NUMBER() OVER (PARTITION BY BillNo ORDER BY Id) AS [r]
		FROM dbo.t
		WHERE 1 = 1 -- your filter conditions
	)
SELECT c.BillNo,' + CHAR(13) + CHAR(10)
	+ @cmd + CHAR(13) + CHAR(10)
	+ '		FROM cte c'  + CHAR(13) + CHAR(10)
	+ '		GROUP BY BillNo';


EXEC (@cmd);
GO
DROP TABLE dbo.t;
--<----------
BillNo      col1     col2    col3    col4    col5    col6    col7    col8    col9    col10
----------- -------- ------- ------- ------- ------- ------- ------- ------- ------- --------
1           1.0000   2.0000  3.0000  4.0000  5.0000  6.0000  7.0000  8.0000  9.0000  10.0000
2           1.0000   2.0000  3.0000  4.0000  5.0000  6.0000  7.0000  NULL    NULL    NULL
Warning: Null value is eliminated by an aggregate or other SET operation.

where you first individuate the max of the rotation and then dinamically build the statement to rotate the results... but such scenarios really are "bad" :)

regards

http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/

Proposé comme réponse Naomi NMicrosoft Community Contributor, Moderator mercredi 29 février 2012 16:11
Marqué comme réponse Kalman TothMicrosoft Community Contributor, Moderator vendredi 9 mars 2012 19:42
Non marqué comme réponse Hari Vaag lundi 19 mars 2012 17:32

mercredi 29 février 2012 04:59

0

hello thanks for you suggestion and guidance, but the client needs it in this way, so i have to give it in print and display....

ill explain it again. my table has date, billno, itemname, itemqty, itemamt, totalamt.
each bill can have max 10 items. now when the client wants to view the sale for a particular date, he should get the result in column format with fields(itemname,itemqty, itemamt, totalamt) getting displayed column wise at the end of the resultset for that particular date, there should be a total at the bottom for each item

hope the above is clear..

rgds

HV

mercredi 29 février 2012 16:08

Modérateur

0

In this case you're talking about case based pivot, e.g.

;with cte as (select date, BillNo, ItemName, ItemQty, TotalAmt, row_number() over (partition by Date, BillNo order by ItemName) as Rn

from BillInfo)

select date, BillNo,

max(case when Rn = 1 then ItemName end) as Item1, 
max(case when Rn =1 then ItemQty end) as Qty1,
 max(case when Rn = 1 then TotalAmt end) as TotalAmt1,

max(case when Rn = 2 then ItemName end) as Item2, 
max(case when Rn =2 then ItemQty end) as Qty2, 
max(case when Rn = 2 then TotalAmt end) as TotalAmt2,

etc.

from cte

GROUP BY date, BillNo

I suggest to add totals per item in the reporting software.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Modifié Naomi NMicrosoft Community Contributor, Moderator mercredi 29 février 2012 16:09

vendredi 2 mars 2012 12:28

0

This type of report is best done in a report wrting tool such as SSRS

Dave Frommer - BI Architect - Independent

Proposé comme réponse Naomi NMicrosoft Community Contributor, Moderator vendredi 2 mars 2012 13:31

vendredi 2 mars 2012 17:18

0

thanks

can you guide me as to where do i start for SSRS please

rgds

HV

vendredi 2 mars 2012 19:05

Modérateur

0

Take a look at this blog post

Report Builder 3.0 - Table or Matrix Wizard

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

samedi 3 mars 2012 05:27

0

thanks Naomi N

i tried using the below

;with cte as (select entry_dt, BillNo, ItemName, vechanjatho, Total_Amt, row_number() over (partition by entry_dt, BillNo order by ItemName) as Rn
from item_sale)
select entry_dt, BillNo,
max(case when Rn = 1 then ItemName end) as Item1, 
max(case when Rn =1 then vechanjatho end) as Qty1,
max(case when Rn = 1 then Total_Amt end) as TotalAmt1,
max(case when Rn = 2 then ItemName end) as Item2, 
max(case when Rn =2 then vechanjatho end) as Qty2, 
max(case when Rn = 2 then Total_Amt end) as TotalAmt2
from cte
GROUP BY entry_dt, BillNo

and the output i get is different lines

2012-03-01 09:50:00.000	1000	dva A 	95	950	NULL	NULL	NULL
2012-03-01 09:51:00.000	1000	dva b	100	2500	NULL	NULL	NULL
2012-03-01 09:50:00.000	1001	dva A 	95	900	NULL	NULL	NULL
2012-03-01 09:51:00.000	1001	dva B 	100	2550	NULL	NULL	NULL

what i need is the bill no 1000 should display all records in one line (a max is upto 10 items in each bill)

can you kindly guide me with this please

rgds

HV

dimanche 4 mars 2012 02:27

Modérateur

0

Looks like we don't want to include date field. Remove date field, e.g.

;with cte as (select BillNo, ItemName, ItemQty, TotalAmt, row_number() over (partition by BillNo order by ItemName, date) as Rn

from BillInfo)

select BillNo,

max(case when Rn = 1 then ItemName end) as Item1, 
max(case when Rn =1 then ItemQty end) as Qty1,
 max(case when Rn = 1 then TotalAmt end) as TotalAmt1,

max(case when Rn = 2 then ItemName end) as Item2, 
max(case when Rn =2 then ItemQty end) as Qty2, 
max(case when Rn = 2 then TotalAmt end) as TotalAmt2,

etc.

from cte

GROUP BY BillNo

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

dimanche 4 mars 2012 03:55

0

thanks

ive tweaked the code for my needs and ive got the desired output. thanks a ton for the same....

below is the code. only 2 things which are left in this

1. my columns are max 10, but if they are below 10, then how do i display only those columns which are not null, which means that RN in the code can run a max up to 10, but if the rows are only 3 then it should only show 3 columns(means RN).

2. how do i add a total row wise and column wise please

;with cte as (select entry_dt,BillNo,kharidarname,  gaamname, ItemName, vechanjatho, Total_Amt, row_number() over (partition by BillNo order by ItemName, entry_dt) as Rn

from item_sale)

select max(entry_dt) as entry_dt, BillNo, max(kharidarname) as kharidarname,  max(gaamname) as gaamname,

max(case when Rn = 1 then ItemName end) as Item1, 
max(case when Rn =1 then vechanjatho end) as Qty1,
max(case when Rn = 1 then Total_Amt end) as TotalAmt1,
max(case when Rn = 2 then ItemName end) as Item2, 
max(case when Rn =2 then vechanjatho end) as Qty2, 
max(case when Rn = 2 then Total_Amt end) as TotalAmt2
from cte
GROUP BY BillNo

rgds

HV

dimanche 4 mars 2012 04:14

Modérateur

0

If you want the number of columns to be dynamic, you will use the exactly same idea, but dynamically, e.g.

select entry_dt,BillNo,kharidarname,  gaamname, ItemName, vechanjatho, Total_Amt, row_number() 
over (partition by BillNo order by ItemName, entry_dt) as Rn
into #TempResults
from item_sale

declare @SQL nvarchar(max), @Rows smallint, @Loop smallint

select @Rows = max(Rn) from #TempResults

select @Loop = 1, @SQL = ''

while @Loop <=@Rows
  BEGIN
   SET @SQL = @SQL + '
,max(case when Rn = ' + cast(@Loop as varchar(5)) + 
' then ItemName END as ItemName' + cast(@Loop as varchar(5)) + 
',max(case when Rn =' + cast(@Loop as varchar(5)) +  ' then vechanjatho end) as Qty' + 
+ cast(@Loop as varchar(5)) + 
',max(case when Rn = '+ cast(@Loop as varchar(5)) + ' then Total_Amt end) as TotalAmt'+ cast(@Loop as varchar(5)) 
  SET @Loop = @Loop + 1
 END

SET @SQL = 'select max(entry_dt) as entry_dt, BillNo, max(kharidarname) as kharidarname,  
max(gaamname) as gaamname' + @SQL + '
GROUP BY BillNo'

print @SQL -- test

execute(@SQL)

Once you get it working, you can also add totals to this query.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Modifié Naomi NMicrosoft Community Contributor, Moderator dimanche 4 mars 2012 20:28
Marqué comme réponse Kalman TothMicrosoft Community Contributor, Moderator vendredi 9 mars 2012 19:42
Non marqué comme réponse Hari Vaag lundi 19 mars 2012 17:32

dimanche 4 mars 2012 16:45

0

thanks again, but below is the error i get

Msg 102, Level 15, State 1, Line 15
Incorrect syntax near '@SQL'.
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'SET'.

HV

dimanche 4 mars 2012 16:57

0

There's a missing SELECT:

SELECT @SQL = @SQL + '

dimanche 4 mars 2012 17:14

0

thanks

i get a new error, i know what the problem is but dont know how to solve it.

select max(entry_dt) as entry_dt, BillNo, max(kharidarname) as kharidarname, max(gaamname) as gaamname max(case when Rn = 1 then ItemName END) as ItemName1,max(case when Rn =1 then vechanjatho end) as Qty1,max(case when Rn = 1 then Total_Amt end) as TotalAmt1max(case when Rn = 2 then ItemName END) as ItemName2,max(case when Rn =2 then vechanjatho end) as Qty2,max(case when Rn = 2 then Total_Amt end) as TotalAmt2max(case when Rn = 3 then ItemName END) as ItemName3,max(case when Rn =3 then vechanjatho end) as Qty3,max(case when Rn = 3 then Total_Amt end) as TotalAmt3max(case when Rn = 4 then ItemName END) as ItemName4,max(case when Rn =4 then vechanjatho end) as Qty4,max(case when Rn = 4 then Total_Amt end) as TotalAmt4 from item_sale GROUP BY BillNo

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'max'.

error is that after every totalamt in the select case there is a (,) missing, but how do i add it, coz when i add it also adds a comma at the end of the SQL statement.

kindly help

rgds

HV

dimanche 4 mars 2012 17:19

0

C'mon, is it really that hard to figure out there's a missing comma? One reason might be the lack of proper code formatting though.

I'm not sure if the task you're faced with is compliant with the skills available...

dimanche 4 mars 2012 17:26

0

my dear friend, i know the problem, and i know the solution that i have to check that the loop and rows values if they are equal it should not put the (,), but im not aware of the syntax, thats the help im asking for .... rgds

HV

dimanche 4 mars 2012 17:53

0

hello thanks a ton for all your help, ive tweaked your code and made it work exactly the way i want results. only thing what i need now is row wise and column wise total please.... below is the code

drop table #tempresults
select entry_dt,BillNo,kharidarname,  gaamname, ItemName, vechanjatho, Total_Amt, row_number() 
over (partition by BillNo order by ItemName, entry_dt) as Rn
into #TempResults
from item_sale
declare @SQL nvarchar(max), @Rows smallint, @Loop smallint;
select @Rows = max(Rn) from #TempResults;
print @rows
select @Loop = 1, @SQL = ' ';
while @Loop <=@Rows
BEGIN
select @SQL = @SQL + 'max(case when Rn = ' + cast(@Loop as varchar(5)) + ' then ItemName END) as ItemName' + cast(@Loop as varchar(5)) + 
',max(case when Rn =' + cast(@Loop as varchar(5)) +  ' then vechanjatho end) as Qty' + cast(@Loop as varchar(5)) + 
',max(case when Rn = '+ cast(@Loop as varchar(5)) + ' then Total_Amt end) as TotalAmt'+ cast(@Loop as varchar(5)) 
if @loop < @Rows
begin 
select @sql = @sql +','
end
print @loop
 SET @Loop = @Loop + 1;
 END
SET @SQL = 'select max(entry_dt) as entry_dt, BillNo, max(kharidarname) as kharidarname, max(gaamname) as gaamname, ' + @SQL + ' from #tempResults GROUP BY BillNo'
print @SQL -- test
execute(@SQL)

my results are as below

2012-03-01 09:51:00.000	1000	harI	pa3`pura	dva A 	95	950	dva b	100	2500	NULL	NULL	NULL	NULL	NULL	NULL
2012-03-01 09:51:00.000	1001	harI	pa3`pura	dva A 	95	900	dva B 	100	2550	NULL	NULL	NULL	NULL	NULL	NULL
2012-03-02 09:50:00.000	1002	harI	pa3`pura	dva A 	10	1500	dva b	20	2000	dva C	30	2500	dva D	40	3000

kindly help me get the totals please

rgds

HV

dimanche 4 mars 2012 20:27

Modérateur

0

Can you post your input (as DDL + insert statements with data) and desired output? BTW, what are your own thoughts of how to get the desired result?

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

lundi 5 mars 2012 16:48

0

hello,

i did not get your question. can you kindly explain please...

rgs

HV

lundi 5 mars 2012 17:03

Modérateur

0

Take a look at how this question is asked

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/de09496b-701d-4609-ae31-2adc084c18ed/

I want you to ask your question similar way - e.g. provide structure for your table and data as insert statements and then what kind of output you want based on input. So far I wrote all the queries from the top of my head - but having a working sample will also help to create a working solution which you would not need to tweak.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

mercredi 14 mars 2012 03:27

0

hello,

your help has so far solved my query, but now i need the output itemwise so that i can total it at the end of the page.

my desired output format is below

2012-03-01 09:51:00.000	1000	harI	pa3`pura	dva A 	95	950	dva b	100	2500	NULL	NULL	NULL	NULL	NULL	NULL
2012-03-01 09:51:00.000	1001	harI	pa3`pura	dva A 	95	900	dva B 	100	2550	NULL	NULL	NULL	NULL	NULL	NULL
2012-03-02 09:50:00.000	1002	harI	pa3`pura	dva A 	10	1500	dva b	20	2000	dva C	30	2500	dva D	40	3000

my table structure as below

CREATE TABLE [dbo].[item_sale](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[shakhaname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[tarik] [datetime] NULL,
	[kharidarName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[vaarasnaam] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[gaamName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[itemcategory] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[itemname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[utpadakName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[biyaranvarg] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[paaknaam] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[paakvistar] [float] NULL,
	[banavatlotno] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[vechanvigat] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[vechanbhavperkl] [float] NULL,
	[vechanjatho] [float] NULL,
	[kulkimmat] [float] NULL,
	[billno] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[sale_type] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[sale_comm] [float] NULL,
	[rahat_amt] [float] NULL,
	[entry_dt] [datetime] NULL,
	[entry_by] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[total_amt] [float] NULL,
 CONSTRAINT [PK_cashmemo] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

and insert statements as below

insert into item_sale values('4rad','3/7/2012 13:21','dcdcd','','dcdc','j>taunaxk dva','dva 1','taru~a','','','0','dcdcd c','10','100','100','10000','1000','cashmemo','0','0','3/7/2012 13:22','hari','10000')
insert into item_sale values('4rad','3/12/2012 16:52','manv','','paT~apura','j>taunaxk dva','dva 1','taru~a','','','0','jkn nknk 9879878','12','25','100','2500','1001','cashmemo','0','0','3/12/2012 16:53','','2500')
insert into item_sale values('4rad','3/12/2012 16:55','p/vI~a','','moDera','j>taunaxk dva','dva 1','taru~a','','','0','cdv dfvfd vfd','0','25','10','250','1002','cashmemo','0','0','3/12/2012 16:56','','250')
insert into item_sale values('4rad','3/12/2012 16:55','p/vI~a','','moDera','j>taunaxk dva','dva 1','taru~a','','','0','cdv dfvfd vfd','12','25','12','300','1002','cashmemo','0','0','3/12/2012 16:56','','300')
insert into item_sale values('4rad','3/12/2012 18:16','fvfv','fvf vf','dvdvdfvdfv fdvfd','bIyar~a','dva n> 1','','','','0','fvfvff vfvf','150','20','100','2000','1500','cashmemo_1','0','0','3/12/2012 18:16','','2000')
insert into item_sale values('4rad','3/12/2012 18:32','dcdscdsc','','sdcdscdsc','bIyar~a','dva n> 1','','','','0','','','20','100','2000','1','rahat','40','500','3/12/2012 18:32','','1540')
insert into item_sale values('4rad','3/12/2012 18:36','fvfvfdvf','','fvfvfv','bIyar~a','dva n> 1','','','','0','','','20','100','2000','2','rahat','50','500','3/12/2012 18:36','','1550')

hope my question is clear for you to help me.... I have to display the results sorted by itemwise and total it row wise...

rgds

HV

jeudi 15 mars 2012 13:47

0

hello,

can anyone please help me on this

rgds

HV

lundi 19 mars 2012 17:34

0

hello,

i have given the details below, can you please help me with the final SP pls

rgds

HV

lundi 19 mars 2012 17:41

Modérateur

0

What is the result you're currently getting and what is the result you want to get? Did you try adding GROUPING SETS to the original query to introduce vertical total?

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

lundi 19 mars 2012 17:48

0

hello

the results that i need are as below

2012-03-01 09:51:00.000	1000	harI	pa3`pura	dva A 	95	950	dva b	100	2500	NULL	NULL	NULL	NULL	NULL	NULL
2012-03-01 09:51:00.000	1001	harI	pa3`pura	dva A 	95	900	dva B 	100	2550	NULL	NULL	NULL	NULL	NULL	NULL
2012-03-02 09:50:00.000	1002	harI	pa3`pura	dva A 	10	1500	dva b	20	2000	dva C	30	2500	dva D	40	3000

which means that the grouping has to be done on Billno first and then itemname, only then i will be able to give the total verticaly. but the results which im getting currently is in the serial the user is buying the product in ... like in the above result, the user may buy "dva B" first and then the second item as "dva A" so i need to group by billno, itemname

lemme know if im not clear pls....

rgds

HV

lundi 19 mars 2012 18:11

Modérateur

0

Sorry, I don't understand you. Show your current output, what is wrong with it and what output do you want. I don't understand the above output and I don't see any totaling.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

samedi 24 mars 2012 09:00

0

Hello

Below is the table structure

CREATE TABLE [dbo].[item_sale](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[shakhaname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[tarik] [datetime] NULL,
	[kharidarName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[vaarasnaam] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[gaamName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[itemcategory] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[itemname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[utpadakName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[biyaranvarg] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[paaknaam] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[paakvistar] [float] NULL,
	[banavatlotno] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[vechanvigat] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[vechanbhavperkl] [float] NULL,
	[vechanjatho] [float] NULL,
	[kulkimmat] [float] NULL,
	[billno] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[sale_type] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[sale_comm] [float] NULL,
	[rahat_amt] [float] NULL,
	[entry_dt] [datetime] NULL,
	[entry_by] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[total_amt] [float] NULL,
 CONSTRAINT [PK_cashmemo] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Below is the SP

drop table #tempresults
declare @SQL nvarchar(max), 
@Rows smallint, 
@Loop smallint

select entry_dt,BillNo,kharidarname,  gaamname, ItemName, vechanjatho, Total_Amt, row_number() 
over (partition by BillNo order by ItemName, entry_dt) as Rn
into #TempResults
from item_sale
select @Rows = max(Rn) from #TempResults;
print @rows
select @Loop = 1, @SQL = ' ';
while @Loop <=@Rows
BEGIN
select @SQL = @SQL + 'max(case when Rn = ' + cast(@Loop as varchar(5)) + ' then ItemName END) as ItemName' + cast(@Loop as varchar(5)) + 
',max(case when Rn =' + cast(@Loop as varchar(5)) +  ' then vechanjatho end) as Qty' + cast(@Loop as varchar(5)) + 
',max(case when Rn = '+ cast(@Loop as varchar(5)) + ' then Total_Amt end) as TotalAmt'+ cast(@Loop as varchar(5)) 
if @loop < @Rows
begin 
select @sql = @sql +','
end
print @loop
 SET @Loop = @Loop + 1;
 END
SET @SQL = 'select max(entry_dt) as entry_dt, BillNo, max(kharidarname) as kharidarname, max(gaamname) as gaamname, ' + @SQL + ' from #tempResults GROUP BY BillNo,ItemName'

print @SQL -- test
execute(@SQL)

below is the results which im getting but that is NOT CORRECT

2012-03-20 13:04:00.000	1000	hrI	paT~apura	^aanuTop 1 lITr	10	3000	NULL	NULL	NULL	NULL	NULL	NULL
2012-03-22 17:14:00.000	1001	dcdcd 	dcdc d	^aanuTop 1 lITr	90	27000	NULL	NULL	NULL	NULL	NULL	NULL
2012-03-22 17:14:00.000	1001	dcdcd 	dcdc d	NULL	NULL	NULL	^aanuTop 250 g/am	10	1000	NULL	NULL	NULL
2012-03-20 13:05:00.000	1000	hrI	paT~apura	NULL	NULL	NULL	rIjNT bayr 100 g/am	20	2400	NULL	NULL	NULL
2012-03-20 13:05:00.000	1000	hrI	paT~apura	NULL	NULL	NULL	NULL	NULL	NULL	rIjNT bayr 250 g/am	100	23000
2012-03-23 11:05:00.000	1002	dcdc	dth bb	yuirya {fko 	50	14582.5	NULL	NULL	NULL	NULL	NULL	NULL

the correct result what i want is below

Entry_DT	Billno	kharidaarname	gaamname	itemname1	qty1	total1	itemname2	qty2	total2	itemname3	qty3	total3	itemname4	qty4	total4	itemname5	qty5	total4
3/20/2012	1000	hrI	paT~apura	^aanuTop 1 lITr	10	3000	rIjNT bayr 100 g/am	20	2400	rIjNT bayr 250 g/am	100	23000	NULL	NULL	NULL	NULL	NULL	NULL
3/22/2012	1001	dcdcd 	dcdc d	^aanuTop 1 lITr	90	27000	NULL	NULL	NULL	NULL	NULL	NULL	^aanuTop 250 g/am	10	1000	NULL	NULL	NULL
3/23/2012	1002	dcdc	dth bb	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL				yuirya {fko 	50	14582.5

There is no limit to the itemnames so it can go to itemname1.... itemname...nth value

i need the items to be listed in such a way that i can get a total at the end of the report vertically. this is possible only when i can group the itemnames with the same name.

rgds

HV

samedi 24 mars 2012 10:51

0

It would certainly help if you could post INSERT statemetns with sample data and the result you want from that data. It does not have to be that many, but some 20 rows.

Also, please bear in mind that since the column names is partly in your local language, they are a bit of nonsense to some of us.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

samedi 24 mars 2012 13:46

0

thankx

below are insert statements

insert into item_sale values('34','4rad','3/20/2012 1:04:29 PM','hrI','','paT~apura','j>taunaxk dva','^aanuTop 1 lITr','taru~a','','','0','fvcfv v','10','300','10','3000','1000','cashmemo','0','0','3/20/2012 1:04:00 PM','hari','3000','sale');
insert into item_sale values('35','4rad','3/20/2012 1:04:29 PM','hrI','','paT~apura','j>taunaxk dva','rIjNT bayr 100 g/am','taru~a','','','0','fvcfv v','20','120','20','2400','1000','cashmemo','0','0','3/20/2012 1:05:00 PM','hari','2400','sale');
insert into item_sale values('36','4rad','3/20/2012 1:04:29 PM','hrI','','paT~apura','j>taunaxk dva','rIjNT bayr 250 g/am','taru~a','','','0','fvcfv v','10','230','100','23000','1000','cashmemo','0','0','3/20/2012 1:05:00 PM','hari','23000','sale');
insert into item_sale values('37','4rad','3/22/2012 5:13:51 PM','dcdcd ','','dcdc d','j>taunaxk dva','^aanuTop 1 lITr','taru~a','','','0','cfvfvf ','10','300','90','27000','1001','cashmemo','0','0','3/22/2012 5:14:00 PM','hari','27000','sale');
insert into item_sale values('38','4rad','3/22/2012 5:13:51 PM','dcdcd ','','dcdc d','j>taunaxk dva','^aanuTop 250 g/am','taru~a','','','0','cfvfvf ','10','100','10','1000','1001','cashmemo','0','0','3/22/2012 5:14:00 PM','hari','1000','sale');
insert into item_sale values('39','4rad','3/23/2012 11:05:35 AM','dcdc','dcdcdc','dth bb','ra.qatar','yuirya {fko ','','','','0','gg gvv','10','291.65','50','14582.5','1002','cashmemo','0','0','3/23/2012 11:05:00 AM','hari','14582.5','sale');
insert into item_sale values('41','4rad','3/24/2012 3:49:21 PM','jtaInwa[','','paT~apura','j>taunaxk dva','rIjNT bayr 250 g/am','taru~a','','','0','fv fv fvv','10','230','10','2300','1003','cashmemo','0','0','3/24/2012 3:50:00 PM','hari','2300','sale');
insert into item_sale values('42','4rad','3/24/2012 3:49:21 PM','jtaInwa[','','paT~apura','j>taunaxk dva','Aoma{T 250 g/am ^aanuka','taru~a','','','0','fv fv fvv','15','270','10','2700','1003','cashmemo','0','0','3/24/2012 3:50:00 PM','hari','2700','sale');

and the output that i want is as below

Entry_DT	Billno	kharidaarname	gaamname	itemname1	qty1	total1	itemname2	qty2	total2	itemname3	qty3	total3	itemname4	qty4	total4	itemname5	qty5	total4
3/20/2012	1000	hrI	paT~apura	^aanuTop 1 lITr	10	3000	rIjNT bayr 100 g/am	20	2400	rIjNT bayr 250 g/am	100	23000	NULL	NULL	NULL	NULL	NULL	NULL
3/22/2012	1001	dcdcd 	dcdc d	^aanuTop 1 lITr	90	27000	NULL	NULL	NULL	NULL	NULL	NULL	^aanuTop 250 g/am	10	1000	NULL	NULL	NULL
3/23/2012	1002	dcdc	dth bb	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL				yuirya {fko 	50	14582.5

thanks and regards

HV

samedi 24 mars 2012 22:34

0

Thanks for the INSERT statements! I had some problem to get them to work, but I stripped of the last column from the VALUES clause and I was able to insert the data. Next time you post sample data, please test that it works with the CREATE TABLE script you have posted.

So here is a query and a challenge. The challenge is for me to explain what the beast actually does...

Anyway, the result is not exactly as you posted it, but it is my interpretation of your narrative. You want three columns for each indivdual item: Name, Qty and Amt. Is this correctly understood?

Here is the query:

DECLARE @repeatpart nvarchar(MAX),
@SQL nvarchar(MAX),
@nlmarker char(6) = '<CRNL>'

; WITH numbered AS (
   SELECT ItemName,
rn = row_number() OVER(ORDER BY ItemName)
   FROM   (SELECT DISTINCT ItemName FROM item_sale) AS d
)
SELECT @repeatpart =
   (SELECT ' MAX(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
' THEN ItemName END) AS ItemName' + + ltrim(str(rn)) + @nlmarker +
   ' MAX(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
' THEN vechanjatho END) AS Qty' + ltrim(str(rn)) + @nlmarker +
   ' MAX(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
' THEN Total_Amt END) AS Amt' + ltrim(str(rn)) + @nlmarker AS [text()]
FROM   numbered
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')

-- Strip last @nlmarker.
SELECT @repeatpart = substring(@repeatpart, 1, len(@repeatpart) - len(@nlmarker))

-- Replace @nlmarker with comma, CR-LF and an indent.
SELECT @repeatpart = replace(@repeatpart, @nlmarker, ',' + char(13) + char(10) + space(7))

-- Compose the final SQL.
SELECT @SQL =
'SELECT BillNo, kharidarname, gaamname,
' + @repeatpart + '
FROM item_sale
GROUP BY BillNo, kharidarname, gaamname'

print @SQL -- test
execute(@SQL)

To make it a little easier, I compose the SQL in two steps. I first compose the repeated part, the one with all the MAX. The initial CTE is simple: it gets all distinct item names and number them.

I then use FOR XML PATH('') to concatenate all rows into a single string. Instead of using a loop, I can do it in one statement. It's certainly obscure, but it is the standard method to composed a concatenated list from a table source. But since it's XML, characters like < are tokenised, so I need to extract the actual charcter value, and that's the .value() thing at the end.

The @nlmarker is added once too many, so I strip the last one. Then I replace it with the characters I want in that place. (I don't think XML handles embedded CR-LF too well.

Then I composed the final query, print it and execute.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

dimanche 25 mars 2012 16:49

0

hi

thanks for your reply....

I get error as below on the 3rd line "<CRNL>"

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 12
Must declare the scalar variable "@nlmarker".
Msg 137, Level 15, State 2, Line 21
Must declare the scalar variable "@nlmarker".
Msg 137, Level 15, State 2, Line 24
Must declare the scalar variable "@nlmarker".

when i edit the 3rd line to "@nlmarker char(6)" i dont get any result....

can you kindly help on this pls

rgds

HV

dimanche 25 mars 2012 19:20

0

So you are running on SQL 2005. Excuse me for being a bit brave (well lazy),
and assuming that you are on SQL 2008.

The fix is simple. Change

DECLARE @repeatpart nvarchar(MAX),
@SQL nvarchar(MAX),
@nlmarker char(6) = '<CRNL>'

to

DECLARE @repeatpart nvarchar(MAX),
@SQL nvarchar(MAX),
@nlmarker char(6)

SELECT @nlmarker = '<CRNL>'

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Marqué comme réponse Hari Vaag lundi 26 mars 2012 13:02
Non marqué comme réponse Hari Vaag lundi 26 mars 2012 13:02

lundi 26 mars 2012 13:01

0

A million thanks for the solution...

Just need 2 more things if you can help me out with please..

1. Row wise total of the amount at the end of the report.

2. i need to pass 3 parameters --- from date, to date, category name

kindly help me with the same in your SP please

rgds

Modifié Hari Vaag lundi 26 mars 2012 13:09 Forgot the point

lundi 26 mars 2012 13:25

Modérateur

0

Row wise total is a simple sum of TotalAmount. What seems to be the problem of passing the parameters?

DECLARE @repeatpart nvarchar(MAX), 
        @SQL nvarchar(MAX),
        @nlmarker char(6) 

declare @CategoryID int -- will get category id based on passed Category Name
select @CategoryID = CategoryID from dbo.Categories where
CategoryName = @Category

SET @nlmarker= '<CRNL>'

; WITH numbered AS (
   SELECT ItemName, 
          rn = row_number() OVER(ORDER BY ItemName)
   FROM   (SELECT DISTINCT ItemName FROM item_sale

where [SaleDate] between @StartDate and @EndDate and
CategoryID = @CategoryID

) AS d
)
SELECT @repeatpart = 
   (SELECT ' MAX(CASE WHEN ItemName = ' + quotename(ItemName, '''') + 
                ' THEN ItemName END) AS ItemName' + + ltrim(str(rn)) + @nlmarker +
           ' SUM(CASE WHEN ItemName = ' + quotename(ItemName, '''') + 
                ' THEN vechanjatho END) AS Qty' + ltrim(str(rn)) + @nlmarker +
           ' SUM(CASE WHEN ItemName = ' + quotename(ItemName, '''') + 
                ' THEN Total_Amt END) AS Amt' + ltrim(str(rn)) + @nlmarker AS [text()]
    FROM   numbered
    FOR    XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')

-- Strip last @nlmarker.
SELECT @repeatpart = substring(@repeatpart, 1, len(@repeatpart) - len(@nlmarker))

-- Replace @nlmarker with comma, CR-LF and an indent.
SELECT @repeatpart = replace(@repeatpart, @nlmarker, ',' + char(13) + char(10) + space(7))

-- Compose the final SQL.
SELECT @SQL = 
'SELECT BillNo, kharidarname, gaamname, 
' + @repeatpart + ', SUM(Total_Amt) as [Total Amount]
FROM    item_sale
WHERE [SaleDate] between @StartDate and @EndDate and
CategoryID = @CategoryID

GROUP   BY BillNo, kharidarname, gaamname'

print @SQL -- test
execute sp_executeSQL @SQL, N'@StartDate datetime, @EndDate datetime, @CategoryID int', @StartDate, @EndDate, @CategoryID

I posted this code without consulting with your actual structure and field names, so pick up correct field names for CategoryID and SaleDate.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Modifié Naomi NMicrosoft Community Contributor, Moderator mercredi 28 mars 2012 03:35

lundi 26 mars 2012 21:15

0

-- Strip last @nlmarker.
SELECT @repeatpart = substring(@repeatpart, 1, len(@repeatpart) -

len(@nlmarker))

-- Replace @nlmarker with comma, CR-LF and an indent.
SELECT @repeatpart = replace(@repeatpart, @nlmarker, ',' + char(13) +

char(10) + space(7))

-- Compose the final SQL.
SELECT @SQL =
'SELECT BillNo, kharidarname, gaamname,
' + @repeatpart + ', SUM(Total_Amt) as [Total Amount]

A small simplification: in this case we can keep the last @nlmarker so
this be changed to:

-- Replace @nlmarker with comma, CR-LF and an indent.
SELECT @repeatpart = replace(@repeatpart, @nlmarker, ',' + char(13) +
char(10) + space(7))

-- Compose the final SQL.
SELECT @SQL =
'SELECT BillNo, kharidarname, gaamname,
' + @repeatpart + ' SUM(Total_Amt) as [Total Amount]

Note that I removed the comma on this line.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

mardi 27 mars 2012 03:21

0

Thanks for your solution,

My Mistake i probably kept the question in a wrong way....

Along with the rowwise total I need columnwise total for amount also please

can you help me with this pls....

rgds

HV

mardi 27 mars 2012 07:26

0

My Mistake i probably kept the question in a wrong way....

Yes, if you provide table defintion, insert statements and sample data, it so much easier. Not the least, because there is less guessing to do.

Along with the rowwise total I need columnwise total for amount also please

I was just waiting for that one. :-) If you are on SQL 2008, the GROUPING SETS feature could come to use. However, I am at work now, so I can't try it. Then again, if you are presenting the data in some grid, doesn't the grid have a sum capability?

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Proposé comme réponse Naomi NMicrosoft Community Contributor, Moderator mardi 27 mars 2012 13:20

mardi 27 mars 2012 13:22

Modérateur

0

I also suggest to use Grouping Sets. Recently I found a very good blog by Jacob Sebastian explaining it.

http://beyondrelational.com/modules/2/blogs/28/posts/10523/t-sql-tuesday-016-summarizing-data-using-grouping-sets.aspx

See, if you can figure out a whole solution now based on what we already posted and post it here.

This will work in SQL 2008 and up.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Modifié Naomi NMicrosoft Community Contributor, Moderator mardi 27 mars 2012 13:23

mercredi 28 mars 2012 03:17

0

hi,

thanks, but m on SQL 2005.

rgds

HV

mercredi 28 mars 2012 03:34

Modérateur

0

In this case, how about:

-- Compose the final SQL.
SELECT @SQL = 
'SELECT BillNo, kharidarname, gaamname, 
' + @repeatpart + ', SUM(Total_Amt) as [Total Amount]
FROM    item_sale
WHERE [SaleDate] between @StartDate and @EndDate and
CategoryID = @CategoryID

GROUP   BY BillNo, kharidarname, gaamname
UNION ALL
SELECT ''Total'', NULL as kharidarname, NULL as gaamname, 
' + @repeatpart + ', SUM(Total_Amt) as [Total Amount]
FROM    item_sale
WHERE [SaleDate] between @StartDate and @EndDate and
CategoryID = @CategoryID'

print @SQL -- test
execute sp_executeSQL @SQL, N'@StartDate datetime, @EndDate datetime, @CategoryID int', @StartDate, @EndDate, @CategoryID

This will produce just 1 extra total line. Note also the minor change I made in the previous message of a solution. (Replaced MAX with SUM for total_amount and qty fields).

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Modifié Naomi NMicrosoft Community Contributor, Moderator mercredi 28 mars 2012 03:36
Marqué comme réponse Kalman TothMicrosoft Community Contributor, Moderator dimanche 1 avril 2012 06:15

mercredi 28 mars 2012 03:50

0

thanks,

but this does not add at the bottom

1000	hrI	paT~apura	^aanuTop 1 lITr	10	3000	NULL	NULL	NULL	NULL	NULL	NULL	rIjNT bayr 100 g/am	20	2400	rIjNT bayr 250 g/am	100	23000	NULL	NULL	NULL	28400
1001	dcdcd 	dcdc d	^aanuTop 1 lITr	90	27000	^aanuTop 250 g/am	10	1000	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	28000
1003	jtaInwa[	paT~apura	NULL	NULL	NULL	NULL	NULL	NULL	Aoma{T 250 g/am ^aanuka	10	2700	NULL	NULL	NULL	rIjNT bayr 250 g/am	10	2300	NULL	NULL	NULL	5000
Total	NULL	NULL	^aanuTop 1 lITr	90	27000	^aanuTop 250 g/am	10	1000	Aoma{T 250 g/am ^aanuka	10	2700	rIjNT bayr 100 g/am	20	2400	rIjNT bayr 250 g/am	100	23000	NULL	NULL	NULL	61400

Above is the output i get. in the first item, there are 2 items of the same name with amt(3000+27000) this should ideally become 30000, but it still shows 27000. can you kindly help on this pls...

rgds

HV

mercredi 28 mars 2012 04:15

Modérateur

0

What is the exact SQL you used? Did you notice the change I made (changed MAX to SUM)?

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

mercredi 28 mars 2012 07:33

0

You will need something like

ORDER BY coalesce(BillNo, 999999999)

at the bottom, to have the sum line to come last.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

mercredi 28 mars 2012 11:18

0

yes i did notice that, im using SQL 2005 which is default with Visual Studio 2008, but somehow i cannot make it.

Im new to all this that is the reason need help in detail pls

rgds

HV

mercredi 28 mars 2012 13:22

0

yes i did notice that, im using SQL 2005 which is default with Visual Studio 2008, but somehow i cannot make it.

The more interesting question is what you have in production. For your development environment, you can always download an install SQL 2008 Express. Or for that matter SQL 2012.

But it's a good idea to use the same version in development as you will use in production.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

mercredi 28 mars 2012 16:55

0

in production also i have the same version sir

HV

mercredi 28 mars 2012 17:33

Modérateur

0

Post your very latest SQL and what is still wrong with it if you need more help. Otherwise please mark correct answers and close the thread.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Marqué comme réponse Hari Vaag mercredi 28 mars 2012 17:41
Non marqué comme réponse Hari Vaag samedi 31 mars 2012 03:10

mercredi 28 mars 2012 21:30

0

in production also i have the same version sir

Good. Then you should keep development to SQL 2005 as well.

Here is a version of my original query which has sums both per colunm and row without using UNION (but it may give you more sums than you want). The HAVING clause is somewhat klunky, but it works.

DECLARE @repeatpart nvarchar(MAX),
@SQL nvarchar(MAX),
@nlmarker char(6)

SELECT @nlmarker = '<CRNL>'

; WITH numbered AS (
   SELECT ItemName,
rn = row_number() OVER(ORDER BY ItemName)
   FROM   (SELECT DISTINCT ItemName FROM item_sale) AS d
)
SELECT @repeatpart =
   (SELECT ' MAX(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
' THEN ItemName END) AS ItemName' + + ltrim(str(rn)) + @nlmarker +
   ' SUM(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
' THEN vechanjatho END) AS Qty' + ltrim(str(rn)) + @nlmarker +
   ' SUM(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
' THEN Total_Amt END) AS Amt' + ltrim(str(rn)) + @nlmarker AS [text()]
FROM   numbered
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')

-- Replace @nlmarker with comma, CR-LF and an indent.
SELECT @repeatpart = replace(@repeatpart, @nlmarker, ',' + char(13) + char(10) + space(7))

-- Compose the final SQL.
SELECT @SQL =
'SELECT BillNo, kharidarname, gaamname,
' + @repeatpart + ' SUM(Total_Amt) AS Total_Amt_BillNo
FROM item_sale
GROUP BY BillNo, kharidarname, gaamname WITH ROLLUP
HAVING grouping(BillNo) = 1 AND grouping(kharidarname) = 1 AND grouping(gaamname) = 1 OR
grouping(BillNo) = 0 AND grouping(kharidarname) = 0 AND grouping(gaamname) = 0
'

print @SQL -- test
execute(@SQL)
go

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Marqué comme réponse Kalman TothMicrosoft Community Contributor, Moderator samedi 31 mars 2012 03:30
Non marqué comme réponse Hari Vaag samedi 31 mars 2012 03:31

samedi 31 mars 2012 03:13

0

hi,

i tried to tweak your help but could not get the desired output, also the thread was getting too long so i moved it to this with more specific details..

The SP first does not give me a correct total....

secondly when i run from my VB.net code it gives me an error as described in the link.

Ive put in the insert statements of the records which are there in my item_sale table

insert into item_sale values('4rad','3/20/2012 13:04','hrI','','paT~apura','j>taunaxk dva','^aanuTop 1 lITr','taru~a','','','0','fvcfv v','10','300','10','3000','1000','cashmemo','0','0','3/20/2012 13:04','hari','3000','sale');
insert into item_sale values('4rad','3/20/2012 13:04','hrI','','paT~apura','j>taunaxk dva','rIjNT bayr 100 g/am','taru~a','','','0','fvcfv v','20','120','20','2400','1000','cashmemo','0','0','3/20/2012 13:05','hari','2400','sale');
insert into item_sale values('4rad','3/20/2012 13:04','hrI','','paT~apura','j>taunaxk dva','rIjNT bayr 250 g/am','taru~a','','','0','fvcfv v','10','230','100','23000','1000','cashmemo','0','0','3/20/2012 13:05','hari','23000','sale');
insert into item_sale values('4rad','3/22/2012 17:13','dcdcd','','dcdc d','j>taunaxk dva','^aanuTop 1 lITr','taru~a','','','0','cfvfvf','10','300','90','27000','1001','cashmemo','0','0','3/22/2012 17:14','hari','27000','sale');
insert into item_sale values('4rad','3/22/2012 17:13','dcdcd','','dcdc d','j>taunaxk dva','^aanuTop 250 g/am','taru~a','','','0','cfvfvf','10','100','10','1000','1001','cashmemo','0','0','3/22/2012 17:14','hari','1000','sale');
insert into item_sale values('4rad','3/23/2012 11:05','dcdc','dcdcdc','dth bb','ra.qatar','yuirya {fko','','','','0','gg gvv','10','291.65','50','14582.5','1002','cashmemo','0','0','3/23/2012 11:05','hari','14582.5','sale');
insert into item_sale values('4rad','3/24/2012 15:49','jtaInwa[','','paT~apura','j>taunaxk dva','rIjNT bayr 250 g/am','taru~a','','','0','fv fv fvv','10','230','10','2300','1003','cashmemo','0','0','3/24/2012 15:50','hari','2300','sale');
insert into item_sale values('4rad','3/24/2012 15:49','jtaInwa[','','paT~apura','j>taunaxk dva','Aoma{T 250 g/am ^aanuka','taru~a','','','0','fv fv fvv','15','270','10','2700','1003','cashmemo','0','0','3/24/2012 15:50','hari','2700','sale');

Kindly help

http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/e2579f51-3c5e-4ac3-a7e0-c97f0429aa71

rgds

HV

samedi 31 mars 2012 03:21

0

hi mate,

i tried your code and it gave me total perfectly. thanks

I need to pass 3 variables

1. Startdate

2. End Date

3. Categoryname

and the result of the SP should be based on the above values passed, can you help me with the tweak of your code pls.

rgds

HV

samedi 31 mars 2012 09:27

2

The SP first does not give me a correct total....

More exactly what is wrong?

secondly when i run from my VB.net code it gives me an error as described in the link.

So there are some errors in the VB code you have in that forum. Before I explain where the errors are, I like to make a statement. What you get here is assistance. That is, we give you an outline for a solution. Sometimes that may be a complete script, but this is very important: at no point we relieve you from the chores of thinking. You cannot just take the code as-is, you need make some effort to understand it. OK, so the XML PATH bit is a bit of mumbo jumbo and you can consider it as such. But the rest you need to have some understanding of. If not, you need to tell your manager or client that you don't have the knowledge for the task and they need to find someone else. After all, you presumably get paid to produce this code - we don't get paid to help you. Which is alright by me as long as you try to learn something from the advice we give you.

OK, so back to the technical bit. In your VB code you have:

cmd.Parameters.Add("@repeatpart", SqlDbType.NVarChar, 100)
cmd.Parameters("@repeatpart").Value = ""
cmd.Parameters.Add("@SQL", SqlDbType.NVarChar, 100)
cmd.Parameters("@SQL").Value = ""
cmd.Parameters.Add("@nlmarker", SqlDbType.Char, 6)
cmd.Parameters("@nlmarker").Value = ""

But @repeatpart, @SQL and @nlmarker are local parameters in your stored procedure, and not parameters.

As I understand the thread in the VB forum, you don't get an error message; you simply do not get any rows back. Your post is that forum is also confusing, because you say you have a stored procedure, but you post a batch of SQL code which is not a stored procedures. Which opens the question of how the parameter list looks like.

In any case, below there is my complete repro script that I have used to test my solutions. It creates the table (where I had to drop the IDENTITY property to get the INSERT to work), it includes your INSERT statements (where I had to drop the last value 'sale') to get them to work. Then comes my query batch, and the end I drop the table (so that I can easily run the script again).

I suggest that you compose a complete repro of the same kind which demonstrates your problems. That is, instead of a batch, you should have your stored procedure and the EXEC statement to call it. Test the script in tempdb (so that you don't mess up your real table) before you post it.

CREATE TABLE [dbo].[item_sale](
   [id] [int] NOT NULL,
   [shakhaname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [tarik] [datetime] NULL,
   [kharidarname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [vaarasnaam] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [gaamname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [itemcategory] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [ItemName] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [utpadaname] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [biyaranvarg] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [paaknaam] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [paakvistar] [float] NULL,
   [banavatlotno] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [vechanvigat] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [vechanbhavperkl] [float] NULL,
   [vechanjatho] [float] NULL,
   [kulkimmat] [float] NULL,
   [BillNo] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [sale_type] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [sale_comm] [float] NULL,
   [rahat_amt] [float] NULL,
   [entry_dt] [datetime] NULL,
   [entry_by] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Total_Amt] [float] NULL,
CONSTRAINT [PK_cashmemo] PRIMARY KEY CLUSTERED
(
   [id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
go
insert into item_sale values('34','4rad','3/20/2012 1:04:29 PM','hrI','','paT~apura','j>taunaxk dva','^aanuTop 1 lITr','taru~a','','','0','fvcfv v','10','300','10','3000','1000','cashmemo','0','0','3/20/2012 1:04:00 PM','hari','3000');
insert into item_sale values('35','4rad','3/20/2012 1:04:29 PM','hrI','','paT~apura','j>taunaxk dva','rIjNT bayr 100 g/am','taru~a','','','0','fvcfv v','20','120','20','2400','1000','cashmemo','0','0','3/20/2012 1:05:00 PM','hari','2400');
insert into item_sale values('36','4rad','3/20/2012 1:04:29 PM','hrI','','paT~apura','j>taunaxk dva','rIjNT bayr 250 g/am','taru~a','','','0','fvcfv v','10','230','100','23000','1000','cashmemo','0','0','3/20/2012 1:05:00 PM','hari','23000');
insert into item_sale values('37','4rad','3/22/2012 5:13:51 PM','dcdcd ','','dcdc d','j>taunaxk dva','^aanuTop 1 lITr','taru~a','','','0','cfvfvf ','10','300','90','27000','1001','cashmemo','0','0','3/22/2012 5:14:00 PM','hari','27000');
insert into item_sale values('38','4rad','3/22/2012 5:13:51 PM','dcdcd ','','dcdc d','j>taunaxk dva','^aanuTop 250 g/am','taru~a','','','0','cfvfvf ','10','100','10','1000','1001','cashmemo','0','0','3/22/2012 5:14:00 PM','hari','1000');
insert into item_sale values('39','4rad','3/23/2012 11:05:35 AM','dcdc','dcdcdc','dth bb','ra.qatar','yuirya {fko ','','','','0','gg gvv','10','291.65','50','14582.5','1002','cashmemo','0','0','3/23/2012 11:05:00 AM','hari','14582.5');
insert into item_sale values('41','4rad','3/24/2012 3:49:21 PM','jtaInwa[','','paT~apura','j>taunaxk dva','rIjNT bayr 250 g/am','taru~a','','','0','fv fv fvv','10','230','10','2300','1003','cashmemo','0','0','3/24/2012 3:50:00 PM','hari','2300');
insert into item_sale values('42','4rad','3/24/2012 3:49:21 PM','jtaInwa[','','paT~apura','j>taunaxk dva','Aoma{T 250 g/am ^aanuka','taru~a','','','0','fv fv fvv','15','270','10','2700','1003','cashmemo','0','0','3/24/2012 3:50:00 PM','hari','2700');
go
SELECT * FROM item_sale ORDER BY BillNo, id
go
DECLARE @repeatpart nvarchar(MAX),
@SQL nvarchar(MAX),
@nlmarker char(6)

SELECT @nlmarker = '<CRNL>'

; WITH numbered AS (
   SELECT ItemName,
rn = row_number() OVER(ORDER BY ItemName)
   FROM   (SELECT DISTINCT ItemName FROM item_sale) AS d
)
SELECT @repeatpart =
   (SELECT ' MAX(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
' THEN ItemName END) AS ItemName' + + ltrim(str(rn)) + @nlmarker +
   ' SUM(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
' THEN vechanjatho END) AS Qty' + ltrim(str(rn)) + @nlmarker +
   ' SUM(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
' THEN Total_Amt END) AS Amt' + ltrim(str(rn)) + @nlmarker AS [text()]
FROM   numbered
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')

-- Replace @nlmarker with comma, CR-LF and an indent.
SELECT @repeatpart = replace(@repeatpart, @nlmarker, ',' + char(13) + char(10) + space(7))

-- Compose the final SQL.
SELECT @SQL =
'SELECT BillNo, kharidarname, gaamname,
' + @repeatpart + ' SUM(Total_Amt) AS Total_Amt_BillNo
FROM item_sale
GROUP BY BillNo, kharidarname, gaamname WITH ROLLUP
HAVING grouping(BillNo) = 1 AND grouping(kharidarname) = 1 AND grouping(gaamname) = 1 OR
grouping(BillNo) = 0 AND grouping(kharidarname) = 0 AND grouping(gaamname) = 0
'

print @SQL -- test
execute(@SQL)
go

drop table item_sale

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Marqué comme réponse Naomi NMicrosoft Community Contributor, Moderator dimanche 1 avril 2012 05:05

samedi 31 mars 2012 16:58

0

hello,

thanks, but im not able to pass the variables as i mentioned above, can you help me tweak the code pls.

rgds

hari

HV

samedi 31 mars 2012 17:17

0

thanks, but im not able to pass the variables as i mentioned above, can you help me tweak the code pls.

Only if you help me to help you. Please see my earlier post of today for I want from you.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

dimanche 1 avril 2012 09:46

0

Hi,
You should go with PIVOT TABLE.

lundi 2 avril 2012 03:38

0

Im not able to understand what details you need from me. Can you kindly tell me what exactly you need please

Coz i need to pass 3 variables in your SP as mentioned below

1. Startdate

2. End Date

3. Categoryname

rgds

HV

lundi 2 avril 2012 03:42

Modérateur

0

Can you please start a new thread on this topic. Post the procedure as you have now and what exactly is still not clear.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

lundi 2 avril 2012 22:25

1

Im not able to understand what details you need from me. Can you kindly tell me what exactly you need please

I feel that we have an uphill battle. I my long post of Saturday, March 31, 2012 9:27 AM, which Naomi marked as an answer, I commented on several things. I also gave a full script that you could run and see the result. I asked for a similar script from you to show what problems you have.

I see you have started a new thread, but I don't have time to look into it tonight. And I will not mark the thread in my newsreader, so it is not likely that I will see it again. (I will see posts in this thread.) I see now that there is a second table which was not in the plot before.

I get an uncanny feeling of a neverending story.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

mercredi 4 avril 2012 05:42

0

no no wait...

Im sorry about it.... there are a few things which im not able to understand coz m new to this...
ill send you my code where i face problems when i get back to my desk....

One more help which i need in your code is how can i make the itemnames as the header of the results, reason is that i save on 1 column for each itemname

rgds

HV

mercredi 4 avril 2012 07:36

0

One more help which i need in your code is how can i make the itemnames as the header of the results, reason is that i save on 1 column for each itemname

That's simple: in

SELECT @repeatpart =
   (SELECT ' MAX(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
' THEN ItemName END) AS ItemName' + + ltrim(str(rn)) + @nlmarker +
   ' SUM(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
' THEN vechanjatho END) AS Qty' + ltrim(str(rn)) + @nlmarker +
   ' SUM(CASE WHEN ItemName = ' + quotename(ItemName, '''') +
' THEN Total_Amt END) AS Amt' + ltrim(str(rn)) + @nlmarker AS [text()]
FROM   numbered
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')

replace

AS ItemName' + ltrim(str(rn))

with

AS ' + quotename(ItemName)

And then you can drop the row_number thing - the only reason it was there because you asked for the colunm headers to be ItemName1, ItemName2 and so on.

It does help if you present all your requirements from the beginning.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

jeudi 5 avril 2012 03:11

0

thanks

but not this way...

The itemname column should not be there, but instead become the heading of qty and amt so it will look like

"itemname qty" and "itemname amt"

rgds

HV

jeudi 5 avril 2012 07:18

0

The itemname column should not be there, but instead become the heading of qty and amt so it will look like

"itemname qty" and "itemname amt"

In a haste I overlooked that part. However, I am sure that you can make that out on your own with some effort.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Proposé comme réponse Naomi NMicrosoft Community Contributor, Moderator jeudi 5 avril 2012 12:45

samedi 7 avril 2012 04:12

0

thanks,

i wud definately take efforts for this, but im new to this. your help will be appreciated

thanks and rgds

HV

samedi 7 avril 2012 09:27

0

i wud definately take efforts for this, but im new to this. your help will be appreciated

And if you only work with solutions we spoonfeed you, you will remain new in this profession for your all life. You have gotten a couple of scripts from us. Pick them apart. Try to understand what is going on. If there is something that is obscure to you, ask.

My ultimate aim hanging out in these forums, is not to give free consulting, least of all to people who are paid to do their jobs. Instead, I hope to be a mentor so that I can help you to be more successful in your job.

Really great moments for me is then I help someone to walk the first mile, and that person then is able to find a good solution to his problem.

So that is the question: are you willing to learn, or do you just want a solution that works, but you don't understand?

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

display rows in columns in SQL

Toutes les réponses

Report Builder 3.0 - Table or Matrix Wizard

Produits

Ressources

Solutions

Mises à jour

Évaluations

Sites connexes

Formation

Certifications

Autres ressources

Support TechNet

Autres liens support