Get detail of total instead of summary...pls help

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

SQL Server 2008 Forums

Transact-SQL (2008)

Get detail of total instead of summary...pls help

Author

Topic

blocker
Yak Posting Veteran

89 Posts

Posted - 2011-10-24 : 21:53:29

Hi..Good day!

I just got a solution from my last post(http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=167029) to get the balances of all the customers base on credit & payment..The result of the query above looks like below:


LastName              FirstName           Balance
Dela Cruz             Juan                5,000.25
Segundo               Pablo               4,000.65
Oliveros              Riza                3,000.00

etc.

here is the query that generate that result..



select     c.[Last Name], 
           c.[First Name],
           currentbalance = sum(case when t.salestype = 'credit' then t.totalsales
                                     when t.salestype = 'pay'    then -t.totalsales
                                     else 0
                                     end)
from       tbl_costumers c
inner join tbl_pos_sales_summary t on c.[ID No.] = t.costumerid


group by   c.[Last Name], 
           c.[First Name]

having sum(case when t.salestype = 'credit' then t.totalsales
                                     when t.salestype = 'pay'    then -t.totalsales
                                     else 0
                                     end)>0
        
order by   c.[Last Name],
           c.[First Name]

Today my client ask to generate the detail of the total..
This should be the result in format that include date of credit or purchase - assuming the date field is transdate:


LastName        FirstName      TransDate       Credit      Payment       Balance
Dela Cruz       Juan           01/25/2011      2000.25                   2000.25
Dela Cruz       Juan           02/25/2011                  1000.00       1000.25
Dela Cruz       Juan           03/25/2011      4000.00                   5000.25

Segundo         Pablo          01/22/2011      3000.65                   3000.65
Segundo         Pablo          02/22/2011                  1000.00       2000.65
Segundo         Pablo          03/22/2011      3000.00                   4000.65

Oliveros        Riza           01/26/2011      1000.00                   1000.00
Segundo         Pablo          02/26/2011                  500.00         500.00
Segundo         Pablo          03/26/2011      2500.00                   3000.00

...etc...

I just need to modify the query but I cant get it to work..

Thank you for helping.!
[url][/url][url][/url]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-24 : 22:34:30

[code]
select c.[Last Name],
c.[First Name],
TransDate,
credit = case when t.salestype = 'credit' then t.totalsales end,
payment = case when t.salestype = 'pay' then t.totalsales end,
b.balance
from tbl_costumers c
inner join tbl_pos_sales_summary t on c.[ID No.] = t.costumerid
cross apply (
select balance = sum(case when x.salestype = 'credit' then x.totalsales
when x.salestype = 'pay' then -x.totalsales
else 0
end)
from tbl_pos_sales_summary x
where x.costumerid = c.[ID No.]
and x.TransDate <= t.TransDate
) b
order by c.[Last Name],
c.[First Name],
t.TransDate[/code]

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

blocker
Yak Posting Veteran

89 Posts

Posted - 2011-10-24 : 22:49:13

Thank you sir...

Can I filter only those customers which ending balance is greater than zero?

Which should I put the


having ...statement..

Thank you again!...

quote:

Originally posted by khtan


select     c.[Last Name], 
           c.[First Name],
	   TransDate,
	   credit	= case	when t.salestype = 'credit' then t.totalsales  end,
	   payment	= case	when t.salestype = 'pay'    then t.totalsales end,
	   b.balance
from       tbl_costumers c
inner join tbl_pos_sales_summary t on c.[ID No.] = t.costumerid
cross apply (
		select	balance	= sum(case when x.salestype = 'credit' then x.totalsales
                                     	   when x.salestype = 'pay'    then -x.totalsales
                                     	   else 0
                                     	    end)
		from	tbl_pos_sales_summary x
		where	x.costumerid	= c.[ID No.]
		and	x.TransDate	<= t.TransDate
	    ) b
order by   c.[Last Name],
           c.[First Name],
	   t.TransDate

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

Here is the balance that is greater than zero..Thank you for helping..God bless..


           c.[First Name],
	   TransDate,
	   credit	= case	when t.salestype = 'credit' then t.totalsales  end,
	   payment	= case	when t.salestype = 'pay'    then t.totalsales end,
	   b.balance
from       tbl_costumers c
inner join tbl_pos_sales_summary t on c.[ID No.] = t.costumerid
cross apply (
		select	balance	= sum(case when x.salestype = 'credit' then x.totalsales
                                     	   when x.salestype = 'pay'    then -x.totalsales
                                     	   else 0
                                     	    end)
		from	tbl_pos_sales_summary x
		where	x.costumerid	= c.[ID No.]
		and	x.TransDate	<= t.TransDate

having sum(case when x.salestype = 'credit' then x.totalsales
                                     	   when x.salestype = 'pay'    then -x.totalsales
                                     	   else 0
                                     	    end)>0

	    ) b

order by   c.[Last Name],
           c.[First Name],
	   t.TransDate

blocker
Yak Posting Veteran

89 Posts

Posted - 2011-10-24 : 23:34:21

quote:

Originally posted by blocker

Thank you sir...

Can I filter only those customers which ending balance is greater than zero?

Which should I put the


having ...statement..

Thank you again!...

quote:

Originally posted by khtan


select     c.[Last Name], 
           c.[First Name],
	   TransDate,
	   credit	= case	when t.salestype = 'credit' then t.totalsales  end,
	   payment	= case	when t.salestype = 'pay'    then t.totalsales end,
	   b.balance
from       tbl_costumers c
inner join tbl_pos_sales_summary t on c.[ID No.] = t.costumerid
cross apply (
		select	balance	= sum(case when x.salestype = 'credit' then x.totalsales
                                     	   when x.salestype = 'pay'    then -x.totalsales
                                     	   else 0
                                     	    end)
		from	tbl_pos_sales_summary x
		where	x.costumerid	= c.[ID No.]
		and	x.TransDate	<= t.TransDate
	    ) b
order by   c.[Last Name],
           c.[First Name],
	   t.TransDate

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

Here is the balance that is greater than zero..Thank you for helping..God bless..


           c.[First Name],
	   TransDate,
	   credit	= case	when t.salestype = 'credit' then t.totalsales  end,
	   payment	= case	when t.salestype = 'pay'    then t.totalsales end,
	   b.balance
from       tbl_costumers c
inner join tbl_pos_sales_summary t on c.[ID No.] = t.costumerid
cross apply (
		select	balance	= sum(case when x.salestype = 'credit' then x.totalsales
                                     	   when x.salestype = 'pay'    then -x.totalsales
                                     	   else 0
                                     	    end)
		from	tbl_pos_sales_summary x
		where	x.costumerid	= c.[ID No.]
		and	x.TransDate	<= t.TransDate

having sum(case when x.salestype = 'credit' then x.totalsales
                                     	   when x.salestype = 'pay'    then -x.totalsales
                                     	   else 0
                                     	    end)>0

	    ) b

order by   c.[Last Name],
           c.[First Name],
	   t.TransDate

This query produces great result, but is it possible to insert 1 empty row before the next customer records will start?

Thank you!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-25 : 05:14:41

quote:
This query produces great result, but is it possible to insert 1 empty row before the next customer records will start?

Seriously, you shouldn't do this in SQL. This should be done at your front end application

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-25 : 05:26:32

quote:
Can I filter only those customers which ending balance is greater than zero?


select     c.[Last Name], 
           c.[First Name],
	   TransDate,
	   credit	= case	when t.salestype = 'credit' then t.totalsales  end,
	   payment	= case	when t.salestype = 'pay'    then -t.totalsales end,
	   b.balance
from       tbl_costumers c
inner join  (
		select	costumerid
		from	tbl_pos_sales_summary 
		group by costumerid
		having	sum(case when salestype = 'credit' then totalsales
                                 when salestype = 'pay'    then -totalsales
                                 else 0
                                 end) > 0
	    ) d				on c.[ID No.] = d.costumerid
inner join tbl_pos_sales_summary t 	on c.[ID No.] = t.costumerid
cross apply (
		select	balance	= sum(case when x.salestype = 'credit' then x.totalsales
                                     	   when x.salestype = 'pay'    then -x.totalsales
                                     	   else 0
                                     	    end)
		from	tbl_pos_sales_summary x
		where	x.costumerid	= c.[ID No.]
		and	x.TransDate	<= t.TransDate
	    ) b
order by   c.[Last Name],
           c.[First Name],
	   t.TransDate

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

blocker
Yak Posting Veteran

89 Posts

Posted - 2011-10-25 : 21:16:57

quote:

Originally posted by khtan

quote:
Can I filter only those customers which ending balance is greater than zero?


select     c.[Last Name], 
           c.[First Name],
	   TransDate,
	   credit	= case	when t.salestype = 'credit' then t.totalsales  end,
	   payment	= case	when t.salestype = 'pay'    then -t.totalsales end,
	   b.balance
from       tbl_costumers c
inner join  (
		select	costumerid
		from	tbl_pos_sales_summary 
		group by costumerid
		having	sum(case when salestype = 'credit' then totalsales
                                 when salestype = 'pay'    then -totalsales
                                 else 0
                                 end) > 0
	    ) d				on c.[ID No.] = d.costumerid
inner join tbl_pos_sales_summary t 	on c.[ID No.] = t.costumerid
cross apply (
		select	balance	= sum(case when x.salestype = 'credit' then x.totalsales
                                     	   when x.salestype = 'pay'    then -x.totalsales
                                     	   else 0
                                     	    end)
		from	tbl_pos_sales_summary x
		where	x.costumerid	= c.[ID No.]
		and	x.TransDate	<= t.TransDate
	    ) b
order by   c.[Last Name],
           c.[First Name],
	   t.TransDate

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

Thank you sir for the advise!..
Here is a query with a slight modification..But it seems that the balance is not computing correctly..


select     c.[Last Name], 
           c.[First Name],
	   t.salesid,t.transdate,
	   credit	= case	when t.salestype = 'credit' then t.totalsales  end,
	   payment	= case	when t.salestype = 'pay'    then -t.totalsales end,
	   b.balance
from       tbl_costumers c

inner join  (
		select	costumerid
		from	tbl_pos_sales_summary 
		group by costumerid
		
		having	sum(case when salestype = 'credit' then totalsales
                                 when salestype = 'pay'    then -totalsales
                                 else 0
                                 end) > 0
                                 
	    ) d on c.[ID No.] = d.costumerid
	    
inner join tbl_pos_sales_summary t 	on c.[ID No.] = t.costumerid
cross apply (
		select	balance	= sum(case when x.salestype = 'credit' then x.totalsales
                                     	   when x.salestype = 'pay'    then -x.totalsales
                                     	   else 0
                                     	    end)

		from	tbl_pos_sales_summary x
		where	x.costumerid	= c.[ID No.]
		and	x.transdate	<= t.transdate
	    ) b

order by   c.[Last Name],
           c.[First Name],
	   t.salesid

And the result is below:


Last Name	First Name   salesid	transdate	credit	 payment	 balance
Abdul	        Ramonito 	13	01/19/11	920	 NULL	         920
Abdul	        Ramonito 	33	01/27/11	1809.5	 NULL	         2789.5
Abdul	        Ramonito 	68	01/13/11	NULL	 NULL	         0
Abdul	        Ramonito 	133	01/27/11	60	 NULL	         2789.5

There is an error in the balance on line 2 of the result..As well as, Is it possible to remove those two NULL values of credit & payment column @ line 3? I dont know why it happens as well as those NULL to be replace with "-" sign for presentation purposes.

The result must be:


Last Name	First Name   salesid	transdate	credit	 payment	 balance
Abdul	        Ramonito 	13	01/19/11	920	   -	         920
Abdul	        Ramonito 	33	01/27/11	1809.5	   -	         2729.5
Abdul	        Ramonito 	133	01/27/11	60	   -	         2789.5

Thank you for helping.God bless!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-26 : 02:13:24

quote:
There is an error in the balance on line 2 of the result..

The query didn't handle the situation where you have 2 transactions on the same day. (01/27/11). See changes in red

quote:
Is it possible to remove those two NULL values of credit & payment column @ line 3?

Yes. That's is because you have a salestype that is not CREDIT or PAY. If that salestype should be exclude in your result and calculation, just add the condition in the WHERE clause. See changes in blue


select     c.[Last Name], 
           c.[First Name],
	   t.salesid,
	   t.TransDate,
	   credit	= case	when t.salestype = 'credit' then t.totalsales  end,
	   payment	= case	when t.salestype = 'pay'    then -t.totalsales end,
	   b.balance
from       tbl_costumers c
inner join  (
		select	costumerid
		from	tbl_pos_sales_summary 
		group by costumerid
		having	sum(case when salestype = 'credit' then totalsales
                                 when salestype = 'pay'    then -totalsales
                                 else 0
                                 end) > 0
	    ) d				on c.[ID No.] = d.costumerid
inner join tbl_pos_sales_summary t 	on c.[ID No.] = t.costumerid
cross apply (
		select	balance	= sum(case when x.salestype = 'credit' then x.totalsales
                                     	   when x.salestype = 'pay'    then -x.totalsales
                                     	   else 0
                                     	    end)
		from	tbl_pos_sales_summary x
		where	x.costumerid	= c.[ID No.]
		and	x.TransDate	<= t.TransDate
		and	x.salesid	<=  t.salesid
	    ) b
where	t.salestype	in ('credit', 'pay')
order by   c.[Last Name],
           c.[First Name],
	   t.TransDate,
           t.salesid

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

blocker
Yak Posting Veteran

89 Posts

Posted - 2011-10-27 : 22:01:02

quote:

Originally posted by khtan

quote:
There is an error in the balance on line 2 of the result..

The query didn't handle the situation where you have 2 transactions on the same day. (01/27/11). See changes in red

quote:
Is it possible to remove those two NULL values of credit & payment column @ line 3?


select     c.[Last Name], 
           c.[First Name],
	   t.salesid,
	   t.TransDate,
	   credit	= case	when t.salestype = 'credit' then t.totalsales  end,
	   payment	= case	when t.salestype = 'pay'    then -t.totalsales end,
	   b.balance
from       tbl_costumers c
inner join  (
		select	costumerid
		from	tbl_pos_sales_summary 
		group by costumerid
		having	sum(case when salestype = 'credit' then totalsales
                                 when salestype = 'pay'    then -totalsales
                                 else 0
                                 end) > 0
	    ) d				on c.[ID No.] = d.costumerid
inner join tbl_pos_sales_summary t 	on c.[ID No.] = t.costumerid
cross apply (
		select	balance	= sum(case when x.salestype = 'credit' then x.totalsales
                                     	   when x.salestype = 'pay'    then -x.totalsales
                                     	   else 0
                                     	    end)
		from	tbl_pos_sales_summary x
		where	x.costumerid	= c.[ID No.]
		and	x.TransDate	<= t.TransDate
		and	x.salesid	<=  t.salesid
	    ) b
where	t.salestype	in ('credit', 'pay')
order by   c.[Last Name],
           c.[First Name],
	   t.TransDate,
           t.salesid

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

I'm sorry sir for late reply..But it seems that the balance is still got an error:
Here is the result of the query:


Last Name First Name	salesid	transdate	 credit 	 payment 	 balance 
Abdul	  Ramonito 	13	01/19/11	 920.00 	 NULL 	         920.00 
Abdul	  Ramonito 	33	01/27/11	 1,809.50 	 NULL 	         2,729.50 
Abdul	  Ramonito 	133	01/27/11	 60.00 	         NULL 	         2,789.50 
Abdul	  Ramonito 	136	02/15/11	 828.50 	 NULL 	         3,618.00 
Abdul	  Ramonito 	194	02/25/11	 635.25 	 NULL 	         4,253.25 
Abdul	  Ramonito 	1307	02/28/11	 NULL 	        (2,729.50)	 1,523.75 
Abdul	  Ramonito 	297	03/11/11	 2,382.75 	 NULL 	         6,636.00 
Abdul	  Ramonito 	2065	03/12/11	 NULL 	        (1,523.75)	 2,382.75 
Abdul	  Ramonito 	413	03/30/11	 1,761.00 	 NULL 	         8,397.00 
Abdul	  Ramonito 	453	04/04/11	 1,106.25 	 NULL 	         9,503.25 
Abdul	  Ramonito 	2296	04/13/11	 NULL 	        (2,327.72)	 2,922.28

Balance error on line 7,8,9,11 .
This also happens to other customer records...Hope to fix this..I repeatedly changed the query but still incorrect..

Thank you for helping and your time..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-28 : 00:31:33

try


; with 
pos_sales_summary as
(
	select	*, rn	= row_number() over (partition by costumerid order by TransDate)
	from	tbl_pos_sales_summary
)
select     c.[Last Name], 
           c.[First Name],
	   t.salesid,
	   t.TransDate,
	   credit	= case	when t.salestype = 'credit' then t.totalsales  end,
	   payment	= case	when t.salestype = 'pay'    then -t.totalsales end,
	   b.balance
from       tbl_costumers c
inner join  (
		select	costumerid
		from	pos_sales_summary 
		group by costumerid
		having	sum(case when salestype = 'credit' then totalsales
                                 when salestype = 'pay'    then -totalsales
                                 else 0
                                 end) > 0
	    ) d				on c.[ID No.] = d.costumerid
inner join pos_sales_summary t 	on c.[ID No.] = t.costumerid
cross apply (
		select	balance	= sum(case when x.salestype = 'credit' then x.totalsales
                                     	   when x.salestype = 'pay'    then -x.totalsales
                                     	   else 0
                                     	    end)
		from	pos_sales_summary x
		where	x.costumerid	= c.[ID No.]
		and	x.TransDate	<= t.TransDate
		and	x.rn		<= t.rn
	    ) b
where	t.salestype	in ('credit', 'pay')
order by   c.[Last Name],
           c.[First Name],
	   t.TransDate,
           t.salesid

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

blocker
Yak Posting Veteran

89 Posts

Posted - 2011-10-31 : 02:23:55

quote:

Originally posted by khtan

try


; with 
pos_sales_summary as
(
	select	*, rn	= row_number() over (partition by costumerid order by TransDate)
	from	tbl_pos_sales_summary
)
select     c.[Last Name], 
           c.[First Name],
	   t.salesid,
	   t.TransDate,
	   credit	= case	when t.salestype = 'credit' then t.totalsales  end,
	   payment	= case	when t.salestype = 'pay'    then -t.totalsales end,
	   b.balance
from       tbl_costumers c
inner join  (
		select	costumerid
		from	pos_sales_summary 
		group by costumerid
		having	sum(case when salestype = 'credit' then totalsales
                                 when salestype = 'pay'    then -totalsales
                                 else 0
                                 end) > 0
	    ) d				on c.[ID No.] = d.costumerid
inner join tbl_pos_sales_summary t 	on c.[ID No.] = t.costumerid
cross apply (
		select	balance	= sum(case when x.salestype = 'credit' then x.totalsales
                                     	   when x.salestype = 'pay'    then -x.totalsales
                                     	   else 0
                                     	    end)
		from	pos_sales_summary x
		where	x.costumerid	= c.[ID No.]
		and	x.TransDate	<= t.TransDate
		and	x.rn		<= t.rn
	    ) b
where	t.salestype	in ('credit', 'pay')
order by   c.[Last Name],
           c.[First Name],
	   t.TransDate,
           t.salesid

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

Sorry sir for late reply..
But there is an error in the query..here is it.



Msg 207, Level 16, State 1, Line 39
Invalid column name 'rn'.

but when i replaced the 'rn' variable to something like this[in color blue] it will work but still some of the balances got an error. But at last it is almost completely correct:


; with 
pos_sales_summary as
(
	select	*, rn	= row_number() over (partition by costumerid order by TransDate)
	from	tbl_pos_sales_summary
)
select     c.[Last Name], 
           c.[First Name],
	   t.salesid,
	   t.TransDate,
	   credit	= case	when t.salestype = 'credit' then t.totalsales  end,
	   payment	= case	when t.salestype = 'pay'    then -t.totalsales end,
	   b.balance
from       tbl_costumers c
inner join  (
		select	costumerid
		from	pos_sales_summary 
		group by costumerid
		having	sum(case when salestype = 'credit' then totalsales
                                 when salestype = 'pay'    then -totalsales
                                 else 0
                                 end) > 0
	    ) d				on c.[ID No.] = d.costumerid
inner join tbl_pos_sales_summary t 	on c.[ID No.] = t.costumerid
cross apply (
		select	balance	= sum(case when x.salestype = 'credit' then x.totalsales
                                     	   when x.salestype = 'pay'    then -x.totalsales
                                     	   else 0
                                     	    end)
		from	pos_sales_summary x
		where	x.costumerid	= c.[ID No.]
		and	x.TransDate	<= t.TransDate
		and	x.rn		<= t.salesid
	    ) b
where	t.salestype	in ('credit', 'pay')
order by   c.[Last Name],
           c.[First Name],
	   t.TransDate,
           t.salesid

Here is the result of the query.


Last Name	First Name	salesid	transdate	credit	  payment	 balance 
Abdul	        Ramonito 	13	01/19/11	920	  NULL	         920.00 
Abdul	        Ramonito 	33	01/27/11	1809.5	  NULL	         2,789.50 
Abdul	        Ramonito 	133	01/27/11	60	  NULL	         2,789.50 
Abdul	        Ramonito 	136	02/15/11	828.5	  NULL	         3,618.00 
Abdul	        Ramonito 	194	02/25/11	635.25	  NULL	         4,253.25 
Abdul	        Ramonito 	297	03/11/11	2382.75	  NULL	         3,906.50 
Abdul	        Ramonito 	413	03/30/11	1761	  NULL	         4,143.75 
Abdul	        Ramonito 	453	04/04/11	1106.25	  NULL	         5,250.00 
Abdul	        Ramonito 	505	04/14/11	1079	  NULL	         2,421.98 
Abdul	        Ramonito 	677	05/13/11	2462.05	  NULL	         2,462.05 
Abdul	        Ramonito 	951	06/14/11	2622.5	  NULL	         2,622.50 
Abdul	        Ramonito 	1072	06/28/11	1942.5	  NULL	         4,565.00 
Abdul	        Ramonito 	1249	07/14/11	3545.97	  NULL	         4,617.32 
Abdul	        Ramonito 	1307	02/28/11	NULL	  -2729.5	 1,523.75 
Abdul	        Ramonito 	1341	07/22/11	321.5	  NULL	         4,938.82 
Abdul	        Ramonito 	1605	08/12/11	2278.65	  NULL	         7,217.47 
Abdul	        Ramonito 	1793	08/28/11	3345.25	  NULL	         10,562.72 
Abdul	        Ramonito 	2004	09/14/11	100	  NULL	         7,285.07 
Abdul	        Ramonito 	2065	03/12/11	NULL	  -1523.75	 2,382.75 
Abdul	        Ramonito 	2092	09/21/11	3591.15	  NULL	         5,904.73 
Abdul	        Ramonito 	2183	09/28/11	2167.75	  NULL	         8,072.48 
Abdul	        Ramonito 	2222	09/29/11	45	  NULL	         6,617.48 
Abdul	        Ramonito 	2293	04/14/11	NULL	  -79	         2,421.98 
Abdul	        Ramonito 	2296	04/13/11	NULL	  -2327.72	 1,421.98 
Abdul	        Ramonito 	2297	04/13/11	NULL	  -1500.3	 1,421.98 
Abdul	        Ramonito 	2354	10/08/11	735	  NULL	         7,352.48 
Abdul	        Ramonito 	2560	10/25/11	1291.1	  NULL	         8,352.48 
Abdul	        Ramonito 	2692	04/30/11	NULL	  -672.28	 -   
Abdul	        Ramonito 	3013	04/30/11	NULL	  -1749.7	 -   
Abdul	        Ramonito 	4202	05/31/11	NULL	  -2462.05	 -   
Abdul	        Ramonito 	4673	07/13/11	NULL	  -3493.65	 1,071.35 
Abdul	        Ramonito 	6493	09/04/11	NULL	  -3377.65	 7,185.07 
Abdul	        Ramonito 	7255	09/18/11	NULL	  -4971.49	 2,313.58 
Abdul	        Ramonito 	7536	09/29/11	NULL	  -1500	         6,617.48 
Abdul	        Ramonito 	8205	10/25/11	NULL	  -291.1	 8,352.48

balance error in line 2,6,7 and so on.

Thank you for helping..

TRANSOFTWARE

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-31 : 03:02:27

my mistake. change this line


inner join tbl_pos_sales_summary pos_sales_summary t 	on c.[ID No.] = t.costumerid

also edited the original post to reflect the changes

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

blocker
Yak Posting Veteran

89 Posts

Posted - 2011-11-02 : 21:37:47

quote:
Originally posted by khtan

my mistake. change this line

inner join ~~tbl_pos_sales_summary~~ pos_sales_summary t on c.[ID No.] = t.costumerid

also edited the original post to reflect the changes

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

Sorry sir for late reply..
But it finally works..I just want to know on how it was done by the query..I mean the whole thing. Especially this part


; with 
pos_sales_summary as
(
	select	*, rn	= row_number() over (partition by costumerid order by TransDate)
	from	tbl_pos_sales_summary
)

And also this part...


cross apply (
		select	balance	= sum(case when x.salestype = 'credit' then x.totalsales
                                     	   when x.salestype = 'pay'    then -x.totalsales
                                     	   else 0
                                     	    end)

		from	tbl_pos_sales_summary x
		where	x.costumerid	= c.[ID No.]
		and	x.transdate	<= t.transdate
	    ) b

Thank you! & God bless!

TRANSOFTWARE

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-02 : 21:58:16

quote:

; with 
pos_sales_summary as
(
	select	*, rn	= row_number() over (partition by costumerid order by TransDate)
	from	tbl_pos_sales_summary
)

This is Common Table Expression(CTE). CTE is like derived table. One difference is CTE can be reference more than once. (The query reference it twice). The main purpose of the part of query is because it required a running no (rn) that is order by TransDate for each customerid . The rn is required in the CROSS APPLY query to find the cumulative balance

quote:

cross apply (
		select	balance	= sum(case when x.salestype = 'credit' then x.totalsales
                                     	   when x.salestype = 'pay'    then -x.totalsales
                                     	   else 0
                                     	    end)
		from	pos_sales_summary x
		where	x.costumerid	= c.[ID No.]
		and	x.TransDate	<= t.TransDate
		and	x.rn		<= t.rn
	    ) b

This part of the query is to find the cumulative balance. It calculate the balance for each customerid and sum up the totalsales ( + credit / - pay) up to the TransDate. As you might have more than one transaction for the same TransDate, it can't based on TransDate alone else you might have earlier issue where the cumulative balance is not calculated correctly. To overcome this a unique running number (by customer) is required. This is done by rn from the CTE. So the CROSS APPLY query will perform the summation until the rn.

Note : this "x.TransDate <= t.TransDate" in the CROSS APPLY query is redundant and can be removed.

Basically what you required here with the balance is normally refer to as running total. And can be easily done at the front end side.

Also refer to here on the APPLY operator

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

blocker
Yak Posting Veteran

89 Posts

Posted - 2011-11-20 : 19:35:59

quote:
Originally posted by khtan

quote:
; with
pos_sales_summary as
(
select *, rn = row_number() over (partition by costumerid order by TransDate)
from tbl_pos_sales_summary
)

This is Common Table Expression(CTE). CTE is like derived table. One difference is CTE can be reference more than once. (The query reference it twice). The main purpose of the part of query is because it required a running no (rn) that is order by TransDate for each customerid . The rn is required in the CROSS APPLY query to find the cumulative balance
quote:
cross apply (
select balance = sum(case when x.salestype = 'credit' then x.totalsales
when x.salestype = 'pay' then -x.totalsales
else 0
end)
from pos_sales_summary x
where x.costumerid = c.[ID No.]
~~and x.TransDate <= t.TransDate~~
and x.rn <= t.rn
) b

This part of the query is to find the cumulative balance. It calculate the balance for each customerid and sum up the totalsales ( + credit / - pay) up to the TransDate. As you might have more than one transaction for the same TransDate, it can't based on TransDate alone else you might have earlier issue where the cumulative balance is not calculated correctly. To overcome this a unique running number (by customer) is required. This is done by rn from the CTE. So the CROSS APPLY query will perform the summation until the rn.

Note : this "x.TransDate <= t.TransDate" in the CROSS APPLY query is redundant and can be removed.

Basically what you required here with the balance is normally refer to as running total. And can be easily done at the front end side.

Also refer to here on the APPLY operator

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

Sorry sir for late reply..But thank you very much for the explanation..
This helps me a lot....God bless..

TRANSOFTWARE

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-21 : 00:51:03

you are welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources