Wednesday, June 10, 2015

Need tutoring help or want to learn to become a professional software developer?

If you are in Little Rock, Jacksonville, or Cabot area and want a tutor to learn how to become a professional software developer go to LittleRockDev .

Learn to code today!  www.littlerockdev.com
Posted by at No comments:

Thursday, November 15, 2012

SSRS, How to avoid sending out a report when the dataset is empty

 

A great reference but I do not think they had this solution was http://blogs.msdn.com/b/bimusings/archive/2005/07/29/445080.aspx

and

http://stackoverflow.com/questions/6498271/send-report-only-when-the-attachment-has-data-in-ssrs

My solution is closer to the second reference but goes a little further.  I created a stored procedure with an extra parameter called @UseErrorAtEnd and if that is given as a 1 then it will error out if the dataset is empty which causes the email to NOT be sent.  In my report I change the parameter for @UseErrorAtEnd to default to =false and I set it to hidden for users that are using the report.  On the subscription I set it to true if I want no email on empty dataset or I set it to false if I want an email on an empty dataset.    


-- =============================================
-- Author:        Larry Bellou
-- Create date: 11/15/2012
-- Description:    
-- =============================================
create PROCEDURE [dbo].[RPT_CreditDebitByStatus]
(
    @StatusID INT,
    @UseErrorAtEnd BIT = 0
)
AS
BEGIN
            SELECT 
                cdm.CREDITDEBITMEMOID, 
                cdm.CREDITDEBITMEMOSTATUS,
                cdm.InvoiceAccount,
                cdm.REASONITEMID,
                Sum(cdl.lineamount) AS 'TotalAmount'
             FROM axdb_live.dbo.PTNCREDITDEBITMEMOTABLE cdm(nolock),
                    axdb_live.dbo.PTNCREDITDEBITMEMOLINE cdl (nolock)
            WHERE 1=1
                AND cdm.CREDITDEBITMEMOID = cdl.CREDITDEBITMEMOID
                AND CREDITDEBITMEMOSTATUS = @StatusID
            GROUP BY  cdm.CREDITDEBITMEMOID, cdm.CREDITDEBITMEMOSTATUS, cdm.CREDITDEBITMEMOTYPE,
                        cdm.InvoiceAccount, cdm.REASONITEMID
END 

IF (@@ROWCOUNT = 0 AND @UseErrorAtEnd = 1)
BEGIN
    RAISERROR('No data', 16, 1)
END

Posted by at No comments:

Friday, November 9, 2012

SQL Server, Get Business Days Function

We have a database for functions so they generic ones can be shared across multiple databases.  This is one I created for business days.

First you need to create a holiday table or non-shipping days table and put your companies dates in it.  I went ahead and put around 10 years of dates.  Most dates are easy to figure out if you go look up the holiday on the web.

Once you have this table you are ready for your function, below is the code..

 


USE [PTNFunctions]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_GetExpectedShipDate]
        Script Date: 11/09/2012 13:49:11 ******/
 
--select ptnfunctions.dbo.fn_GetBusinessDay('11/27/2012', -3)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
alter function [dbo].[fn_GetBusinessDay]
(
    @StartDate DATETIME,
    @NumberOfBusinessDays INT = 1
)
returns DATETIME
AS
BEGIN
DECLARE @BusinessDate DATETIME
DECLARE @BusinessDayCount INT
DECLARE @IncrementalDay INT
 
    SELECT @BusinessDayCount = 0
    SELECT @BusinessDate = @StartDate
 
    IF @NumberOfBusinessDays > 0
    BEGIN
        SELECT @IncrementalDay = 1
    END
    ELSE IF ( @NumberOfBusinessDays < 0 )
    BEGIN
        SELECT @IncrementalDay = -1
    END
 
    SELECT @NumberOfBusinessDays = Abs(@NumberOfBusinessDays)
 
    ----------------------------------------------------
    --add or subtract days to the current date based off number 
    --of business days, may work out this way
    ----------------------------------------------------
    WHILE (@BusinessDayCount < @NumberOfBusinessDays)
    BEGIN
        SELECT @BusinessDate = @BusinessDate + @IncrementalDay
        
        IF(datename(weekday,@BusinessDate) != 'Saturday' 
            AND datename(weekday,@BusinessDate) != 'Sunday')
        BEGIN
            IF(NOT EXISTS(SELECT * FROM 
                            Holidays (nolock) 
                            WHERE HolidayDate=
                                    convert(DATETIME,Convert(nvarchar(20),@BusinessDate,101))))
            BEGIN 
                SELECT @BusinessDayCount = @BusinessDayCount + 1
            END
        END
        
    END
 
return @BusinessDate
end





The power of this is in the use when you need to get back a select statement based off the number of business days like this..


 



SELECT 
    * 
FROM 
    yourTableHere cm (nolock)
WHERE 
    cm.yourFieldHere=3
    AND cm.CREATEDDATE >= ptnfunctions.dbo.fn_GetBusinessDay(cm.CREATEDDATE, 3)


Or you need to use it in the select part to return number of business days for a sql statement, or report..



SELECT 
    ptnfunctions.dbo.fn_GetBusinessDay(cm.CREATEDDATE, 3) AS '3BusinessDays', * 
FROM 
    yourTableHere cm (nolock)
WHERE 
    cm.yourFieldHere = 1
Posted by at No comments:

Dynamics Ax multiselect grid

For reference: http://www.axaptapedia.com/index.php?title=Multiple_grid_selections

I wanted to test this out here are the steps to produce with a twist thrown in for Active on the datasource.

  1. Create a new form, called TestMultiSelect
  2. copy CustTable to the data source
  3. under design,
    1. add a grid,
      1. on grid copy "Invoice" field group to the grid
    2. add a button
      1. on the button select Multiselect = Yes
  4. code for the buttons clicked event see Figure 1
  5. on active code override of the datasource custtable put code for figure 2
  6. That is it, now you have a multiselect button and a way while form is loaded to check things like should the button be lit up or not in the active method.
   1: void clicked()
   2: {
   3:  
   4:     custTable   currentCustTable;
   5:     ;
   6:     super();
   7:  
   8:     for (currentCustTable = custTable_ds.getFirst(true) ?
   9:                                     custTable_ds.getFirst(true) :
  10:                                     custTable_ds.cursor(); currentCustTable; currentCustTable = custTable_ds.getnext())
  11:     {
  12:         info(currentCustTable.InvoiceAccount);
  13:     }
  14:  
  15: }

Figure 1


 



   1: public int active()
   2: {
   3:     int ret;
   4:     CustTable currentCustTable;
   5:  
   6:     ret = super();
   7:     
   8:     for (currentCustTable = custTable_ds.getFirst(true) ?
   9:                                     custTable_ds.getFirst(true) :
  10:                                     custTable_ds.cursor(); currentCustTable; currentCustTable = custTable_ds.getnext())
  11:     {
  12:         info(currentCustTable.InvoiceAccount);
  13:     }
  14:  
  15:  
  16:     return ret;
  17: }

Figure 2
Posted by at No comments:

Friday, May 11, 2012

SQL: Using case in where clause to check conditional column in select list

 

Today I had a colleague ask me how he could use a conditional column in a where clause.  At first I did not think it was possible, but then I started thinking about it and solved the issue by copying the case statement to the where clause.

 

Below is a simple query snippet I came up with to test it, and it worked!  Pretty cool..

   1: SELECT 
   2:     case myKey WHEN 205 THEN 'hello' ELSE 'goodbye' END AS 'testit', * 
   3: FROM 
   4:     myTable 
   5: WHERE 
   6:     case myKey WHEN 205 THEN 'hello' ELSE 'goodbye' END = 'hello'
Posted by at No comments:

Thursday, March 1, 2012

Simple example for Linq predicates (multiple tables)

Everything you need to know is here (and how to download LinqKit.dll for Entity Framework) http://www.albahari.com/nutshell/predicatebuilder.aspx but I thought I would give my simple example and hopefully it clears any confusion up for you.

In LinqPad I did exactly the following:

1) Added a connection to my Entity Framework dll to use in the query

2) clicked f4 in query window

3) Found LinqKit.dll and added it as a reference

4) Checked the “Include PredicateBuilder”

5) Clicked “Additional Namespace Imports”

6) Typed “LinqKit”

7) Clicked “Set as default for new queries”

8) Changes Language to C# Program

9) wrote query below which uses predicates..

   1: void Main()
   2: {
   3:     var prs = SearchProducts("1", "", "", "");
   4:     
   5:     prs.Dump();
   6: }
   7:  
   8: IQueryable<ProductReturn> SearchProducts (string returnNumber, string serial, string accessCard, string trackingNumber)
   9: {
  10:   var predicate = PredicateBuilder.False<ProductReturn>();
  11:   
  12:   if(String.IsNullOrWhiteSpace(returnNumber) && String.IsNullOrWhiteSpace(serial) && String.IsNullOrWhiteSpace(accessCard) && String.IsNullOrWhiteSpace(trackingNumber))
  13:   {
  14:     predicate = PredicateBuilder.True<ProductReturn>();        
  15:   }
  16:   else
  17:   {
  18:  
  19:         if(!String.IsNullOrWhiteSpace(returnNumber)) predicate = predicate.Or(pr1 => pr1.ProductReturnIDFull.Contains(returnNumber));  
  20:         if(!String.IsNullOrWhiteSpace(serial)) predicate = predicate.Or(le1 => le1.LinesExpecteds.Any(m => m.Serial.Contains(serial)));  
  21:         if(!String.IsNullOrWhiteSpace(accessCard)) predicate = predicate.Or(le2 => le2.LinesExpecteds.Any(m => m.AccessCard.Contains(accessCard)));
  22:         if(!String.IsNullOrWhiteSpace(trackingNumber)) predicate = predicate.Or(le3 => le3.LinesExpecteds.Any(m => m.PrepaidLabelTracking.Contains(trackingNumber)));  
  23:     }    
  24:  
  25:   return ProductReturns.AsExpandable().Where (predicate);
  26: }
  27: // Define other methods and classes here

select sql generated in LinqPad 4 below with parameter for returnNumber and serial only.



   1: -- Region Parameters
   2: DECLARE p__linq__0 NVarChar(1) SET p__linq__0 = '%1%'
   3: DECLARE p__linq__1 NVarChar(1) SET p__linq__1 = '%a%'
   4: -- EndRegion
   5: SELECT 
   6: [Extent1].[ProductReturnID] AS [ProductReturnID], 
   7: [Extent1].[ProductReturnIDFull] AS [ProductReturnIDFull], 
   8: [Extent1].[ReturnStatusID] AS [ReturnStatusID], 
   9: [Extent1].[ReturnSystemStatusID] AS [ReturnSystemStatusID], 
  10: [Extent1].[CustID] AS [CustID], 
  11: [Extent1].[ContactName] AS [ContactName], 
  12: [Extent1].[ContactPhone] AS [ContactPhone], 
  13: [Extent1].[ContactEmail] AS [ContactEmail], 
  14: [Extent1].[ShipFromAddress1] AS [ShipFromAddress1], 
  15: [Extent1].[ShipFromAddress2] AS [ShipFromAddress2], 
  16: [Extent1].[ShipFromCity] AS [ShipFromCity], 
  17: [Extent1].[ShipFromState] AS [ShipFromState], 
  18: [Extent1].[ShipFromZipCode] AS [ShipFromZipCode], 
  19: [Extent1].[ReturnWarehouse] AS [ReturnWarehouse], 
  20: [Extent1].[PrepaidLabelCount] AS [PrepaidLabelCount], 
  21: [Extent1].[PrepaidLabelPDF] AS [PrepaidLabelPDF], 
  22: [Extent1].[PalletCountCarrier] AS [PalletCountCarrier], 
  23: [Extent1].[PalletCountTotalReturn] AS [PalletCountTotalReturn], 
  24: [Extent1].[PickUpDate] AS [PickUpDate], 
  25: [Extent1].[PickUpTime] AS [PickUpTime], 
  26: [Extent1].[IsLiftGateRequired] AS [IsLiftGateRequired], 
  27: [Extent1].[IsShrinkWrapRequired] AS [IsShrinkWrapRequired], 
  28: [Extent1].[IsPalletJackRequired] AS [IsPalletJackRequired], 
  29: [Extent1].[IsRestockingFee] AS [IsRestockingFee], 
  30: [Extent1].[RestockingFeeWaivedComment] AS [RestockingFeeWaivedComment], 
  31: [Extent1].[TotalPrepaidWeight] AS [TotalPrepaidWeight], 
  32: [Extent1].[TotalWeight] AS [TotalWeight], 
  33: [Extent1].[UpdateCounter] AS [UpdateCounter], 
  34: [Extent1].[Active] AS [Active], 
  35: [Extent1].[CreateDate] AS [CreateDate], 
  36: [Extent1].[CreatedBy] AS [CreatedBy], 
  37: [Extent1].[ModifiedDate] AS [ModifiedDate], 
  38: [Extent1].[ModifiedBy] AS [ModifiedBy]
  39: FROM [dbo].[ProductReturns] AS [Extent1]
  40: WHERE ([Extent1].[ProductReturnIDFull] LIKE @p__linq__0 ESCAPE N'~') OR ( EXISTS (SELECT 
  41:     1 AS [C1]
  42:     FROM [dbo].[LinesExpected] AS [Extent2]
  43:     WHERE ([Extent1].[ProductReturnID] = [Extent2].[ProductReturnID]) AND ([Extent2].[Serial] LIKE @p__linq__1 ESCAPE N'~')
  44: ))

 



Predicates are pretty sweet!

Posted by at No comments:

Linq with in() the where clause

I recently needed to do a select with a in() clause, so T sql needed to be 

   1: select * from ProductReturns pr inner join LinesExpected le on(pr.ProductReturnID=le.ProductReturnID)
   2: where pr.ProductReturnID in(4, 3, 9, 10, 27)


To accomplish this I used an array and did a “contains” within it.  In C# looks like following



   1: int[] numbers = new int[5] {4, 3, 9, 10, 27};
   2:  
   3:  
   4: var query = from i in ProductReturns
   5:             from c in LinesExpecteds
   6:             where i.ProductReturnID == c.ProductReturnID
   7:             && numbers.Contains(i.ProductReturnID)
   8:             select i;
   9:  
  10: var items = query.ToList();


Resulting SQL is



   1: SELECT 
   2: [Extent1].[ProductReturnID] AS [ProductReturnID], 
   3: [Extent1].[ProductReturnIDFull] AS [ProductReturnIDFull], 
   4: [Extent1].[ReturnStatusID] AS [ReturnStatusID], 
   5: [Extent1].[ReturnSystemStatusID] AS [ReturnSystemStatusID], 
   6: [Extent1].[CustID] AS [CustID], 
   7: [Extent1].[ContactName] AS [ContactName], 
   8: [Extent1].[ContactPhone] AS [ContactPhone], 
   9: [Extent1].[ContactEmail] AS [ContactEmail], 
  10: [Extent1].[ShipFromAddress1] AS [ShipFromAddress1], 
  11: [Extent1].[ShipFromAddress2] AS [ShipFromAddress2], 
  12: [Extent1].[ShipFromCity] AS [ShipFromCity], 
  13: [Extent1].[ShipFromState] AS [ShipFromState], 
  14: [Extent1].[ShipFromZipCode] AS [ShipFromZipCode], 
  15: [Extent1].[ReturnWarehouse] AS [ReturnWarehouse], 
  16: [Extent1].[PrepaidLabelCount] AS [PrepaidLabelCount], 
  17: [Extent1].[PrepaidLabelPDF] AS [PrepaidLabelPDF], 
  18: [Extent1].[PalletCountCarrier] AS [PalletCountCarrier], 
  19: [Extent1].[PalletCountTotalReturn] AS [PalletCountTotalReturn], 
  20: [Extent1].[PickUpDate] AS [PickUpDate], 
  21: [Extent1].[PickUpTime] AS [PickUpTime], 
  22: [Extent1].[IsLiftGateRequired] AS [IsLiftGateRequired], 
  23: [Extent1].[IsShrinkWrapRequired] AS [IsShrinkWrapRequired], 
  24: [Extent1].[IsPalletJackRequired] AS [IsPalletJackRequired], 
  25: [Extent1].[IsRestockingFee] AS [IsRestockingFee], 
  26: [Extent1].[RestockingFeeWaivedComment] AS [RestockingFeeWaivedComment], 
  27: [Extent1].[TotalPrepaidWeight] AS [TotalPrepaidWeight], 
  28: [Extent1].[TotalWeight] AS [TotalWeight], 
  29: [Extent1].[UpdateCounter] AS [UpdateCounter], 
  30: [Extent1].[Active] AS [Active], 
  31: [Extent1].[CreateDate] AS [CreateDate], 
  32: [Extent1].[CreatedBy] AS [CreatedBy], 
  33: [Extent1].[ModifiedDate] AS [ModifiedDate], 
  34: [Extent1].[ModifiedBy] AS [ModifiedBy]
  35: FROM  [dbo].[ProductReturns] AS [Extent1]
  36: INNER JOIN [dbo].[LinesExpected] AS [Extent2] ON [Extent1].[ProductReturnID] = [Extent2].[ProductReturnID]
  37: WHERE [Extent1].[ProductReturnID] IN (4,3,9,10,27)
Posted by at No comments:
Subscribe to: Posts (Atom)