Wednesday, May 23, 2012

SQL Code - Is Weekend - user defined function

SQL Code - Is Weekend - user defined function

I need to know - "Is this day a weekend?" This function is used to determine if a date is a Saturday or Sunday then 1 else 0.

/****** Object:  UserDefinedFunction [dbo].[SA_weekend]    Script Date: 05/23/2012 11:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[SA_weekend]
(
 @Date datetime
)
RETURNS int
AS
 /********************************************************************************************
 Description:  This function is used to determine if a date is a weekend then 1 else 0. (Saturday or Sunday)

 dbo.[SA_weekend]
 ********************************************************************************************/
 Begin
 Declare @IsWeekend int
 Select @IsWeekend = 0

 Declare @DayofWeek varchar(10)
 --SET DATEFIRST 7;
 SET @DayofWeek = (SELECT
        case when DATEPART(dw, @Date) = 1 then 'Sunday'
        when DATEPART(dw, @Date) = 2 then 'Monday'
        when DATEPART(dw, @Date) = 3 then 'Tuesday'
        when DATEPART(dw, @Date) = 4 then 'Wednesday'
        when DATEPART(dw, @Date) = 5 then 'Thursday'
        when DATEPART(dw, @Date) = 6 then 'Friday'
        when DATEPART(dw, @Date) = 7 then 'Saturday'
        end)

 If @DayofWeek = 'Sunday'
 begin
  Set @IsWeekend = 1
 end
 else if @DayofWeek = 'Saturday'
 begin
  Set @IsWeekend = 1
 end

 Return @IsWeekend
  End

GO

To test this script -

--testing dates for weekends
declare @StartDate datetime
--set @StartDate = '5/25/2012'
--set @StartDate = '5/26/2012'
--set @StartDate = '5/27/2012'
set @StartDate = '5/27/2011'
--set @StartDate = '5/28/2011'
--set @StartDate = '5/29/2011'

select dbo.SA_weekend(@StartDate)

SQL Holiday Dates user defined function

SQL Code Description:  This user defined function is used to determine if a date is a holiday then 1 else 0.

/****** Object:  UserDefinedFunction [dbo].[SA_holidayDates]    Script Date: 05/23/2012 10:24:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[SA_holidayDates]
(
 @Date datetime
)
RETURNS int
AS
 /********************************************************************************************
 Description:  This function is used to determine if a date is a holiday then 1 else 0.
 Holidays are Christmas, New Year's Day, July 4th, Memorial Day (last Monday of May) and Labor Day (first Monday of September)

 dbo.SA_holidayDates
 ********************************************************************************************/
  begin
 Declare @IsHoliday int
 Select @IsHoliday = 0
 if @Date = (select CAST('12/25/' + convert(varchar(4),(datepart(yyyy,@Date))) as DATE))
  or @Date = (select CAST('1/1/' + convert(varchar(4),(datepart(yyyy,@Date))) as DATE))
  or @Date  = (select CAST('7/4/' + convert(varchar(4),(datepart(yyyy,@Date))) as DATE))
  or @Date = (select DATEADD(wk, DATEDIFF(wk,0,CAST('5/31/' + convert(varchar(4),(datepart(yyyy,@Date))) as DATE)), 0))
  or @Date = (select DATEADD(wk, DATEDIFF(wk,0,CAST('9/1/' + convert(varchar(4),(datepart(yyyy,@Date))) as DATE))+1, 0) )
 
  Set @IsHoliday = 1
 
--select @IsHoliday
 Return @IsHoliday
  End

GO

To test your data - use this testing query -
--testing dates for holidays
declare @StartDate datetime
--set @StartDate = '5/28/2012'
--set @StartDate = '9/3/2012'
--set @StartDate = '1/1/2012'
--set @StartDate = '12/25/2012'
--set @StartDate = '7/4/2012'
set @StartDate = '5/30/2011'
--set @StartDate = '9/5/2011'
--set @StartDate = '1/1/2011'
--set @StartDate = '12/25/2011'
--set @StartDate = '7/4/2011'

select dbo.SA_holidayDates(@StartDate)

--compliments of Ann Roth

Friday, April 27, 2012

Error: having an incorrect namespace

Good afternoon,
I am at work - I am a business analyst.  Today, in SQL Server Reporting Services, I came across an issue that you might also have.  If a report is opened and saved in SSRS 2010 - an error occurs when trying to re-open it in SSRS 2008.  The report will only show the HTML code when opened in VS2008 - Solution Explorer.

The following steps will fix this issue:

1.  I was trying to open an SSRS report in VS 2008 that had been built on a laptop that was using VS2010.  The error message stated something about having an incorrect namespace - and only the HTML showed:
The following line was the clue to the issue - xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition"  
Original line in the xml code:
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">

I copied the following code from a report that was working – into the xml -
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

And replaced the code in my report – one line as above.

2.  The next error that appeared was that there was no object <Report Sections>

I went through the xml version of the report and deleted the two tags – and NOT the code inside – leaving the Body as is:
<Report Sections>
<Report Section>


</Report Section>
</Report Sections>

And it worked!  The report opened normally in the SSRS 2008 interface.

Tuesday, February 28, 2012

Steps to Setting Up the Netduino


Steps to Setup the Netduino


Wiring the LCD Display–


Figure 6 - Tape the pins into place onto the LCD display
Figure 7 - Solder wires to the LCD display.


Hook up the cables as follows -

LCD Pin Number
Symbol
Netduino Pin
Description
1
VSS
GND
Ground
2
VDD
5V
Logic voltage
3
V0
10K Pot Leg 2
op voltage for LCD. Controls color/contrast
4
RS
D12
Register selector
5
R/W
GND
Low means write, so you can just ground it
6
E
D11
Chip enable
7
DB0
D9
Data Bit 0
8
DB1
D8
Data Bit 1
9
DB2
D7
Data Bit 2
10
DB3
D6
Data Bit 3
11
DB4
D5
Data Bit 4
12
DB5
D4
Data Bit 5
13
DB6
D3
Data Bit 6
14
DB7
D2
Data Bit 7
15
LED(+)
Resistor to 5V
Backlight LED Anode
16
LED(-)
Ground
Backlight LED Cathode
Figure 8 - Begin to Attach the Netduino to the Breadboard with Cables




Code


Classes created by Secret Labs for the Netduino –

Class
Namespace
Assembly
AnalogInput
SecretLabs.NETMF.Hardware
SecretLabs.NETMF.Hardware.dll
Pins
SecretLabs.NETMF.Hardware.Netduino-Plus
SecretLabs.NETMF.Hardware.Netduino-Plus.dll



Classes created by the author of Getting Started with the Internet of Things – Cuno Pfister

Class
Namespace
Assembly
Buffer
Gsiot.Server
Gsiot.Server.dll
CSharpRepresentation
Gsiot.Server
Gsiot.Server.dll
DigitalActuator
Gsiot.Server
Gsiot.Server.dll
Digital Sensor
Gsiot.Server
Gsiot.Server.dll
Http Server
Gsiot.Server
Gsiot.Server.dll
Manipulated Variable
Gsiot.Server
Gsiot.Server.dll
Measured Variable
Gsiot.Server
Gsiot.Server.dll
PachubeClient
Gsiot.PachubeClient
Gsiot.PachubeClient.dll
RequestHandler Context
Gsiot.Server
Gsiot.Server.dll




Gsiot.Server project


The Gsiot.Server project encapsulates some of the web server functionality needed to interpret and respond to web requests. The project as it comes from the book needs to be modified and re-compiled in order to handle and relay requests to the LCD.

File
Notes
Code
Representations.cs
Add this function to allow the handling of string variables in the put method.
 public static bool TryDeserializeString(RequestHandlerContext context,
     out object content) {
     if (context.RequestContent != null) {
                content = context.RequestContent;
                return true;
            }
            content = null;
            return false;
        }





Other changes in the Server.cs file have been made to expose network settings assigned by the DHCP server in your router to the program that will be using this DLL. Please see the code.

public string DHCP { get; set; }

        public string MAC { get; internal set; }

        public string IPAddress { get; internal set; }

        public string GWAddress { get; internal set; }

        public string DNS1Address { get; internal set; }

        public string BaseURI { get; internal set; }



Then further down in the code -       
void NetworkConfiguration()

        {

            NetworkInterface[] nis =

                NetworkInterface.GetAllNetworkInterfaces();

            // we assume that a network interface is available

            Contract.Assert(nis[0] != null);



            var x = new char[17];

            var i = 0;

            byte[] a = nis[0].PhysicalAddress;

            const string hex = "0123456789ABCDEF";

            for (var j = 0; j != 6; j = j + 1)

            {

                x[i] = (char)(hex[(a[j] & 0xF0) >> 4]);

                i = i + 1;

                x[i] = (char)(hex[a[j] & 0x0F]);

                i = i + 1;

                if (i != 17)

                {

                    x[i] = '-';

                    i = i + 1;

                }

            }

            Debug.Print("DHCP enabled: " + nis[0].IsDhcpEnabled);

            if (nis[0].IsDhcpEnabled) { DHCP = "true"; } else { DHCP = "false"; }

            string mac = new string(x);

            Debug.Print("MAC address: " + mac);

            MAC = mac;

            string localHost = nis[0].IPAddress;

            Debug.Print("Device address: " + localHost);

            IPAddress = localHost;

            Debug.Print("Gateway address: " + nis[0].GatewayAddress);

            GWAddress = nis[0].GatewayAddress;

            if (nis[0].DnsAddresses.Length > 0)

            {

                Debug.Print("Primary DNS address: " + nis[0].DnsAddresses[0]);

                DNS1Address = nis[0].DnsAddresses[0];

            }

            string uri = "http://";

            if (RelayDomain != null)

            {

                uri = uri + RelayHost;

            }

            else

            {

                uri = uri + localHost;

            }

            if (Port != 80)

            {

                uri = uri + ":" + Port;

            }

            if (RelayDomain != null)

            {

                uri = uri + "/" + RelayDomain;

            }       serviceRoot = uri;     Debug.Print("Base Uri: " + uri + "/");            BaseURI = serviceRoot;

        }



After making these changes compile the Gsiot.Server in Release mode.

The newly compiled DLL Gsiot.Server.dll will require the be and the le directories whenever you include it in your project.  The new project name is NetworkLCD -

NetworkLCD project


The NetworkLCD project handles 3 main functions:

-          Listen for incoming web requests using a multithreaded instance of the Gsiot.Server.

-          Initialize and write text to the LCD display.

-          Initialize and blink the on-board LED at a given rate.
Figure 9  - Hello World! And the Blinking LED

Code from the Program.cs in the NetworkLCD program -

/* --Code from Program.cs in the NetworkLCD program--------------------

             * SET UP LCD

             * --------------------- */

            var lcdThread = new Thread(lcd.Run);

            lcdThread.Start();

            print(bufferStr,"start LCD");



            /* ---------------------

             * SET UP LED

             * --------------------- */

            var blinkerThread = new Thread(blinker.Run);

            blinkerThread.Start();

            print(bufferStr, "start LED");



            /* ---------------------

             * Start Server

             * --------------------- */

            webServer.Open();

            print(bufferStr,"Server " + webServer.BaseURI);

            baseuri = webServer.BaseURI;

            webServer.Run();

        }



        /* ---------------------

         * put txt message in

         * buffer for LCD thread

         * to pick up and display

         * --------------------- */

        static void print(Buffer buffer, string msg) {

            buffer.HandlePut(msg);

        }



        /* ---------------------

         * return a "file" to the

         * web client with instructions

         * on which put methods to

         * send us

         * --------------------- */

        static void HandleBlinkTargetHtml(RequestHandlerContext context) {

            string requestUri = context.BuildRequestUri("/blinkingPeriod/target");

            string requestUri2 = context.BuildRequestUri("/lcdMessage/target");

            var script =

                @"<html>

                <head>

                  <script type=""text/javascript"">

                    var r;

                    try {

                      r = new XMLHttpRequest();

                    } catch (e) {

                      r = new ActiveXObject('Microsoft.XMLHTTP');

                    }

                    function put (content) {

                      r.open('PUT', '" + baseuri + requestUri + @"');

                      r.setRequestHeader(""Content-Type"", ""text/plain"");

                      r.send(document.getElementById(""period"").value);

                    }

                    function putLCD (content) {

                      r.open('PUT', '" + requestUri2 + @"');

                      r.setRequestHeader(""Content-Type"", ""text/plain"");

                      r.send(document.getElementById(""msg"").value);

                    }

                  </script>

                </head>

                <body>

                  <p>

                    Blink Rate

                    <input type=""text"" value=""500"" id=""period"">

                    <input type=""button"" value=""Set"" onclick=""put()""/>

                  </p><p>

                    Message

                    <input type=""text"" value=""Hello World"" id=""msg"">

                    <input type=""button"" value=""Set"" onclick=""putLCD()""/>

                  </p>

                </body>

              </html>";

            context.SetResponse(script, "text/html");

        }

    }

/* ---------------------

     * Continuously checks for

     * new strings in the buffer

     * to display

     * --------------------- */

    public class LCD {

        public Buffer SourceBuffer { get; set; }



        static GpioLcdTransferProvider lcdProvider = new GpioLcdTransferProvider(

             Pins.GPIO_PIN_D12,  // RS

             Pins.GPIO_NONE,     // RW

             Pins.GPIO_PIN_D11,  // enable

             Pins.GPIO_PIN_D9,   // d0

             Pins.GPIO_PIN_D8,   // d1

             Pins.GPIO_PIN_D7,   // d2

             Pins.GPIO_PIN_D6,   // d3

             Pins.GPIO_PIN_D5,   // d4

             Pins.GPIO_PIN_D4,   // d5

             Pins.GPIO_PIN_D3,   // d6

             Pins.GPIO_PIN_D2);  // d7

        static Lcd lcd;



        public void Run() {

            // create the LCD interface

            lcd = new Lcd(lcdProvider);

            // set up the LCD's number of columns and rows:

            lcd.Begin(16, 2);

            // Print a message to the LCD.

            print("init LCD");

            var period = 500;

            string msg = "";

            while (true) {

                Thread.Sleep(period / 2);

                object setpoint = SourceBuffer.HandleGet();

                if (setpoint != null) {

                    if (msg != (string)setpoint) {

                        msg = (string)setpoint;

                        Debug.Print(msg);

                        print(msg);

                    }

                }

            }



       

        }

        static void print(string msg) {

            lcd.Clear();

            lcd.Write(msg);

        }

    }

}

End of today's post.