sumitmotwani

How To Check If A Datareader Is Null Or Empty

2024-08-21T17:06:00.001-07:00

I have a datareader that return a lsit of records from a sql server database. I have a field in the database called 'Additional'. This field is 50% of the time empty or null. I am

Solution 1:

if (myReader["Additional"] != DBNull.Value)
{
    ltlAdditional.Text = "contains data";
}
else
{
     ltlAdditional.Text = "is null";
}

Solution 2:

if (myReader.HasRows) //The key Word is **.HasRows**

{

    ltlAdditional.Text = "Contains data";

}

else

{   

    ltlAdditional.Text = "Is null Or Empty";

}

Solution 3:

I haven't used DataReaders for 3+ years, so I wanted to confirm my memory and found this. Anyway, for anyone who happens upon this post like I did and wants a method to test IsDBNull using the column name instead of ordinal number, and you are using VS 2008+ (& .NET 3.5 I think), you can write an extension method so that you can pass the column name in:

publicstaticclassDataReaderExtensions
{
    publicstaticboolIsDBNull(this IDataReader dataReader, string columnName )
    {
        return dataReader[columnName] == DBNull.Value;
    }
}

Kevin

Solution 4:

This is the correct and tested solution

if (myReader.Read())
{

    ltlAdditional.Text = "Contains data";
}
else
{   
    ltlAdditional.Text = "Is null";
}

Solution 5:

I also use OleDbDataReader.IsDBNull()

if ( myReader.IsDBNull(colNum) ) { retrievedValue = ""; }
else { retrievedValue = myReader.GetString(colNum); }

Select Top N Dynamically With N From The Same Select Statement

2024-08-21T16:13:00.001-07:00

I have a procedure that tries to insert samples in a table for each Agent, the # of samples for each agent varies based on some calculation Declare @samplesize int Declare @Top int

Solution 1:

You can use row_number() to do essentially the same thing:

WITH DataToInsert AS
(
    Select AgentID, Surveys, LOB,(casewhenday(getdate())<4then3else (day(getdate())) - (Surveys*3) end) SampleSize from Current_Agent_SurveyCount_HSI Where surveys<8 
)
InsertInto Survey_Source_New (LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp])  
    select LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp]
    from (Select ss.LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp],
                 row_number() over (orderby newid()) as seqnum
          From Survey_source_Level1 ss innerjoin
               DataToInsert du on ss.AgentZID=du.agentID
          where flag isnulland du.samplesize<7
         ) t
    where seqnum <= du.sample_size

You might be able to simplify this a bit, but I don't know if flag is coming from du or ss.

Solution 2:

I have an added scenario over the same question. I made some changes to the existing solution, the scenario is : I get a list of Agents everyday and have to send each Agent a set of random Surveys, the count depends on some calculation for each agent.

--Get Agent List
;WITH AgentsList AS    
(    
    Select AgentID, Surveys, LOB,(casewhenday(getdate())<4then3else (day(getdate())) - (Surveys*3) end) SampleSize     
    from Current_Agent_SurveyCount_FIOS Where surveys<8     
)
--Get All the Surveys for each Agent
, AgentSurveys AS
(
    select ss.LOB, ss.CenterName, ss.CallDate, ss.AgentZid, ss.TN, ss.Ticket, ss.RecordingID, ss.Cycle, ss.[TimeStamp],ss.Flag,AL.samplesize 
    from Survey_Source_Level1_Sri ss
    innerjoin AgentsList AL on ss.AgentZID=AL.agentID
    where flag isnullGROUPBY ss.LOB, ss.CenterName, ss.CallDate, ss.AgentZid, ss.TN, ss.Ticket, ss.RecordingID, ss.Cycle, ss.[TimeStamp],ss.Flag,AL.samplesize
)
--Mark random ranking for each surveySelect LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp],samplesize,    
             rank() over (partitionby agentzid orderby newid()) as seqnum    
      From AgentSurveys
      groupby LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp],samplesize      
      orderby agentzid,seqnum,tn

Now from the last query i get a table like

I require the N number of rows from each group where N comes from SampleSize column.

Creating A Character Sequence On Postgresql

2024-08-21T15:03:00.001-07:00

So, I have this sequence referenced on a table column where everytime I do an insert its value is defined by nextval('ptable_pr_codigo_seq'::regclass) CREATE SEQUENCE public.ptable

Solution 1:

Challenge accepted ;)

I do not think there is any way to do that with only PostgreSQL sequence mechanism (1) But If you really need something like that (and I am quite interested on why you need something like this), you can do a function that return you the next value that you want and put it into a trigger.

For example, create a table first :

createtable test (test_id varchar);

Use a function like this one below

createor replace function next_id_test()
 returnstriggerlanguage plpgsql as $function$
beginwith cte_conform_char_list as
    (
        select val, row_number() over (orderby val), lead(val) over (orderby val)
        from (values ('A'), ('B'), ('C'), ('D'), ('E'), ('F')) as t(val) -- you can continue this list as much as you want it ;)orderby1
    )
    , cte_built_char_list as
    (
        select 
            cte.val
            , cte.row_number
            , coalesce(cte.lead, cte_2.val) as next_char
        from cte_conform_char_list cte
            leftouterjoin cte_conform_char_list cte_2
                on cte_2.row_number = cte.row_number - (selectmax(row_number) from cte_conform_char_list) +1
    )
    selectcasewhen row_number < (selectmax(row_number) from cte_built_char_list)
                then repeat(next_char, cast(rank() over (partitionby row_number orderby test_id) asint)) 
                else repeat(next_char, cast(rank() over (partitionby row_number orderby test_id) +1asint))
        endas next_test_id into new.test_id
    from test T
        innerjoin cte_built_char_list cte onsubstring(T.test_id from1for1) = cte.val
    orderbychar_length(test_id), test_id;

    returnnew;
end;
$function$;

Attach the function to a before trigger

createtrigger tg_test before inserton test foreachrowexecuteprocedure next_id_test();

Insert a value that doesn't really matter (it will be changed anyway)

insertinto test values ('ttt');

Then you can observe you have the right character.

select *
from test;

I know that's a little heavy way but I do not see any other one. The function is probably not perfect but I do not have a lot of time :)

Hope it's going to help you ;)

Violation Of Primary Key Constraint Error Sql

2024-08-21T14:47:00.001-07:00

Here is the code BEGIN TRANSACTION INSERT INTO [cresql].[dbo].[AR_Transactions] (DateTime , Dirty, Store_ID, Trans_Type, Cashier_ID, CustNum, Trans_Amount, Prev_Cust_Bala

Solution 1:

By design, your table [cresql].[dbo].[AR_Transactions] is supposed to hold only one value for the primary key - in your case, this seems to be a combination of columns that make up this PK.

This is not a SQL issue, but seems to be a design concept.

You should find out what combination of keys is making up your primary key - it may be that the design states that you can insert once and update later on for that combination (although I would not consider this to be good design).

EDIT

Since Trans_Id is the PK, and you are not using that column in your insert, it is either being inserted as IDENTITY, as a DEFAULT (brrrrr), or using a TRIGGER.

IDENTITY: Check to see if the IDENTITY specification is screwed up. If so, just reset it. Here is how to How can I reseed an identity column in a T-SQL table variable?

DEFAULT: This is an odd choice, and you may have to ask the one who designed the database, read documentation, or just simply figure it out on your own. In any case, this is a very uncommon choice.

TRIGGER: Find the trigger and read through the code to see what it is doing. Here is how to find a list of triggers What is the most portable way to check whether a trigger exists in SQL Server?

Alternate scenario:

There could be a trigger that inserts into an AUDIT table that may have the problem. Check if the table has any other triggers and see what they do.

Oracle Outer Join Syntax

2024-08-21T14:12:00.001-07:00

I have a query that looks like this: select * from find fsc, let lf, cust cus, STRIKE ist WHERE fsc.id = lf.id AND ist.ID_old = fsc.ID_old AND lf.

Solution 1:

I believe you want this:

select *
from find fsc joinlet lf
     on fsc.id = lf.id join
     STRIKE ist
     on ist.ID_old = fsc.ID_old left join
     cust cus
     on lf.cust_id = cus.cust_id;

To be honest, the outer join is probably not necessary. Why would lf have a cust_id that is not valid? The only reasonable possibility is if the value is NULL.

Does Sqlscript For Sap Hana Support The Use Of Insert With Ctes (common Table Expressions)?

2024-08-21T12:58:00.001-07:00

I know this isn't a specific bit of code or problem, but I am having trouble with a very similar issue to the person asking this (except theirs is for SQL Server): Combining INSERT

Solution 1:

Short answer: No, CTEs are not supported for INSERT/UPDATE statements.

Longer answer: SQLScript's INSERT/UPDATE commands are actually "borrowed" SQL commands as the documentation explains.

Checking the documentation for SQL INSERT we find that it supports a subquery as a source of values.

The subquery term is defined as part of the SQL SELECT statement. Checking the documentation for SELECT shows that <subquery> and <with_clause> are different, non-overlapping terms.

This means, that CTEs cannot be used in subqueries and therefore not be part of the subqueries used in INSERT/UPDATE commands.

You can, however, use SQLScript table variables in INSERT statements in your SQLScript blocks, which is very similar to CTEs:

DO BEGIN
  te_a :=SELECT10, 'xyz'as VAL from dummy;
  te_b :=SELECT20, 'abc'as VAL from dummy;

  te_all :=SELECT*from :te_a
      UNIONALLSELECT*from :te_b;

  INSERTINTO VALS 
             (SELECT*from :te_all);

END;

Solution 2:

You can convert the CTE into a Sub-Select statement in many cases

You can use following

insertinto city (city, countryid, citycode)
select
    city, countryid, citycode
from (
    -- CTE Expression as subselectselect*from city
    -- end (CTE)
) cte

Instead of using following valid CTE command combined with INSERT (on SQL Server)

with cte as (
    select*from city
)
insertinto city (city, countryid, citycode)
select
    city, countryid, citycode
from cte

Solution 3:

SAP HANA includes this posibility, the order of the code is different than SQL Server:

INSERT INTO EXAMPLE (ID)
 WITH cte1 AS (SELECT1AS ID FROM DUMMY)
 SELECT ID FROM cte1;

Sql Select With Stored Procedure And Parameters?

2024-08-21T12:19:00.001-07:00

I've been writing a lot of web services with SQL inserts based on a stored procedure, and I haven't really worked with any SELECTS. The one SELECT I have in mind is very simple.

Solution 1:

Here is where you are going wrong:

cmd.ExecuteNonQuery();

You are executing a query.

You need to ExecuteReader or ExecuteScalar instead. ExecuteReader is used for a result set (several rows/columns), ExecuteScalar when the query returns a single result (it returns object, so the result needs to be cast to the correct type).

var result = (int)cmd.ExecuteScalar();

The results variable will now hold a OledbDataReader or a value with the results of the SELECT. You can iterate over the results (for a reader), or the scalar value (for a scalar).

Solution 2:

Since you are only after a single value, you can use cmd.ExecuteScalar();

A complete example is as follows:

string ConnString = "Data Source=Removed"; 
    string userSid = "SomeSid";
    string SqlString = "SELECT COUNT(AD_SID) As ReturnCount FROM AD_Authorization WHERE AD_SID = @userSID;"; 
    int returnCount = 0;
    using (OleDbConnection conn = new OleDbConnection(ConnString)) 
    { 
        using (OleDbCommand cmd = new OleDbCommand(SqlString, conn)) 
        { 
            cmd.CommandType = CommandType.Text; 
            cmd.Parameters.AddWithValue("@userSID", userSid); 
            conn.Open(); 
            returnCount = Convert.ToInt32(cmd.ExecuteScalar()); 
        } 
    }

If you wanted to return MULTIPLE rows, you can use the ExecuteReader() method. This returns an IDataReader via which you can enumerate the result set row by row.

Solution 3:

You need to use ExecuteScalar instead of ExecuteNonQuery:

Stringquery="SELECT COUNT(AD_SID) As ReturnCount FROM AD_Authorization WHERE AD_SID = @userSID ";
using (OleDbConnectionconn=newOleDbConnection(ConnString)) {
    using (OleDbCommandcmd=newOleDbCommand(query, conn))
    {
        cmd.Parameters.AddWithValue("userSID", userSID.ToString());
        conn.Open();
        intreturnCount= (Int32) cmd.ExecuteScalar();
        conn.Close();
    }
}

Solution 4:

cmd.executescalar will return a single value, such as your count.

You would use cmd.executereader when you are returning a list of records

Stored Procedure To Find Free Slots With Different Work Times Per Day Of The Week

2024-08-21T11:40:00.001-07:00

I am using the awesome stored procedure created by Aaron Bertrand here as a start since it almost does what I need. Sadly my SQL skills are not as good as his so I need some help h

Solution 1:

Ok, this answer builds on the last one, we're going to add the lunch intervals and the events and going to look at a date range for events.

First lets make some test data:

DECLARE @event TABLE
(
    [event_id] [int] IDENTITY(1,1),
    [event_start] [datetime],
    [event_end] [datetime],
    [UserId] [int]
)

INSERT INTO @event
( event_start, event_end, UserId )
VALUES
('12/01/2014 09:00:00', '12/01/2014 09:30:00', 1),
('12/01/2014 09:30:00', '12/01/2014 10:30:00', 1),
('12/01/2014 11:00:00', '12/01/2014 12:00:00', 1),
('12/01/2014 13:00:00', '12/01/2014 16:30:00', 1);

DECLARE @Users TABLE
(
    [UserID] [int],
    [Username] [varchar](32) NULL,
    [Sunday] [bit] NULL,
    [Monday] [bit] NULL,
    [Tuesday] [bit] NULL,
    [Wednesday] [bit] NULL,
    [Thursday] [bit] NULL,
    [Friday] [bit] NULL,
    [Saturday] [bit] NULL,
    [WorkStartSunday] [time](7) NULL,
    [WorkEndSunday] [time](7) NULL,
    [IntervalStartSunday] [time](7) NULL,
    [IntervalEndSunday] [time](7) NULL,
    [WorkStartMonday] [time](7) NULL,
    [WorkEndMonday] [time](7) NULL,
    [IntervalStartMonday] [time](7) NULL,
    [IntervalEndMonday] [time](7) NULL,
    [WorkStartTuesday] [time](7) NULL,
    [WorkEndTuesday] [time](7) NULL,
    [IntervalStartTuesday] [time](7) NULL,
    [IntervalEndTuesday] [time](7) NULL,
    [WorkStartWednesday] [time](7) NULL,
    [WorkEndWednesday] [time](7) NULL,
    [IntervalStartWednesday] [time](7) NULL,
    [IntervalEndWednesday] [time](7) NULL,
    [WorkStartThursday] [time](7) NULL,
    [WorkEndThursday] [time](7) NULL,
    [IntervalStartThursday] [time](7) NULL,
    [IntervalEndThursday] [time](7) NULL,
    [WorkStartFriday] [time](7) NULL,
    [WorkEndFriday] [time](7) NULL,
    [IntervalStartFriday] [time](7) NULL,
    [IntervalEndFriday] [time](7) NULL,
    [WorkStartSaturday] [time](7) NULL,
    [WorkEndSaturday] [time](7) NULL,
    [IntervalStartSaturday] [time](7) NULL,
    [IntervalEndSaturday] [time](7) NULL
);

INSERT INTO @Users 
([UserID], [Username], [Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [WorkStartSunday], [WorkEndSunday], [IntervalStartSunday], [IntervalEndSunday], [WorkStartMonday], [WorkEndMonday], [IntervalStartMonday], [IntervalEndMonday], [WorkStartTuesday], [WorkEndTuesday], [IntervalStartTuesday], [IntervalEndTuesday], [WorkStartWednesday], [WorkEndWednesday], [IntervalStartWednesday], [IntervalEndWednesday], [WorkStartThursday], [WorkEndThursday], [IntervalStartThursday], [IntervalEndThursday], [WorkStartFriday], [WorkEndFriday], [IntervalStartFriday], [IntervalEndFriday], [WorkStartSaturday], [WorkEndSaturday], [IntervalStartSaturday], [IntervalEndSaturday]) 
VALUES 
(1, 'User A', 0, 1, 1, 0, 0, 0, 0, NULL, NULL, NULL, NULL, CAST('08:00:00' AS Time), CAST('18:00:00' AS Time), CAST('12:00:00' AS Time), CAST('13:00:00' AS Time), CAST('10:00:00' AS Time), CAST('15:00:00' AS Time), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

Once again, I am going to create a numbers table, now lets set our data range, and I am also going to generate time slots based on a 30 minute duration.

DECLARE@yearstart DATETIME ='01/01/2014'DECLARE@duration_minutes INT=30DECLARE@number_of_numbers INT=100000;
;WITH
    a AS (SELECT1AS i UNIONALLSELECT1),
    b AS (SELECT1AS i FROM a AS x, a AS y),
    c AS (SELECT1AS i FROM b AS x, b AS y),
    d AS (SELECT1AS i FROM c AS x, c AS y),
    e AS (SELECT1AS i FROM d AS x, d AS y),
    f AS (SELECT1AS i FROM e AS x, e AS y),
    numbers AS 
(
    SELECT TOP(@number_of_numbers)
    (ROW_NUMBER() OVER (ORDERBY (SELECTNULL))) -1AS number
    FROM f
)
, mydays AS
(
    SELECT DATEADD(DAY, number, @yearstart) CheckDate
    FROM numbers
)
, myduration AS
(
    SELECT DATEADD(MINUTE, number *@duration_minutes, @yearstart) CheckMinute
    FROM numbers
)
, workdays AS
(
    SELECT 
        m.CheckDate +CAST(u.WorkStartSunday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.WorkEndSunday as datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =1AND u.Sunday =1)
    UNIONSELECT 
        m.CheckDate +CAST(u.WorkStartMonday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.WorkEndMonday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =2AND u.Monday =1)
        UNIONSELECT 
        m.CheckDate +CAST(u.WorkStartTuesday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.WorkEndTuesday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =3AND u.Tuesday =1)
    UNIONSELECT 
        m.CheckDate +CAST(u.WorkStartWednesday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.WorkEndWednesday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =4AND u.Wednesday =1)
    UNIONSELECT 
        m.CheckDate +CAST(u.WorkStartThursday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.WorkEndThursday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =5AND u.Thursday =1)
    UNIONSELECT 
        m.CheckDate +CAST(u.WorkStartFriday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.WorkEndFriday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =6AND u.Friday =1)
    UNIONSELECT 
        m.CheckDate +CAST(u.WorkStartSaturday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.WorkEndSaturday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =7AND u.Saturday =1)
),
intervals AS
(
    SELECT 
        m.CheckDate +CAST(u.IntervalStartSunday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.IntervalEndSunday as datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =1AND u.Sunday =1)
        AND u.IntervalStartSunday ISNOTNULLUNIONSELECT 
        m.CheckDate +CAST(u.IntervalStartMonday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.IntervalEndMonday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =2AND u.Monday =1)
        AND u.IntervalStartMonday ISNOTNULLUNIONSELECT 
        m.CheckDate +CAST(u.IntervalStartTuesday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.IntervalEndTuesday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =3AND u.Tuesday =1)
        AND u.IntervalStartTuesday ISNOTNULLUNIONSELECT 
        m.CheckDate +CAST(u.IntervalStartWednesday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.IntervalEndWednesday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =4AND u.Wednesday =1)
        AND u.IntervalStartWednesday ISNOTNULLUNIONSELECT 
        m.CheckDate +CAST(u.IntervalStartThursday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.IntervalEndThursday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =5AND u.Thursday =1)
        AND u.IntervalStartThursday ISNOTNULLUNIONSELECT 
        m.CheckDate +CAST(u.IntervalStartFriday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.IntervalEndFriday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =6AND u.Friday =1)
        AND u.IntervalStartFriday ISNOTNULLUNIONSELECT 
        m.CheckDate +CAST(u.IntervalStartSaturday AS datetime) AS WorkStart,
        m.CheckDate +CAST(u.IntervalEndSaturday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNERJOIN@Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) =7AND u.Saturday =1)
        AND u.IntervalStartSaturday ISNOTNULL
)
, timeslots AS
(
    SELECT m.CheckMinute AS StartSlot,
        LEAD(m.CheckMinute) OVER (ORDERBY m.CheckMinute) AS EndSlot
    FROM myduration m
)

Now I also created the intervals (lunch hour data) and our final output will be a list of time slots and whether it is open, event, or lunch for a date range:

select 
    t.StartSlot,
    t.EndSlot,
    CASEWHEN i.WorkStart IS NULL AND e.event_start IS NULL THEN'Open' WHEN i.WorkStart ISNOT NULL THEN'Lunch'ELSE'Event'ENDAS IType
from timeslots t
INNER JOIN workdays w
    ON t.StartSlot >= w.WorkStart 
    AND t.StartSlot < w.WorkEnd
LEFT JOIN intervals i
    ON t.StartSlot >= i.WorkStart
    AND t.StartSlot < i.WorkEnd
LEFT JOIN @event e
    ON t.StartSlot >= e.event_start
    AND t.StartSlot < e.event_end
WHERE w.WorkStart >= '12/01/2014'AND w.WorkEnd < '12/04/2014'ORDERBY t.StartSlot

Here is the output:

StartSlotEndSlotIType2014-12-01 08:00:00.000 2014-12-01 08:30:00.000 Open2014-12-01 08:30:00.000 2014-12-01 09:00:00.000 Open2014-12-01 09:00:00.000 2014-12-01 09:30:00.000 Event2014-12-01 09:30:00.000 2014-12-01 10:00:00.000 Event2014-12-01 10:00:00.000 2014-12-01 10:30:00.000 Event2014-12-01 10:30:00.000 2014-12-01 11:00:00.000 Open2014-12-01 11:00:00.000 2014-12-01 11:30:00.000 Event2014-12-01 11:30:00.000 2014-12-01 12:00:00.000 Event2014-12-01 12:00:00.000 2014-12-01 12:30:00.000 Lunch2014-12-01 12:30:00.000 2014-12-01 13:00:00.000 Lunch2014-12-01 13:00:00.000 2014-12-01 13:30:00.000 Event2014-12-01 13:30:00.000 2014-12-01 14:00:00.000 Event2014-12-01 14:00:00.000 2014-12-01 14:30:00.000 Event2014-12-01 14:30:00.000 2014-12-01 15:00:00.000 Event2014-12-01 15:00:00.000 2014-12-01 15:30:00.000 Event2014-12-01 15:30:00.000 2014-12-01 16:00:00.000 Event2014-12-01 16:00:00.000 2014-12-01 16:30:00.000 Event2014-12-01 16:30:00.000 2014-12-01 17:00:00.000 Open2014-12-01 17:00:00.000 2014-12-01 17:30:00.000 Open2014-12-01 17:30:00.000 2014-12-01 18:00:00.000 Open2014-12-02 10:00:00.000 2014-12-02 10:30:00.000 Open2014-12-02 10:30:00.000 2014-12-02 11:00:00.000 Open2014-12-02 11:00:00.000 2014-12-02 11:30:00.000 Open2014-12-02 11:30:00.000 2014-12-02 12:00:00.000 Open2014-12-02 12:00:00.000 2014-12-02 12:30:00.000 Open2014-12-02 12:30:00.000 2014-12-02 13:00:00.000 Open2014-12-02 13:00:00.000 2014-12-02 13:30:00.000 Open2014-12-02 13:30:00.000 2014-12-02 14:00:00.000 Open2014-12-02 14:00:00.000 2014-12-02 14:30:00.000 Open2014-12-02 14:30:00.000 2014-12-02 15:00:00.000 Open

Solution 2:

Ok lets make the test data:

DECLARE @Users TABLE
(
    [UserID] [int],
    [Username] [varchar](32) NULL,
    [Sunday] [bit] NULL,
    [Monday] [bit] NULL,
    [Tuesday] [bit] NULL,
    [Wednesday] [bit] NULL,
    [Thursday] [bit] NULL,
    [Friday] [bit] NULL,
    [Saturday] [bit] NULL,
    [WorkStartSunday] [time](7) NULL,
    [WorkEndSunday] [time](7) NULL,
    [IntervalStartSunday] [time](7) NULL,
    [IntervalEndSunday] [time](7) NULL,
    [WorkStartMonday] [time](7) NULL,
    [WorkEndMonday] [time](7) NULL,
    [IntervalStartMonday] [time](7) NULL,
    [IntervalEndMonday] [time](7) NULL,
    [WorkStartTuesday] [time](7) NULL,
    [WorkEndTuesday] [time](7) NULL,
    [IntervalStartTuesday] [time](7) NULL,
    [IntervalEndTuesday] [time](7) NULL,
    [WorkStartWednesday] [time](7) NULL,
    [WorkEndWednesday] [time](7) NULL,
    [IntervalStartWednesday] [time](7) NULL,
    [IntervalEndWednesday] [time](7) NULL,
    [WorkStartThursday] [time](7) NULL,
    [WorkEndThursday] [time](7) NULL,
    [IntervalStartThursday] [time](7) NULL,
    [IntervalEndThursday] [time](7) NULL,
    [WorkStartFriday] [time](7) NULL,
    [WorkEndFriday] [time](7) NULL,
    [IntervalStartFriday] [time](7) NULL,
    [IntervalEndFriday] [time](7) NULL,
    [WorkStartSaturday] [time](7) NULL,
    [WorkEndSaturday] [time](7) NULL,
    [IntervalStartSaturday] [time](7) NULL,
    [IntervalEndSaturday] [time](7) NULL
);

INSERT INTO @Users 
([UserID], [Username], [Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [WorkStartSunday], [WorkEndSunday], [IntervalStartSunday], [IntervalEndSunday], [WorkStartMonday], [WorkEndMonday], [IntervalStartMonday], [IntervalEndMonday], [WorkStartTuesday], [WorkEndTuesday], [IntervalStartTuesday], [IntervalEndTuesday], [WorkStartWednesday], [WorkEndWednesday], [IntervalStartWednesday], [IntervalEndWednesday], [WorkStartThursday], [WorkEndThursday], [IntervalStartThursday], [IntervalEndThursday], [WorkStartFriday], [WorkEndFriday], [IntervalStartFriday], [IntervalEndFriday], [WorkStartSaturday], [WorkEndSaturday], [IntervalStartSaturday], [IntervalEndSaturday]) 
VALUES 
(1, 'User A', 0, 1, 1, 0, 0, 0, 0, NULL, NULL, NULL, NULL, CAST('08:00:00' AS Time), CAST('18:00:00' AS Time), CAST('12:00:00' AS Time), CAST('13:00:00' AS Time), CAST('10:00:00' AS Time), CAST('15:00:00' AS Time), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

Now that we have the user record created, lets make a numbers table and set the beginning of the date range we are creating:

DECLARE@yearstart DATETIME ='01/01/2014'DECLARE@number_of_numbers INT=100000;
;WITH
    a AS (SELECT1AS i UNIONALLSELECT1),
    b AS (SELECT1AS i FROM a AS x, a AS y),
    c AS (SELECT1AS i FROM b AS x, b AS y),
    d AS (SELECT1AS i FROM c AS x, c AS y),
    e AS (SELECT1AS i FROM d AS x, d AS y),
    f AS (SELECT1AS i FROM e AS x, e AS y),
    numbers AS 
(
    SELECT TOP(@number_of_numbers)
    ROW_NUMBER() OVER (ORDERBY (SELECTNULL)) AS number
    FROM f
)

Using our numbers table, lets make some days to check against:

, mydaysAS
(
    SELECT DATEADD(DAY, number, @yearstart) CheckDate
    FROM numbers
)

Now using the user table, we are going to make a all of the working days of the week for the user, based on whether the flag is set, and the starting date / time and ending date / time

, workdaysAS
(
    SELECT 
        m.CheckDate + CAST(u.WorkStartSunday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndSunday as datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 1AND u.Sunday = 1)
    UNION
    SELECT 
        m.CheckDate + CAST(u.WorkStartMonday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndMonday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 2AND u.Monday = 1)
        UNION
    SELECT 
        m.CheckDate + CAST(u.WorkStartTuesday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndTuesday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 3AND u.Tuesday = 1)
    UNION
    SELECT 
        m.CheckDate + CAST(u.WorkStartWednesday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndWednesday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 4AND u.Wednesday = 1)
    UNION
    SELECT 
        m.CheckDate + CAST(u.WorkStartThursday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndThursday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 5AND u.Thursday = 1)
    UNION
    SELECT 
        m.CheckDate + CAST(u.WorkStartFriday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndFriday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 6AND u.Friday = 1)
    UNION
    SELECT 
        m.CheckDate + CAST(u.WorkStartSaturday AS datetime) AS WorkStart,
        m.CheckDate + CAST(u.WorkEndSaturday AS datetime) AS WorkEnd,
        u.UserID,
        u.Username
    FROM mydays m
    INNER JOIN @Users u
        ON (DATEPART(WEEKDAY, m.CheckDate ) = 7AND u.Saturday = 1)
)

Now lets look at the data we generated:

SELECT w.WorkStart, w.WorkEnd, DATENAME(WEEKDAY, w.WorkStart) DayOfWeek, w.UserID, w.Username
FROM workdays w
where w.WorkStart >='12/01/2014'AND w.WorkEnd <'01/01/2015'

Here is the output:

WorkStartWorkEndDayOfWeekUserIDUsername2014-12-01 08:00:00.000 2014-12-01 18:00:00.000 Monday1UserA2014-12-02 10:00:00.000 2014-12-02 15:00:00.000 Tuesday1UserA2014-12-08 08:00:00.000 2014-12-08 18:00:00.000 Monday1UserA2014-12-09 10:00:00.000 2014-12-09 15:00:00.000 Tuesday1UserA2014-12-15 08:00:00.000 2014-12-15 18:00:00.000 Monday1UserA2014-12-16 10:00:00.000 2014-12-16 15:00:00.000 Tuesday1UserA2014-12-22 08:00:00.000 2014-12-22 18:00:00.000 Monday1UserA2014-12-23 10:00:00.000 2014-12-23 15:00:00.000 Tuesday1UserA2014-12-29 08:00:00.000 2014-12-29 18:00:00.000 Monday1UserA2014-12-30 10:00:00.000 2014-12-30 15:00:00.000 Tuesday1UserA

You can do the same thing for the intervals (I guess the interval is the lunch break?), and then join against them to find the work times available, and then join against the events to see which work times you can create an event against.

Inserting List In Specific Column Using Python

2024-08-21T10:26:00.001-07:00

i have a database where there are two column sess_id and data. Now i want to insert a list from py to db how should i do this. suppose this is the list to be inserted in data colum

Solution 1:

Depends on what you want to do. If you want to insert a list of values into one column you can loop through the list and insert row by row. You can also use executemany instead of execute.

If you're directly inputting the list then use excutemany, otherwise if you're looping through the list use execute.

cursor.executemany("""
              INSERT INTO table
              (data)
              VALUES (?)
              """, data_list)

for i in data_list:
    cursor.execute("INSERT INTO table (data) VALUES (?)", i)

Solution 2:

You should iterate list like this:

cursor_three=conn.cursor()
for elem in database_list:
    cursor_three.execute('INSERT INTO table (data) VALUES (%s)' % elem) 
cursor_three.commit()

After that you will have sess_id column empty unless you declared it autoincrement row. If you have two sorted arrays for example

database_id_list = [212, 444, 532...]
database_data_list= ['elem212','elem444','elem532'...]

You should use zip() function to iterate them simulteniously, like this:

for elem inzip(database_id_list, database_data_list):
    cursor_three.execute('INSERT INTO table (sess_id, data) VALUES (%s, %s)' % elem)  # elem is tuple here

Notice that this code is using string fromat function for simplness (you didn't noticed which database you're using) and it is not protected from SQL injections. You better use your sql deriver's formatter, for example for psycopg2:

cursor_three.execute('INSERT INTO table (data) VALUES (%s)', [elem])

What Are The Advantages Of Merge Over Simple If Exists?

2024-08-21T09:33:00.001-07:00

I want to know what are the advantages of MERGE over simply using IF EXISTS. Which is the suggested approach? Does MERGE performs Update and Insert row-by-row matching conditions?

Solution 1:

MERGE combines INSERT, UPDATE and DELETE logic into one DML statement, and therefore is atomic. If you are doing single row UPSERTS then the advantages are less obvious. For example, a naive implementation of an UPSERT may look like the following:

IF  EXISTS (SELECT*FROM t1 where id=@id)
    UPDATE t1 SET ... WHERE id=@idELSEINSERTINTO t1 (...) VALUES (...)

However, without wrapping this in a transaction, it is possible that the row we're going to update will be deleted between the SELECT and the UPDATE. Adding minimal logic to address that issue give us this:

BEGIN TRAN
IF  EXISTS (SELECT*FROM t1 WITH (HOLDLOCK, UPDLOCK) where id=@id )
    UPDATE t1 SET ... WHERE id=@idELSEINSERTINTO t1 (...) VALUES (...)
COMMIT

This logic isn't necessary with the MERGE statement.

There are no comparisons that should be drawn between CURSORS and the MERGE statement.

Solution 2:

Merge will give you the option of updating, inserting and deleting data in a target table where is it matched in a source table. It is a set based operation so is not like a cursor (row by row)

I am not sure how you mean by advantages over 'IF EXISTS', but merge is a useful and flexible way of synchronizing 2 tables

this is a useful resource for merge https://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/

How Do You Convert Rows To Columns In Postgres?

2024-08-21T09:17:00.001-07:00

I have a table with the following columns: user_id, grade. user_id | grade 1 k 1 3 2 4 2 5 3 2 I would like to be able to make a table that

Solution 1:

Using filtered aggregation is typically much easier than using the crosstab() function:

select user_id, 
       max(grade) filter(where grade = 'k') isnotnullas teaches_grade_k,
       max(grade) filter(where grade = '1') isnotnullas teaches_grade_1,
       max(grade) filter(where grade = '2') isnotnullas teaches_grade_2,
       max(grade) filter(where grade = '3') isnotnullas teaches_grade_3,
       max(grade) filter(where grade = '4') isnotnullas teaches_grade_5
       max(grade) filter(where grade = '5') isnotnullas teaches_grade_5
from users
groupby user_id;

Solution 2:

Another option, similar to a filtered aggregation, would be to use a regular array aggregation and check for existence, like

WITH users (user_id, grade) AS (
    VALUES
        (1, 'k'),
        (1, '1'),
        (2, '4'),
        (2, '5'),
        (3, '2')
),
user_grades AS (
    SELECT user_id, array_agg(grade) AS grades
    FROM users
    GROUPBY user_id
)
SELECT
    user_id,
    'k'=ANY(grades) AS teaches_k,
    '1'=ANY(grades) AS teaches_1,
    '2'=ANY(grades) AS teaches_2,
    '3'=ANY(grades) AS teaches_3,
    '4'=ANY(grades) AS teaches_4,
    '5'=ANY(grades) AS teaches_5
FROM
    user_grades

How To Get Renamed, Deleted, And Added Columns Of The Table?

2024-08-21T08:25:00.001-07:00

Suppose I have a table in a database (TblExample) that contains three columns: Name Age City After some time, someone added one extra column (Country) or deleted a column (City)

Solution 1:

From the tag I see you are using SQL, you can check out DDL triggers:

http://sqlserverpedia.com/blog/sql-server-security/csidba-finding-out-who-made-schema-changes/

Adapt Beginner C# Sql Server Insert Example To Work With My Database

2024-08-21T08:05:00.001-07:00

I have read TONS of tutorials, articles and whatever regarding my issue and honestly, due to my lack of experience I can't twist my fingers around this one so I hope some of you gu

Solution 1:

Here is some advice to get you going, learning programming is a lot of trial and error.

Start off basic, litrally put three textboxes on a form/page and a button.
Double click the button to go the code-behind and view the buttons click event.
Paste in the body of code included with your question (everything in the try-catch).
Put a break-point on the Public Void Button_Click line of code and press F11 to step through the code.

"one thing is having the code-behind working but how to make the buttons and textboxes working is still a misery"*

Put the textbox as the value rather than your hardcoded values:

cmd.Parameters.AddWithValue("@Address", textBox1.Text);

You also should not insert the Id value, instead modify the EmployeeDetails table and set the ID column to in the properties set Identity Specification (IS Identity) = True. Then right click the ID column and set Primary Key.

Post any error messages you encounter here and when you do get get it working, an additional exercise (that will be very valuable for you) would use a database stored procedure rather than ad-hoc SQL, to safe-guard against sql-injection attacks.

I'm assuming you have SQL Server installed and have a 'employee' database with a table called EmployeeDetails.

Solution 2:

protectedvoidGvManualShows_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        //label lbl = (label)e.Row.FindControl("lblHidden");if (e.Row.Cells[14].Text == "Y")
        {
            // CheckBox cb = (CheckBox)e.Row.FindControl("chk");

            CheckBox chk = (CheckBox)e.Row.Cells[0].FindControl("chkBox");
            chk.Checked = true;

        }
    }
}

Solution 3:

It's fairly simple. You just have to modify the connection string, the query and its parameters:

privatevoidbutton1_Click(object sender, EventArgs e)
{
    try
    {
        string connectionString =
            "server=SQLServer;" +         // SQLServer is your SQL server machine"initial catalog=employee;" + // employee is your database"user id=sa;" +               // sa is the login to connect the database"password=sa123";             // sa123 is the password of the loginusing (SqlConnection conn =
            new SqlConnection(connectionString))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(
                "INSERT INTO [NyhedTB] ([NyhedDato], [NyhedTitel], [NyhedTekst]) " +
                "VALUES (@NyhedDato, @NyhedTitel, @NyhedTekst)", conn))
            {
                cmd.Parameters.AddWithValue("@NyhedDato", textBoxDate.Text);
                cmd.Parameters.AddWithValue("@NyhedTitel", textBoxTitle.Text);
                cmd.Parameters.AddWithValue("@NyhedTekst", textBoxBody.Text);

                int rows = cmd.ExecuteNonQuery();  // Inserted rows number
            }
        }
    }
    catch (SqlException ex)
    {
        //Log exception//Display Error message
    }
}

Solution 4:

I made changed example code with your requirements and added comments, hope it would be a bit clearer for you to understand whats going on:

staticvoidInsert()
{
    try
    {
        string connectionString =
        "server=.;" +
        "initial catalog=MyDatabaseName;" + //here you write database name where your NyhedTB table is"user id=sa;" + //user name to connect to database"password=sa123"; //passwordusing (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            using (SqlCommand cmd =
              new SqlCommand("INSERT INTO NyhedTB (NyhedDato, NyhedTitel, NyhedTekst) VALUES (@NyhedDato, @NyhedTitel, @NyhedTekst)", conn))
            {
                //all "things" in your sql command what beggins with @ //means that it is parameter and you need to pass values for these parameters: //For @NyhedDato parameter you set text from your textbox
                cmd.Parameters.AddWithValue("@NyhedDato", txtDate.Text);

                //For @NyhedTitel parameter you set text from title textbox
                cmd.Parameters.AddWithValue("@NyhedTitel", txtTitle.Text);

                //For @NyhedTekst parameter you set text from content textbox
                cmd.Parameters.AddWithValue("@NyhedTekst", txtContent.Text);

                //Execute insert command and get how many records was efected, in this case it should be rows = 1 because you inserting just one recordint rows = cmd.ExecuteNonQuery();
            }
        }
    }
    catch (SqlException ex)
    {
       //Log exception//Display Error message
    }
}

P.s. code not tested. And when you say

I have 3 rows that has to be inserted: Headline Date Content/the news itself.

actually you mean you want to insert record with fields

How To To Give Preference To Null Value During Select

2024-08-21T07:47:00.001-07:00

I have a table with Id value 1000 null 1000 En 1000 Fr 1000 Es 1001 En 1001 Fr 1001 Es Output of the select query should be as follows. (Since 1000 has

Solution 1:

You can use NOT EXISTS and a correlated subquery to check for the non-existence of a NULL for an ID. Include these rows and also rows where value is NULL.

SELECT t1.id,
       t1.value
       FROM elbat t1
       WHERENOTEXISTS (SELECT*FROM elbat t2
                                WHERE t2.id = t1.id
                                      AND t2.value ISNULL)
              OR t1.value ISNULL;

Solution 2:

with
  t (id, value) as (
    select1000, nullfrom dual unionallselect1000, 'En'from dual unionallselect1000, 'Fr'from dual unionallselect1000, 'Es'from dual unionallselect1001, 'En'from dual unionallselect1001, 'Fr'from dual unionallselect1001, 'Es'from dual
  )
select id, valuefrom   (
         select t.*, 
                dense_rank() over (partitionby id orderby nvl2(value, 1, 0)) rnk
         from   t
       )
where  rnk =1
;

        ID VA
---------- --10001001 En
      1001 Fr
      1001 Es

Functions used in this query:

NVL2()https://docs.oracle.com/database/121/SQLRF/functions132.htm#SQLRF00685

DENSE_RANK()https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions043.htm

Solution 3:

In the most recent versions of Oracle, you can actually do this without a subquery:

select t.*from t
orderbyrank() over (partitionby id orderby (casewhenvalueisnullthen1else2end))
fetchfirst1rowwith ties;

Here is a db<>fiddle.

Solution 4:

You can use analytical function as following:

Select id , valuefrom
(Select t.*,
       Coalesce(Sum(casewhenvalueisnullthen1end) over (partitionby id), 0) as cnt
 From your_table)
Where (cnt =1andvalueisnull)
    or cnt =0

Cheers!!

I Want To Write One Common Stored Procedure To Insert Data In Any Table Of My Database

2024-08-21T06:53:00.001-07:00

I have 6 table with different fields. I want to access table name dynamically. is there any idea to do it? My code is below this is simple procedure which I want to make dynamic to

Solution 1:

Don't go there. It's a bad idea since you will end up with a long, inefficient stored procedure that will be vulnerable to SQL injection attacks and have performance issues. Writing an insert stored procedure for each table is the way to go.

You wrote you have six different tables with different columns, so writing a stored procedure to handle inserts for all of them will require you to send all the parameters for all columns as well as a parameter for the table name, and a nested if...else with 6 possible paths, one for each table. This will end up as a long, messy, poorly written code at best, bad in each parameter: security, performance, code readability and maintainability.

The only way that makes some sense to achieve such a goal is to write individual insert stored procedures for each table, and then write a stored procedure that will take all of the possible parameters and the table name and inside of it decide what insert stored procedure to execute based on the value of the table name parameter. However, you will be better off leaving conditions like these to the SQL client (your c# code in this case) then to SQL Server.

Solution 2:

Its very easy to do.........

Just call the sql query using Data Adapter.

select TABLE_NAME from INFORMATION_SCHEMA.TABLES

Solution 3:

As you said you need dynamic SQL like this:

Createprocedure [dbo].[Insert_Data]
(
    @TableName nvarchar(512),
    @Values nvarchar(max)
)
BEGINDECLARE@SQL nvarchar(max)
    SELECT@SQL='INSERT INTO '+@TableName+' VALUES ('+@Values+')'EXEC(@SQL)
END

Note that @Values will be like this '1, ''name'', 10.2' and with the same order of columns.

Createprocedure [dbo].[Insert_Data]
(
    @TableName nvarchar(512),
    @Fields nvarchar(max),
    @Values nvarchar(max)
)
BEGINDECLARE@SQL nvarchar(max)
    SELECT@SQL='INSERT INTO '+@TableName+' ('+@Fields+') VALUES ('+@Values+')'EXEC(@SQL)
END

To more ability to handle column order and remove identity columns.

Solution 4:

As Robert Harvey mentioned it is a bad idea, anyway if you want to you can do something like....

CREATEPROCEDURE Insert_Data
 @TableName     SYSNAME
,@Column1       SYSNAME         =NULL
,@Column2       SYSNAME         =NULL 
,@Column3       SYSNAME         =NULL
,@Value1        NVARCHAR(100)   =NULL
,@Value2        NVARCHAR(100)   =NULL
,@Value3        NVARCHAR(100)   =NULLASBEGINSET NOCOUNT ON;
  DECLARE@Sql NVARCHAR(MAX);

SET@Sql= N' INSERT INTO '+ QUOTENAME(@TableName) 
         + N' ( '+ STUFF(
           CASEWHEN@Column1ISNOTNULLTHEN N','+ QUOTENAME(@Column1) ELSE N''END+CASEWHEN@Column2ISNOTNULLTHEN N','+ QUOTENAME(@Column2) ELSE N''END+CASEWHEN@Column3ISNOTNULLTHEN N','+ QUOTENAME(@Column3) ELSE N''END
                 ,1,1,'')
         + N' ) '+ N' VALUES ( '+  STUFF(
           CASEWHEN@Value1ISNOTNULLTHEN N', @Value1'ELSE N''END+CASEWHEN@Value2ISNOTNULLTHEN N', @Value2'ELSE N''END+CASEWHEN@Value3ISNOTNULLTHEN N', @Value3'ELSE N''END  
             ,1,1,'')
          + N' ) 'Exec sp_executesql @Sql
                  ,N'@Value1 NVARCHAR(100),@Value2 NVARCHAR(100),@Value3 NVARCHAR(100)'
                  ,@Value1
                  ,@Value2
                  ,@Value3END

Convert Image Data Type Into Base64 And Base64 Into Image Data Type

2024-08-21T06:15:00.001-07:00

I'm using Data-Type 'Image' in MS SQL 2012 to store Image. problem: I have an image in BASE64 string in C# /9j/4AAQSkZJRgABAQEASABIAAD/4SKhRXhpZgAATU0AKgAAAAgABwESAAMAAAABAAEAAAEaA

Solution 1:

I found the solution for this, Please look into this because it might be useful when you do not want to change your datatype Image in your database:

I sent base64 string as it is to database and there I converted it into varbinary like this:

SELECTCAST(N''AS xml).value('xs:base64Binary(sql:variable("@Image2"))', 'varbinary(max)')

and inserted it into the image column.

then I retrieved an image in base64 like this:

SELECTcast(''as xml).value('xs:base64Binary(sql:column("img"))', 'varchar(max)') FROM imageTemp WHERE...

It gave me the exact Base64 string which I have sent earlier.

Thank You.

Solution 2:

I believe you need help on how to store image data in sql server.

You can create a table like below. Please note that you may not want to create database , just a table would suffice. If you already have a table where you want to save this data, you will have to make sure that the type of column is varbinary(max).

CREATE DATABASE MyDatabase;  
GO  
USE MyDatabase;  
GO  
CREATETABLE MyImageDatabaseTable (Id int, BLOBData varbinary(max));  
GO

Then you can read the data as:

SqlConnectionobjConn=newSqlConnection(connectionString);  
objConn.Open();  
SqlCommandobjCmd=newSqlCommand("Select * From MyImageDatabaseTable", objConn);  
SqlDataReaderdr= objCmd.ExecuteReader();  
while(dr.Read())
{
     byte[] myImageByteArrayData = (byte[]) dr["BLOBData"];  

     stringmyImageBase64StringData= Convert.ToBase64String(myImageByteArrayData );
}

Hope this helps.

Solution 3:

Answering your question

How to retrieve base64 from database using this byte[] array?

SQL-Server will transform your binary to base64 implicitly when embedding it into XML.

Try this

--I fill a binary variable with some dummy contentDECLARE@SomeBinaryVARBINARY(MAX) =CAST('This is a dummy content'ASVARBINARY(MAX));
--this is the resulting binarySELECT@SomeBinary;
--Now you see what happens using FOR XML PATHSELECT (SELECT@SomeBinaryFOR XML PATH(''))

We can re-convert this easily

DECLARE@base64VARCHAR(MAX)='VGhpcyBpcyBhIGR1bW15IGNvbnRlbnQ=';
SELECTCAST(@base64AS XML).value('.','varbinary(max)');

Probably you'll have to use a CAST(YourImageTypedColumn AS VARBINARY(MAX)) first.

But it might be easier to retrieve this as binary and to the conversion on your client side application.

Solution 4:

I use this method bellow, to convert bytearray from database to an image.

public Bitmap ConvertByteArrayToBitmap(byte[] Array)
{
    if (Array == null) returnnull;

    using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
    {
        ms.Write(Array, 0, Array.Length);
        ms.Position = 0L;

        returnnew Bitmap(ms);
    }
}

Regards.

Solution 5:

You can Convert binary to Base64 using XML and the hint "for xml path"

select file_name,ColumnWithBinary,ColumnToSwFinalResult
from TableWithBinary
cross apply (select ColumnWithBinary '*'for xml path('')) T (ColumnToSwFinalResult)
GO

Deploying Laravel On Heroku "pdoexception In Connector.php Line 55" (sqlite)

2024-08-21T05:19:00.001-07:00

I tried to deploy my first Laravel project on Heroku. It's a small project, so I'm using SQLite as my database. After deploying the project using git push heroku master, and clicki

Solution 1:

I've also encountered this on Heroku.

I added:

{"require":{"ext-pdo_sqlite":"*"}}

Solution 2:

Sqlite is setup as a shared extension on Heroku, but has to be enabled through composer.json i believe.

Try adding:

{"require":{"sqlite3":"*"}}

To your composer.json file.

Complex / Algorithmic Sql Query

2024-08-21T04:42:00.001-07:00

I am wondering if its possible to implement an SQL Query that will act like a sort of algorithm to calculate a certain figure for me based on the following table: This is the initi

Solution 1:

Sure... try something like this.

select e1.Requestor, 
       e1.TimeOfAction as LoginTime, 
       (selectmin(ActivityTime)
        from EventLog where TimeOfAction > e1.TimeOfAction 
        and Activity = 'Logout') as LogoutTime
from EventLog e1
where e1.ActivityType = 'Login'
order by Requestor, LoginTime

Second Solution... See if this works better for ya.

select requestor,
    (selectmin(activitytime)
     from eventlog 
     where activitytime < e.activitytime
     and activity = 'Login'and e.activity = 'Logout') as LoginTime, 
    (selectmax(activitytime)
     from eventlog 
     where activitytime > e.activitytime
     and activity = 'Logout'and e.activity = 'Login') as LogoutTime, 
from eventlog e
order by requestor, logintime

Solution 2:

selectmin(TimeOfAction) Login, null Logout, Requestor
  from EventLog
 where Activity = 'Login'groupby Requestor
union
selectnull Login, max(TimeOfAction) Logout, Requestor
  from EventLog
 where Activity = 'Logout'groupby Requestor

Solution 3:

Heres an option for you using some CTEs and row_numbers. Basically, it orders the events for each user, then finds the list of logins that either follow a logout or nothing, then finds a list of logouts that proceed logins or nothing, then associates them into pairs.

;with events as (
  select*,
         row_number() over(partitionby Requestor orderby TimeOfAction) rowfrom EventLog
), logins as (
  select e1.Activity, e1.TimeOfAction, e1.Requestor,
         row_number() over(partitionby e1.Requestor orderby e1.TimeOfAction) rowfrom events e1
    leftjoin events e2 on e1.Requestor=e2.Requestor
                       and e1.row=e2.row+1where e1.Activity='Login'and e1.Activity!=isnull(e2.Activity, 'Logout')
), logouts as (
  select e1.Activity, e1.TimeOfAction, e1.Requestor,
         row_number() over(partitionby e1.Requestor orderby e1.TimeOfAction) rowfrom events e1
    leftjoin events e2 on e1.Requestor=e2.Requestor
                       and e1.row=e2.row-1where e1.Activity='Logout'and e1.Activity!=isnull(e2.Activity, 'Login')
)
select i.Requestor, i.TimeOfAction as loginTime, o.TimeOfAction as logoutTime
from logins i
  leftjoin logouts o on i.Requestor=o.Requestor
                     and i.row=o.row

NOTE: Query performance may be (drastically?) increased by splitting off some, or all, of the CTE queries into temp tables. i.e. something like the following:

select*,
       row_number() over(partitionby Requestor orderby TimeOfAction) rowinto #events
from EventLog

select e1.Activity, e1.TimeOfAction, e1.Requestor,
       row_number() over(partitionby e1.Requestor orderby e1.TimeOfAction) rowinto #logins
from #events e1
  leftjoin #events e2 on e1.Requestor=e2.Requestor
                     and e1.row=e2.row+1where e1.Activity='Login'and e1.Activity!=isnull(e2.Activity, 'Logout')

select e1.Activity, e1.TimeOfAction, e1.Requestor,
       row_number() over(partitionby e1.Requestor orderby e1.TimeOfAction) rowinto #logouts
from #events e1
  leftjoin #events e2 on e1.Requestor=e2.Requestor
                     and e1.row=e2.row-1where e1.Activity='Logout'and e1.Activity!=isnull(e2.Activity, 'Login')

select i.Requestor, i.TimeOfAction as loginTime, o.TimeOfAction as logoutTime
from #logins i
  leftjoin #logouts o on i.Requestor=o.Requestor
                      and i.row=o.row

droptable #logouts
droptable #logins
droptable #events

Is Sequential Disk Read Actually Sequential?

2024-08-21T04:24:00.001-07:00

I'm using PostgreSQL 9.4. First, I have postgreSQL installed on a system with the only one ssd-drive. I'm trying to understand what sequential read is and end up with some issue.

Solution 1:

"sequential scan" means a table was read from the beginning to the end, sequentially row by row. It means nothing in terms of how data is read from physical storage.

So the term is about logical reads.

Not sure if the answer needs more explanation.

Insert Not Working For Sqlalchemy Database Session

2024-08-21T04:05:00.001-07:00

Why isn't a record being inserted? There is an id returned but when I check the database there is no new record. From models.py from zope.sqlalchemy import ZopeTransactionExtension

Solution 1:

This is because you are not using ORM to insert new rows threfore transaction doesn't know it should commit on it's own, because transaction state is not marked as dirty.

Place the following code after you DBSession.execute the query in your views.py.

from zope.sqlalchemy importmark_changedsession= DBSession()
session.execute(...your query...)
mark_changed(session)

At this point transaction should be able to properly commit your query, alternatively use ORM to insert the new row.

Here is a bit more on this subject:

https://pypi.python.org/pypi/zope.sqlalchemy/0.7.4#id15

By default, zope.sqlalchemy puts sessions in an 'active' state when they are first used. ORM write operations automatically move the session into a 'changed' state. This avoids unnecessary database commits. Sometimes it is necessary to interact with the database directly through SQL. It is not possible to guess whether such an operation is a read or a write. Therefore we must manually mark the session as changed when manual SQL statements write to the DB.

Solution 2:

try

DBSession.flush()

after execute

Coalesce In Where Clause?

2024-08-21T03:29:00.001-07:00

Is there a good way to express within SQL to select a specific row if a column is matched, otherwise to select a more general row? Something like: SELECT name FROM names

Solution 1:

@Blorgbeard answer (using top 1 and order by) is probably the best way but just to be different you could also use a sub-query and not exists:

SELECTnameFROMnamesWHERE (colA = @colA) OR (colA = 'ALL' AND NOT EXISTS(
                                                SELECT name FROM names 
                                                WHERE colA = @colA))

I guess the "advantage" of this is that it uses more standard sql.

Solution 2:

This query, although not pretty, should do what you want:

SELECTTOP1nameFROMnamesWHERE (colA = @colA OR colA = 'ALL') 
ORDERBYCASEWHEN colA='ALL' THEN 1 ELSE 0 END

Edit: For multiple columns, I think what you would want is this:

SELECTTOP1nameFROMnamesWHERE (colA = @colA AND colB = @colB) OR (colA = 'ALL') 
ORDERBYCASEWHEN colA='ALL' THEN 1 ELSE 0 END

I'm assuming that there is just one ALL row, so there's no need to check for colB='ALL', and that the "key" for the table is colA and colB combined - so you're not interested in rows where colA matches but colB doesn't, for example.

Solution 3:

COALESCE is useful when joining multiple tables and you want the first non-null value in specific order.

Ex.

SELECT
   TheID=COALESCE(Table1.TheID,Table2.TheID,NULL)
FROM
   Table1 
   INNERJOIN Table2 ON Table1.TheID=Table2.TheID

One alternative way to achieve what you are asking apart from the example you posted would be to set a nullable parameter and only match if it has a value:

Ex.

CREATETest(@Param1 INT=NULL)
ASSELECT
    *
FROMTableWHERE
    (@Param1 IS NULL OR IDField=@Param1)

If all non-matching records require a record as well as matches then I guess you could join the table on itself to get matches and mismatched.

SELECT
    Calc=CASEWHEN Table2.IDField ISNULLTHEN Table.Value ELSE'ALL'ENDFROMTableINNERJOINTable Table2 ON Table2.IDField<>@Param1WHERE
    (Table.IDField=@Param1)

Sql Server Datediff Vs .net Datediff

2024-08-21T03:12:00.001-07:00

SQL Server: DECLARE @Date1 DATETIME SET @Date1 = '2012-26-12 12:00:05 AM' DECLARE @Date2 DATETIME SET @Date2 = '2012-28-12 12:00:00 AM' SELECT @Date1, @Date2, DATEDIFF(DAY,@Date1

Solution 1:

Actually the VB.NET expression is returning you the correct values since the total number of hours is not equal to 48 .Hence it is returning 1

In SQL Server , DateDiff function returns a round off value .

Try dividing the value with total number of seconds in day which is 86400.0

SELECT @Date1, @Date2, DATEDIFF(ss,@Date1,@Date2) /86400.0

The value returned will be 1.9999421 instead of 2

The difference between the 2 date in seconds is 172795 which is less than 172800 (Total seconds for 2 days).Hence the function should be returning only 1 if your not rounding off the result

Solution 2:

Day DateDiff round down the number returned. Try to return hours and divide by 24 to get count of days

DateDiff(DateInterval.Hour,Convert.ToDateTime("26-12-2012 12:00:05 AM"), 
Convert.ToDateTime("28-12-2012 12:00:00 AM"))/24

How Do I Include Empty Rows In A Single Group By Day(date_field) Sql Query?

2024-08-21T01:39:00.001-07:00

I'm using MS SQL Server but welcome comparitive solutions from other databases. This is the basic form of my query. It returns the number of calls per day from the 'incidentsm1' t

Solution 1:

How about something like this?

SELECTCOUNT(incident_id) AS "Calls",
  MAX(open_time),
  days.open_day
FROM
(
  select datepart(dd,dateadd(day,-6,getdate())) as open_day unionselect datepart(dd,dateadd(day,-5,getdate())) as open_day unionselect datepart(dd,dateadd(day,-4,getdate())) as open_day unionselect datepart(dd,dateadd(day,-3,getdate())) as open_day unionselect datepart(dd,dateadd(day,-2,getdate())) as open_day unionselect datepart(dd,dateadd(day,-1,getdate())) as open_day unionselect datepart(dd,dateadd(day, 0,getdate())) as open_day 
) days
leftjoin 
(
 SELECT
   incident_id,
   opened_by,
   open_time - (9.0/24) AS open_time,
   DATEPART(dd, (open_time-(9.0/24))) AS open_day
 FROM incidentsm1 
 WHERE DATEDIFF(DAY, open_time-(9.0/24), GETDATE()) <7
) inc1 ON days.open_day = incidents.open_day
GROUPBY days.open_day

I've only tested it on a simplified table schema, but I think it should work. You might need to tinker with the dateadd stuff..

Solution 2:

I would suggest the usage of a date table. With an existing date table in place, you can perform a RIGHT OUTER JOIN to the date table to bring in your missing days.

Solution 3:

Can you create a table variable with the dates that you need and then RIGHT JOIN onto it? For example,

DECLARE@dateTableTABLE ([date] SMALLDATETIME)

INSERTINTO@dateTableVALUES('26 FEB 2009')
INSERTINTO@dateTableVALUES('27 FEB 2009')
-- etcSELECTCOUNT(*) AS "Calls",
  MAX(open_time),
  open_day
FROM 
  (
SELECT
 incident_id,
 opened_by,
 open_time - (9.0/24) AS open_time,
 DATEPART(dd, (open_time-(9.0/24))) AS open_day
   FROM incidentsm1
   RIGHTJOIN@dateTable dates
   ON incidentsm1.open_day = dates.date
   WHERE 
 DATEDIFF(DAY, open_time-(9.0/24), GETDATE())<7

  ) inc1
GROUPBY open_day

The more ideal situation however, would be to have a table object with the dates in

Solution 4:

Can you create the set of dates as part of your query? Something along the lines of:

SELECTCOUNT(*) AS Calls, ...
    FROM incidentsm1 RIGHTOUTERJOIN
         (SELECT date_values
            FROMTABLE(('27 Feb 2009'), ('28 Feb 2009'), ('1 Mar 2009'),
                       ('2 Mar 2009'), ('3 Mar 2009'), ('4 Mar 2009'),
                       ('5 Mar 2009')) AS date_list
         )
         ON ...

This is inspired by a sort of hybrid of Informix and DB2 notations and is pretty much guaranteed to be syntactically incorrect in both. Basically, is there a way in your DBMS of creating a literal table on the fly. One possibility - ugly but barely doable - would be to do a 7-way UNION of date literals selected from 'dual' or some table expression that guarantees one row (in Informix terms, SELECT MDY(2,28,2009) FROM "informix".systables WHERE tabid = 1 UNION ...).

Mssqlsm 2008 Odd Login Failed On Select Statement

2024-08-21T01:21:00.001-07:00

I have a variety of users on my SQL server, all of which CAN login to the server; however one user, once logged in cannot run anything (select, alter, edit) on a specific view - bu

Solution 1:

You probably have either

linked server issues. That is, the views access a linked server where the login mapping is incorrect
You have 2 views in different schemas. Do you use select * from view or select * from dbo.view: are you consistent?

Solution 2:

When you do a select 1 * from ViewName and you get a Login Failed, it possibly indicates one of the following:

Referencing table/view from another database:

This user may not have permissions to view objects in a different database.

Referencing table/view from Remote Server or a Linked Server:

This user may be using a mapping for either one of those servers where the login information may be out of date or may not have the proper credentials.

Trying To Get The Rooms Of Hotels That Got The Most Profit In A Query

2024-08-20T22:44:00.001-07:00

I'm trying to implement a query which give me the sum of most profitable room in each hotel(25 hotels) Below is my query: SELECT hotels.hotel_id,rooms.room_id,hotel_name,room_numbe

Solution 1:

Try this query:

SELECT * FROM 
(SELECT hotels.hotel_id,rooms.room_id,hotel_name,room_number,SUM(rooms.room_price) AS profit,COUNT(rooms.room_id) AS COUNT
FROM hotels,rooms,bookings
WHERE hotels.hotel_id=rooms.hotel_id
AND rooms.room_id=bookings.room_id
GROUPBY rooms.room_id) a GROUPBY hotel_id;

Edit:

This might do it:

SELECT hotel_id,room_id,room_number,MAX(a.tc) AS"Count",MAX(tp) AS"MostProfit"FROM
(SELECT hotel_id,rooms.room_id,room_number,COUNT(rooms.room_id) AS"tc",SUM(room_price) AS"tp"FROM rooms JOIN bookings 
ON rooms.room_id=bookings.room_id
GROUPBY rooms.room_id) a GROUPBY hotel_id

Solution 2:

Please try below once:

SELECT RO_BOOK.HOTEL_ID,
       RO_BOOK.ROOM_ID,
       RO_BOOK.ROOM_NUMBER,
       RO_BOOK.TOTAL_BOOKINGS,
       MAX(RO_BOOK.TOTAL_EARNINGS) PROFITS
FROM(
    SELECT  ROOMS.HOTEL_ID
        ROOMS.ROOM_ID,
        ROOMS.ROOM_NUMBER,
        COUNT(ROOMS.ROOM_ID) TOTAL_BOOKINGS
        SUM(ROOMS.ROOM_PRICE) TOTAL_EARNINGS
    FROM 
        ROOMS, BOOKINGS
    WHERE 
        BOOKINGS.ROOM_ID = ROOMS.ROOM_ID
        GROUPBY ROOMS.ROOM_ID) RO_BOOK
GROUPBY RO_BOOK.HOTEL_ID ;

It is similar to @tcadidot0 code, but column MAX(a.tc) AS "Count" return the maximum count irrespective of ROOM_ID. For eg: if hotel 1 has 2 rooms say, R100 and R200. The Cost of R100 be 1000 and R200 be 100. No of times R100 booked be 1, and R200 be 3. So the query would return: HOTEL 1 , R100, COUNT 2, PROFIT 1000.

Please correct me if I got the question wrong.