ASP.NET,SQL BASICS

Tuesday, March 18, 2014

Combine Summary & Details Sales Table

USE [WomensWorld]
GO
/****** Object: StoredProcedure [dbo].[GetDailySalesStatementNew] Script Date: 03/18/2014 18:07:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- GetDailySalesStatementNew 'F001','05/16/2013','05/16/2013'
-- GetDailySalesStatementNew 'All','05/16/2012','05/16/2015'
-- =============================================
ALTER PROCEDURE [dbo].[GetDailySalesStatementNew]
(
@ShopID nvarchar(4),
@startDT nvarchar(15),
@endDT nvarchar(15)
)
AS
BEGIN
SET NOCOUNT ON;

SELECT
ROW_NUMBER() OVER (ORDER BY t.[barcode]) as SL
,s.[ShopID]
,s.[Invno]
,s.[invsl]
,s.[TotalAmt]
,s.[DiscPrcnt]
,s.[DiscAmt]
,s.[VatAmt]
,s.[counterid]
,CAST(Convert(nvarchar,s.[saledt],101) as DATE) as saledt
,s.[Disc_Ref]
,s.[Disc_Reasons]
,s.[PayType]
,s.[MAX_DISC]
,s.[CardName]
,s.[advamt]
,s.[cshamt]
,s.[crdamt]
,s.[netamt]
,s.[customer_id]
,s.[Customername]
,t.[userid] as userid
,s.[tsec]
,s.[PaidAmt]
,s.[ChangeAmt]
,s.[CorpID]
,s.[CorpName]
,s.[AptDt]
,s.[AptTime]
,s.[InvType]
,s.[AdvSlip]
,s.[Point]
,s.[PointRedeem]
,s.[RU]
,t.[barcode]
,t.[Prdname]
,t.[sqty]
,t.[Employee_ID]
,t.[Employee_Name]
INTO #Temp_Sale_Summary
FROM [Sale_SSummary] s
RIGHT JOIN [Sale_TokenPrint] t ON s.[invsl] = t.[invsl]
WHERE ((s.[ShopID] = @ShopID AND @ShopID<>'All') OR (@ShopID='All'))
AND CAST(Convert(nvarchar,s.[saledt],101) as DATE) BETWEEN @startDT AND @endDT
ORDER BY s.Invno

DECLARE @invList TABLE
(
SL bigint
)

INSERT INTO @invList (SL)
SELECT MAX(SL) FROM #Temp_Sale_Summary GROUP BY invsl

UPDATE #Temp_Sale_Summary
SET TotalAmt=0,
DiscPrcnt=0,
DiscAmt=0,
VatAmt=0,
advamt=0,
cshamt=0,
crdamt=0,
netamt=0,
PaidAmt=0,
ChangeAmt=0,
Point=0
FROM #Temp_Sale_Summary t LEFT JOIN @invList p ON t.SL = p.SL WHERE p.SL IS NULL

SELECT
s.SL
,s.[ShopID]
,s.[Invno]
,s.[invsl]
,s.[TotalAmt]
,s.[DiscPrcnt]
,s.[DiscAmt]
,s.[VatAmt]
,s.[counterid]
,s.saledt
,s.[Disc_Ref]
,s.[Disc_Reasons]
,s.[PayType]
,s.[MAX_DISC]
,s.[CardName]
,s.[advamt]
,s.[cshamt]
,s.[crdamt]
,s.[netamt]
,s.[customer_id]
,s.[Customername]
,s.userid
,s.[tsec]
,s.[PaidAmt]
,s.[ChangeAmt]
,(s.[PaidAmt] - s.[ChangeAmt]) as RecvAmt
,s.[CorpID]
,s.[CorpName]
,s.[AptDt]
,s.[AptTime]
,s.[InvType]
,s.[AdvSlip]
,s.[Point]
,s.[PointRedeem]
,s.[RU]
,s.[barcode]
,s.[Prdname]
,s.[sqty]
,s.[Employee_ID]
,s.[Employee_Name]
FROM #Temp_Sale_Summary s

DROP TABLE #Temp_Sale_Summary
END

Saturday, February 22, 2014

Combine column from multiple rows into single row

he data looks like this:

id  row_num  customer_code comments
-----------------------------------
1   1        Dilbert        Hard
1   2        Dilbert        Worker
2   1        Wally          Lazy

My results need to look like this:

id  customer_code comments
------------------------------
1   Dilbert        Hard Worker
2   Wally          Lazy

DECLARE @x TABLE 
(
  id INT, 
  row_num INT, 
  customer_code VARCHAR(32), 
  comments VARCHAR(32)
);

INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';

SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments 
    FROM @x AS x2 WHERE id = x.id
     ORDER BY row_num
     FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;

Reference: http://dba.stackexchange.com/questions/17921/combine-column-from-multiple-rows-into-single-row

Monday, September 30, 2013

Little Programming: ASP.NET C# Popup MessageBox

Little Programming: ASP.NET C# Popup MessageBox:

public static void ShowAlertMessage(string error)
        {
            Page page = HttpContext.Current.Handler as Page;
            if (page != null)
            {
                error = error.Replace("'", "\'");

                ScriptManager.RegisterStartupScript(page, page.GetType(), "err_msg", "alert('" + error + "');", true);
            }
        }

Cursor For Generate Bill Month

USE [ShopSMCity]
GO
/****** Object: StoredProcedure [dbo].[SP_Advertising_GetBillMonth] Script Date: 10/01/2013 10:09:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- EXEC SP_Advertising_GetBillMonth @BillNo='201301260006'
-- =============================================
CREATE PROCEDURE [dbo].[SP_Advertising_GetBillMonth]
(
@BillNo nvarchar(30)
)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Declare
@MonthBill nvarchar(15),
@List nvarchar(300),
@ID int

Create table #temp(
ID int identity(1,1),
[Month YYYY] nvarchar(20)
)
insert into #temp([Month YYYY])
SELECT (CASE
When [PaymentType]= 'Pre Paid' then DATENAME(MONTH, BillDate) + ' ' + DATENAME(YEAR, BillDate)
Else DATENAME(MONTH,dateadd(d,-1, BillDate)) + ' ' + DATENAME(YEAR, dateadd(d,-1, BillDate))

END) AS [Month YYYY]
--DATENAME(MONTH, BillDate) + ' ' + DATENAME(YEAR, BillDate) AS [Month YYYY]

from Advertising_BillCreationDetails WHERE BillNo=@BillNo order by BillDate

--select * from #temp

DECLARE cursor_MonthList CURSOR FOR

SELECT distinct [Month YYYY],ID from #temp order by ID

DECLARE @charInx int

OPEN cursor_MonthList
FETCH NEXT FROM cursor_MonthList INTO @MonthBill,@ID
set @List = @MonthBill;
WHILE @@FETCH_STATUS=0
BEGIN

FETCH NEXT FROM cursor_MonthList INTO @MonthBill,@ID

SET @charInx =0

select @charInx= charindex(@MonthBill, @List)

if(@charInx <=0)
BEGIN
set @List += +', '+ @MonthBill;
END
END
CLOSE cursor_MonthList
DEALLOCATE cursor_MonthList

select coalesce(@List,'') as BillMonthList

drop table #temp

END

Query From Different Sales Table

ALTER PROCEDURE [dbo].[SP_DateAndBarcodeWiseSalesDetailsSuppWise]

@supplier nvarchar(100),
@group nvarchar(100),
@product nvarchar(100),
@brand nvarchar(100),

@startDT nvarchar(20),
@endDate nvarchar(20),
@sMonth int,
@sYear int,
@eMonth int,
@eYear int
AS
BEGIN

SET NOCOUNT ON;

declare @m int,
@y int,
@cYear int,
@month int,
@tblname nvarchar(100),
@tblSaleReturn nvarchar(100),
@stMonth int,
@SQL nvarchar(2000),
@retSQL nvarchar(2000)

create table #tmp
(
a int
)

CREATE TABLE #Sale(
[SupID] [nvarchar](4) NULL,
[Qty] [money] NULL,
[sBarCode] [nvarchar](15) NULL,
[BarCode] [nvarchar](18) NULL,
[SQty] [money] NULL,
[Invoice] [nvarchar](30) NULL

)

CREATE TABLE #SaleReturn(
[Invoice] [nvarchar](30) NULL,
[cInvoice] [nvarchar](30) NULL,
[SupID] [nvarchar](4) NULL,
[Qty] [money] NULL,
[sBarCode] [nvarchar](15) NULL,
[BarCode] [nvarchar](18) NULL,
[SQty] [money] NULL,
[rQty] [money] NULL,
[Returned] [nvarchar](1) NULL

)

SET @m = @sMonth
SET @y = @sYear

set @stMonth=@sMonth
while(@sYear<=@eYear)
begin
-- print convert(nvarchar(50),@sYear,101)

if(@stMonth=13)
begin
set @stMonth=1
end

while(@stMonth<=12)
begin
Set @m = 100+@stMonth
set @tblname='Sale'+convert(nvarchar(50),@sYear,101)+SUBSTRING(Cast(@m as nvarchar(4)),2,3)
set @tblSaleReturn = 'SaleReturn' + convert(nvarchar(50),@sYear,101)+SUBSTRING(Cast(@m as nvarchar(4)),2,3)
--print @tblname
--print @tblSaleReturn
declare
@sY nvarchar(50),
@sM nvarchar(50),
@sDT nvarchar(50),
@eDT nvarchar(50),
@foundTbl int,
@returnFound int

set @sY=cast(@sYear as nvarchar(50))
set @sM=cast(@stMonth as nvarchar(50) )
set @sDT=convert(nvarchar(50),@startDT,101)
set @eDT=convert(nvarchar(50),@endDate,101)

set @SQL='insert into #tmp SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE'' AND TABLE_NAME= '''+@tblname+'''and TABLE_SCHEMA=''dbo'''
--print @SQL
exec(@SQL)
set @foundTbl = (select a from #tmp)
delete from #tmp

if(@foundTbl = 1)
begin
print ':)'
print @tblname
print @tblSaleReturn

set @SQL='INSERT INTO #Sale
(
[SupID],
[Qty],
[sBarCode],
[BarCode],
[SQty],
[Invoice]
)
SELECT
s2.[SupID],
s2.[Qty],
s2.[sBarCode],
s2.[BarCode],
s2.[SQty],
s2.[Invoice]

FROM '+@tblname+' s2
inner join StyleSize ss
on ss.Barcode = s2.BarCode
WHERE (SaleDt>='''+@sDT+''' and SaleDt<='''+@eDT+''') and ((s2.SupID='''+@supplier+''' and '''+@supplier+'''<>''All'')or('''+@supplier+'''=''All''))'

--print @SQL

set @retSQL = 'INSERT INTO #SaleReturn
(
[Invoice],
[cInvoice],
[SupID],
[Qty],
[sBarCode],
[BarCode],
[SQty],
[rQty],
[Returned]

)
SELECT
s2.[Invoice],
s2.[cInvoice],
s2.[SupID],
s2.[Qty],
s2.[sBarCode],
s2.[BarCode],
s2.[SQty],
s2.[rQty],
s2.[Returned]

FROM '+@tblSaleReturn+' s2
inner join StyleSize ss
on ss.Barcode = s2.BarCode
WHERE (ReturnedDt>='''+@sDT+''' and ReturnedDt<='''+@eDT+''') and ((s2.SupID='''+@supplier+''' and '''+@supplier+'''<>''All'')or('''+@supplier+'''=''All''))'

--print @retSQL

end
else
begin
set @SQL=''
set @retSQL=''
print ':('
end
print @SQL
print @retSQL
exec(@sql)
exec(@retSQL)

if(@stMonth=@eMonth and @sYear=@eYear)
begin
set @stMonth=13
end

else
begin
set @stMonth=@stMonth+1
end

end

set @sYear=@sYear+1
end
--select SUM(sQty) from #Sale
--select SUM(rQty) from #SaleReturn

SQL Trigger

ALTER TRIGGER [dbo].[TR_Buy_ChangeTransferStatus]
ON [dbo].[Buy]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare
@StockID decimal(18,0),
@Item_id nvarchar(10),
@Transfer nvarchar(1)

Select @StockID=StockID,@Item_id=Item_id,@Transfer=Coalesce(Transfer,'N') From deleted

IF(@Transfer='Y')
BEGIN
UPDATE Buy SET Transfer='N' WHERE StockID=@StockID AND Item_id=@Item_id
END